If you want to read a specific cell from a Microsoft Office Excel file (*.xml) with LINQ in C# you can use the following code:

Microsoft Office Excel 2003 file (“C:\BDATA\Cars.xml”) with a worksheet “Settings”:
image

Code to read the value “Leon”

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using System.Xml.Linq;

namespace UnitTest
{
    [TestFixture]
    public class TestCode
    {
        [Test]
        public void ReadExcelCellTest()
        {
            XDocument document = XDocument.Load(@"C:\BDATA\Cars.xml");
            XNamespace workbookNameSpace = @"urn:schemas-microsoft-com:office:spreadsheet";

            // Get worksheet
            var query = from w in document.Elements(workbookNameSpace + "Workbook").Elements(workbookNameSpace + "Worksheet")
                        where w.Attribute(workbookNameSpace + "Name").Value.Equals("Settings")
                        select w;
            List<XElement> foundWoksheets = query.ToList<XElement>();
            if (foundWoksheets.Count() <= 0) { throw new ApplicationException("Worksheet Settings could not be found"); }
            XElement worksheet = query.ToList<XElement>()[0];

            // Get the row for "Seat"
            query = from d in worksheet.Elements(workbookNameSpace + "Table").Elements(workbookNameSpace + "Row").Elements(workbookNameSpace + "Cell").Elements(workbookNameSpace + "Data")
                    where d.Value.Equals("Seat")
                    select d;
            List<XElement> foundData = query.ToList<XElement>();
            if (foundData.Count() <= 0) { throw new ApplicationException("Row 'Seat' could not be found"); }
            XElement row = query.ToList<XElement>()[0].Parent.Parent;

            // Get value cell of Etl_SPIImportLocation_ImportPath setting
            XElement cell = row.Elements().ToList<XElement>()[1];

            // Get the value "Leon"
            string cellValue = cell.Elements(workbookNameSpace + "Data").ToList<XElement>()[0].Value;

            Console.WriteLine(cellValue);
        }
    }
}

2 Comments

  1. Pingback: Knowledgebase » Blog Archive » Xml namespaces in a XDocument with LINQ in C#

  2. Hello,
    Regarding your’s article: How to read a Microsoft Office Excel 2003 xml file with LINQ in C# – I’ve got a problem to read more than two values from one row: e.g. if(cell[1] = AUDI and cell[2] =A3) then get value = cell[3].
    Can you help me with it?
    Thanks in advance

    Olaf

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.