How to read an Excel file into a Dataset

If you need to read an Excel file into a dataset so you can work with the data, try this:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace MyNamespace
{
    public class ExcelReader
    {
        public DataSet ConvertExcelFileToDataSet(string folderPath, string fileName)
        {
            //string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + folderPath + ";Extended Properties=\"text;Excel 8.0;HDR=No;FMT=Delimited\"";
            
            string connString = "provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + folderPath + "\\" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes\"";
            //string connString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;
            //Extended Properties = "Excel 8.0;HDR=YES";
            DataSet ds = new DataSet();

            using (OleDbConnection oledbConn = new OleDbConnection(connString))
            {
                //_logger.WriteToLog(className + " " + "IMPORT:Opening file-" + folderPath + "\\" + fileName);
                OleDbDataAdapter oleda = new OleDbDataAdapter();
                oledbConn.Open();
                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
                oleda.SelectCommand = cmd;
                oleda.Fill(ds, "Table1");
                //_logger.WriteToLog(className + " " + "IMPORT:Dataset fill for file-" + folderPath + "\\" + fileName + " was successful");
            }
            return ds;
        }

        
    }
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s