ClosedXML添加图像 [英] ClosedXML add image
问题描述
我可以使用OpenXML将图像添加到Excel电子表格中。
但是对于程序的其余部分,我使用ClosedXML来添加数据。
我可以使用列和行索引在特定单元格中添加数据。
如果我可以将图像添加到excel(它当前是一个单独的图层,悬停在单元格上),如何使用ClosedXML将其添加到单元格?
I am able to add in an image to an excel spreadsheet by using OpenXML. However for the rest of the program I use ClosedXML to add data. I can add data at a specific cell using the column and row index. If I can add an image to an excel (It currently is a separate layer it seems, hovering over cells), how can I add it to a cell using 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");
}
这是我使用的代码,我在这里找到堆栈溢出的代码。我修改它以使用MemoryStream作为图像。
首先,我遇到的一个问题是我将电子表格文档传递给方法,但是我不确定在ClosedXML中我可以做些什么来解决这个问题
真的很感激任何有关我的方法的帮助走吧。
理想情况下,我很乐意简单地说
This is the code I used, which I found here on stack overflow somewhere. I modified it to use a MemoryStream for the image. So firstly one of the problems I have is that I pass a spreadsheet document to the method, however I'm not sure what I can do in ClosedXML to fix this Really appreciate any help with how I could go about this. Ideally I would love to simply say
ws.Cell(colnum, rownum).Value = AddImageToExcel(wb, ImageToMemoryStream(imagelocation));
感谢您的帮助!
推荐答案
我有点迟到,但万一有人来看我已经为ClosedXML添加(有限的)图像支持。
I little bit late here, but in case someone else comes looking I've added (limited) image support to ClosedXML.
Fork可以在
找到 https://closedxml.codeplex.com/SourceControl / network / forks / ajwhiteway / ClosedXMLImageSupport
编辑:添加一些细节如何工作。
Adding some detail to how it works.
新类,XLPicture和XLMarker已被添加。
New classes, XLPicture and XLMarker, have been added.
图片可以通过
XLPicture pic = new XLPicture
{
NoChangeAspect = true,
NoMove = true,
NoResize = true,
ImageStream = fIn,
Name = "Test Image"
};
截至目前,它只接受流而不是文件,并且它只会吐出JPEG。如果有需求,可以更改此内容。
As of now it only accepts streams, not files, and it only spits out JPEGs. This can be changed if there is demand.
创建图片后,您可以为其创建标记
Once your picture is created you create a marker for where it goes
XLMarker fMark = new XLMarker
{
ColumnId = 2,
RowId = 2
};
pic.AddMarker(fMark);
如果添加单个标记,它会将图像直接嵌入到单元格中。如果您添加2个标记,它将跨越两个标记跨越图像。
If you add a single marker it will embed the image directly into the cell. If you add 2 markers it will span the image across the two markers.
然后将其添加到工作表中
Then to add it to the sheet just
worksheet.AddPicture(pic);
干杯人。
这篇关于ClosedXML添加图像的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!