NPOI导入每行带图片的EXCEL

   

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

一、首先引用NPOI相关的dll

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

       三、定义照片信息类

 

 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;        }    }

四、计算照片的位置类

 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)                    {                        var picture = (HSSFPicture)shape;                       // var anchor = (HSSFClientAnchor)shape;                        var anchor = (HSSFClientAnchor)picture.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)));            }        }        }

五、应用

    public ActionResult GetTableFromExcel()        {            try            {                HttpFileCollectionBase file = Request.Files;                HttpPostedFileBase fileData = file[0];                if (fileData != null)                {                    if (fileData.ContentLength == 0)                    {                        return Content("{'success':'false','msg':'并无上传的文件'}");                    }                }                Stream streamfile = fileData.InputStream;                XSSFWorkbook hssfworkbook = new XSSFWorkbook(streamfile);//2007以上版本              //  HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);                NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);                DataTable table = new DataTable();                IRow headerRow = sheet.GetRow(0);//第一行为标题行                  int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells                  int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1                  //handling header.                  for (int i = headerRow.FirstCellNum; i < cellCount; i++)                {                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);                    table.Columns.Add(column);                }                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)                {                    IRow row = sheet.GetRow(i);                    DataRow dataRow = table.NewRow();                    if (row != null)                    {                        for (int j = row.FirstCellNum; j < cellCount; j++)                        {                            if (row.GetCell(j) != null)                                dataRow[j] = GetCellValue(row.GetCell(j));                        }                    }                    table.Rows.Add(dataRow);                }                List<BS_Identify> identify_list = new List<BS_Identify>();                for (int i = 0; i < table.Rows.Count; i++)                {                    BS_Identify identify = new BS_Identify();                                       identify.XM = table.Rows[i][0].ToString();                    //读取除了照片列以外的数据                    identify_list.Add(identify);                    //myUpLoadBLL.ForDownLoad(table.Rows[i][1].ToString(), table.Rows[i][2].ToString(),Convert.ToBoolean( table.Rows[i][3]));                  }                List<PicturesInfo> picture_list = new List<PicturesInfo>();                picture_list = NpoiExtend.GetAllPictureInfos(sheet, 1, rowCount, 0, cellCount, false);                foreach (var a in picture_list)                {                    identify_list[a.MinRow-1].Img = a.PictureData;                }                //操作读取的数据                return Content("{'success':'true','msg':''}");            }            catch(Exception ex)            {                return Content("{'success':'false','msg':"+ex.Message);            }        }        /// <summary>          /// 根据Excel列类型获取列的值          /// </summary>          /// <param name="cell">Excel列</param>          /// <returns></returns>          private static string GetCellValue(ICell cell)        {            if (cell == null)                return string.Empty;            switch (cell.CellType)            {                case CellType.Blank:                    return string.Empty;                case CellType.Boolean:                    return cell.BooleanCellValue.ToString();                case CellType.Error:                    return cell.ErrorCellValue.ToString();                case CellType.Numeric:                case CellType.Unknown:                default:                    return cell.ToString();                case CellType.String:                    return cell.StringCellValue;                case CellType.Formula:                    try                    {                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);                        e.EvaluateInCell(cell);                        return cell.ToString();                    }                    catch                    {                        return cell.NumericCellValue.ToString();                    }            }        }