public static Stream ListToExcel(object list, Dictionary<string, string> titles, bool IsExportAllCol)
{
const int startIndex = 0;
var fields = titles.Keys.ToList();
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("sheet1");
sheet.DefaultRowHeight = 200 * 20;
var row = sheet.CreateRow(startIndex);
var headStyle = GetHeadStyle(workbook);
EachHelper.EachListHeader(list, (i, name, type) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
fields.Add(name);
else
return;
}
var cellIndex = fields.IndexOf(name) + startIndex;
var cell = row.CreateCell(cellIndex);
cell.SetCellValue(titles.ContainsKey(name)?titles[name]:name);
cell.CellStyle = headStyle;
sheet.AutoSizeColumn(cellIndex);
});
EachHelper.EachListRow(list, (rowIndex, dataRow) =>
{
row = sheet.CreateRow(rowIndex + 1);
EachHelper.EachObjectProperty(dataRow, (i, name, value) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
fields.Add(name);
else
return;
}
var cellIndex = fields.IndexOf(name) + startIndex;
var dataStyle = GetDataStyle(workbook);
var cell = row.CreateCell(cellIndex);
cell.CellStyle = dataStyle;
switch ((value??string.Empty).GetType().Name.ToLower())
{
case "int32":
case "int64":
case "decimal":
dataStyle.Alignment = HorizontalAlignment.RIGHT;
cell.SetCellValue(ZConvert.To<double>(value,0));
break;
default:
cell.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell.SetCellValue(ZConvert.ToString(value));
break;
}
});
});
var ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
sheet = null;
row = null;
return ms;
}
public void Init(object data)
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("sheet1");
sheet.DefaultRowHeight = 200 * 20;
}
