Click or drag to resize

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

LoadDataTable writes the rows of a System.Data.DataTable to a worksheet, optionally with the column names in the first row. GetDataTable reads a range of a worksheet into a new DataTable; the column names can come from the first row of the range.

Use them to export the result of a database query to Excel, or to read an Excel file into a DataTable that you can bind to a grid or save to a database.

Code Samples

Read the used range of an existing workbook into a DataTable:

using System.Data;

DataTable employees;
ExcelWorkbook sourceWorkbook = new ExcelWorkbook(excelFile);
try
{
    ExcelWorksheet sourceWorksheet = sourceWorkbook.Worksheets[0];
    // true: the first row of the range has the column names
    employees = sourceWorksheet.GetDataTable(sourceWorksheet.UsedRange, true);
}
finally
{
    sourceWorkbook.Close();
}

Load a DataTable into a new workbook, starting with row 5, column 1, with the column names in row 5:

ExcelWorkbook workbook = new ExcelWorkbook(ExcelWorkbookFormat.Xlsx_2007);
ExcelWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Employees";

worksheet.LoadDataTable(dataTable, 5, 1, true);

// a bold header that stays visible when scrolling, and columns fitted to the data
worksheet[5, 1, 5, dataTable.Columns.Count].Style.Font.Bold = true;
worksheet["A6"].FreezePanes();
worksheet.AutofitColumns();

workbook.Save("Employees.xlsx");
workbook.Close();
See Also