Read Excel file in C# and VB.NET

GemBox.Spreadsheet can read many Excel file formats (including XLS, XLSX, CSV and ODS) in the same manner. Specifically, it uses a Load method to read from a physical Excel file or the Excel file's stream in C# and VB.NET.

The following example demonstrates how to read all ExcelCells, their Value and ValueTypes, in ExcelFile object.

Note that when you iterate through all read cells in an ExcelRow, you should use AllocatedCells collection to prevent an unnecessary allocation of ExcelCell objects.

Screenshot

Reading Excel Screenshot

See the full code below, use RUN EXAMPLE to execute.

Download input file
Upload your file (Drag files here) 
1using System;
2using System.Collections.Generic;
3using System.Drawing;
4using System.Text;
5using GemBox.Spreadsheet;
6
7class Sample
8{
9    [STAThread]
10    static void Main(string[] args)
11    {
12        // If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
14
15        ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");
16
17        StringBuilder sb = new StringBuilder();
18
19        // Iterate through all worksheets in an Excel workbook.
20        foreach (ExcelWorksheet sheet in ef.Worksheets)
21        {
22            sb.AppendLine();
23            sb.AppendFormat("{0} {1} {0}", new string('-', 25), sheet.Name);
24
25            // Iterate through all rows in an Excel worksheet.
26            foreach (ExcelRow row in sheet.Rows)
27            {
28                sb.AppendLine();
29
30                // Iterate through all allocated cells in an Excel row.
31                foreach (ExcelCell cell in row.AllocatedCells)
32                {
33                    if (cell.ValueType != CellValueType.Null)
34                        sb.Append(string.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25));
35                    else
36                        sb.Append(new string(' ', 25));
37                }
38            }
39        }
40
41        Console.WriteLine(sb.ToString());
42    }
43}

1Imports System.Text
2Imports System.Drawing
3Imports GemBox.Spreadsheet
4
5Module Samples
6
7    Sub Main()
8
9        ' If using Professional version, put your serial key below.
10        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
11
12        Dim ef As ExcelFile = ExcelFile.Load("SimpleTemplate.xlsx")
13
14        Dim sb As New StringBuilder()
15
16        ' Iterate through all worksheets in an Excel workbook.
17        For Each sheet As ExcelWorksheet In ef.Worksheets
18            sb.AppendLine()
19            sb.AppendFormat("{0} {1} {0}", New String("-"c, 25), sheet.Name)
20
21            ' Iterate through all rows in an Excel worksheet.
22            For Each row As ExcelRow In sheet.Rows
23                sb.AppendLine()
24
25                ' Iterate through all allocated cells in an Excel row.
26                For Each cell As ExcelCell In row.AllocatedCells
27                    If cell.ValueType <> CellValueType.Null Then
28                        sb.Append(String.Format("{0} [{1}]", cell.Value, cell.ValueType).PadRight(25))
29                    Else
30                        sb.Append(New String(" "c, 25))
31                    End If
32                Next
33            Next
34        Next
35
36        Console.WriteLine(sb.ToString())
37
38    End Sub
39
40End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.