Import or Export data to XLS, XLSX, CSV or HTML Files from your .NET applications

FAQ

Help

Bugs & Features

Articles

How to Import or Export data to Excel or HTML files from your .NET applications?

Following examples show two different ways to import or export to Excel 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.

First example imports/exports data to Excel file (in XLS format) by directly working with DataTable using InsertDataTable and ExtractToDataTable methods:

C# code:

ExcelFile ef = new ExcelFile();
DataTable dataTable = new DataTable();

// 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.
// 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.
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("DataTable.xls");

Visual Basic .NET code:

Dim ef As New ExcelFile
Dim dataTable As New 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.
' 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.
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("DataTable.xls")

Second example imports/exports data to Excel file (in XLS format) by direct access to cells in the first worksheet:

C# code:

ExcelFile ef = new ExcelFile();

// Loads Excel file.
ef.LoadXls("filename.xls");

// Selects first worksheet.
ExcelWorksheet ws = ef.Worksheets[0];

// Selects the first row from the worksheet.
ExcelRow row = ws.Rows[0];

foreach (ExcelCell cell in row.AllocatedCells)
{
// Writes every cell of the first row at the output.
Console.WriteLine(cell.Value);
}

// Writes "Hello world!" to the first cell of the worksheet.
ws.Cells[0, 0].Value = "Hello world!";

// Saves the file in XLS format.
ef.SaveXls("NewFile.xls");

Visual Basic .NET code:

Dim ef As New ExcelFile

' Loads Excel file.
ef.LoadXls("filename.xls")

' Selects first worksheet.
Dim worksheet As ExcelWorksheet = ef.Worksheets(0)

' Selects the first row from the worksheet.
Dim row As ExcelRow = worksheet.Rows(0)

For Each cell In row.AllocatedCells
' Writes every cell of the first row at the output.
Console.WriteLine(cell.Value)
Next

' Writes "Hello world!" to the first cell of the worksheet.
worksheet.Cells(0, 0).Value = "Hello world!"

' Saves the file in XLS format.
ef.SaveXls("NewFile.xls")

To import/export the file in another format you only need to replace the LoadXls/SaveXls method with preferred one (LoadXlsx/SaveXlsx, LoadCsv/SaveCsv or SaveHtml).