Exemple #1
0
        private static void AddWorksheet(SpreadsheetDocument sDoc, Worksheet worksheetData)
        {
            Regex validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$");

            if (!validSheetName.IsMatch(worksheetData.Name))
            {
                throw new InvalidSheetNameException(worksheetData.Name);
            }

            // throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook
            string    UCName = worksheetData.Name.ToUpper();
            XDocument wXDoc  = sDoc.WorkbookPart.GetXDocument();

            if (wXDoc
                .Root
                .Elements(S.sheets)
                .Elements(S.sheet)
                .Attributes(SSNoNamespace.name)
                .Select(a => ((string)a).ToUpper())
                .Contains(UCName))
            {
                throw new WorksheetAlreadyExistsException(worksheetData.Name);
            }

            // create the worksheet with the supplied name
            XDocument appXDoc = sDoc
                                .ExtendedFilePropertiesPart
                                .GetXDocument();
            XElement vector = appXDoc
                              .Root
                              .Elements(EP.TitlesOfParts)
                              .Elements(VT.vector)
                              .FirstOrDefault();

            if (vector != null)
            {
                int?size = (int?)vector.Attribute(SSNoNamespace.size);
                if (size == null)
                {
                    size = 1;
                }
                else
                {
                    size = size + 1;
                }
                vector.SetAttributeValue(SSNoNamespace.size, size);
                vector.Add(
                    new XElement(VT.lpstr, worksheetData.Name));
                XElement i4 = appXDoc
                              .Root
                              .Elements(EP.HeadingPairs)
                              .Elements(VT.vector)
                              .Elements(VT.variant)
                              .Elements(VT.i4)
                              .FirstOrDefault();
                if (i4 != null)
                {
                    i4.Value = ((int)i4 + 1).ToString();
                }
                sDoc.ExtendedFilePropertiesPart.PutXDocument();
            }

            WorkbookPart  workbook      = sDoc.WorkbookPart;
            string        rId           = "R" + Guid.NewGuid().ToString().Replace("-", "");
            WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>(rId);

            XDocument wbXDoc = workbook.GetXDocument();
            XElement  sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault();

            sheets.Add(
                new XElement(S.sheet,
                             new XAttribute(SSNoNamespace.name, worksheetData.Name.ToString()),
                             new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1),
                             new XAttribute(R.id, rId)));
            workbook.PutXDocument();

            string ws    = S.s.ToString();
            string relns = R.r.ToString();

            using (Stream partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write))
            {
                using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
                {
                    partXmlWriter.WriteStartDocument();
                    partXmlWriter.WriteStartElement("worksheet", ws);
                    partXmlWriter.WriteStartElement("sheetData", ws);

                    int numColumnHeadingRows = 0;
                    int numColumns           = 0;
                    int numColumnsInRows     = 0;
                    int numRows;
                    if (worksheetData.ColumnHeadings != null)
                    {
                        Row row = new Row
                        {
                            Cells = worksheetData.ColumnHeadings
                        };
                        SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows);
                    }
                    SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out numColumnsInRows,
                                  out numRows);
                    int totalRows    = numColumnHeadingRows + numRows;
                    int totalColumns = Math.Max(numColumns, numColumnsInRows);
                    if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null)
                    {
                        partXmlWriter.WriteEndElement();
                        string rId2 = "R" + Guid.NewGuid().ToString().Replace("-", "");
                        partXmlWriter.WriteStartElement("tableParts", ws);
                        partXmlWriter.WriteStartAttribute("count");
                        partXmlWriter.WriteValue(1);
                        partXmlWriter.WriteEndAttribute();
                        partXmlWriter.WriteStartElement("tablePart", ws);
                        partXmlWriter.WriteStartAttribute("id", relns);
                        partXmlWriter.WriteValue(rId2);
                        TableDefinitionPart tdp   = worksheetPart.AddNewPart <TableDefinitionPart>(rId2);
                        XDocument           tXDoc = tdp.GetXDocument();
                        XElement            table = new XElement(S.table,
                                                                 new XAttribute(SSNoNamespace.id, 1),
                                                                 new XAttribute(SSNoNamespace.name, worksheetData.TableName),
                                                                 new XAttribute(SSNoNamespace.displayName, worksheetData.TableName),
                                                                 new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString()),
                                                                 new XAttribute(SSNoNamespace.totalsRowShown, 0),
                                                                 new XElement(S.autoFilter,
                                                                              new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString())),
                                                                 new XElement(S.tableColumns,
                                                                              new XAttribute(SSNoNamespace.count, totalColumns),
                                                                              worksheetData.ColumnHeadings.Select((ch, i) =>
                                                                                                                  new XElement(S.tableColumn,
                                                                                                                               new XAttribute(SSNoNamespace.id, i + 1),
                                                                                                                               new XAttribute(SSNoNamespace.name, ch.Value)))),
                                                                 new XElement(S.tableStyleInfo,
                                                                              new XAttribute(SSNoNamespace.name, "TableStyleMedium2"),
                                                                              new XAttribute(SSNoNamespace.showFirstColumn, 0),
                                                                              new XAttribute(SSNoNamespace.showLastColumn, 0),
                                                                              new XAttribute(SSNoNamespace.showRowStripes, 1),
                                                                              new XAttribute(SSNoNamespace.showColumnStripes, 0)));
                        tXDoc.Add(table);
                        tdp.PutXDocument();
                    }
                }
            }
        }