Write in front
It's only for record. It's a great honor to help a little partner who is still lost.
In the past two or three days, I have done a good job in exporting and downloading excel tables using easyexcel. I also feel a lot. In the humanitarian spirit of planting trees by predecessors and enjoying the cool by future generations, I quickly code this article to deepen my memory.
text
1. Introduction and use of easyexcel in springboot backend
1.1 introducing dependencies
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> </dependency>
1.2 interface serviceImpl method
xxxServiceImpl.java
public void exportExcelData(String wo_id, HttpServletResponse response) throws BusinessException {
// Note that some students have reported that using swagger will lead to various problems. Please use the browser or postman directly
try {
// template file
//Here, getResourceAsStream is used to obtain the Excel template file stream packaged by the server;
//If the getPath method is used to obtain the file address, the local ieda environment can obtain it, and it will become invalid after uploading to the server. All adopted streams can take effect. The specific reasons have not been carefully checked yet. Interested children's shoes can try by themselves!
//InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/excelTemplate.xls");
// Get header data
if (wo_id == null || wo_id.isEmpty()) {
throw new BusinessException(BusinessCodeEnum.PARAMETER_ERROR, "Work order id Get empty!");
}
// The following two lines are my own business. Don't worry
CkdPoInfo ckdPoInfo = ckdPoInfoMapper.selectByPrimaryKey(wo_id);
String work_order = ckdPoInfo.getWork_order();
List<ExportExcelData> excelDataList = getData(wo_id); // Get excel table body data from database
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//Set header Center
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//Content strategy
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//Set horizontal center
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); //Because the merge policy is customized, the default merge policy here is not used
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// Here is urlencoder Encode can prevent Chinese garbled code. Of course, it has nothing to do with easyexcel
String fileName = URLEncoder.encode(work_order+"Report export test", "UTF-8").replaceAll("\\+", "%20");
// response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
response.addHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
response.addHeader("Access-Control-Expose-Headers", "Content-disposition");
// Custom merge strategy, important
Map<String, List<RowRangeDto>> srategyMap = ExcelUtil.addMergeStrategy(excelDataList);
// Here, you need to set not to close the flow;
EasyExcel.write(response.getOutputStream(), ExportExcelData.class).autoCloseStream(Boolean.FALSE)
// Register merge policy
.registerWriteHandler(new BizMergeStrategy(srategyMap)) // The custom merge policy is called here
.registerWriteHandler(ExcelUtil.CellStyleStrategy()) // Call a custom tool class
.sheet("xxx Information table")
.doWrite(excelDataList); // Write the acquired list data set
} catch (Exception e) {
// Reset response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
// Map<String, String> map = MapUtils.newHashMap();
// map.put("status", "failure");
// map.put("message", "file download failed" + e.getMessage());
// try {
// response.getWriter().println(JSON.toJSONString(map));
// } catch (IOException ex) {
// ex.printStackTrace();
// }
e.printStackTrace();
throw new BusinessException(BusinessCodeEnum.PARAMETER_ERROR, "export excel Failed!");
}
}
- Above try_catch statement calls easyexcel to write the file stream. I wrote it in the class method of serviceImpl, or I can write this paragraph directly in the controller. All individual business layer codes are placed in the service, and the controller layer is only responsible for calling the service layer.
- wo_id is the parameter I need to transfer. It can be transferred or not according to my actual situation.
- Excel datalist is the list set encapsulated by me to obtain my database data, and getData() is the specific implementation method;
- The implementation of getData() is different in different scenarios, so we won't post its implementation here
- Custom merge policies and custom ExcelUtil tool classes will be placed below. It's not urgent.
1.3 provide a method to de duplicate the list set (remove duplicates and merge value values according to the same key)
public List<CkdMaterialPackage> getNewList(List<CkdMaterialPackage> oldList) {
List<CkdMaterialPackage> newList = new ArrayList<>();
HashMap<NewCkdMtrPackage, CkdMaterialPackage> tempMap = new HashMap<NewCkdMtrPackage, CkdMaterialPackage>();
// Remove duplicate key s
for (CkdMaterialPackage ckdMaterialPackage : oldList) {
String odm_pn = ckdMaterialPackage.getOdm_pn(); // The item number acts as the key name
String exporter_pn = ckdMaterialPackage.getExporter_pn();
String importer_pn = ckdMaterialPackage.getImporter_pn();
NewCkdMtrPackage newCkdMtrPackage = new NewCkdMtrPackage(); // As the key value of map
// Assign a value to the attribute, and the class object composed of the following three parts is used as the key
newCkdMtrPackage.setOdm_pn(odm_pn);
newCkdMtrPackage.setExporter_pn(exporter_pn);
newCkdMtrPackage.setImporter_pn(importer_pn);
//
if (tempMap.containsKey(newCkdMtrPackage)) {
// Merge value s of the same item No
ckdMaterialPackage.setQuantity(tempMap.get(newCkdMtrPackage).getQuantity() + ckdMaterialPackage.getQuantity());
// hashmap does not allow duplicate keys. When there are duplicate keys, the value corresponding to the previous key will be overwritten
tempMap.put(newCkdMtrPackage, ckdMaterialPackage);
} else {
tempMap.put(newCkdMtrPackage, ckdMaterialPackage);
}
}
for (Map.Entry<NewCkdMtrPackage, CkdMaterialPackage> entry : tempMap.entrySet()) {
newList.add(entry.getValue());
}
return newList;
}
- This method is to pass in an original data list, and then return a list after de coincidence
- List < CkdMaterialPackage > the CkdMaterialPackage in the oldlist is my own database table entity class. Replace it with your own
- NewCkdMtrPackage is a specially defined entity class. It can be understood that the attributes inside act as keys as a whole; For example, a list set contains several elements of name,sex,height and score. I want to merge the duplicate name,sex and height and sum the score. That means I have more than one column merged. Then encapsulate these duplicates into a class and execute them as a key as a whole.
1.4 BizMergeStrategy merge policy class
public class BizMergeStrategy extends AbstractMergeStrategy {
private Map<String, List<RowRangeDto>> strategyMap; // RowRangeDto row start end range class
private Sheet sheet;
public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
this.strategyMap = strategyMap;
}
@Override
protected void merge(org.apache.poi.ss.usermodel.Sheet sheet, Cell cell, Head head, Integer integer) {
this.sheet = sheet;
if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
/**
* Ensure that each cell is merged once. If the above judgment is not added, because it is operated by one cell,
* For example, merge A2:A3. When the cell is A2, merge A2 and A3, but when the cell is A3, merge A2 and A3,
* At this time, A2 and A3 are merged cells
*/
for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
Integer columnIndex = Integer.valueOf(entry.getKey());
entry.getValue().forEach(rowRange -> {
//Add a merge request
sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
rowRange.getEnd(), columnIndex, columnIndex));
});
}
}
}
}
- RowRangeDto
/**
* dto class for starting and ending positions of line segments
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class RowRangeDto {
private int start;
private int end;
}
1.5 custom ExcelUtil tool class
public class ExcelUtil{
public static Map<String, List<RowRangeDto>> addMergeStrategy(List<ExportExcelData> excelDataList) {
Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
ExportExcelData preExcelData = null;
for (int i = 0; i < excelDataList.size(); i++) {
ExportExcelData currExcelData = excelDataList.get(i);
if (preExcelData != null) {
//Judge whether to merge from the second line
if (currExcelData.getPallet_number().equals(preExcelData.getPallet_number())) {
//If the pallet number is the same, the pallet number, pallet gross weight and pallet size can be combined
fillStrategyMap(strategyMap, "0", i);
fillStrategyMap(strategyMap, "1", i);
fillStrategyMap(strategyMap, "2", i);
//If the pallet number is the same and the cartoon box number is the same, three columns of cartoon box number, single box gross weight and single box size can be combined
if (currExcelData.getCarton_number().equals(preExcelData.getCarton_number())) {
fillStrategyMap(strategyMap, "3", i);
fillStrategyMap(strategyMap, "4", i);
fillStrategyMap(strategyMap, "5", i);
// //If the pallet number and cartoon box number are the same, and the material item number is the same, the material information in the column of material item number can be consolidated, which has been consolidated during query
// if (currExcelData.getCoopOrg().equals(preExcelDto.getCoopOrg())) {
// fillStrategyMap(strategyMap, "2", i);
// }
}
}
}
preExcelData = currExcelData;
}
return strategyMap;
}
private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index) {
List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
boolean flag = false;
for (RowRangeDto dto : rowRangeDtoList) {
//Whether the end index in the segmented list is the index of the previous row. If so, the index + 1
if (dto.getEnd() == index) {
dto.setEnd(index + 1);
flag = true;
}
}
//If not, add a new segment
if (!flag) {
rowRangeDtoList.add(new RowRangeDto(index, index + 1));
}
strategyMap.put(key, rowRangeDtoList);
}
public static HorizontalCellStyleStrategy CellStyleStrategy(){
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//Set background color
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//Set header font
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//Set header Center
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//Content strategy
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//Set horizontal center
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}
Controller method:
/**
* Export and download excel report
* @param wo_id
* @param response
* @throws BusinessException
*/
@GetMapping("/downloadExcel")
public void exportExcelData(
@RequestParam(value = "wo_id") String wo_id,
HttpServletResponse response
) throws BusinessException {
// Get report data
ckdPoInfoService.exportExcelData(wo_id, response);
}
The above are all back-end interfaces and methods.
2. The Vue front end calls the background download excel interface to complete the download by clicking the button
2.1 vue code corresponding to the figure above
<span v-if="(scope.row.status&63)===60">
<el-popconfirm // Bubble box
style="margin-left:20px;"
@confirm="export_excel(scope.row.wo_id)"
title="Export customs declaration?"
>
<el-button type="text" slot="reference" size="small" icon="el-icon-document-copy">Export customs declaration</el-button>
</el-popconfirm>
</span>
2.2 export_excel() method
export_excel(wo_id){
//scope.row.wo_id
console.log("wo_id = "+wo_id);
const url = this.BASE_API_URL + 'springbootApi/downloadExcel?wo_id='+wo_id;
axios.get(url,
{
responseType: 'blob' <!--The response type must be set to binary stream-->
})
.then((res) => {
if (!res) return
console.log("res data = "+res.data);
let blob = new Blob([res.data], {type: 'application/vnd.ms-excel;charset=utf-8'}) // file type
console.log(res.headers['content-disposition']); // Get the filename from the headers of the response, and the back-end response The file name set by setheader ("content disposition", "attachment; filename =" + filename + ". Xlsx");
//Take the element of array [1] as the file name with = split
let filename = window.decodeURI(res.headers['content-disposition'].split('=')[1])
let url = window.URL.createObjectURL(blob); // Create download link
let aLink = document.createElement("a"); // href attribute assigned to a tag
aLink.style.display = "none";
aLink.href = url;
aLink.setAttribute("download", filename);
document.body.appendChild(aLink); // Attach the a label
aLink.click(); // a tag click event
document.body.removeChild(aLink); // Remove a tag
window.URL.revokeObjectURL(url); // Destroy download link
return this.$message.success("Export of customs declaration succeeded");
}).catch(function (error) {
console.log(error);
})
}
3. vue calls the background interface to download excel in various ways (this section draws lessons from others' summary)
Method 1: directly through a label
<a href="/images/logo.jpg" download="logo" />
Advantages: simple and convenient.
Disadvantages: This download method only supports Firefox and Chrome, does not support IE and Safari, and the compatibility is not good enough.
Method 2: through window location
window.location = 'http://127.0.0.1:8080/api/download?name=xxx&type=xxx'
In fact, it is similar. I directly enter the interface address in the browser url address bar and press enter to download.
Advantages: simple and convenient.
Disadvantages: only get requests can be made, which is inconvenient when there is token verification.
Method 3: axios requests the background interface (directory 2 is based on this)
4. Summarize some problems encountered to avoid small partners stepping on the pit
① First, after the back-end interface is written, I directly enter the api interface into the browser to call the test:
http://localhost:8989/xxx/downloadExcel?wo_id=33b948460598420eb533d62930c9
Results the downloaded file and save box pops up to prove that the back-end interface is available; When testing, you can write and test the front and back ends separately.
② I define that the download logic uses easyexcel, but the previous logic of uploading files used Excel kit, and then the poi version is 3.1. However, my easyexcel contains version 4.0 poi, so some methods of uploading file logic report errors.
Solution: POM You can manually add a higher version of the conflicting jar package to the XML dependency.
③ The back-end has set the name of the downloaded excel table defined in the header, but the vue front-end does not get:
// Here is urlencoder Encode can prevent Chinese garbled code. Of course, it has nothing to do with easyexcel
String fileName = URLEncoder.encode(work_order+"Report export test", "UTF-8").replaceAll("\\+", "%20");
// response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
response.addHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//response.addHeader("Access-Control-Expose-Headers", "Content-disposition"); Not yet
console.log(res.headers['content-disposition']); // The console output here is undefined
After checking the data, it is found that content disposition must be added to access control expose headers when setting the request header in the background, and the front end can get it.
That is, add a code in the background:
response.addHeader("Access-Control-Expose-Headers", "Content-disposition");
Then vue can get the file name.
Reference article:
1. Vue project uses axios request interface to download excel (with front-end code)
2. Downloading binary stream files with axios in vue
3. Brother wrote great
4. The boss set the template filling. You can have a look. You can also download compressed packages and leave a portal
5. This helps me the most
6. github source code easyexcel address
7. Use easy excel to export excel with complex header
8. What feels useful is here
Split line====