C# Worksheet.Save方法代码示例

        public static WorksheetPart AddWorksheet(this WorkbookPart workbookPart, string name, Worksheet sheet)
        {
            sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            
            var result = workbookPart.AddNewPart<WorksheetPart>();
            result.Worksheet = sheet;
            sheet.Save();

            workbookPart.Workbook.Sheets.Append(
                new Sheet
                {
                    Name = name,
                    SheetId = (uint)workbookPart.Workbook.Sheets.Count() + 1,
                    Id = workbookPart.GetIdOfPart(result)
                });

            return result;
        }

        public void AddRow(Worksheet worksheet, bool isHeader, params object[] values)
        {
            var row = new Row();
            var sheetData = worksheet.GetFirstChild<SheetData>();
            var styleIndex = StyleConst.CellFormat.CellFormatStyle.CELL_REGULAR;

            if (isHeader)
                styleIndex = StyleConst.CellFormat.CellFormatStyle.CELL_HEADER;

            foreach (var value in values)
            {
                var cell = GetCell(value, styleIndex);
                row.AppendChild(cell);
            }

            sheetData.Append(row);
            worksheet.Save();
        }

        // Given a Worksheet and a cell name, verifies that the specified cell exists.
        // If it does not exist, creates a new cell.
        private static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)
        {
            string columnName = GetColumnName(cellName);
            uint rowIndex = GetRowIndex(cellName);

            IEnumerable<Row> rows = worksheet.Descendants<Row>().Where(r => r.RowIndex.Value == rowIndex);

            // If the Worksheet does not contain the specified row, create the specified row.
            // Create the specified cell in that row, and insert the row into the Worksheet.
            if (rows.Count() == 0)
            {
                Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };
                Cell cell = new Cell() { CellReference = new StringValue(cellName) };
                row.Append(cell);
                worksheet.Descendants<SheetData>().First().Append(row);
                worksheet.Save();
            }
            else
            {
                Row row = rows.First();

                IEnumerable<Cell> cells = row.Elements<Cell>().Where(c => c.CellReference.Value == cellName);

                // If the row does not contain the specified cell, create the specified cell.
                if (cells.Count() == 0)
                {
                    Cell cell = new Cell() { CellReference = new StringValue(cellName) };
                    row.Append(cell);
                    worksheet.Save();
                }
            }
        }

        public static void UpdateValue(
            WorkbookPart wbPart,
            Worksheet ws, 
            string addressName, 
            string value,
            UInt32Value styleIndex, 
            bool isString)
        {
                DocumentFormat.OpenXml.Spreadsheet.Cell cell = InsertCellInWorksheet(ws, addressName);

                if (isString)
                {
                    // Either retrieve the index of an existing string,
                    // or insert the string into the shared string table
                    // and get the index of the new item.
                    int stringIndex = InsertSharedStringItem(wbPart, value);

                    cell.CellValue = new CellValue(stringIndex.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
                else
                {
                    cell.CellValue = new CellValue(value);
                    cell.DataType = new EnumValue<CellValues>(CellValues.Number);
                }

                if (styleIndex > 0)
                    cell.StyleIndex = styleIndex;

                // Save the worksheet.
                ws.Save();
        }

        /// <summary>
        ///     Merges the cell with the given range in the given worksheet
        /// </summary>
        /// <param name="worksheet">Worksheet the mergecell belongs to</param>
        /// <param name="cell1Name">Start range of the merge cell</param>
        /// <param name="cell2Name">End range of the merge cell</param>
        private static void MergeTwoCells(Worksheet worksheet, string cell1Name,
                                          string cell2Name)
        {
            if (worksheet == null || string.IsNullOrEmpty(cell1Name) ||
                string.IsNullOrEmpty(cell2Name)) {
                return;
            }

            MergeCells mergeCells;
            if (worksheet.Elements<MergeCells>().Any()) {
                mergeCells = worksheet.Elements<MergeCells>().First();
            } else {
                mergeCells = new MergeCells();

                // Insert a MergeCells object into the specified position.
                if (worksheet.Elements<CustomSheetView>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<CustomSheetView>()
                                                   .First());
                } else if (worksheet.Elements<DataConsolidate>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<DataConsolidate>()
                                                   .First());
                } else if (worksheet.Elements<SortState>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<SortState>()
                                                   .First());
                } else if (worksheet.Elements<AutoFilter>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<AutoFilter>()
                                                   .First());
                } else if (worksheet.Elements<Scenarios>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<Scenarios>()
                                                   .First());
                } else if (worksheet.Elements<ProtectedRanges>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<ProtectedRanges>()
                                                   .First());
                } else if (worksheet.Elements<SheetProtection>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<SheetProtection>()
                                                   .First());
                } else if (worksheet.Elements<SheetCalculationProperties>().Any()) {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet
                                              .Elements
                                              <SheetCalculationProperties>()
                                              .First());
                } else {
                    worksheet.InsertAfter(mergeCells,
                                          worksheet.Elements<SheetData>()
                                                   .First());
                }
            }

            // Create the merged cell and append it to the MergeCells collection.
            var mergeCell = new MergeCell {
                Reference = new StringValue(cell1Name + ":" + cell2Name)
            };
            mergeCells.Append(mergeCell);

            worksheet.Save();
        }

 private void InsertTextCellValue(Worksheet worksheet, string column, uint row, string value)
 {
     Cell cell = ReturnCell(worksheet, column, row);
     CellValue v = new CellValue();
     v.Text = value;
     cell.AppendChild(v);
     cell.DataType = new EnumValue<CellValues>(CellValues.String);
     worksheet.Save();
 }

        private void SetColumns(Worksheet worksheet)
        {
            var cols = new Columns();
            cols.AppendChild(new Column {
                Min = 1, Max = 1, Width = 65,
                CustomWidth = true
            });
            cols.AppendChild(new Column {
                Min = 2, Max = 2, Width = 20,
                CustomWidth = true
            });
            cols.AppendChild(new Column {
                Min = 3, Max = 3, Width = 20,
                CustomWidth = true
            });
            cols.AppendChild(new Column {
                Min = 4, Max = 4, Width = 20,
                CustomWidth = true
            });
            cols.AppendChild(new Column {
                Min = 5, Max = 5, Width = 20,
                CustomWidth = true
            });

            worksheet.InsertBefore(cols, worksheet.First(x => x.LocalName == "sheetData"));
            worksheet.Save();
        }