CODEWITHSUNDEEP
c#xmlexcelvisual-studio-2012
Jan
Jan

Reputation: 25

c# VS Express 2012 excel xml read and list

I'm beginning to program in C#. I can use Visual Studio Express 2012 for this purpose. I'm trying to create application that will import data from xml spreadsheet 2003 from specific column (but not specified number of entries in that column) and it will list text from each cell (all of them in that column). I have read few topics about it, like this one: http://social.msdn.microsoft.com/Forums/windowsapps/en-US/4fce4765-2d05-4a2b-8d0a-6219e87f3307/reading-excel-file-using-c-in-winrt-platform?forum=winappswithcsharp

but most of the answers are related with Visual Studio 2012 not the express version, thus I'm limited with libraries and extensions. Most of this solutions when I try to use them, don't work in my VS Express 2012 cause they are missing something.

This program is working for me and is returning value of one specific cell. How can I change it, so it will read every cell from that column, assign every value to a table (or maybe variable) so I can work with this content and maybe randomize order later?

namespace UnitTest
{
    public class TestCode
    {
        //ReadExcelCellTest
        public static void Main()
        {
            XDocument document = XDocument.Load(@"C:\Projekt2\File1.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("Sheet1")
                    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("StateID")
                select d;
        List<XElement> foundData = query.ToList<XElement>();
        if (foundData.Count() <= 0) { throw new ApplicationException("Row 'StateID' 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);
    }
}

}

Upvotes: 2

Views: 728

Answers (2)

Csaba Toth
Csaba Toth

Reputation: 10729

Handling Excel sheets is not as easy as one might expect. For example: cell content is often just a reference to the real value stored in a dictionary (that's what the List<Dictionary<string, string>> is for in the code in the forum topic you linked). Also, other whacky things can happen, like receiving unexpected NULL cells at the end of a row.

I don't know how low level you want to keep your code. If there's a possibility that the functionality will evolve, you better look for some libraries. Take a look at Microsoft's own Open XML SDK: Open XML SDK 2.5. That provides support for all XML based office formats. There are two downsides: it's 12MB+ assembly, and the other is that this one is still not as high level as I expected. But you get some concepts like rows and columns.

The other alternatives are some non Microsoft libraries. There are numerous ones on CodePlex and other open source repos. Watch out to select something which is active and updated. Take a look at the issue section of the project, you'll see that there are usually many. You can see how they are handled. Many projects focus on Excel only, which is probably what you need, and will be smaller than a general OpenXML solution.

You can get paid product. Finally I ended up using SmartXLS, because it turned out our company had a license.

Upvotes: 0

Mars Robertson
Mars Robertson

Reputation: 13233

I believe that any version of Visual Studio would allow you to use open-source libraries: http://closedxml.codeplex.com/

Link to doc: http://closedxml.codeplex.com/wikipage?title=Finding%20and%20extracting%20the%20data&referringTitle=Documentation

Reading Excel cell value should be much easier then.

How can I change it, so it will read every cell from that column, assign every value to a table (or maybe variable) so I can work with this content and maybe randomize order later?

I hope that closedxml will help you achieve this task.

Upvotes: 0

Related Questions