CODEWITHSUNDEEP
c#excelxlsx
Noufal P
Noufal P

Reputation: 87

how to get all excel sheet data in c# using OleDbDataAdapter

My excel sheet contains sheet1, sheet2, sheet3(total 3 sheets). i need to get all data from 3 sheets. This is my code given below

           var cnnStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 12.0;'");

            var cnn = new OleDbConnection(cnnStr);

            // get schema, then data
            var dt = new DataTable();
            try
            {
                cnn.Open();
                var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //DataTable dtCols = cnn.GetSchema("Columns");
                if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
                string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
                string sql = String.Format("select * from [{0}]", worksheet);
                var da = new OleDbDataAdapter(sql, cnn);
                da.Fill(dt);
            }
            catch (Exception e)
            {
                // ???
                throw e;
            }
            finally
            {
                // free resources
                cnn.Close();
            }

From the above code, i gets data from sheet 1 only. how can i get data from all sheets?

Upvotes: 0

Views: 426

Answers (1)

gunhal
gunhal

Reputation: 11

you must declare worksheetNumber before

if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");

like this

int worksheetNumber = 3; if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");

Upvotes: 0

Related Questions