File download - Download Excel

Keywords: Java Excel Database Attribute Spring

;

1. Prepare the data first. The data here is not the data directly found in the database, but a copy of the data found. The data of the two does not affect each other, so that the copied data can be modified.

(1) define a method (implementation class method of service layer) of data found from database, omit the method of mapper mapping file and dao layer interface

/**
 * Parameters are entity classes. All data is obtained according to parameter conditions. forEach The method is just a loop through the obtained elements, and the attribute values of the elements can be modified according to the business requirements
 * /
public List<User> export(User user) {
    List<User> userList = userDao.selectAll(user);
    userList.stream().forEach(e->{
        // Password echo character representation
        e.setPassword(e.getPassword().replaceAll(e.getPassword(), "******"));
    });
    return userList;
}

② define a method to copy data, in which you can use the copyProperties method of the BeanUtils package provided by the Spring framework to copy the properties of the object

/**
 * How to copy attributes
 */
public List<User> queueExcel(User user) {
    List<User> userList = export(user);
    List<User> exportList = new ArrayList<>();
    userList.stream().forEach(e->{
        // Define a user Object to accept the properties of the object
        User user = new User();
//Object properties copying BeanUtils.copyProperties(e, user); exportList.add(user); }); return exportList; }

 

2. Once the data is ready, you need to define a method. The content of the method is: the header corresponding to the data; the file Excel is exported to the response.

@RequestMapping(value = "/queueExcel",method = RequestMethod.POST)
@ResponseBody
public void queueExcel(@RequestBody User user, HttpServletResponse response){
    try{
        List<User> exportList = userService.queueExcel(user);
        LinkedHashMap<String, String> columnMap = new LinkedHashMap<>();
        columnMap.put("userId", "Job No");
        columnMap.put("username", "user name");
        columnMap.put("realName", "Real name");
        columnMap.put("password", "User password");
        columnMap.put("gender", "Gender");

        //Set export file name
        String fileName = "test.xlsx";

        //export Excel reach HttpServletResponse
        utils.excel.ExcelUtil.exportExcelToHttpServletResponse(exportList, columnMap, fileName, response);
        response.getOutputStream().flush();
    } catch (Exception me) {
        me.printStackTrace();
    }
}

 

3. There is no Excel file to export to the HttpServletResponse object. In this case, you need to create classes and methods under the defined package.

/**
 * Export Excel to HttpServletResponse
 *
 * @param dataList  Data list
 * @param columnMap Output columns and their Chinese column names in order
 * @param fileName  file name
 * @param <T>
 */
public static <T> void exportExcelToHttpServletResponse(List<T> dataList, LinkedHashMap<String, String> columnMap, String fileName, HttpServletResponse response) {
    if (null == dataList) {
        return;
    }
    try {
        //Write to byte output stream
        ByteArrayOutputStream byteArrayOutputStream = exportExcelOutputStream(dataList, columnMap);
        if (null == byteArrayOutputStream) {
            return;
        }
        //Export file stream to HttpServletResponse
        //Prevent random code, set HttpServletResponse code
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        //Set page disable caching
        response.setDateHeader("Expires", 0);
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Pragma", "no-cache");
        //Set download file name
        response.setHeader("Content-Disposition", "attachment;filename=" +
                new String(fileName.getBytes("gb2312"), "ISO8859-1"));
        //File output stream write HttpServletResponse
        byteArrayOutputStream.writeTo(response.getOutputStream());
    } catch (Exception e) {
        e.printStackTrace();
    }
}

 

4. There seems to be a lack of a way to write to the byte stream

/**
 * Export to Excel output stream
 *
 * @param dataList  Data list
 * @param columnMap Output columns and their Chinese column names in order
 * @param <T>
 * @return oss File url
 */
public static <T> ByteArrayOutputStream exportExcelOutputStream(List<T> dataList, LinkedHashMap<String, String> columnMap) {
    ByteArrayOutputStream byteArrayOutputStream = null;
    if (null == dataList) {
        return null;
    }
    try {
        // Create from tool class writer,establish xlsx format
        ExcelWriter writer = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
        //
        if (null != columnMap) {
            //Set column title
            for (Map.Entry<String, String> entry : columnMap.entrySet()) {
                writer.addHeaderAlias(entry.getKey(), entry.getValue());
            }
            //Filter data columns based on column headers
            ArrayList<Map<String, Object>> rows = new ArrayList<>();
            Map<String, Object> tempMap1 = null;
            Map<String, Object> tempMap2 = null;
            String column;
            for (T data : dataList) {
                tempMap1 = BeanUtil.beanToMap(data);
                tempMap2 = new LinkedHashMap<>();
                for (Map.Entry<String, String> entry : columnMap.entrySet()) {
                    column = entry.getKey();
                    tempMap2.put(column, tempMap1.get(column));
                }
                rows.add(tempMap2);
            }
            //Output filtered data
            writer.write(rows);
        } else {
            //But filtering, direct output data
            writer.write(dataList);
        }
        //Write to byte output stream
        byteArrayOutputStream = new ByteArrayOutputStream();
        writer.flush(byteArrayOutputStream);
        //close writer,Free memory
        writer.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    //
    return byteArrayOutputStream;
}

Posted by Duey on Tue, 12 May 2020 07:10:01 -0700