2.poi导出工具类2024-01-02

前言

之前的一篇文章已经介绍了POI导入的工具类，然后有小伙伴私信我说能不能整理一个POI导出的工具类。现在它来了！！！

如果有小伙伴不知道POI导入的工具类，可以看我上篇文章：POI导入工具类

现在我们言归正传，直接开始

工具类

POI导出工具类

public class DownLoadExcelUtil {

    /**
     * 导出excel
     *
     * @param response     http响应
     * @param list        数据列表 List<E> 实体类列表
     * @param nameMap      excel表头Map，存放"属性名,中文名"格式字符串，"中文名"为excel标题行， "属性名"为对象字段名
     * @param filePath    导出文件全路径
     * @param sheetName   sheet名称：sheet1
     * @param dataPattern 日期格式：yyyy-MM-dd HH:mm:ss
     * @throws Exception 另一个程序正在使用此文件，进程无法访问
     */
    public static <E> void exportListEntity(HttpServletResponse response, List<E> list, LinkedHashMap<String, String> nameMap,
                                            String filePath, String sheetName, String dataPattern) throws Exception {
        //工作簿
        Workbook wb;
        // 创建模板工作表
        String excelEndStr = ".xls";
        if (filePath.endsWith(excelEndStr)) {
            wb = new HSSFWorkbook();
        } else {
            //大于1024行时会把之前的行写入硬盘，解决内存溢出
            wb = new SXSSFWorkbook(1024);
        }
        //创建sheet
        buildSheet(list, nameMap, sheetName, dataPattern, wb);
        //流输出excel
        streamOutPutExcel(response, filePath, wb);
    }

    /**
     * 流导出excel
     *
     * @param response
     * @param filePath
     * @param wb
     * @throws IOException
     */
    public static void streamOutPutExcel(HttpServletResponse response, String filePath, Workbook wb) throws IOException {
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        wb.write(byteArrayOutputStream);
        byte[] byteArray = byteArrayOutputStream.toByteArray();
        ByteArrayInputStream in = new ByteArrayInputStream(byteArray);
        DownLoadUtil.downLoad(response, in, filePath);
    }

    /**
     * 构建excel的Sheet页
     *
     * @param list
     * @param nameMap
     * @param sheetName
     * @param dataPattern
     * @param wb
     * @param <E>
     * @throws IllegalAccessException
     */
    public static <E> void buildSheet(List<E> list, LinkedHashMap<String, String> nameMap, String sheetName, String dataPattern, Workbook wb) throws IllegalAccessException {
        // 标题数组
        String[] headerArr = new String[nameMap.size()];
        // 字段名数组
        String[] includeArr = new String[nameMap.size()];
        // 遍历参数中的map进行赋值
        int num = 0;
        for (String key: nameMap.keySet()) {
            includeArr[num] = nameMap.get(key);
            headerArr[num] = key;
            num ++;
        }
        List<String> includeList = Arrays.asList(includeArr);
        //字体
        Font font = wb.createFont();
        font.setBold(true);
        //样式
        CellStyle styleBold = createBorderedStyle(wb);
        styleBold.setFont(font);
        styleBold.setVerticalAlignment(VerticalAlignment.CENTER);
        //自动换行
        styleBold.setWrapText(false);
        //样式
        CellStyle styleWrap = createBorderedStyle(wb);
        styleWrap.setWrapText(false);
        styleWrap.setVerticalAlignment(VerticalAlignment.TOP);
        //表
        Sheet sheet = wb.createSheet(sheetName);
        for (int i = 0; i < headerArr.length; i++) {
            sheet.autoSizeColumn(i);
        }
        //行
        Row row = sheet.createRow(0);
        //单元格
        Cell cell;
        //写入标题行
        for (int i = 0; i < headerArr.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headerArr[i]);
            cell.setCellStyle(styleBold);
        }
        //写入数据
        //实体类
        E e;
        //属性数组
        Field[] fields = new Field[]{};
        //属性
        Field field;
        //属性值
        Object value;
        //yyyy-MM-dd HH:mm:ss
        DateFormat dtf = new SimpleDateFormat(dataPattern);
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        if (list.size() > 0 && includeArr.length > 0) {
            fields = putFieldsInOrderByExcelHeader(list.get(0),includeArr);
        }
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            e = list.get(i);
            // 利用反射，获取属性数组
            for (int f = 0, c = 0; f < fields.length; f++) {
                field = fields[f];
                if (field != null && includeList.contains(field.getName())) {
                    cell = row.createCell(c);
                    cell.setCellStyle(styleWrap);
                    //设置些属性是可以访问的
                    field.setAccessible(true);
                    //得到此属性的值
                    value = field.get(e);
                    //Byte,Short,Int,Long,Float,Double,Boolean,Char, String,Date,BigDecimal,byte[]
                    //cell.setCellValue: boolean,Calendar,Date,double,RichTextString,String

                    if (value == null) {
                        c++;
                        continue;
                    } else if (value instanceof Date) {
                        cell.setCellValue(dtf.format((Date) value));
                    } else if (value instanceof BigDecimal) {
                        cell.setCellValue(Double.parseDouble(value.toString()));
                    } else {
                        cell.setCellValue(value.toString());
                    }
                    c++;
                }
            }
        }
    }

    /**
     * 创建样式
     *
     * @param wb
     * @return
     */
    private static CellStyle createBorderedStyle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    public DownLoadExcelUtil() {
        super();
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
    }

    @Override
    public String toString() {
        return super.toString();
    }

    @Override
    protected Object clone() throws CloneNotSupportedException {
        return super.clone();
    }

    @Override
    public int hashCode() {
        return super.hashCode();
    }

    @Override
    public boolean equals(Object obj) {
        return super.equals(obj);
    }

    /**
     * 通过Excel的表头转化为对象属性数组
     *
     * @param object 对象
     * @param excelHeaderArr 表头数组
     * @return 属性数组
     */
    private static Field[] putFieldsInOrderByExcelHeader(Object object, String[] excelHeaderArr) {
        Field[] fields = new Field[excelHeaderArr.length];
        List<Field> fieldsInOrder = new ArrayList<>();
        if (object == null) {
            return fields;
        }
        Field[] objectFields = object.getClass().getDeclaredFields();
        if (excelHeaderArr.length == 0) {
            return objectFields;
        }
        if (objectFields.length > 0) {
            Map<String,Object> fieldMap = new HashMap<>(32);
            Arrays.stream(objectFields).forEach(
                    field -> {
                        if (field!=null) {
                            fieldMap.put(field.getName(),field);
                        }
                    }
            );
            if (fieldMap.size() > 0) {
                for (String headerPropertyName : excelHeaderArr) {
                    if (StringUtils.isNotBlank(headerPropertyName) && fieldMap.get(headerPropertyName)!=null) {
                        fieldsInOrder.add((Field)fieldMap.get(headerPropertyName));
                    }
                }
                if (!fieldsInOrder.isEmpty()) {
                    fieldsInOrder.toArray(fields);
                }
            }
        }
        return fields;
    }
}

使用

首先还是要设置一下实体类

@AllArgsConstructor
@Data
public class UserVo {

    @ApiModelProperty(value = "账号", dataType = "String")
    private String account;

    @ApiModelProperty(value = "密码", dataType = "String")
    private String userPassword;
}

编写导出接口和导出的逻辑

@GetMapping("/downloadExcelTest")
void downloadExcelTest(HttpServletResponse response, HttpServletRequest request) throws Exception {
    loginService.downloadExcelTest(response, request);
}

public void downloadExcelTest(HttpServletResponse response, HttpServletRequest request) throws Exception {
    //模拟导出的数据
    List<UserVo> list = new ArrayList<>();
    list.add(new UserVo("admin","123"));
    list.add(new UserVo("xiaoming","456"));
    list.add(new UserVo("xiaohong","789"));

    String sheetName = "sheet1";
    String filePath = "导出Excel测试.xls";
    LinkedHashMap<String, String> map = ApiModelPropertyUtils.getApiModelPropertyValueAndFieldName(UserVo.class);
    DownLoadExcelUtil.exportListEntity(response, list, map, filePath, sheetName, "yyyy-MM-dd HH:mm:ss");
}