你的位置:首页 > ASP.net教程

[ASP.net教程]使用NPOI从Excel中提取图片及图片位置信息


问题背景:

话说,在ExcelReport的开发过程中,有一个比较棘手的问题:怎么复制图片呢?

当然,解决这个问题的第一步是:能使用NPOI提取到图片及图片的位置信息。到这里,一切想法都很顺利。但NPOI到底怎么提取图片及图片的位置信息呢?NPOI能不能提取到图片的位置信息呢?

这是两个问题。是两个让BaiGoogleDu几近沉默的问题。但官方教程的评论中还是流露出了答案的蛛丝马迹。

wx%0[j7z[`p]%r(6uf~es(4

哇咔咔,这是我去看源码寻答案的的动力。

此处省去(N多字)… …,这里宕掉了我三个晚上的时间。

不再累述,代码你懂的… ….

PicturesInfo.cs

public class PicturesInfo
{
  public int MinRow { get;set; }
  public int MaxRow { get;set; }
  public int MinCol { get;set; }
  public int MaxCol { get;set; }
  public Byte[] PictureData { get; private set; }
 
  public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol,Byte[] pictureData)
  {
    this.MinRow = minRow;
    this.MaxRow = maxRow;
    this.MinCol = minCol;
    this.MaxCol = maxCol;
    this.PictureData = pictureData;
  }
}


NpoiExtend.cs

public static class NpoiExtend
{
  public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet)
  {
    return sheet.GetAllPictureInfos(null,null,null,null);
  }
 
  public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet,int? minRow,int? maxRow,int? minCol,int? maxCol,bool onlyInternal = true)
  {
    if (sheet is HSSFSheet)
    {
      return GetAllPictureInfos((HSSFSheet)sheet,minRow,maxRow,minCol,maxCol,onlyInternal);
    }
    else if (sheet is XSSFSheet)
    {
      return GetAllPictureInfos((XSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
    }
    else
    {
      throw new Exception("未处理类型,没有为该类型添加:GetAllPicturesInfos()扩展方法!");
    }
  }
 
  private static List<PicturesInfo> GetAllPictureInfos(HSSFSheet sheet,int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
  {
    List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
 
    var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
    if (null != shapeContainer)
    {
      var shapeList = shapeContainer.Children;
      foreach (var shape in shapeList)
      {
        if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
        {
          var picture = (HSSFPicture)shape;
          var anchor = (HSSFClientAnchor)shape.Anchor;
          
          if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
          {
            picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
          }
        }
      }
    }
 
    return picturesInfoList;
  }
 
  private static List<PicturesInfo> GetAllPictureInfos(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
  {
    List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
 
    var documentPartList = sheet.GetRelations();
    foreach (var documentPart in documentPartList)
    {
      if (documentPart is XSSFDrawing)
      {
        var drawing = (XSSFDrawing)documentPart;
        var shapeList = drawing.GetShapes();
        foreach (var shape in shapeList)
        {
          if (shape is XSSFPicture)
          {
            var picture = (XSSFPicture)shape;
            var anchor = picture.GetPreferredSize();
            
            if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
            {
              picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
            }
          }
        }
      }
    }
 
    return picturesInfoList;
  }
 
  private static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
    int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
  {
    int _rangeMinRow = rangeMinRow ?? pictureMinRow;
    int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
    int _rangeMinCol = rangeMinCol ?? pictureMinCol;
    int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;
 
    if (onlyInternal)
    {
      return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
          _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
    }
    else
    {
      return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
      (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
    }
  }
}