Esempio n. 1
0
        /// <summary>
        /// Adds a given worksheet to the document
        /// </summary>
        /// <param name="worksheet">Worksheet document to add</param>
        /// <returns>Worksheet part just added</returns>
        public static WorksheetPart Add(SpreadsheetDocument doc, XDocument worksheet)
        {
            // Associates base content to a new worksheet part
            WorkbookPart  workbook      = doc.WorkbookPart;
            WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>();

            worksheetPart.PutXDocument(worksheet);

            // Associates the worksheet part to the workbook part
            XDocument document = doc.WorkbookPart.GetXDocument();
            int       sheetId  =
                document.Root
                .Element(ns + "sheets")
                .Elements(ns + "sheet")
                .Count() + 1;

            int worksheetCount =
                document.Root
                .Element(ns + "sheets")
                .Elements(ns + "sheet")
                .Where(
                    t =>
                    t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
                    )
                .Count() + 1;

            // Adds content to workbook document to reference worksheet document
            document.Root
            .Element(ns + "sheets")
            .Add(
                new XElement(ns + "sheet",
                             new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
                             new XAttribute("sheetId", sheetId),
                             new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
                             )
                );
            doc.WorkbookPart.PutXDocument();
            return(worksheetPart);
        }
Esempio n. 2
0
        static void Main(string[] args)
        {
            // Update an existing pivot table
            FileInfo qs  = new FileInfo("../../QuarterlySales.xlsx");
            FileInfo qsu = new FileInfo("../../QuarterlyPivot.xlsx");

            int row = 1;

            using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
                       SmlDocument.FromFileName(qs.FullName)))
            {
                using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
                {
                    WorksheetPart sheet = WorksheetAccessor.GetWorksheet(doc, "Range");
                    using (StreamReader source = new StreamReader("../../PivotData.txt"))
                    {
                        while (!source.EndOfStream)
                        {
                            string line = source.ReadLine();
                            if (line.Length > 3)
                            {
                                string[] fields = line.Split(',');
                                int      column = 1;
                                foreach (string item in fields)
                                {
                                    double num;
                                    if (double.TryParse(item, out num))
                                    {
                                        WorksheetAccessor.SetCellValue(doc, sheet, row, column++, num);
                                    }
                                    else
                                    {
                                        WorksheetAccessor.SetCellValue(doc, sheet, row, column++, item);
                                    }
                                }
                            }
                            row++;
                        }
                    }
                    sheet.PutXDocument();

                    WorksheetAccessor.UpdateRangeEndRow(doc, "Sales", row - 1);
                }
                streamDoc.GetModifiedSmlDocument().SaveAs(qsu.FullName);
            }

            // Create from scratch
            row = 1;
            int maxColumn = 1;

            using (OpenXmlMemoryStreamDocument streamDoc = OpenXmlMemoryStreamDocument.CreateSpreadsheetDocument())
            {
                using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
                {
                    WorksheetAccessor.CreateDefaultStyles(doc);
                    WorksheetPart     sheet = WorksheetAccessor.AddWorksheet(doc, "Range");
                    MemorySpreadsheet ms    = new MemorySpreadsheet();

#if false
                    int font0 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font2 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 18,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
                        Name   = "Cambria",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Major
                    });
                    int font3 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 15,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font4 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 13,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font5 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 3),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font6 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FF006100"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font7 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FF9C0006"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font8 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FF9C6500"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font9 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FF3F3F76"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font10 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FF3F3F3F"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font11 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FFFA7D00"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font12 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FFFA7D00"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font13 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 0),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font14 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FFFF0000"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font15 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Italic = true,
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo("FF7F7F7F"),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font16 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Bold   = true,
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });
                    int font17 = WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Size   = 11,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 0),
                        Name   = "Calibri",
                        Family = 2,
                        Scheme = WorksheetAccessor.Font.SchemeType.Minor
                    });

                    int fill0 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.None, null, null));
                    int fill1 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Gray125, null, null));
                    int fill2 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo("FFC6EFCE")));
                    int fill3 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo("FFFFC7CE")));
                    int fill4 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo("FFFFEB9C")));
                    int fill5 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo("FFFFCC99")));
                    int fill6 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo("FFF2F2F2")));
                    int fill7 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo("FFA5A5A5")));
                    int fill8 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo("FFFFFFCC")));
                    int fill9 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                      null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4)));
                    int fill10 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(4, 0.79998168889431442)));
                    int fill11 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(4, 0.59999389629810485)));
                    int fill12 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(4, 0.39997558519241921)));
                    int fill13 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 5)));
                    int fill14 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(5, 0.79998168889431442)));
                    int fill15 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(5, 0.59999389629810485)));
                    int fill16 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(5, 0.39997558519241921)));
                    int fill17 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 6)));
                    int fill18 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(6, 0.79998168889431442)));
                    int fill19 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(6, 0.59999389629810485)));
                    int fill20 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(6, 0.39997558519241921)));
                    int fill21 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 7)));
                    int fill22 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(7, 0.79998168889431442)));
                    int fill23 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(7, 0.59999389629810485)));
                    int fill24 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(7, 0.39997558519241921)));
                    int fill25 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 8)));
                    int fill26 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(8, 0.79998168889431442)));
                    int fill27 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(8, 0.59999389629810485)));
                    int fill28 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(8, 0.39997558519241921)));
                    int fill29 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       null, new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 9)));
                    int fill30 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(9, 0.79998168889431442)));
                    int fill31 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(9, 0.59999389629810485)));
                    int fill32 = WorksheetAccessor.GetFillIndex(doc, new WorksheetAccessor.PatternFill(WorksheetAccessor.PatternFill.PatternType.Solid,
                                                                                                       new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Indexed, 65),
                                                                                                       new WorksheetAccessor.ColorInfo(9, 0.39997558519241921)));

                    int border1 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thick,
                                                                  new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4))
                    });
                    int border2 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thick, new WorksheetAccessor.ColorInfo(4, 0.499984740745262))
                    });
                    int border3 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Medium, new WorksheetAccessor.ColorInfo(4, 0.39997558519241921))
                    });
                    int border4 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Left   = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
                        Right  = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
                        Top    = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F")),
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF7F7F7F"))
                    });
                    int border5 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Left   = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
                        Right  = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
                        Top    = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF3F3F3F"))
                    });
                    int border6 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FFFF8001"))
                    });
                    int border7 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Left   = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
                        Right  = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
                        Top    = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F")),
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double, new WorksheetAccessor.ColorInfo("FF3F3F3F"))
                    });
                    int border8 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Left   = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
                        Right  = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
                        Top    = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2")),
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FFB2B2B2"))
                    });
                    int border9 = WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        Top = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin,
                                                               new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4)),
                        Bottom = new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Double,
                                                                  new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 4))
                    });
#endif

                    int southIndex = WorksheetAccessor.GetStyleIndex(doc, 0, 8, 1, 2,
                                                                     new WorksheetAccessor.CellAlignment {
                        HorizontalAlignment = WorksheetAccessor.CellAlignment.Horizontal.Center
                    },
                                                                     true, false);
                    WorksheetAccessor.GradientFill gradient = new WorksheetAccessor.GradientFill(90);
                    gradient.AddStop(new WorksheetAccessor.GradientStop(0, new WorksheetAccessor.ColorInfo("FF92D050")));
                    gradient.AddStop(new WorksheetAccessor.GradientStop(1, new WorksheetAccessor.ColorInfo("FF0070C0")));
                    int northIndex = WorksheetAccessor.GetStyleIndex(doc, 0,
                                                                     WorksheetAccessor.GetFontIndex(doc, new WorksheetAccessor.Font
                    {
                        Italic = true,
                        Size   = 8,
                        Color  = new WorksheetAccessor.ColorInfo(WorksheetAccessor.ColorInfo.ColorType.Theme, 1),
                        Name   = "Times New Roman",
                        Family = 1
                    }),
                                                                     WorksheetAccessor.GetFillIndex(doc, gradient),
                                                                     WorksheetAccessor.GetBorderIndex(doc, new WorksheetAccessor.Border
                    {
                        DiagonalDown = true,
                        Diagonal     =
                            new WorksheetAccessor.BorderLine(WorksheetAccessor.BorderLine.LineStyle.Thin, new WorksheetAccessor.ColorInfo("FF616100"))
                    }),
                                                                     null, false, false);
                    WorksheetAccessor.CheckNumberFormat(doc, 100, "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)");
                    int amountIndex = WorksheetAccessor.GetStyleIndex(doc, 100, 0, 0, 0, null, false, false);

                    using (StreamReader source = new StreamReader("../../PivotData.txt"))
                    {
                        while (!source.EndOfStream)
                        {
                            string line = source.ReadLine();
                            if (line.Length > 3)
                            {
                                string[] fields = line.Split(',');
                                int      column = 1;
                                foreach (string item in fields)
                                {
                                    double num;
                                    if (double.TryParse(item, out num))
                                    {
                                        if (column == 6)
                                        {
                                            ms.SetCellValue(row, column++, num, amountIndex);
                                        }
                                        else
                                        {
                                            ms.SetCellValue(row, column++, num);
                                        }
                                    }
                                    else if (item == "Accessories")
                                    {
                                        ms.SetCellValue(row, column++, item, WorksheetAccessor.GetStyleIndex(doc, "Good"));
                                    }
                                    else if (item == "South")
                                    {
                                        ms.SetCellValue(row, column++, item, southIndex);
                                    }
                                    else if (item == "North")
                                    {
                                        ms.SetCellValue(row, column++, item, northIndex);
                                    }
                                    else
                                    {
                                        ms.SetCellValue(row, column++, item);
                                    }
                                }
                                maxColumn = column - 1;
                            }
                            row++;
                        }
                    }
                    WorksheetAccessor.SetSheetContents(doc, sheet, ms);
                    WorksheetAccessor.SetRange(doc, "Sales", "Range", 1, 1, row - 1, maxColumn);
                    WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
                    WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);

                    // Configure pivot table rows, columns, data and filters
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Year", WorksheetAccessor.PivotAxis.Column);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Category", WorksheetAccessor.PivotAxis.Row);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Product", WorksheetAccessor.PivotAxis.Row);
                    WorksheetAccessor.AddDataValue(doc, pivot, "Amount");
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Region", WorksheetAccessor.PivotAxis.Page);
                }
                streamDoc.GetModifiedSmlDocument().SaveAs("../../NewPivot.xlsx");
            }


            // Add pivot table to existing spreadsheet
            // Demonstrate multiple data fields
            using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(
                       SmlDocument.FromFileName("../../QuarterlyUnitSales.xlsx")))
            {
                using (SpreadsheetDocument doc = streamDoc.GetSpreadsheetDocument())
                {
                    WorksheetPart pivot = WorksheetAccessor.AddWorksheet(doc, "Pivot");
                    WorksheetAccessor.CreatePivotTable(doc, "Sales", pivot);

                    // Configure pivot table rows, columns, data and filters
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Year", WorksheetAccessor.PivotAxis.Column);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Quarter", WorksheetAccessor.PivotAxis.Column);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Category", WorksheetAccessor.PivotAxis.Row);
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Product", WorksheetAccessor.PivotAxis.Row);
                    WorksheetAccessor.AddDataValue(doc, pivot, "Total");
                    WorksheetAccessor.AddDataValue(doc, pivot, "Quantity");
                    WorksheetAccessor.AddDataValue(doc, pivot, "Unit Price");
                    WorksheetAccessor.AddPivotAxis(doc, pivot, "Region", WorksheetAccessor.PivotAxis.Page);
                }
                streamDoc.GetModifiedSmlDocument().SaveAs("../../QuarterlyUnitSalesWithPivot.xlsx");
            }
        }
        /// <summary>
        /// Method for adding a new table definition part
        /// </summary>
        /// <param name="worksheet">Worksheet to add the table to</param>
        /// <param name="tableStyle">Style to be assigned to the table</param>
        /// <param name="useHeaders">Set a header row</param>
        /// <param name="fromColumn">Initial column for table</param>
        /// <param name="toColumn">Final column for table</param>
        /// <param name="fromRow">Intial row for table</param>
        /// <param name="toRow">Final row for table</param>
        public static OpenXmlPowerToolsDocument Add(SmlDocument doc, string worksheetName, string tableStyle, bool useHeaders, short fromColumn, short toColumn, int fromRow, int toRow)
        {
            using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
            {
                using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
                {
                    //Getting the id for this table
                    int tableId = GetNextTableId(document);

                    //Set the table cell range
                    string tableRange = string.Format("{0}{1}:{2}{3}", WorksheetAccessor.GetColumnId(fromColumn),
                                                      fromRow,
                                                      WorksheetAccessor.GetColumnId(toColumn),
                                                      toRow);

                    //Creating a new id for the relationship between the table definition part and the worksheet
                    string tableRelationShipId = "rId" + Guid.NewGuid();

                    //Create a new table definition part
                    WorksheetPart       worksheet = WorksheetAccessor.Get(document, worksheetName);
                    TableDefinitionPart table     = worksheet.AddNewPart <TableDefinitionPart>(tableRelationShipId);

                    //string tableColumns = string.Empty;
                    XElement tableColumnsXElement = new XElement(ns + "tableColumns", new XAttribute("count", (toColumn - fromColumn) + 1));
                    //Get the name for table column elements from the first table row
                    string[] tableHeaders = GetTableHeaders(document, worksheet, fromRow, fromColumn, toColumn);
                    for (int i = 0; i <= (toColumn - fromColumn); i++)
                    {
                        //Create the markup for the SpreadsheetML table column elements
                        tableColumnsXElement.Add(
                            new XElement(ns + "tableColumn", new XAttribute("id", i + 1), new XAttribute("name", tableHeaders[i])));
                    }

                    XElement tableXElement =
                        new XElement(ns + "table",
                                     new XAttribute("xmlns", ns), //default namespace
                                     new XAttribute("id", tableId),
                                     new XAttribute("name", "Table" + tableId.ToString()),
                                     new XAttribute("displayName", "Table" + tableId.ToString()),
                                     new XAttribute("ref", tableRange),
                                     new XAttribute("totalsRowShown", "0"));

                    if (useHeaders)
                    {
                        tableXElement.Add(
                            new XElement(ns + "autoFilter", new XAttribute("ref", tableRange)));
                    }

                    tableXElement.Add(tableColumnsXElement);

                    tableXElement.Add(
                        new XElement(ns + "tableStyleInfo",
                                     new XAttribute("name", tableStyle),
                                     new XAttribute("showFirstColumn", "0"),
                                     new XAttribute("showLastColumn", "0"),
                                     new XAttribute("showRowStripes", "0"),
                                     new XAttribute("showColumnStripes", "0")));

                    //Write the markup to the Table Definition Part Stream
                    XmlWriter tablePartStreamWriter = XmlWriter.Create(table.GetStream());
                    tableXElement.WriteTo(tablePartStreamWriter);

                    tablePartStreamWriter.Flush();
                    tablePartStreamWriter.Close();

                    //Create or modify the table parts definition at worksheet (for setting the relationship id with the new table)
                    XDocument worksheetMarkup = worksheet.GetXDocument();
                    //Look for the tableParts element at worksheet markup
                    XElement tablePartsElement = worksheetMarkup.Root.Element(ns + "tableParts");
                    if (tablePartsElement != null)
                    {
                        //tableParts elements does exist at worksheet markup
                        //increment the tableParts count attribute value
                        short tableCount = System.Convert.ToInt16(tablePartsElement.Attribute("count").Value);
                        tablePartsElement.SetAttributeValue("count", tableCount++.ToString());
                    }
                    else
                    {
                        //tableParts does not exist at worksheet markup
                        //create a new tableParts element
                        tablePartsElement = new XElement(ns + "tableParts",
                                                         new XAttribute(ns + "count", "1"));

                        worksheetMarkup.Root.Add(tablePartsElement);
                    }

                    //create the tablePart element
                    XElement tablePartEntryElement = new XElement(ns + "tablePart",
                                                                  new XAttribute(relationshipns + "id", tableRelationShipId));

                    //add the new tablePart element to the worksheet tableParts element
                    tablePartsElement.Add(tablePartEntryElement);
                    worksheet.PutXDocument();
                }
                return(streamDoc.GetModifiedDocument());
            }
        }