Export xml data to Excel using OpenXML

Question

The file is downloading an xlsx file but when i tried to open the file it is saying file is corrupted. Here is the code i'm trying to use please let me know if any changes has to be done for the following.

private void button1_Click(object sender, EventArgs e)
{
    ArrayList DataNode = new ArrayList();
    XmlDocument xmlobj = new XmlDocument();
    ArrayList FinalXML = new ArrayList();
    XslCompiledTransform xXslt = new XslCompiledTransform();
    xmlobj.Load(@"D:\ExcelImport\Input.xml");
    xXslt.Load(@"D:\ExcelImport\demoxsl.xslt");
    XmlNodeList DN ;
    DN = xmlobj.DocumentElement.GetElementsByTagName("Data");
    for (int i = 0; i < DN.Count; i++)
    {
        DataNode.Add("<ShaleDataExport><Data Flag = '" + i + "' >" + DN.Item(i).InnerXml + "</Data></ShaleDataExport>");    
    }
    string ShaleDataExportXML;
    int k = 0 ;
    while (k < DN.Count)
    {
        ShaleDataExportXML = DataNode[k].ToString();
        XmlDocument xml =  new XmlDocument();
        xml.LoadXml(ShaleDataExportXML);
        StringWriter sw = new StringWriter();  
        xXslt.Transform(xml, null, sw);
        FinalXML.Add(sw);
        sw.Close();
        k++;
    }
    using (SpreadsheetDocument doc = SpreadsheetDocument.Create(@"D:\ExcelImport\OutPut\OutPut.xlsx", DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbook = doc.AddWorkbookPart();
        string XML;
        string WorbookXML;
        WorbookXML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><workbook xmlns=""schemas.openxmlformats.org/.../main"" xmlns:r=""schemas.openxmlformats.org/.../relationships""><sheets>";
        for (int j = 0; j < DN.Count; j++)
        {
            WorksheetPart[] sheet = new WorksheetPart[DN.Count];
            sheet[j] = workbook.AddNewPart<WorksheetPart>();
            string sheetId = workbook.GetIdOfPart(sheet[j]);
            XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><worksheet xmlns=""schemas.openxmlformats.org/.../main"" >";
            XML += FinalXML[j].ToString() + "</worksheet>";
            string SheetXML = XML.ToString();
            XmlDocument SXML = new XmlDocument();
            SXML.LoadXml(SheetXML);
            byte[] byteArray = Encoding.ASCII.GetBytes(SXML.OuterXml);
            MemoryStream stream = new MemoryStream(byteArray);
            StreamReader reader = new StreamReader(stream);
            string text = reader.ReadToEnd();
            WorbookXML += "<sheet name="+ AddPartXml(sheet[j], text) + " sheetId=" + j.ToString() + "  r:id=" + sheetId.ToString() + " />";
        }
        WorbookXML += "</sheets></workbook>";
        AddPartXml(workbook, WorbookXML);
        doc.Close();
    }
}
public string  AddPartXml(OpenXmlPart part, string xml)
{
    Uri uri = part.Uri;
    String[] sheetNames = uri.OriginalString.Split('/');
    string sheetName = sheetNames[sheetNames.Length - 1].Split('.')[0];
    using (Stream stream = part.GetStream())
    {
        byte[] buffer = (new UTF8Encoding()).GetBytes(xml);
        stream.Write(buffer, 0, buffer.Length);
    }
    return sheetName;
}

Thanks in advance

Vineet Mangal

Are you able to step through the code? Are you able to download the file manually with no problem? — JimmyPena, Jul 10 '12 at 16:28
Hi Jimmy i am able to trace the code i found that in workbookPart.workSheet part having some problwm when trying to add multiple sheets in workbook.sheets. Can you tell me what to do as I am able to download the file for one sheet without any problem. — vineet, Jul 11 '12 at 05:54

score 0 · Accepted Answer · answered Jul 12 '12 at 06:04

        private void button1_Click(object sender, EventArgs e)
        {
            XmlDocument xmlobj = new XmlDocument();
            xmlobj.Load(@"C:\Excel Import\\Input.xml");
            XslCompiledTransform xXslt = new XslCompiledTransform();
            xXslt.Load(@"C:\ExportToexcel\Data.xslt");

            StringWriter sw = new StringWriter();

            xXslt.Transform(xmlobj, null, sw);
            richTextBox2.Text = sw.ToString();
            sw.Close();
            XmlDocument Xdoc = new XmlDocument();
            Xdoc.LoadXml(sw.ToString());
            Xdoc.Save(@"c:\temp\output.xml");
            StreamReader sr = File.OpenText(@"c:\temp\output.xml");

            string strSheetData = sr.ReadToEnd();
            ArrayList DataNode = new ArrayList();
            ArrayList FinalXML = new ArrayList();
            XmlNodeList DN;
            DN = xmlobj.DocumentElement.GetElementsByTagName("Data");
            for (int i = 0; i < DN.Count; i++)
            {
                DataNode.Add("<ShaleDataExport><Data Flag = '" + i + "' >" + DN.Item(i).InnerXml + "</Data></ShaleDataExport>");

            }
            string ShaleDataExportXML;
            int k = 0;
            while (k < DN.Count)
            {
                ShaleDataExportXML = DataNode[k].ToString();
                XmlDocument xml = new XmlDocument();
                xml.LoadXml(ShaleDataExportXML);
                StringWriter sw1 = new StringWriter();
                xXslt.Transform(xml, null, sw1);
                FinalXML.Add(sw1);
                sw.Close();
                k++;
            }

            using (SpreadsheetDocument doc = SpreadsheetDocument.Create(@"c:\\temp\\output.xlsx", DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbook1 = doc.AddWorkbookPart();
                string XML;
                string WorbookXML;
                WorbookXML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><workbook xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships""><sheets>";
                for (int j = 0; j < DN.Count; j++)
                {
                    WorksheetPart[] sheet = new WorksheetPart[DN.Count];
                    sheet[j] = workbook1.AddNewPart<WorksheetPart>();
                    string sheetId = workbook1.GetIdOfPart(sheet[j]);
                    XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" >";
                    XML += FinalXML[j].ToString() + "</worksheet>";
                    string SheetXML = XML.ToString();
                    XmlDocument SXML = new XmlDocument();
                    SXML.LoadXml(SheetXML);
                    byte[] byteArray = Encoding.ASCII.GetBytes(SXML.OuterXml);
                    MemoryStream stream = new MemoryStream(byteArray);
                    StreamReader reader = new StreamReader(stream);
                    string text = reader.ReadToEnd();
                    **WorbookXML += "<sheet name=" + "\"sheet" + (j + 1).ToString() + "\" " + " sheetId=\"" + (j + 1).ToString() + "\"  r:id=\"" + sheetId.ToString() + "\" />";
                    AddPartXml(sheet[j], text);**
                }
                WorbookXML += "</sheets></workbook>";
                AddPartXml(workbook1, WorbookXML);
                doc.Close();
            }

        }

        public void AddPartXml(OpenXmlPart part, string xml)
        {
            **using (Stream stream = part.GetStream())
            {
                byte[] buffer = (new UTF8Encoding()).GetBytes(xml);
                stream.Write(buffer, 0, buffer.Length);
            }**
        }

@JimmyPena I got my fault and corrected the code.I highlighted the changes i made. Th problem was with the escaping the string when mentioning the sheet name in worksheetpart. — vineet, Jul 12 '12 at 06:07
OK, don't forget to accept your answer so others can see that your problem was solved. — JimmyPena, Jul 13 '12 at 20:07
@JimmyPena where is the option for the accepting the answer as u mentioned to accept the answer. — vineet, Jul 24 '12 at 07:25
Underneath the up/down arrows next to the answer there should be a checkmark. Click it to accept your answer. See http://meta.stackexchange.com/q/5234/147645 if you need more assistance. — JimmyPena, Jul 24 '12 at 11:19
@JimmyPena When i tried to write a big transformed xml data to excel using openxml..its given me a big exception..this is my code::public void AddPartXml(OpenXmlPart part, string xml) { using (Stream stream = part.GetStream()) { byte[] buffer = (new UTF8Encoding()).GetBytes(xml); stream.Write(buffer, 0, buffer.Length); stream.Dispose(); } } Can u guide me how i can write the xml in chunk to excel. — vineet, Jul 26 '12 at 06:39

Export xml data to Excel using OpenXML

1 Answers1