MiniExcel

MiniExcel

轻量高效的 .NET Excel 处理工具

MiniExcel 是一款轻量级的 .NET Excel 处理工具,通过流式处理算法大幅降低内存占用,有效避免内存溢出问题。该库支持实时处理数据,兼容 LINQ 延迟执行,可实现低消耗、快速分页等复杂查询。MiniExcel 无需依赖 Microsoft Office,DLL 大小仅 150KB,API 简洁易用,适合处理大型 Excel 文件。其高效率和低内存消耗特性使其成为 .NET 平台上理想的 Excel 处理解决方案。

MiniExcelExcel处理.NET低内存消耗高性能Github开源项目
<div align="center"> <p><a href="https://www.nuget.org/packages/MiniExcel"><img src="https://img.shields.io/nuget/v/MiniExcel.svg" alt="NuGet"></a> <a href="https://www.nuget.org/packages/MiniExcel"><img src="https://img.shields.io/nuget/dt/MiniExcel.svg" alt=""></a> <a href="https://ci.appveyor.com/project/shps951023/miniexcel/branch/master"><img src="https://ci.appveyor.com/api/projects/status/b2vustrwsuqx45f4/branch/master?svg=true" alt="Build status"></a> <a href="https://gitee.com/dotnetchina/MiniExcel"><img src="https://gitee.com/dotnetchina/MiniExcel/badge/star.svg" alt="star"></a> <a href="https://github.com/shps951023/MiniExcel" rel="nofollow"><img src="https://img.shields.io/github/stars/shps951023/MiniExcel?logo=github" alt="GitHub stars"></a> <a href="https://www.nuget.org/packages/MiniExcel"><img src="https://img.shields.io/badge/.NET-%3E%3D%204.5-red.svg" alt="version"></a> </p> </div>

<img align="right" src="https://github.com/dotnet-foundation/swag/blob/main/logo/dotnetfoundation_v4.png?raw=true" width="100" />

<div align="center"> <p>This project is part of the <a href="https://www.dotnetfoundation.org/">.NET Foundation</a> and operates under their <a href="https://www.dotnetfoundation.org/code-of-conduct">code of conduct</a>. </p> </div>
<div align="center"> <p><strong><a href="README.md">English</a> | <a href="README.zh-CN.md">简体中文</a> | <a href="README.zh-Hant.md">繁體中文</a></strong></p> </div>
<div align="center"> Your <a href="https://github.com/shps951023/MiniExcel">Star</a> and <a href="https://miniexcel.github.io">Donate</a> can make MiniExcel better </div>

Introduction

MiniExcel is simple and efficient to avoid OOM's .NET processing Excel tool.

At present, most popular frameworks need to load all the data into the memory to facilitate operation, but it will cause memory consumption problems. MiniExcel tries to use algorithm from a stream to reduce the original 1000 MB occupation to a few MB to avoid OOM(out of memory).

image

Features

  • Low memory consumption, avoid OOM (out of memory) and full GC
  • Support real-time operation of each row of data
  • Support LINQ deferred execution, it can do low-consumption, fast paging and other complex queries
  • Lightweight, without Microsoft Office installed, no COM+, DLL size is less than 150KB
  • Easy API style to read/write/fill excel

Get Started

Installation

You can install the package from NuGet

Release Notes

Please Check Release Notes

TODO

Please Check TODO

Performance

Benchmarks logic can be found in MiniExcel.Benchmarks , and test cli

dotnet run -p .\benchmarks\MiniExcel.Benchmarks\ -c Release -f netcoreapp3.1 -- -f * --join

Output from the latest run is :

BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19042 Intel Core i7-7700 CPU 3.60GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores [Host] : .NET Framework 4.8 (4.8.4341.0), X64 RyuJIT Job-ZYYABG : .NET Framework 4.8 (4.8.4341.0), X64 RyuJIT IterationCount=3 LaunchCount=3 WarmupCount=3

Benchmark History : Link

Import/Query Excel

Logic : Test1,000,000x10.xlsx as performance test basic file, 1,000,000 rows * 10 columns "HelloWorld" cells, 23 MB file size

LibraryMethodMax Memory UsageMean
MiniExcel'MiniExcel QueryFirst'0.109 MB0.0007264 sec
ExcelDataReader'ExcelDataReader QueryFirst'15.24 MB10.66421 sec
MiniExcel'MiniExcel Query'17.3 MB14.17933 sec
ExcelDataReader'ExcelDataReader Query'17.3 MB22.56508 sec
Epplus'Epplus QueryFirst'1,452 MB18.19801 sec
Epplus'Epplus Query'1,451 MB23.64747 sec
OpenXmlSDK'OpenXmlSDK Query'1,412 MB52.00327 sec
OpenXmlSDK'OpenXmlSDK QueryFirst'1,413 MB52.34865 sec
ClosedXml'ClosedXml QueryFirst'2,158 MB66.18897 sec
ClosedXml'ClosedXml Query'2,184 MB191.43412 sec

Export/Create Excel

Logic : create a total of 10,000,000 "HelloWorld" excel

LibraryMethodMax Memory UsageMean
MiniExcel'MiniExcel Create Xlsx'15 MB11.53181 sec
Epplus'Epplus Create Xlsx'1,204 MB22.50971 sec
OpenXmlSdk'OpenXmlSdk Create Xlsx'2,621 MB42.47399 sec
ClosedXml'ClosedXml Create Xlsx'7,141 MB140.93992 sec

Excel Query/Import <a name="getstart1"></a>

1. Execute a query and map the results to a strongly typed IEnumerable [Try it]

Recommand to use Stream.Query because of better efficiency.

public class UserAccount { public Guid ID { get; set; } public string Name { get; set; } public DateTime BoD { get; set; } public int Age { get; set; } public bool VIP { get; set; } public decimal Points { get; set; } } var rows = MiniExcel.Query<UserAccount>(path); // or using (var stream = File.OpenRead(path)) var rows = stream.Query<UserAccount>();

image

2. Execute a query and map it to a list of dynamic objects without using head [Try it]

  • dynamic key is A.B.C.D..
MiniExcel1
Github2
var rows = MiniExcel.Query(path).ToList(); // or using (var stream = File.OpenRead(path)) { var rows = stream.Query().ToList(); Assert.Equal("MiniExcel", rows[0].A); Assert.Equal(1, rows[0].B); Assert.Equal("Github", rows[1].A); Assert.Equal(2, rows[1].B); }

3. Execute a query with first header row [Try it]

note : same column name use last right one

Input Excel :

Column1Column2
MiniExcel1
Github2
var rows = MiniExcel.Query(useHeaderRow:true).ToList(); // or using (var stream = File.OpenRead(path)) { var rows = stream.Query(useHeaderRow:true).ToList(); Assert.Equal("MiniExcel", rows[0].Column1); Assert.Equal(1, rows[0].Column2); Assert.Equal("Github", rows[1].Column1); Assert.Equal(2, rows[1].Column2); }

4. Query Support LINQ Extension First/Take/Skip ...etc

Query First

var row = MiniExcel.Query(path).First(); Assert.Equal("HelloWorld", row.A); // or using (var stream = File.OpenRead(path)) { var row = stream.Query().First(); Assert.Equal("HelloWorld", row.A); }

Performance between MiniExcel/ExcelDataReader/ClosedXML/EPPlus queryfirst

5. Query by sheet name

MiniExcel.Query(path, sheetName: "SheetName"); //or stream.Query(sheetName: "SheetName");

6. Query all sheet name and rows

var sheetNames = MiniExcel.GetSheetNames(path); foreach (var sheetName in sheetNames) { var rows = MiniExcel.Query(path, sheetName: sheetName); }

7. Get Columns

var columns = MiniExcel.GetColumns(path); // e.g result : ["A","B"...] var cnt = columns.Count; // get column count

8. Dynamic Query cast row to IDictionary<string,object>

foreach(IDictionary<string,object> row in MiniExcel.Query(path)) { //.. } // or var rows = MiniExcel.Query(path).Cast<IDictionary<string,object>>(); // or Query specified ranges (capitalized) // A2 represents the second row of column A, C3 represents the third row of column C // If you don't want to restrict rows, just don't include numbers var rows = MiniExcel.QueryRange(path, startCell: "A2", endCell: "C3").Cast<IDictionary<string, object>>();

9. Query Excel return DataTable

Not recommended, because DataTable will load all data into memory and lose MiniExcel's low memory consumption feature.

var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: true);

image

10. Specify the cell to start reading data

MiniExcel.Query(path,useHeaderRow:true,startCell:"B3")

image

11. Fill Merged Cells

Note: The efficiency is slower compared to not using merge fill

Reason: The OpenXml standard puts mergeCells at the bottom of the file, which leads to the need to foreach the sheetxml twice

var config = new OpenXmlConfiguration() { FillMergedCells = true }; var rows = MiniExcel.Query(path, configuration: config);

image

support variable length and width multi-row and column filling

image

12. Reading big file by disk-base cache (Disk-Base Cache - SharedString)

If the SharedStrings size exceeds 5 MB, MiniExcel default will use local disk cache, e.g, 10x100000.xlsx(one million rows data), when disable disk cache the maximum memory usage is 195MB, but able disk cache only needs 65MB. Note, this optimization needs some efficiency cost, so this case will increase reading time from 7.4 seconds to 27.2 seconds, If you don't need it that you can disable disk cache with the following code:

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

You can use SharedStringCacheSize to change the sharedString file size beyond the specified size for disk caching

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

image

image

Create/Export Excel <a name="getstart2"></a>

  1. Must be a non-abstract type with a public parameterless constructor .

  2. MiniExcel support parameter IEnumerable Deferred Execution, If you want to use least memory, please do not call methods such as ToList

e.g : ToList or not memory usage image

1. Anonymous or strongly type [Try it]

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

2. IEnumerable<IDictionary<string, object>>

var values = new List<Dictionary<string, object>>() { new Dictionary<string,object>{{ "Column1", "MiniExcel" }, { "Column2", 1 } }, new Dictionary<string,object>{{ "Column1", "Github" }, { "Column2", 2 } } }; MiniExcel.SaveAs(path, values);

Create File Result :

Column1Column2
MiniExcel1
Github2

3. IDataReader

  • Recommended, it can avoid to load all data into memory
MiniExcel.SaveAs(path, reader);

image

DataReader export multiple sheets (recommand by Dapper ExecuteReader)

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); }

4. Datatable

  • Not recommended, it will load all data into memory

  • DataTable use Caption for column name first, then use columname

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("MiniExcel", 1); table.Rows.Add("Github", 2); } MiniExcel.SaveAs(path, table);

5. Dapper Query

Thanks @shaofing #552 , please use CommandDefinition + CommandFlags.NoCache

using (var connection = GetConnection(connectionString)) { var rows = connection.Query( new CommandDefinition( @"select 'MiniExcel' as Column1,1 as Column2 union all select 'Github',2" , flags: CommandFlags.NoCache) ); // Note: QueryAsync will throw close connection exception MiniExcel.SaveAs(path, rows); }

Below code will load all data into memory

using (var connection = GetConnection(connectionString)) { var rows = connection.Query(@"select 'MiniExcel' as Column1,1 as Column2 union all select

编辑推荐精选

讯飞智文

讯飞智文

一键生成PPT和Word,让学习生活更轻松

讯飞智文是一个利用 AI 技术的项目,能够帮助用户生成 PPT 以及各类文档。无论是商业领域的市场分析报告、年度目标制定,还是学生群体的职业生涯规划、实习避坑指南,亦或是活动策划、旅游攻略等内容,它都能提供支持,帮助用户精准表达,轻松呈现各种信息。

热门AI工具AI办公办公工具讯飞智文AI在线生成PPTAI撰写助手多语种文档生成AI自动配图
讯飞星火

讯飞星火

深度推理能力全新升级,全面对标OpenAI o1

科大讯飞的星火大模型,支持语言理解、知识问答和文本创作等多功能,适用于多种文件和业务场景,提升办公和日常生活的效率。讯飞星火是一个提供丰富智能服务的平台,涵盖科技资讯、图像创作、写作辅助、编程解答、科研文献解读等功能,能为不同需求的用户提供便捷高效的帮助,助力用户轻松获取信息、解决问题,满足多样化使用场景。

模型训练热门AI工具内容创作智能问答AI开发讯飞星火大模型多语种支持智慧生活
Spark-TTS

Spark-TTS

一种基于大语言模型的高效单流解耦语音令牌文本到语音合成模型

Spark-TTS 是一个基于 PyTorch 的开源文本到语音合成项目,由多个知名机构联合参与。该项目提供了高效的 LLM(大语言模型)驱动的语音合成方案,支持语音克隆和语音创建功能,可通过命令行界面(CLI)和 Web UI 两种方式使用。用户可以根据需求调整语音的性别、音高、速度等参数,生成高质量的语音。该项目适用于多种场景,如有声读物制作、智能语音助手开发等。

Trae

Trae

字节跳动发布的AI编程神器IDE

Trae是一种自适应的集成开发环境(IDE),通过自动化和多元协作改变开发流程。利用Trae,团队能够更快速、精确地编写和部署代码,从而提高编程效率和项目交付速度。Trae具备上下文感知和代码自动完成功能,是提升开发效率的理想工具。

热门AI工具生产力协作转型TraeAI IDE
咔片PPT

咔片PPT

AI助力,做PPT更简单!

咔片是一款轻量化在线演示设计工具,借助 AI 技术,实现从内容生成到智能设计的一站式 PPT 制作服务。支持多种文档格式导入生成 PPT,提供海量模板、智能美化、素材替换等功能,适用于销售、教师、学生等各类人群,能高效制作出高品质 PPT,满足不同场景演示需求。

讯飞绘文

讯飞绘文

选题、配图、成文,一站式创作,让内容运营更高效

讯飞绘文,一个AI集成平台,支持写作、选题、配图、排版和发布。高效生成适用于各类媒体的定制内容,加速品牌传播,提升内容营销效果。

AI助手热门AI工具AI创作AI辅助写作讯飞绘文内容运营个性化文章多平台分发
材料星

材料星

专业的AI公文写作平台,公文写作神器

AI 材料星,专业的 AI 公文写作辅助平台,为体制内工作人员提供高效的公文写作解决方案。拥有海量公文文库、9 大核心 AI 功能,支持 30 + 文稿类型生成,助力快速完成领导讲话、工作总结、述职报告等材料,提升办公效率,是体制打工人的得力写作神器。

openai-agents-python

openai-agents-python

OpenAI Agents SDK,助力开发者便捷使用 OpenAI 相关功能。

openai-agents-python 是 OpenAI 推出的一款强大 Python SDK,它为开发者提供了与 OpenAI 模型交互的高效工具,支持工具调用、结果处理、追踪等功能,涵盖多种应用场景,如研究助手、财务研究等,能显著提升开发效率,让开发者更轻松地利用 OpenAI 的技术优势。

Hunyuan3D-2

Hunyuan3D-2

高分辨率纹理 3D 资产生成

Hunyuan3D-2 是腾讯开发的用于 3D 资产生成的强大工具,支持从文本描述、单张图片或多视角图片生成 3D 模型,具备快速形状生成能力,可生成带纹理的高质量 3D 模型,适用于多个领域,为 3D 创作提供了高效解决方案。

3FS

3FS

一个具备存储、管理和客户端操作等多种功能的分布式文件系统相关项目。

3FS 是一个功能强大的分布式文件系统项目,涵盖了存储引擎、元数据管理、客户端工具等多个模块。它支持多种文件操作,如创建文件和目录、设置布局等,同时具备高效的事件循环、节点选择和协程池管理等特性。适用于需要大规模数据存储和管理的场景,能够提高系统的性能和可靠性,是分布式存储领域的优质解决方案。

下拉加载更多