【NPOI】导出Excel带图片
1. 本地路径图片
//填写内容 for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1 + 1); row.Height = 80 * 20; //设置excel行高，像素点是1/20 for (int j = 0; j < dt.Columns.Count; j++) { if (j == 4) { var dPath = AppDomain.CurrentDomain.BaseDirectory + dt.Rows[i][j].ToString().Replace(@"/", @"\"); if (File.Exists(dPath)) //防止文件不存在时报错 { byte[] picBytes = System.IO.File.ReadAllBytes(dPath); int picIndex = workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.JPEG); XSSFDrawing patr = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 2, j + 1, i + 3); XSSFPicture picture = (XSSFPicture)patr.CreatePicture(anchor, picIndex); } } else { row.CreateCell(j, CellType.String).SetCellValue(dt.Rows[i][j].ToString()); } } }
2. 网络路径图片
/// <summary> /// 向sheet插入图片 /// </summary> private static void AddCellPicture(ISheet sheet, XSSFWorkbook workbook, string imgPath, int row, int col) { Uri uri = new Uri(imgPath); //imgPath :网络图片地址 WebRequest webRequest = WebRequest.Create(uri); using (WebResponse webResponse = webRequest.GetResponse()) { //防止发生报错：GDI+中发生一般性错误的解决办法 Bitmap bitmap = new Bitmap(webResponse.GetResponseStream()); //读取图片流 Bitmap OldImage = new Bitmap(bitmap);//将图片流复制到新的图片流中 bitmap.Dispose(); //将原来的图片流释放，将图片文件进行解锁。 using (MemoryStream ms = new MemoryStream()) { OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); byte[] bytes = ms.ToArray(); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); //添加图片 XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row + 2, col + 1, row + 3); //图片位置，图片左上角为（col, row） XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //pict.Resize(); //用图片原始大小来显示 } } }
3. 导出Excel
#region 方案商品导出Excel private static ICellStyle GetTitleCellStyleForScheme(IWorkbook wb) { ICellStyle cellStyle = wb.CreateCellStyle(); IFont font = wb.CreateFont(); font.FontName = "微软雅黑"; font.FontHeightInPoints = 15; font.Boldweight = short.MaxValue; cellStyle.SetFont(font); //水平对齐 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //垂直对齐 cellStyle.VerticalAlignment = VerticalAlignment.Center; //自动换行 cellStyle.WrapText = true; return cellStyle; } /// <summary> /// 向sheet插入图片 /// </summary> private static void AddCellPicture(ISheet sheet, XSSFWorkbook workbook, string imgPath, int row, int col) { Uri uri = new Uri(imgPath); //imgPath :网络图片地址 WebRequest webRequest = WebRequest.Create(uri); using (WebResponse webResponse = webRequest.GetResponse()) { Bitmap bitmap = new Bitmap(webResponse.GetResponseStream()); //读取图片流 Bitmap OldImage = new Bitmap(bitmap);//将图片流复制到新的图片流中 bitmap.Dispose(); //将原来的图片流释放，将图片文件进行解锁。 using (MemoryStream ms = new MemoryStream()) { OldImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); byte[] bytes = ms.ToArray(); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); //添加图片 XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row + 2, col + 1, row + 3); //图片位置，图片左上角为（col, row） XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //pict.Resize(); //用图片原始大小来显示 } } } /// <summary> /// 导出Excel /// </summary> /// <param name="tbScheme"></param> /// <param name="tbGoods"></param> /// <param name="sheetName"></param> /// <param name="exportType">0：基础数据表；1：报价工具</param> /// <param name="SchemeBuyWay">4：集采；其他为代发</param> /// <returns></returns> public static byte[] ExportExcelForSchemeGoods(DataTable tbScheme, DataTable tbGoods, string sheetName, string exportType, string SchemeBuyWay) { byte[] ret = null; string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; XSSFWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); IRow rowHead = sheet.CreateRow(0); //首行为方案信息 rowHead.Height = 50 * 20; string strSchemeInfo = string.Empty; if (tbScheme != null && tbScheme.Rows.Count > 0) { strSchemeInfo += "方案编号：" + tbScheme.Rows[0]["SchemeCode"] + "；方案名称：" + tbScheme.Rows[0]["SchemeName"] + "；单人预算：" + tbScheme.Rows[0]["SingleBudget"] + "；采购人数：" + tbScheme.Rows[0]["BuyNumber"] + "；方案状态：" + tbScheme.Rows[0]["ApprovalStatusName"] + "；创建人：" + tbScheme.Rows[0]["Creator"] + "；方案有效期：" + tbScheme.Rows[0]["SchemeAgeingTime"]; } //合并单元格显示方案信息 if (tbGoods != null && tbGoods.Rows.Count > 0) { for (int i = 0; i < tbGoods.Columns.Count; i++) { ICell cell = rowHead.CreateCell(i, CellType.String); cell.CellStyle = GetTitleCellStyleForScheme(workbook); cell.SetCellValue(strSchemeInfo); } NPOI.SS.Util.CellRangeAddress range = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tbGoods.Columns.Count - 1); sheet.AddMergedRegion(range); rowHead = sheet.CreateRow(1); //填写表头 for (int i = 0; i < tbGoods.Columns.Count; i++) { if (exportType == "0") //基础数据 { if (tbGoods.Columns[i].ColumnName.ToString() == "商品图片") { sheet.SetColumnHidden(i, true); continue; } } if (SchemeBuyWay != "4") //代发不展示单人采购量 { if (tbGoods.Columns[i].ColumnName.ToString() == "单人份采购量/总采购量") { sheet.SetColumnHidden(i, true); continue; } } ICell cell = rowHead.CreateCell(i, CellType.String); cell.CellStyle = getHeadCellStyle(workbook); cell.SetCellValue(tbGoods.Columns[i].ColumnName.ToString()); } //填写内容 for (int i = 0; i < tbGoods.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1 + 1); for (int j = 0; j < tbGoods.Columns.Count; j++) { if (exportType == "1") //报价工具：带图片 { if (j == 1) { var imgPath = tbGoods.Rows[i][j].ToString(); if (imgPath != "") { AddCellPicture(sheet, workbook, imgPath, i, j); } } } else { // 跳过图片列 if (j == 1) { sheet.SetColumnHidden(j, true); continue; } } if (SchemeBuyWay != "4") //代发不展示单人采购量 { if (j == 8) { sheet.SetColumnHidden(j, true); continue; } } ICell cell = row.CreateCell(j, CellType.String); cell.CellStyle = getTextCellStyle(workbook); cell.SetCellValue(tbGoods.Rows[i][j].ToString()); row.Height = 100 * 20; //设置默单元格行高 } } for (int i = 0; i <= tbGoods.Columns.Count; i++) { sheet.AutoSizeColumn(i); //列宽自适应，只对英文和数字有效 sheet.SetColumnWidth(1, 30 * 256); //设置图片列大小 } } using (var ms = new MemoryStream()) { HttpResponse httpResponse = HttpContext.Current.Response; httpResponse.Clear(); httpResponse.Buffer = true; httpResponse.Charset = Encoding.UTF8.BodyName; httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + filename); httpResponse.ContentEncoding = Encoding.UTF8; httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8"; workbook.Write(httpResponse.OutputStream); httpResponse.End(); ret = ms.ToArray(); ms.Close(); ms.Dispose(); } GC.Collect(); return ret; } #endregion
