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的情况下无法通过编译。

posted @ 2024-01-19 10:26  大胡子毛绒老头  阅读(67)  评论(0编辑  收藏  举报