0

Basically, I'm trying to open an excel file and read the its xml. However when I run my program I get a run time saying "Root element is missing". I checked a number of solutions and none of them fix my issue. I've also checked that my file exists and can be accessed. Not sure what the issue is here if anyone could direct my in the right direction it would be great. Also I'm not sure if I should be using FileStream here.

My code is as follows:

string path = @"C:\Users\Craig\Documents\newTest.xlsx";
using (FileStream xml = File.Open(path, FileMode.Open, FileAccess.Read))
{
    // Encode the XML string in a UTF-8 byte array
    byte[] encodedString = Encoding.UTF8.GetBytes(xmlDoc.InnerXml);

    // Put the byte array into a stream and rewind it to the beginning
    MemoryStream ms = new MemoryStream(encodedString);
    ms.Flush();
    ms.Position = 0;

    // Build the XmlDocument from the MemorySteam of UTF-8 encoded bytes
    if (xml.Position > 0)
    {
        xml.Position = 0;
    }

    XDocument xDoc = XDocument.Load(ms);

    Console.WriteLine(xml);

}

Updates enter image description here

enter image description here

enter image description here

enter image description here

Dirk Vollmar
  • 172,527
  • 53
  • 255
  • 316
Craig Gallagher
  • 1,613
  • 6
  • 23
  • 52
  • 3
    Use OuterXml instead of InnerXml. See http://stackoverflow.com/questions/12578609/xmldocument-difference-between-innerxml-and-outerxml. – Ricardo Peres Nov 29 '16 at 11:08
  • Can you open "newTest.xlsx" with a bowser for example chrome? – huse.ckr Nov 29 '16 at 11:09
  • Ricardo is right this is totally what I was about to say. – Phiter Nov 29 '16 at 11:09
  • 1
    What is xmlDoc? Are you aware that xlsx files are a zip file, not one XML file? – Crowcoder Nov 29 '16 at 11:10
  • I think the problem is with out there in xml. Its is better check it with a browser. – huse.ckr Nov 29 '16 at 11:12
  • @RicardoPeres OuterXml made no difference. As Crowcoder said there are a number of xml files within the xlsx but I'm not sure how I should deal wit this – Craig Gallagher Nov 29 '16 at 11:13
  • @user3060520 I can open all the xml files in my browser – Craig Gallagher Nov 29 '16 at 11:14
  • upload the xml and share with us please. – huse.ckr Nov 29 '16 at 11:15
  • 2
    also have a look at: [Read an Excel file (.xls/.xlsx)](http://stackoverflow.com/questions/12996234/optimal-way-to-read-an-excel-file-xls-xlsx) – huse.ckr Nov 29 '16 at 11:17
  • @user3060520 I added a number of images of excel files but there is more not sure it you want to see them – Craig Gallagher Nov 29 '16 at 11:20
  • why are you loading the xml from a memory stream? – Daniel A. White Nov 29 '16 at 11:21
  • @DanielA.White because I was getting a `data at the root level is invalid line 1 position 1 excel file` error and I added a memory stream as apparently that's a good way to deal with it. – Craig Gallagher Nov 29 '16 at 11:24
  • 1
    @CraigGallagher: What is it you actually are trying to achieve? Can you explain what you want to do with the document? Your question looks like a classical [XY problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – Dirk Vollmar Nov 29 '16 at 11:26
  • @DirkVollmar Sorry for a vague question. Basically I want to scan through all the xml documents. Once I have done this I was going to use Regex to look for certain patterns. – Craig Gallagher Nov 29 '16 at 11:27
  • 1
    @CraigGallagher: xlsx (Excel spreadsheet format) is not plain XML. It is a zip package with a specific structure also containing XML parts with a certain semantics (defined by the OpenXML specification). What information are you interested in? – Dirk Vollmar Nov 29 '16 at 11:28
  • I thought I may have been able to access the xml within the xlsx file. Looks like I'm going about this the completely wrong way. I just need to scan through all the xml files then I can do the more complicated stuff later. – Craig Gallagher Nov 29 '16 at 11:31
  • It's certainly possible to access the XML inside xlsx files. But how you do that depends on what you are scanning for and what that "complicated stuff" is. – Dirk Vollmar Nov 29 '16 at 11:33
  • I want to scan through the xml files and then I want to add regex to find certain patterns and then remove those elements that the pattern is in – Craig Gallagher Nov 29 '16 at 11:35

0 Answers0