11 January, 2010
2 Comments
0 categories
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”:
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); } } }
Tags: C#
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