最近做一个EXCEL 导入的功能，导入一些人员的基本信息，其中一项是人员的照片，经查资料发现在EXCEL中照片和单元格之间并无对应的关系，即使照片在某个单元格内，所以在读取的时候照片那一列读出来的全是空，经研究查找后找到了一种解决的方式，通过计算得出照片在EXCEL 中的单元格，大概过程如下：

一、首先引用NPOI相关的dll

NPOI | 导入每行带图片的EXCEL - 图1

二、在程序中添加NPOI相关的命名空间

NPOI | 导入每行带图片的EXCEL - 图2

三、定义照片信息类

  1.  public class PicturesInfo
  2.     {
  3.         public int MinRow { get; set; }
  4.         public int MaxRow { get; set; }
  5.         public int MinCol { get; set; }
  6.         public int MaxCol { get; set; }
  7.         public Byte[] PictureData { get; private set; }
  9.         public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, Byte[] pictureData)
  10.         {
  11.             this.MinRow = minRow;
  12.             this.MaxRow = maxRow;
  13.             this.MinCol = minCol;
  14.             this.MaxCol = maxCol;
  15.             this.PictureData = pictureData;
  16.         }
  17.     }

四、计算照片的位置类

  1.  public static class NpoiExtend
  2.     {
  3.          public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet)
  4.         {
  5.             return sheet.GetAllPictureInfos(null, null, null, null);
  6.         }
  8.         public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal = true)
  9.         {
  10.             if (sheet is HSSFSheet)
  11.             {
  12.                 return GetAllPictureInfos((HSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
  13.             }
  14.             else if (sheet is XSSFSheet)
  15.             {
  16.                 return GetAllPictureInfos((XSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
  17.             }
  18.             else
  19.             {
  20.                 throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
  21.             }
  22.         }
  24.         private static List<PicturesInfo> GetAllPictureInfos(HSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
  25.         {
  26.             List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
  28.             var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
  29.             if (null != shapeContainer)
  30.             {
  31.                 var shapeList = shapeContainer.Children;
  32.                 foreach (var shape in shapeList)
  33.                 {
  34.                     if (shape is HSSFPicture)
  35.                     {
  36.                         var picture = (HSSFPicture)shape;
  37.                        // var anchor = (HSSFClientAnchor)shape;
  38.                         var anchor = (HSSFClientAnchor)picture.Anchor;
  39.                         if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
  40.                         {
  41.                             picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
  42.                         }
  43.                     }
  44.                 }
  45.             }
  47.             return picturesInfoList;
  48.         }
  50.         private static List<PicturesInfo> GetAllPictureInfos(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
  51.         {
  52.             List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();
  54.             var documentPartList = sheet.GetRelations();
  55.             foreach (var documentPart in documentPartList)
  56.             {
  57.                 if (documentPart is XSSFDrawing)
  58.                 {
  59.                     var drawing = (XSSFDrawing)documentPart;
  60.                     var shapeList = drawing.GetShapes();
  61.                     foreach (var shape in shapeList)
  62.                     {
  63.                         if (shape is XSSFPicture)
  64.                         {
  65.                             var picture = (XSSFPicture)shape;
  66.                             var anchor = picture.GetPreferredSize();
  68.                             if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
  69.                             {
  70.                                 picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
  71.                             }
  72.                         }
  73.                     }
  74.                 }
  75.             }
  77.             return picturesInfoList;
  78.         }
  80.         private static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
  81.             int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
  82.         {
  83.             int _rangeMinRow = rangeMinRow ?? pictureMinRow;
  84.             int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
  85.             int _rangeMinCol = rangeMinCol ?? pictureMinCol;
  86.             int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;
  88.             if (onlyInternal)
  89.             {
  90.                 return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
  91.                         _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
  92.             }
  93.             else
  94.             {
  95.                 return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
  96.                 (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
  97.             }
  98.         }
  100.     }

五、应用

  1.     public ActionResult GetTableFromExcel()
  2.         {
  3.             try
  4.             {
  5.                 HttpFileCollectionBase file = Request.Files;
  6.                 HttpPostedFileBase fileData = file[0];
  9.                 if (fileData != null)
  10.                 {
  11.                     if (fileData.ContentLength == 0)
  12.                     {
  13.                         return Content("{'success':'false','msg':'并无上传的文件'}");
  14.                     }
  15.                 }
  17.                 Stream streamfile = fileData.InputStream;
  19.                 XSSFWorkbook hssfworkbook = new XSSFWorkbook(streamfile);//2007以上版本
  20.               //  HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
  23.                 NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
  26.                 DataTable table = new DataTable();
  27.                 IRow headerRow = sheet.GetRow(0);//第一行为标题行  
  28.                 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells  
  29.                 int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1  
  30.                 //handling header.  
  31.                 for (int i = headerRow.FirstCellNum; i < cellCount; i++)
  32.                 {
  33.                     DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
  34.                     table.Columns.Add(column);
  35.                 }
  36.                 for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
  37.                 {
  38.                     IRow row = sheet.GetRow(i);
  39.                     DataRow dataRow = table.NewRow();
  40.                     if (row != null)
  41.                     {
  42.                         for (int j = row.FirstCellNum; j < cellCount; j++)
  43.                         {
  44.                             if (row.GetCell(j) != null)
  45.                                 dataRow[j] = GetCellValue(row.GetCell(j));
  46.                         }
  47.                     }
  48.                     table.Rows.Add(dataRow);
  49.                 }
  51.                 List<BS_Identify> identify_list = new List<BS_Identify>();
  52.                 for (int i = 0; i < table.Rows.Count; i++)
  53.                 {
  54.                     BS_Identify identify = new BS_Identify();
  56.                     identify.XM = table.Rows[i][0].ToString();
  57.                     //读取除了照片列以外的数据
  58.                     identify_list.Add(identify);
  59.                     //myUpLoadBLL.ForDownLoad(table.Rows[i][1].ToString(), table.Rows[i][2].ToString(),Convert.ToBoolean( table.Rows[i][3]));  
  60.                 }
  62.                 List<PicturesInfo> picture_list = new List<PicturesInfo>();
  63.                 picture_list = NpoiExtend.GetAllPictureInfos(sheet, 1, rowCount, 0, cellCount, false);
  66.                 foreach (var a in picture_list)
  67.                 {
  68.                     identify_list[a.MinRow-1].Img = a.PictureData;
  69.                 }
  71.                 //操作读取的数据
  72.                 return Content("{'success':'true','msg':''}");
  73.             }
  74.             catch(Exception ex)
  75.             {
  76.                 return Content("{'success':'false','msg':"+ex.Message);
  77.             }
  78.         }
  79.         /// <summary>  
  80.         /// 根据Excel列类型获取列的值  
  81.         /// </summary>  
  82.         /// <param name="cell">Excel列</param>  
  83.         /// <returns></returns>  
  84.         private static string GetCellValue(ICell cell)
  85.         {
  86.             if (cell == null)
  87.                 return string.Empty;
  88.             switch (cell.CellType)
  89.             {
  90.                 case CellType.Blank:
  91.                     return string.Empty;
  92.                 case CellType.Boolean:
  93.                     return cell.BooleanCellValue.ToString();
  94.                 case CellType.Error:
  95.                     return cell.ErrorCellValue.ToString();
  96.                 case CellType.Numeric:
  97.                 case CellType.Unknown:
  98.                 default:
  99.                     return cell.ToString();
  100.                 case CellType.String:
  101.                     return cell.StringCellValue;
  102.                 case CellType.Formula:
  103.                     try
  104.                     {
  105.                         HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
  106.                         e.EvaluateInCell(cell);
  107.                         return cell.ToString();
  108.                     }
  109.                     catch
  110.                     {
  111.                         return cell.NumericCellValue.ToString();
  112.                     }
  113.             }
  114.         }