C#使用npoi生成excel流
using System.Collections.Generic; using System.Data; using System.Dynamic; using System.IO; using System.Linq; namespace Tools { public static class ExcelHelper { public static Stream ToExcel(object a, IDictionary<string, object> cs = default) { return a is IQueryable q ? ToExcel(q, cs) : a is List<IDictionary<string, object>> dd ? ToExcel(dd, cs) : a is DataTable t ? ToExcel(t) : default; } private static Stream ToExcel(IQueryable source, dynamic HSSFWorkbook, IDictionary<string, object> cs) { MemoryStream ms = new(); var s = HSSFWorkbook.CreateSheet(); var r0 = s.CreateRow(0); var ci = 0; cs ??= new ExpandoObject(); if (cs.Count is 0) { source.ElementType.GetProperties().ToList().ForEach(g => cs[g.Name] = g.Name); } cs.ToList().ForEach(g => r0.CreateCell(ci++).SetCellValue(g.Value + "")); var i = 1; var ps = source.ElementType.GetProperties().ToList(); foreach (var item in source) { var r = s.CreateRow(i++); var j = 0; foreach (var ite in cs) { r.CreateCell(j++).SetCellValue(source.ElementType.GetProperty(ite.Key).GetValue(item) + ""); } } HSSFWorkbook.Write(ms); ms.Position = 0; return ms; } private static Stream ToExcel(List<IDictionary<string, object>> source, dynamic HSSFWorkbook, IDictionary<string, object> cs) { MemoryStream ms = new(); var s = HSSFWorkbook.CreateSheet(); var r0 = s.CreateRow(0); var ci = 0; cs.ToList().ForEach(g => r0.CreateCell(ci++).SetCellValue(g.Value + "")); var i = 1; foreach (var item in source) { var r = s.CreateRow(i++); var j = 0; foreach (var ite in cs) { r.CreateCell(j++).SetCellValue(item[ite.Key] + ""); } } HSSFWorkbook.Write(ms); ms.Position = 0; return ms; } private static MemoryStream ToExcel(DataTable source, dynamic HSSFWorkbook) { MemoryStream ms = new(); var s = HSSFWorkbook.CreateSheet(source.TableName); var r0 = s.CreateRow(0); var ci = 0; source.Columns.OfType<DataColumn>().ToList().ForEach(g => r0.CreateCell(ci++).SetCellValue(g.ColumnName)); var i = 1; foreach (DataRow row in source.Rows) { var r = s.CreateRow(i++); foreach (DataColumn column in source.Columns) { r.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } } HSSFWorkbook.Write(ms); ms.Position = 0; return ms; } } }
参数HSSFWorkbook在从外界new(),免得在没引用NPOI的情况下无法通过编译。