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;//起始为1 dc.AutoIncrementStep = 1;//步长为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(0); for (int i = 0; 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 = 0; i < dt.Count(); i++) { IRow row1 = sheet.CreateRow(i + 1); ICell cell = row1.CreateCell(0); cell.SetCellValue(i + 1); cell = row1.CreateCell(1); cell.SetCellValue(dt[i].RIP_Name); cell = row1.CreateCell(2); cell.SetCellValue(dt[i].PB_AllName); var arry = dt[i].PB_AllName.Split('>'); cell = row1.CreateCell(3); cell.SetCellValue(arry.Length > 0 ? arry[0] : ""); cell = row1.CreateCell(4); cell.SetCellValue(arry.Length > 1 ? arry[1] : ""); cell = row1.CreateCell(5); cell.SetCellValue(arry.Length > 2 ? arry[2] : ""); cell = row1.CreateCell(6); cell.SetCellValue(arry.Length > 3 ? arry[3] : ""); cell = row1.CreateCell(7); cell.SetCellValue(dt[i].AName); cell = row1.CreateCell(8); cell.SetCellValue(dt[i].BD_Name); cell = row1.CreateCell(9); cell.SetCellValue(dt[i].U_Name); cell = row1.CreateCell(10); cell.SetCellValue(dt[i].H_Code); cell = row1.CreateCell(11); cell.SetCellValue(dt[i].RIP_DistributName); cell = row1.CreateCell(12); cell.SetCellValue(dt[i].RIP_SeverityName); cell = row1.CreateCell(13); cell.SetCellValue(dt[i].HCIC_AddDateStr); cell = row1.CreateCell(14); cell.SetCellValue(dt[i].RectificationNum.ToString()); cell = row1.CreateCell(15); cell.SetCellValue(dt[i].QuestionStateName); if (dt[i].ContentAccListForCreate != null && dt[i].ContentAccListForCreate.Count > 0) { MsgInsetImg(sheet, row1, dt, i, xssfworkbook, dt[i].ContentAccListForCreate, 16); } if (dt[i].ContentAccListForInspection != null && dt[i].ContentAccListForInspection.Count > 0) { MsgInsetImg(sheet, row1, dt, i, xssfworkbook, dt[i].ContentAccListForInspection, 22); } if (dt[i].ContentAccListForCheck != null && dt[i].ContentAccListForCheck.Count > 0) { MsgInsetImg(sheet, row1, dt, i, xssfworkbook, dt[i].ContentAccListForCheck, 28); } } /*不能使用如下方法生成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 = 0; j < 6; j++) { //设置图片那的宽高 sheet.SetColumnWidth(colnum + j, 5845); row1.Height = 1731; 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(160, 115, 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 > 0) { var rows = i + 1; 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(0, 0, 0, 0, cols, rows, 1, 3); /* 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像素
