/// <summary>
/// 生成excel
/// </summary>
/// <param name="columns">栏位，格式为：{
/// column_name:'栏位名称',
/// column_display_name:'表头显示名称'
/// }
/// </param>
/// <param name="data">数据，格式为：
/// {
///column_name1:'值',
///column_name2:'值',
/// .....
///column_nameN:'值',
///}
/// </param>
/// <returns></returns>
public byte[] GenerateExcel(IEnumerable <FrameDLRObject> columns, IEnumerable <FrameDLRObject> data)
{
IWorkbook workbook = null;
try
{
using (var ms = new MemoryStream())
{
ms.Seek(0, SeekOrigin.Begin);
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(ms);
}
ISheet sheet = workbook.GetSheetAt(0);
var index = 0;
//设置表头
var header = sheet.CreateRow(index);
var headerstyle = workbook.CreateCellStyle();
headerstyle.Alignment = HorizontalAlignment.Center;
headerstyle.BorderBottom = BorderStyle.Thin;
headerstyle.BorderTop = BorderStyle.Thin;
headerstyle.BorderLeft = BorderStyle.Thin;
headerstyle.BorderRight = BorderStyle.Thin;
headerstyle.FillBackgroundColor = IndexedColors.BlueGrey.Index;
headerstyle.FillPattern = FillPattern.SolidForeground;
var cellindex = 0;
foreach (dynamic item in columns)
{
var cell = header.CreateCell(cellindex);
cell.CellStyle = headerstyle;
cell.SetCellValue(item.column_display_name);
cellindex++;
}
cellindex = 0;
var datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Center;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
foreach (var item in data)
{
index++;
var row = sheet.CreateRow(index);
foreach (dynamic c in columns)
{
var cell = row.CreateCell(cellindex);
cell.CellStyle = datastyle;
var v = item.GetValue(c.column_name);
if (v == null)
{
cell.SetCellValue("");
}
else if (v is DateTime)
{
cell.SetCellValue(((DateTime)v).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
cell.SetCellValue(v);
}
}
cellindex++;
}
byte[] buffer = new byte[1024];
using (MemoryStream output = new MemoryStream())
{
workbook.Write(output);
buffer = output.ToArray();
}
return(buffer);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
}
}