Recently, I have faced a new requirement, which is to export the data queried by users to an Excel file. In order to meet this demand, I decided to use the EasyPoi library to implement this function, and support setting the number of rows of each Excel file and generating a compressed package. Below, I will share with you the specific implementation method.
Since I am using the SpringBoot framework, I will use SpringBoot as an example to explain here. For other frameworks, there are no other differences except the way dependencies are introduced.
1. Template method
1. First we introduce the dependency of easypoi in the pom file, here we introduce its starter
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.3.0</version>
</dependency>
2. Create an excel.xls template file. Here I place it in the resources directory of the project. The template format is as follows
Table colors, cell styles, etc. can be customized, mainly in data processing. The content in {{}} in the above picture can accept passed values. Of course, you can also place it in other locations, such as at the end of your task. excelTitle is just the field name you want to pass in. That’s it, you can take it as you like. Note that the data row must be wrapped in double brackets {{}}. userList is the object name you want to pass in. t is the abbreviation for userList.
The code is implemented as follows. Here we calculate how many excel files need to be generated based on the number of data items and the number of items in each excel file, and then submit each file to a thread. After all threads have finished executing, do subsequent processing
1. Controller layer code
/**
* Export user list
* @param response
* @param exportSize number of export items
* @param partSize number of single files
*/
@PostMapping("exportUserInfoList")
public void exportUserInfoList(HttpServletResponse response,
HttpServletRequest request,
@RequestParam("exportSize") int exportSize,
@RequestParam("partSize") int partSize) throws Exception{<!-- -->
userService.exportUserInfoList(exportSize,partSize,request,response);
}
2. The service layer code fragments the data. Here I use the ListUtil.partition method in the hutool tool class for fragmentation. Of course, you can implement fragmentation by yourself. We create a ThreadPoolExecutor thread pool to submit threads, use the CountDownLatch program counter to count, pass in the number of threads, which is the number of files we will generate, and then call the await() method to wait for all threads to finish executing. Finally close the thread.
I used the method in the Faker tool to create the data here. You can modify it yourself.
Note: The userList in the params object corresponds to the userList in the template above, and excelTitle is the title. Other parameters can also be passed here. Based on this feature, the requirements for dynamic table headers and dynamic column names can be realized< /em>
private static final int CORE_POOL_SIZE = 10;
private static final int MAX_POOL_SIZE = 20;
/**
* Temporary file storage path
*/
private static final String TEMP_FILE_PATH = "/home/workspace/tempFile/";
/**
* Export user list
*
* @param exportSize number of export items
* @param partSize number of single files
*/
public void exportUserInfoList(int exportSize, int partSize, HttpServletRequest request, HttpServletResponse response) throws Exception {<!-- -->
List<Map<String, Object>> userList = new ArrayList<>();
Faker faker = new Faker(Locale.CHINA);
for (int i = 1; i <= exportSize; i + + ) {<!-- -->
Map<String, Object> userInfo = new HashMap<String, Object>() {<!-- -->{<!-- -->
put("userId", faker.code().asin());
put("userName", faker.name().username());
put("age", 18);
put("addr", faker.address().fullAddress());
put("tel", faker.phoneNumber().cellPhone());
}};
userList.add(userInfo);
}
//data fragmentation
List<List<Map<String, Object>>> partition = ListUtil.partition(userList, partSize);
//Template address
String templateUrl = "excelTemplate/UserList.xls";
String tempFolder = TEMP_FILE_PATH + IdUtil.simpleUUID();
if (!FileUtil.exist(tempFolder)) {<!-- -->
FileUtil.mkdir(tempFolder);
}
int taskSize = partition.size();
CountDownLatch doneSignal = new CountDownLatch(taskSize);
ThreadPoolExecutor executor = new ThreadPoolExecutor(CORE_POOL_SIZE, MAX_POOL_SIZE, 60000, TimeUnit.MILLISECONDS, new LinkedBlockingDeque<>(10));
for (int i = 0; i < taskSize; i + + ) {<!-- -->
int curPage = i + 1;
int finalI = i;
Thread thread = new Thread(new Runnable() {<!-- -->
@Override
public void run() {<!-- -->
String excelName = tempFolder + File.separator + "User List-" + curPage + ".xls";
try (OutputStream outputStream = new FileOutputStream(excelName)) {<!-- -->
File file = new File(excelName);
if (!file.exists()) {<!-- -->
file.createNewFile();
}
Map<String, Object> params = new HashMap<>();
// Get the data of each slice
params.put("userList", partition.get(finalI));
params.put("excelTitle", "User List");
TemplateExportParams templateExportParams = new TemplateExportParams(templateUrl);
Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams, params);
workbook.write(outputStream);
outputStream.flush();
} catch (IOException e) {<!-- -->
log.error(e.getMessage());
e.printStackTrace();
} finally {<!-- -->
doneSignal.countDown();
}
}
});
executor.execute(thread);
System.out.println("Submit thread---" + i);
}
doneSignal.await();
executor.shutdown();
InputStream ins = null;
try {<!-- -->
String zipFilePath = tempFolder + File.separator + "UserList.zip";
zipFolder(tempFolder, zipFilePath);
handleResponseHeader("UserList.zip", request, response);
ins = new FileInputStream(zipFilePath);
IOUtils.copy(ins, response.getOutputStream());
} catch (Exception e) {<!-- -->
e.printStackTrace();
} finally {<!-- -->
IOUtils.closeQuietly(ins);
}
}
3. The following is the method I use to generate compressed files. I encapsulated it into a tool class so that you can use it directly
/**
* Compressed file
* @param sourceFolderPath The folder to be compressed. Example: D:temp/user
* @param zipFilePath compressed package path D:temp/user1.zip
*/
public static void zipFolder(String sourceFolderPath, String zipFilePath) {<!-- -->
try {<!-- -->
FileOutputStream fos = new FileOutputStream(zipFilePath);
ZipOutputStream zos = new ZipOutputStream(fos);
zipDirectory(sourceFolderPath, sourceFolderPath, zos);
zos.close();
fos.close();
} catch (IOException e) {<!-- -->
e.printStackTrace();
}
}
public static void zipDirectory(String rootPath, String sourceFolderPath, ZipOutputStream zos) throws IOException {<!-- -->
//Create a File object.
File folder = new File(sourceFolderPath);
// List all files and subfolders in a folder.
for (String fileName : folder.list()) {<!-- -->
// If the file name ends with .zip, that is, it is a compressed file.
// Then skip this file and do not compress it.
if (fileName.endsWith(".zip")) {<!-- -->
continue;
}
// Full path to create file.
String filePath = sourceFolderPath + File.separator + fileName;
// If the file corresponding to the path is a directory, then call this method recursively,
// Continue to compress files in this directory.
if (new File(filePath).isDirectory()) {<!-- -->
zipDirectory(rootPath, filePath, zos);
continue;
}
String relativePath = filePath.substring(rootPath.length() + 1);
ZipEntry ze = new ZipEntry(relativePath);
// Add this ZipEntry object to the ZipOutputStream, which actually adds a new entry to the zip file.
zos.putNextEntry(ze);
//Create a FileInputStream object to read the file.
FileInputStream fis = new FileInputStream(filePath);
byte[] buffer = new byte[1024];
// Define a variable to store the number of bytes read from the file.
int len;
// Loop to read the data of the file and write the data to the zip file.
while ((len = fis.read(buffer)) > 0) {<!-- -->
zos.write(buffer, 0, len);
}
fis.close();
// Close ZipEntry, which actually indicates the end of an entry in the zip file.
zos.closeEntry();
}
}
4. Here is a method for handling download responses. The author found that sometimes the file names downloaded in Firefox browser were garbled, so I optimized them for Firefox browser
/**
* Set response headers
*
* @param fileName file name
* @param request
* @param response
*/
public static void handleResponseHeader(String fileName, HttpServletRequest request, HttpServletResponse response) {<!-- -->
// file name
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
String header = request.getHeader("User-Agent");
try {<!-- -->
if (header.contains("Firefox")) {<!-- -->
//Solve the problem of garbled file names when downloading in Firefox browser
response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName, "UTF-8"));
} else {<!-- -->
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
}
} catch (UnsupportedEncodingException e) {<!-- -->
log.error(e.getMessage(), e);
e.printStackTrace();
}
}
2. Annotation method
1. First prepare an entity class and add Excel annotations. Name is your column name, orderNum is the order of the columns, and width is the width of the column.
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("tableListExcel")
public class TableExcel implements Serializable {<!-- -->
/**
* name
*/
@Excel(name = "name",orderNum = "1",width = 4)
private String name;
/**
* type
*/
@Excel(name = "age",orderNum = "2",width = 3)
private String age;
/**
* can be empty
*/
@Excel(name = "Address",orderNum = "3",width = 2)
private String addr;
/**
* Note
*/
@Excel(name = "Phone number",orderNum = "4",width = 5)
private String tel;
}
controller layer
@GetMapping("exportSysTableInfo")
public void exportSysTableInfo(HttpServletRequest request,HttpServletResponse response) {<!-- -->
sysConfigService.exportExcel(response);
}
service layer
public void exportExcel(HttpServletResponse response) {<!-- -->
// Here is the collection of sheetse
List<Map<String, Object>> sheetsList = new ArrayList<>();
List<TableExcel> excels1 = new ArrayList<>();
List<TableExcel> excels2 = new ArrayList<>();
List<TableExcel> excels3 = new ArrayList<>();
// Build your test data
TableExcel tableExcel1 = new TableExcel("Zhang San", "23", "Fengxian District, Shanghai", "15888888888");
excels1.add(tableExcel1);
TableExcel tableExcel2 = new TableExcel("李思", "32", "Putuo District, Shanghai", "0394-451212");
excels2.add(tableExcel2);
TableExcel tableExcel3 = new TableExcel("王五", "47", "Jing'an District, Shanghai", "65123222222");
excels3.add(tableExcel3);
//Create parameter object
ExportParams exportParams = new ExportParams();
//Set the name of the sheet
exportParams.setSheetName("001");
//Create the map used by the sheet
Map<String, Object> tableDataMap = new HashMap<>(4);
tableDataMap.put("title", exportParams);
tableDataMap.put("entity", TableExcel.class);
tableDataMap.put("data", excels1);
sheetsList.add(tableDataMap);
//Create parameter object
ExportParams exportParams2 = new ExportParams();
//Set the name of sheet2
exportParams2.setSheetName("002");
//Create the map used by sheet2
Map<String, Object> tableDataMap2 = new HashMap<>(4);
tableDataMap2.put("title", exportParams2);
tableDataMap2.put("entity", TableExcel.class);
tableDataMap2.put("data", excels2);
sheetsList.add(tableDataMap2);
//Create parameter object
ExportParams exportParams3 = new ExportParams();
//Set the name of sheet3
exportParams3.setSheetName("003");
//Create the map used by sheet3
Map<String, Object> tableDataMap3 = new HashMap<>(4);
tableDataMap3.put("title", exportParams3);
tableDataMap3.put("entity", TableExcel.class);
tableDataMap3.put("data", excels3);
sheetsList.add(tableDataMap3);
Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
ExcelUtil.downLoadExcel("easyPOI export table test", response, workbook);
}
The effect of generating Excel is as follows: 001, 002, 003 are customized multiple sheet pages
ExcelUtil tool class
/**
* Excel type enumeration
*/
enum ExcelTypeEnum {<!-- -->
XLS("xls"), XLSX("xlsx");
private String value;
ExcelTypeEnum(String value) {<!-- -->
this.value = value;
}
public String getValue() {<!-- -->
return value;
}
public void setValue(String value) {<!-- -->
this.value = value;
}
}
/**
* download file
* @param fileName file name
* @param response
* @param workbook excel data
*/
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook){<!-- -->
try {<!-- -->
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLS.getValue(), "UTF-8")) ;
workbook.write(response.getOutputStream());
} catch (IOException e) {<!-- -->
e.printStackTrace();
}
}
Well, the above is the solution on how to use the EasyPoi library and multi-threading technology to efficiently export Excel files. I hope this blog can be helpful to you, and I welcome any suggestions and questions you have, let us make progress together!