Importing Data from a DataTable Object into Excel with ExpertXls Library for .NET

The ExpertXls Excel Library for .NET offers support for loading values from a System.DataTable object directly into an Excel worksheet and for saving values from an Excel worksheet to a System.DataTable object. A DataTable object can be loaded into a worksheet using the method ExcelWorksheet.LoadDataTable() and a worksheet from a workbook can be exported to DataTable object using the ExcelWorksheet.GetDataTable() method.

Code Samples

Below there is a sample code for loading a DataTable into a worksheet and for getting a DataTable object from a worksheet:

Copy

// load an existing Excel file to a temporary workbook

// create the Excel stream
string dataFilePath = System.IO.Path.Combine(Server.MapPath("~"), @"Data\awemployees.xls");
System.IO.FileStream sourceXlsDataStream = new System.IO.FileStream(dataFilePath, System.IO.FileMode.Open);

ExcelWorkbook tempWorkbook = new ExcelWorkbook(sourceXlsDataStream);
ExcelWorksheet tempWorksheet = tempWorkbook.Worksheets[0];

// get the data from the used range of the temporary workbook to a .NET DataTable object
DataTable exportedDataTable = tempWorksheet.GetDataTable(tempWorksheet.UsedRange, true);

//close the temporary workbook
tempWorkbook.Close();
//close the data stream
sourceXlsDataStream.Close();

// Create the workbook in which the data from the DataTable will be loaded
ExcelWorkbookFormat workbookFormat = radioXlsFormat.Checked ? ExcelWorkbookFormat.Xls_2003 : ExcelWorkbookFormat.Xlsx_2007;

// create the workbook in the desired format with a single worksheet
ExcelWorkbook workbook = new ExcelWorkbook(workbookFormat);

// get the first worksheet in the workbook
ExcelWorksheet worksheet = workbook.Worksheets[0];

// set the default worksheet name
worksheet.Name = "Data Loaded from a DataTable";

// load data from DataTable into the worksheet
worksheet.LoadDataTable(exportedDataTable, 5, 1, true);

' load an existing Excel file to a temporary workbook

' create the Excel stream
Dim dataFilePath As String = System.IO.Path.Combine(Server.MapPath("~"), "Data\awemployees.xls")
Dim sourceXlsDataStream As New System.IO.FileStream(dataFilePath, System.IO.FileMode.Open)

Dim tempWorkbook As New ExcelWorkbook(sourceXlsDataStream)
Dim tempWorksheet As ExcelWorksheet = tempWorkbook.Worksheets(0)

' get the data from the used range of the temporary workbook to a .NET DataTable object
Dim exportedDataTable As DataTable = tempWorksheet.GetDataTable(tempWorksheet.UsedRange, True)

'close the temporary workbook
tempWorkbook.Close()
'close the data stream
sourceXlsDataStream.Close()

' Create the workbook in which the data from the DataTable will be loaded
Dim workbookFormat As ExcelWorkbookFormat = If(radioXlsFormat.Checked, ExcelWorkbookFormat.Xls_2003, ExcelWorkbookFormat.Xlsx_2007)

' create the workbook in the desired format with a single worksheet
Dim workbook As New ExcelWorkbook(workbookFormat)

' get the first worksheet in the workbook
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)

' set the default worksheet name
worksheet.Name = "Data Loaded from a DataTable"

' load data from DataTable into the worksheet
worksheet.LoadDataTable(exportedDataTable, 5, 1, True)