65.9K
CodeProject 正在变化。 阅读更多。
Home

Excel 的快速灵活导入导出工具

IvanFeng
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.09/5 (7投票s)

2020 年 10 月 8 日

MIT

8分钟阅读

viewsIcon

21507

使用 AutoExcel，您可以快速地导入和导出 Excel，无需硬编码，拥抱变化。

为什么选择 AutoExcel？

Excel 导入导出在软件开发中非常普遍，只要您是程序员，您肯定遇到过。我相信很多人和我一样，会选择使用 Apache POI 来完成这项工作。在感受 POI 强大功能的同时，我的团队也遇到了以下问题

  1. 直接使用 POI 操作 Excel 会产生大量的硬编码，您很难在代码中写出行索引和列索引。
  2. 大量的不可复用的格式控制代码，例如背景色，对齐方式，单元格样式等。
  3. 实施顾问明确给出了现成的模板，但不得不重新写代码去实现，导致开发效率低下。
  4. 模板调整的时候，开发资源不得不去使用。
  5. 简单的导出也需要专门的代码。

AutoExcel 解决了以上问题。它非常简单，只需少量代码即可完成复杂的导入导出。在使用它时，程序员没有导入导出的感知，即不需要直接操作 POI。同时，由实施顾问提供的 Excel 就是导入导出模板，除非增加新的数据源或字段，否则模板的更新无需动用开发资源。

AutoExcel 没有过度封装 POI，而是充分利用 Excel 本身的功能 - 名称管理器，通过一些技巧，将单元格与数据源映射起来，从而解耦了程序员和 POI，避免了硬编码，让导入导出工作变得愉快，不再枯燥。

出版信息

  • 当前版本：v2.0.0
  • 支持 Excel 格式：2007

特点

  • 带模板导出

    • 支持多工作表
    • 支持基础对象和表格数据
    • 单个工作表支持多个可变长度数据源
    • 支持数据横向填充
    • 自动应用单元格样式
    • 自动填充行号
    • 自动填充公式
    • 自动汇总

  • 直接导出

    • 支持多工作表
    • 带基础样式导出
    • 自动调整列宽

  • 导入

    • 支持多工作表
    • 自动数据类型转换

  • 支持百万级数据秒级导入导出

功能预览

导出前

image

image

image

image

导出后

image

image

image

image

要实现以上所有导出，您只需要写以下少量代码（您需要额外代码准备好数据源，例如从数据库。下面的示例，使用 DataGenerator 来生成演示数据。）

// Set export parameters, such as data source name, data source, etc.
List<TemplateExportPara> paras = new ArrayList<>();
paras.add(new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit()));
paras.add(new TemplateExportPara("Contract", DataGenerator.genContracts()));
paras.add(new TemplateExportPara("Project", DataGenerator.genProjects(1)));

List<Product> products = DataGenerator.genProducts(1);
TemplateExportPara para3 = new TemplateExportPara("Product", products);
// When a single sheet has multiple data sources, 
// the data source above should be set to inserted
para3.setInserted(true);
paras.add(para3);

TemplateExportPara para5 = new TemplateExportPara("Product2", products);
// Horizontal fill
para5.setDataDirection(DataDirection.Right);
paras.add(para5);

// (Optional operation) Remove unnecessary sheets
ExcelSetting excelSetting = new ExcelSetting();
excelSetting.setRemovedSheets(Arrays.asList("will be removed"));

AutoExcel.save(this.getClass().getResource("/template/Export.xlsx").getPath(),
               this.getClass().getResource("/").getPath() + "AutoExcel.xlsx",
               paras,
               excelSetting);

Maven

<dependency>
  <groupId>net.fenghaitao</groupId>
  <artifactId>auto-excel</artifactId>
  <version>2.0.0</version>
</dependency>

了解模板

要实现以上导出，您首先需要完成模板的制作。一些报表制作工具，例如微软的 RDL，您会在 RDL 中做出导出模型，然后将数据导出到 Excel 中，再与代码结合。在这个过程中，RDL 只是一个中间件。它意味着每一次有新的导出任务，就必须先做出一个导出模型。在 AutoExcel 中，Excel 就是模板。如果您的 Excel 来自于实施顾问，那么很有可能这个 Excel 已经设置好了数据格式、单元格样式等。并且等待着您去填充数据。那么，为什么不使用这个 Excel 作为我们的导出模板，我们所要做的仅仅是添加我们自己的东西到里面。

名称管理器

Excel 中的名称管理器，一个被大多数人忽略的功能，在 AutoExcel 中成为了数据源与单元格之间的桥梁。您可以通过点击菜单 公式 -> 名称管理器 来打开名称管理器。每个名称对应 Excel 中的一个特定位置，它可以是一个区域，也可以是一个单元格。当然，在这里，我们定义的名称全部指向单元格。因此，可以理解为名称管理器是用来给单元格命名的。正是因为单元格有了名称，我们才能在不需要个性化代码的情况下，自动地给单元格赋值。

image

为单元格定义好名称后，当您再次点击该单元格时，您会发现在左上角显示着您刚刚定义的名称。

image

除了在名称管理器中添加新名称，还有一种方式更直观、更快速。点击您想命名的单元格，然后在左上角直接输入名称，最后按下 回车 键。推荐通过这种方式创建名称。

image

命名规则

由于单元格的名称决定了填入什么样的数据，以及如何填充，因此必须按照以下规则来命名

  1. DataSourceName.FieldName[.AggregateType], 用于填充普通字段或普通字段的聚合，例如：product.SaleArea.sum
  2. DataSourceName.Formula.xxxx, 用于填充公式，例如：product.Formula.1
  3. DataSourceName.RowNo, 用于填充行号，例如：product.RowNo

所有名称均不区分大小写，下面将根据具体场景进行介绍。

导出

基础对象

image

如图所示，每个单元格的名称都写在备注里，按照 DataSourceName.FieldName 的规则来写。

Java 代码：

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
//DataGenerator.genBusinessUnit() used to generate demo data
TemplateExportPara para = 
    new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit());
AutoExcel.save(templatePath, outputPath, para);

单表

image

如果您想导出数据列表，只需按照基础对象的书写规则进行命名即可。当然，列表数据的导出往往比基础对象复杂。例如，您可能需要一列行号，但又不希望在代码中做特殊处理。这时，您可以使用 DataSourceName.RowNo 来将工作交给 AutoExcel 处理。注意 RowNo 是一个内置字段，如果该字段包含在您的数据源中，它将被覆盖。

还有一种非常常见的情况，您在表格中有一个带有公式的单元格，例如：=E6+F6，您希望下一行的单元格被赋值为 =E7+F7。这时，您应该使用 DataSourceName.Formula.xxxx，您可以使用任何喜欢的公式，AutoExcel 最终都会为您自动填充。xxxx 部分您可以写任何内容，只要名称唯一即可。Formula 也是一个内置字段。

Java 代码：

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
//DataGenerator.genContracts() used to generate demo data
TemplateExportPara para = new TemplateExportPara("Contract", DataGenerator.genContracts());
AutoExcel.save(templatePath, outputPath, para);

如果您运行了上面的代码，您会发现 AutoExcel 自动地为您应用了单元格样式。您希望应用到导出数据上的任何样式，都可以通过在模板中设置数据起始行（也就是您命名的那个单元格所在的行）的样式来控制。

image

多表

image

在单个 Sheet 中导出多个表。如果您有这样的需求，请在后台代码中将非底部的表的导出参数设置为：setInserted(true)。如图所示，与 products 对应的导出参数 para 应该设置为：para.setInserted(true)。您知道，AutoExcel 不关心是否有足够的空间来导出数据，它只会连续输出。所以当您的模板空间不够时，您需要告诉 AutoExcel，然后 AutoExcel 会在导出之前腾出足够的空间来容纳您数据。

这里引入了一个新的命名规则：DataSourceName.FieldName.AggregateType，用于对指定字段进行汇总。目前支持两种聚合类型：Sum 和 Avg。

Java 代码：

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
List<TemplateExportPara> paras = new ArrayList<>();
//DataGenerator.genProjects() used to generate demo data
paras.add(new TemplateExportPara("Project", DataGenerator.genProjects()));

//DataGenerator.genProducts() used to generate demo data
TemplateExportPara para = new TemplateExportPara("Product", DataGenerator.genProducts());
para.setInserted(true);  //Need to set when the space is not enough in the template
paras.add(para);

AutoExcel.save(templatePath, outputPath, paras);

横向填充

image

如果您需要向右填充数据而不是向下填充，您只需要使用 setDataDirection(DataDirection.Right) 即可。

Java 代码：

String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
TemplateExportPara para = new TemplateExportPara("Product2", DataGenerator.genProducts());
para.setDataDirection(DataDirection.Right);  //Fill data to the right
AutoExcel.save(templatePath, outputPath, para);

直接导出

直接导出，也就是说导出过程不需要使用模板，适合集成到后端系统的通用导出功能中。代码非常简单。

String outputPath = this.getClass().getResource("/").getPath() + "Export Directly.xlsx";
DirectExportPara para = new DirectExportPara(DataGenerator.genProjects(1));
AutoExcel.save(outputPath, para);

效果：

image

当然，您不喜欢这种标题以及标题的顺序，那么就需要使用 FieldSetting 来让您的标题可读，并按照您喜欢的顺序显示。

List<FieldSetting> fieldSettings = new ArrayList<FieldSetting>() {{
    add(new FieldSetting("projName", "Project Name"));
    add(new FieldSetting("projInfo", "Project Info."));
    add(new FieldSetting("saleStartDate", "Sales Start Date"));
    add(new FieldSetting("availablePrice", "Available Price"));
    add(new FieldSetting("availableAmount", "Available Amount"));
}};
String outputPath = this.getClass().getResource("/").getPath() + "Export Directly.xlsx";
DirectExportPara para = new DirectExportPara(DataGenerator.genProjects(), "Projects", fieldSettings);
AutoExcel.save(outputPath, para);

最终效果

image

当然，您也可以一次导出多个工作表。

String outputPath = this.getClass().getResource("/").getPath() + "Export Directly.xlsx";
List<DirectExportPara> paras = new ArrayList<>();
paras.add(new DirectExportPara(DataGenerator.genProjects(200), "Projects",
        DataGenerator.genProjectFieldSettings()));
paras.add(new DirectExportPara(DataGenerator.genContracts()));
AutoExcel.save(outputPath, paras);

自定义操作

AutoExcel 致力于处理通用场景的导入导出。如果存在个性化需求，您应该拿回 Workbook 的控制权，并根据您的需求执行个性化处理。save 方法提供了两个 Consumers，其中 actionAhead 将在导出操作开始前调用，actionBehind 将在导出完成后调用。您可以使用这两个 Consumers 来添加您想要的功能。

String templatePath = this.getClass().getResource("/template/Common.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "ExportWithTemplate.xlsx";
List<TemplateExportPara> paras = new ArrayList<>();
paras.add(new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit()));
Consumer<Workbook> actionAhead = Workbook -> {
    //Do whatever you want
};        
Consumer<Workbook> actionBehind = workbook -> {
    //Do whatever you want
};
AutoExcel.save(templatePath, outputPath, paras, actionAhead, actionBehind);

导入

V2.0.0 中的导入不再使用模板，因此您需要通过 FieldSetting 来指定列名与字段名的映射关系，这部分内容可能存储在您的数据库中。支持同时导入多个工作表，并且可以指定标题行索引和数据起始行索引。

List<ImportPara> importParas = new ArrayList<ImportPara>() {{
    add(new ImportPara(0, DataGenerator.genProductFieldSettings()));
    add(new ImportPara(1, DataGenerator.genProjectFieldSettings(), 1, 5));
}};
String fileName = this.getClass().getResource("/template/Import.xlsx").getPath();
DataSet dataSet = AutoExcel.read(fileName, importParas);
// Method 1: Obtain the original data without type conversion, you can
//           check whether the data meets the requirements in this way
List<Map<String, Object>> products = dataSet.get("Product");
List<Map<String, Object>> projects = dataSet.get("Project");
// Method 2: Obtain the data of the specified class through the sheet index, the type is
//           automatically converted, and an exception will be thrown if the conversion fails
// List<Product> products = dataSet.get(0, Product.class);
// List<Project> projects= dataSet.get(1, Project.class);
// Method 3: Obtain the data of the specified class through the sheet name, the type is
//           automatically converted, and an exception will be thrown if the conversion fails
// List<Product> products = dataSet.get("Product", Product.class);
// List<Project> projects = dataSet.get("Project", Project.class);

public static List<FieldSetting> genProjectFieldSettings() {
    List<FieldSetting> fieldSettings = new ArrayList<>();
    fieldSettings.add(new FieldSetting("projName", "Project Name"));
    fieldSettings.add(new FieldSetting("projInfo", "Project Info."));
    fieldSettings.add(new FieldSetting("basalArea", "Basal Area"));
    fieldSettings.add(new FieldSetting("availableArea", "Available Area"));
    fieldSettings.add(new FieldSetting("buildingArea", "Building Area"));
    fieldSettings.add(new FieldSetting("buildingsNumber", "Buildings Number"));
    fieldSettings.add(new FieldSetting("saleStartDate", "Sales Start Date"));
    fieldSettings.add(new FieldSetting("landAcquisitionTime", "Land Acquisition Time"));
    fieldSettings.add(new FieldSetting("availablePrice", "Available Price"));
    fieldSettings.add(new FieldSetting("availableAmount", "Available Amount"));
    fieldSettings.add(new FieldSetting("insideArea", "Inside Area"));
    return fieldSettings;
}

public static List<FieldSetting> genProductFieldSettings() {
    List<FieldSetting> fieldSettings = new ArrayList<FieldSetting>() {{
        add(new FieldSetting("projName", "Project Name"));
        add(new FieldSetting("basalArea", "Basal Area"));
        add(new FieldSetting("availableArea", "Available Area"));
        add(new FieldSetting("buildingArea", "Building Area"));
        add(new FieldSetting("buildingsNumber", "Buildings Number"));
    }};
    return fieldSettings;
}

ImportPara 的参数

  1. sheetIndex: 必需，工作表索引
  2. fieldSettings: 必需，列名与字段名的映射
  3. titleIndex: 可选，标题行索引，从 0 开始
  4. dataStartIndex: 可选，数据起始行索引，从 0 开始

 

引用

为什么使用 FieldSetting 而不是使用注解来声明列名？

  1. 非侵入性，不影响原有代码。
  2. 在系统设计时，为了复用同一份配置，比如页面显示、导出、导入、打印等，都显示相同的列名，我们就会将这些配置存储在数据库等存储介质中，然后在需要时加载。这种方式也可以避免硬编码，并且方便进行动态配置。FieldSetting 就是为了匹配这种方式而存在的。AutoExcel 尽可能的将导入导出集成到您的自动化系统中。

 

百万级数据测试

单位：ms

  10 万行 10 列数据 100 万行 10 列数据
带模板导出 6,258 23,540
直接导出 5,711 24,952
导入 4,466 21,595
导入 + 类型转换 4,823 26,279

运行示例代码

请到单元测试中获取完整的示例代码。

GitHub

image

历史

  • 2020 年 10 月 8 日：初始版本
© . All rights reserved.