/// <summary> /// 导出Excel /// </summary> /// <typeparam name="T"></typeparam> /// <param name="data">数据</param> /// <param name="headDict">头部信息</param> /// <param name="imgColumnList">图片列</param> /// <param name="sheetName">Sheet名</param> /// <param name="showSrNo">是否要加上序号</param> /// <returns></returns> public static byte[] ExportExcel <T>(List <T> data, Dictionary <string, string> headDict, List <string> imgColumnList, string sheetName = "", bool showSrNo = false) { if (imgColumnList == null) { imgColumnList = new List <string>(); } DataTable dt = ListToDataTable <T>(data); byte[] result = null; List <string> keyList = new List <string>(); if (showSrNo) { keyList.Add("RowNum"); dt.Columns.Add("RowNum"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["RowNum"] = i + 1; } } //通过键的集合取 foreach (string key in headDict.Keys) { keyList.Add(key); } IWorkbook workbook = new XSSFWorkbook(); //设置宽度 ICellStyle style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; style.VerticalAlignment = VerticalAlignment.Center; //垂直居中 style.Alignment = HorizontalAlignment.Center; //水平对齐; if (showSrNo) { headDict.Add("RowNum", "序号"); } ISheet sheet = sheetName.IsNullOrEmpty() ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName); //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < keyList.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(headDict[keyList[i]]); cell.CellStyle = style; } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < keyList.Count; j++) { if (imgColumnList.Contains(keyList[j])) { //插入图片 byte[] bytes = HttpMethods.GetImage(dt.Rows[i][keyList[j]].ToString()); if (bytes != null) { ICell cell = row1.CreateCell(j); cell.CellStyle = style; try { int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 100, j, i + 1, j + 1, i + 2); //##处理照片位置,【图片左上角为(col, row)第row+1行col+1列,右下角为( col +1, row +1)第 col +1+1行row +1+1列,宽为100,高为50 XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } catch { cell.SetCellValue(dt.Rows[i][keyList[j]].ToString()); } } } else { ICell cell = row1.CreateCell(j); cell.SetCellValue(dt.Rows[i][keyList[j]].ToString()); cell.CellStyle = style; } } } //自适应列宽 for (int i = 0; i < keyList.Count; i++) { sheet.AutoSizeColumn(i, true); } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); result = ms.GetBuffer(); ms.Close(); }; return(result); }