C# Workbook.Append方法代码示例

        public MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
        {
            MemoryStream stream = new MemoryStream();
            UInt32 rowcount = 0;

            // Create the Excel document
            var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
            var workbookPart = document.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            var relId = workbookPart.GetIdOfPart(worksheetPart);

            var workbook = new Workbook();
            var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
            var worksheet = new Worksheet();
            var sheetData = new SheetData();
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            var sheets = new Sheets();
            var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            document.WorkbookPart.Workbook = workbook;
            document.WorkbookPart.Workbook.Save();

            // Add header to the sheet
            var row = new Row { RowIndex = ++rowcount };
            for (int i = 0; i < fieldsToExpose.Length; i++)
            {
                row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
            }
            sheetData.AppendChild(row);
            worksheetPart.Worksheet.Save();

            // Add data to the sheet
            foreach (DataRow dataRow in data.Rows)
            {
                row = new Row { RowIndex = ++rowcount };
                for (int i = 0; i < fieldsToExpose.Length; i++)
                {
                    row.Append(CreateTextCell(ColumnLetter(i), rowcount, dataRow[fieldsToExpose[i]].ToString()));
                }
                sheetData.AppendChild(row);
            }
            worksheetPart.Worksheet.Save();
            document.Close();
            return stream;
        }

        public void Create(string fileName)
        {
            FileName = fileName;

            using (SpreadsheetDocument doc = SpreadsheetDocument.Create(FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = doc.AddWorkbookPart();
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                //	Create Styles
                var stylesPart = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                Style = new CustomStylesheet();
                LoadCustomFonts();
                LoadCustomBorders();
                LoadCustomStyles();
                Style.Save(stylesPart);

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                //	create workbook and sheet
                var workbook = new Workbook();
                var worksheet = new Worksheet();
                var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

                //	create columns
                var columns = new Columns();
                CreateColumns(columns);
                worksheet.Append(columns);

                //	create Sheet
                var sheets = new Sheets();
                var sheet = new Sheet { Name = "My sheet", SheetId = 1, Id = relId };
                sheets.Append(sheet);

                workbook.Append(fileVersion);
                workbook.Append(sheets);

                var sheetData = new SheetData();
                LoadData(sheetData);
                worksheet.Append(sheetData);

                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                doc.WorkbookPart.Workbook = workbook;
                doc.WorkbookPart.Workbook.Save();
                //doc.Close();
            }
        }

        // Generates content of workbookPart1. 
        private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            sheets1.Append(sheet1);

            workbook1.Append(sheets1);
            workbookPart1.Workbook = workbook1;
        }

        /// <summary>
        /// Generate all inspection reports
        /// </summary>
        /// <param name="templatePath">Path of template</param>
        /// <param name="itemsource">Item source</param>
        /// <returns>MemoryStream</returns>
        public static MemoryStream GenerateAllInspectionReports(ExportInspectionReportsModel itemsource,string logoPath)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms,SpreadsheetDocumentType.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();

                int sheetId = 1;
                Sheets sheets = new Sheets();
                if (itemsource.IsSelectedServiceOrder)
                {
                    //Generate service order report
                    GenerateServiceOrder(itemsource, workbookPart, sheets,sheetId,logoPath);
                    sheetId++;
                }
                //Generate inspection reports
                GenerateAllInspectionReports(itemsource, workbookPart, sheets, sheetId, logoPath);

                //add the new sheet to the report
                workbook.Append(sheets);
                //save all report
                workbook.Save();
                //close the stream.
                document.Close();

            }
            return ms;
        }

        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)124226U };

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 240, YWindow = 45, WindowWidth = (UInt32Value)28320U, WindowHeight = (UInt32Value)12855U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();

            Sheet sheet1 = new Sheet(){ Name = "NOMBRE DE SOLAPA", SheetId = (UInt32Value)1U, Id = "rId1" };

            switch (_Estado.ToUpper())
            {
                case "ORDENADO":
                    {
                        sheet1.Name = "O" + _oCabecera.AnoMes + " " + _oCabecera.IdentifEspacio;
                        break;
                    }
                case "ESTIMADO":
                    {
                        sheet1.Name = "E" + _eCabecera.AnoMes + " V" + _eCabecera.Version + " " + _eCabecera.IdentifEspacio;
                        break;
                    }
                case "CERTIFICADO":
                    {
                        sheet1.Name = "C" + _cCabecera.AnoMes + " " + _cCabecera.IdentifEspacio + _cCabecera.IdentifOrigen;
                        break;
                    }
            }

            sheets1.Append(sheet1);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)124519U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }

        //создает нову книгу с листами Excel
        protected virtual void GenWorkbookPart(string sheetName, WorkbookPart workbookPart)
        {
            Workbook workbook = new Workbook();

            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet() { Name = sheetName, SheetId = (UInt32Value)1U, Id = "rId1" };
            sheets.Append(sheet);
            workbook.Append(sheets);
            workbookPart.Workbook = workbook;
        }

        //Main class function, export the mutationList to XLSX file, sets file name to testName.
        public static void saveXLS(String testName, List<Mutation> mutationList)
        {
            SpreadsheetDocument xl = null;
            string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
            fullPath += ".xlsx";
            try
            {
                xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
            }
            catch (IOException )
            {
                throw ;
            }
            WorkbookPart wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();
            SheetData sd = new SheetData();
            WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = GenerateStyleSheet();
            wbsp.Stylesheet.Save();

            //save the longest width for each column.
            string[] longestWordPerColumn = new string[12];

            int k = 0;

            //create and add header row.
            Row headerRow = new Row();
            foreach (string s in Mutation.getHeaderForExport())
            {
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(s);
                cell.StyleIndex = 1;
                headerRow.AppendChild(cell);
                longestWordPerColumn[k] = s;
                k++;
            }
            sd.AppendChild(headerRow);

            //create and add rows for each mutation.
            foreach (Mutation m in mutationList)
            {
                Row newRow = new Row();
                string[] infoString = m.getInfoForExport();
                for (int i = 0; i < infoString.Length; i++)
                {
                    Cell cell1 = new Cell();
                    if (i == 1)
                        cell1.DataType = CellValues.Number;
                    else
                        cell1.DataType = CellValues.String;
                    cell1.CellValue = new CellValue(infoString[i]);
                    if (!m.CosmicName.Equals("-----"))
                        cell1.StyleIndex = 2;
                    else
                        cell1.StyleIndex = 3;
                    newRow.AppendChild(cell1);
                    if (longestWordPerColumn[i].Length < infoString[i].Length)
                        longestWordPerColumn[i] = infoString[i];
                }
                sd.AppendChild(newRow);
            }

            //Sets the column width to longest width for each column.
            Columns columns = new Columns();
            for (int i = 0; i < 12; i++)
            {
                columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
            }
            ws.Append(columns);


            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "Sheet1";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);
            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
        }

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

        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook();
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView() { XWindow = 360, YWindow = 150, WindowWidth = (UInt32Value)14355U, WindowHeight = (UInt32Value)4680U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };

            sheets1.Append(sheet1);
            CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }

        /// <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;

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

        protected void ExcelDl_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=ViewHistoryChart.xlsx");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            Chart1.RenderControl(hw);

            using (MemoryStream stream = new MemoryStream())
            {
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                {
                    var fileName = HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\ChartImage.png";

                    WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart();
                    WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                    Workbook wb = new Workbook();
                    FileVersion fv = new FileVersion();
                    fv.ApplicationName = "Microsoft Office Excel";
                    Worksheet ws = new Worksheet();
                    SheetData sd = new SheetData();

                    // add contents
                    List<string> values = new List<string>();

                    //values.Add(lblSectionFunctionSelected.Text);

                    uint i = 1;
                    foreach (var value in values)
                    {
                        UInt32Value rowIndex = UInt32Value.FromUInt32(i);
                        var row = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
                        sd.Append(row);

                        var cell = new Cell
                        {
                            CellValue = new CellValue(value),
                            DataType = new EnumValue<CellValues>(CellValues.String)
                        };
                        row.InsertAt(cell, 0);
                        i++;
                    }
                    // add image
                    DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
                    ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
                    using (FileStream fs = new FileStream(fileName, FileMode.Open))
                    {
                        imgp.FeedData(fs);
                    }
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
                    nvdp.Id = 1025;
                    nvdp.Name = "Picture 1";
                    nvdp.Description = "Chart";
                    DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
                    picLocks.NoChangeAspect = true;
                    picLocks.NoChangeArrowheads = true;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties();
                    nvpdp.PictureLocks = picLocks;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties();
                    nvpp.NonVisualDrawingProperties = nvdp;
                    nvpp.NonVisualPictureDrawingProperties = nvpdp;

                    DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
                    stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill();
                    DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
                    blip.Embed = dp.GetIdOfPart(imgp);
                    blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
                    blipFill.Blip = blip;
                    blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
                    blipFill.Append(stretch);

                    DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
                    DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
                    offset.X = 0;
                    offset.Y = 0;
                    t2d.Offset = offset;
                    Bitmap bm = new Bitmap(fileName);
                    //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
                    //http://stackoverflow.com/questions/1341930/pixel-to-centimeter
                    //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
                    DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
                    extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
                    extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
                    bm.Dispose();
                    t2d.Extents = extents;
                    DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties();
                    sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
                    sp.Transform2D = t2d;
                    DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
                    prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
                    prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
                    sp.Append(prstGeom);
                    sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

                    DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
//.........这里部分代码省略.........

        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U };

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
            Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };

            sheets1.Append(sheet1);
            sheets1.Append(sheet2);
            CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)152511U };

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(calculationProperties1);

            workbookPart1.Workbook = workbook1;
        }

        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" }  };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties();

            AlternateContent alternateContent1 = new AlternateContent();
            alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");

            AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };

            X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Timeline\\" };
            absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");

            alternateContentChoice1.Append(absolutePath1);

            alternateContent1.Append(alternateContentChoice1);

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet(){ Name = "data", SheetId = (UInt32Value)1U, Id = "rId1" };
            Sheet sheet2 = new Sheet(){ Name = "data2", SheetId = (UInt32Value)3U, Id = "rId2" };
            Sheet sheet3 = new Sheet(){ Name = "Cache", SheetId = (UInt32Value)4U, Id = "rId3" };
            Sheet sheet4 = new Sheet(){ Name = "Level", SheetId = (UInt32Value)2U, Id = "rId4" };
            Sheet sheet5 = new Sheet(){ Name = "Caption", SheetId = (UInt32Value)5U, Id = "rId5" };
            Sheet sheet6 = new Sheet(){ Name = "ShowHeader", SheetId = (UInt32Value)6U, Id = "rId6" };
            Sheet sheet7 = new Sheet(){ Name = "ShowSelectionLabel", SheetId = (UInt32Value)7U, Id = "rId7" };
            Sheet sheet8 = new Sheet(){ Name = "ShowTimeLevel", SheetId = (UInt32Value)8U, Id = "rId8" };
            Sheet sheet9 = new Sheet(){ Name = "ShowHorizontalScrollbar", SheetId = (UInt32Value)9U, Id = "rId9" };
            Sheet sheet10 = new Sheet(){ Name = "ScrollPosition", SheetId = (UInt32Value)10U, Id = "rId10" };
            Sheet sheet11 = new Sheet(){ Name = "Style", SheetId = (UInt32Value)11U, Id = "rId11" };

            sheets1.Append(sheet1);
            sheets1.Append(sheet2);
            sheets1.Append(sheet3);
            sheets1.Append(sheet4);
            sheets1.Append(sheet5);
            sheets1.Append(sheet6);
            sheets1.Append(sheet7);
            sheets1.Append(sheet8);
            sheets1.Append(sheet9);
            sheets1.Append(sheet10);
            sheets1.Append(sheet11);

            DefinedNames definedNames1 = new DefinedNames();
            DefinedName definedName1 = new DefinedName(){ Name = "NativeTimeline_Date" };
            definedName1.Text = "#N/A";
            DefinedName definedName2 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate" };
            definedName2.Text = "#N/A";
            DefinedName definedName3 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate1" };
            definedName3.Text = "#N/A";
            DefinedName definedName4 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate2" };
            definedName4.Text = "#N/A";
            DefinedName definedName5 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate3" };
            definedName5.Text = "#N/A";
            DefinedName definedName6 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate4" };
            definedName6.Text = "#N/A";
            DefinedName definedName7 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate5" };
            definedName7.Text = "#N/A";
            DefinedName definedName8 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate6" };
            definedName8.Text = "#N/A";
            DefinedName definedName9 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate7" };
            definedName9.Text = "#N/A";

            definedNames1.Append(definedName1);
            definedNames1.Append(definedName2);
            definedNames1.Append(definedName3);
            definedNames1.Append(definedName4);
            definedNames1.Append(definedName5);
            definedNames1.Append(definedName6);
            definedNames1.Append(definedName7);
            definedNames1.Append(definedName8);
            definedNames1.Append(definedName9);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };

            PivotCaches pivotCaches1 = new PivotCaches();
            PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId12" };
            PivotCache pivotCache2 = new PivotCache(){ CacheId = (UInt32Value)1U, Id = "rId13" };

            pivotCaches1.Append(pivotCache1);
            pivotCaches1.Append(pivotCache2);

            WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();

            WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
            workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
            X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();

            workbookExtension1.Append(workbookProperties2);

            WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{D0CA8CA8-9F24-4464-BF8E-62219DCF47F9}" };
            workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//.........这里部分代码省略.........

        // Generates content of workbookPart1.
        private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
        {
            Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" }  };
            workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
            FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
            WorkbookProperties workbookProperties1 = new WorkbookProperties(){ CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)153222U };

            AlternateContent alternateContent1 = new AlternateContent();
            alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");

            AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };

            X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Pivot\\" };
            absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");

            alternateContentChoice1.Append(absolutePath1);

            alternateContent1.Append(alternateContentChoice1);

            BookViews bookViews1 = new BookViews();
            WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };

            bookViews1.Append(workbookView1);

            Sheets sheets1 = new Sheets();
            Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };

            sheets1.Append(sheet1);

            DefinedNames definedNames1 = new DefinedNames();
            DefinedName definedName1 = new DefinedName(){ Name = "Query", LocalSheetId = (UInt32Value)0U, Hidden = true };
            definedName1.Text = "Sheet1!$B$2:$I$13";

            definedNames1.Append(definedName1);
            CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };

            PivotCaches pivotCaches1 = new PivotCaches();
            PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId2" };

            pivotCaches1.Append(pivotCache1);

            WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();

            WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{FCE2AD5D-F65C-4FA6-A056-5C36A1767C68}" };
            workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");

            X15.DataModel dataModel1 = new X15.DataModel();

            X15.ModelTables modelTables1 = new X15.ModelTables();
            X15.ModelTable modelTable1 = new X15.ModelTable(){ Id = "Query_7c41ad89-7105-4c9f-ab5a-881bd3e6a1b9", Name = "Query", Connection = "DAT105 Timestamp - Foodmart 2000 account" };

            modelTables1.Append(modelTable1);

            dataModel1.Append(modelTables1);

            workbookExtension1.Append(dataModel1);

            WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{69C81A23-63F3-4edf-8378-127667AE99B5}" };

            OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:workbookPr15 chartTrackingRefBase=\"1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");

            workbookExtension2.Append(openXmlUnknownElement1);

            workbookExtensionList1.Append(workbookExtension1);
            workbookExtensionList1.Append(workbookExtension2);

            workbook1.Append(fileVersion1);
            workbook1.Append(workbookProperties1);
            workbook1.Append(alternateContent1);
            workbook1.Append(bookViews1);
            workbook1.Append(sheets1);
            workbook1.Append(definedNames1);
            workbook1.Append(calculationProperties1);
            workbook1.Append(pivotCaches1);
            workbook1.Append(workbookExtensionList1);

            workbookPart1.Workbook = workbook1;
        }

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