How to import all the Excel sheets to DataSet in C#

Question

I've searched internet for this and couldn't really find a question like it. Everyone was looking for a way to import an individual sheet in the excel file but what I want is to import all the sheets in the file to DataTable's in DataSet without knowing the sheet names.

I've not done much things with Excel before. This a sample and partially working code I've found on the internet and it only parses the given sheet name:

public static DataSet Parse(string fileName, string workSheetName)
{
    string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
    string query = string.Format("SELECT * FROM [{0}$]", workSheetName);

    DataSet data = new DataSet();
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        con.Open();
        OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
        adapter.Fill(data);
    }

    return data;
}

In the code above, as you see, workSheetName should be passed in so the query can know where to look at to import. In my case, I want it to traverse all the sheets no matter what they are named like and import them to individual DataTable's of a DataSet.

So in essence, the final thing will be a DataSet in which each DataTable holds rows for each sheet in the imported file.

Answer 1

This is a code I came up with and it works perfect but I saw someone else already added an answer:

static DataSet Parse(string fileName)
{
    string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);


    DataSet data = new DataSet();

    foreach(var sheetName in GetExcelSheetNames(connectionString))
    {
        using (OleDbConnection con = new OleDbConnection(connectionString))
        {    
            var dataTable = new DataTable();
            string query = string.Format("SELECT * FROM [{0}]", sheetName);
            con.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
            adapter.Fill(dataTable);
            data.Tables.Add(dataTable);
        }
    }

    return data;
}

static string[] GetExcelSheetNames(string connectionString)
{
        OleDbConnection con = null;
        DataTable dt = null;
        con= new OleDbConnection(connectionString);
        con.Open();
        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt == null)
        {
            return null;
        }

        String[] excelSheetNames = new String[dt.Rows.Count];
        int i = 0;

        foreach (DataRow row in dt.Rows)
        {
            excelSheetNames[i] = row["TABLE_NAME"].ToString();
            i++;
        }

        return excelSheetNames;
}

Answer 2

Because I was bored:

 static void Main(string[] args)
 {
            string filename = @"c:\temp\myfile.xlsx";    
            System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection( 
                        "Provider=Microsoft.ACE.OLEDB.12.0; " +
                         "data source='" + filename + "';" +
                            "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
            myConnection.Open();
            DataTable mySheets = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                
            DataSet ds = new DataSet();
            DataTable dt;

            for (int i = 0; i <= mySheets.Rows.Count; i++)
            {
                   dt =   makeDataTableFromSheetName(filename, mySheets.Rows[i]["TABLE_NAME"].ToString());
                   ds.Tables.Add(dt);
            }
 }

private static DataTable makeDataTableFromSheetName(string filename, string sheetName)
{      
    System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
    "Provider=Microsoft.ACE.OLEDB.12.0; " +
    "data source='" + filename + "';" +
    "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");

    DataTable dtImport = new DataTable();
    System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "$]", myConnection);
    myImportCommand.Fill(dtImport);
    return dtImport;
}

Answer 3

The function that was suggested by Avitus is correct but it has logica error, you must rewrite in :

DataTable dtImport = new DataTable();
using ( System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
            "Provider=Microsoft.ACE.OLEDB.12.0; " +
             "data source='" + filename + "';" +
                "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ")){


using ( System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "$]", myConnection))
myImportCommand.Fill(dtImport);
} return dtImport;

this is correct, otherwise you must dispose connection and dataadapter manually.