目标：.NET导出Excel文件（含多张图片）
前言
NPOI作为国人开发的开源项目，文档完善，更新及时，为.NET开发者提供了便利，主要用于生成Excel报表，搜索引擎模块中Excel中的文本提取，批量生成Excel文件，基于Excel文件模板生成新的Excel等多方面。
一、导出Excel图片
放在前面：我开发时遇到的最大问题是，导出的Excel仅显示最后一张图片，后面会给出解决方法
二、使用步骤
1.引用NPOI
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
2.代码
点击导出 调用的方法：
protected void Button3_Click(object sender, System.EventArgs e)
{
string sql = "执行的sql语句";
DataSet ds = DBFunction.GetDs("DataBaseDSN", sql);
if (ds.Tables[0].Rows.Count <= 0)
{
OtherFunction.Alert("没有检测到要导出的数据！", Label1);
return;
}
try{
ExportExcel(ds, "EXCEL文件名");
}
catch (Exception ex)
{
OtherFunction.Alert("数据导出失败!" + ex.ToString(), Label1);
}
}
NPOI导出Excel
public void ExportExcel(DataSet ds, string FileName)
{
DataTable dtXmlb = ds.Tables[0];
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet;
NPOI.SS.UserModel.Row row;
NPOI.SS.UserModel.Cell cell;
string sheetName;
try
{
HSSFPalette palette = wb.GetCustomPalette();
//第一行 标题行 字体样式
HSSFFont headFont = (HSSFFont)wb.CreateFont();
headFont.FontName = "等线";
headFont.FontHeightInPoints = 10;
headFont.Boldweight = (short)FontBoldWeight.BOLD;
//headFont.Color = palette.FindColor(0, 0, 255).GetIndex();
//第二行 详情行 字体样式
HSSFFont titleFont = (HSSFFont)wb.CreateFont();
titleFont.FontName = "等线";
titleFont.FontHeightInPoints = 10;
titleFont.Boldweight = (short)FontBoldWeight.BOLD;
// 为标题单元格添加样式
HSSFCellStyle headStyle = (HSSFCellStyle)wb.CreateCellStyle();
headStyle.VerticalAlignment = VerticalAlignment.CENTER;
headStyle.Alignment = HorizontalAlignment.CENTER;
headStyle.SetFont(headFont);
// 为详情单元格添加样式
HSSFCellStyle titleStyle = (HSSFCellStyle)wb.CreateCellStyle();
titleStyle.VerticalAlignment = VerticalAlignment.CENTER;
titleStyle.Alignment = HorizontalAlignment.LEFT;
titleStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
titleStyle.FillForegroundColor = palette.FindColor(204, 204, 255).GetIndex();
titleStyle.SetFont(titleFont);
HSSFCellStyle cellStyle = (HSSFCellStyle)wb.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
cellStyle.Alignment = HorizontalAlignment.CENTER;
int rowIndex = 0;
sheetName = "这里是sheet名字";
sheet = wb.CreateSheet(sheetName);
row = sheet.CreateRow(rowIndex);
row.Height = 650;
cell = row.CreateCell(0);
cell.SetCellValue("这里是标题行名字");
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 8));//合并居中(标题)
cell.CellStyle = headStyle;
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
for (int j = 1; j <= dtXmlb.Rows.Count; j++)
{
for (int k = 0; k < dtXmlb.Columns.Count; k++)
{
if (k == 0)//详情
{
if (j == 1)
{
rowIndex++;
}
else
{
rowIndex = rowIndex + 12;
}
NPOI.SS.UserModel.Row contentrow = sheet.CreateRow(rowIndex);
contentrow.Height = 500;
Cell contentcell = contentrow.CreateCell(k);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 8));//合并居中
contentcell.CellStyle = titleStyle;
contentcell.SetCellValue(dtXmlb.Rows[j - 1][k].ToString());
}
else if (k == 1)//图1
{
rowIndex++;
NPOI.SS.UserModel.Row contentrow = sheet.CreateRow(rowIndex);
Cell contentcell = contentrow.CreateCell(k);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 11, 0, 3));//合并居中
contentcell.CellStyle = cellStyle;
AddCellPicture(sheet, wb, patriarch, dtXmlb.Rows[j - 1][k].ToString(), rowIndex, rowIndex + 12, 0, 4);
}
else if (k == 2) //图2
{
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 11, 5, 8));//合并居中
AddCellPicture(sheet, wb, patriarch, dtXmlb.Rows[j - 1][k].ToString(), rowIndex, rowIndex + 12, 5, 9);
}
}
}
}
catch (Exception e)
{
OtherFunction.Alert("数据导出失败!" + e.ToString(), Label1);
return;
}
// 输出到Excel
using (MemoryStream ms = new MemoryStream())
{
wb.Write(ms);
System.Web.HttpContext.Current.Response.Clear();
FileName = (System.Web.HttpUtility.UrlEncode(FileName)).Replace("+", " ");
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", FileName + DateTime.Now.ToString("yyyyMMddHHmmssfff")));
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
wb = null;
ms.Close();
ms.Dispose();
System.Web.HttpContext.Current.Response.End();
}
}
导出图片的方法：
private void AddCellPicture(NPOI.SS.UserModel.Sheet sheet, HSSFWorkbook workbook, HSSFPatriarch patriarch, string fileUrl, int firstRow, int lastRow,int firstCol,int lastCol)
{
try
{
//校验地址
if (fileUrl == null || fileUrl == "" || fileUrl.IndexOf("/") == -1 || fileUrl.IndexOf(".") == -1)
{
return;
}
string[] arr = fileUrl.Split(new Char[] { '/' });
string fileName = arr[arr.Length - 1];
string fileLocation = HttpContext.Current.Server.MapPath("~/download");
string fullPath = Path.Combine(fileLocation, Path.GetFileName(fileName));
//本地没有，下载文件保存到本地
if (!File.Exists(fullPath))
{
WebClient webClient = new WebClient();
webClient.DownloadFile(fileUrl, fullPath);
}
//从本地取
if (!string.IsNullOrEmpty(fullPath))
{
byte[] bytes = System.IO.File.ReadAllBytes(fullPath);
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
//HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, firstCol, firstRow, lastCol, lastRow);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
return;
}
catch (Exception e) {
throw e;
}
}
下载图片的方法里注释了一行：
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
原因是：
每张表只能有一个HSSFPatriarch对象，如果把它的创建放在导出图片方法中，那么最后的图片会消掉之前的图片。
解决方法：将HSSFPatriarch对象放在for循环前定义，以参数方式带入方法中使用
总结
参考博客：
https://blog.csdn.net/lxrj2008/article/details/80003726
400
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
