/// <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);
}
