Reading and writing to Excel 2007 or Excel 2010 from C# - Part V: Full source for reading

Published on 16 March 2010 • Estimated reading time: 2 minutes

[Note: See the series index for a list of all parts in this series.]

A few people have struggled with getting all the bits of code scattered in this series to actually work. This isn’t only because they are scattered, but also because Part III did not display the code correctly and there was a bug in Part IV. I’ve gone back and fixed those issues, and to help further, here is the full code in one consolidated view (click read more if needed to see it):

using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Packaging;
using System.Linq;
using System.Xml;
using System.Xml.Linq;

namespace ReadFromExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Cell> parsedCells = new List<Cell>();
            string fileName = @"C:\Users\bbdnet0758\Desktop\Demo.xlsx";
            Package xlsxPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite);
            try
            {
                PackagePartCollection allParts = xlsxPackage.GetParts();

                PackagePart sharedStringsPart = (from part in allParts
                                                 where part.ContentType.Equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml")
                                                 select part).Single();

                XElement sharedStringsElement = XElement.Load(XmlReader.Create(sharedStringsPart.GetStream()));

                Dictionary<int, string> sharedStrings = new Dictionary<int, string>();
                ParseSharedStrings(sharedStringsElement, sharedStrings);

                XElement worksheetElement = GetWorksheet(1, allParts);

                IEnumerable<XElement> cells = from c in worksheetElement.Descendants(ExcelNamespaces.excelNamespace + "c")
                                              select c;

                foreach (XElement cell in cells)
                {
                    string cellPosition = cell.Attribute("r").Value;
                    int index = IndexOfNumber(cellPosition);
                    string column = cellPosition.Substring(0, index);
                    int row = Convert.ToInt32(cellPosition.Substring(index, cellPosition.Length - index));
                    int valueIndex = Convert.ToInt32(cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value);

                    parsedCells.Add(new Cell(column, row, sharedStrings[valueIndex]));
                }
            }
            finally
            {
                xlsxPackage.Close();
            }

            // The following is additional code not covered in the posts—just to show it works.
            foreach (Cell cell in parsedCells)
            {
                Console.WriteLine(cell);
            }
        }

        private static void ParseSharedStrings(XElement sharedStringsElement, Dictionary<int, string> sharedStrings)
        {
            IEnumerable<XElement> sharedStringsElements = from s in sharedStringsElement.Descendants(ExcelNamespaces.excelNamespace + "t")
                                                          select s;

            int counter = 0;
            foreach (XElement sharedString in sharedStringsElements)
            {
                sharedStrings.Add(counter, sharedString.Value);
                counter++;
            }
        }

        private static XElement GetWorksheet(int worksheetID, PackagePartCollection allParts)
        {
            PackagePart worksheetPart = (from part in allParts
                                         where part.Uri.OriginalString.Equals(String.Format("/xl/worksheets/sheet{0}.xml", worksheetID))
                                         select part).Single();

            return XElement.Load(XmlReader.Create(worksheetPart.GetStream()));
        }

        private static int IndexOfNumber(string value)
        {
            for (int counter = 0; counter < value.Length; counter++)
            {
                if (char.IsNumber(value[counter]))
                {
                    return counter;
                }
            }
            return 0;
        }
    }

    internal static class ExcelNamespaces
    {
        internal static XNamespace excelNamespace = XNamespace.Get("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
        internal static XNamespace excelRelationshipsNamespace = XNamespace.Get("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
    }

    public class Cell
    {
        public Cell(string column, int row, string data)
        {
            Column = column;
            Row = row;
            Data = data;
        }

        public override string ToString()
        {
            return string.Format("{0}:{1} - {2}", Row, Column, Data);
        }

        public string Column { get; set; }
        public int Row { get; set; }
        public string Data { get; set; }
    }
}