Epplus SetPosition图片问题 [英] Epplus SetPosition picture issue

问题描述

我正在使用文件中找到此部分代码。 （
xdr，twoCellAnchor，ExcelPackage.schemaSheetDrawings）;
colNode.AppendChild（drawNode）;

您可以轻松创建自己的这个dll副本。好消息是，您只需修改两个文件来解决此问题。所以..

从

我们需要生成绘图的代码作为 oneCellAnchor ，所以我们必须删除图片的< xdr：to> 元素，并创建元素< xdr：ext /> c $ c>图片尺寸作为参数。

新的xml结构将如下所示：

 < xdr：oneCellAnchor editAs =oneCell> 
< xdr：from> ...< / xdr：from> 
< xdr：ext cx =1234567cy =7654321/> 
< xdr：pic> 
 ... 
< / xdr：oneCellAnchor> 
  

好的，怎么做？

Epplus代码中的更改

ExcelDrawings.cs （链接到这里的文件）

1. 我们首先在 ExcelDrawings.cs 内修改方法 CreateDrawingXml（）。为了保留原始功能，我们添加一个可选参数（如果创建 oneCellAnchor ）具有默认值。在方法中，根据此参数，我们创建一个或者拖动的单元格锚点，并创建或不创建< xdr：to> 元素。






此方法代码的重要部分：

  private XmlElement CreateDrawingXml（bool twoCell = true）{
 if（DrawingXml.OuterXml ==）
 {...} //未更改
 XmlNode colNode = _drawingsXml.SelectSingleNode（// xdr：wsDr NameSpaceManager）; 
 //方法代码的第一个变化
 XmlElement drawNode; 
 if（twoCell）
 drawNode = _drawingsXml.CreateElement（
xdr，twoCellAnchor，ExcelPackage.schemaSheetDrawings）; 
 else 
 drawNode = _drawingsXml.CreateElement（
xdr，oneCellAnchor，ExcelPackage.schemaSheetDrawings）; 
 colNode.AppendChild（drawNode）; 
 
 //从位置添加元素; //未更改
 XmlElement fromNode = _drawingsXml.CreateElement（
xdr，from，ExcelPackage.schemaSheetDrawings）; 
 drawNode.AppendChild（fromNode）; 
 fromNode.InnerXml =< xdr：col> 0< / xdr：col>< xdr：colOff> 0< / xdr：colOff> 
 +< xdr：row> 0< / xdr：row>< xdr：rowOff> 0< / xdr：rowOff>; 
 
 //添加到位置元素; 
 //方法的第二个变化
如果（twoCell）
 {
 XmlElement toNode = _drawingsXml.CreateElement（
xdr，to，ExcelPackage.schemaSheetDrawings） ; 
 drawNode.AppendChild（toNode）; 
 toNode.InnerXml =< xdr：col> 10< / xdr：col>< xdr：colOff> 0< / xdr：colOff> 
 +< xdr：row> 10< / xdr：row>< xdr：rowOff> 0< / xdr：rowOff>; 
} 
 return drawNode; 
} 
  

然后我们为 AddPicture 在同一个文件中：

  public ExcelPicture AddPicture（string Name，Image image，Uri Hyperlink）
 {
 if（image！= null）{
 if（_drawingNames.ContainsKey（Name.ToLower（）））{
抛出新的异常（名称已经存在于图形集合）; 
} 
 XmlElement drawNode = CreateDrawingXml（false）; 
 //更改：我们需要创建元素，尺寸为
 // like：< xdr：ext cx =3857625cy =1047750/> 
 XmlElement xdrext = _drawingsXml.CreateElement（
xdr，ext，ExcelPackage.schemaSheetDrawings）; 
 xdrext.SetAttribute（cx，
（image.Width * ExcelDrawing.EMU_PER_PIXEL）.ToString（））; 
 xdrext.SetAttribute（cy，
（image.Height * ExcelDrawing.EMU_PER_PIXEL）.ToString（））; 
 drawNode.AppendChild（xdrext）; 
 //更改结束，下一部分方法是一样的：
 drawNode.SetAttribute（editAs，oneCell）; 
 ... 
} 
} 
  

FileInfo 作为输入参数：

  public ExcelPicture AddPicture（string Name，FileInfo ImageFile，Uri Hyperlink）
 {
 if（ImageFile！= null）{
 if（_drawingNames.ContainsKey（Name.ToLower（）））{
 throw new Exception已经存在于图纸集）; 
} 
 XmlElement drawNode = CreateDrawingXml（false）; 
 //更改：首先创建ExcelPicture对象并计算EMU维度
 ExcelPicture pic = new ExcelPicture（this，drawNode，ImageFile，Hyperlink）; 
 XmlElement xdrext = _drawingsXml.CreateElement（
xdr，ext，ExcelPackage.schemaSheetDrawings）; 
 xdrext.SetAttribute（cx，
（pic.Image.Width * ExcelDrawing.EMU_PER_PIXEL）.ToString（））; 
 xdrext.SetAttribute（cy，
（pic.Image.Height * ExcelDrawing.EMU_PER_PIXEL）.ToString（））; 
 drawNode.AppendChild（xdrext）; 
 //更改结束，下一部分方法是一样的（没有创建pic对象）
 drawNode.SetAttribute（editAs，oneCell）; 
 ... 
} 
} 
  

所以，这是所有重要的代码。现在我们必须更改搜索节点的代码并保持元素中的顺序。





在 private void AddDrawings（） xpath from：

  XmlNodeList list = _drawingsXml.SelectNodes（
// xdr：twoCellAnchor，NameSpaceManager）; 
  

到这个

：


$ pre> XmlNodeList list = _drawingsXml.SelectNodes（
//（xdr：twoCellAnchor or xdr：oneCellAnchor），NameSpaceManager）;


全部在这个文件中，现在我们更改了

ExcelPicture .cs （此处链接到文件）





原始代码查找用于在构造函数中附加下一个代码的节点，如下所示：

  node.SelectSingleNode（ XDR：于，NameSpaceManager）; 
  

因为我们不创建< xdr：to> 元素总是，我们更改这个代码：

 内部ExcelPicture（ExcelDrawings drawing，XmlNode node 
，Image image ，Uri超链接）
：base（drawings，node，xdr：pic / xdr：nvPicPr / xdr：cNvPr / @ name）
 {
 XmlElement picNode = node.OwnerDocument.CreateElement 
xdr，pic，ExcelPackage.schemaSheetDrawings）; 
 //编辑：找到xdr：to或xdr：ext如果xdr：不存在
 XmlNode befor = node.SelectSingleNode（xdr：to，NameSpaceManager）; 
 if（befor！= null&&&forfor.Name ==xdr：to）
 node.InsertAfter（picNode，befor）; 
 else {
 befor = node.SelectSingleNode（xdr：ext，NameSpaceManager）; 
 node.InsertAfter（picNode，befor）; 
} 
 //更改结束，下一部分构造函数不变
 _hyperlink =超链接; 
 ... 
} 
  

与FileInfo的第二个构造函数相同输入参数：

 内部ExcelPicture（ExcelDrawings图纸，XmlNode节点
，FileInfo imageFile，Uri超链接）
 ：base（drawing，node，xdr：pic / xdr：nvPicPr / xdr：cNvPr / @ name）
 {
 XmlElement picNode = node.OwnerDocument.CreateElement（
xdr pic，ExcelPackage.schemaSheetDrawings）; 
 //编辑：找到xdr：to或xdr：ext如果xdr：不存在
 XmlNode befor = node.SelectSingleNode（xdr：to，NameSpaceManager）; 
 if（befor！= null&&&forfor.Name ==xdr：to）
 node.InsertAfter（picNode，befor）; 
 else {
 befor = node.SelectSingleNode（xdr：ext，NameSpaceManager）; 
 node.InsertAfter（picNode，befor）; 
} 
 //更改结束，下一部分构造函数不变
 _hyperlink =超链接; 
 ... 
  

现在，照片创建为 oneCellAnchor 。如果需要，您可以为展位变体创建多个 AddPicture 方法。最后一步是构建此项目并创建自己的定制 EPPlus.dll 。然后关闭使用这个DLL的项目，并复制新文件 EPPlus.dll ， EPPlus.pdb ， EPPlus.XML 在您的项目（备份和替换您的原始dll文件）在同一个地方（所以您不需要在项目引用或设置任何更改）。

然后打开并重建项目，并尝试如果这可以解决您的问题。




I am using Epplus library to generate Excel 2010 and up compatible files in Asp.Net C#. I am using version 3.1.2 which is the latest at this moment.

I am setting the row height first, before adding any pictures like this:

ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("sheet 1");
while (i < dt.Rows.Count + offset)
{
    ws.Row(i).Height = 84;
    i++;
}

dt is my DataTable with DataRows. After setting the height, I am looping again through the rows to add the pictures

while (i < dt.Rows.Count + offset)
{
    var prodImg = ws.Drawings.AddPicture(dr["code"].ToString(), new FileInfo(path));
    prodImg.SetPosition(i - 1, 0, 14, 0);
    prodImg.SetSize(75);
}

This works, but this does not:

var prodImg = ws.Drawings.AddPicture(dr["code"].ToString(), new FileInfo(path));
int w = prodImg.Image.Width;
int h = prodImg.Image.Height;

if (h > 140) // because height of 84 is 140 pixels in excel
{
    double scale = h / 140.0;
    w = (int)Math.Floor(w / scale);
    h = 140;
}

int xOff = (150 - w) / 2;
int yOff = (140 - h) / 2;

prodImg.SetPosition(i - 1, xOff, 11, yOff);
prodImg.SetSize(w, h);

This results in off center pictures and unresized images. And this code then which is in the same loop:

var prodImgDm = ws.Drawings.AddPicture("bcdm" + dr["code"].ToString(), new FileInfo(pathDm));
prodImgDm.SetPosition(i - 1, 25, 15, 40);
prodImgDm.SetSize(100);

This does work sometimes. the pictures prodImgDm are datamatrix images with a static width and height and do not need to be resized because they are always small/tiny. So also without the SetSize in some rows, it works and in some other rows, it does not work. Really strange because the code is the same. It might be something in the library and/or Excel. Perhaps I am using it wrong? Any epplus picture expert?

Thanks in advance!!

edit sometimes a picture is worth a thousand words, so here is the screenshot. As you can see the product images are not horizontal and vertical aligned in the cell. And the datamatrix on the far right is sometimes scaled about 120% even when I set SetSize(100) so it is really strange to me. So the last datamatrix has the correct size... I already found this SO thread but that does not help me out, I think.

edit 2013/04/09 Essenpillai gave me a hint to set

pck.DoAdjustDrawings = false;

but that gave me even stranger images:

the datamatrix is still changing on row basis. on row is ok, the other is not. and the ean13 code is too wide.

解决方案

I have the same problem with Epplus library.
After I find no solution how to solve this in my code, I checked source code of this library. Epplus create excel picture always as twoCellAnchor drawing. In xlsx files you can find drawingXYZ.xml with this code:

<xdr:twoCellAnchor editAs="oneCell">
  <xdr:from> ... </xdr:from>
  <xdr:to> ... </xdr:to>
  <xdr:pic>
  ...
</xdr:twoCellAnchor>

So, picture is always connected to two cells, and this is not variable part of Epplus library. You can find this part of code in ExcelDrawing.cs file.

  XmlElement drawNode = _drawingsXml.CreateElement(
    "xdr", "twoCellAnchor", ExcelPackage.schemaSheetDrawings);
  colNode.AppendChild(drawNode);

You can easy create your own copy of this dll. The good news is that you need to modify only two files to fix this problem. So..

Download your copy of source codes for Epplus library from this site and open in Visual Studio.

We need to generate code of drawing as oneCellAnchor, so we must remove <xdr:to> element for pictures and create element <xdr:ext /> with picture dimensions as parameters.
New xml structure will looks like:

<xdr:oneCellAnchor editAs="oneCell">
  <xdr:from> ... </xdr:from>
  <xdr:ext cx="1234567" cy="7654321" />
  <xdr:pic>
  ...
</xdr:oneCellAnchor>

Ok, so, how to do this?

Changes in Epplus code

ExcelDrawings.cs (link to file here)

1. At first we modify method CreateDrawingXml() inside ExcelDrawings.cs. Order to preserve the original functionality we add an optional parameter (if create oneCellAnchor) with default value. And in method, based this parameter, we create one or tow cell anchor and create or not <xdr:to> element.

Important part of this method code:

private XmlElement CreateDrawingXml(bool twoCell = true) { 
  if (DrawingXml.OuterXml == "") 
  { ... } // not changed
  XmlNode colNode= _drawingsXml.SelectSingleNode("//xdr:wsDr", NameSpaceManager);
  //First change in method code
  XmlElement drawNode;
  if (twoCell)
    drawNode = _drawingsXml.CreateElement(
      "xdr", "twoCellAnchor", ExcelPackage.schemaSheetDrawings);
  else
    drawNode = _drawingsXml.CreateElement(
      "xdr", "oneCellAnchor", ExcelPackage.schemaSheetDrawings);
  colNode.AppendChild(drawNode);

  //Add from position Element; // Not changed
  XmlElement fromNode = _drawingsXml.CreateElement(
    "xdr", "from", ExcelPackage.schemaSheetDrawings);
  drawNode.AppendChild(fromNode);
  fromNode.InnerXml = "<xdr:col>0</xdr:col><xdr:colOff>0</xdr:colOff>"
    + "<xdr:row>0</xdr:row><xdr:rowOff>0</xdr:rowOff>";

  //Add to position Element;
  //Second change in method
  if (twoCell)
  {
    XmlElement toNode = _drawingsXml.CreateElement(
      "xdr", "to", ExcelPackage.schemaSheetDrawings);
    drawNode.AppendChild(toNode);
    toNode.InnerXml = "<xdr:col>10</xdr:col><xdr:colOff>0</xdr:colOff>"
      + "<xdr:row>10</xdr:row><xdr:rowOff>0</xdr:rowOff>";
  }
  return drawNode;
}

Then we modify two methods for AddPicture inside the same file:

public ExcelPicture AddPicture(string Name, Image image, Uri Hyperlink)
{
  if (image != null) {
    if (_drawingNames.ContainsKey(Name.ToLower())) {
      throw new Exception("Name already exists in the drawings collection");
    }
    XmlElement drawNode = CreateDrawingXml(false);
    // Change: we need create element with dimensions
    // like: <xdr:ext cx="3857625" cy="1047750" />
    XmlElement xdrext = _drawingsXml.CreateElement(
      "xdr", "ext", ExcelPackage.schemaSheetDrawings);
    xdrext.SetAttribute("cx", 
      (image.Width * ExcelDrawing.EMU_PER_PIXEL).ToString());
    xdrext.SetAttribute("cy", 
      (image.Height * ExcelDrawing.EMU_PER_PIXEL).ToString());
    drawNode.AppendChild(xdrext);
    // End of change, next part of method is the same:
    drawNode.SetAttribute("editAs", "oneCell");
    ...
  }
}

And this method with FileInfo as input parameter:

public ExcelPicture AddPicture(string Name, FileInfo ImageFile, Uri Hyperlink)
{
  if (ImageFile != null) {
    if (_drawingNames.ContainsKey(Name.ToLower())) {
      throw new Exception("Name already exists in the drawings collection");
    }
    XmlElement drawNode = CreateDrawingXml(false);
    // Change: First create ExcelPicture object and calculate EMU dimensions
    ExcelPicture pic = new ExcelPicture(this, drawNode, ImageFile, Hyperlink);
    XmlElement xdrext = _drawingsXml.CreateElement(
      "xdr", "ext", ExcelPackage.schemaSheetDrawings);
    xdrext.SetAttribute("cx", 
      (pic.Image.Width * ExcelDrawing.EMU_PER_PIXEL).ToString());
    xdrext.SetAttribute("cy", 
      (pic.Image.Height * ExcelDrawing.EMU_PER_PIXEL).ToString());
    drawNode.AppendChild(xdrext);
    // End of change, next part of method is the same (without create pic object)
    drawNode.SetAttribute("editAs", "oneCell");
    ...
  }
}

So, this are all important code. Now we must change code for searching nodes and preserve order in elements.

In private void AddDrawings() we change xpath from:

XmlNodeList list = _drawingsXml.SelectNodes(
  "//xdr:twoCellAnchor", NameSpaceManager);

To this:

XmlNodeList list = _drawingsXml.SelectNodes(
  "//(xdr:twoCellAnchor or xdr:oneCellAnchor)", NameSpaceManager);

It is all in this file, now we change
ExcelPicture.cs (link to file here)

Original code find node for append next code in constructor like this:

node.SelectSingleNode("xdr:to",NameSpaceManager);

Because we do not create <xdr:to> element always, we change this code:

internal ExcelPicture(ExcelDrawings drawings, XmlNode node
  , Image image, Uri hyperlink) 
  : base(drawings, node, "xdr:pic/xdr:nvPicPr/xdr:cNvPr/@name")
{
  XmlElement picNode = node.OwnerDocument.CreateElement(
    "xdr", "pic", ExcelPackage.schemaSheetDrawings);
  // Edited: find xdr:to, or xdr:ext if xdr:to not exists
  XmlNode befor = node.SelectSingleNode("xdr:to",NameSpaceManager);
  if (befor != null && befor.Name == "xdr:to")
    node.InsertAfter(picNode, befor);
  else {
    befor = node.SelectSingleNode("xdr:ext", NameSpaceManager);
    node.InsertAfter(picNode, befor);
  }
  // End of change, next part of constructor is unchanged
  _hyperlink = hyperlink;
  ...
}

And the same for second constructor with FileInfo as input parameter:

internal ExcelPicture(ExcelDrawings drawings, XmlNode node
  , FileInfo imageFile, Uri hyperlink) 
  : base(drawings, node, "xdr:pic/xdr:nvPicPr/xdr:cNvPr/@name")
{
  XmlElement picNode = node.OwnerDocument.CreateElement(
    "xdr", "pic", ExcelPackage.schemaSheetDrawings);
  // Edited: find xdr:to, or xdr:ext if xdr:to not exists
  XmlNode befor = node.SelectSingleNode("xdr:to", NameSpaceManager);
  if (befor != null && befor.Name == "xdr:to")
    node.InsertAfter(picNode, befor);
  else {
    befor = node.SelectSingleNode("xdr:ext", NameSpaceManager);
    node.InsertAfter(picNode, befor);
  }
  // End of change, next part of constructor is unchanged
  _hyperlink = hyperlink;
  ...

Now, pictures are created as oneCellAnchor. If you want, you can create multiple AddPicture methods for booth variants. Last step is build this project and create your own custom EPPlus.dll. Then close your project which use this dll and copy new files EPPlus.dll, EPPlus.pdb, EPPlus.XML inside your project (backup and replace your original dll files) at the same place (so you don't need do any change in your project references or settings).
Then open and rebuild your project and try if this solve your problem.

这篇关于Epplus SetPosition图片问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！