Export simple export Excel

Time:2021-10-21

Select a third party Library

  • Epplus: authorization problem after 5.0out
  • Magicodes.ie: Based on epplush 4. X, iexporterheaderfilter has poor performance, and the order will be out of order when dynamically adjusting the orderout
  • Npoi: I don’t like it very much
  • Closed XML: Based on document. Openxml, it has good performance, but it needs to encapsulate some business functionsi like this

Simple export function

//Export
protected override void Action()
{
    //Create Workbook
    var wb = new XLWorkbook(XLEventTracking.Disabled);
    wb.Worksheets.Add("test", Datas);

    wb.SaveAs($"{Guid.NewGuid()}.xlsx");
}


internal static class ClosedXMLExtension
{
    //Add ixlworksheet extension, you can also use function encapsulation
    public static IXLWorksheet Add(this IXLWorksheets wss, string sheetName, IEnumerable datas)
    {
        var ws = wss.Add(sheetName);
        var props = typeof(T).GetProperties();
        int row = 1, col = 1;
        
        foreach (var data in datas)
        {
            foreach (var prop in props)
            {
                ws.Cell(row, col).Value = prop.GetValue(data);
                col++;
            }
            col = 1;
            row++;
        }
        return ws;
    }
}

Further encapsulation

explain
  • The whole uses reflection to get the value to fill the cell
  • Header can be controlled dynamically, but the implementation of control flow is relatively simple
  • Match the dynamic header with the lowercase of the model field
public static class ClosedXMLExtension
{
    /// 
    ///Populate a single sheet with data
    /// 
    /// 
    /// IXLWorksheets
    ///Sheet name (unique required)
    ///Data source
    /// 
    public static IXLWorksheet Add(this IXLWorksheets wss, string sheetName, IEnumerable dataSource, Dictionary headers = null)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), "datasource cant be null");
        if (string.IsNullOrEmpty(sheetName))
            throw new ArgumentNullException(nameof(dataSource), "sheet's name cant be null");

        //Create sheet
        var ws = wss.Add(sheetName);

        //If there is no data in the header, it is returned directly
        if (headers != null && !headers.Any())
            return ws;

        var props = typeof(T).GetProperties();
        headers = ChangeDynamicHeadersOrder(headers);

        AddHeader(ws, headers == null ?
            props.Select(x => x.Name) :
            headers.Select(x => x.Value.DisplayName));

        int row = 2, col = 1;

        foreach (var data in dataSource)
        {
            foreach (var prop in props)
            {
                if (headers == null)
                {
                    ws.Cell(row, col).Value = prop.GetValue(data);
                    col++;
                }
                else if (headers.TryGetValue(prop.Name.ToLower(), out var dynamicHeader))
                    ws.Cell(row, dynamicHeader.Index).Value = prop.GetValue(data);
            }

            col = 1;
            row++;
        }

        return ws;
    }

    /// 
    ///Add table header
    /// 
    /// 
    /// 
    private static void AddHeader(IXLWorksheet ws, IEnumerable headerNames)
    {
        int row = 1, col = 1;
        foreach (var name in headerNames)
        {
            var cell = ws.Cell(row, col);
            cell.Value = name;
            cell.Style.Font.Bold = true;
            cell.Style.Fill.SetBackgroundColor(XLColor.LightBlue);

            col++;
        }
    }

    /// 
    ///Modify the order of headers to prevent data overlap
    /// 
    /// 
    /// 
    private static Dictionary ChangeDynamicHeadersOrder(Dictionary headers)
    {
        if (headers == null) return null;

        var temp = headers
            .OrderBy(x => x.Value.Index)
            .Select((x, index) =>
                new KeyValuePair(
                    x.Key.ToLower(),
                    new DynamicHeader() { Index = index + 1, DisplayName = x.Value.DisplayName }
                ));

        return temp.ToDictionary(x => x.Key, x => x.Value);
    }
}

public class DynamicHeader
{
    /// 
    ///Serial number
    /// 
    public int Index { get; set; }

    /// 
    ///Header display name
    /// 
    public string DisplayName { get; set; }
}