none
How to add/manipulate multiple worksheets in workbook using DocumentFormat RRS feed

  • Question

  • I can create a worksheet in a workbook, add data, and save it, but can't seem to figure out how to make it work for multiple worksheets. I'm using code similar to the link below, but I think I have issues in referencing new sheets after the first. http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx I think the areas I have issues with hvae to do with Worksheet.First() and/or WorksheetParts.First(). spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild( new Cell() { CellValue = new CellValue("101") }); spreadSheet.WorkbookPart.Workbook.GetFirstChild().AppendChild(new Sheet() { Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()), ... How can I make adjustments for each new sheet instance as it is created & saved? TIA
    Wednesday, February 16, 2011 5:07 PM

Answers

  • Hi Bantar,

    Thank you for posting and we are glad to help with you.

    After reading your post, I knew your issue that you want to manipulate mutiple worksheets via Open XML. I recommend you a good tool named Open XML SDK 2.0 Productivity Tool ,which can reflect the code from the Excel file. You can download from this website:http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en .

    I reflected the code by using the tool and generated mutiple worksheets via the code, and it can be modified according the requirements.  Below is my code snippet which can generate the workbook and insert three worksheets:

    using System;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;

    namespace OpenXmlCreateExcelSheets
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"), SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
                    GeneratedClass gcObj = new GeneratedClass();
                    gcObj.CreateWorkbookPart(workbookpart);
                    workbookpart.Workbook.Save();
                }
            }
        }
    }

    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
    using A = DocumentFormat.OpenXml.Drawing;

    namespace OpenXmlCreateExcelSheets
    {
        class GeneratedClass
        {
            // Adds child parts and generates content of the specified part.
            public void CreateWorkbookPart(WorkbookPart part)
            {
                WorksheetPart worksheetPart1 = part.AddNewPart<WorksheetPart>("rId3");
                GenerateWorksheetPart1Content(worksheetPart1);

                WorksheetPart worksheetPart2 = part.AddNewPart<WorksheetPart>("rId2");
                GenerateWorksheetPart2Content(worksheetPart2);

                WorksheetPart worksheetPart3 = part.AddNewPart<WorksheetPart>("rId1");
                GenerateWorksheetPart3Content(worksheetPart3);

                //WorkbookStylesPart workbookStylesPart1 = part.AddNewPart<WorkbookStylesPart>("rId5");
                //GenerateWorkbookStylesPart1Content(workbookStylesPart1);

                //ThemePart themePart1 = part.AddNewPart<ThemePart>("rId4");
                //GenerateThemePart1Content(themePart1);

                GeneratePartContent(part);

            }

            // Generates content of worksheetPart1.
            private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
            {
                Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                worksheet1.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                worksheet1.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                worksheet1.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };

                SheetViews sheetViews1 = new SheetViews();
                SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };

                sheetViews1.Append(sheetView1);
                SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

                SheetData sheetData1 = new SheetData();

                Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };

                Cell cell1 = new Cell() { CellReference = "A1" };
                CellValue cellValue1 = new CellValue();
                cellValue1.Text = "103";

                cell1.Append(cellValue1);

                row1.Append(cell1);

                sheetData1.Append(row1);
                PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

                worksheet1.Append(sheetDimension1);
                worksheet1.Append(sheetViews1);
                worksheet1.Append(sheetFormatProperties1);
                worksheet1.Append(sheetData1);
                worksheet1.Append(pageMargins1);

                worksheetPart1.Worksheet = worksheet1;
            }

            // Generates content of worksheetPart2.
            private void GenerateWorksheetPart2Content(WorksheetPart worksheetPart2)
            {
                Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                worksheet2.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                worksheet2.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                worksheet2.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                SheetDimension sheetDimension2 = new SheetDimension() { Reference = "A1" };

                SheetViews sheetViews2 = new SheetViews();
                SheetView sheetView2 = new SheetView() { WorkbookViewId = (UInt32Value)0U };

                sheetViews2.Append(sheetView2);
                SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

                SheetData sheetData2 = new SheetData();

                Row row2 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };

                Cell cell2 = new Cell() { CellReference = "A1" };
                CellValue cellValue2 = new CellValue();
                cellValue2.Text = "102";

                cell2.Append(cellValue2);

                row2.Append(cell2);

                sheetData2.Append(row2);
                PageMargins pageMargins2 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

                worksheet2.Append(sheetDimension2);
                worksheet2.Append(sheetViews2);
                worksheet2.Append(sheetFormatProperties2);
                worksheet2.Append(sheetData2);
                worksheet2.Append(pageMargins2);

                worksheetPart2.Worksheet = worksheet2;
            }

            // Generates content of worksheetPart3.
            private void GenerateWorksheetPart3Content(WorksheetPart worksheetPart3)
            {
                Worksheet worksheet3 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                worksheet3.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                worksheet3.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                worksheet3.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                SheetDimension sheetDimension3 = new SheetDimension() { Reference = "A1" };

                SheetViews sheetViews3 = new SheetViews();
                SheetView sheetView3 = new SheetView() { WorkbookViewId = (UInt32Value)0U };

                sheetViews3.Append(sheetView3);
                SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

                SheetData sheetData3 = new SheetData();

                Row row3 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };

                Cell cell3 = new Cell() { CellReference = "A1" };
                CellValue cellValue3 = new CellValue();
                cellValue3.Text = "101";

                cell3.Append(cellValue3);

                row3.Append(cell3);

                sheetData3.Append(row3);
                PageMargins pageMargins3 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

                worksheet3.Append(sheetDimension3);
                worksheet3.Append(sheetViews3);
                worksheet3.Append(sheetFormatProperties3);
                worksheet3.Append(sheetData3);
                worksheet3.Append(pageMargins3);

                worksheetPart3.Worksheet = worksheet3;
            }

           
            // Generates content of part.
            private void GeneratePartContent(WorkbookPart part)
            {
                Workbook workbook1 = new Workbook();
                workbook1.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "5", LowestEdited = "4", BuildVersion = "9302" };
                WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U };

                BookViews bookViews1 = new BookViews();
                WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U, ActiveTab = (UInt32Value)2U };

                bookViews1.Append(workbookView1);

                Sheets sheets1 = new Sheets();
                Sheet sheet1 = new Sheet() { Name = "test", SheetId = (UInt32Value)1U, Id = "rId1" };
                Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
                Sheet sheet3 = new Sheet() { Name = "Sheet3", SheetId = (UInt32Value)3U, Id = "rId3" };

                sheets1.Append(sheet1);
                sheets1.Append(sheet2);
                sheets1.Append(sheet3);
                CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)122211U };

                workbook1.Append(fileVersion1);
                workbook1.Append(workbookProperties1);
                workbook1.Append(bookViews1);
                workbook1.Append(sheets1);
                workbook1.Append(calculationProperties1);

                part.Workbook = workbook1;
            }
        }
    }

    I hope this can help you and feel free to follow up after you have tried.

    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.

    • Marked as answer by Bruce Song Thursday, February 24, 2011 9:45 AM
    Monday, February 21, 2011 8:01 AM

All replies

  • Hi Bantar,

    Thank you for posting and we are glad to help with you.

    After reading your post, I knew your issue that you want to manipulate mutiple worksheets via Open XML. I recommend you a good tool named Open XML SDK 2.0 Productivity Tool ,which can reflect the code from the Excel file. You can download from this website:http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en .

    I reflected the code by using the tool and generated mutiple worksheets via the code, and it can be modified according the requirements.  Below is my code snippet which can generate the workbook and insert three worksheets:

    using System;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;

    namespace OpenXmlCreateExcelSheets
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"), SpreadsheetDocumentType.Workbook))
                {
                    WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
                    GeneratedClass gcObj = new GeneratedClass();
                    gcObj.CreateWorkbookPart(workbookpart);
                    workbookpart.Workbook.Save();
                }
            }
        }
    }

    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    using X14 = DocumentFormat.OpenXml.Office2010.Excel;
    using A = DocumentFormat.OpenXml.Drawing;

    namespace OpenXmlCreateExcelSheets
    {
        class GeneratedClass
        {
            // Adds child parts and generates content of the specified part.
            public void CreateWorkbookPart(WorkbookPart part)
            {
                WorksheetPart worksheetPart1 = part.AddNewPart<WorksheetPart>("rId3");
                GenerateWorksheetPart1Content(worksheetPart1);

                WorksheetPart worksheetPart2 = part.AddNewPart<WorksheetPart>("rId2");
                GenerateWorksheetPart2Content(worksheetPart2);

                WorksheetPart worksheetPart3 = part.AddNewPart<WorksheetPart>("rId1");
                GenerateWorksheetPart3Content(worksheetPart3);

                //WorkbookStylesPart workbookStylesPart1 = part.AddNewPart<WorkbookStylesPart>("rId5");
                //GenerateWorkbookStylesPart1Content(workbookStylesPart1);

                //ThemePart themePart1 = part.AddNewPart<ThemePart>("rId4");
                //GenerateThemePart1Content(themePart1);

                GeneratePartContent(part);

            }

            // Generates content of worksheetPart1.
            private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
            {
                Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                worksheet1.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                worksheet1.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                worksheet1.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" };

                SheetViews sheetViews1 = new SheetViews();
                SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };

                sheetViews1.Append(sheetView1);
                SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

                SheetData sheetData1 = new SheetData();

                Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };

                Cell cell1 = new Cell() { CellReference = "A1" };
                CellValue cellValue1 = new CellValue();
                cellValue1.Text = "103";

                cell1.Append(cellValue1);

                row1.Append(cell1);

                sheetData1.Append(row1);
                PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

                worksheet1.Append(sheetDimension1);
                worksheet1.Append(sheetViews1);
                worksheet1.Append(sheetFormatProperties1);
                worksheet1.Append(sheetData1);
                worksheet1.Append(pageMargins1);

                worksheetPart1.Worksheet = worksheet1;
            }

            // Generates content of worksheetPart2.
            private void GenerateWorksheetPart2Content(WorksheetPart worksheetPart2)
            {
                Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                worksheet2.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                worksheet2.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                worksheet2.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                SheetDimension sheetDimension2 = new SheetDimension() { Reference = "A1" };

                SheetViews sheetViews2 = new SheetViews();
                SheetView sheetView2 = new SheetView() { WorkbookViewId = (UInt32Value)0U };

                sheetViews2.Append(sheetView2);
                SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

                SheetData sheetData2 = new SheetData();

                Row row2 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };

                Cell cell2 = new Cell() { CellReference = "A1" };
                CellValue cellValue2 = new CellValue();
                cellValue2.Text = "102";

                cell2.Append(cellValue2);

                row2.Append(cell2);

                sheetData2.Append(row2);
                PageMargins pageMargins2 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

                worksheet2.Append(sheetDimension2);
                worksheet2.Append(sheetViews2);
                worksheet2.Append(sheetFormatProperties2);
                worksheet2.Append(sheetData2);
                worksheet2.Append(pageMargins2);

                worksheetPart2.Worksheet = worksheet2;
            }

            // Generates content of worksheetPart3.
            private void GenerateWorksheetPart3Content(WorksheetPart worksheetPart3)
            {
                Worksheet worksheet3 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                worksheet3.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                worksheet3.AddNamespaceDeclaration("mc""http://schemas.openxmlformats.org/markup-compatibility/2006");
                worksheet3.AddNamespaceDeclaration("x14ac""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
                SheetDimension sheetDimension3 = new SheetDimension() { Reference = "A1" };

                SheetViews sheetViews3 = new SheetViews();
                SheetView sheetView3 = new SheetView() { WorkbookViewId = (UInt32Value)0U };

                sheetViews3.Append(sheetView3);
                SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };

                SheetData sheetData3 = new SheetData();

                Row row3 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" }, DyDescent = 0.25D };

                Cell cell3 = new Cell() { CellReference = "A1" };
                CellValue cellValue3 = new CellValue();
                cellValue3.Text = "101";

                cell3.Append(cellValue3);

                row3.Append(cell3);

                sheetData3.Append(row3);
                PageMargins pageMargins3 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };

                worksheet3.Append(sheetDimension3);
                worksheet3.Append(sheetViews3);
                worksheet3.Append(sheetFormatProperties3);
                worksheet3.Append(sheetData3);
                worksheet3.Append(pageMargins3);

                worksheetPart3.Worksheet = worksheet3;
            }

           
            // Generates content of part.
            private void GeneratePartContent(WorkbookPart part)
            {
                Workbook workbook1 = new Workbook();
                workbook1.AddNamespaceDeclaration("r""http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "5", LowestEdited = "4", BuildVersion = "9302" };
                WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U };

                BookViews bookViews1 = new BookViews();
                WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U, ActiveTab = (UInt32Value)2U };

                bookViews1.Append(workbookView1);

                Sheets sheets1 = new Sheets();
                Sheet sheet1 = new Sheet() { Name = "test", SheetId = (UInt32Value)1U, Id = "rId1" };
                Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
                Sheet sheet3 = new Sheet() { Name = "Sheet3", SheetId = (UInt32Value)3U, Id = "rId3" };

                sheets1.Append(sheet1);
                sheets1.Append(sheet2);
                sheets1.Append(sheet3);
                CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)122211U };

                workbook1.Append(fileVersion1);
                workbook1.Append(workbookProperties1);
                workbook1.Append(bookViews1);
                workbook1.Append(sheets1);
                workbook1.Append(calculationProperties1);

                part.Workbook = workbook1;
            }
        }
    }

    I hope this can help you and feel free to follow up after you have tried.

    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.

    • Marked as answer by Bruce Song Thursday, February 24, 2011 9:45 AM
    Monday, February 21, 2011 8:01 AM
  • I have 3 workbooks each containing 2 worksheets. How can I merge the 3 workbooks into a single workbook with 2 worksheets? Sheet 1 of my new workbook will be a merge of the the first sheet for each of the 3 workbooks, and similarly sheets 2 of the new workbook is the merge of the sheet 2 for each of the 3 workbooks.

     

    Thanks.

    Wednesday, March 16, 2011 5:04 AM