Working Example

Dec 26, 2013 at 2:15 PM
I love this tool, as by converting Excel file of any type into DataTables in a DataSet, Excel becomes transparent to the user. In my application, this makes life easy.
I made a short working C# demo that I post here.

Make a new Project, add a DataGridView and run the demo with either xls or xlsx file.
using System.Data;
using System.Windows.Forms;
using System.IO;
using Excel;

namespace ExcelDataReader
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            displayDataGrid(getDataSet());
        }

        public DataSet dataSet = new DataSet();

        public DataSet getDataSet()
        {
            // Get the Excel file and convert to dataset 
            OpenFileDialog fileDialog = new OpenFileDialog();
            DialogResult result = fileDialog.ShowDialog();
            FileStream stream = File.Open(fileDialog.FileName, FileMode.Open, FileAccess.Read);
            if (fileDialog.FileName.EndsWith("xls"))
                dataSet = readExcel2003(stream);
            if (fileDialog.FileName.EndsWith("xlsx"))
                dataSet = readExcelXML(stream);
            return dataSet;
        }

        public DataSet readExcelXML(FileStream stream)
        {
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            dataSet = excelReader.AsDataSet();
            excelReader.Close();
            return dataSet;
        }

        public DataSet readExcel2003(FileStream stream)
        {
            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            dataSet = excelReader.AsDataSet();
            excelReader.Close();
            return dataSet;
        }

        public void displayDataGrid(DataSet dataSet)
        {
            dataGridView1.DataSource = dataSet.Tables[1];
            dataGridView1.Show();

        }
    }
}