In the scenario of exporting excel, I usually export a List directly into a sheet, using npoi Mapper library is very convenient. Recently, I often need to convert the jsonarray returned by the interface into an excel table. For example, the columns obtained from elasticsearch or clickhouse are not fixed. For example, the clickhouse changes according to the field set in the select statement. It is impossible to define an unknown class in advance and then deserialize it! So I think of another way, which is what this article will share: dynamically generate class + template engine to generate Excel/Word/Html/PDF, etc

I have put the code on github

https://github.com/yuzd/Exporter

Welcome, star!

The overall idea is:

• 1 if the class cannot be defined in advance, the class T is dynamically generated according to the input
• 2 then load the data into the List set
• 3 use template engine + List to generate target file

Step 1: dynamically generate class T according to input

According to the current needs, input is divided into two categories

1. Unable to determine the type of class

• Comma separated string collection in CSV format
• jsonarray string
• DataTable
• DataSet
• DataReader

For this scenario, we need to dynamically generate class classes step by step

2. You already know the type of class

• List set (T is the class type we want)
• Map set in the form of key and value (the key set is used as the column, and the type of value is the class type we want)

For this scenario, we only need to use the template engine in the last step of the process

Dynamically generate the text of class

1. csv scenario

The csv file itself can be opened by double clicking. For example, if you send it to qq or wechat, you can't preview it. If it is converted to excel, you can preview it directly

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

First, according to the first column "Name,Age, test", the Razor template engine is used to generate the text of a 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

The long text generated by the class is as follows:

image

• In order to ensure that the same fields share a class type, the default generation rule of class name is Data_$ hash of {field concatenation string}

2. jsonarray scenario

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

Adopt xamasoft The jsonclassgenerator library generates class text

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

3. DataTable and other scenarios

For example, in DataTable, first take all the columns from it, and then generate class text in the same way as 1

Dynamically compile and generate class classes

The class text of class is generated according to the above method. Next, it needs to be dynamically compiled into class and loaded into the current Domain.

natasha component is adopted. The usage is as follows

AssemblyCSharpBuilder builder = new("ExportCoreClass")
{
    Domain = DomainManagement.Default
};
//code = class text
builder.Add(code);
var asm = builder.GetAssembly();
//This type is the class type we want
var type = asm.DefinedTypes.First(t => t.Name == mrj.ClassName);

It should be noted here that because the className is generated by specific rules, check whether it already exists in the current Domain before dynamically compiling and generating the class

/// <summary>
///Check that the current domain has created the same class
/// </summary>
/// <param name="className"></param>
/// <returns></returns>
private static Type? GetExistedTypeInCurrentDomain(string className)
{
    try
    {
        //Check that the current domain has created the same 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;
}

Step 2: load the data into the List set

This step is relatively simple, because the class type has been generated. The next step is to create a List collection by reflection, and load each item of input data into the instance of T generated by reflection

image

Step 2: use template engine + List to generate target file

In fact, the Razor template engine has been used to help us generate class text. The Razor template engine is very powerful and extensible

Here, we use different types to correspond to different Razor templates. At present, the following have been implemented:

• Excel2003
• Excel2007 and above
• Word2003
• Word2007 and above
• Html(Table)

As shown below:

image

The factory mode is adopted for external use, and different output s are processed with different classes, which is also convenient for adding other types of exports (such as PDF) in the future

Take Excel as an example

To generate excel by non POI library, first introduce what the excel template looks like

< = excel before 2003 has this structure:

image

=The 2007 version of excel has the following structure:

<?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>

According to the above xml structure, you also need to use documentformat Openxml library to generate excel

/// <summary>
///Generate excel byte array
/// </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();
}

Other types of output are similar routines. Make a template, and then List data + template + processing = final file

nuget address and common usage

Install-Package ExporterCore

CSV (comma separated) export to excel

var arrCSV = new List<string>();
arrCSV.Add("Name,WebSite,connect");
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 export to 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);

Export list to 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);

Multiple list s export multiple sheets of the same excel

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

Unfinished to be continued

In the future, the built-in template may be improved to allow users to customize, which will be complete

Pay attention to official account and study together