Open and read XLS files from your C#/VB.NET applications
The following example shows how you can read XLS and other Excel files by using only the GemBox.Spreadsheet .NET component.
GemBox.Spreadsheet can open many Excel file formats (including XLSX, XLS, XLSB, ODS, CSV, XML and HTML) in the same manner. Once you load your file from a disk, stream, database or some other source, you can read it, import its content, or modify it. You can read XLS files with just a few lines of code. You only need an IDE like Visual Studio or JetBrains Rider and .NET Framework, .NET Core, or any other platform that implements .NET Standard. Follow these steps to load a file from a disk and read its content: You can test reading the content of your XLS files with the interactive example below. Just upload your file and click on Run Example. After clicking on Run example, the C#/VB.NET code will be compiled, and the content of your file will be read with only .NET framework and the GemBox.Spreadsheet component. The following example loads the Excel file, iterates through its cells, and appends the content of the cells to StringBuilder. With GemBox.Spreadsheet you can also import data from any sheet to a The following example shows how you can load XLS or any other Excel file and create a XLS stands for eXceL Spreadsheet. It is a binary file format used as Microsoft Excel's primary file format up to version 2007, when it was superseded by Office Open XML. Although Excel encourages the use of new XML-based formats as a replacement for XLS, many tools still use XLS as their primary format. Beside XLS, with GemBox.Spreadsheet you can also read the following file formats using the same API: XLSX, XLSB, ODS, CSV, TXT, XLSM, XLTX, XLTM, XML, HTML, MHTML.Steps for reading XLS files
Try reading XLS files online
using System;
using System.Text;
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
var sb = new StringBuilder();
// Iterate through all worksheets in an Excel workbook.
foreach (var worksheet in workbook.Worksheets)
{
sb.AppendLine();
sb.AppendFormat("{0} {1} {0}", new string('-', 25), worksheet.Name);
// Iterate through all rows in an Excel worksheet.
foreach (var row in worksheet.Rows)
{
sb.AppendLine();
// Iterate through all allocated cells in an Excel row.
foreach (var cell in row.AllocatedCells)
if (cell.ValueType != CellValueType.Null)
sb.Append(string.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25));
else
sb.Append(new string(' ', 25));
}
}
Console.WriteLine(sb.ToString());
}
}
Imports System
Imports System.Text
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
Dim sb = New StringBuilder()
' Iterate through all worksheets in an Excel workbook.
For Each worksheet In workbook.Worksheets
sb.AppendLine()
sb.AppendFormat("{0} {1} {0}", New String("-"c, 25), worksheet.Name)
' Iterate through all rows in an Excel worksheet.
For Each row In worksheet.Rows
sb.AppendLine()
' Iterate through all allocated cells in an Excel row.
For Each cell In row.AllocatedCells
If cell.ValueType <> CellValueType.Null Then
sb.Append(String.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25))
Else
sb.Append(New String(" "c, 25))
End If
Next
Next
Next
Console.WriteLine(sb.ToString())
End Sub
End Module
Import data from XLS to DataTable
DataTable
. GemBox.Spreadsheet offers lots of options, such as the possibility to detect the columns data type, that can simplify your code.DataTable
from a specific cell range in the Excel worksheet in C# and VB.NET.using System;
using System.Data;
using System.Text;
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
// Select the first worksheet from the file.
var worksheet = workbook.Worksheets[0];
// Create DataTable from an Excel worksheet.
var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions()
{
ColumnHeaders = true,
StartRow = 1,
NumberOfColumns = 5,
NumberOfRows = worksheet.Rows.Count - 1,
Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
});
// Write DataTable content
var sb = new StringBuilder();
sb.AppendLine("DataTable content:");
foreach (DataRow row in dataTable.Rows)
{
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}", row[0], row[1], row[2], row[3], row[4]);
sb.AppendLine();
}
Console.WriteLine(sb.ToString());
}
}
Imports System
Imports System.Data
Imports System.Text
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If you are using the Professional version, enter your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
' Select the first worksheet from the file.
Dim worksheet = workbook.Worksheets(0)
' Create DataTable from an Excel worksheet.
Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
{
.ColumnHeaders = True,
.StartRow = 1,
.NumberOfColumns = 5,
.NumberOfRows = worksheet.Rows.Count - 1,
.Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
})
' Write DataTable content
Dim sb = New StringBuilder()
sb.AppendLine("DataTable content:")
For Each row As DataRow In dataTable.Rows
sb.AppendFormat("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}" & vbTab & "{4}", row(0), row(1), row(2), row(3), row(4))
sb.AppendLine()
Next
Console.WriteLine(sb.ToString())
End Sub
End Module
XLS file format