NPOI学习

using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;

namespace NPOIDemo
{
    public class Excel
    {
        public HSSFWorkbook hssfWorkbook
        {
            get {
                FileStream file = new FileStream(@"D:\Source\NPOIDemo\NPOIDemo\Files\temp.xls", FileMode.Open, FileAccess.Read);

                return new HSSFWorkbook(file);
            }
        }

        /// <summary>
        /// 获取Sheet中所有数据有效性
        /// </summary>
        /// <returns></returns>
        public List<IDataValidation> GetDataValidations()
        {
            ISheet ws = hssfWorkbook.GetSheet("1");
            return ws.GetDataValidations();
        }

        /// <summary>
        /// 获取单元格数据有效性
        /// </summary>
        /// <param name="range">单元格范围 F71:F93或E5等</param>
        /// <returns></returns>
        public string[] GetExplicitListValuesByRange(string range)
        {
            ISheet ws = hssfWorkbook.GetSheet("1");
            var dataValidations = ws.GetDataValidations();
            foreach (var item in dataValidations)
            {
                foreach (var rangeAddresses in item.Regions.CellRangeAddresses)
                {
                    if (rangeAddresses.FormatAsString() == range)
                    {
                        return item.ValidationConstraint.ExplicitListValues;
                    }
                }
            }

            return new string[0];
        }

        /// <summary>
        /// 获取批注
        /// </summary>
        /// <returns></returns>
        public string GetCommont()
        {
            ISheet ws = hssfWorkbook.GetSheet("1");
            IComment commont=ws.GetCellComment(88, 55);

            return commont.String.String;
        }

        /// <summary>
        /// 提取图片及图片信息
        /// </summary>
        /// <returns></returns>
        public IList<PicturesInfo> GetPictures()
        {
            ISheet ws = hssfWorkbook.GetSheet("1");
            //return ws.Workbook.GetAllPictures();

            return ws.GetAllPictureInfos(365,370,9,20);
        }

    }
    
   
}

 

提取图片扩展方法来源：使用NPOI从Excel中提取图片及图片位置信息

public class PicturesInfo  
  {  
      public string ext { get; set; }  
      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, string ext)  
      {  
          this.MinRow = minRow;  
          this.MaxRow = maxRow;  
          this.MinCol = minCol;  
          this.MaxCol = maxCol;  
          this.PictureData = pictureData;  
          this.ext = ext;  
      }  
  }  
  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,picture.PictureData.MimeType));  
                      }  
                  }  
              }  
          }  
  
          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,picture.PictureData.MimeType));  
                          }  
                      }  
                  }  
              }  
          }  
  
          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)));  
          }  
      }  
  }