Import Multiple Sheet to single DataSet C#

By | March 4, 2014
rx online

In this article we will learn to import the excel data from multiple sheet into .Net environment (Dataset)  from where you push data to SQL if required.

Also, i tried to handle the various file extension like xls, xlsx and xlsm (macros enabled excel file).

First step to import the excel file is that you need proper Engine who will communicate with excel file.

“Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;” for xls and xlsx

“Provider=Microsoft.ACE.OLEDB.12.0;” for xlsm.

To read the data from Excel first we need to available the file on local disk. E.g. if you are using import from excel from window application then you can read directly from hard disk. However, in case of web we need to upload the file to server and then read the same from local disk.

You can upload the file to server with the help of FileUploader.

I have now create the simple methods which will take two inputs

  1. File Name with path
  2. sheet name separated by (,)

 

To read the data from excel we have to use the OleDbConnection

using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;

namespace DevHDTool.ImportExcel
{
    public class ImportExcel
    {

        private string xlsStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;";
        private string xlsxStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;";
        private string xlsmStr = "Provider=Microsoft.ACE.OLEDB.12.0;";
        private string strconnection;
        private DataSet dsExcelData2 = new DataSet();
        private string strfileextension;
        /// <summary>
        /// This methos is used to convert the excel data to DataSet format
        /// </summary>
        /// <param name="file"></param>
        /// <param name="tabs"></param>
        /// <returns></returns>
        public DataSet ConvertExcelToDataset(string file, string tabs)
        {

            strfileextension = file.Substring(file.LastIndexOf('.'));

            if (strfileextension == ".xlsm" || strfileextension == ".xlsx")
            {
                strconnection = xlsxStr + " Data Source=" + file.ToString() + " ;Excel 12.0;HDR=Yes;IMEX=1";
            }
            else
            {
                strconnection = xlsStr + " Data Source=" + file.ToString();
            }

            OleDbConnection olecn = new OleDbConnection(strconnection);
            try
            {

                StringBuilder sbLog = new StringBuilder();

                string[] tab = tabs.Split(',');
                StringBuilder sbSQL = new StringBuilder();

                foreach (string strtab in tab)
                {
                    if (strtab.Trim().Length > 0)
                    {
                        DataSet dsExcelData = new DataSet();
                        OleDbCommand olecmd = new OleDbCommand(Convert.ToString("select distinct * from [" + strtab + "$]"), olecn);
                        OleDbDataAdapter olead = new OleDbDataAdapter(olecmd);
                        olead.Fill(dsExcelData);

                        DataTable dt = dsExcelData.Tables[0];
                        dt.TableName = strtab;

                        DataTable dt2 = new DataTable();
                        dt2 = dt.Copy();
                        dsExcelData2.Tables.Add(dt2);
                    }
                }
                return dsExcelData2;
            }
            catch (Exception ex)
            {
                return new DataSet();
            }
        }
}
}

 

Lets understand the logic what i wrote to read data from multiple sheet and return as single Dataset.

I passed 2 parameter filename, and sheet name separated by (,)

Very first line was checking the extension of uploaded/excel file and depending on file extension it will pick the proper engine  and attach the source path and store the same in variable strconnection which we will pass to OleDbConnection to read.

Now connection is established successfully,  as i mentioned that we are trying to read the multiple sheet which we passed to methods ConvertExcelToDataset in param tabs (,) separated.

 string[] tab = tabs.Split(',');
StringBuilder sbSQL = new StringBuilder();

This peace of code will separate all the sheet name and store it in array tab which we will execute one by one

 foreach (string strtab in tab)
                {
                    if (strtab.Trim().Length > 0)
                    {
                        DataSet dsExcelData = new DataSet();
                        OleDbCommand olecmd = new OleDbCommand(Convert.ToString("select distinct * from [" + strtab + "$]"), olecn);
                        OleDbDataAdapter olead = new OleDbDataAdapter(olecmd);
                        olead.Fill(dsExcelData);

                        DataTable dt = dsExcelData.Tables[0];
                        dt.TableName = strtab;

                        DataTable dt2 = new DataTable();
                        dt2 = dt.Copy();
                        dsExcelData2.Tables.Add(dt2);
                    }
                }

In above code i am calling each sheet with help of OleDbCommand and filling the DataSet dsExcelData. Since, we are executing single sheet at a time so on next line i am fetching the table and store it in local variable dt whoes datatype is DataTable.

DataTable dt = dsExcelData.Tables[0];
dt.TableName = strtab;

and the coping the table to global variable with the help of dt2 = dt.Copy(); since this table is already assigned to DataSet dsExcelData which was throwing error. to handle that i create another DataTable dt and copy the sheet data to this table and then add the same to variable to dsExcelData2 which is global variable for this class. which we are returning to the end user.

DataTable dt2 = new DataTable();
dt2 = dt.Copy();
dsExcelData2.Tables.Add(dt2);

 

I have successfully implemented this in two live projects and it is working fine as per my expectation. This method is designed in such a way that it hardly required any modification.  However, if you require  you can modify the same as per you requirement.

Hopes this article helps you to achieve your task.

Don’t forget to post you feedback, you feedback may help others.

1,601 total views, 4 views today