Displaying multiple XML files in a single excel file

[indupriya9]

I have a number of XML files in a directory with the same format. I would like to display these files in a readable format either in html or excel (preferable).

I tried importing in Excel 2003 using the standard Data -- XML -- Import menu. But with this I am having problems with importing multiple xml documents. The error that comes up is "No data was imported".

Can anyone suggest me a solution to display these XML files in a meaningful way?

Thanks in advance
ip

 

[indupriya9]

Hi again

I have this code in C# which I got off the web that exactly does what I want but this reads only one XML file.

If someone can suggest me some modifications to this code so that it can read all the XML files in a directory and append to the same excel file it would be so much helpful

Here is the code:

public void Page_Load(Object sender, EventArgs E) {
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("11.xml"));
        XmlDataDocument xdd = new XmlDataDocument(ds);
        XslTransform  xt = new XslTransform();
        xt.Load(Server.MapPath("Style.xsl"));
        xt.Transform(xdd, null, Response.OutputStream);
        Response.End();

Thanks in advance

 

[jmeckley]

if all the xml documents have the same format(nodes) then read each xml file into an xmlreader/xmldocument. then have the dataset read this xml object and continue as before.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[indupriya9]

Hi Jason

Thanks for your reply. Actually I am new to .net Can you give me an example of what you mean by read each xml file into xmlreader?

ip

 

[jmeckley]

public string CombineXMLDocuments(IEnumerable<string> files)
{
   XmlReader reader;
   XmlTextWriter writer = new XmlTextWriter(new StringWriter());
   writer.WriteStartDocument();
   string xlst = "type='text/xsl' href='Style.xsl'";
   writer.WriteProcessingInstruction("xml-stylesheet", xlst);
   writer.WriteStartElement("Files");

   foreach (string file in files)
   {
      using (reader = new XmlTextReader(file))
      {
         while (reader.Read())
         {
            if (reader.NodeType == NodeType.Element)
            {
               writer.Write(reader);
            }
         }
      }
   }
   writer.WriteEndElement("Files");
   writer.WriteEndDocument();

   writer.Flush();
   string toReturn = writer.ToString();
   writer.Dispose();

   return toReturn;
}

string[] files = new string[] {
   "file1.xml",
   "file2.xml",
   "file3.xml",
};

Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
Response.Write(CombineXMLDocuments(files));
Response.End();

more info on XmlReader and XmlTextWriter. You may need to tweak the while(reader.Read()) loop to omit root nodes and xml headers.

the basics of what is happening:
convert this

<File1>
   <Node>
      <Child Attribute="1" />
   </Node>
   <Node>
      <Child Attribute="2" />
   </Node>
</File1>


<File2>
   <Node>
      <Child Attribute="3" />
   </Node>
   <Node>
      <Child Attribute="4" />
   </Node>
</File2>

<File3>
   <Node>
      <Child Attribute="5" />
   </Node>
   <Node>
      <Child Attribute="6" />
   </Node>
</File3>

to

<transform instructions>
<Files>
   <Node>
      <Child Attribute="1" />
   </Node>
   <Node>
      <Child Attribute="2" />
   </Node>
   <Node>
      <Child Attribute="3" />
   </Node>
   <Node>
      <Child Attribute="4" />
   </Node>
   <Node>
      <Child Attribute="5" />
   </Node>
   <Node>
      <Child Attribute="6" />
   </Node>
</Files>

this code isn't perfect by any means, but it should get you started.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[indupriya9]

Thank you very much for this. This has given me a real head start. I will play around with this. I am used to asp but am new to .net. A quick look at your code gives me some confidence.

I will get back to you after trying this

Thanks once again
ip