How to export data from excel sheet to a datatable?

1 min read

The IWorksheet.ExportDataTable method is used to export data from excel sheet to DataTable. Please refer the below code snippet which illustrates this:

//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Get as DataTable
DataTable datatable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
//Upload to dataset
DataSet ds = new DataSet();
ds.Tables.Add(datatable);

'The first worksheet object in the worksheets collection is accessed.
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Get as DataTable
Dim datatable As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
'Upload to dataset
Dim ds As DataSet = New DataSet()
ds.Tables.Add(datatable)

Did you find this information helpful?

Yes

Did not find the solution Contact Support

Comments (2)

Please to leave a comment

lethalmarc

Oct 19, 2014 11:09 PM

You can use OLEDB ... MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); Source : http://csharp.net-informations.com/excel/csharp-excel-oledb.htm Lethal

Divya

Nov 27, 2016 07:51 AM

Iam unable to find methods other than importdata that can be used to add data to worksheet. Import data table is not available. I have added reference to syncdusion.xlsio in my xamarin Android project. Am i missing any other reference?

Johnson Manohar

Nov 06, 2017 06:59 PM

Hi Divya,

Import from DataTable is not supported in Xamarin platform.

How to export data from excel sheet to a datatable?

Access denied