最近做一个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; }
  8. public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, Byte[] pictureData)
  9. {
  10. this.MinRow = minRow;
  11. this.MaxRow = maxRow;
  12. this.MinCol = minCol;
  13. this.MaxCol = maxCol;
  14. this.PictureData = pictureData;
  15. }
  16. }

四、计算照片的位置类

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

五、应用

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