快速,灵活的Excel导入和导出工具（译文）

原文地址：https://www.codeproject.com/Articles/5280754/autoexcel-user-manual

原文作者：IvanFeng

译文由本站翻译

前言

With AutoExcel, you can quickly import and export excel without hard coding, and embrace changes. 借助AutoExcel,您无需硬编码即可快速导入和导出excel,并接受更改. AutoExcel is very simple and only requires a small amount of code to complete complex import and export. When using it, programmers have no sense of import and export, that is, there is no need to directly manipulate POI. AutoExcel非常简单,只需要少量代码即可完成复杂的导入和导出.使用它时,程序员没有导入和导出的感觉,也就是说,不需要直接操纵POI.

• (Download source (GitHub)) 下载源(GitHub)

(Why AutoExcel?) 为什么选择AutoExcel？

(Excel import and export is very common in software development, as long as you are a programmer, you have met. I believe that many people will choose to use Apache POI to complete this work like me. While feeling the power of POI, my team also encountered the following problems:) Excel的导入和导出在软件开发中非常普遍,只要您是程序员,就一定会遇到.我相信许多人会选择像我一样使用Apache POI完成这项工作.在感受到POI的力量的同时,我的团队还遇到以下问题:

• (Directly use POI to operate Excel will generate a lot of hard code, you will hardly write row index and column index in the code.) 直接使用POI来操作Excel会生成很多硬代码,几乎不会在代码中写行索引和列索引.

• (A large number of non-reusable format control codes, such as background color, alignment, cell style, etc.) 大量不可重复使用的格式控制代码,例如背景色,对齐方式,单元格样式等.

• (The implementation consultant clearly provided a ready-made template, but had to develop the code to implement it again, resulting in low development efficiency.) 实施顾问显然提供了现成的模板,但是不得不开发代码以再次实施它,导致开发效率低下.

• (Development resources have to be used when the template is adjusted.) 调整模板时必须使用开发资源.

• (Simple export also requires specific code.) 简单的导出还需要特定的代码.

(AutoExcel) AutoExcel(solves the above problems. It is very simple and only requires a small amount of code to complete complex import and export. When using it, programmers have no sense of import and export, that is, there is no need to directly manipulate POI. At the same time, the implementation consultant provides Excel is the import and export template, unless new data sources or fields are added, the template update does not need to use development resources.) 解决了以上问题.它非常简单,只需要少量代码即可完成复杂的导入和导出.使用它时,程序员没有导入和导出的感觉,也就是说,不需要直接操纵POI.同时,实施顾问提供的Excel是导入和导出模板,除非添加了新的数据源或字段,否则模板更新不需要使用开发资源. (AutoExcel) AutoExcel(does not over-encapsulate the POI, but makes full use of Excel’s own feature-the name manager, through some tricks, the cell and the data source are mapped, thereby decoupling the programmer and the POI, and avoid hard code, so that import and export work becomes enjoyable and no longer boring.) 不会过度封装POI,而是充分利用Excel自身的功能-名称管理器,通过一些技巧,将单元格和数据源进行映射,从而使程序员和POI脱钩,并避免使用硬代码,因此可以导入出口工作变得愉快而不再无聊.

(Function preview) 功能预览

(Before export) 导出前

(After export) 导出后

(To achieve the above export, you only need to write the following small amount of code (you need additional code to prepare the data source, for example, from the database)) 要实现上述导出,您只需要编写以下少量代码(您需要其他代码来准备数据源,例如从数据库中)

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

List<Product> products = DataGenerator.genProducts();
TemplateExportPara para3 = new TemplateExportPara("Product", products);
para3.setInserted(true);
paras.add(para3);

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

ExcelSetting excelSetting = new ExcelSetting();
excelSetting.setRemovedSheets(Arrays.asList("will be removed"));

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

(Know the template) 了解模板

(To achieve the above export, you first need to complete the production of the template. Some report creation tools such as Microsoft’s RDL, you will make the export model in RDL, and then export the data to Excel in combination with the code. In this process, RDL only acts as an intermediary. It means that every time there is a new export task, an export model must be made first. In AutoExcel, Excel is the template. If your Excel comes from an implementation consultant, it is very likely that this Excel has already set the data format, cell style, etc. And it is waiting for you to fill in the data. In that case, why not use this Excel as our export template, what we have to do is just add our stuff to it.) 要实现上述导出,您首先需要完成模板的生产.一些报表创建工具(例如Microsoft的RDL),您将在RDL中创建导出模型,然后将数据与代码一起导出到Excel.在此过程中,RDL仅充当中介.这意味着每当有新的导出任务时,都必须首先创建导出模型.在AutoExcel中,Excel是模板.如果您的Excel来自实施顾问,则很有可能该Excel已经设置了数据格式,单元格样式等.它正在等待您填写数据.在那种情况下,为什么不使用此Excel作为我们的导出模板,我们要做的就是在其中添加内容.

(Name manager) 名称管理

(The name manager in Excel, a feature that is ignored by most people, has become a bridge between data sources and cells in AutoExcel. You can open the name manager by clicking the menu) Excel中的名称管理器已被大多数人忽略,它已成为AutoExcel中数据源和单元格之间的桥梁.您可以通过单击菜单打开名称管理器(Formula->Name Manager) 公式->名称管理器(. Each name corresponds to a specific location in Excel. It can be a region or a cell. Of course, here, the names we defined all point to cells. So it can be understood that the name manager is used to name cells. It is precisely because the cell has a name that we can automatically assign a value to the cell without the need for personalized code.) .每个名称对应于Excel中的特定位置.它可以是一个区域或一个单元.当然,在这里,我们定义的名称都指向单元格.因此可以理解,名称管理器用于命名单元格.正是因为该单元格具有名称,我们可以自动为该单元格分配一个值,而无需个性化代码.

(After defining the name for the cell, when you click on the cell again, you will find the name you just defined is displayed in the upper left corner.) 定义单元格的名称后,再次单击该单元格时,您会发现刚定义的名称显示在左上角.

(In addition to adding new names in the name manager, there is another way that is more intuitive and faster. Click on the cell you want to name, then directly enter the name in the upper left corner, and finally press the Entry button. It is recommended to create names in this way.) 除了在名称管理器中添加新名称之外,还有另一种更直观,更快捷的方法.单击要命名的单元格,然后直接在左上角输入名称,最后按Enter键.建议以这种方式创建名称.

(Name rule) 命名规则

(Because the cell name determines what kind of data and how to fill in, it must be named according to the following rules:) 由于单元名称决定了哪种数据以及如何填写,因此必须根据以下规则进行命名:

• (DataSourceName.FieldName[.AggregateType], used to fill common fields or aggregate of common fields, e.g. product.SaleArea.sum) DataSourceName.FieldName [.AggregateType],用于填充公共字段或公共字段的集合,例如product.SaleArea.sum

• (DataSourceName.Formula.xxxx, used to fill the formula, e.g. product.Formula.1) DataSourceName.Formula.xxxx,用于填充公式,例如产品公式1

• (DataSourceName.RowNo, used to fill the row number, e.g. product.RowNo) DataSourceName.RowNo,用于填充行号,例如产品行号

(All names are not case sensitive) 所有名称都不区分大小写(, the following will be introduced according to specific scenarios.) ,下面将根据具体情况进行介绍.

(Export) 导出

(Basic object) 基本对象

(As shown in the figure, the name of each cell is indicated in the remarks, written in accordance with the rules of) 如图所示,每个单元格的名称均在备注中指出,并按照 DataSourceName.FieldName (java code：) Java代码:

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

(Single table) 单表

(If you want to export a list of data, you only need to name it according to the writing rules of the base object. Of course, the export of list data is often more complicated than the basic object. For example, you may need a column of row numbers, but you don’t want to do special processing in the code. At this time, you can use) 如果要导出数据列表,则只需根据基础对象的编写规则对其进行命名.当然,列表数据的导出通常比基本对象要复杂.例如,您可能需要一列行号,但是您不想在代码中进行特殊处理.目前,您可以使用 DataSourceName.RowNo (to hand over the work to AutoExcel to process. Note that RowNo is a built-in field. If this field is included in the data source, it will be overwritten.) 将工作移交给AutoExcel处理.请注意,RowNo是一个内置字段.如果该字段包含在数据源中,它将被覆盖. (There is also a very common situation, you have a cell with a formula in the table, such as:) 还有一种非常常见的情况,您在表中有一个带有公式的单元格,例如: =E6+F6 (, you want the cell in the next row to be assigned the value) ,您希望为下一行的单元格分配值 =E7+F7 (. At this time, you should use) .此时,您应该使用 DataSourceName.Formula.xxxx (, you can use any formula you like, and AutoExcel will automatically fill it for you eventually. You can write whatever you want at the part of) ,您可以使用任何喜欢的公式,AutoExcel最终会自动为您填充.您可以在其中编写任何内容 xxxx (, as long as the name is unique. Formula is also a built-in field.) ,只要名称是唯一的.公式也是一个内置字段. (java code：) Java代码:

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

(Multi-table) 多表

(Export multiple tables in one Sheet. If you have such a requirement, please set the export parameter of the table that is not at the bottom in the background code to:) 在一张工作表中导出多个表.如果您有此要求,请将后台代码中不在底部的表的导出参数设置为: setInserted(true) (. As shown in the figure above, the export parameter para corresponding to products should be set as follows:) .如上图所示,与产品相对应的导出参数对应设置如下: para.setInserted(true) (. You know, AutoExcel does not care about whether there is enough space for data export, it will only output continuously. So when your template space is not enough, you need to tell AutoExcel, and then AutoExcel will make enough space to hold your data before exporting.) .您知道,AutoExcel并不关心是否有足够的空间来导出数据,它只会连续输出.因此,当模板空间不足时,您需要告诉AutoExcel,然后AutoExcel将在导出之前腾出足够的空间来容纳数据. (A new naming rule is introduced here:) 此处引入了新的命名规则: DataSourceName.FieldName.AggregateType (, used to total the specified fields. Currently, two aggregate types are supported: Sum and Avg.) ,用于总计指定的字段.当前,支持两种聚合类型:Sum和Avg. (java code：) Java代码:

String templatePath = this.getClass().getResource("/template/Common.xlsx").getPath();
String outputPath = this.getClass().getResource("/").getPath() + "ExportWithTemplate.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);

(Fill data to the right) 向右填充数据

(If you need to fill the data to the right instead of down, you just need to use) 如果您需要在右侧而不是向下填充数据,则只需使用 setDataDirection(DataDirection.Right) (.) . (java code：) Java代码:

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

(Export directly) 直接导出

(Export directly, that is, the export process does not require the use of templates, and is suitable for integration into the general export function of the back-end system. The code is very simple.) 直接导出,即,导出过程不需要使用模板,并且适合集成到后端系统的常规导出功能中.代码很简单.

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

(effect：) 影响:

(Of course, you don’t like this kind of title and title order, so you need to use FieldSetting to make your title readable and display in the order you like.) 当然,您不喜欢这种标题和标题顺序,因此您需要使用FieldSetting使标题可读并以您喜欢的顺序显示.

List<FieldSetting> fieldSettings = new ArrayList<FieldSetting>() {{
    add(new FieldSetting("projName", "Project Name"));
    add(new FieldSetting("basalArea", "Basal Area"));
    add(new FieldSetting("buildingArea", "Building Area"));
    add(new FieldSetting("insideArea", "Inside Area"));
    add(new FieldSetting("availableArea", "Available Area"));
    add(new FieldSetting("availablePrice", "Available Price"));
    add(new FieldSetting("availableAmount", "Available Amount"));
}};
String outputPath = this.getClass().getResource("/").getPath() + "Export.xlsx";
DirectExportPara para = new DirectExportPara(DataGenerator.genProjects(), "Projects", fieldSettings);
AutoExcel.save(outputPath, para);

(final effect:) 最终效果:

(Custom action) 自定义动作

(AutoExcel is committed to dealing with general scenarios of import and export. If there is a personalized demand, you should take back the control of) AutoExcel致力于处理一般的进出口方案.如果有个性化需求,则应收回对 Workbook (and perform personalized processing according to your needs. The save method provides two) 并根据您的需求进行个性化处理.保存方法提供两个 Consumers (, of which) , 其中 actionAhead (will be called before the export operation starts, and) 将在导出操作开始之前被调用,并且 actionBehind (will be called after the export is completed. You can use these two) 导出完成后将被调用.您可以使用这两个 Consumers (to add the functions you want.) 添加所需的功能.

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

(Import) 导入

(Compared with export, import has the following characteristics:) 与出口相比,进口具有以下特点:

• (Only one name rule is supported:) 仅支持一个名称规则: DataSourceName.FieldName (.) .

• (The situation where there are multiple tables in one sheet is not currently supported.) 当前不支持在一张纸中有多个表的情况.

• (The default data reading direction (DataDirection) is) 默认数据读取方向(DataDirection)为 null (, that is to read the basic object. If you need to read the list, you need to specify the reading direction as) ,即读取基本对象.如果需要阅读列表,则需要将阅读方向指定为 Down (. Right direction reading is not currently supported.) .当前不支持正确的方向读取.

(java code：) Java代码:

List<ImportPara> importParas = new ArrayList<ImportPara>() {{
    add(new ImportPara("BusinessUnit"));
    add(new ImportPara("Contract", DataDirection.Down));
    add(new ImportPara("Project", DataDirection.Down));
    //add(new ImportPara("Product", DataDirection.Down));  not supported currently
}};
String fileName = this.getClass().getResource("/").getPath() + "ExportWithTemplate.xlsx";
HashMap<String, List<HashMap<String, Object>>> datas = AutoExcel.read(fileName, importParas);

(Run the sample code) 运行示例代码

(Please go to the unit test to get the complete sample code.) 请转到单元测试以获取完整的示例代码. (GitHub) 的GitHub

许可

本文以及所有相关的源代码和文件均已获得The Code Project Open License (CPOL)的许可。