NPOI导入每行带图片的EXCEL

你的名字 2022-07-13 00:07 222阅读 0赞

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

一、首先引用NPOI相关的dll

![Center][]

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

![Center 1][]

三、定义照片信息类

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

[Center]: /images/20220713/b801b28bd6cb42038914702e804f620d.png
[Center 1]: /images/20220713/43ed88b8ff1140029677bd8d3e38cb29.png