Yoko.Excel.Core 1.0.1

Yoko.Excel.Core

• .NETCore的Excel导出工具,解决十万百万级大数据导出Excel内存占用问题

• 建议使用IDataReader

特点

• 低内存耗用，避免OOM、频繁 FullGC 情况
• 轻量，不需要安装 Microsoft Office、COM+，DLL小于150KB

安装

NuGet搜索 Yoko.Excel.Core

导出 Excel

除非必要，不要用 ToList，ToDatatable 等方法读取全部数据到内存！！！

除非必要，不要用 ToList，ToDatatable 等方法读取全部数据到内存！！！

除非必要，不要用 ToList，ToDatatable 等方法读取全部数据到内存！！！

1. IDataReader

• 推荐使用，可以避免载入全部数据到内存

YokoExcel.SaveAs(path, reader);

• DataReader 单表格导出方式 (示例使用 SqlSugar )

var path = Path.Combine(@"D:\App.Excel\", $"{Guid.NewGuid()}.xlsx");
//=========== SqlSugar ===========
//强制类型ISugarQueryable
 var db = GetDB();  
//订单数据
var data = db.Queryable<TradeOrder>()
       .Where(x => SqlFunc.Between(x.AddTime, "2021-08-01 00:00:00", "2021-08-01 08:00:00"));
var sql = data.Clone().ToSql();
using var reader = db.Ado.GetDataReader(sql.Key, sql.Value);
//===============================
//自定义字段映射
 var fieldMapping = new List<FieldMapping>()
 {
       new FieldMapping()
       {
             FieldID = "OrderID",
             FieldName = "订单ID"
       },
       new FieldMapping()
       {
             FieldID = "CityCode",
             FieldName = "城市编号",
             FieldDefinition = new Hashtable{
                  { "012345","A城市"},
                  { "456789","B城市"}
             }
        }
  };
//导出-原始数据
YokoExcel.SaveAs(path, reader);
//导出-部分自定义映射数据
//YokoExcel.SaveAs(path, reader, fieldMapping, "订单报表");
//TODO: 手动关闭
reader.Close();

2. 支持集合<匿名类别>

var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx");
YokoExcel.SaveAs(path, new[] {
    new { Column1 = "YokoExcel001", Column2 = 1 },
    new { Column1 = "YokoExcel002", Column2 = 2}
});

3. IEnumerable<IDictionary<string, object>>

var values = new List<Dictionary<string, object>>()
{
    new Dictionary<string,object>{{ "Col1", "YokoExcel001" }, { "Col0001", 1 } },
    new Dictionary<string,object>{{ "Col2", "YokoExcel002" }, { "Col0002", 2 } }
};
YokoExcel.SaveAs(path, values);

4. Datatable

• 不推荐使用，会将数据全载入内存

var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx");
var table = new DataTable();
{
    table.Columns.Add("Column1", typeof(string));
    table.Columns.Add("Column2", typeof(decimal));
    table.Rows.Add("YokoExcel111", 1);
    table.Rows.Add("YokoExcel222", 2);
}
YokoExcel.SaveAs(path, table);

5. SaveAs 支持 Stream

• 生成文件不落地

using (var stream = new MemoryStream()) //支持 FileStream,MemoryStream..等
{
    stream.SaveAs(values);
}

• DemoAPI 导出 Excel

public IActionResult DownloadExcel()
{
    var values = new[] {
        new { Column1 = "YokoExcel", Column2 = 1 },
        new { Column1 = "YokoExcel222", Column2 = 2}
    };
    
    var memoryStream = new MemoryStream();
    memoryStream.SaveAs(values);
    memoryStream.Seek(0, SeekOrigin.Begin);
    
    return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    {
        FileDownloadName = "demo001.xlsx"
    };
}