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.
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();