How to export from DataTable to excel?

[andywu188]

How to export from DataTable to excel?

DEMO:

public static void ToExcel()
    {
        var mapper = new Mapper();
        var dt = new DataTable("Order");

        dt.Columns.AddRange(new[] { 
            new DataColumn("Id", typeof(int)), 
            new DataColumn("No", typeof(string)), 
            new DataColumn("CreationTime", typeof(DateTime)), 
            new DataColumn("State", typeof(int)) });

        //test data
        dt.Rows.Add(1, "001", DateTime.Now, 1);
        dt.Rows.Add(2, "002", DateTime.Now, 2);
        dt.Rows.Add(3, "003", DateTime.Now, 3);

        mapper.Map((column) =>
        {
            ???
            return false;
        }, (column, target) =>
        {
            ???
            return false;
        }, (column, source) =>
        {
            ???
            return false;
        });
        mapper.Save(???????????);
    }

public enum State
{
    [Description("New Order")]
    NewOrder = 1,
    [Description("Canceled")]
    Canceled = 2,
    [Description("Completed")]
    Completed = 3
}

Excel DEMO:	Id	order number	creation time	state
1	001	2022-07-01 08:05	New Order
2	002	2022-07-01 18:25	Canceled
3	003	2022-07-01 22:01	Completed

creation time format: yyyy-MM-dd HH:mm state text from enum DescriptionAttribute

[donnytian]

Hi, you need to define a POCO first, such as :

public class Order
{
    public string OrderNumber { get; set; }
    public DateTime CreationTime { get; set; }
    public State State { get; set; }
// ...
}

In your case, since you already have a datatable, you may go with NPOI API directly without the mapper. Just get value from datatable and set value with "Cell.SetValue()" in loops. This mapper is about to do the mapping between strong-typed objects and the data source(Excel file), but datatable here is kind of data source, so just go directly with NPOI api between these two types data srouces.

[andywu188]

Due to my business requirements, the exported data is user configurable export fields, so model cannot be used. But I need a mapper, so that it is more convenient to handle the conversion of values, etc. When using npoi API directly, there is no mapper, which is very inconvenient.

[donnytian]

So mapper is not going to save you here. Either define a DTO type to do the mapping or export the data in the database as Excel directly.