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