private byte[] BuildExcel(ExecuteResult result)
{
if (result.Result == null)
{
return(new byte[0]);
}
var workbook = new XSSFWorkbook();
for (int i = 0; i < result.Result.Count; i++)
{
var resultItem = result.Result[i];
ISheet sheet = workbook.CreateSheet("result_" + (i + 1).ToString());
ICellStyle style = sheet.Workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
int rowIndex = 0;
#region 创建头部
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
int colNum = -1;
for (int j = 0; j < resultItem.Columns.Count; j++)
{
dataRow.WriteString(++colNum, resultItem.Columns[j], style);
}
#endregion
sheet.CreateFreezePane(0, 1, 0, 1);//冻结首行
var doubleCellStyle = sheet.CreateDoubleCellStyle();
var datetimeCellStyle = sheet.Workbook.CreateCellStyle();
IDataFormat datastyle = sheet.Workbook.CreateDataFormat();
datetimeCellStyle.DataFormat = datastyle.GetFormat("yyyy-MM-dd hh:mm:ss");
rowIndex++;
foreach (var values in resultItem.Values)
{
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < values.Length; j++)
{
var col = values[j];
if (null == col)
{
}
else if (col is int num_int)
{
row.WriteDouble(j, num_int);
}
else if (col is double num_double)
{
row.WriteDouble(j, num_double, doubleCellStyle);
}
else if (col is DateTime num_dt)
{
row.WriteDateTime(j, num_dt, datetimeCellStyle);
}
else if (col is string num_str)
{
row.WriteString(j, num_str);
}
else
{
row.WriteString(j, col.ToString());
}
}
rowIndex++;
}
}
var sqlsheet = workbook.CreateSheet("sql");
for (int i = 0; i < result.Result.Count; i++)
{
var resultItem = result.Result[i];
var hrow = sqlsheet.CreateRow(i);
var cell = hrow.CreateCell(0);
cell.SetCellValue("result_" + (i + 1).ToString());
var cell2 = hrow.CreateCell(1);
cell2.SetCellValue(resultItem.Sql);
}
using (var ms = new MemoryStream()) {
workbook.Write(ms);
return(ms.ToArray());
}
}
