C# Worksheet.InsertAfter方法代码示例

//.........这里部分代码省略.........
                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[4];
                for (int n = 0; n < 4; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 4; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }
                List<CertificateDocument> dataSource = model.Certificates.Collection;
                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the title
                UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);

                //set min date and max date in header
                Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
                string minDate, maxDate;
                //get dates
                if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
                {
                    minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
                    maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
                }
                else
                {
                    minDate = model.IssuanceDateFrom;
                    maxDate = model.IssuanceDateTo;
                }

                //write both dates
                UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
                UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);

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

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
                columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
                columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
                worksheet.Append(columns);

                int rowIndex = 8;

                Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
                AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
                AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
                AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
                sheetData1.Append(rowData);

                rowIndex = 9;

                //build the data
                foreach (var item in dataSource)
                {

                    rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };

                    AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1);
                    AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1);
                    AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1);
                    AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1);

                    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 = Resources.Common.CertificateList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();
            }
            return ms;
        }

        public static MergeCell InsertMergeCell(Worksheet ws,
    string addressName1,
    string addressName2)
        {
            SheetData sheetData = ws.GetFirstChild<SheetData>();
            MergeCells cells = ws.GetFirstChild<MergeCells>();
            if (cells == null)
            {
                cells = new MergeCells();
                ws.InsertAfter<MergeCells>(cells, sheetData);
            }

            string s = addressName1 + ":" + addressName2;

            MergeCell cell = cells.Elements<MergeCell>().Where(c => c.Reference == s).FirstOrDefault();
            if (cell == null)
            {
                cell = new MergeCell();
                cell.Reference = s;
                cells.AppendChild<MergeCell>(cell);
            }

            return cell;
        }

        /// <summary>
        /// Generate an excel file with the list of users
        /// </summary>
        /// <param name="dataSource">The list of users</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateUserReport(List<UserModel> dataSource, string logoPath)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                //create the new workbook
                WorkbookPart workbookPart = document.AddWorkbookPart();
                Workbook workbook = new Workbook();
                workbookPart.Workbook = workbook;

                //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
                WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

                //get and save the stylesheet
                Stylesheet stylesheet = VocStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                //add the new workseet
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                Sheets sheets = new Sheets();

                //get the number of columns in the report
                Row rowTitle;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[4];
                for (int n = 0; n < 4; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 4; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("A2", Resources.Common.UserList, currentRowTitle, 5);

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

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 45));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 42));
                columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 10));
                columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 32));
                worksheet.Append(columns);

                int rowIndex = 8;

                Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, Resources.Common.Email, rowData, 2);
                AppendTextCell("B" + rowIndex, Resources.Common.FullName, rowData, 2);
                AppendTextCell("C" + rowIndex, Resources.Common.Active, rowData, 2);
                AppendTextCell("D" + rowIndex, Resources.Common.Role, rowData, 2);
                sheetData1.Append(rowData);

                rowIndex = 9;

                //build the data
                foreach (var item in dataSource)
                {

                    rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };

                    AppendTextCell("A" + rowIndex.ToString(), item.Email, rowData, 1);
                    AppendTextCell("B" + rowIndex.ToString(), item.FullName, rowData, 1);
                    AppendTextCell("C" + rowIndex.ToString(), item.IsActive, rowData, 1);
                    AppendTextCell("D" + rowIndex.ToString(), item.Role, rowData, 1);

                    sheetData1.Append(rowData);
                    rowIndex++;
                }

                //add the information of the current sheet
                worksheet.Append(sheetData1);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
//.........这里部分代码省略.........

        /// <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();
        }

//.........这里部分代码省略.........
                    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++;
            }
        }

//.........这里部分代码省略.........
                UpdateStringCellValue("B2", model.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);

                Drawing drawing = AddLogo(logoPath, worksheetPart);

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

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

                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 < model.Captions.Count; i++)
                {
                    var caption = model.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 item in model.DataRows)
                {
                    int numberOfColumnsData = item.FieldValues.Count;
                    //get column names
                    string[] excelColumnNamesData = new string[numberOfColumnsData];
                    for (int n = 0; n < numberOfColumnsData; n++)
                        excelColumnNamesData[n] = GetExcelColumnName(n);

                    //build the data information
                    Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
                    {
                        DynamicDataRowValue col = item.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 = model.FormName, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();
            }
            return report;
        }

//.........这里部分代码省略.........
                //get and save the stylesheet
                Stylesheet stylesheet = VestalisStyleSheet();
                workbookStylesPart.Stylesheet = stylesheet;
                workbookStylesPart.Stylesheet.Save();

                //add the new workseet
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                Sheets sheets = new Sheets();

                //get the number of columns in the report
                Row rowTitle;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[2];
                for (int n = 0; n < 2; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 2; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

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

                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "B2:B4";
                mergeCells.Append(mergeCell);
                Drawing drawing = AddLogo(logoPath, worksheetPart);

                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 50));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 71));
                worksheet.Append(columns);

                int rowIndex = 8;
                Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, LanguageResource.BusinessApplicationName, rowData, 2);
                AppendTextCell("B" + rowIndex, itemSource.BusinessApplicatioName, rowData, 1);
                sheetData1.Append(rowData);

                rowIndex = 9;
                rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex, LanguageResource.CatalogueName, rowData, 2);
                AppendTextCell("B" + rowIndex, itemSource.CatalogueSelectedName, rowData, 1);
                sheetData1.Append(rowData);

                rowIndex = 11;

                rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                AppendTextCell("A" + rowIndex.ToString(), LanguageResource.Value, rowData, 2);
                AppendTextCell("B" + rowIndex.ToString(), LanguageResource.Description, rowData, 2);
                sheetData1.Append(rowData);

                rowIndex = 12;

                foreach (var item in itemSource.SearchResult.Collection)
                {
                    rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    AppendTextCell("A" + rowIndex.ToString(), item.CatalogueValueData, rowData, 1);
                    AppendTextCell("B" + rowIndex.ToString(), item.CatalogueValueDescription, rowData, 1);
                    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 = LanguageResource.Report, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();
            }

            return ms;
        }