C# .NET - How to get Excel.Worksheet in DataTable using C#

Asked By Tejaswini Prashant J on 29-Dec-09 07:06 AM

Hi,
    I want to get the data from an excel worksheet to a data Table in c#.
    How I can do that using Microsoft.Office.Interop.Excel?

   This is my code snippets

   Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
             Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"c:\\TestBook1.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
             Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
             Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

Now,what to do next to get the Worksheet data in DataTable ?

Regards,
Tejaswini .

How to get Excel.Worksheet in DataTable using C#

Sakshi a replied to Tejaswini Prashant J on 29-Dec-09 07:10 AM

Create a reference in your project to Excel Objects Library. The excel object library can be added in the COM tab of adding reference dialog. I hope the following code in your menu click event method will help you a lot to achieve your need.

this.openFileDialog1.FileName = "*.xls";
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
   {
      Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
         openFileDialog1.FileName, 0, true, 5,
          "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
          0, true);
     Excel.Sheets sheets = theWorkbook.Worksheets;
     Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
     for (int i = 1; i <= 10; i++)
     {
     Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
     System.Array myvalues = (System.Array)range.Cells.Value;
     string[] strArray = ConvertToStringArray(myvalues);
     }
}

Thanks and Regards,
http://www.codecollege.NET

1 more solution

Sakshi a replied to Tejaswini Prashant J on 29-Dec-09 07:11 AM

If you are using a dynamically generated excel file then you can use the following:

Excel.Sheets sheets = m_Excel.Worksheets;

Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

System.Array myvalues;

Excel.Range range = worksheet.get_Range("A1", "E1".ToString());

myvalues = (System.Array)range.Cells.Value;

Thanks and Regards,
http://www.codecollege.NET

sol 3

Sakshi a replied to Tejaswini Prashant J on 29-Dec-09 07:11 AM

Now for the C# (this example assumes I have an Excel file at C:\Book1.xls and a named object in this workbook called "MyObject"):

using System.Data;
using System.Data.OleDb;
...
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from MyObject", con);
DataTable dt = new DataTable();
da.Fill(dt);

Thanks and Regards,
http://www.codecollege.NET

re - get Excel.Worksheet in DataTable using C#

DL M replied to Tejaswini Prashant J on 29-Dec-09 07:13 AM

hi,
you can try this link

http://forums.asp.net/t/1255191.aspx

http://www.aspspider.com/resources/Resource510.aspx

http://justins-fat-tire.blogspot.com/2008/07/updated-getting-data-from-excel-file-in.html

If you need to continue with your snippet,

[)ia6l0 iii replied to Tejaswini Prashant J on 29-Dec-09 08:00 AM

then you would need to get the data range into an Excel Range and iterate through it and construct the DataTable. There is no easy way out.

So, you need to append some pretty basic code around the Interop code that you have shown.

Like:

Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"c:\\TestBook1.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);

Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

'If you are not sure of the range of data, use UsedRange, but make sure you handle the null values explicitly.

int columnCount = worksheet .UsedRange.Cells.Columns.Count;

int rowCount = worksheet .UsedRange.Cells.Rows.Count;

DataSet myDataSet = new DataSet();

DataTable myDataTable = new DataTable();

//Add the DataColumns to the DataTable.

for (int counter = 1; counter <= columnCount ; counter++)

{

myDataTable.Columns.Add(string.concat("specifythecolumnnamehere", counter.ToString()), System.Type.GetType("System.String"));

}

DataRow myDataRow;

Excel.Range range;

//Add the Rows to the DataTable.

for (int rowCounter = 1; rowCounter <= rowCount; rowCounter++)

{

myDataRow = myDataTable.NewRow();

for (int colCounter = 1; colCounter <= columnCount; colCounter ++)

{

range = (Microsoft.Office.Interop.Excel.Range) (worksheet.Cells[rowCounter, colCounter]);

myDataRow[string.concat("specifythecolumnnamehere", colCounter.ToString()) = range.Text;

}

myDataTable.Rows.Add(dr);

}

//Add the datatable to the DataSet.

myDataSet.Tables.Add(myDataTable);

However note that the OleDataAdapter method reads the used range from a WorkSheet into a DataSet directly , which is simpler if that is what you are after.

Excel sheet to DataTable

Shailendrasinh Parmar replied to Tejaswini Prashant J on 02-Jan-10 01:24 AM

Here is the code to import Excel sheet data to DataTable

public DataTable Import(String path)

{

Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();

Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;

int index = 0;

object rowIndex = 2;

DataTable dt = new DataTable();

dt.Columns.Add("FirstName");

dt.Columns.Add("LastName");

dt.Columns.Add("Mobile");

dt.Columns.Add("Landline");

dt.Columns.Add("Email");

dt.Columns.Add("ID");

DataRow row;

while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)

{

rowIndex = 2 + index;

row = dt.NewRow();

row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);

row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);

row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);

row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);

row[4] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2);

index++;

dt.Rows.Add(row);

}

app.Workbooks.Close();

return dt;

}

Ref :: http://www.aspspider.com/resources/Resource510.aspx

Hope this helps.

Vinicius replied to Shailendrasinh Parmar on 14-Apr-10 09:41 AM

Very good solution !
I tried and worked vey well.

Cika Pero replied to Tejaswini Prashant J on 25-Nov-11 05:00 AM

Hello,

you also very easily import Excel to DataTable with this Excel C# / VB.NET component.

If you have typed DataTable then you can use this Excel C# code snippet that also shows how to handle value conversion between Excel cell value and DataTable cell value:

var ef = new ExcelFile();

ef.LoadXls("MyData.xls");

var ws = ef.Worksheets[0];

ws.ExtractDataEvent += (sender, e) =>

{

    if (e.ErrorID == ExtractDataError.WrongType)

    {

      // Handle value conversion here.

      e.DataTableValue = e.ExcelValue;

      e.Action = ExtractDataEventAction.Continue;

    }

};

// dataTable has to be typed DataTable - it needs to have schema (columns) defined.

ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows[1], ws.Columns[0]);

On the other hand if you need that DataTable be produced from Excel file, then you can use this code that will create DataTable with DataColumn types resolved from Excel column values:

var excelFile = new ExcelFile();

excelFile.LoadXls("MyData.xls");

var dataTable = excelFile.Worksheets[0].CreateDataTable(ColumnTypeResolution.Auto);

Cika Pero replied to Cika Pero on 25-Nov-11 05:04 AM

** EDIT: forgot to add relevant links **

Hello,

you also very easily import http://www.gemboxsoftware.com/support/articles/import-export-datatable-xls-xlsx-ods-csv-html-net with this http://www.gemboxsoftware.com/spreadsheet/overview component.

If you have typed DataTable then you can use this http://www.gemboxsoftware.com/spreadsheet/overview code snippet that also shows how to handle value conversion between Excel cell value and DataTable cell value:

var ef = new ExcelFile();

ef.LoadXls("MyData.xls");

var ws = ef.Worksheets[0];

ws.ExtractDataEvent += (sender, e) =>

{

    if (e.ErrorID == ExtractDataError.WrongType)

    {

    // Handle value conversion here.

    e.DataTableValue = e.ExcelValue;

    e.Action = ExtractDataEventAction.Continue;

    }

};

// dataTable has to be typed DataTable - it needs to have schema (columns) defined.

ws.ExtractToDataTable(dataTable, ws.GetUsedCellRange().Height, ExtractDataOptions.None, ws.Rows[1], ws.Columns[0]);

On the other hand if you need that DataTable be produced from Excel file, then you can use this code that will create DataTable with DataColumn types resolved from Excel column values:

var excelFile = new ExcelFile();

excelFile.LoadXls("MyData.xls");

var dataTable = excelFile.Worksheets[0].CreateDataTable(ColumnTypeResolution.Auto);