preface
A company project recently had a need: report export. Down the whole system, at least more than 100 statements need to be exported. At this time how to implement elegant report export, release productivity is very important. The following is to share with you the use of the tool class and implementation ideas.
Function points implemented
For each report to have the same operation, we naturally separate out, this is easy. And the most important is: how to package those operations that are not the same in each report form well, and improve the reuse as much as possible; In view of the above principles, the following key functions are achieved:
- Export data of any type
- Set the table header freely
- Set the export format of the field freely
Using the instance
The utility class implements three function points, so you can set these three points when using it:
- Set the data list
- Set the header
- Formatting fields
The following export function returns excel data directly to the client. ProductInfoPos is the list of data to be exported. ExcelHeaderInfo is used to store the table header information, including the table header name, the first column, the last column, the first row, and the last row. Because the default exported data format is string, you also need a Map parameter to specify the format type of a field (for example, numeric, decimal, date). It’s good to get a general idea of how to use them, and these parameters will be explained in more detail below.
@Override
public void export(HttpServletResponse response, String fileName) {
// Data to be exported
List<TtlProductInfoPo> productInfoPos = this.multiThreadListProduct();
ExcelUtils excelUtils = new ExcelUtils(productInfoPos, getHeaderInfo(), getFormatInfo());
excelUtils.sendHttpResponse(response, fileName, excelUtils.getWorkbook());
}
// Get the header information
private List<ExcelHeaderInfo> getHeaderInfo(a) {
return Arrays.asList(
new ExcelHeaderInfo(1.1.0.0."id"),
new ExcelHeaderInfo(1.1.1.1."Trade Name"),
new ExcelHeaderInfo(0.0.2.3."Classification"),
new ExcelHeaderInfo(1.1.2.2.Type "ID"),
new ExcelHeaderInfo(1.1.3.3."Category name"),
new ExcelHeaderInfo(0.0.4.5."Brand"),
new ExcelHeaderInfo(1.1.4.4."Brand ID"),
new ExcelHeaderInfo(1.1.5.5."Brand Name"),
new ExcelHeaderInfo(0.0.6.7."Store"),
new ExcelHeaderInfo(1.1.6.6."The store ID"),
new ExcelHeaderInfo(1.1.7.7."Store Name"),
new ExcelHeaderInfo(1.1.8.8."Price"),
new ExcelHeaderInfo(1.1.9.9."Inventory"),
new ExcelHeaderInfo(1.1.10.10."Sales"),
new ExcelHeaderInfo(1.1.11.11."Insertion time"),
new ExcelHeaderInfo(1.1.12.12."Update Time"),
new ExcelHeaderInfo(1.1.13.13."Is the record deleted?")); }// Get formatting information
private Map<String, ExcelFormat> getFormatInfo(a) {
Map<String, ExcelFormat> format = new HashMap<>();
format.put("id", ExcelFormat.FORMAT_INTEGER);
format.put("categoryId", ExcelFormat.FORMAT_INTEGER);
format.put("branchId", ExcelFormat.FORMAT_INTEGER);
format.put("shopId", ExcelFormat.FORMAT_INTEGER);
format.put("price", ExcelFormat.FORMAT_DOUBLE);
format.put("stock", ExcelFormat.FORMAT_INTEGER);
format.put("salesNum", ExcelFormat.FORMAT_INTEGER);
format.put("isDel", ExcelFormat.FORMAT_INTEGER);
return format;
}
Copy the codeImplementation effect
Source code analysis
Ha ha, analyze your own code, a bit interesting. Since it is not convenient to post too much code, you can clone the source code on Github and come back to the article. ✨ source address ✨ LZ use poI 4.0.1 version of this tool, want to use massive data export naturally have to use SXSSFWorkbook this component. I won’t go into details about the use of POI here, but I’ll show you how to encapsulate poI.
Member variables
Let’s focus on the ExcelUtils class, which is the core of the export implementation, and take a look at three member variables.
private List list;
private List<ExcelHeaderInfo> excelHeaderInfos;
private Map<String, ExcelFormat> formatInfo;
Copy the codelist
This member variable is used to hold the data to be exported.
ExcelHeaderInfo
This member variable is mainly used to hold the header information. Since we need to define multiple header information, we need to use a list to hold the header information. ExcelHeaderInfo(int firstRow, int lastRow, int firstCol, int lastCol, String title)
firstRow: The first line of the position occupied by the table headerlastRow: The trailing line of the position occupied by the table headerfirstCol: The first column of the position occupied by the table headerlastCol: The trailing line of the position occupied by the table headertitle: Specifies the name of the table header
ExcelFormat
This parameter is mainly used to format fields. We need to agree in advance which format to change to. So we define a variable of type enumeration, which has only one string member variable to hold the format we want to convert, such as FORMAT_INTEGER to an integer. Because we need to accept multiple field conversion formats, we define a Map type to receive, and this parameter can be omitted (the default format is a string).
public enum ExcelFormat {
FORMAT_INTEGER("INTEGER"),
FORMAT_DOUBLE("DOUBLE"),
FORMAT_PERCENT("PERCENT"),
FORMAT_DATE("DATE");
private String value;
ExcelFormat(String value) {
this.value = value;
}
public String getValue(a) {
returnvalue; }}Copy the codeCore method
1. Create a table header
This method is used to initialize the header, and the key to creating the header is the addMergedRegion(CellRangeAddress Var1) method of the Sheet class in the POI, which is used for cell fusion. We walk through the ExcelHeaderInfo list, fuse the cells according to the coordinates of each ExcelHeaderInfo, then create the cells in the first row and column of each merged cell, and then assign values to the cells. This completes the header setting of any type.
// Create the header
private void createHeader(Sheet sheet, CellStyle style) {
for (ExcelHeaderInfo excelHeaderInfo : excelHeaderInfos) {
Integer lastRow = excelHeaderInfo.getLastRow();
Integer firstRow = excelHeaderInfo.getFirstRow();
Integer lastCol = excelHeaderInfo.getLastCol();
Integer firstCol = excelHeaderInfo.getFirstCol();
// Cell fusion is performed only when the row spacing or column spacing is greater than 0
if((lastRow - firstRow) ! =0|| (lastCol - firstCol) ! =0) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
// Get the first line position of the current table header
Row row = sheet.getRow(firstRow);
// Create a new cell in the first row and column of the table header
Cell cell = row.createCell(firstCol);
// Assign cells
cell.setCellValue(excelHeaderInfo.getTitle());
cell.setCellStyle(style);
sheet.setColumnWidth(firstCol, sheet.getColumnWidth(firstCol) * 17 / 12); }}Copy the code2. Transform data
Before performing literal assignment, we first convert the original list of data into a two-dimensional array of strings. The reason for converting to string format is that it can handle all types uniformly, and then we can convert back if necessary.
// Convert the raw data into a two-dimensional array
private String[][] transformData() {
int dataSize = this.list.size();
String[][] datas = new String[dataSize][];
// Get the number of columns for the report
Field[] fields = list.get(0).getClass().getDeclaredFields();
// Get the array of field names for the entity class
List<String> columnNames = this.getBeanProperty(fields);
for (int i = 0; i < dataSize; i++) {
datas[i] = new String[fields.length];
for (int j = 0; j < fields.length; j++) {
try {
/ / assignment
datas[i][j] = BeanUtils.getProperty(list.get(i), columnNames.get(j));
} catch (Exception e) {
LOGGER.error("Failed to get object property value"); e.printStackTrace(); }}}return datas;
}
Copy the codeIn this method we use reflection technology, very clever implementation of any type of data export (here any type refers to any report type, different reports, exported data must be different, so in the Java implementation of the entity class must be different). To convert a List into a two-dimensional array, we need to know the following:
- The number of columns in a two-dimensional array
- The number of rows in a two-dimensional array
- The value of each element in a two-dimensional array
What if you get all three of these things?
- Through reflection
Field[] getDeclaredFields()This method takes all of the fields of the entity class and indirectly knows how many columns there are - The size of the List is the number of rows in a two-dimensional array
- Although each entity class has a different field name, is it true that we cannot get the value of a field of the entity class? No, you know, you got it
reflectionYou own the whole world, so what else can you do? Instead of using reflection directly, we use something calledBeanUtilsThe tool can be very convenient to help us to an entity class field assignment and field value acquisition. That’s easy. PassBeanUtils.getProperty(list.get(i), columnNames.get(j))So with one line of code, we get the entitylist.get(i)In the name ofcolumnNames.get(j)The value of this field.list.get(i)Of course it’s the entity class that we walk through the raw data, andcolumnNamesA list is an array of all the field names of an entity class, which is also obtained by reflection. For specific implementation, refer to the LZ source code.
3. Assign text
Here the body is designated as formal table data content, in fact, there are not too many strange skills, the main function has been realized above, here is mainly for cell assignment and export format processing (mainly in order to export Excel can carry out convenient operations).
// Create a text
private void createContent(Row row, CellStyle style, String[][] content, int i, Field[] fields) {
List<String> columnNames = getBeanProperty(fields);
for (int j = 0; j < columnNames.size(); j++) {
if (formatInfo == null) {
row.createCell(j).setCellValue(content[i][j]);
continue;
}
if (formatInfo.containsKey(columnNames.get(j))) {
switch (formatInfo.get(columnNames.get(j)).getValue()) {
case "DOUBLE":
row.createCell(j).setCellValue(Double.parseDouble(content[i][j]));
break;
case "INTEGER":
row.createCell(j).setCellValue(Integer.parseInt(content[i][j]));
break;
case "PERCENT":
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
Cell cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(Double.parseDouble(content[i][j]));
break;
case "DATE":
row.createCell(j).setCellValue(this.parseDate(content[i][j])); }}else{ row.createCell(j).setCellValue(content[i][j]); }}}Copy the codeNow that we’re done exporting the core methods of the utility class, let’s talk about multi-threaded queries.
Pull two more
1. Multi-threaded query data
The ideal is full, the reality is a bit skinny. Although LZ creates 20 threads to query 50W data, the overall efficiency is not 50W /20, but only a few seconds faster. If you know the reason, you can leave me a message to discuss with me.
Since multiple threads are executing simultaneously, you can’t guarantee which one will finish first, but you can guarantee that the data order is consistent. Here we use the Callable interface, through the implementation of the Callable interface thread can have the return value, we get the query results of all child threads, and then merged into a result set. So how do you guarantee the order of the merge? We start by creating a List of type FutureTask, the type of which is the result set returned.
List<FutureTask<List<TtlProductInfoPo>>> tasks = new ArrayList<>();
Copy the codeWhen we start a thread, we add that thread’s FutureTask to the tasks list, so that the order of the elements in the tasks list is the order in which we start the thread.
FutureTask<List<TtlProductInfoPo>> task = new FutureTask<>(new listThread(map));
log.info("Start query for {} records beginning with {}", i * THREAD_MAX_ROW, THREAD_MAX_ROW);
new Thread(task).start();
// Add the task to the Tasks list
tasks.add(task);
Copy the codeNext, there is the sequential plug value, which fetches FutureTask sequentially from the Tasks list and executes FutureTask’s get() method, which blocks the calling thread until it gets the return result. This set of loops completes the sequential storage of all data.
for(FutureTask<List<TtlProductInfoPo>> task : tasks) { try { productInfoPos.addAll(task.get()); } catch (Exception e) { e.printStackTrace(); }}Copy the code2. How to solve the interface timeout problem
If a large amount of data needs to be exported, the interface may time out because the entire export process takes a long time. In fact, it is also very easy to solve, the interface response time is too long, we can shorten the response time. We use asynchronous programming solutions. There are many ways to implement asynchronous programming. Here we use the simplest Async annotation in Spring. For the use of annotations, you can refer to the following key implementation steps:
- Writing asynchronous interface, the interface is responsible for receiving the client’s request was derived, and then begin to execute the export (note: the export is not returned to the client directly, but is downloaded to a local server), as long as issued export directive, you can immediately to the client returns a the only sign of the excel file (for later to find the file), end of the interface.
- Write the Excel status interface. After the client gets the unique symbol of the Excel file, it starts to call the interface every second to check the export status of the Excel file
- Write an interface to return excel files from the local server if the client checks that Excel has been successfully downloaded
Local to the serverAt this point, you can request the interface to download the file directly.
This will solve the interface timeout problem.
The source address
Github.com/dearKundy/e…
Source taking posture
- Create table (insert data by yourself)
CREATE TABLE `ttl_product_info` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Record Unique Identifier'.`product_name` varchar(50) NOT NULL COMMENT 'Trade Name'.`category_id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'type ID'.`category_name` varchar(50) NOT NULL COMMENT 'Redundant class names - Avoid cross-table joins'.`branch_id` bigint(20) NOT NULL COMMENT 'the brand ID'.`branch_name` varchar(50) NOT NULL COMMENT 'Redundant brand names - Avoid cross-table joins'.`shop_id` bigint(20) NOT NULL COMMENT 'commodity ID'.`shop_name` varchar(50) NOT NULL COMMENT 'Redundant store names - Avoid cross-table joins'.`price` decimal(10.2) NOT NULL COMMENT 'Current price of goods - this is hot data, and price changes need to be recorded, price detail table is required'.`stock` int(11) NOT NULL COMMENT 'Inventory - Hot Data'.`sales_num` int(11) NOT NULL COMMENT 'sales'.`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Insertion time'.`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'.`is_del` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Has the record been deleted?',
PRIMARY KEY (`id`),
KEY `idx_shop_category_salesnum` (`shop_id`.`category_id`.`sales_num`),
KEY `idx_category_branch_price` (`category_id`.`branch_id`.`price`),
KEY `idx_productname` (`product_name`))ENGINE=InnoDB AUTO_INCREMENT=15000001 DEFAULT CHARSET=utf8 COMMENT='Commodity Information Sheet';
Copy the code- To run the program
- In the browser’s address bar: http://localhost:8080/api/excelUtils/export you can download
Canvassing link
This article is written here, like friends can like, comment, add attention oh!