How to import Excel XML spreadsheets without interop

• Download source files - 1.52 KB

Introduction

Did you ever need to import Excel spreadsheets? If you did, you know there're sometimes problems with interop assemblies deploying to desktops and so.

Office 2003 offers to store spreadsheets in XML. So store your spreadsheet as XML and try to import them. Just include ExcelReader.cs in your project and use the SPOTX namespace. Then you'll be able to add the imported DataTable as datasource to your grid, like in the included example.

using SPOTX;

namespace ExcelImportTest
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void importXMLSpreadsheetToolStripMenuItem_Click(object sender, 
                     EventArgs e)
        {
            //select file in dialog and then fill DataSOurce of main grid 
            if (ctlOfn.ShowDialog() == DialogResult.OK)
                this.ctlGridImport.DataSource = 
                          SPOTX.ExcelReader.ReadExcelXML(ctlOfn.FileName);
        }
    }
}

The first row in the imported Excel file are column headers. The method was tested on contiguous spreadsheets only. The code is clear enough I think, so adapting for .NET 1.1 consists only in replacing the Dictionary by Hashtable. Also modifying import to return collections or to omit column headers seems easy.

Hope this will help somebody.