Download and generate excel template

Example code:

① Entity class of template:

@Getter
@Setter
@ToString
@SuperBuilder
@NoArgsConstructor
public class WsWorkerTemplate{
    @ApiModelProperty(value = "NAME")
    private String workerName;

    @ApiModelProperty(value = "Phone Number")
    private String workerPhone;
    
    @ApiModelProperty(value = "ID number")
    private String workerIdcard;
}

② Class to generate template:

    @ApiOperation("Download generated excel template")
    @GetMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response) throws IOException {

        WsWorkerTemplate wsWorker = new WsWorkerTemplate();
//        wsWorker.setWorkerName("张三");
//        wsWorker.setWorkerPhone("15992714704");
//        wsWorker.setWorkerIdcard("130501197512158843");
        List<WsWorkerTemplate> rows = CollUtil.newArrayList(wsWorker);

        // Create writer by tool class, default create xls format
        ExcelWriter writer = ExcelUtil.getWriter();
        // custom header alias
        writer.addHeaderAlias("workerName", "Name");
        writer.addHeaderAlias("workerPhone", "mobile number");
        writer.addHeaderAlias("workerIdcard", "ID number");
        // merge the header row after the cell, using the default header style
        String content = "Instructions for filling out the form: \n\t" +
                "1, name: 2 to 15 Chinese characters; \n\t" +
                "2, cell phone number: 11-digit cell phone number format; \n\t" +
                "3, ID number: 18-bit second-generation ID number; \n\t";
        writer.merge(2,content,false);
        //set the column width
        writer.setColumnWidth(-1,30);

        writer.setRowHeight(0,80);
        // write the content at once, use the default style, and force the output headers
        writer.write(rows, true);
        //out is the OutputStream, the target stream to write out to
        // response is the HttpServletResponse object
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls is the name of the file that pops up the download dialog box, it can't be Chinese, please encode it yourself
        //response.setHeader("Content-Disposition", "attachment;filename=test-file.xls");
        String excelName="worker.xls";
        // When setting response.setHeader, if it contains Chinese characters, it must be converted to ISO8859-1 format, otherwise the set Chinese will be incorrect.
        response.setHeader("content-disposition", "attachment;filename="+new String(excelName.getBytes("gb2312"), "ISO8859-1"));
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        // Close the writer and free memory
        writer.close();
        // Remember to close the output servlet stream here
        IoUtil.close(out);
    }

excel import

 /**
     * Batch Import
     *
     * @param file
     * @return
     */
    public ResultObj<Object> importWorker(@RequestParam("file") MultipartFile file, String worksiteId) {
        ResultObj<Object> resultObj = new ResultObj<Object>();
        String message1 = "";
        List<String> strList = new LinkedList<>();
        List<WsWorker> list = new LinkedList<>();
        try {
            InputStream inputStream = file.getInputStream();
            list = getWsWorkerExcel(inputStream);
            if (list != null && list.size() > 0) {
                    for (WsWorker w : list) {
                        w.setWorksiteId(worksiteId);
                        w.setCheckResult(0);
                        wsWorkerMapper.insertSelective(w);
                    }
                    resultObj.setStatus(200);
                    resultObj.setMsg("import successfully");
              }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return resultObj;
    }

Get Excel data:

  public List<WsWorker> getWsWorkerExcel(InputStream inputStream) {

        ExcelReader reader = ExcelUtil.getReader(inputStream);
        reader.addHeaderAlias("Name", "workerName");
        reader.addHeaderAlias("Phone Number", "workerPhone");
        reader.addHeaderAlias("ID Number", "workerIdcard");

        List<WsWorker> workers = reader.read(1, 2, WsWorker.class);
        return workers;
    }