当前位置 : 主页 > 网络编程 > net编程 >

#yyds干货盘点#【愚公系列】2023年03月 .NET CORE工具案例-.NET Core使用MiniExcel

来源:互联网 收集:自由互联 发布时间:2023-09-03
前言 1.MiniExcel 的介绍 MiniExcel 是一个简单、高效避免 OOM 的 .NET 处理 Excel 查、写、填充数据工具。 目前主流框架大多需要将数据全载入到内存方便操作,但这会导致内存消耗问题,Mi

前言

1.MiniExcel 的介绍

MiniExcel 是一个简单、高效避免 OOM 的 .NET 处理 Excel 查、写、填充数据工具。

目前主流框架大多需要将数据全载入到内存方便操作,但这会导致内存消耗问题,MiniExcel 尝试以 Stream 角度写底层算法逻辑,能让原本 1000 多 MB 占用降低到几 MB,避免内存不够情况。

MiniExcel 所具有的特点有:

  • 低内存耗用,避免 OOM (out of memoery)、频繁 Full GC 情况
  • 支持即时操作每行数据
  • 兼具搭配 LINQ 延迟查询特性,能办到低消耗、快速分页等复杂查询
  • 轻量,不需要安装 Microsoft Office、COM+,DLL 小于 150KB
  • 简便操作的 API 风格

MiniExcel 的性能测试报告如下:

  • 导入、查询 Excel 比较
Library Method 最大内存耗用 平均时间 MiniExcel 'MiniExcel QueryFirst' 0.1 09 MB 0.0007264 sec ExcelDataReader 'ExcelDataReader QueryFirst 15.24 MB 10.66421 sec MiniExcel 'MiniExcel Query' 17.3 MB 14.17933 sec ExcelDataReader 'ExcelDataReader Query' 17.3 MB 22.56508 sec Epplus 'Epplus QueryFirst' 1,452 MB 1 8.19801 sec Epplus 'Epplus Query' 1,451 MB 23.64747 sec OpenXmISDK 'OpenXmISDK Query' 1,412 MB 52.00327 sec OpenXmlSDK 'OpenXmISDK QueryFirst' 1,413 MB 52.34865 sec ClosedXmI 'ClosedXml QueryFirst' 2,158 MB 66.18897 sec ClosedXml 'ClosedXml Query' 2,184 MB 191.43412 sec
  • 导出、创建 Excel 比较
Library Method 最大内存耗用 平均时间 MiniExcel 'MiniExcel Create Xlsx' 15 MB 11.53181 sec Epplus 'Epplus Create XIsx' 1,204 MB 22.50971 sec OpenXmlSdk 'OpenXmISdk Create XIsx' 2,621 MB 42.47 399 sec ClosedXml 'ClosedXml Create Xlsx' 7,141 MB 1 40.93992 sec

MiniExcel框架:https://github.com/MiniExcel/MiniExcel 在这里插入图片描述

一、.NET Core使用MiniExcel

.NET平台上对Excel进行操作主要有两种方式。

  • 第一种,把Excel文件看成一个数据库,通过OleDb的方式进行读取与操作;
  • 第二种,调用Excel的COM组件。
  • 第三种,插件方式,目前主流框架大多需要将数据全载入到内存方便操作,但这会导致内存消耗问题,MiniExcel 尝试以 Stream 角度写底层算法逻辑,能让原本1000多MB占用降低到几MB,避免内存不够情况。

在这里插入图片描述

1.安装Nuget包

使用Nuget搜索Miniexcel安装

在这里插入图片描述

2.准备文件

准备一个Excel测试文件 在这里插入图片描述

3.准备Model类

public class Test
{
    public string Status { get; set; }
    public string OrderNo { get; set; }
    public string OrderType { get; set; }
    public string Address { get; set; }
    public string Num { get; set; }
    public string Torr { get; set; }
    public string Ynum { get; set; }
    public string County { get; set; }
    public string Level { get; set; }
    public string Date { get; set; }
    public string AtrNo { get; set; }
}

4.读/导入 Excel

var rows = MiniExcel.Query<Test>(@"./Test.xlsx").ToList();
// or
using (var stream = File.OpenRead(@"./Test.xlsx"))
    var rows = stream.Query<Test>();

在这里插入图片描述 Query 查询支援延迟加载(Deferred Execution),能配合LINQ First/Take/Skip办到低消耗、高效率复杂查询

var rows = MiniExcel.Query<Test>(@"./Test.xlsx").First();
//or
using (var stream = File.OpenRead(@"./Test.xlsx"))
{
    var row = stream.Query().First();
}

在这里插入图片描述 MiniExcel 当判断文件 SharedString 大小超过 5MB,预设会使用本地缓存,如 10x100000.xlsx(一百万笔数据),读取不开启本地缓存需要最高内存使用约195MB,开启后降为65MB。

var config = new OpenXmlConfiguration { EnableSharedStringCache = false };
MiniExcel.Query(path,configuration: config)

也能使用 SharedStringCacheSize 调整 sharedString 文件大小超过指定大小才做硬盘缓存

var config = new OpenXmlConfiguration { SharedStringCacheSize=500*1024*1024 };
MiniExcel.Query(path, configuration: config);

5.写/导出 Excel

var path = $"./{Guid.NewGuid()}.xlsx";
//支持各种类型的导出(匿名类型,强类型,IDataReader等)
MiniExcel.SaveAs(path, new[] {
    new { Column1 = "MiniExcel", Column2 = 1 },
    new { Column1 = "Github", Column2 = 2}
});
//or
using (var cnn = Connection)
{
    cnn.Open();
    var sheets = new Dictionary<string, object>();
    sheets.Add("sheet1", cnn.ExecuteReader("select 1 id"));
    sheets.Add("sheet2", cnn.ExecuteReader("select 2 id"));
    MiniExcel.SaveAs("Demo.xlsx", sheets);
}

在这里插入图片描述

6.模板填充 Excel

在这里插入图片描述

var path = $"./{Guid.NewGuid()}.xlsx";
var templatePath = $"./6ed6226c-dfb8-4e0c-95b1-d9b7e4f7f31a.xlsx";
// 1. By POCO
var value = new
{
    Name = "愚公搬代码",
    CreateDate = new DateTime(2022, 10, 08),
    Age = true,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

//or
// 2. By Dictionary
var value = new Dictionary<string, object>()
{
    ["Name"] = "Jack",
    ["CreateDate"] = new DateTime(2021, 01, 01),
    ["Age"] = true,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

在这里插入图片描述

6.复杂数据填充

在这里插入图片描述

var path = $"./{Guid.NewGuid()}.xlsx";
var templatePath = $"./6ed6226c-dfb8-4e0c-95b1-d9b7e4f7f31a.xlsx";
// 1. By POCO
var value = new
{
    title = "嗨欢迎来到我的世界",
    managers = new[] {
        new{
            Name = "愚公搬代码",
            CreateDate = new DateTime(2022, 10, 08),
            Age = 18,
        },
        new {
            Name = "愚公搬代码1",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 28,
        }
    },
    employees = new[] {
        new{
            Name = "愚公搬代码",
            CreateDate = new DateTime(2022, 10, 08),
            Age = 18,
        },
        new {
            Name = "愚公搬代码1",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 28,
        },
        new {
            Name = "愚公搬代码2",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 30,
        },
        new {
            Name = "愚公搬代码3",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 31,
        }
    }
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

//or
var value = new Dictionary<string, object>()
{
    ["title"] = "嗨欢迎来到我的世界",
    ["managers"] = new[] {
        new{
            Name = "愚公搬代码",
            CreateDate = new DateTime(2022, 10, 08),
            Age = 18,
        },
        new {
            Name = "愚公搬代码1",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 28,
        }
    },
    ["employees"] = new[] {
        new{
            Name = "愚公搬代码",
            CreateDate = new DateTime(2022, 10, 08),
            Age = 18,
        },
        new {
            Name = "愚公搬代码1",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 28,
        },
        new {
            Name = "愚公搬代码2",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 30,
        },
        new {
            Name = "愚公搬代码3",
            CreateDate = new DateTime(2022, 11, 08),
            Age = 31,
        }
    }
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

在这里插入图片描述

不过wps打开没问题,微软的excel打开报错

7.Excel 列属性 (Excel Column Attribute)

属性指定Column

public class ExcelAttributeDemo
{
    [ExcelColumnName("Column1")]
    public string Test1 { get; set; }
    [ExcelColumnName("Column2")]
    public string Test2 { get; set; }
    [ExcelIgnore]
    public string Test3 { get; set; }
    [ExcelColumnIndex("I")] // 系统会自动转换"I"为第8列
    public string Test4 { get; set; } 
    public string Test5 { get; } //系统会忽略此列
    public string Test6 { get; private set; } //set非公开,系统会忽略
    [ExcelColumnIndex(3)] // 从0开始索引
    public string Test7 { get; set; }
}

var rows = MiniExcel.Query<ExcelAttributeDemo>(path).ToList();
Assert.Equal("Column1", rows[0].Test1);
Assert.Equal("Column2", rows[0].Test2);
Assert.Null(rows[0].Test3);
Assert.Equal("Test7", rows[0].Test4);
Assert.Null(rows[0].Test5);
Assert.Null(rows[0].Test6);
Assert.Equal("Test4", rows[0].Test7); 

DynamicColumnAttribute 动态设定 Column

var config = new OpenXmlConfiguration
{
    DynamicColumns = new DynamicExcelColumn[] { 
        new DynamicExcelColumn("id"){Ignore=true},
        new DynamicExcelColumn("name"){Index=1,Width=10},
        new DynamicExcelColumn("createdate"){Index=0,Format="yyyy-MM-dd",Width=15},
        new DynamicExcelColumn("age"){Index=2,Name="Account Age"},
    }
};
var path = PathHelper.GetTempPath();
var value = new[] { new { id = 1, name = "愚公搬代码", createdate = new DateTime(2022, 10, 08) ,age= 31} };
MiniExcel.SaveAs(path, value, configuration: config);

8.Excel 类别自动判断

MiniExcel 预设会根据文件扩展名判断是 xlsx 还是 csv,但会有失准时候,请自行指定。

Stream 类别无法判断来源于哪种 excel 请自行指定

stream.SaveAs(excelType:ExcelType.CSV);
//or
stream.SaveAs(excelType:ExcelType.XLSX);
//or
stream.Query(excelType:ExcelType.CSV);
//or
stream.Query(excelType:ExcelType.XLSX);
上一篇:Blazor_WASM之4:路由
下一篇:没有了
网友评论