1、首先引用NPOI
2、本例用到的引用
3、在Controller里面添加导出方法
public ActionResult ExportMsgData(string term)
{ //为list赋值
MsgListToExcelForXLSXModel1(list, "");
return Content("");
}
4、导出基础方法
public void MsgListToExcelForXLSXModel1(List<BackMsgProblemList> dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test");
DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null; //赋值给dc，是便于对每一个datacolumn的操作
dc = tblDatas.Columns.Add("编号", Type.GetType("System.Int32"));
dc.AutoIncrement = true;//自动增加
dc.AutoIncrementSeed = ;//起始为1
dc.AutoIncrementStep = ;//步长为1
dc.AllowDBNull = false;//
dc = tblDatas.Columns.Add("问题标题", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("问题状态名称", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("问题图片一", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("问题图片二", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("问题图片三", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("问题图片四", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("问题图片五", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("问题图片六", Type.GetType("System.String")); dc = tblDatas.Columns.Add("报验图片一", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("报验图片二", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("报验图片三", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("报验图片四", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("报验图片五", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("报验图片六", Type.GetType("System.String")); dc = tblDatas.Columns.Add("复检图片一", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("复检图片二", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("复检图片三", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("复检图片四", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("复检图片五", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("复检图片六", Type.GetType("System.String"));
//表头
IRow row = sheet.CreateRow();
for (int i = ; i < tblDatas.Columns.Count; i++)
{ ICell cell = row.CreateCell(i);
cell.SetCellValue(tblDatas.Columns[i].ColumnName);
//自动适应宽度
sheet.AutoSizeColumn(i);
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i));
} //数据
for (int i = ; i < dt.Count(); i++)
{
IRow row1 = sheet.CreateRow(i + );
ICell cell = row1.CreateCell();
cell.SetCellValue(i + );
cell = row1.CreateCell();
cell.SetCellValue(dt[i].RIP_Name);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].PB_AllName);
var arry = dt[i].PB_AllName.Split('>');
cell = row1.CreateCell();
cell.SetCellValue(arry.Length > ? arry[] : "");
cell = row1.CreateCell();
cell.SetCellValue(arry.Length > ? arry[] : "");
cell = row1.CreateCell();
cell.SetCellValue(arry.Length > ? arry[] : "");
cell = row1.CreateCell();
cell.SetCellValue(arry.Length > ? arry[] : "");
cell = row1.CreateCell();
cell.SetCellValue(dt[i].AName);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].BD_Name);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].U_Name);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].H_Code);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].RIP_DistributName);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].RIP_SeverityName);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].HCIC_AddDateStr);
cell = row1.CreateCell();
cell.SetCellValue(dt[i].RectificationNum.ToString());
cell = row1.CreateCell();
cell.SetCellValue(dt[i].QuestionStateName);
if (dt[i].ContentAccListForCreate != null && dt[i].ContentAccListForCreate.Count > )
{
MsgInsetImg(sheet, row1, dt, i, xssfworkbook, dt[i].ContentAccListForCreate, );
}
if (dt[i].ContentAccListForInspection != null && dt[i].ContentAccListForInspection.Count > )
{
MsgInsetImg(sheet, row1, dt, i, xssfworkbook, dt[i].ContentAccListForInspection, );
}
if (dt[i].ContentAccListForCheck != null && dt[i].ContentAccListForCheck.Count > )
{ MsgInsetImg(sheet, row1, dt, i, xssfworkbook, dt[i].ContentAccListForCheck, );
}
}
/*不能使用如下方法生成Excel，因为在xssfworkbook.Write(stream);操作后会关闭流，导致报错【不能操作已关闭的流】*/
////转为字节数组
//MemoryStream stream = new MemoryStream();
//xssfworkbook.Write(stream);
//var buf = stream.ToArray(); ////保存为Excel文件
//using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
//{
// fs.Write(buf, 0, buf.Length);
// fs.Flush();
//}
/*可以使用下面方式导出-这里数据量多会报错【发现XX.xlsx中部分内容有问题，是否让我们尽量尝试恢复】*/
//MemoryStream stream = new MemoryStream();
//xssfworkbook.Write(stream); //Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
//Response.BinaryWrite(stream.ToArray());
//xssfworkbook = null;
//stream.Close();
//stream.Dispose();
/*最终推荐下面方式*/
HttpContext curContext = HttpContext.Current; MemoryStream ms = new MemoryStream();
xssfworkbook.Write(ms);
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("aaa.xlsx", Encoding.UTF8) + ".xlsx");
curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
curContext.Response.End();
}
5、插入图片方法
private void MsgInsetImg(ISheet sheet, IRow row1, List<BackMsgProblemList> dt, int i, XSSFWorkbook xssfworkbook, List<string> ContentAccList, int colnum)
{
for (int j = ; j < ; j++)
{
//设置图片那的宽高
sheet.SetColumnWidth(colnum + j, );
row1.Height = ;
if (ContentAccList.Count <= j)
{
break;
}
var dPath = ImageShow + ContentAccList[j];
try
{ System.Drawing.Image imgOutput = System.Drawing.Bitmap.FromFile(dPath);
System.Drawing.Image img = imgOutput.GetThumbnailImage(, , null, IntPtr.Zero);
//图片转换为文件流
MemoryStream ms = new MemoryStream();
img.Save(ms, ImageFormat.Bmp);
BinaryReader br = new BinaryReader(ms);
var picBytes = ms.ToArray();
ms.Close(); //插入图片
if (picBytes != null && picBytes.Length > )
{
var rows = i + ;
var cols = colnum + j;
/* Add Picture to Workbook, Specify picture type as PNG and Get an Index */
int pictureIdx = xssfworkbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG); //添加图片
/* Create the drawing container */
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
/* Create an anchor point */
XSSFClientAnchor anchor = new XSSFClientAnchor(, , , , cols, rows, , ); /* 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;
}
}
catch (Exception ex)
{ log.Fatal("--图片导出失败：当前文件路径：" + dPath);
} }
}
6、导出成果
说明：这里采用的是Excel2007以上版本即：XSSFWorkbook，目前XSSFWorkbook版本的资料较少，希望能帮助大家。
备注：excel宽高采用的不是像素，经过计算大约为宽：36.53125=1像素 高：15.05217391304348=1像素