为什么使用AutoExcel？
Excel导入导出在软件开发中非常常见，只要你接触过开发，就一定会遇到。相信很多人会跟我一样选择用Apache POI来完成这项工作，在感受到POI功能强大的同时，我的团队也遇到了以下问题：
- 直接使用POI操作Excel将产生大量硬编码，你会在编码中写死行索引和列索引
- 大量不可复用的格式控制编码，如背景色、对齐方式、单元格样式等
- 实施顾问明明提供了现成的模板，却还要开发用代码实现一遍，开发效率低下
- 模板调整时不得不动用开发资源
- 简单的导出也需要写特定的代码
AutoExcel解决了上述问题，它非常简单，只需要少量的代码即可完成复杂的导入导出；使用它时，程序员对导入导出无感，即不需要直接操作POI；与此同时，实施顾问提供的Excel即是导入导出模板，除非新增数据源或字段，否则模板更新不需要动用开发资源。
AutoExcel并没有对POI进行过重的封装，而是充分利用了Excel本身具有的特性——名称管理器，通过一些小技巧，将单元格与数据源产生映射，从而解耦程序员与POI，避免产生硬编码，让导入导出工作变得愉快而不再是枯燥乏味。
版本说明
当前版本：v2.0.0
支持Excel格式：2007
特点
- 模板导出
- 支持多个sheet
- 支持基础对象和表格数据
- 单个sheet支持多个不定长数据源
- 支持横向填充数据
- 自动应用单元格样式
- 自动填充行号
- 自动填充公式
- 自动合计
- 直接导出
- 支持多个sheet
- 导出带基本样式
- 自动列宽
- 导入
- 支持多个sheet
- 数据类型自动转换
- 支持百万数据秒级导入导出
功能预览
| 导出前 | 导出后 |
|---|---|
image
|
image
|
image
|
image
|
image
|
image
|
image
|
image
|
实现以上所有导出只需要编写以下少量代码（你需要额外的代码来准备数据源，例如从数据库中获取。示例中使用DataGenerator生成demo数据）
// 设置导出参数，如数据源名称、数据源等
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);
// 单个sheet有多个数据源时，上方数据源应设置为插入
para3.setInserted(true);
paras.add(para3);
TemplateExportPara para5 = new TemplateExportPara("Product2", products);
// 横向填充
para5.setDataDirection(DataDirection.Right);
paras.add(para5);
//（可选操作）移除不需要的sheet
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
除了在名称管理器中新增名称，还有一种方式更加直观快捷。点击你想要命名的单元格，然后直接在左上角输入名称，最后按Entry键即可。推荐使用这种方式创建名称。
image
名称规则
由于单元格名称决定了何种数据按什么方式进行填充，因此必须按以下规则进行命名：
- 数据源名称.字段名称[.合计类型]，用于填充普通字段或普通字段的合计值，如：product.SaleArea.sum
- 数据源名称.Formula.xxxx，用于填充公式，如：product.Formula.1
- 数据源名称.RowNo，用于填充行号，如：product.RowNo
所有名称均不区分大小写，以下会根据具体场景分别进行介绍
导出
基础对象
image
如图所示，批注中注明了每个单元格的名称，按照数据源名称.字段名称的规则书写
String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
//DataGenerator.genBusinessUnit()用于生成demo数据
TemplateExportPara para = new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit());
AutoExcel.save(templatePath, outputPath, para);
单表
image
如果你想导出的是一个列表数据，你只需要按照基础对象的书写规则进行命名即可。当然，列表数据导出往往比基础对象复杂，比如，你可能需要一列行号，而又不想在代码中做特殊处理，这时候你可以使用数据源名称.RowNo，将工作交给AutoExcel来处理。注意，RowNo是内置字段，如果数据源中包含此字段，将会被覆盖。
还有一种情况非常常见，你有一个带公式的单元格在表格中，如：=E6+F6，你希望下一行的单元格被赋值=E7+F7，这时你应该使用数据源名称.Formula.xxxx，你可以使用任何你喜欢的公式，AutoExcel最终都会帮你自动填充。xxxx的部分你可以随便书写，只要保证名称唯一即可。Formula同样是内置字段。
String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
//DataGenerator.genContracts()用于生成demo数据
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会在导出前腾出足够的空间来容纳你的数据。
这里引入了新的命名规则：数据源名称.字段名称.合计类型，用于对指定字段进行合计，目前支持两种合计类型：Sum和Avg。
String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
List<TemplateExportPara> paras = new ArrayList<>();
//DataGenerator.genProjects()用于生成demo数据
paras.add(new TemplateExportPara("Project", DataGenerator.genProjects()));
//DataGenerator.genProducts()用于生成demo数据
TemplateExportPara para = new TemplateExportPara("Product", DataGenerator.genProducts());
para.setInserted(true); //导出空间不够时需设置
paras.add(para);
AutoExcel.save(templatePath, outputPath, paras);
横向填充
image
如果你需要将数据向右而不是向下填充，你只需要使用setDataDirection(DataDirection.Right)即可。
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); //向右填充
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
另外，你也可以一次导出多个sheet
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方法提供了两个Consumer，其中actionAhead会在导出操作开始之前被调用，actionBehind会在导出完成之后被调用，你完全可以通过这两个Consumer来添加你想要的功能。
String templatePath = this.getClass().getResource("/template/Export.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "AutoExcel.xlsx";
List<TemplateExportPara> paras = new ArrayList<>();
paras.add(new TemplateExportPara("BusinessUnit", DataGenerator.genBusinessUnit()));
Consumer<Workbook> actionAhead = Workbook -> {
//做任何你想做的事情
};
Consumer<Workbook> actionBehind = workbook -> {
//做任何你想做的事情
};
AutoExcel.save(templatePath, outputPath, paras, actionAhead, actionBehind);
导入
V2.0.0中的导入不再使用模板，因此需要通过FieldSetting指定列名和字段名的映射关系，该映射关系可能存储在你的数据库中。支持多个sheet同时导入，可指定标题行索引和数据行索引。
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);
// 方式一、获取原始数据，没有类型转换，可通过这种方式检验数据是否符合要求
List<Map<String, Object>> products = dataSet.get("Product");
List<Map<String, Object>> projects = dataSet.get("Project");
// 方式二、通过sheet索引获取指定类的数据，类型自动转换，转换失败将抛出异常
// List<Product> products = dataSet.get(0, Product.class);
// List<Project> projects= dataSet.get(1, Project.class);
// 方式三、通过sheet名称获取指定类的数据，类型自动转换，转换失败将抛出异常
// 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构造方法入参：
- sheetIndex：必填，sheet索引
- fieldSettings：必填，列名与字段名映射设置
- titleIndex：可省略，标题行开始索引，默认为0
- dataStartIndex：可省略，数据行开始索引，默认为1
为什么使用FieldSetting而不采用注解的方式声明列名？
- 非侵入式，不影响原来的代码
- 在设计系统的时候，为了重用相同的配置，如页面展示、导出、导入、打印等，所有这些都展示相同的列名，我们会将这些配置存放在存储介质如数据库，待使用时加载出来，这种方式也可以避免硬编码，还可以方便地进行动态配置，采用FieldSetting就是为了配合这种方式。AutoExcel尽可能让导入导出融入到你的自动化系统。
百万数据耗时测试
单位：毫秒
| 10W行10列数据 | 100W行10列数据 | |
|---|---|---|
| 模板导出 | 6,258 | 23,540 |
| 直接导出 | 5,711 | 24,952 |
| 导入 | 4,466 | 21,595 |
| 导入+类型转换 | 4,823 | 26,279 |
运行示例代码
完整的示例代码请前往这里查看
image
网友评论