NPOI 导出excel 终极方法（图片、合并单元格）

        /// <summary>
        /// 将datatable导出为excel
        /// 图片默认显示在excel 第二行最后一列
        /// </summary>
        /// <param name="table">数据源</param>
        /// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param>
        /// <param name="sheetName">工作簿名称</param>
        /// <param name="picBytes">导出图片字节流</param>
        /// <param name="mergedRegion">合并单元格信息：null不合并单元格</param>
        /// <returns></returns>
        public static MemoryStream ExportToExcel2007(DataTable table, List<Tuple<string, string, int>> excelInfo, string sheetName, byte[] picBytes, List<CellRangeAddress> mergedRegion)
        {
            MemoryStream ms = new MemoryStream();
            try
            {
                using (table)
                {
                    IWorkbook workbook = new XSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    for (int i = 0; i < excelInfo.Count; i++)
                    {
                        sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256);
                    }
                    IRow headerRow = sheet.CreateRow(0);
                    for (int i = 0; i < excelInfo.Count; i++)
                    {
                        headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1);
                    }
                    int rowIndex = 1;
                    foreach (DataRow row in table.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
                        for (int i = 0; i < excelInfo.Count; i++)
                        {
                            dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString());
                        }
                        rowIndex++;
                    }
                    //合并单元格
                    if (mergedRegion != null && mergedRegion.Count > 0)
                    {
                        foreach (CellRangeAddress cellRangeAddress in mergedRegion)
                        {
                            //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                            sheet.AddMergedRegion(cellRangeAddress);
                            ICellStyle style = workbook.CreateCellStyle();

                            //设置单元格的样式：水平对齐居中
                            style.Alignment = HorizontalAlignment.Center;
                            //将新的样式赋给单元格
                            var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
                            cell.CellStyle = style;
                        }
                    }
                    //插入图片
                    if (picBytes != null && picBytes.Length > 0)
                    {
                        var row1 = 2;
                        var col1 = excelInfo.Count + 1;
                        /* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
                        int pictureIdx = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG);  //添加图片
                        /* Create the drawing container */
                        XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                        /* Create an anchor point */
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 240, col1, row1, col1 + 1, row1 + 1);

                        /* Invoke createPicture and pass the anchor point and ID */
                        XSSFPicture picture =(XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
                        /* Call resize method, which resizes the image */
                        picture.Resize();

                        picBytes = null; 
                    }
                    workbook.Write(ms);
                    workbook.Close();
                }
            }
            catch (Exception ex)
            {
                ms = null;
            }
            return ms;
        }
        /// <summary>
        /// 将datatable导出为excel
        /// 图片默认显示在excel 第二行最后一列
        /// </summary>
        /// <param name="table">数据源</param>
        /// <param name="excelInfo">Tuple<excel列名,datatable列名,excel列宽度></param>
        /// <param name="sheetName">工作簿名称</param>
        /// <param name="picBytes">导出图片字节流</param>
        /// <param name="mergedRegion">合并单元格信息：null不合并单元格</param>
        /// <returns></returns>
        public static MemoryStream ExportToExcel97(DataTable table, List<Tuple<string, string, int>> excelInfo, string sheetName, byte[] picBytes, List<CellRangeAddress> mergedRegion)
        {
            MemoryStream ms = new MemoryStream();
            try
            {
                using (table)
                {
                    IWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    for (int i = 0; i < excelInfo.Count; i++)
                    {
                        sheet.SetColumnWidth(i, excelInfo[i].Item3 * 256);
                    }
                    IRow headerRow = sheet.CreateRow(0);
                    for (int i = 0; i < excelInfo.Count; i++)
                    {
                        headerRow.CreateCell(i).SetCellValue(excelInfo[i].Item1);
                    }
                    int rowIndex = 1;
                    foreach (DataRow row in table.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
                        for (int i = 0; i < excelInfo.Count; i++)
                        {
                            dataRow.CreateCell(i).SetCellValue(row[excelInfo[i].Item2].ToString());
                        }
                        rowIndex++;
                    }
                    //合并单元格
                    if (mergedRegion != null && mergedRegion.Count > 0)
                    {
                        foreach (CellRangeAddress cellRangeAddress in mergedRegion)
                        {
                            //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
                            //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                            sheet.AddMergedRegion(cellRangeAddress);
                            ICellStyle style = workbook.CreateCellStyle();

                            //设置单元格的样式：水平对齐居中
                            style.Alignment = HorizontalAlignment.Center;
                            //将新的样式赋给单元格
                            var cell = sheet.GetRow(cellRangeAddress.FirstRow).GetCell(cellRangeAddress.FirstColumn);
                            cell.CellStyle = style;
                        }

                    }
                    //插入图片
                    if (picBytes != null && picBytes.Length > 0)
                    {
                        var row1 = 2;
                        var col1 = excelInfo.Count + 1;
                        int pictureIdx = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG);  //添加图片

                        HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 240, col1, row1, col1 + 1, row1 + 1);

                        //图片位置，图片左上角为（col, row）
                        HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                        pict.Resize(); //用图片原始大小来显示
                        picBytes = null;
                    }
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                }
            }
            catch (Exception ex)
            {
                ms = null;
            }
            return ms;
        }