C# WorksheetPart.GetIdOfPart方法代码示例

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

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

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