CODEWITHSUNDEEP
c#exceldatatable
Tim Freese
Tim Freese

Reputation: 435

Excel Sheets into multiple C# datatables

I am trying to import a excel spreadsheet into a an array of datatables. Each table will be a sheet from the spreadsheet. Right now I am seeing that each table contains the information from all sheets. I am thinking this part is not working correctly.

dataSet.Clear();

Let me know if you can see what I am doing wrong.

Here is the rest of the code.

        public DataTable[] ReadDoc()
        {
        string filename = @"C:\Documents and Settings\user\Desktop\Test.xlsx";
        DataTable dt = null;

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";", filename);
        OleDbConnection connection = new OleDbConnection(connectionString);

        DataSet dataSet = new DataSet();
        DataSet finalDataSet = new DataSet();
        DataTable[] table = new DataTable[3];
        connection.Open();

        dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

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

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

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

        // Loop through all of the sheets if you want too...
        for (int j = 0; j < excelSheets.Length; j++)
        {
            string query = String.Format("SELECT * FROM [" + excelSheets[j] + "]");
            dataSet.Clear();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
            dataAdapter.Fill(dataSet);
            table[j] = dataSet.Tables[0];
        }

        return table;
        }

Thanks for the help.

Upvotes: 0

Views: 1319

Answers (1)

Harval
Harval

Reputation: 76

The problem here is your dataSet, is declared outsife the for. Each datatable array item is getting the same information. dataSet.Tables[0]; You must declare inside the for. Each iteration store different information.

 for (int j = 0; j < excelSheets.Length; j++)
    {
        DataSet dataSet = new DataSet();
        string query = String.Format("SELECT * FROM [" + excelSheets[j] + "]");
        .....
    }

Upvotes: 1

Related Questions