Extract data from datatable

Question

I extract data from all sheets in a workbook using the following code :

foreach (var sheetName in GetExcelSheetNames(connectionString))
            {
                if (sheetName.Contains("_"))
                {
                }
                else
                {
                    using (OleDbConnection con = new OleDbConnection(connectionString))
                    {

                        var dataTable = new DataTable();
                        string query = string.Format("SELECT  * ,{0} as sheetName FROM [{0}]", sheetName);
                        con.Open();
                        OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
                        try
                        {
                            adapter.Fill(dataTable);
                            ds.Tables.Add(dataTable);
                        }
                        catch { }
                    }
                }

I can't just figure how data are stocked in DataTable : sheetname is added as column ? how can I extract it ?

  foreach (DataTable dt in ds.Tables)
                    {
                            using (SqlConnection con = new SqlConnection(consString))
                            {
                                con.Open();
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {

                                    for (int j = 0; j < dt.Columns.Count; j ++)
                                    {
//what should I write here ?
                                    }
                                 }
                              }

did you debug and see what your dt(datatable) has? It clearly shows number columns and rows — prasy, Jul 14 '15 at 13:36

score 0 · Answer 1 · answered Jul 14 '15 at 13:32

0

assuming dt is your datatable variable,

do dt.Rows[row index][column index]

like

dt[2][4] -> will reference the 2nd row, 4th cell

I am not sure, but perhaps the sheetname might be stored at dt.TableName

answered Jul 14 '15 at 13:32

Veverke

9,208
4
51
95

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

In order to get the sheet name, using oledb, you will need to use code that looks something like this (thanks to this SO post and answer):

DataTable dtSheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
     List<string> sheets= new List<string>();
     foreach (DataRow dr in dtSheets.Rows)
     {
        if (dr["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
        {
             sheets.Add(dr["TABLE_NAME"].ToString());
        } 
     }

Below is how you access the values from a datatable:

var someValue = dt.Rows[i][j]

You need to get the item at the column index (j) of the row, at the row index (i), of the current datatable (dt).

Conversely, you can use the name of the column as well.

var someValue = dt.Rows[i]["columnName"]

edited May 23 '17 at 12:14

Community

1
1

answered Jul 14 '15 at 13:33

JasonWilczak

2,303
2
21
37

dt.Rows[i][j] doesn't return sheetname – Jul 14 '15 at 13:34
@canadacanada: so your question is not how to access data loaded into a datatable, rather, where/how are sheets loaded – Veverke Jul 14 '15 at 13:36
@canadacanada no, that will return the value of the cell. So if you are in row index 2 and column index 1, you will get the cell value of C2, in a simple excel example. – JasonWilczak Jul 14 '15 at 13:37
@canadacanada updated the answer to address your comment – JasonWilczak Jul 14 '15 at 13:42

Extract data from datatable

2 Answers2