Export files: Use Hutool to export data as Excel files

background

In daily work, I have met the need to export data to Excel. Here is a brief summary.

Exporting Excel is more common than exporting PDF or Word files.

In practice, in some projects, front-end plug-ins are used to export Excel. When the data volume is large, the client has high requirements and the export is slow, which affects user experience. The other is the back-end direct query, encapsulation, and export as Excel files introduced here today.

The technologies involved include SpringBoot, MyBatis, and Hutool, which is used to export data as Excel.

Rely on

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.5.6</version>
</dependency>

<! --Export as Excel-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>
Copy the code

Core export interface

/ * * *@Author Heartsuit
 * @DateThe 2021-08-09 * /
@RestController
@RequestMapping("employee")
public class EmployeeController {
    private final EmployeeService employeeService;

    public EmployeeController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    /** * export full data@param response
     * @throws IOException
     */
    @GetMapping("export-xls")
    public void exportExcel(HttpServletResponse response) throws IOException, ClassNotFoundException {
        ExcelWriter writer = ExcelUtil.getWriter();
        List<Employee> employees = employeeService.findAll();

        List<Map<String, Object>> rows = employees.stream().map(item -> {
            Map<String, Object> maps = new HashMap<>();
            maps.put("id", item.getId().toString());
            maps.put("name", item.getName());
            maps.put("age", item.getAge());
            maps.put("phone", item.getPhone());
            maps.put("createTime", item.getCreateTime().toString());
            return maps;
        }).collect(Collectors.toList());

        // Title
        int columns = Class.forName("com.heartsuit.springbootmybatis.oa.entity.Employee").getDeclaredFields().length;
        writer.merge(columns - 1."Employee Information");

        // Header
        writer.addHeaderAlias("id"."ID");
        writer.addHeaderAlias("name"."Name");
        writer.addHeaderAlias("age"."Age");
        writer.addHeaderAlias("phone"."Telephone");
        writer.addHeaderAlias("createTime"."Time");

        // Body
        writer.setColumnWidth(0.30);
        writer.setColumnWidth(1.30);
        writer.setColumnWidth(2.30);
        writer.setColumnWidth(3.30);
        writer.setColumnWidth(4.30);
        writer.write(rows, true);

        response.setContentType("application/vnd.ms-excel; charset=utf-8");
        response.setHeader("Content-disposition"."attachment; filename=" + URLEncoder.encode("Staff Information Sheet -" + DateUtil.today() + ".xls"."utf-8"));

        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true); writer.close(); IoUtil.close(out); }}Copy the code

Test interface: Full export

GET http://localhost:8000/employee/export-xls

Note: GET is used here to facilitate testing. POST is actually recommended.

Example Test the efficiency of exporting 10,000 pieces of data

• Batch insert tens of thousands of data into the data table to test the export efficiency again;
• In fact, the export time depends on the efficiency of the search and the total amount of data detected (involving the time of writing Excel and transferring Excel);

Bulk write data interface:

@Test
void insertBatch(a) {
    SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();

    // sqlSession can execute batch operation, try... with...
    try (SqlSession openSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
        long start = System.currentTimeMillis();
        EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
        for (int i = 0; i < 10000; i++) {
            Employee employee = new Employee();
            employee.setName(UUID.randomUUID().toString().substring(0.6));
            employee.setAge(new Random().nextInt(100));
            employee.setPhone(MobileNumber.generate(0));
            mapper.save(employee);
        }
        openSession.commit();
        long end = System.currentTimeMillis();
        System.out.println("Execution duration"+ (end - start)); }}Copy the code

Export 10,000 pieces of data in seconds from tens of thousands of records.

Export effect

Source Code

The full source can be found at GitHub: github.com/heartsuit/d…

If you have any questions or any bugs are found, please feel free to contact me.

Your comments and suggestions are welcome!