How to export data from XLSX/XLS spreadsheet to dataset without need Microsoft Office.

XsExcel SDK not only facilitates its users to import data to workbook from external data sources but also allow them to export their workbook data to a DataSet. Each worksheet in the Microsoft Excel will be converted to a datatable by sheet name, and all the datatables will be set into a dataset to as result.

This guide page provides C# users with how to export data from XLSX and XLS spreadsheet to DataSet with XsExcel control in C#.NET.


string filename = "DataTableSample.xlsx";
// Load a workbook from local excel file
Workbook workbook = new Workbook(filename);

DataSet ds = new DataSet();

// Find all worksheets in the excel
for (int i = 0; i < workbook.SheetCount; i++)
{
    if (!workbook.IsSheetHidden(i))
    {
        // Export worksheet to datatable
        DataTable dt = workbook.ConvertSheetToDataTable(i);
        // Add each datatable from sheet to dataset
        ds.Tables.Add(dt);
    }
}

return ds;

Notice - If you used the trial version of XsExcel SDK, can only edit, insert or get the first 300 row data!

More Excel tutorial