Exemple #1
0
        private void OverWriteSpreadSheetDocument(string fileName)
        {
            using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fileName, true))
            {
                if (processedObjects.Count > 0)
                {
                    GetPSObjectType();
                    CreateColumns();
                    FillValues();

                    List<SSW.Row> rowCollection = GetRowCollection(result);

                    //Add work sheet to existing document
                    SSW.Worksheet workSheet = new SSW.Worksheet();
                    workSheet.Name = this.sheetName;
                    workSheet.TableName = tableName;
                    workSheet.ColumnHeadings = columnHeadings;
                    workSheet.Rows = rowCollection;

                    SSW.SpreadsheetWriter.AddWorksheet(sDoc, workSheet);
                }
            }
        }
        public 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();
                    }
                }
            }
            sDoc.WorkbookPart.WorkbookStylesPart.PutXDocument();
            sDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
        }
Exemple #3
0
        private void GenerateNewSpreadSheetDocument(SpreadsheetDocument document, string sheetName, string tableName)
        {
            List<SSW.Row> rowCollection = GetRowCollection(result);

            // Create new work sheet to te document
            List<SSW.Worksheet> workSheetCollection = new List<SSW.Worksheet>();
            SSW.Worksheet workSheet = new SSW.Worksheet();

            if (string.IsNullOrEmpty(sheetName))
                workSheet.Name = DefaultSheetName;
            else
                workSheet.Name = this.sheetName;

            workSheet.TableName = tableName;
            workSheet.ColumnHeadings = columnHeadings;
            workSheet.Rows = rowCollection;

            workSheetCollection.Add(workSheet);

            // Create work book
            SSW.Workbook workBook = new SSW.Workbook();
            workBook.Worksheets = workSheetCollection;

            // Create Excel File
            SSW.SpreadsheetWriter.Write(fileName, workBook);

        }