Yoko.Excel.Core 1.3.1

[TOC]

Yoko.Excel.Core

• Excel导出工具，解决十万百万级大数据导出Excel内存占用问题
• 建议使用IDataReader

更新内容

1.3.1

• 【优化】导出时判断指定目录是否存在，不存在则创建

1.3.0

• 【新增】读取Excel

1.2.0

• 【新增】自定义指定列宽 ColumnWidth
• 【新增】自定义指定列是否忽略 ExcelIgnore
• 【修改】列为DateTime类型时，强制显示文本格式为 yyyy-MM-dd HH:mm:ss
• 【修改】优化包体积

特点

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

安装

NuGet搜索 Yoko.Excel.Core

读取 Excel

• 实体对象

var path = @"D:\demo.xlsx";
var data = YokoExcel.Query<Order>(path);

// 或者
using var stream = File.OpenRead(path);
var data = stream.Query<Order>();

• 匿名动态对象

var data = YokoExcel.Query(path).ToList();

//或者 
using var stream = File.OpenRead(path);
var data = stream.Query().ToList();

导出 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",
             ExcelIgnore = true
       },
       new FieldMapping()
       {
             FieldID = "CityCode",
             FieldName = "城市编号",
             ColumnWidth = 10,
             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"
    };
}