csv/json/list/datatable导出为excel的通用模块设计

导出excel的场景我一般都是一个List直接导出成一张sheet,用Npoi.Mapper库很方便,最近我经常是需要将接口返回的jsonarray转成一张excel表，比如从elasticsearch中或者从clickhouse中拿到的列是不固定的，比如从clickhouse中是根据select语句中的字段集合变化而变化，无法提前定义一个未知class再反序列化！所以我想了另外一种办法，也就是本文要分享的：动态生成class+模板引擎的方式来生成Excel/Word/Html/PDF等

代码我已放到github上

https://github.com/yuzd/Exporter

欢迎star！

整体思路是：

  • 1如果无法预先定义class那就根据input来动态生成class类T
  • 2再把数据装载到List集合中
  • 3利用模板引擎+List生成目标文件

第一步：先根据input来动态生成class类T

根据目前需要，input分成2大类

1. 无法确定class类型的
  • CSV格式逗号分隔的string集合
  • jsonarray字符串
  • DataTable
  • DataSet
  • DataReader

针对这种场景，那么我们需要按流程一步步来先动态生成class类

2. 已经知道class类型的
  • List集合（T即为我们想要的class类型）
  • key,value形式的Map集合（key集合作为列，value的类型即为我们想要的class类型）

针对这种场景，那么在流程中我们只需要最后一步利用模板引擎即可

动态生成class类的文本

1. csv的场景

csv文件本身双击可以打开，csv文件比如你发到qq或者微信，预览不了，转成excel的话可以直接预览


var arrCSV = new List<string>();
arrCSV.Add("Name,Age,测试");
arrCSV.Add("1112,20,hello");
arrCSV.Add("1232,21,world");

先根据第一列"Name,Age,测试"采用Razor模板引擎生成一个class的文本


using System;
public class @Model.ClassName {
//constructor
public @Model.ClassName (
    @foreach(var prop in Model.Properties){
    <text>string @prop , </text>
    }
    //add a fake property
string fake=null)
{
    @foreach(var prop in Model.Properties){
    <text>this.@prop = @prop;</text>
    }
}//end constructor
//properties
@foreach(var prop in Model.Properties){
    <text>public string @prop{get;set;}</text>
    }
 
}//end class

生成的class文本是长这样的：

image
image
  • 为了后续确保字段相同的都共用一个class类型，class的名称默认的生成规则是Data_${字段拼接string}的hash

2. jsonarray的场景



string json = @"[
        { 'Name':'Andrei Ignat', 
            'WebSite':'http://xxxx/',
            'CV':'adada.xls'        
        },
    { 'Name':'Your Name', 
            'WebSite':'http://your website',
            'CV':'cv.doc'        
        }
    ]";
var data2 = ExportFactory.ExportDataJson(json, ExportToFormat.Excel);
File.WriteAllBytes("a.xlsx", data2);

采用Xamasoft.JsonClassGenerator库生成class文本

public class Data1888056300
{
    public string Name { get; set; }
    public string WebSite { get; set; }
    public string CV { get; set; }
}

3. DataTable等其他的场景

比如DataTable，先从里面取所有的列，然后按照和1同样的方式即可生成class文本

动态编译生成class类

按照上面的方式生成了class的类文本，接下来需要动态编译成class类并加载到当前的Domain中。

采用natasha组件,用法如下


AssemblyCSharpBuilder builder = new("ExportCoreClass")
{
    Domain = DomainManagement.Default
};
//code = class文本
builder.Add(code);
var asm = builder.GetAssembly();
//这个type就是我们想要的class类型
var type = asm.DefinedTypes.First(t => t.Name == mrj.ClassName);

这里要注意一点，因为className我们是特定规则生成的，所以在动态编译生成class之前先检查当前Domain 中是否已存在

/// <summary>
/// 检测当前domain已经创建好了相同的class
/// </summary>
/// <param name="className"></param>
/// <returns></returns>
private static Type? GetExistedTypeInCurrentDomain(string className)
{
    try
    {
        // 检测当前domain已经创建好了相同的class
        var typeExisting = AppDomain.CurrentDomain.GetAssemblies()
            .SelectMany(a => a.GetTypes())
            .FirstOrDefault(t => t.FullName != null && t.FullName.Equals(className));

        if (typeExisting != null)
            return typeExisting;
    }
    catch (Exception)
    {
        //ignore
    }
    return null;
}

第二步：数据装载到List集合中

这一步比较简单，因为class类型已经生成好了，接下来就是采用反射的方式，创建一个List集合， 在把input数据的每一项根据反射生成T的实例装载进去就好了

image
image

第二步：利用模板引擎+List生成目标文件

其实上面已经用了Razor模板引擎来帮我们生成class类文本了，Razor模板引擎非常强大，扩展性也非常好

这里我们采用不同的类型对应不同的Razor模板，目前已经实现了的有：

  • Excel2003
  • Excel2007及以上
  • Word2003
  • Word2007及以上
  • Html(Table)

如下图：

image
image

采用工厂模式暴露对外使用，不同的output采用不同的类进行处理,也方便日后新增其他类型的导出(比如PDF)

以Excel为例子

非POI库的方式来生成excel，先介绍下excel的模板是什么样子

<= 2003版本之前的excel是这样的结构：

image
image

= 2007版本的excel是这样的结构：

<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
<worksheet xmlns='http://schemas.openxmlformats.org/spreadsheetml/2006/main' xmlns:r='http://schemas.openxmlformats.org/officeDocument/2006/relationships'>
    <sheetData>

@Include(Model.NameOfT+"Excel2007Header")

@foreach(var item in Model.Data){
  @Include(Model.NameOfT+"Excel2007Item",item)
} 

    </sheetData>
</worksheet>

根据上面的xml结构还需要用DocumentFormat.OpenXml库来生成excel


/// <summary>
/// 生成excel字节数组
/// </summary>
/// <param name="worksheetName"></param>
/// <param name="textSheet"></param>
/// <returns></returns>
private byte[] CreateExcel2007(string[] worksheetName, string[] textSheet)
{
    using var ms = new MemoryStream();
    using var sd = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
    var workbook = sd.AddWorkbookPart();
    var strSheets = "<sheets>";
    for (var i = 0; i < worksheetName.Length; i++)
    {
        var sheet = workbook.AddNewPart<WorksheetPart>();
        WriteToPart(sheet, textSheet[i]);
        strSheets += string.Format("<sheet name=\"{1}\" sheetId=\"{2}\" r:id=\"{0}\" />",
            workbook.GetIdOfPart(sheet), worksheetName[i], (i + 1));
    }
    strSheets += "</sheets>";
    WriteToPart(workbook, string.Format(
        "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?><workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">{0}</workbook>",
        strSheets
    ));

    sd.Close();
    return ms.ToArray();
}

其他类型的output也是类似套路，制定模板，然后List数据+模板+加工=最终文件

nuget地址以及常用的使用方法

Install-Package ExporterCore

csv(逗号分隔)导出为excel


var arrCSV = new List<string>();
arrCSV.Add("Name,WebSite,连接");
arrCSV.Add("111,http://msprogrammer.serviciipeweb.ro/,http://serviciipeweb.ro/iafblog/content/binary/cv.doc");
arrCSV.Add("123,http://msprogrammer.serviciipeweb.ro/,http://serviciipeweb.ro/iafblog/content/binary/cv.doc");

var data = ExportFactory.ExportDataCsv(arrCSV.ToArray(), ExportToFormat.Excel2007);
File.WriteAllBytes("a.xlsx", data);

json导出为excel

string json = @"[
        { 'Name':'Andrei Ignat', 
            'WebSite':'http://xxx/',
            'CV':'http://aaaaa/binary/cv.doc'        
        },
    { 'Name':'Your Name', 
            'WebSite':'http://your website',
            'CV':'cv.doc'        
        }
    ]";
var data2 = ExportFactory.ExportDataJson(json, ExportToFormat.Excel);
File.WriteAllBytes("a.xlsx", data2);

list导出为excel


List<Person> listWithPerson = new List<Person>
{
    new Person
    {
        Name = "aa",
        Aget = 12
    },
    new Person
    {
        Name = "dasda",
        Aget = 1222
    }
};
var data = ExportFactory.ExportData(listWithPerson, ExportToFormat.Excel);
File.WriteAllBytes("a.xlsx", data);

多个list导出同个excel的多张Sheet


var p = new Person { Name = "andrei", WebSite = "http://xxx.ro/", CV = "http://daary/cv.doc" };
var p1 = new Person { Name = "you", WebSite = "http://yourwebsite.com/" };
var list = new List<Person>() { p, p1 };

var kvp = new List<Tuple<string, string>>();
for (int i = 0; i < 10; i++)
{
    var q = new Tuple<string, string>("This is key " + i, "Value " + i);
    kvp.Add(q);
}

var export = new ExportExcel2007<Person>();
var data = export.ExportMultipleSheets(new IList[] { list, kvp });
File.WriteAllBytes("multiple.xlsx", data);

未完待续

后续可能会完善一下内置的模板可以让使用者定制化，这样就完整了

关注公众号一起学习

 

posted @ 2022-03-05 18:49  俞正东  阅读(559)  评论(0)  编辑  收藏  举报