获取excel中每行的图片

最近有个项目需要将excel中每行插入图片导入数据库。首次导图片参考了好多资料，今天把自己的整理一份，以供今后参考并和大家分享。

先定义一个图片实体类

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

excel插件扩展类

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) //2003
            {
                return GetAllPictureInfos((HSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
            }
            else if (sheet is XSSFSheet) //2007
            { 
                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)); 
                        } 
                    }
                } 
            } 
            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();
                            var anchor =(XSSFClientAnchor) shape.GetAnchor();
                            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)));
            }
        }
     
    }

应用测试

    #region 应用测试
        public static List<PicturesInfo> GetTableFromExcel(HttpPostedFile fileData)
        {
            try
            {
                
                if (fileData != null)
                {
                    if (fileData.ContentLength == 0)
                    {
                       
                        return null;
                    }
                }

                Stream streamfile = fileData.InputStream;

                XSSFWorkbook hssfworkbook = new XSSFWorkbook(streamfile);//2007以上版本  
              

                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+1; j <= cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = GetCellValue(row.GetCell(j));
                        }
                    }
                    table.Rows.Add(dataRow);
                }

              

                List<PicturesInfo> picture_list = new List<PicturesInfo>();
                picture_list = NpoiExtend.GetAllPictureInfos(sheet, 1, rowCount, 0, cellCount, false);
                return picture_list;

               
            }
            catch (Exception ex)
            {
                return null;
              
            }
        }
        /// <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();
                    }
            }
        }   
        #endregion