C#实战：使用ExcelKit实现海量数据导出

一、ExcelKit介绍

Excel导入导出套件，支持百万级(几百万亦可)数据导出和读取（格式仅限xlsx）而不占用多少内存，方便易用的方法让导入导出更易使用支持.Net Core，docker，Windows。

依赖项：.Net Standard2.1 + NPOI2.4.1以上版本

二、安装方式

通过命令安装

Install-Package ExcelKit

直接通过nuget包管理器搜索安装

直接搜索ExcelKit关键字，选择最新的版本安装。

三、示例

3.1 首先新建一个Person.cs 实体类

public class Person
    {
        [ExcelKit(Desc = "用户名", Width = 20, IsIgnore = false, Sort = 10, Align = TextAlign.Center, FontColor = DefineColor.LightBlue)]
        public string UserName { get; set; }
        [ExcelKit(Desc = "密码", Width = 20, Sort = 20, FontColor = DefineColor.Rose)]
        public string Pwd { get; set; }
        [ExcelKit(Desc = "住址", Width = 30, Sort = 30, FontColor = DefineColor.Rose, ForegroundColor = DefineColor.LemonChiffon)]
        public string Address { get; set; }
        [ExcelKit(Desc = "年龄", Width = 10, Sort = 40)]
        public int  Age { get; set; }
        [ExcelKit(Desc = "兴趣爱好", Width = 30, Sort = 50, FontColor = DefineColor.Rose, ForegroundColor = DefineColor.LemonChiffon)]
        public string Hobby { get; set; }

    }

3.2 泛型类方式导出代码示例

操作步骤

● 获取GetWriteContext并指定导出文件名

● 创建Sheet并制定Sheet名（Sheet名作为后期追加数据区分是哪个Sheet的依据）

● AppendData向Sheet中追加数据

● 调用Save保存（默认保存到程序运行目录）或Generate生成Excel信息，web环境调用Generate生成的信息，调用return File(Excel信息)后，可直接用于下载

特别提示，当单个Sheet数据量超过1048200后，后续追加的数据会自动拆分到新的Sheet，使用者不需要自己处理，只管追加数据。

/// <summary>
        /// 基于实体类导出，需要在实体类配置ExcelKit的注解
        /// </summary>
        private static void Export()
        {
            string filename = "测试导出文件";
            if (IsFileInUse(filename + ".xlsx"))
            {
                Console.WriteLine("文件正在占用，请先关闭文件");
            }
            else
            {
                using (var context = ContextFactory.GetWriteContext("测试导出文件"))
                {
                    // 创建第一个sheet
                    var sheet = context.CrateSheet<Person>($"Sheet1");
                    for (int i = 0; i < 1000; i++)
                    {
                        sheet.AppendData($"Sheet1", new Person { UserName = $"1-{i}-小明", Address = $"1-{i}-苏州", Age = 30, Hobby = "读书", Pwd = "123456" });
                    }

                    // 创建第二个sheet
                    var sheet2 = context.CrateSheet<Person>($"Sheet2");
                    for (int i = 0; i < 1500; i++)
                    {
                        sheet2.AppendData($"Sheet2", new Person { UserName = $"2-{i}-小明", Address = $"2-{i}-苏州", Age = 30, Hobby = "读书", Pwd = "123456" });
                    }
                    string filePath = context.Save();
                    Console.WriteLine($"文件路径：{filePath}");
                }
            }
        }

效果

注意：泛类导出需要增加ExcelKitAttribute注解，要不然导出会有问题

ExcelKitAttribute详解

● Code：字段编码，如Name、Age; 读取时不指定Code默认使用字段名

● Desc：字段描述[必指定]，对应Excel列头中的文本，如姓名、地址，

● AllowNull：字段是否允许为空，一般用于读取

● Converter：转换器[导出时]，组件中提供了常用的转换器，如需自定义，则继承自IExportConverter并实现方法

● ConverterParam：转换器辅助参数[导出时]，导出时使用，如日期格式化导出，导出保留的小数位等；如需自定义Converter，则ConverterParam会完全放置到Convert方法的第二个参数中

● Sort：字段顺序[导出时]，导出和读取都可能用到

● Width：列宽[导出时]，指定Excel列宽度

● Align：对齐方式[导出时]，指定Excel列中的文本对齐方式

● FontColor：字体颜色[导出时]，指定Excel列中的字体颜色，枚举项

● ForegroundColor：前景色[导出时]，指定Excel列的填充色，枚举项

● HeadRowFrozen：是否启用表头行冻结[导出时]

● HeadRowFilter：是否启用表头行筛选[导出时]

● IsIgnore：是否完全忽略

● IsOnlyIgnoreRead：是否仅读取时忽略

● IsOnlyIgnoreWrite：是否仅导出时忽略

Converter详解

作用：Converter为内置的接口IExportConverter，主要是为了导出使用；目前提供了单泛型参数，双泛型参数的版本。使用者可以根据接口实现自己的Converter。程序内部提供了常用的Converter，命名空间为：ExcelKit.Core.Infrastructure.Converter ，内置如下：

● BoolConverter：（适用于bool类型字段，可指定ConverterParam，如ConverterParam = "男|女"，字段定义为bool?可空时，true为男，false为女，为空则导出也为空，默认不指定ConverterParam的话，导出后显示为：是否；自定义导出文字，用|区分，左边文字为字段等于true时导出的值，右边为字段等于false时导出的值）

● DateTimeFmtConverter：（日期格式化Converter，如需自定义日期格式，需指定ConverterParam）

● DecimalPointDigitConverter：（小数类Converter，如需指定保留几位小数，需指定ConverterParam）

● EnumConverter：（枚举Converter，需要在枚举上方打上此特性[System.ComponentModel.Description("用户类型")]，导出时就会根据指定的描述展示对应的文字，如果枚举加了可空，则使用时Converter = typeof(EnumConverter<UserStatusEnum?>)）

● EnumerableConverter：（集合类Converter，如字段定义为public List SkuSellRegion { get; set; }则上方Converter = typeof(EnumerableConverter)，导出后会自动拆分为字符串，以，分隔的长文本）

3.3 动态指定列导出

需要定义ExcelKitAttribute属性来定义列头信息，代码具体如下：

 /// <summary>
        /// 动态指定列导出
        /// </summary>
        private static void DynamicExport()
        {
            string filename = "动态测试导出文件";
            if (IsFileInUse(filename+ ".xlsx"))
            {
                Console.WriteLine("文件正在占用，请先关闭文件");
            }
            else
            {
                using (var context = ContextFactory.GetWriteContext(filename))
                {
                    // 定义导出列属性
                    List<ExcelKitAttribute> excelKitAttributes = new List<ExcelKitAttribute>();
                    excelKitAttributes.Add(new ExcelKitAttribute
                    {
                        Code = "UserName",
                        Desc = "用户名",
                        Width = 30,
                        Sort = 10
                    });
                    excelKitAttributes.Add(new ExcelKitAttribute
                    {
                        Code = "Pwd",
                        Desc = "密码",
                        Width = 20,
                        Sort = 20
                    });

                    excelKitAttributes.Add(new ExcelKitAttribute
                    {
                        Code = "Address",
                        Desc = "住址",
                        Width = 50,
                        Sort = 30
                    });

                    // 创建第一个自定义列属性的sheet
                    var sheet = context.CrateSheet($"Sheet1", excelKitAttributes);
                    // 循环插入行记录
                    for (int i = 0; i < 600; i++)
                    {
                        sheet.AppendData($"Sheet1", new Dictionary<string, object> {
                        {"UserName",$"小明-{i}"}
                        ,{"Pwd","123456" }
                        ,{"Address",$"苏州-{i}"}
                    });
                    }
                    // 保存文件
                    string filePath = context.Save();
                    Console.WriteLine($"动态导出文件路径：{filePath}");
                }
            }
        }

效果

3.4 数据读取

参数说明

● 读取主要是按照Sheet索引（默认从1开始）或者Sheet名称（默认Sheet1）

● 目前仅支持单Sheet读取，多Sheet同时读取暂未加入

● 此方式读取时，读取成功的数据在SucData中，读取一行返回一行，故不像一次性全部读取出来那般占内存

● 对于读取失败的数据，ReadXXXOptions中有 FailData ，会返回读取失败的源数据及失败相关信息，方便记录及导出到新的Excel中

● FailData仅仅是读取Excel失败或者转换为目标数据失败才会进FailData，在SucData中的函数本身如果抛错不会进入FailData

● ReadXXXOptions中的DataStartRow（默认从1开始）和DataEndRow（可空不传则读完）代表读取的数据条数位置，不配置采用默认值

● ReadRowsOptions仅仅是读取行数据，数据返回的是一行，没有对应的Key，默认情况下，空单元格会被直接忽略，返回的行数据都是有值的，当需要返回包含空的单元格时，配置ReadEmptyCell为true，同时指定Excel的列信息ColumnHeaders数组，里面的元素为"A" "B" "C"等，即表头列信息，Excel中可看到

3.4.1 读取表头

/// <summary>
        /// 读取表头
        /// </summary>
        private static void ReadHeaders()
        {
            //sheetIndex为Sheet索引(从1开始)，rowLine为行号(从1开始) 一般第一行表示列头，具体根据实际情况确定
            var headers = LiteDataHelper.ReadOneRow(filePath: "动态测试导出文件.xlsx", sheetIndex: 1, rowLine: 1);
            Console.WriteLine($"表头为：{string.Join("  ", headers)}");
        }

效果

3.4.2 读取行数据和行数

  /// <summary>
        /// 读取行数据和行数
        /// </summary>
        private static void ReadExcelDatas()
        {
            var context = ContextFactory.GetReadContext();
            // 读取行数 包含列头
            var count = context.ReadSheetRowsCount("动态测试导出文件.xlsx", new ReadSheetRowsCountOptions { });
            // 读取行数据 
            StringBuilder sb = new StringBuilder();
            context.ReadRows("动态测试导出文件.xlsx", new ReadRowsOptions()
            {
                RowData = rowdata =>
                {
                    sb.Append(JsonConvert.SerializeObject(rowdata) + "\n");
                }
            });
            Console.WriteLine($"读取的数据为:{sb.ToString()}");
        }

效果

3.5 完整代码示例

下面是完整代码示例，增加了文件打开占用的判断。

using ExcelKit.Core.Attributes;
using ExcelKit.Core.ExcelRead;
using ExcelKit.Core.Helpers;
using ExcelKit.Core.Infrastructure.Factorys;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Threading.Tasks;

namespace BigDataExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("测试ExcelKit导出");
            DynamicExport();
        }
        /// <summary>
        /// 并发线程导出示例
        /// </summary>
        private static void TaskExport()
        {
            string filename = "测试导出文件xlsx";
            if (IsFileInUse(filename + ".xlsx"))
            {
                Console.WriteLine("文件正在占用，请先关闭文件");
            }
            else
            {
                using (var context = ContextFactory.GetWriteContext(filename))
                {
                    // 创建第一个sheet 采用了并发多Sheet导出，一个线程一个Sheet，非必须
                    Parallel.For(1, 4, index =>
                    {
                        var sheet = context.CrateSheet<Person>($"Sheet1");
                        for (int i = 0; i < 1000; i++)
                        {
                            sheet.AppendData($"Sheet1", new Person { UserName = $"1-{i}-小明", Address = $"1-{i}-苏州", Age = 30, Hobby = "读书", Pwd = "123456" });
                        }
                    });
                }
            }       
        
        }
        /// <summary>
        /// 基于实体类导出，需要在实体类配置ExcelKit的注解
        /// </summary>
        private static void Export()
        {
            string filename = "测试导出文件";
            if (IsFileInUse(filename + ".xlsx"))
            {
                Console.WriteLine("文件正在占用，请先关闭文件");
            }
            else
            {
                using (var context = ContextFactory.GetWriteContext("测试导出文件"))
                {
                    // 创建第一个sheet
                    var sheet = context.CrateSheet<Person>($"Sheet1");
                    for (int i = 0; i < 1000; i++)
                    {
                        sheet.AppendData($"Sheet1", new Person { UserName = $"1-{i}-小明", Address = $"1-{i}-苏州", Age = 30, Hobby = "读书", Pwd = "123456" });
                    }

                    // 创建第二个sheet
                    var sheet2 = context.CrateSheet<Person>($"Sheet2");
                    for (int i = 0; i < 1500; i++)
                    {
                        sheet2.AppendData($"Sheet2", new Person { UserName = $"2-{i}-小明", Address = $"2-{i}-苏州", Age = 30, Hobby = "读书", Pwd = "123456" });
                    }
                    string filePath = context.Save();
                    Console.WriteLine($"文件路径：{filePath}");
                }
            }
        }
        /// <summary>
        /// 动态指定列导出
        /// </summary>
        private static void DynamicExport()
        {
            string filename = "动态测试导出文件";
            if (IsFileInUse(filename+ ".xlsx"))
            {
                Console.WriteLine("文件正在占用，请先关闭文件");
            }
            else
            {
                using (var context = ContextFactory.GetWriteContext(filename))
                {
                    // 定义导出列属性
                    List<ExcelKitAttribute> excelKitAttributes = new List<ExcelKitAttribute>();
                    excelKitAttributes.Add(new ExcelKitAttribute
                    {
                        Code = "UserName",
                        Desc = "用户名",
                        Width = 30,
                        Sort = 10
                    });
                    excelKitAttributes.Add(new ExcelKitAttribute
                    {
                        Code = "Pwd",
                        Desc = "密码",
                        Width = 20,
                        Sort = 20
                    });

                    excelKitAttributes.Add(new ExcelKitAttribute
                    {
                        Code = "Address",
                        Desc = "住址",
                        Width = 50,
                        Sort = 30
                    });

                    // 创建第一个自定义列属性的sheet
                    var sheet = context.CrateSheet($"Sheet1", excelKitAttributes);
                    // 循环插入行记录
                    for (int i = 0; i < 600; i++)
                    {
                        sheet.AppendData($"Sheet1", new Dictionary<string, object> {
                        {"UserName",$"小明-{i}"}
                        ,{"Pwd","123456" }
                        ,{"Address",$"苏州-{i}"}
                    });
                    }
                    // 保存文件
                    string filePath = context.Save();
                    Console.WriteLine($"动态导出文件路径：{filePath}");
                }
            }
        }

        /// <summary>
        /// 读取表头
        /// </summary>
        private static void ReadHeaders()
        {
            //sheetIndex为Sheet索引(从1开始)，rowLine为行号(从1开始) 一般第一行表示列头，具体根据实际情况确定
            var headers = LiteDataHelper.ReadOneRow(filePath: "动态测试导出文件.xlsx", sheetIndex: 1, rowLine: 1);
            Console.WriteLine($"表头为：{string.Join("  ", headers)}");
        }
        /// <summary>
        /// 读取行数据和行数
        /// </summary>
        private static void ReadExcelDatas()
        {
            var context = ContextFactory.GetReadContext();
            // 读取行数 包含列头
            var count = context.ReadSheetRowsCount("动态测试导出文件.xlsx", new ReadSheetRowsCountOptions { });
            // 读取行数据 
            StringBuilder sb = new StringBuilder();
            context.ReadRows("动态测试导出文件.xlsx", new ReadRowsOptions()
            {
                RowData = rowdata =>
                {
                    sb.Append(JsonConvert.SerializeObject(rowdata) + "\n");
                }
            });
            Console.WriteLine($"读取的数据为:{sb.ToString()}");
        }

        /// <summary>
        /// 判断文件是否被占用
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static bool IsFileInUse(string fileName)
        {
            bool inUse = true;

            FileStream fs = null;
            try
            {
                fs = new FileStream(fileName, FileMode.Open,FileAccess.Read,FileShare.None);
                inUse = false;
            }
            catch
            {
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }                    
            }
            return inUse;//true表示正在使用,false没有使用
        }

    }
}

四、总结

ExcelKit支持百万级的数据导出，占用内存较小，使用也比较方便，有需要的朋友可以试一下！

源码地址：https://github.com/hgmsq/BigDataExcel