C# WorkbookPart.GetIdOfPart方法代码示例

        public Worksheet AddSheet(WorkbookPart workbookPart, string name)
        {
            var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();

            // add single Sheet
            var workSheetpart = workbookPart.AddNewPart<WorksheetPart>();
            workSheetpart.Worksheet = new Worksheet(new SheetData());
            workSheetpart.Worksheet.Save();

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

            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return workSheetpart.Worksheet;
        }

        /// <summary>
        /// 插入worksheet
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <returns></returns>
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName = null)
        {
            //創建一個新的WorkssheetPart（後面將用它來容納具體的Sheet）
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();
 
            //取得Sheet集合
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            if (sheets == null)
            {
                sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            }
 
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
 
            //得到Sheet的唯一序號
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }
 
            string sheetTempName = "Sheet" + sheetId;
 
            if (sheetName != null)
            {
                bool hasSameName = false;
                //檢測是否有重名
                foreach (var item in sheets.Elements<Sheet>())
                {
                    if (item.Name == sheetName)
                    {
                        hasSameName = true;
                        break;
                    }
                }
                if (!hasSameName)
                {
                    sheetTempName = sheetName;
                }
            }
 
            //創建Sheet實例並將它與sheets關聯
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetTempName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();
 
            return newWorksheetPart;
        }

        // Given a WorkbookPart, inserts a new worksheet.
        private WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // We need single sheet only, if there is a sheet, return
            if (workbookPart.WorksheetParts.Count() > 0)
            {
                return workbookPart.WorksheetParts.FirstOrDefault<WorksheetPart>();
            }

            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            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();

            return newWorksheetPart;
        }

        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            Sheet foundSheet = null;
            foreach (Sheet sheet in workbookPart.Workbook.Sheets)
            {
                if (sheet.Name == sheetName)
                {
                    foundSheet = sheet;
                    break;
                }
            }

            if (foundSheet == null)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();

                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                // 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();
                return newWorksheetPart;
            }
            else
            {
                return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
            }
        }

//.........这里部分代码省略.........
                using (OpenXmlReader oxr = OpenXmlReader.Create(wbp.CalculationChainPart))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CalculationCell))
                        {
                            cc = (CalculationCell)oxr.LoadCurrentElement();
                            if (cc.SheetId == null)
                            {
                                cc.SheetId = iCurrentSheetId;
                            }
                            else
                            {
                                if (cc.SheetId.Value != iCurrentSheetId)
                                    iCurrentSheetId = cc.SheetId.Value;
                            }
                            slcc.FromCalculationCell(cc);
                            slwb.CalculationCells.Add(slcc.Clone());
                        }
                    }
                }
            }

            // To determine the type of sheet. Do this before the part
            // where the table and pivot table parts are set.

            bool bFound = false;
            string sRelID = string.Empty;
            foreach (SLSheet sheet in slwb.Sheets)
            {
                bFound = false;
                foreach (WorksheetPart wspFound in wbp.WorksheetParts)
                {
                    sRelID = wbp.GetIdOfPart(wspFound);
                    if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                    {
                        sheet.SheetType = SLSheetType.Worksheet;
                        bFound = true;
                        break;
                    }
                }

                if (!bFound)
                {
                    foreach (ChartsheetPart csp in wbp.ChartsheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(csp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.Chartsheet;
                            bFound = true;
                            break;
                        }
                    }
                }

                if (!bFound)
                {
                    foreach (DialogsheetPart dsp in wbp.DialogsheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(dsp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.DialogSheet;
                            bFound = true;
                            break;

        /// <summary>
        ///     Copies a sheet with a given sheet name and saves it with the clonedSheetName in the workbook
        /// </summary>
        /// <param name="docType">DocType to created for temp file in the method</param>
        /// <param name="workbookPart">Workbook which the worksheet to be copied and the copied worksheet to be saved</param>
        /// <param name="sheetName">Name of the worksheet to copy</param>
        /// <param name="clonedSheetName">New name of the copied sheet</param>
        private void copySheet(SpreadsheetDocumentType docType,
                               WorkbookPart workbookPart, string sheetName,
                               string clonedSheetName)
        {
            //Get the source sheet to be copied
            WorksheetPart sourceSheetPart = getWorksheetPart(workbookPart,
                                                             sheetName);
            //Take advantage of AddPart for deep cloning
            SpreadsheetDocument tempSheet =
                SpreadsheetDocument.Create(new MemoryStream(), docType);
            WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
            WorksheetPart tempWorksheetPart =
                tempWorkbookPart.AddPart(sourceSheetPart);
            //Add cloned sheet and all associated parts to workbook
            WorksheetPart clonedSheet = workbookPart.AddPart(tempWorksheetPart);

            //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
            int numTableDefParts =
                sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
            _tableId = numTableDefParts;
            //Clean up table definition parts (tables need unique ids)
            if (numTableDefParts != 0) {
                fixupTableParts(clonedSheet);
            }
            //There should only be one sheet that has focus
            cleanView(clonedSheet);

            //Add new sheet to main workbook part
            var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            var copiedSheet = new Sheet();
            copiedSheet.Name = clonedSheetName;
            copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
            copiedSheet.SheetId = (uint) sheets.ChildElements.Count + 1;
            sheets.Append(copiedSheet);
            //Save Changes
            workbookPart.Workbook.Save();
        }

//.........这里部分代码省略.........
                    sheetData.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("B2", itemsource.BusinessApplicationName, currentRowTitle, 5);

                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "B2:E2";
                mergeCells.Append(mergeCell);

                currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
                //add the form name
                UpdateStringCellValue("B4", itemsource.ServiceOrderSheetName, currentRowTitle, 5);

                //merge all cell in the form name
                mergeCell = new MergeCell();
                mergeCell.Reference = "B4:E4";
                mergeCells.Append(mergeCell);
                Drawing drawing = AddLogo(logoPath, worksheetPart);
                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 26));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 73));

                worksheet.Append(columns);

                int rowIndex = 8;
                Row sectionRow;

                foreach (var section in serviceOrder.Sections)
                {
                    sectionRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    mergeCell = new MergeCell();
                    mergeCell.Reference = "A" + rowIndex + ":B" + rowIndex;
                    mergeCells.Append(mergeCell);
                    AppendTextCell("A" + rowIndex, section.Caption, sectionRow, 6);
                    AppendTextCell("B" + rowIndex, string.Empty, sectionRow, 6);
                    sheetData.Append(sectionRow);
                    foreach (var element in section.FormElements)
                    {
                        rowIndex++;
                        //The current row is obtained for updating the value of the cell
                        Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                        switch (element.Field.FieldType)
                        {
                            case FieldType.Catalogue:
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                if (!string.IsNullOrEmpty(element.Field.FieldValue))
                                {
                                    string catalogueValue = CatalogueBusiness.GetCatalogueValue(new Guid(element.Field.FieldValue)).CatalogueValueData;
                                    AppendTextCell("B" + rowIndex.ToString(), catalogueValue, rowData, 1);
                                }
                                else
                                {
                                    AppendTextCell("B" + rowIndex.ToString(), string.Empty, rowData, 1);
                                }
                                break;
                            case FieldType.RegularExpressionText:
                            case FieldType.Time:
                            case FieldType.SingleTextLine:
                            case FieldType.MultipleTextLine:
                            case FieldType.Datepicker:
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                AppendTextCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
                                break;
                            case FieldType.Boolean:
                                string boolValue = element.Field.FieldValue == "True" ? LanguageResource.Yes : LanguageResource.No;
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                AppendTextCell("B" + rowIndex.ToString(), boolValue, rowData, 1);
                                break;
                            case FieldType.Integer:
                            case FieldType.Decimal:
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                AppendNumberCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
                                break;
                            default:
                                break;
                        }

                        sheetData.Append(rowData);
                    }
                    rowIndex+=2;
                }

                worksheet.Append(sheetData);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = itemsource.ServiceOrderSheetName, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                sheetId++;
            }
        }

//.........这里部分代码省略.........
                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("B2", item.Value.BusinessApplicationName, currentRowTitle, 5);

                string lastColumnName = excelColumnNamesTitle.Last() + "2";
                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "B2:" + lastColumnName;
                mergeCells.Append(mergeCell);

                currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
                //add the form name
                UpdateStringCellValue("B4", item.Key, currentRowTitle, 5);

                lastColumnName = lastColumnName.Replace("2", "4");

                //merge all cell in the form name
                mergeCell = new MergeCell();
                mergeCell.Reference = "B4:" + lastColumnName;
                mergeCells.Append(mergeCell);

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                int rowIndex = 7;

                //get the names of the columns
                string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
                for (int n = 0; n < numberOfColumnsCaption; n++)
                    excelColumnNamesCaptions[n] = GetExcelColumnName(n);

                Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                //build column names of the report
                Columns columns = new Columns();
                for (int i = 0; i < item.Value.Captions.Count; i++)
                {
                    var caption = item.Value.Captions[i];
                    AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
                    columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
                }
                sheetData1.Append(rowCaption);
                //add the new row with the name of the columns
                worksheet.Append(columns);
                rowIndex = 8;
                //write the data of the report
                foreach (var row in item.Value.DataRows)
                {
                    int numberOfColumnsData = row.FieldValues.Count;
                    //get column names
                    string[] excelColumnNamesData = new string[numberOfColumnsData];
                    for (int n = 0; n < numberOfColumnsData; n++)
                        excelColumnNamesData[n] = GetExcelColumnName(n);

                    //build the row
                    Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
                    {
                        DynamicDataRowValue col = row.FieldValues[colInx];
                        switch (col.FieldType)
                        {
                            case (int)FieldType.Catalogue:
                            case (int)FieldType.RegularExpressionText:
                            case (int)FieldType.Time:
                            case (int)FieldType.SingleTextLine:
                            case (int)FieldType.MultipleTextLine:
                            case (int)FieldType.Datepicker:
                            case (int)FieldType.Boolean:
                            case (int)FieldType.AutoComplete:
                            case (int)FieldType.StatusField:
                                AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
                                break;
                            case (int)FieldType.Integer:
                            case (int)FieldType.Decimal:
                                AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
                                break;
                            default:
                                break;
                        }
                    }

                    //add the new row to the report
                    sheetData1.Append(rowData);
                    rowIndex++;
                }

                //add the information of the current sheet
                worksheet.Append(sheetData1);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = item.Key, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                sheetId++;
            }
        }

        protected internal override void CreateDocument()
        {
            Report r = base.Report();
            _workbookPart = _spreadSheet.AddWorkbookPart();

            var openXmlExportHelper = new OpenXmlWriterHelper();
            _styleSheet = openXmlExportHelper.CreateDefaultStylesheet();

            _workbookPart.Workbook = _workbook;
            var sheets = _workbook.AppendChild<Sheets>(new Sheets());

            // create worksheet 1
            var worksheetPart =_workbookPart.AddNewPart<WorksheetPart>();
            var sheet = new Sheet() { Id = _workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = r.Name };
            sheets.Append(sheet);

            _workSheetSettings.Add(new WorkSheetSetting(r.Name));

            _writer = OpenXmlWriter.Create(worksheetPart);
            _writer.WriteStartElement(new Worksheet());
            _writer.WriteStartElement(new SheetData());

               _currentWorkSheet = _workSheetSettings.Last();
        }

        //private static void AddTextToWorkSheet(string path, string sheetName, string text, string columnName, uint rowIndex)
        //{
        //    // Open the document for editing.
        //    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
        //    {
        //        // Get the SharedStringTablePart. If it does not exist, create a new one.
        //        SharedStringTablePart shareStringPart;
        //        if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        //        {
        //            shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        //        }
        //        else
        //        {
        //            shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
        //        }
        //        // Insert the text into the SharedStringTablePart.
        //        int index = InsertSharedStringItem(spreadSheet.WorkbookPart, text);
        //        // Insert a new worksheet.
        //        WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart, sheetName);
        //        // Insert cell A1 into the new worksheet.
        //        Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex);
        //        // Set the value of cell A1.
        //        cell.CellValue = new CellValue(index.ToString());
        //        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
        //        // Save the new worksheet.
        //        worksheetPart.Worksheet.Save();
        //    }
        //}
        public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            Sheet foundSheet = workbookPart.Workbook.Sheets.Cast<Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName);

            if (foundSheet == null)
            {
                // Add a new worksheet part to the workbook.
                var newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();

                var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                // Append the new worksheet and associate it with the workbook.
                var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append((IEnumerable<OpenXmlElement>)sheet);
                return newWorksheetPart;
            }

            return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
        }

        // Given a WorkbookPart, inserts a new worksheet.
        public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            // GetFirstChild <T>: Find the first child element in type T
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new worksheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            // Give the new worksheet a name.
            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();

            return newWorksheetPart;
        }