How to Import or Export DataGrid to Excel or HTML files from your
.NET applications?
Following example shows how to import or export DataGrid to XLS, XLSX, CSV or HTML
files by using only GemBox.Spreadsheet .NET component.
GemBox.Spreadsheet is a .NET component which provides easy and high performance
way to write, read or convert native Microsoft Excel files (XLS, CSV or XLSX) and
HTML files without the need for Microsoft Excel on either the developer or client
machines. GemBox.Spreadsheet Free comes free of charge while GemBox.Spreadsheet
Professional is a commercial version (licensed per developer). Find more information
about GemBox.Spreadsheet features or reasons why our component is better then Excel Automation.
Example imports/exports DataGrid to Excel file (in XLS format) by directly working
with DataGrid using InsertDataTable and ExtractToDataTable methods:
|
ExcelFile ef =
new ExcelFile();
DataTable dataTable = (DataTable)dataGrid1.DataSource;
// Depending on the format of the input file, you need to
change this:
dataTable.Columns.Add("FirstName",
typeof(string));
dataTable.Columns.Add("LastName",
typeof(string));
// Load Excel file.
ef.LoadXls("FileName.xls");
// Select the first worksheet from the file.
ExcelWorksheet ws = ef.Worksheets[0];
// Extract the data from the worksheet to the DataTable (DataGrid).
// Data is extracted starting at first row and first column for 10 rows or until
the first empty row appears.
ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow,
ws.Rows[0], ws.Columns[0]);
// Change the value of the first cell in the DataTable (DataGrid).
dataTable.Rows[0][0] = "Hello world!";
// Insert the data from DataTable to the worksheet starting
at cell "A1".
ws.InsertDataTable(dataTable, "A1",
true);
// Save the file to XLS format.
ef.SaveXls("DataGrid.xls");
|
|
Dim ef
As New ExcelFile
Dim dataTable As
DataTable = DirectCast(dataGrid1.DataSource, DataTable)
' Depending on the format of the input file, you need to
change this:
dataTable.Columns.Add("FirstName",
GetType(String))
dataTable.Columns.Add("LastName",
GetType(String))
' Load Excel file.
ef.LoadXls("FileName.xls")
' Select the first worksheet from the file.
Dim ws As
ExcelWorksheet = ef.Worksheets(0)
' Extract the data from the worksheet to the DataTable (DataGrid).
' Data is extracted starting at first row and first column for 10 rows or until
the first empty row appears.
ws.ExtractToDataTable(dataTable, 10, ExtractDataOptions.StopAtFirstEmptyRow,
ws.Rows(0), ws.Columns(0))
' Change the value of the first cell in the DataTable (DataGrid).
dataTable.Rows(0)(0) = "Hello world!"
' Insert the data from DataTable to the worksheet
starting at cell "A1".
ws.InsertDataTable(dataTable, "A1",
True)
' Save the file to XLS format.
ef.SaveXls("DataGrid.xls")
|
To import/export the DataGrid in another format you only need to replace the LoadXls/SaveXls
method with preferred one (LoadXlsx/SaveXlsx, LoadCsv/SaveCsv or SaveHtml).
|
|
|
© GemBox Software. All rights reserved.
|
