Dev Direct Solution Center

Reading data from XLS, XLSX or CSV files from .NET app with GemBox.Spreadsheet.

Download the complete source code for this solution.

Introduction

This example shows how to read data from XLS/XLSX/CSV files by using only GemBox.Spreadsheet (no need for Automation).

Detail

This simple example shows how easy it is to read different data (cell values, formulas and comments) from XLS/XLSX/CSV files.

Example shows how to read XLS/XLSX/CSV file, select specified worksheet and cell and read its value, formula and comment.

Setup

Download the sample code using the above link and unzip it. After you unzip sample code you will notice that there are two solutions - one for C# and one for VB.

Load XLS/XLSX/CSV file

Button "Open File" will load selected XLS/XLSX/CSV file and fill listBox with sheet names from that file. Loading spreadsheet file and extracting sheet names is done like this:

C#
    // Load spreadsheet file from specified format.         
    switch (openFileDialog1.FilterIndex)
    {
    	case 1:
    		excelFile.LoadXls(filename);
    	break;
    	case 2:
    		excelFile.LoadXlsx(filename, XlsxOptions.None);
    	break;
    	case 3:
    		excelFile.LoadCsv(filename, CsvType.CommaDelimited);
    	break;
    }

    listBox1.Items.Clear();

    // Fill listBox with sheet names of all sheets in the spreadsheet file.
    foreach (ExcelWorksheet ws in excelFile.Worksheets)
    	listBox1.Items.Add(ws.Name);

VB
    ' Load spreadsheet file from specified format.
    Select Case OpenFileDialog1.FilterIndex
    	Case 1
    		excelFile.LoadXls(filename)
    	Exit Select
    	Case 2
    		excelFile.LoadXlsx(filename, XlsxOptions.None)
    	Exit Select
    	Case 3
    		excelFile.LoadCsv(filename, CsvType.CommaDelimited)
    	Exit Select
    End Select

    listBox1.Items.Clear()

    Dim ws As ExcelWorksheet

    ' Fill listBox with sheet names of all sheets in the spreadsheet file.
    For Each ws In excelFile.Worksheets
    	listBox1.Items.Add(ws.Name)
    Next

Extracting data from specified sheet and cell in XLS/XLSX/CSV file

Button "Get cell value >>" will get the value, formula and comment from specified cell. Source sheet is selected from listBox by selecting its name. Cell is selected by defining its name or its row and column position - depending on radioButton selected.

Selecting specified cell and writing its values is done like this:

C#
         
    ExcelWorksheet sheet = excelFile.Worksheets[listboxSelectedIndex];
    ExcelCell cell;

    // Get cell object.
    if (radioButton1.Checked)
    	cell = sheet.Cells[int.Parse(textBox1.Text), int.Parse(textBox2.Text)];
    else
    	cell = sheet.Cells[textBox3.Text];

    object cellValue = cell.Value;

    // Get cell values.
    if (cellValue != null)
    	textBox4.Text = cellValue.ToString();
    
    textBox5.Text = cell.Formula;
    textBox6.Text = cell.Comment.Text;

VB
    Dim sheet As ExcelWorksheet = excelFile.Worksheets(listboxSelectedIndex)
    Dim cell As ExcelCell

    ' Get cell object.
    If radioButton1.Checked Then
    	cell = sheet.Cells(Integer.Parse(textBox1.Text), Integer.Parse(textBox2.Text))
    Else
    	cell = sheet.Cells(textBox3.Text)
    End If

    Dim cellValue As Object = cell.Value

    ' Get cell values.
    If (Not cellValue Is Nothing) Then
    	textBox4.Text = cellValue.ToString
    End If

    textBox5.Text = cell.Formula
    textBox6.Text = cell.Comment.Text

Conclusion

This simple solution describes how to read from different spreadsheet files by using GemBox.spreadsheet.

Capabilities of GemBox.Spreadsheet are really impressive. You can use it for reading XLS, XLSX and CSV files and writing to XLS, XLSX, CSV and HTML files.

GemBox.Spreadsheet has many functionalities that are not presented in this example. You can use: styles, hyperlinks, images, merged regions, named ranges, print options, view options, preservation of illustrations, shapes, charts, auto filter, data validation, conditional formatting, macros, pivot tables and more features that you can find at link below.