C# Packaging.WorksheetPart类代码示例

        public static void SetInlineCell(WorksheetPart worksheetPart, string text, string columnName, uint rowIndex)
        {
            Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);

            cell.CellValue = new CellValue(string.IsNullOrWhiteSpace(text) ? "" : text);
            cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
        }

        public void AddNewWorksheet()
        {
            WorkbookPart workbookPart = spreadSheet.WorkbookPart;

            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();
            CurrentWorksheetPart = newWorksheetPart;
            //workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1;
            //workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1;
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();
        }

        public static void SetCellDateTime(WorksheetPart worksheetPart, DateTime value, string columnName, uint rowIndex)
        {
            Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);

            cell.CellValue = new CellValue(value.ToOADate().ToString("0.000#####"));
            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
        }

        private void UpdateWorksheetContent(WorksheetPart workSheetPart, List<ExcelSheet> templateRows, SharedStringTable sharedStrings, List<ProjectFile> projectFiles, Customer customer, User user)
        {
            IEnumerable<SheetData> sheets = workSheetPart.Worksheet.ChildElements.OfType<SheetData>();

            foreach (var sheetData in sheets)
            {
                sheetData.RemoveAllChildren<Row>();

                List<String> strings = sharedStrings.ChildElements.OfType<SharedStringItem>().Where(shItem => shItem.ChildElements.OfType<Text>().Any()).Select(shItem =>
                    {
                        var firstOrDefault = shItem.ChildElements.OfType<Text>().FirstOrDefault();
                        return firstOrDefault != null ? firstOrDefault.Text : String.Empty;
                    }).ToList();

                //from this point is the content
                int linesToAdd = 0;
                var templateSheet = templateRows.FirstOrDefault(row => row.SheetData.Equals(sheetData));
                if (templateSheet != null)
                {
                    foreach (var projectFile in projectFiles)
                    {
                        GenerateWorksheetDataContent(sheetData, templateSheet.Rows, linesToAdd, new List<TokensProvider> {projectFile, customer, user}, strings);
                        linesToAdd += (templateSheet.Rows.Count + 5);
                    }
                }
            }
        }

        public static void SetCellDate(WorksheetPart worksheetPart, DateTime value, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row)
        {
            Cell cell = GetCell(worksheetPart.Worksheet, columnName, row);

            cell.CellValue = new CellValue(value.ToOADate().ToString("0"));
            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
        }

        public static void SetCell(WorksheetPart worksheetPart, string text, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row, out Cell cell)
        {
            cell = GetCell(worksheetPart.Worksheet, columnName, row);

            cell.CellValue = new CellValue(string.IsNullOrWhiteSpace(text) ? "" : text);
            cell.DataType = new EnumValue<CellValues>(CellValues.String);
        }

        /// <summary>
        /// Creates the content of the shet (columns, rows, cells)
        /// </summary>
        /// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
        /// <param name="worksheetPart">The worksheetpart for this item</param>
        private void InsertTextIntoCells(SpreadsheetDocument spreadsheetDocument, WorksheetPart worksheetPart)
        {
            // Get the SharedStringTablePart. If it does not exist, create a new one.
            SharedStringTablePart shareStringPart;
            if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any())
            {
                shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }

            #region Excel headers

            string header1 = "ResourceTypeID";
            int index1 = _sharedResources.InsertSharedStringItem(header1, shareStringPart);
            Cell headerCell1 = _sharedResources.InsertCellInWorksheet("A", 1, worksheetPart);
            headerCell1.CellValue = new CellValue(index1.ToString(CultureInfo.InvariantCulture));
            headerCell1.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            string header2 = "ResourceType";
            int index2 = _sharedResources.InsertSharedStringItem(header2, shareStringPart);
            Cell headerCell2 = _sharedResources.InsertCellInWorksheet("B", 1, worksheetPart);
            headerCell2.CellValue = new CellValue(index2.ToString(CultureInfo.InvariantCulture));
            headerCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            #endregion

            #region insert the items from the temporary list into the ktUIOrder excel sheet

            int columnCount = 1;
            uint rowCount = 2;

            foreach (ktResourceType resourceType in _resourceType.ktResourceTypeList)
            {
                    if (columnCount >= 2)
                    {
                        columnCount = 1;
                    }

                    string text1 = resourceType.ResourceTypeID;
                    Cell cell1 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
                    cell1.CellValue = new CellValue(text1);
                    cell1.DataType = CellValues.Number;
                    columnCount++;

                    string text2 = resourceType.ResourceType;
                    Cell cell2 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
                    cell2.CellValue = new CellValue(text2);
                    cell2.DataType = CellValues.String;
                    columnCount++;

                    rowCount++;
            }

            #endregion

            worksheetPart.Worksheet.Save();
        }

 public OpenXLRow(WorkbookPart wbPart, WorksheetPart wsPart, List<string> columns, Row xRow)
 {
     this.WbPart = wbPart;
     this.WsPart = wsPart;
     this.Columns = columns;
     this._cells = GetCells(xRow);
 }

 private Sheet GetCurrentSheet(WorksheetPart worksheetPart)
 {
     var sheetId = workbookPart.GetIdOfPart(worksheetPart);
     var correspondingSheet = sheets.FirstOrDefault(
         s => s.Id.HasValue && s.Id.Value == sheetId);
     return correspondingSheet;
 }

 public string GetValueCell(ref WorkbookPart wbPart, ref WorksheetPart wsPart, string addressName)
 {
     string value = null;
     Cell theCell = wsPart.Worksheet.Descendants<Cell>().
         Where(c => c.CellReference == addressName).FirstOrDefault();
     value = GetCleanValueCell(theCell, ref wbPart);
     return value;
 }

 public void CreateHeader(WorksheetPart worksheetPart)
 {
     if (worksheetPart == null)
     {
         throw new ArgumentNullException("worksheetPart");
     }
     ExcelUtility.SetStringCell(worksheetPart.Worksheet, "B", 2, ServiceOffering.Id.ToString());
     ExcelUtility.SetStringCell(worksheetPart.Worksheet, "C", 2, ServiceOffering.Name);
 }

 public void CreateErrorRows(WorksheetPart worksheetPart)
 {
     for (int rowIndex = 0; rowIndex < ErrorRows.Count; rowIndex++)
     {
         for (int i = 0; i < ErrorRows[rowIndex].RowErrors.Count; i++)
         {
             ExcelUtility.SetStringCell(worksheetPart.Worksheet, CellDictionary[i], (uint)rowIndex + 4, ErrorRows[rowIndex].RowErrors[i]);
         }
     }
 }

        private void AddReportColumns(WorkbookPart workbookPart, WorksheetPart worksheetPart, List<string> columnNames)
        {
            for (int i = 0; i < columnNames.Count; i++)
            {
                string fieldName = columnNames[i];

                WorkbookHelper.AddCellText(
                    workbookPart,
                    worksheetPart,
                    fieldName,
                    (uint)(i + 1), 1U);
            }
        }

        private WorksheetPart wsSheet = null; //  WorkSheetPart

        #endregion Fields

        #region Constructors

        public LogGenerator(string fn)
        {
            ////  get spreadsheet path from constructor
            //path = folder;
            ////  File name is based on date and time
            //DateTime now = DateTime.Now;
            ////  Construct the spreadsheet filename
            //string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
            //    path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
            if (File.Exists(fn))
            {
                doc = SpreadsheetDocument.Open(fn, true);
            }
            else
            {
                //
                //  Create the Spreadsheet document
                //
                doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
                //
                //  Add WoorkBookPart to the document
                //
                wbPart = doc.AddWorkbookPart();
                wbPart.Workbook = new Workbook();
                wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                //
                //  Add WorkSheetPart to the WorkBookPart
                //
                wsSheet = wbPart.AddNewPart<WorksheetPart>();
                wsSheet.Worksheet = new Worksheet(new SheetData());
                wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                //
                //  Add sheets to the WorkBook
                //
                sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                //
                //  Append new sheet and associate it with the workbook
                //
                sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
                sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                sheets.Append(sheet);

                wbPart.Workbook.Save();
                //CreatePackage(fn);

                //if (File.Exists(fn))
                //{
                //    TestXls();
                //}
            }
        }

 private void AddReportLines(WorkbookPart workbookPart, WorksheetPart worksheetPart, Report report, uint startRow)
 {
     for (int iRow = 0; iRow < report.ReportLines.Count; iRow++)
     {
         for (int iCol = 0; iCol < report.ColumnNames.Count; iCol++)
         {
             WorkbookHelper.AddCellText(
                 workbookPart,
                 worksheetPart,
                 report[iRow][iCol].ToString(),
                 (uint)iCol + 1, (uint)iRow + startRow + 1);
         }
     }
 }