Article directory
- 1.Background
- 2. Excel features that implement functions
-
- 2.1.Feature introduction
- 2.2. Drop-down box linkage
- 2.3. Cells automatically match IDs
- 2.4. Error message
- 3. Code implementation
-
- 3.1.Basic process code
- 3.2. Name manager configuration
- 3.3. Validity configuration
- 3.4. Function filling
- 3.5. Other supplements
- 4. Summary
1.Background
Recently, I have been working on a related requirement for importing personal sales goals into a CRM system. If I need to import the salesperson’s goals into the system, I need to fill in the salesperson ID and the salesperson’s name in the Excel import template. This is an error-prone point when using it, because if these two fields are left to the user to fill in freely, it is easy to fill in the wrong fields. In addition to overfilling in the text itself, the mapping relationship between the two fields may also be filled in incorrectly.
In order to solve this problem, I checked the information and found that there are several features in Excel that can make the salesperson’s name and ID into a drop-down linkage effect, so that there will be no problem of incorrect filling.
After implementing this function, I found it quite interesting, and there is relatively little information on this aspect on the Internet, so I simply recorded and shared it here.
2. Excel features that implement functions
2.1. Feature introduction
Before implementing the code, first understand the three Excel features that are involved in this function: Name manager, indirect formula, data validity. I am using WPS here, so I will use WPS below. Let’s give an example. Microsoft Office also has the same function in a similar location. Students who use Office can research it on their own.
- Name Manager:
Similar to the function of a data dictionary, there are two main fields:name (key)andreference position (value reference), the so-called reference positionIt is the coordinate of the cell that needs to be referenced. The cell can be 1, 1 row or 1 column. In the current requirements, name and ID are in one-to-one correspondence, so we only need to fill in a cell reference here. The configuration is as shown in the figure below:
- indirect formula
This formula can be used to reference the configuration of the name manager. The corresponding value can be obtained through=indirect(name). For example, in Sheet1, Zhang San’s id can be obtained through this formula, as shown in the figure below :
- Data Validity
The data is used to verify whether the data in the current cell meets the requirements. When the requirements are not met, certain prompts can be given. In addition, there are some additional functions, such as: Used to make a drop-down list, So we can consider directly making the cell a drop-down list and selecting the name through the drop-down list.
After the operation is completed, the cells in column A can be drop-down selected.
In the same way, the ID list page can be made into a drop-down and placed in column B.
After the operation is completed, the cells in column A can be drop-down selected.
However, in this way of implementation, the name and ID are selected separately. Although there will be no mistakes due to manual input, there will still be a problem of mismatched mapping relationships. Let’s look down and see that this problem can be solved through drop-down linkage.
2.2. Drop-down box linkage
With the above foundation, it is relatively simple to realize the linkage of the drop-down box. We only need to combine the three features mentioned above and modify the validity in column B, as shown below:
After this configuration, when selecting Id in column B, only the Id corresponding to the currently selected name will appear, as shown in the figure:
2.3. Cells automatically match ID
The linkage of the drop-down selection boxes has been implemented above, but this method still requires manual selection one by one. Is there a way to automatically fill in the ID in column B when the name in column A is selected?
Students who are familiar with Excel formulas should know how to do it. In fact, we only need to write the reference formula of the name manager again on the cell:
After writing this, when data is selected in the cell of column A, column B can automatically fill in the ID. However, if no data is selected in column A, then a #REF! error will appear in column B. We can modify the formula to deal with this error: =IFERROR(INDIRECT($A1),"") or =IF(ISERROR(INDIRECT($A1)),"",INDIRECT ($A1)), these two formulas are equivalent, they will determine whether the reference is normal, and if it is not normal, fill in the empty string.
After modification, there will be no error:
2.4. Error message
After the validity configuration is completed, you can configure a custom error prompt, which will pop up after other information is entered in the cell. The configuration location is still in the validity area. Take column A as an example:
3. Code implementation
Next, the basic process code will be provided first, and then implemented in the order of name manager, drop-down list configuration (including data verification), and formula filling.
Since POI has been introduced into the package of EasyExcel, we only need to introduce the jar package of EasyExcel here. The 3.1.0 version is used.
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
3.1. Basic process code
In order to facilitate subsequent implementation, some basic export code will be written here. Students who have not used EasyExcel can take a look. If students are already familiar with EasyExcel, they can take a look directly below. of3.2.
First, an export object is provided for downloading the import template. Here, there are only two fields: name and id:
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.Getter;
import lombok.Setter;
/**
* Salesperson Excel import template object
*/
@Getter
@Setter
public class MemberExcelTemplateModel {<!-- -->
@ExcelProperty("salesperson name")
private String name;
@ExcelProperty("salesperson id")
private WriteCellData<String> memberIdFormula;
}
The id field here uses WriteCellData instead of Long, String and other fields, mainly for subsequent filling of formulas, which will be discussed in detail below.
Then write a processor, use the above template to generate Excel, and write the generated Excel file into HttpServletResponse:
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* Excel template download processor
*/
@Slf4j
@Component
public class ExcelTemplateDownloadHandler {<!-- -->
public void buildExcelTmpl(HttpServletResponse response) {<!-- -->
List<MemberExcelTemplateModel> list = new ArrayList<>();
try {<!-- -->
EasyExcelFactory.write(disposeExportSetting(response).getOutputStream(), MemberExcelTemplateModel.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("Sales target import template")
.doWrite(list);
} catch (IOException e) {<!-- -->
log.error("Export of overall analysis of clue statistics failed", e);
}
}
/**
* Set the response header, type, encoding, etc. for exporting Excel
*/
private HttpServletResponse disposeExportSetting(HttpServletResponse response) throws UnsupportedEncodingException {<!-- -->
response.setContentType("application/x-xls");
response.setCharacterEncoding("utf-8");
String name = URLEncoder.encode("template", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
return response;
}
}
Finally, a controller is provided to initiate Http requests and download and import templates:
@RestController
@RequestMapping("/excel")
public class ExcelController {<!-- -->
@Resource
private ExcelTemplateDownloadHandler excelTemplateDownloadHandler;
/**
* Export excel template
*/
@PostMapping("/getExcelTmpl")
public void getExcelTmpl(HttpServletResponse response) {<!-- -->
excelTemplateDownloadHandler.buildExcelTmpl(response);
}
}
A simple download process has been written. Download an Excel file through the debugging tool. The effect is as follows:
3.2. Name manager configuration
After you have a basic template, go to the second step and create a new sheet to save the salesperson information and create a name manager.
First, we need to find out the salesperson information in the database and provide a Member object to receive:
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import java.util.Arrays;
import java.util.List;
/**
* salesperson
*/
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Member {<!-- -->
/**
* Salesperson ID
*/
private String id;
/**
* Salesperson’s name
*/
private String name;
/**
* Simulate obtaining the salesperson list from the database
*/
public static List<Member> getMemberList() {<!-- -->
return Arrays.asList(
new Member("1", "张三"),
new Member("2", "李思"),
new Member("3", "王五"),
new Member("4", "Zhao Liu"),
new Member("5", "天七")
);
}
}
Next, you need to use an extension point of EasyExcel: SheetWriteHandler
We need to do further operations after the Sales Target Import Template this sheet is created. All we need to do is use the afterSheetCreate method. Let’s talk about two The function of formal parameters:
WriteWorkbookHolder: Get theExcelobject of the current operationWriteSheetHolder: Get thesheetobject of the current operation, which refers to theSales Target Import Template
Write a custom handler that inherits SheetWriteHandler:
/**
* Custom drop-down list processor
*/
public class MySheetWriteHandler implements SheetWriteHandler {<!-- -->
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {<!-- -->
Workbook workbook = writeWorkbookHolder.getWorkbook();
//Create a sheet and save the drop-down data source. The main ones here are the salesperson's name and salesperson ID.
String sheetName = "dataSource";
Sheet workbookSheet = workbook.createSheet(sheetName);
List<Member> memberList = Member.getMemberList();
for (int i = 0; i < memberList.size(); i + + ) {<!-- -->
Member member = memberList.get(i);
//Write salesperson data, row represents the starting row number, cell represents the starting column number
Row row = workbookSheet.createRow(i);
row.createCell(0).setCellValue(member.getName());
row.createCell(1).setCellValue(member.getId());
//Create name manager
Name workbookName = workbook.createName();
//Add an underscore to avoid names starting with numbers like 000001
workbookName.setNameName("_" + member.getName());
workbookName.setRefersToFormula(sheetName + "!$B$" + (i + 1));
}
}
}
One difference between this and the Excel demonstration above is that the name processor starts with underscore. This is a pitfall that I stepped into. Names that start with numbers This will cause an error to be reported when creating the name processor. After using the underscore, modify the function INDIRECT("_" & amp;$A1) synchronously and add an underscore.
After the processor is written, you need to register the export location:
After registering and exporting again, you will find that the salesperson data source and name manager have been correctly written:
3.3. Validity configuration
The next step is to set the name selection to a drop-down selection in Sales Target Import, which is the validity configuration:
public class MySheetWriteHandler implements SheetWriteHandler {<!-- -->
/**
* Set the starting row of the drop-down box, the default is the second row
*/
private static final int FIRST_ROW = 1;
/**
* Set the drop-down box to end the row
*/
private static final int LAST_ROW = 10000;
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {<!-- -->
/// Omit name manager code...
// Validity handling helper object
DataValidationHelper validationHelper = writeSheetHolder.getSheet().getDataValidationHelper();
// Salesperson name drop-down data source matching
CellRangeAddressList nameRange = new CellRangeAddressList(FIRST_ROW, LAST_ROW, 0, 0);
DataValidationConstraint nameConstraint = validationHelper.createFormulaListConstraint(sheetName + "!$A$1:$A$" + (memberList.size() + 1)); // The first column of the data source
DataValidation nameValidation = validationHelper.createValidation(nameConstraint, nameRange);
nameValidation.setShowErrorBox(true);
nameValidation.createErrorBox("Error", "Please choose the correct name");
writeSheetHolder.getSheet().addValidationData(nameValidation);
// Salesperson ID drop-down linkage
CellRangeAddressList idRange = new CellRangeAddressList(FIRST_ROW, LAST_ROW, 1, 1);
DataValidationConstraint idConstraint = validationHelper.createFormulaListConstraint("=INDIRECT("_" & amp;$A2)"); // Add underline to function
DataValidation idValidation = validationHelper.createValidation(idConstraint, idRange);
idValidation.setShowErrorBox(true);
idValidation.createErrorBox("Error", "Please select the correct id");
writeSheetHolder.getSheet().addValidationData(idValidation);
}
}
Check the effect after downloading:
3.4. Function filling
Finally, the only thing left is to fill in the formula in the cell of the salesperson ID. Since the data of Sales Target Import Template has been written through EasyExcel, POI cannot be repeated here. Writing, so the formula filling needs to be placed in the writing of EasyExcel. This is why the sales id field in the MemberExcelTemplateModel provided above is WriteCellData just to fill in the formula.
Before downloading the import template, process the data that needs to be exported:
public void buildExcelTmpl(HttpServletResponse response) {<!-- -->
List<MemberExcelTemplateModel> list = new ArrayList<>();
//Fill 10,000 rows of formulas by default
for (int i = 0; i < 10000; i + + ) {<!-- -->
//define function
FormulaData formulaData = new FormulaData();
formulaData.setFormulaValue("IFERROR(INDIRECT("_" & amp;$A" + (i + 2) + "),"")");
//Set the function object into the template object
WriteCellData<String> formula = new WriteCellData<>();
formula.setFormulaData(formulaData);
MemberExcelTemplateModel memberExcelTemplateModel = new MemberExcelTemplateModel();
memberExcelTemplateModel.setMemberIdFormula(formula);
list.add(memberExcelTemplateModel);
}
try {<!-- -->
EasyExcelFactory.write(disposeExportSetting(response).getOutputStream(), MemberExcelTemplateModel.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("Sales target import template")
//Register custom processor
.registerWriteHandler(new MySheetWriteHandler())
.doWrite(list);
} catch (IOException e) {<!-- -->
log.error("Export of overall analysis of clue statistics failed", e);
}
}
Check the export results and see that the salesperson ID column has been filled in with the function normally.
3.5. Other supplements
In the above example, there are only two fields: name and id. In actual development, there may be restrictions such as year, month, sales group, amount, etc. You can refer to the above example for expansion.
4. Summary
This article mainly discusses how to create an Excel template with drop-down, drop-down linkage, data verification, and automatic filling functions.
Starting from Excel’s own features name manager, validity, formula, the principle of function implementation is explained, and a template is manually configured. Then through the combination of EasyExcel and POI, the template is generated and downloaded using code.
I hope this article can be helpful to everyone’s development! Like and collect! Your support is my biggest motivation for updating!