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;
    }

Compress picture size

@Log4j2
public class Base64Util {

    /**
     * Compressed images within 100k
     *
     * @param base64Img
     * @return
     */
    public static String resizeImageTo100K(String base64Img) {
        try {
            BufferedImage src = base64String2BufferedImage(base64Img);
            BufferedImage output = Thumbnails.of(src).size(src.getWidth()/3, src.getHeight()/3).asBufferedImage();
            String base64 = imageToBase64(output);
            if (base64.length() - base64.length()/8 * 2 > 40000) {
                output = Thumbnails.of(output).scale(1/(base64.length()/40000)).asBufferedImage();
                base64 = imageToBase64(output);
            }
            return base64;
        } catch (Exception e) {
            return base64Img;
        }
    }


    /**
     * base64 convert to BufferedImage
     *
     * @param base64string
     * @return
     */
    public static BufferedImage base64String2BufferedImage(String base64string) {
        BufferedImage image = null;
        try {
            InputStream stream = BaseToInputStream(base64string);
            image = ImageIO.read(stream);
        } catch (IOException e) {
            log.info("");
        }
        return image;
    }

    /**
     * Base64 convert to InputStream
     *
     * @param base64string
     * @return
     */
    private static InputStream BaseToInputStream(String base64string) {
        ByteArrayInputStream stream = null;
        try {
            BASE64Decoder decoder = new BASE64Decoder();
            byte[] bytes1 = decoder.decodeBuffer(base64string);
            stream = new ByteArrayInputStream(bytes1);
        } catch (Exception e) {
            // TODO: handle exception
        }
        return stream;
    }

    /**
     * BufferedImage switch to base64
     *
     * @param bufferedImage
     * @return
     */
    public static String imageToBase64(BufferedImage bufferedImage) {
        Base64 encoder = new Base64();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        try {
            ImageIO.write(bufferedImage, "jpg", baos);
        } catch (IOException e) {
            log.info("");
        }
        return new String(encoder.encode((baos.toByteArray())));
    }

}