Import and Export between Excel files and DataTable
The Document Processing Library(DPL) allows you to create, import, modify and export documents without relying on external dependencies like Adobe Acrobat or Microsoft Office.
With the R2 2021 release, the RadSpreadProcessing library introduced the DataTableFormatProvider which allows an easy conversion between a DPL's Workbook and a DataTable. A full list of the supported Excel file formats can be found in the RadSpreadProcessing Overview - Supported formats.
As the Workbook can be imported from and exported to an Excel file, the import/export between Excel files and DataTable becomes super easy. With a few lines of code, you can bind a databound control such as the Grid to an Excel file or export a DataTable to a physical Excel file.
This article will cover two scenarios:
The examples in this article use the
XlsxFormatProviderfor demo purposes but any other format provider could be used instead. A list of the available format providers that allow the easy import/export of a Workbook can be found in RadSpreadProcessing Formats and Conversion - General Information article.
Import DataTable From Excel
To get a DataTable from text(.txt) or Excel file(.csv, .xlsx), we have 2 steps:
-
Import Excel file to a Workbook
-
Export Workbook To a DataTable
// Step 1: Load Excel file to DPL Workbook
Telerik.Windows.Documents.Spreadsheet.Model.Workbook workbook;
IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
if (!File.Exists(path))
{
throw new FileNotFoundException(String.Format("File {0} was not found!", path));
}
using (Stream input = new FileStream(path, FileMode.Open))
{
workbook = formatProvider.Import(input);
}
// Step 2: Create DataTable from Workbook
DataTableFormatProvider provider = new DataTableFormatProvider();
DataTable dataTable = provider.Export(workbook.ActiveWorksheet);
Export DataTable To Excel
To Export a DataTable to a text(.txt) or Excel file(.csv, .xlsx), we have 2 steps:
- Import DataTable to a Workbook
- Export Workbook to an Excel file
// Step 1: Convert a DataTable to Workbook
DataTableFormatProvider provider = new DataTableFormatProvider();
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
provider.Import(dataTable, worksheet);
// Step 2: Save Workbook as Excel file
IWorkbookFormatProvider formatProvider = new Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx.XlsxFormatProvider();
using (Stream output = new FileStream(path, FileMode.Create))
{
formatProvider.Export(workbook, output);
}