ClosedXML添加图片

Question

我可以使用OpenXML将图像添加到Excel电子表格中。但是，对于程序的其余部分，我使用ClosedXML添加数据。 我可以使用列和行索引在特定单元格中添加数据。 如果我可以将图像添加到Excel（它目前似乎是一个独立的层，悬停在单元格上），如何使用ClosedXML将其添加到单元格中？

    //Adds an image to the excel file
    public void AddImageToExcel(SpreadsheetDocument sd, MemoryStream imagestream)
    {
        DrawingsPart dp = sd.WorkbookPart.WorksheetParts.First().AddNewPart<DrawingsPart>();
        ImagePart imgp = dp.AddImagePart(ImagePartType.Jpeg, sd.WorkbookPart.WorksheetParts.First().GetIdOfPart(dp));

        MemoryStream bmstream = new MemoryStream(imagestream.ToArray());
        bmstream.Seek(0, SeekOrigin.Begin);

        MemoryStream fs;
        using (fs = imagestream)
        {
            fs.Position = 0;
            imgp.FeedData(fs);
        }

        DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
        nvdp.Id = 1025;
        nvdp.Name = "Chart Image";
        nvdp.Description = "Image";
        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(bmstream);

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

        //The position corrosponds these numbers. X= 600000 & y = 200000 adds up to 1 cell
        pos.X = 600000;
        pos.Y = 200000;

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

        Xdr.Position pp = new Xdr.Position();
        pp.X = 0;
        pp.Y = 0;


        anchor.Position = pp;
        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);
        sd.WorkbookPart.WorksheetParts.First().Worksheet.Append(drawing);
        MessageBox.Show("Excel File created");
    }

这是我使用的代码，我在 stack overflow 的某个地方找到了它。我修改了它，用 MemoryStream 来处理图像。 首先，我的一个问题是我将电子表格文档传递给了该方法，但我不确定在 ClosedXML 中该怎么做才能解决这个问题。 非常感谢您对如何解决此问题的任何帮助。 理想情况下，我希望能够简单地说出：

ws.Cell(colnum, rownum).Value = AddImageToExcel(wb, ImageToMemoryStream(imagelocation));

感谢任何帮助！

- Ayohaych

Answer 1

ClosedXML现在具有基本的图像/图片支持。根据https://github.com/ClosedXML/ClosedXML/wiki/How-can-I-insert-an-image：

using (var wb = new XLWorkbook())
{
  var ws = wb.AddWorksheet("Sheet1");

  var imagePath = @"c:\path\to\your\image.jpg";
  var image = ws.AddPicture(imagePath)
      .MoveTo(ws.Cell("B3").Address)
      .Scale(.5); // optional: resize picture
      
  wb.SaveAs("file.xlsx");
}

- Francois Botha

---

谢谢您先生。以上代码是否有错误，imagePath 和 ImageLocation 应该是相同的吗？ - Zeek2

此外，是否可以指定实际的图像文件内容，例如作为ASP标记中的位置，例如“data：Image / PNG，....”？ - Zeek2

@Zeek2 请查看 AddPicture 的重载范围，网址为 https://github.com/ClosedXML/ClosedXML/blob/f2063a73cdd7c4d95f920de4241e95858e4b3b77/ClosedXML/Excel/IXLWorksheet.cs#L467 - Francois Botha

Answer 2

我有点晚了，但如果有人来找我，我已经为ClosedXML添加了（有限的）图像支持。

分叉可以在https://closedxml.codeplex.com/SourceControl/network/forks/ajwhiteway/ClosedXMLImageSupport中找到。

编辑：添加一些详细信息。

新类XLPicture和XLMarker已被添加。

图片可以通过以下方式创建。

  XLPicture pic = new XLPicture
  {
    NoChangeAspect = true,
    NoMove = true,
    NoResize = true,
    ImageStream = fIn,
    Name = "Test Image"
  };

目前仅支持流，不支持文件，并且仅输出JPEG格式的图片。如果有需求，可以进行更改。

创建图片后，您需要为其创建一个标记以确定其位置。

  XLMarker fMark = new XLMarker
  {
    ColumnId = 2,
    RowId = 2
  };
  pic.AddMarker(fMark);

如果您添加一个标记，它将直接嵌入到单元格中。如果您添加两个标记，它将跨越这两个标记的图像。

然后将其添加到工作表中就可以了。

 worksheet.AddPicture(pic);

大家好！

- Ajwhiteway

---

分支链接无效。你能分享一下你创建的DLL文件吗？感谢你的努力。 - Murat

Sure,https://drive.google.com/file/d/0BxZ-lcOB4nQTQjlOVjhhU3Eybmc/view?usp=sharing - Ajwhiteway

目前我只编写了对JPG的支持。尝试转换为JPG格式，看看是否有帮助。我认为添加PNG应该不会太难...我忘记了我会将流转换为位图，然后输出JPG文件，如果您可以附上Excel文件，我可以查看问题。 - Ajwhiteway

所以我有一个大致的想法。我认为它正在向工作表添加一个重复的绘图部件。我会看看是否能够纠正这个问题，并在我有空的时候重新编译DLL并再次发布。 - Ajwhiteway

请注意，现在的ClosedXML支持添加图像，不再需要@Ajwhiteway的分支。 ClosedXML API的文档在https://github.com/ClosedXML/ClosedXML/wiki/How-can-I-insert-an-image 上有描述。 - Francois Botha

Answer 3

这个特性从0.89.0版本开始已经被包含在闭合的XML中。

这段代码适用于MVC5，但你也可以在其他地方使用它。

        public ActionResult Contact()
    {
        //Creating excelsheet and adding workbook with name Picture
        XLWorkbook workbook = new XLWorkbook();
        IXLWorksheet worksheet = workbook.Worksheets.Add("Picture");

        //Adjusting size of the excel cell as per requirement
        worksheet.Column(3).Width = 60;
        worksheet.Row(3).Height = 50;

        var imagePath = @"~/Images/Picture2.png";

        //Adding image to the worksheet and moving it to the cell
        var image = worksheet.AddPicture(Server.MapPath(imagePath))
            .MoveTo(worksheet.Cell(3, 3).Address);
        image.Name = "Logo";

        //Scaling down image as per our cell size
        image.ScaleWidth(.5);
        image.ScaleHeight(.3);

        //Formating the cell with border and color
        worksheet.Cell(3, 3).Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
        worksheet.Cell(3, 3).Style.Border.OutsideBorderColor = XLColor.Blue;

        //asving worksheet in memory stream
        MemoryStream stream = new MemoryStream();
        workbook.SaveAs(stream);
        stream.Position = 0;
        //returning the final excelsheet with name Picture
        return new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        { FileDownloadName = "Pictures.xlsx" };
    }

您可以参考链接以获得详细的解释。希望能帮助到有需要的人。

https://www.youtube.com/watch?v=8X0kg0YzI2g&t=242s

- Utkarsh