NPOI 导入EXECEL数据包括每行有图片

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data;
using System.IO;
namespace Maticsoft.Common
{
    public class ExcelHelperNPOI
    {
        /// <summary>
        /// 利用NPOI对Excel数据做导入导出处理（mhw）
        /// </summary>
        public ExcelHelperNPOI()
        {

        }
        readonly int EXCEL2003_MaxRowNum = 65534;
        /// <summary>
        /// 将datatable中的数据导出到excel中
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="fileDir">存储Excel的文件夹的绝对路径</param>
        /// <returns>excel的绝对路径</returns>
        public string DataTableToExcel(DataTable dt, string sheetName, string fileDir, string excelName)
        {
            string excelPath = "";

            HSSFWorkbook workbook = new HSSFWorkbook();
            if (dt != null && dt.Rows.Count > 0)
            {
                DataTableToWorkbook(dt, sheetName, workbook);
            }
            excelName = sheetName + excelName + ".xls";
            if (fileDir != null && fileDir != "")
            {
                if (!Directory.Exists(fileDir))//如果文件加不存在，则创建一个
                    Directory.CreateDirectory(fileDir);
                excelPath = Path.Combine(fileDir, excelName);
                if (File.Exists(excelPath))
                    File.Delete(excelPath);

                using (Stream stream = File.OpenWrite(excelPath))
                {
                    workbook.Write(stream);
                }

            }
            return excelPath;
        }
        /// <summary>
        /// 导出Excel文件，自动返回可下载的文件流 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        public void DataTableToExcel(DataTable dt, string sheetName)
        {
            string excelPath = "";
            string excelName = "";
            string fileDir = "";
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (dt != null && dt.Rows.Count > 0)
            {
                DataTableToWorkbook(dt, sheetName, workbook);
            }
            excelName = sheetName + excelName + ".xls";
            if (fileDir != null && fileDir != "")
            {
                if (!Directory.Exists(fileDir))//如果文件加不存在，则创建一个
                    Directory.CreateDirectory(fileDir);
                excelPath = Path.Combine(fileDir, excelName);
                if (File.Exists(excelPath))
                    File.Delete(excelPath);

                using (Stream stream = File.OpenWrite(excelPath))
                {
                    workbook.Write(stream);
                }

            }
        }
        /// <summary>
        /// 将datatable中的数据写入workbook
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetName"></param>
        /// <param name="workbook"></param>
        private void DataTableToWorkbook(DataTable dt, string sheetName, HSSFWorkbook workbook)
        {
            if (sheetName == null || sheetName == "")
                sheetName = "sheet";
            if (dt.Rows.Count < EXCEL2003_MaxRowNum)
                WriteDataToSheet(dt, 0, dt.Rows.Count - 1, workbook, sheetName);
            else
            {
                int sheetNum = dt.Rows.Count / EXCEL2003_MaxRowNum;
                for (int i = 0; i < sheetNum; i++)
                {
                    int startRowIndex = i * EXCEL2003_MaxRowNum;
                    int endRowIndex = startRowIndex + EXCEL2003_MaxRowNum - 1;
                    WriteDataToSheet(dt, startRowIndex, endRowIndex, workbook, sheetName + i.ToString());
                }
                int lastSheetItemCount = dt.Rows.Count % EXCEL2003_MaxRowNum;
                if (lastSheetItemCount > 0)
                    WriteDataToSheet(dt, dt.Rows.Count - lastSheetItemCount, dt.Rows.Count - 1, workbook, sheetName + sheetNum.ToString());
            }
        }
        /// <summary>
        /// 将datatable中的数据添加到sheet中，如果数据量大于65534，则将多余的数据添加到第二个sheet中
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="endRowIndex"></param>
        /// <param name="workbook"></param>
        /// <param name="sheetName"></param>      
        private void WriteDataToSheet(DataTable dt, int startRowIndex, int endRowIndex, HSSFWorkbook workbook, string sheetName)
        {
            ISheet sheet;
            sheet = workbook.CreateSheet(sheetName);
            int rowExcelIndex = 0;
            //将datatable中的数据导入到excel中
            #region 创建标题行
            IRow rowTitle = sheet.CreateRow(rowExcelIndex++);
            IFont fontTitle = workbook.CreateFont();//创建标题字体样式
            fontTitle.Color = HSSFColor.Red.Index;
            fontTitle.FontHeightInPoints = 20;
            fontTitle.Boldweight = 20;
            fontTitle.FontName = "宋体";

            ICellStyle cellTitleStyle = workbook.CreateCellStyle();//创建标题单元格样式
            cellTitleStyle.SetFont(fontTitle);
            cellTitleStyle.BorderBottom = BorderStyle.Thin;
            cellTitleStyle.BorderLeft = BorderStyle.Thin;
            cellTitleStyle.BorderRight = BorderStyle.Thin;
            cellTitleStyle.BorderTop = BorderStyle.Thin;
            cellTitleStyle.Alignment = HorizontalAlignment.Center;

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); //合并第一行（标题行）单元格
            ICell cellTitle = rowTitle.CreateCell(0, CellType.String);  //创建标题所在的单元格cell
            cellTitle.CellStyle = cellTitleStyle;
            cellTitle.SetCellValue(sheetName);
            #endregion

            #region 创建表头行
            IRow rowHeader = sheet.CreateRow(rowExcelIndex++);//创建表头行
            //创建表头行样式
            ICellStyle rowHeaderCellStyle = workbook.CreateCellStyle();
            IFont rowHeaderFont = workbook.CreateFont();
            rowHeaderFont.Boldweight = short.MaxValue;
            rowHeaderCellStyle.BorderBottom = BorderStyle.Thin;
            rowHeaderCellStyle.BorderLeft = BorderStyle.Thin;
            rowHeaderCellStyle.BorderRight = BorderStyle.Thin;
            rowHeaderCellStyle.BorderTop = BorderStyle.Thin;
            rowHeaderCellStyle.SetFont(rowHeaderFont);

            //设置表头行内容
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                string header = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
                ICell rowHeaderCell = rowHeader.CreateCell(i, CellType.String);
                rowHeaderCell.CellStyle = rowHeaderCellStyle;
                rowHeaderCell.SetCellValue(header);
                //设置每一列的宽度
                if (dt.Columns[i].MaxLength < 11)
                    sheet.SetColumnWidth(i, 10 * 256);
                else if (dt.Columns[i].MaxLength > 80)
                    sheet.SetColumnWidth(i, 80 * 256);
                else
                    sheet.SetColumnWidth(i, dt.Columns[i].MaxLength * 256);
            }
            #endregion

            #region 向excel中填充内容
            ICellStyle rowContentCellStyle = workbook.CreateCellStyle();
            rowContentCellStyle.BorderBottom = BorderStyle.Thin;
            rowContentCellStyle.BorderLeft = BorderStyle.Thin;
            rowContentCellStyle.BorderRight = BorderStyle.Thin;
            rowContentCellStyle.BorderTop = BorderStyle.Thin;
            for (int rowIndex = startRowIndex; rowIndex <= endRowIndex; rowIndex++)
            {
                IRow row = sheet.CreateRow(rowExcelIndex++);
                for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
                {
                    ICell contentCell = row.CreateCell(colIndex, CellType.String);
                    contentCell.CellStyle = rowContentCellStyle;
                    contentCell.SetCellValue(dt.Rows[rowIndex][colIndex].ToString());
                }
            }
            #endregion
        }
        /// 将客户端上传的excel文件转换成datatable
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public DataTable ConvertToDataTable(string fileName, int sheetIndex)
        {
            DataTable dt = new DataTable();
            HSSFWorkbook workbook = null;
            string fileExtension = Path.GetExtension(fileName);
            if (fileExtension == ".xls")
            {

                using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    workbook = new HSSFWorkbook(file);
                    if (workbook != null && workbook.NumberOfSheets > 0)
                        dt = ConvertToDataTable(workbook, sheetIndex);
                }
            }


            return dt;
        }

        /// <summary>
        /// 将workbook中的内容转换成datatable
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private DataTable ConvertToDataTable(HSSFWorkbook workbook, int sheetIndex)
        {
            ISheet sheet = workbook.GetSheetAt(sheetIndex);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            //最后一列的标号  即总的行数
            int rowCount = sheet.LastRowNum;
            //获取sheet的首行
            IRow headerRow = sheet.GetRow(0);
            //一行最后一个方格的编号 即总的列数
            int cellCount = headerRow.LastCellNum;

            #region 获取EXCEL中的图片
            List<PicturesInfo> pictures = NpoiExtend.GetAllPictureInfos(sheet);

            foreach (PicturesInfo pic in pictures)
            {
                int minrow = pic.MinRow; int maxrow = pic.MaxRow;
                int mincol = pic.MinCol; int maxcol = pic.MaxCol;
                

            }
            #endregion

            DataTable dt = null;
            while (rows.MoveNext())
            {
                IRow row = (HSSFRow)rows.Current;
                if (row.RowNum == 0)   //初始化dt，并添加列
                {
                    dt = new DataTable();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        dt.Columns.Add(row.Cells[i].ToString());
                    }
                    continue;
                }
                if (dt != null)
                {
                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null || cell.ToString() == "")
                        {
                            dr[i] = DBNull.Value;
                        }
                        else
                        {
                            if (cell.CellType.ToString() == "NUMERIC")
                                dr[i] = cell.NumericCellValue.ToString();
                            else
                                dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }
    }
    public class PicturesInfo
    {
        public string ext { get; set; }
        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, string ext)
        {
            this.MinRow = minRow;
            this.MaxRow = maxRow;
            this.MinCol = minCol;
            this.MaxCol = maxCol;
            this.PictureData = pictureData;
            this.ext = ext;
        }
    }
    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 && 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,picture.PictureData.MimeType));
                        }
                    }
                }
            }

            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,picture.PictureData.MimeType));
                            }
                        }
                    }
                }
            }

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