C# WorksheetPart.AddNewPart方法代码示例

        // Adds child parts and generates content of the specified part.
        public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height)
        {
            List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList();
            uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current));

            uint tableId = maxTableId + 1;

            var tables = new TableParts { Count = 1U };
            worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables);

            var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>();
            string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart);

            string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height);
            var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false };
            var autoFilter1 = new AutoFilter { Reference = cellReference };

            var tableColumns1 = new TableColumns { Count = (uint)columns.Length };
            for (int iColumn = 0; iColumn < columns.Length; iColumn++)
            {
                var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] };
                tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn);
            }
            var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };

            table1.Append((IEnumerable<OpenXmlElement>)autoFilter1);
            table1.Append((IEnumerable<OpenXmlElement>)tableColumns1);
            table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1);

            newTableDefnPart.Table = table1;

            var table = new TablePart { Id = relationshipId };
            tables.Append((IEnumerable<OpenXmlElement>)table);

            //TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
            //worksheetPart.Worksheet.Append(tableStyles1);
        }

        private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context)
        {
            worksheetPart.Worksheet.RemoveAllChildren<TablePart>();

            if (!worksheet.Tables.Any()) return;

            foreach (var table in worksheet.Tables)
            {
                var tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook);

                var xlTable = (XLTable)table;
                xlTable.RelId = tableRelId;

                var tableDefinitionPart = worksheetPart.AddNewPart<TableDefinitionPart>(tableRelId);
                GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context);
            }
        }

        private void DeleteComments(WorksheetPart worksheetPart, XLWorksheet worksheet, SaveContext context)
        {
            // We have the comments so we can delete the comments part
            worksheetPart.DeletePart(worksheetPart.WorksheetCommentsPart);
            var vmlDrawingPart = worksheetPart.VmlDrawingParts.FirstOrDefault();

            // Only delete the VmlDrawingParts for comments.
            if (vmlDrawingPart != null)
            {
                var xdoc = XDocumentExtensions.Load(vmlDrawingPart.GetStream(FileMode.Open));
                //xdoc.Root.Elements().Where(e => e.Name.LocalName == "shapelayout").Remove();
                xdoc.Root.Elements().Where(
                    e => e.Name.LocalName == "shapetype" && (string)e.Attribute("id") == @"_x0000_t202").Remove();
                xdoc.Root.Elements().Where(
                    e => e.Name.LocalName == "shape" && (string)e.Attribute("type") == @"#_x0000_t202").Remove();
                var imageParts = vmlDrawingPart.ImageParts.ToList();
                var legacyParts = vmlDrawingPart.LegacyDiagramTextParts.ToList();
                var rId = worksheetPart.GetIdOfPart(vmlDrawingPart);
                worksheet.LegacyDrawingId = rId;
                worksheetPart.ChangeIdOfPart(vmlDrawingPart, "xxRRxx"); // Anything will do for the new relationship id
                // we just want it alive enough to create the copy

                var hasShapes = xdoc.Root.Elements().Any(e => e.Name.LocalName == "shape" || e.Name.LocalName == "group");

                VmlDrawingPart vmlDrawingPartNew = null;
                var hasNewPart = (imageParts.Count > 0 || legacyParts.Count > 0 || hasShapes);
                if (hasNewPart)
                {
                    vmlDrawingPartNew = worksheetPart.AddNewPart<VmlDrawingPart>(rId);

                    using (var writer = new XmlTextWriter(vmlDrawingPartNew.GetStream(FileMode.Create), Encoding.UTF8))
                    {
                        writer.WriteRaw(xdoc.ToString());
                    }

                    imageParts.ForEach(p => vmlDrawingPartNew.AddPart(p, vmlDrawingPart.GetIdOfPart(p)));
                    legacyParts.ForEach(p => vmlDrawingPartNew.AddPart(p, vmlDrawingPart.GetIdOfPart(p)));
                }

                worksheetPart.DeletePart(vmlDrawingPart);

                if (hasNewPart && rId != worksheetPart.GetIdOfPart(vmlDrawingPartNew))
                    worksheetPart.ChangeIdOfPart(vmlDrawingPartNew, rId);
            }
        }

//.........这里部分代码省略.........
        //    categoryAxisData1.AppendChild(stringReference2);

        //    C.Values values1 = new C.Values();

        //    C.NumberReference numberReference1 = new C.NumberReference();
        //    C.Formula formula3 = new C.Formula();
        //    formula3.Text = "Sheet1!$B$2:$B$3";

        //    C.NumberingCache numberingCache1 = new C.NumberingCache();
        //    C.FormatCode formatCode1 = new C.FormatCode();
        //    formatCode1.Text = "General";
        //    C.PointCount pointCount3 = new C.PointCount() { Val = (UInt32Value)2U };

        //    C.NumericPoint numericPoint1 = new C.NumericPoint() { Index = (UInt32Value)0U };
        //    C.NumericValue numericValue4 = new C.NumericValue();
        //    numericValue4.Text = "5";

        //    numericPoint1.AppendChild(numericValue4);

        //    C.NumericPoint numericPoint2 = new C.NumericPoint() { Index = (UInt32Value)1U };
        //    C.NumericValue numericValue5 = new C.NumericValue();
        //    numericValue5.Text = "10";

        //    numericPoint2.AppendChild(numericValue5);

        //    numberingCache1.AppendChild(formatCode1);
        //    numberingCache1.AppendChild(pointCount3);
        //    numberingCache1.AppendChild(numericPoint1);
        //    numberingCache1.AppendChild(numericPoint2);

        //    numberReference1.AppendChild(formula3);
        //    numberReference1.AppendChild(numberingCache1);

        //    values1.AppendChild(numberReference1);

        //    barChartSeries.AppendChild(index1);
        //    barChartSeries.AppendChild(order1);
        //    barChartSeries.AppendChild(seriesText1);
        //    barChartSeries.AppendChild(categoryAxisData1);
        //    barChartSeries.AppendChild(values1);
        //    C.Shape shape1 = new C.Shape() { Val = C.ShapeValues.Box };
        //    C.AxisId axisId1 = new C.AxisId() { Val = (UInt32Value)71429120U };
        //    C.AxisId axisId2 = new C.AxisId() { Val = (UInt32Value)71432064U };
        //    C.AxisId axisId3 = new C.AxisId() { Val = (UInt32Value)0U };

        //    bar3DChart.AppendChild(barChartSeries);
        //    bar3DChart.AppendChild(shape1);
        //    bar3DChart.AppendChild(axisId1);
        //    bar3DChart.AppendChild(axisId2);
        //    bar3DChart.AppendChild(axisId3);

        //    return bar3DChart;
        //}

        //private C.BarGroupingValues GetBarGrouping(XLChart xlChart)
        //{
        //    if (xlChart.BarGrouping == XLBarGrouping.Clustered)
        //        return C.BarGroupingValues.Clustered;
        //    else if (xlChart.BarGrouping == XLBarGrouping.Percent)
        //        return C.BarGroupingValues.PercentStacked;
        //    else if (xlChart.BarGrouping == XLBarGrouping.Stacked)
        //        return C.BarGroupingValues.Stacked;
        //    else
        //        return C.BarGroupingValues.Standard;
        //}

        //private C.BarDirectionValues GetBarDirection(XLChart xlChart)
        //{
        //    if (xlChart.BarOrientation == XLBarOrientation.Vertical)
        //        return C.BarDirectionValues.Column;
        //    else
        //        return C.BarDirectionValues.Bar;
        //}
        //--

        private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart,
            XLWorksheet xlWorksheet,
            SaveContext context)
        {
            foreach (var pt in xlWorksheet.PivotTables)
            {
                var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook);

                var pivotTableCacheDefinitionPart = workbookPart.AddNewPart<PivotTableCacheDefinitionPart>(ptCdp);
                GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt);

                var pivotCaches = new PivotCaches();
                var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp};

                pivotCaches.AppendChild(pivotCache);

                workbookPart.Workbook.AppendChild(pivotCaches);

                var pivotTablePart =
                    worksheetPart.AddNewPart<PivotTablePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
                GeneratePivotTablePartContent(pivotTablePart, pt);

                pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
            }
        }

        /// <summary>
        /// Add the logo of the system
        /// </summary>
        /// <param name="logoPath">Path of the logo</param>
        /// <param name="worksheetPart">Worksheet Part</param>
        /// <returns>Drawing</returns>
        private static Drawing AddLogo(string logoPath, WorksheetPart worksheetPart)
        {
            string sImagePath = logoPath;
            DrawingsPart dp = worksheetPart.AddNewPart<DrawingsPart>();
            ImagePart imgp = dp.AddImagePart(ImagePartType.Png, worksheetPart.GetIdOfPart(dp));
            using (FileStream fs = new FileStream(sImagePath, FileMode.Open, FileAccess.Read))
            {
                imgp.FeedData(fs);
            }

            DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
            nvdp.Id = 1025;
            nvdp.Name = "Picture 1";
            nvdp.Description = "logo";
            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(sImagePath);
            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();
            picture.NonVisualPictureProperties = nvpp;
            picture.BlipFill = blipFill;
            picture.ShapeProperties = sp;

            DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position();
            pos.X = 18 * 914400  / 72;
            pos.Y = 28 * 914400 / 72;

            Extent ext = new Extent();
            ext.Cx = extents.Cx;
            ext.Cy = extents.Cy;
            AbsoluteAnchor anchor = new AbsoluteAnchor();
            anchor.Position = pos;
            anchor.Extent = ext;

            anchor.Append(picture);
            anchor.Append(new ClientData());
            WorksheetDrawing wsd = new WorksheetDrawing();
            wsd.Append(anchor);
            Drawing drawing = new Drawing();
            drawing.Id = dp.GetIdOfPart(imgp);

            wsd.Save(dp);
            return drawing;
        }

        private static Drawing GetLogo(ExcelType excelType, WorksheetPart wsp, out uint startIndex)
        {
            var dp = wsp.AddNewPart<DrawingsPart>();
            Drawing drawing;
            var sImagePath = GetLogoPath(excelType);
            if (string.IsNullOrEmpty(sImagePath))
            {
                startIndex = 1;
                return null;
            }
            startIndex = 8;
            //sorry, I must do it in this way, MEmory stream oesn't work in FeedData. There are several discussions in the internet about it.
            ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
            var bm = new Bitmap(Resource.CEEX_small);
            //var bm = new Bitmap(@"ExcelProvider\_img\CEEX_small.png");
            using (var ms = new MemoryStream())
            {
                bm.Save(ms,ImageFormat.Png);
                ms.Seek(0, 0);
                imgp.FeedData(ms);
            }

            var nvdp = new NonVisualDrawingProperties();
            nvdp.Id = 1025;
            nvdp.Name = "logo";
            nvdp.Description = "polymathlogo";
            var picLocks = new PictureLocks();
            picLocks.NoChangeAspect = true;
            picLocks.NoChangeArrowheads = true;
            var nvpdp = new NonVisualPictureDrawingProperties();
            nvpdp.PictureLocks = picLocks;
            var nvpp = new NonVisualPictureProperties();
            nvpp.NonVisualDrawingProperties = nvdp;
            nvpp.NonVisualPictureDrawingProperties = nvpdp;

            var stretch = new Stretch();
            stretch.FillRectangle = new FillRectangle();

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

            var t2d = new Transform2D();
            var offset = new Offset();
            offset.X = 0;
            offset.Y = 0;
            t2d.Offset = offset;
            //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
            var extents = new Extents();
            extents.Cx = bm.Width * (long)(914400 / bm.HorizontalResolution);
            extents.Cy = bm.Height * (long)(914400 / bm.VerticalResolution);
            bm.Dispose();
            t2d.Extents = extents;
            var sp = new ShapeProperties();
            sp.BlackWhiteMode = BlackWhiteModeValues.Auto;
            sp.Transform2D = t2d;
            var prstGeom = new PresetGeometry();
            prstGeom.Preset = ShapeTypeValues.Rectangle;
            prstGeom.AdjustValueList = new AdjustValueList();
            sp.Append(prstGeom);
            sp.Append(new NoFill());

            var picture = new Picture();
            picture.NonVisualPictureProperties = nvpp;
            picture.BlipFill = blipFill;
            picture.ShapeProperties = sp;

            var pos = new Position();
            pos.X = 0;
            pos.Y = 0;
            var ext = new Extent();
            ext.Cx = extents.Cx;
            ext.Cy = extents.Cy;
            var anchor = new AbsoluteAnchor();
            anchor.Position = pos;
            anchor.Extent = ext;
            anchor.Append(picture);
            anchor.Append(new ClientData());
            var wsd = new WorksheetDrawing();
            wsd.Append(anchor);
            drawing = new Drawing();
            drawing.Id = dp.GetIdOfPart(imgp);
            wsd.Save(dp);
            return drawing;
        }

        static void InsertImageTest(Sheet sheet, WorksheetPart sheetPart)
        {
            var filepath = System.IO.Path.GetFullPath(@"..\..\test.jpg");
            int widthPx = 100, heightPx = 100;
            int rowIndex = 2, colomnIndex = 3;
            int rowOffsetPx = 5, columnOffsetPx = 10;

            var imageType = ImagePartType.Jpeg;
            var noChangeAspect = true;
            var noCrop = false;
            var noMove = false;
            var noResize = false;
            var noRotation = false;
            var noSelection = false;

            float imageResX, imageResY;
            using (var bmp = Image.FromFile(filepath) as Bitmap)
            {
                imageResX = bmp.HorizontalResolution;
                imageResY = bmp.VerticalResolution;
            }
            var widthEmu = CalcEmuScale(widthPx, imageResX);
            var heightEmu = CalcEmuScale(heightPx, imageResY);
            var columnOffsetEmu = CalcEmuScale(columnOffsetPx, imageResY);
            var rowOffsetEmu = CalcEmuScale(rowOffsetPx, imageResX);

            var drawingsPart = sheetPart.DrawingsPart ?? sheetPart.AddNewPart<DrawingsPart>();

            if (!sheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
            {
                sheetPart.Worksheet.Append(new Drawing() { Id = sheetPart.GetIdOfPart(drawingsPart) });
            }

            if (drawingsPart.WorksheetDrawing == null)
            {
                drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
            }
            var sheetDrawing = drawingsPart.WorksheetDrawing;

            var imagePart = drawingsPart.AddImagePart(imageType);

            using (var stream = new FileStream(filepath, FileMode.Open))
            {
                imagePart.FeedData(stream);
            }

            var nvps = sheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>();
            var nvpId = (nvps.Count() > 0) ? (UInt32Value)nvps.Max(prop => prop.Id.Value) + 1 : 1U;

            var pictureLocks = new A.PictureLocks()
            {
                NoChangeAspect = noChangeAspect,
                NoCrop = noCrop,
                NoMove = noMove,
                NoResize = noResize,
                NoRotation = noRotation,
                NoSelection = noSelection,
            };

            var oneCellAnchor = new Xdr.OneCellAnchor(
                new Xdr.FromMarker()
                {
                    ColumnId = new Xdr.ColumnId((colomnIndex - 1).ToString()),
                    RowId = new Xdr.RowId((rowIndex - 1).ToString()),
                    ColumnOffset = new Xdr.ColumnOffset(columnOffsetEmu.ToString()),
                    RowOffset = new Xdr.RowOffset(rowOffsetEmu.ToString()),
                },

                new Xdr.Extent() { Cx = widthEmu, Cy = heightEmu, },

                new Xdr.Picture(
                    new Xdr.NonVisualPictureProperties(
                        new Xdr.NonVisualDrawingProperties() { Id = nvpId, Name = Path.GetFileName(filepath), Description = filepath, },
                        new Xdr.NonVisualPictureDrawingProperties(pictureLocks)
                        ),
                    new Xdr.BlipFill(
                        new A.Blip() { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print },
                        new A.Stretch(new A.FillRectangle())
                        ),
                    new Xdr.ShapeProperties(
                        new A.Transform2D(
                            new A.Offset() { X = 0, Y = 0, },
                            new A.Extents() { Cx = widthEmu, Cy = heightEmu }
                            ),
                        new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle }
                        )
                    ),
                new Xdr.ClientData());

            sheetDrawing.Append(oneCellAnchor);

            var errors = new DocumentFormat.OpenXml.Validation.OpenXmlValidator().Validate(sheetPart);
            foreach (var err in errors)
            {
                Console.WriteLine(err.Description);
            }
            if(errors.Count() > 0)
            {
                Console.Read();
            }
//.........这里部分代码省略.........

        // Creates a pivot table in the specified sheet using the specified range name
        // The new pivot table will not be configured with any fields in the rows, columns, filters or values
        public static PivotTablePart CreatePivotTable(SpreadsheetDocument document, string rangeName, WorksheetPart sheet)
        {
            int startRow, startColumn, endRow, endColumn;
            WorksheetPart sourceSheet = GetRange(document, rangeName, out startRow, out startColumn, out endRow, out endColumn);

            // Fill out pivotFields element (for PivotTablePart) and cacheFields element (for PivotTableCacheDefinitionPart)
            // with an element for each column in the source range
            XElement pivotFields = new XElement(S.pivotFields, new XAttribute(NoNamespace.count, (endColumn - startColumn + 1).ToString()));
            XElement cacheFields = new XElement(S.cacheFields, new XAttribute(NoNamespace.count, (endColumn - startColumn + 1).ToString()));
            for (int column = startColumn; column <= endColumn; column++)
            {
                pivotFields.Add(new XElement(S.pivotField, new XAttribute(NoNamespace.showAll, "0")));
                XElement sharedItems = new XElement(S.sharedItems);
                // Determine numeric sharedItems values, if any
                object value = GetCellValue(document, sourceSheet, column, startRow + 1);
                if (value is double || value is Int32)
                {
                    bool hasDouble = false;
                    double minValue = Convert.ToDouble(value);
                    double maxValue = Convert.ToDouble(value);
                    if (value is double)
                        hasDouble = true;
                    for (int row = startRow + 1; row <= endRow; row++)
                    {
                        value = GetCellValue(document, sourceSheet, column, row);
                        if (value is double)
                            hasDouble = true;
                        if (Convert.ToDouble(value) < minValue)
                            minValue = Convert.ToDouble(value);
                        if (Convert.ToDouble(value) > maxValue)
                            maxValue = Convert.ToDouble(value);
                    }
                    sharedItems.Add(new XAttribute(NoNamespace.containsSemiMixedTypes, "0"),
                        new XAttribute(NoNamespace.containsString, "0"), new XAttribute(NoNamespace.containsNumber, "1"),
                        new XAttribute(NoNamespace.minValue, minValue.ToString()), new XAttribute(NoNamespace.maxValue, maxValue.ToString()));
                    if (!hasDouble)
                        sharedItems.Add(new XAttribute(NoNamespace.containsInteger, "1"));
                }
                cacheFields.Add(new XElement(S.cacheField, new XAttribute(NoNamespace.name, GetCellValue(document, sourceSheet, column, startRow).ToString()),
                    new XAttribute(NoNamespace.numFmtId, "0"), sharedItems));
            }

            // Fill out pivotCacheRecords element (for PivotTableCacheRecordsPart) with an element
            // for each row in the source range
            XElement pivotCacheRecords = new XElement(S.pivotCacheRecords, new XAttribute("xmlns", S.s),
                new XAttribute(XNamespace.Xmlns + "r", R.r), new XAttribute(NoNamespace.count, (endRow - startRow).ToString()));
            for (int row = startRow + 1; row <= endRow; row++)
            {
                XElement r = new XElement(S.r);

                // Fill the record element with a value from each column in the source row
                for (int column = startColumn; column <= endColumn; column++)
                {
                    object value = GetCellValue(document, sourceSheet, column, row);
                    if (value is String)
                        r.Add(new XElement(S._s, new XAttribute(NoNamespace.v, value.ToString())));
                    else
                        r.Add(new XElement(S.n, new XAttribute(NoNamespace.v, value.ToString())));
                }
                pivotCacheRecords.Add(r);
            }

            // Create pivot table parts with proper links
            PivotTablePart pivotTable = sheet.AddNewPart<PivotTablePart>();
            PivotTableCacheDefinitionPart cacheDef = pivotTable.AddNewPart<PivotTableCacheDefinitionPart>();
            PivotTableCacheRecordsPart records = cacheDef.AddNewPart<PivotTableCacheRecordsPart>();
            document.WorkbookPart.AddPart<PivotTableCacheDefinitionPart>(cacheDef);

            // Set content for the PivotTableCacheRecordsPart and PivotTableCacheDefinitionPart
            records.PutXDocument(new XDocument(pivotCacheRecords));
            cacheDef.PutXDocument(new XDocument(new XElement(S.pivotCacheDefinition, new XAttribute("xmlns", S.s),
                new XAttribute(XNamespace.Xmlns + "r", R.r), new XAttribute(R.id, cacheDef.GetIdOfPart(records)),
                new XAttribute(NoNamespace.recordCount, (endRow - startRow).ToString()),
                    new XElement(S.cacheSource, new XAttribute(NoNamespace.type, "worksheet"),
                        new XElement(S.worksheetSource, new XAttribute(NoNamespace.name, rangeName))),
                    cacheFields)));

            // Create the pivotCache entry in the workbook part
            int cacheId = 1;
            XDocument wb = document.WorkbookPart.GetXDocument();
            if (wb.Root.Element(S.pivotCaches) == null)
                wb.Root.Add(new XElement(S.pivotCaches));
            else
            {
                if (wb.Root.Element(S.pivotCaches).Elements(S.pivotCache).Count() != 0)
                    cacheId = wb.Root.Element(S.pivotCaches).Elements(S.pivotCache).Max(n => Convert.ToInt32(n.Attribute(NoNamespace.cacheId).Value)) + 1;
            }
            wb.Root.Element(S.pivotCaches).Add(new XElement(S.pivotCache,
                new XAttribute(NoNamespace.cacheId, cacheId),
                new XAttribute(R.id, document.WorkbookPart.GetIdOfPart(cacheDef))));
            document.WorkbookPart.PutXDocument();

            // Set the content for the PivotTablePart
            pivotTable.PutXDocument(new XDocument(new XElement(S.pivotTableDefinition, new XAttribute("xmlns", S.s),
                new XAttribute(NoNamespace.name, "PivotTable1"), new XAttribute(NoNamespace.cacheId, cacheId.ToString()),
                new XAttribute(NoNamespace.dataCaption, "Values"),
                new XElement(S.location, new XAttribute(NoNamespace._ref, "A3:C20"),
                    new XAttribute(NoNamespace.firstHeaderRow, "1"), new XAttribute(NoNamespace.firstDataRow, "1"),
//.........这里部分代码省略.........

        private static void GeneratePicture(WorksheetPart worksheetPart, IXLPicture picture)
        {
            if (!File.Exists(picture.FilePath))
            {
                throw new ArgumentException("image file not found: " + picture.FilePath);
            }

            var drawingsPart = worksheetPart.DrawingsPart ?? worksheetPart.AddNewPart<DrawingsPart>();

            var worksheet = worksheetPart.Worksheet;
            if (!worksheet.ChildElements.OfType<Drawing>().Any())
            {
                var drawing = new Drawing()
                {
                    Id = worksheetPart.GetIdOfPart(drawingsPart),
                };
                worksheet.InsertBefore(drawing, worksheet.Last());
            }

            if (drawingsPart.WorksheetDrawing == null)
            {
                drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
            }
            var sheetDrawing = drawingsPart.WorksheetDrawing;

            var imagePart = drawingsPart.AddImagePart(XlPictureTypeConverter.Convert(picture.Type));
            int imageWidth, imageHeight;
            float imageResX, imageResY;
            using (var stream = new FileStream(picture.FilePath, FileMode.Open))
            {
                using (var bitmap = new System.Drawing.Bitmap(stream))
                {
                    imageWidth = bitmap.Width;
                    imageHeight = bitmap.Height;
                    imageResX = bitmap.HorizontalResolution;
                    imageResY = bitmap.VerticalResolution;
                }

                stream.Seek(0, SeekOrigin.Begin);
                imagePart.FeedData(stream);
            }

            if (picture.WidthPx == default(int))
            {
                picture.WidthPx = imageWidth;
            }
            if (picture.HeightPx == default(int))
            {
                picture.HeightPx = imageHeight;
            }

            var nvps = sheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>();
            var nvpId = (nvps.Count() > 0) ? (UInt32Value)nvps.Max(prop => prop.Id.Value) + 1 : 1U;

            var pictureLocks = new A.PictureLocks()
            {
                NoChangeAspect = !picture.CanUserChangeAspect,
                NoCrop = !picture.CanUserCrop,
                NoMove = !picture.CanUserMove,
                NoResize = !picture.CanUserResize,
                NoRotation = !picture.CanUserRotate,
                NoSelection = !picture.CanUserSelect,
            };

            OpenXmlCompositeElement cellAnchor;
            {
                var markers = picture.GetMarkers();
                var markerCount = markers.Count();
                if (markerCount == 0)
                {
                    cellAnchor = new Xdr.AbsoluteAnchor(new Xdr.Position() { X = 0, Y = 0 });
                }
                else if (markerCount == 1)
                {
                    cellAnchor = new Xdr.OneCellAnchor(
                        XLMarkerConverter.Convert<Xdr.FromMarker>(
                            markers.First(),
                            (x) => CalcEmuScale(x, imageResX),
                            (y) => CalcEmuScale(y, imageResY)));
                }
                else if (markerCount == 2)
                {
                    var from = markers.First();
                    var to = markers.Last();
                    if (
                        from.RowIndex > to.RowIndex ||
                        from.ColumnIndex > to.ColumnIndex ||
                        (from.RowIndex == to.RowIndex && from.RowOffsetPx > to.RowOffsetPx) ||
                        (from.ColumnIndex == to.ColumnIndex && from.ColumnOffsetPx > to.ColumnOffsetPx)
                        )
                    {
                        throw new ArgumentException("positions of second marker must be larger than those of first marker");
                    }

                    cellAnchor = new Xdr.TwoCellAnchor(
                        XLMarkerConverter.Convert<Xdr.FromMarker>(
                            from,
                            (x) => CalcEmuScale(x, imageResX),
                            (y) => CalcEmuScale(y, imageResY)),
                        XLMarkerConverter.Convert<Xdr.ToMarker>(
//.........这里部分代码省略.........