C# Worksheet.Elements方法代码示例

 public static void EnsureColumn(Worksheet worksheet, uint columnIndex)
 {
     var columns = worksheet.Elements<Columns>().FirstOrDefault();
     if (columns == null)
     {
         columns = worksheet.InsertAt(new Columns(), 0);
     }
     if (columns.Elements<Column>().Where(item => item.Min == columnIndex).Count() == 0)
     {
         Column previousColumn = null;
         for (uint counter = columnIndex - 1; counter > 0; counter--)
         {
             previousColumn = columns.Elements<Column>().Where(item => item.Min == counter).FirstOrDefault();
             if (previousColumn != null)
             {
                 break;
             }
         }
         columns.InsertAfter(new Column()
            {
                Min = columnIndex,
                Max = columnIndex,
                CustomWidth = true,
                Width = 9
            }, previousColumn);
     }
 }

 public XLWSContentManager(Worksheet opWorksheet)
 {
     contents.Add(XLWSContents.SheetProperties, opWorksheet.Elements<SheetProperties>().LastOrDefault());
     contents.Add(XLWSContents.SheetDimension, opWorksheet.Elements<SheetDimension>().LastOrDefault());
     contents.Add(XLWSContents.SheetViews, opWorksheet.Elements<SheetViews>().LastOrDefault());
     contents.Add(XLWSContents.SheetFormatProperties, opWorksheet.Elements<SheetFormatProperties>().LastOrDefault());
     contents.Add(XLWSContents.Columns, opWorksheet.Elements<Columns>().LastOrDefault());
     contents.Add(XLWSContents.SheetData, opWorksheet.Elements<SheetData>().LastOrDefault());
     contents.Add(XLWSContents.SheetCalculationProperties, opWorksheet.Elements<SheetCalculationProperties>().LastOrDefault());
     contents.Add(XLWSContents.SheetProtection, opWorksheet.Elements<SheetProtection>().LastOrDefault());
     contents.Add(XLWSContents.ProtectedRanges, opWorksheet.Elements<ProtectedRanges>().LastOrDefault());
     contents.Add(XLWSContents.Scenarios, opWorksheet.Elements<Scenarios>().LastOrDefault());
     contents.Add(XLWSContents.AutoFilter, opWorksheet.Elements<AutoFilter>().LastOrDefault());
     contents.Add(XLWSContents.SortState, opWorksheet.Elements<SortState>().LastOrDefault());
     contents.Add(XLWSContents.DataConsolidate, opWorksheet.Elements<DataConsolidate>().LastOrDefault());
     contents.Add(XLWSContents.CustomSheetViews, opWorksheet.Elements<CustomSheetViews>().LastOrDefault());
     contents.Add(XLWSContents.MergeCells, opWorksheet.Elements<MergeCells>().LastOrDefault());
     contents.Add(XLWSContents.PhoneticProperties, opWorksheet.Elements<PhoneticProperties>().LastOrDefault());
     contents.Add(XLWSContents.ConditionalFormatting, opWorksheet.Elements<ConditionalFormatting>().LastOrDefault());
     contents.Add(XLWSContents.DataValidations, opWorksheet.Elements<DataValidations>().LastOrDefault());
     contents.Add(XLWSContents.Hyperlinks, opWorksheet.Elements<Hyperlinks>().LastOrDefault());
     contents.Add(XLWSContents.PrintOptions, opWorksheet.Elements<PrintOptions>().LastOrDefault());
     contents.Add(XLWSContents.PageMargins, opWorksheet.Elements<PageMargins>().LastOrDefault());
     contents.Add(XLWSContents.PageSetup, opWorksheet.Elements<PageSetup>().LastOrDefault());
     contents.Add(XLWSContents.HeaderFooter, opWorksheet.Elements<HeaderFooter>().LastOrDefault());
     contents.Add(XLWSContents.RowBreaks, opWorksheet.Elements<RowBreaks>().LastOrDefault());
     contents.Add(XLWSContents.ColumnBreaks, opWorksheet.Elements<ColumnBreaks>().LastOrDefault());
     contents.Add(XLWSContents.CustomProperties, opWorksheet.Elements<CustomProperties>().LastOrDefault());
     contents.Add(XLWSContents.CellWatches, opWorksheet.Elements<CellWatches>().LastOrDefault());
     contents.Add(XLWSContents.IgnoredErrors, opWorksheet.Elements<IgnoredErrors>().LastOrDefault());
     //contents.Add(XLWSContents.SmartTags, opWorksheet.Elements<SmartTags>().LastOrDefault());
     contents.Add(XLWSContents.Drawing, opWorksheet.Elements<Drawing>().LastOrDefault());
     contents.Add(XLWSContents.LegacyDrawing, opWorksheet.Elements<LegacyDrawing>().LastOrDefault());
     contents.Add(XLWSContents.LegacyDrawingHeaderFooter, opWorksheet.Elements<LegacyDrawingHeaderFooter>().LastOrDefault());
     contents.Add(XLWSContents.DrawingHeaderFooter, opWorksheet.Elements<DrawingHeaderFooter>().LastOrDefault());
     contents.Add(XLWSContents.Picture, opWorksheet.Elements<Picture>().LastOrDefault());
     contents.Add(XLWSContents.OleObjects, opWorksheet.Elements<OleObjects>().LastOrDefault());
     contents.Add(XLWSContents.Controls, opWorksheet.Elements<Controls>().LastOrDefault());
     contents.Add(XLWSContents.AlternateContent, opWorksheet.Elements<AlternateContent>().LastOrDefault());
     contents.Add(XLWSContents.WebPublishItems, opWorksheet.Elements<WebPublishItems>().LastOrDefault());
     contents.Add(XLWSContents.TableParts, opWorksheet.Elements<TableParts>().LastOrDefault());
     contents.Add(XLWSContents.WorksheetExtensionList, opWorksheet.Elements<WorksheetExtensionList>().LastOrDefault());
 }

        /// <summary>
        /// Generate an excel file with the information of certificates
        /// </summary>
        /// <param name="dataSource">The list of certificates</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateCertificateReport(CertificateListModel model, 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);
                }
                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;

//.........这里部分代码省略.........

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

        /// <summary>
        /// Generate service order report
        /// </summary>
        /// <param name="itemsource">Item source</param>
        /// <param name="workbookPart">Worbook part</param>
        private static void GenerateServiceOrder(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets,int sheetId,string logoPath)
        {
            if (itemsource.IsSelectedServiceOrder)
            {

                // Remove the sheet reference from the workbook.
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                // The SheetData object will contain all the data.
                SheetData sheetData = new SheetData();
                Worksheet worksheet = new Worksheet();

                Form serviceOrder = itemsource.ServiceOrderData;

                Row rowTitle;
                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[9];
                for (int n = 0; n < 9; 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 < 9; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    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;
//.........这里部分代码省略.........

        /// <summary>
        /// Generate inspection reports
        /// </summary>
        /// <param name="itemsource">Item source</param>
        /// <param name="workbookPart">Worbook part</param>
        private static void GenerateAllInspectionReports(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets, int sheetId, string logoPath)
        {
            //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
            foreach (var item in itemsource.InspectionReports)
            {
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

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

                //get the number of columns in the report
                Row rowTitle;
                int numberOfColumnsCaption = item.Value.Captions.Count;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
                for (int n = 0; n < numberOfColumnsCaption; 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 < numberOfColumnsCaption; 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", 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:
//.........这里部分代码省略.........

        /// <summary>
        /// Generate an excel report dinamically
        /// </summary>
        /// <param name="model">Data source</param>
        public static MemoryStream GenerateReportDinamically(DynamicDataGrid model, string logoPath)
        {
            MemoryStream report = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(report, 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 = 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;
                int numberOfColumnsCaption = model.Captions.Count;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
                for (int n = 0; n < numberOfColumnsCaption; 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 < numberOfColumnsCaption; 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", 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];
//.........这里部分代码省略.........

        /// <summary>
        /// Generate the report for catalogue categories
        /// </summary>
        /// <param name="templatePath">Path of the template</param>
        /// <param name="itemSource">Item source</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateCatalogueValueReport(CatalogueValueSearchModel itemSource,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 = 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);
//.........这里部分代码省略.........