C# How To Read .xlsx Excel File With 3 Lines of Code

• Download ExcelDLL.zip - 6 KB
• Download Excel.zip - 12.3 KB

Introduction

Our customers often have to import data from very simple Excel *.xslx-files into our software product: with some relevant rows and cells in rather primitive worksheets of an Excel workbook, and that's it. But we do not want to use large DLL's or third party software for that. Therefore we produced a small solution for our needs. It could be useful for you, too: 

Using the code

Download the "Excel.dll" (12 kByte, you need .net 4.5!) and add it as a reference to your project. Or adapt the compact source code before - with only the relevant classes "Workbook", "Worksheet", "Row" and "Cell" and 45 C# code lines doing important work in total. Then read the worksheets, rows and cells from any Excel *.xlsx-file in your program like so:

foreach (var worksheet in Workbook.Worksheets(@"C:\ExcelFile.xlsx")
    foreach (var row in worksheet.Rows)
        foreach (var cell in row.Cells)
            // if (cell != null) // Do something with the cells

Here you iterate through the worksheets, the rows (and the cells of each row) of the Excel file within three lines of code.

Points of Interest

This article (written by M I developer) describes all the theoretical background, if you are interested in it. We based our solution on the integrated ZIP-library in .net 4.5 and the standard XML-serializer of .net.

If you want to adapt our solution to your needs: edit the simple source code for the Excel.dll. This is how it works:

Maybe you did not know that xlsx-files are ZIP-files. And the text strings of the Excel cells of all worksheets per workbook are always stored in a file named "xl/sharedStrings.xml", while the worksheets are called "xl/worksheets/sheet[1...n].xml".

So we have to unzip and deserialize the relevant XML files in the Excel xlsx-file:

using System.IO.Compression;

public static IEnumerable<worksheet> Worksheets(string ExcelFileName)
{
    worksheet ws;

    using (ZipArchive zipArchive = ZipFile.Open(ExcelFileName, ZipArchiveMode.Read))
    {
        SharedStrings = DeserializedZipEntry<sst>(GetZipArchiveEntry(zipArchive, @"xl/sharedStrings.xml"));
        foreach (var worksheetEntry in (WorkSheetFileNames(zipArchive)).OrderBy(x => x.FullName))
        {
            ws = DeserializedZipEntry<worksheet>(worksheetEntry);
            ws.ExpandRows();
            yield return ws;
        }
    }
}

As you see, we also have to find all worksheets of the workbook at the beginning. We filter the ZIP-archive entries for that:

private static IEnumerable<ziparchiveentry> WorkSheetFileNames(ZipArchive ZipArchive)
{
    foreach (var zipEntry in ZipArchive.Entries)
        if (zipEntry.FullName.StartsWith("xl/worksheets/sheet"))
            yield return zipEntry;
}

For deserialization of each XML formatted ZIP-entry (see also this article written by Md. Rashim uddin) we use this generic method:

private static T DeserializedZipEntry<T>(ZipArchiveEntry ZipArchiveEntry)
{
    using (Stream stream = ZipArchiveEntry.Open())
        return (T)new XmlSerializer(typeof(T)).Deserialize(XmlReader.Create(stream));
}

Therefore the XML-structures have to be reflected in our classes. Here you see the "sst"-class and the "SharedString"-class for the XML in the "shared strings table":

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="72" uniqueCount="6">
  <si>
    <t>Text A</t>
  </si>
  <si>
    <t>Text B</t>
  </si>
</sst>

public class sst
{
    [XmlElement("si")]
    public SharedString[] si;

    public sst()
    {
    }
}

public class SharedString
{
    public string t;
}

The same strategy we also use for the "worksheet" -XML-file in the ZIP-file. There we focus on the XML-elements and -attributes "row", "c", "v", "r" and "t". All the work is done again by the XmlSerializer:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dimension ref="A1:F12"/>
<sheetViews>
  <sheetView workbookViewId="0"></sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="10" defaultRowHeight="15"/>
<sheetData>
  <row r="1">
    <c r="A1" t="s">
      <v>0</v>
    </c>
    <c r="B1" t="s">
      <v>1</v>
    </c>
    <c r="C1" t="s">
      <v>2</v>
    </c>
  </row>
</sheetData>
</worksheet>

public class worksheet
{
    [XmlArray("sheetData")]
    [XmlArrayItem("row")]
    public Row[] Rows;

    public class worksheet
    {
    }
}
public class Row
{
    [XmlElement("c")]
    public Cell[] FilledCells;
}
public class Cell
{
    [XmlAttribute("r")]
    public string CellReference;
    [XmlAttribute("t")]
    public string tType = "";
    [XmlElement("v")]
    public string Value;
}

Of course we have to do a little bit in order to convert the usual Excel cell references like "A1", "B1" and so on to column indices. That is done via the setter of "CellReference" in the "Cell"-class (here we also derive the maximum column index for the whole worksheet) ...

[XmlAttribute("r")]
public string CellReference
{
    get
    {
        return ColumnIndex.ToString();
    }
    set
    {
        ColumnIndex = worksheet.GetColumnIndex(value);
        if (ColumnIndex > worksheet.MaxColumnIndex)
            worksheet.MaxColumnIndex = ColumnIndex;
    }
}

... and a small method named "GetColumnIndex()":

private int GetColumnIndex(string CellReference)
{
    string colLetter = new Regex("[A-Za-z]+").Match(CellReference).Value.ToUpper();
    int colIndex = 0;

    for (int i = 0; i < colLetter.Length; i++)
    {
        colIndex *= 26;
        colIndex += (colLetter[i] - 'A' + 1);
    }
    return colIndex - 1;
}

The last challenge has to do with the fact, that the Excel file does not contain empty Excel cells. So the tiny methods "ExpandRows()" and "ExpandCells()" handle that problem:

public void ExpandRows()
{
    foreach (var row in Rows)
        row.ExpandCells(NumberOfColumns);
}

public void ExpandCells(int NumberOfColumns)
{
    Cells = new Cell[NumberOfColumns];
    foreach (var cell in FilledCells)
        Cells[cell.ColumnIndex] = cell;
    FilledCells = null;
}

In the end we have an array of all rows and an array of all cells for each row representing all columns of the specific Excel worksheet. Empty cells are null in the array, but the ColumnIndex of each cell in "Row.Cells[]" corresponds with the actual Excel column of each cell.

Unfortunately the XML format is not very clear about how to interpret the value of the Excel cells. We tried to do it like so, but any hint for improvement would be appreciated:

if (tType.Equals("s"))
{
    Text = Workbook.SharedStrings.si[Convert.ToInt32(_value)].t;
    return;
}
if (tType.Equals("str"))
{
    Text = _value;
    return;
}
try
{
    Amount = Convert.ToDouble(_value, CultureInfo.InvariantCulture);
    Text = Amount.ToString("#,##0.##");
    IsAmount = true;
}
catch (Exception ex)
{
    Amount = 0;
    Text = String.Format("Cell Value '{0}': {1}", _value, ex.Message);
}

Besides, when you know that an Excel cell contains a date as its value, you can use this method for conversion:

public static DateTime DateFromExcelFormat(string ExcelCellValue)
{
    return DateTime.FromOADate(Convert.ToDouble(ExcelCellValue));
}

Let me know how the total Excel.DLL works in your environment - and have fun with it!

History

26.7.2014 - Posted initially.

26.7.2014 - Files uploaded here.

26.7.2014 - Explanations added, formatting improved.

28.7.2014 - More explanation added. Deserialization slightly improved.

29.7.2014 - Date conversion from Excel format to DateTime via DateTime.FromOADate()

29.7.2014 - Essence of class "worksheet" added.

31.7.2014 - Comments added in the source code, XML-documentation added to the dll

31.7.2014 - The program now reads all worksheets from a workbook, not only the first one