C#: Loading two Excel into one DataTable

HI

I am writing a C# windows program and I need to load an Excel sheet into a DataTable. This is not a problem, and I do it like this:

dt_Database.Clear();
       
OleDbDataAdapter MyDA = new OleDbDataAdapter("select * from [" + "ark1" + "$]", @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelDatabaseFile.Text + ";Extended Properties=\"Excel 8.0;Persist Security Info=False;HDR=NoIMEX=1;\"");
        try
        {
          MyDA.Fill(dt_Database);
        }
        catch (OleDbException oe)
        {
          MessageBox.Show(oe.Message);
        }

This works fine. My problem is that the data that I have now is split into two execl sheets.

Is there a way to append data from the second Excel sheet to the dt_Database DataTable after filling it with data from the first Excel sheet??

Thanks in advance
Søren Augustesen
LVL 1
poultarpAsked:
Who is Participating?
 
tuhinbCommented:
Step 1:
You can read the Excel file using the OLEDB adapter to get the Excel sheet names using the code below:

public static string[] getExcelSheets(string mFile)
{
    try
    {
        string strXlsConnString;
        strXlsConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                           mFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        OleDbConnection xlsConn = new OleDbConnection(strXlsConnString);
        xlsConn.Open();
        DataTable xlTable = new DataTable();
        xlTable = xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        System.String strExcelSheetNames = "";
        string sheetName;
        //Loop through the excel database table names and take only
        //the tables that ends with a $ characters. Other tables are not worksheets...
        for (int lngStart = 0; lngStart < xlTable.Rows.Count; lngStart++)
        {
            //Remove the single-quote surrounding the table name...
            sheetName = xlTable.Rows[lngStart][2].ToString().Replace("'", "");
            if (sheetName.EndsWith("$")) //Yes, this is a worksheet
            {
                //concatenate with a single-quote delimeter... to be returned
                //as a string array later using the split function
                strExcelSheetNames += sheetName.Substring(0, sheetName.Length - 1) + "~";
            }
        }

        if (strExcelSheetNames.EndsWith("~"))
        //the last single quote needs to be removed
        //so that the array index ends with the last sheetname
        {
            strExcelSheetNames = strExcelSheetNames.Substring(0,
                                    strExcelSheetNames.Length - 1);
        }

        xlsConn.Close();
        xlsConn.Dispose();
        char[] chrDelimter = { '~' };
        return strExcelSheetNames.Split(chrDelimter);
    }
    catch (Exception exp)
    {
        throw new Exception("Error while listing the excel" +
                            " sheets from upload file " + exp.Message, exp);
    }
}

Step 2:
Once sheet names are stored in a string array variable, you can read the contents from the Excel sheet using your code above and merge the content of each data tables.

http://www.codeproject.com/KB/aspnet/ExporttoMultipleWorksheet.aspx
0
 
poultarpAuthor Commented:
Hi

I can now see that I didn't explain my problem correctly in the first post - sorry about that.

My data is not in to different Excel sheets, but in two different Excel files, so your very detailed explanation is not what I was looking for.

However the "merge" command was just what I was looking for - and the way to read data from multiple sheets were a nice bonus :o)

Regards
Søren Augustesen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.