Click here to Skip to main content
12,550,447 members (45,044 online)
Click here to Skip to main content
Add your own
alternative version


109 bookmarked

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

, 29 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Read rows and cells from an xlsx-file: quick, dirty, effective.


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);
            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="" count="72" uniqueCount="6">
    <t>Text A</t>
    <t>Text B</t>
public class sst
    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="">
<dimension ref="A1:F12"/>
  <sheetView workbookViewId="0"></sheetView>
<sheetFormatPr baseColWidth="10" defaultRowHeight="15"/>
  <row r="1">
    <c r="A1" t="s">
    <c r="B1" t="s">
    <c r="C1" t="s">
public class worksheet
    public Row[] Rows;

    public class worksheet
public class Row
    public Cell[] FilledCells;
public class Cell
    public string CellReference;
    public string tType = "";
    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) ...

public string CellReference
        return ColumnIndex.ToString();
        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)

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 =[Convert.ToInt32(_value)].t;
if (tType.Equals("str"))
    Text = _value;
    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!


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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

dietmar paul schoder
United Kingdom United Kingdom
I am a developer and convinced user of Visual Studio, C#, .net, WPF, MVVM, the project management standards of PMI and IPMA - and Microsoft Surface Pro 3. I live in Eastbourne, United Kingdom.

You may also be interested in...


Comments and Discussions

QuestionCan i R/W to excel using this ?? Pin
Member 1269891723-Aug-16 21:42
memberMember 1269891723-Aug-16 21:42 
BugIt does not work Pin
voquanghoa4-Jul-16 7:19
membervoquanghoa4-Jul-16 7:19 
GeneralRe: It does not work Pin
dietmar paul schoder4-Jul-16 8:20
professionaldietmar paul schoder4-Jul-16 8:20 
QuestionSelecting Worksheets Pin
branta.canadensis27-Jun-16 3:33
memberbranta.canadensis27-Jun-16 3:33 
AnswerRe: Selecting Worksheets Pin
dietmar paul schoder27-Jun-16 3:40
professionaldietmar paul schoder27-Jun-16 3:40 
QuestionCode skipped Pin
Member 1259965523-Jun-16 2:45
memberMember 1259965523-Jun-16 2:45 
AnswerRe: Code skipped Pin
dietmar paul schoder23-Jun-16 3:28
professionaldietmar paul schoder23-Jun-16 3:28 
QuestionHow could I read defined names or list of defined names in Name Manager? Pin
Member 1116901421-Jun-16 1:43
memberMember 1116901421-Jun-16 1:43 
AnswerRe: How could I read defined names or list of defined names in Name Manager? Pin
dietmar paul schoder21-Jun-16 3:03
professionaldietmar paul schoder21-Jun-16 3:03 
AnswerRe: How could I read defined names or list of defined names in Name Manager? Pin
Member 1116901422-Jun-16 0:11
memberMember 1116901422-Jun-16 0:11 
QuestionNull value returned on specific <si> elements. Pin
Member 120772587-Feb-16 7:56
memberMember 120772587-Feb-16 7:56 
AnswerRe: Null value returned on specific <si> elements. Pin
dietmar paul schoder7-Feb-16 12:06
professionaldietmar paul schoder7-Feb-16 12:06 
GeneralRe: Null value returned on specific <si> elements. Pin
ByBlener19-Oct-16 2:31
memberByBlener19-Oct-16 2:31 
Questionxls Extension problem Pin
Sonam Mehta26-Nov-15 1:45
memberSonam Mehta26-Nov-15 1:45 
AnswerRe: xls Extension problem Pin
dietmar paul schoder26-Nov-15 1:59
professionaldietmar paul schoder26-Nov-15 1:59 
QuestionHow to get details of merge cells ? And cell text is bold or not? Pin
Noman Sadiq26-Oct-15 10:57
memberNoman Sadiq26-Oct-15 10:57 
AnswerRe: How to get details of merge cells ? And cell text is bold or not? Pin
dietmar schoder26-Oct-15 12:26
memberdietmar schoder26-Oct-15 12:26 
QuestionSample is needed Pin
vivekthangaswamy22-Oct-15 13:13
professionalvivekthangaswamy22-Oct-15 13:13 
AnswerRe: Sample is needed Pin
vivekthangaswamy22-Oct-15 16:50
professionalvivekthangaswamy22-Oct-15 16:50 
AnswerRe: Sample is needed Pin
dietmar schoder22-Oct-15 22:41
memberdietmar schoder22-Oct-15 22:41 
SuggestionRe: Sample is needed Pin
vivekthangaswamy23-Oct-15 3:25
professionalvivekthangaswamy23-Oct-15 3:25 
GeneralRe: Sample is needed Pin
dietmar schoder23-Oct-15 7:37
memberdietmar schoder23-Oct-15 7:37 
GeneralRe: Sample is needed Pin
vivekthangaswamy26-Oct-15 11:32
professionalvivekthangaswamy26-Oct-15 11:32 
GeneralRe: Sample is needed Pin
dietmar schoder26-Oct-15 12:17
memberdietmar schoder26-Oct-15 12:17 
GeneralRe: Sample is needed Pin
vivekthangaswamy26-Oct-15 12:18
professionalvivekthangaswamy26-Oct-15 12:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161021.1 | Last Updated 29 Jul 2014
Article Copyright 2014 by dietmar paul schoder
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid