none
how to create multiple sheets in excel workbook using openxml + C# .net

    Question

  • Hello every one..

    Following code i'm using to create an excel work book, but the oroblem for me is i'm getting 2 xmls in the work sheet folder but 2nd xml is overriding the first xml and displaying. I want to create multiple sheets inside 1 work book and put the data comming in the different xmls. if any body has worked on this,please help me out.

     

    private

     

    void button1_Click(object sender, EventArgs

    e)

    {

     

    XmlDocument xmlobj = new XmlDocument

    ();

    xmlobj.Load(

    @"C:\Inetpub\wwwroot\Input.xml"

    );

     

    //xmlobj.LoadXml(richTextBox1.Text);

     

    XslCompiledTransform xXslt = new XslCompiledTransform

    ();

    xXslt.Load(

    @"C:\Documents and Settings\veeresh\Desktop\Excel - import\Excel Import - Pharma\demoxsl.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:\output.xml"

    );

     

    StreamReader sr = File.OpenText(@"c:\output.xml"

    );

     

    string

    strSheetData = sr.ReadToEnd();

     

    XmlDocument xmlobj1 = new XmlDocument

    ();

    xmlobj1.Load(

    @"C:\Documents and Settings\veeresh\Desktop\XMLFile2.xml"

    );

     

    XslCompiledTransform xXslt1 = new XslCompiledTransform

    ();

    xXslt1.Load(

    @"C:\Documents and Settings\veeresh\Desktop\Excel - import\Excel Import - Pharma\demoxsl.xslt"

    );

     

    StringWriter sw1 = new StringWriter

    ();

    xXslt1.Transform(xmlobj1,

    null

    , sw1);

     

    //richTextBox2.Text = sw.ToString();

    sw1.Close();

     

    XmlDocument Xdoc1 = new XmlDocument

    ();

    Xdoc1.LoadXml(sw1.ToString());

    Xdoc1.Save(

    @"c:\output1.xml"

    );

     

    StreamReader sr1 = File.OpenText(@"c:\output1.xml"

    );

     

    string

    strSheetData1 = sr1.ReadToEnd();

     

    //SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("c:\\output.xlsx", SpreadsheetDocumentType.Workbook);

     

     

     

     

    using (SpreadsheetDocument doc = SpreadsheetDocument.Create("c:\\output.xlsx", DocumentFormat.OpenXml.SpreadsheetDocumentType

    .Workbook))

    {

     

    WorkbookPart

    workbook = doc.AddWorkbookPart();

     

    WorksheetPart sheet = workbook.AddNewPart<WorksheetPart

    >();

     

    string sheetId = workbook.GetIdOfPart(sheet);

    // Create a blank XLSX file

     

    string XML = @"<?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><sheet name=""{1}"" sheetId=""1"" r:id=""{0}"" /></sheets></workbook>"

    ;

    XML =

    string.Format(XML, sheetId, "Sheet"

    );

     

    this.AddPartXml(workbook, XML);

    // Insert our sheetData element to the sheet1.xml

    XML =

    @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" >{0}</worksheet>"

    ;

    XML =

    string

    .Format(XML, strSheetData);

     

    this

    .AddPartXml(sheet, XML);

     

    //workbook1 = doc.AddWorkbookPart();

     

    WorksheetPart sheet1 = workbook.AddNewPart<WorksheetPart

    >();

     

    string sheetId1 = workbook.GetIdOfPart(sheet1);

    // Create a blank XLSX file

     

    string XML1 = @"<?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><sheet name=""{1}"" sheetId=""1"" r:id=""{0}"" /></sheets></workbook>"

    ;

    XML1 =

    string.Format(XML1, sheetId1, "Sheet1"

    );

     

    this.AddPartXml(workbook, XML1);

    // Insert our sheetData element to the sheet1.xml

    XML1 =

    @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" >{0}</worksheet>"

    ;

    XML1 =

    string

    .Format(XML1, strSheetData1);

     

    this

    .AddPartXml(sheet1, XML1);

     

    doc.Close();

    }

    }

    Tuesday, June 07, 2011 8:20 AM

All replies

  • Hi Veeresh,

    Please take a look at this article about insert a new worksheet into spreadsheet document:

    http://msdn.microsoft.com/en-us/library/cc881781.aspx

    // Given a document name, inserts a new worksheet.
    public static void InsertWorksheet(string docName)
    {
      // Open the document for editing.
      using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
      {
        // Add a blank WorksheetPart.
        WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
        newWorksheetPart.Worksheet = new Worksheet(new SheetData());
    
        Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
        string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
    
        // Get a unique ID for the new worksheet.
        uint sheetId = 1;
        if (sheets.Elements<Sheet>().Count() > 0)
        {
          sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }
    
        // Give the new worksheet a name.
        string sheetName = "Sheet" + sheetId;
    
        // Append the new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
        sheets.Append(sheet);
      }
    }

    Hope the code snippet can help you to create mutiple sheets in Excel.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 10, 2011 9:22 AM
  • Bruce Song,

    Thanks for the reply

    i had over come with my above problem, but the problem i'm facing is.. my code is creating an xslsx file for me in the specified path with all the xml files in the respective folder structure.

    C:\Final.xlsx\xl\workbook.xml

    <?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>
    
     <fileVersion appName= "xl" lastEdited= "5" lowestEdited= "5" rupBuild="9302" />
    
     <workbookPr defaultThemeVersion= "124226" />
    
     <bookViews>
    
      <workbookView xWindow= "120" yWindow= "45" windowWidth= "15195" windowHeight= "7680" activeTab= "1" />
    
     </bookViews>
    
     <sheet name= "sheet1" sheetId="0" r:id="R66c9d9928a0f4fe4" />
    
     <sheet name= "sheet2" sheetId="1" r:id="Recdf2ee23f524b65" />
    
     </sheets>
    
    </workbook>
    
    


    C:\Final.xlsx\xl\worksheets\sheet.xml

     

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"> <sheetData> <row r="1"> <c r="A1" t="inlineStr"> <is> <t>Well Index</t> </is> </c> <c r="B1" t="inlineStr"> <is> <t>Field</t> </is> </c> <c r="C1" t="inlineStr"> <is> <t>Well Name</t> </is> </c> <c r="D1" t="inlineStr"> <is> <t>Mid Stream</t> </is> </c> <c r="E1" t="inlineStr"> <is> <t>Choose Code</t> </is> </c> <c r="F1" t="inlineStr"> <is> <t>Rig Name</t> </is> </c> <c r="G1" t="inlineStr"> <is> <t>Projected TD</t> </is> </c> <c r="H1" t="inlineStr"> <is> <t>WI</t> </is> </c> <c r="I1" t="inlineStr"> <is> <t>Production NRI</t> </is> </c> <c r="J1" t="inlineStr"> <is> <t>Capex Start Date</t> </is> </c> </row> <row r="2"> <c r="A2" t="inlineStr"> <is> <t>111</t> </is> </c> <c r="B2" t="inlineStr"> <is> <t>Zafiro</t> </is> </c> <c r="C2" t="inlineStr"> <is> <t>RL 02</t> </is> </c> <c r="D2" t="inlineStr"> <is> <t>PC.170</t> </is> </c> <c r="E2" t="inlineStr"> <is> <t>1</t> </is> </c> <c r="F2" t="inlineStr"> <is> <t>12345</t> </is> </c> <c r="G2" t="inlineStr"> <is> <t>0.35</t> </is> </c> <c r="H2" t="inlineStr"> <is> <t>0.28</t> </is> </c> <c r="I2" t="inlineStr"> <is> <t>jan - 09</t> </is> </c> </row> > </sheetData> </worksheet>
    
    

    C:\Final.xlsx\xl\worksheets\sheet2.xml

     This is also a same xml as above,  

     

     

    i'm using this follwing code to achive this,

    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(@"C:\Inetpub\wwwroot\Input.xml");
    
        xXslt.Load(@"C:\Documents and Settings\veeresh\Desktop\Excel - import\Excel Import - Pharma\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("c:\\Final.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=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" 
    
             xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships""><sheets>
    
             <fileVersion appName= xl lastEdited= 5 lowestEdited= 5 rupBuild=9302 />
    
             <workbookPr defaultThemeVersion= 124226 />
    
             <bookViews><workbookView xWindow= 120 yWindow= 45 windowWidth= 15195 windowHeight= 7680 activeTab= 1 />
    
             </bookViews>";
    
         for (int j = 0; j < DN.Count; j++)
    
         {
    
          WorksheetPart sheet;
    
          sheet = workbook.AddNewPart<WorksheetPart>();
    
          string sheetId = workbook.GetIdOfPart(sheet);
    
          XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" 
    
            xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships"" 
    
            xmlns:mc=""http://schemas.openxmlformats.org/markup-compatibility/2006"" 
    
            mc:Ignorable=""x14ac""
    
            xmlns:x14ac=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"">";
    
          XML += FinalXML[j].ToString() + "</worksheet>";
    
          this.AddPartXml(sheet, XML);
    
          WorbookXML += "<sheet name= sheet" + (j + 1).ToString() + " sheetId=" + j.ToString() + " r:id=" + sheetId.ToString() + " />";
    
         }
    
         WorbookXML += "</sheets></workbook>";
    
         this.AddPartXml(workbook, WorbookXML);
    
         doc.Close();
    
        }
    
       }
    
    
    
    
    
       
    
    
    
       protected void AddPartXml(OpenXmlPart part, string xml) 
    
       {
    
        using (Stream stream = part.GetStream()) 
    
    
    
         { byte[] buffer = (new UTF8Encoding()).GetBytes(xml); 
    
          stream.Write(buffer, 0, buffer.Length); 
    
         } 
    
       }
    
    


    Please help me out in this case.

    Thank in advance,

    Veeresh Nannivala.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     


    veeresh Software Engineer PointCross.com India Pvt Ltd
    Monday, June 13, 2011 1:39 PM