工具类之导出EXCEL和CSV

文件名注解，参数为文件名，可以选择增加日期后缀，作用在要导出是实体类上

@Target(ElementType.TYPE)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface FileName {​    // 前缀    String value();​    // 是否加日期后缀    boolean addDate() default false;​    // 日期后缀    String pattern() default "yyyy-MM-dd";}

 

文件头注解，参数有标题名，当某些值是日期类型时可以进行格式化，作用在要导出是实体类里面的具体属性上

@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface FileHeader {​    // 标题    String value();​    // 日期类型数据格式化    String pattern() default "yyyy-MM-dd HH:mm:ss";}

 

CSV分页导出，先进行实例化，参数有http返回和带有注解的实体类，分批调用export写入数据

public class CsvPageExport<T> {​    private HttpServletResponse response;    private Pair<List<FileHeader>, List<Field>> headerField;​    public CsvPageExport(HttpServletResponse response, Class<T> clazz) {        this.response = response;        this.headerField = ExportUtil.getHeaderField(clazz);        ExportUtil.writeCsvResponse(response, ExportUtil.getFileName(clazz));        ExportUtil.writeCsv(response, ExportUtil.getHeader(headerField.getLeft()));    }​    public void export(List<T> dataList) {        ExportUtil.writeCsv(response, ExportUtil.getValue(dataList, headerField));    }}

 

EXCEL多SHEET对象，属性有sheet名字和它里面的具体数据集合

public class SheetData<T> {​    private String sheetName;    private List<T> dataList = new ArrayList<>();​    public String getSheetName() {        return sheetName;    }​    public SheetData<T> setSheetName(String sheetName) {        this.sheetName = sheetName;        return this;    }​    public List<T> getDataList() {        return dataList;    }​    public SheetData<T> setDataList(List<T> dataList) {        this.dataList = dataList;        return this;    }}

 

具体实现逻辑，主要有注解导出EXCEL，注解导出多SHEET的EXCEL，注解导出CSV

public class ExportUtil {​    private static final Logger LOGGER = LoggerFactory.getLogger(ExportUtil.class);    private static final String EMPTY_FILE_NAME = "空文件";​    /**     * 注解导出EXCEL     */    public static void excel(HttpServletResponse response, List<?> dataList) {        Triple<String, String[], List<String[]>> nameHeaderValue = getNameHeaderValue(dataList);        Workbook workbook = new XSSFWorkbook();        Sheet sheet = workbook.createSheet();        writeExcel(workbook, sheet, nameHeaderValue.getMiddle(), nameHeaderValue.getRight());        writeExcelResponse(response, nameHeaderValue.getLeft(), workbook);    }​    /**     * 注解导出多SHEET的EXCEL     */    public static <T> void multiSheetExcel(HttpServletResponse response, List<SheetData<T>> sheetDataList) {        multiSheetExcel(response, sheetDataList, getDefaultFileName(sheetDataList));    }​    /**     * 注解导出多SHEET的EXCEL     */    public static <T> void multiSheetExcel(HttpServletResponse response, List<SheetData<T>> sheetDataList, String fileName) {        Workbook workbook = new XSSFWorkbook();        for (SheetData<T> sheetData : sheetDataList) {            String sheetName = sheetData.getSheetName();            Sheet sheet = StringUtils.isBlank(sheetName) ? workbook.createSheet() : workbook.createSheet(sheetName);            Pair<String[], List<String[]>> headerValue = getHeaderValue(sheetData.getDataList());            writeExcel(workbook, sheet, headerValue.getLeft(), headerValue.getRight());        }        writeExcelResponse(response, fileName, workbook);    }​    /**     * 注解导出CSV     */    public static void csv(HttpServletResponse response, List<?> dataList) {        Triple<String, String[], List<String[]>> nameHeaderValue = getNameHeaderValue(dataList);        writeCsvResponse(response, nameHeaderValue.getLeft());        writeCsv(response, nameHeaderValue.getMiddle());        writeCsv(response, nameHeaderValue.getRight());    }​    protected static void writeExcel(Workbook workbook, Sheet sheet, String[] header, List<String[]> value) {        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();        createRow(sheet, 0, header, cellStyle);        for (int i = 0; i < value.size(); i++) {            createRow(sheet, i + 1, value.get(i), cellStyle);        }    }​    protected static <T> String getDefaultFileName(List<SheetData<T>> sheetDataList) {        if (CollectionUtils.isEmpty(sheetDataList)) {            return EMPTY_FILE_NAME;        }        T data = null;        for (SheetData<T> sheetData : sheetDataList) {            for (T o : sheetData.getDataList()) {                data = o;                break;            }        }        if (data == null) {            return EMPTY_FILE_NAME;        }        return getFileName(data.getClass());    }​    protected static void writeExcelResponse(HttpServletResponse response, String fileName, Workbook workbook) {        response.setContentType("application/vnd.ms-excel;charset=UTF-8");        String filename;        try {            filename = URLEncoder.encode(fileName + ".xlsx", "UTF-8");        } catch (UnsupportedEncodingException e) {            filename = System.currentTimeMillis() + ".xlsx";        }        response.setHeader("Content-Disposition", "attachment; filename=" + filename);        try {            OutputStream out = response.getOutputStream();            workbook.write(out);        } catch (IOException e) {            LOGGER.error("{} export exception", filename, e);        }    }​    protected static void createRow(Sheet sheet, int row, String[] cellValues, CellStyle cellStyle) {        Row iRow = sheet.createRow(row);        for (int i = 0; i < cellValues.length; i++) {            Cell cell = iRow.createCell(i);            cell.setCellStyle(cellStyle);            cell.setCellValue(cellValues[i]);        }    }​    protected static void writeCsvResponse(HttpServletResponse response, String fileName) {        response.setContentType("text/csv;charset=UTF-8");        String filename;        try {            filename = URLEncoder.encode(fileName + ".csv", "UTF-8");        } catch (UnsupportedEncodingException e) {            filename = System.currentTimeMillis() + ".csv";        }        response.setHeader("Content-Disposition", "attachment; filename=" + filename);        try {            response.getOutputStream().write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});            response.getOutputStream().flush();        } catch (IOException e) {            LOGGER.error("write CSV add BOM head error", e);        }    }​    protected static void writeCsv(HttpServletResponse response, String data) {        try {            response.getOutputStream().write((data + "\n").getBytes(StandardCharsets.UTF_8));            response.getOutputStream().flush();        } catch (IOException e) {            LOGGER.error("write CSV error", e);        }    }​    protected static void writeCsv(HttpServletResponse response, String[] data) {        StringBuilder stringBuilder = new StringBuilder();        for (String e : data) {            if (stringBuilder.length() > 0) {                stringBuilder.append(",");            }            stringBuilder.append(e.replace(",", " "));        }        writeCsv(response, stringBuilder.toString());    }​    protected static void writeCsv(HttpServletResponse response, List<String[]> data) {        for (String[] arr : data) {            writeCsv(response, arr);        }    }​    protected static Triple<String, String[], List<String[]>> getNameHeaderValue(List<?> dataList) {        if (CollectionUtils.isEmpty(dataList)) {            return Triple.of(EMPTY_FILE_NAME, new String[0], Collections.<String[]>emptyList());        }        Pair<String[], List<String[]>> pair = getHeaderValue(dataList);        return Triple.of(getFileName(dataList.get(0).getClass()), pair.getLeft(), pair.getRight());    }​    protected static Pair<String[], List<String[]>> getHeaderValue(List<?> dataList) {        if (CollectionUtils.isEmpty(dataList)) {            return Pair.of(new String[0], Collections.<String[]>emptyList());        }        Pair<List<FileHeader>, List<Field>> headerField = getHeaderField(dataList.get(0).getClass());        return Pair.of(getHeader(headerField.getLeft()), getValue(dataList, headerField));    }​    protected static List<String[]> getValue(List<?> dataList, Pair<List<FileHeader>, List<Field>> headerField) {        if (CollectionUtils.isEmpty(dataList) || headerField == null) {            return Collections.emptyList();        }        List<String[]> value = new ArrayList<>(dataList.size());        for (Object data : dataList) {            String[] temp = new String[headerField.getLeft().size()];            for (int index = 0; index < headerField.getLeft().size(); index++) {                try {                    temp[index] = getData(headerField.getLeft().get(index), headerField.getRight().get(index).get(data));                } catch (Exception e) {                    LOGGER.error("get value error.", e);                    temp[index] = "【error】";                }            }            value.add(temp);        }        return value;    }​    protected static Pair<List<FileHeader>, List<Field>> getHeaderField(Class<?> clazz) {        Field[] fields = clazz.getDeclaredFields();        List<FileHeader> headerList = new ArrayList<>(fields.length);        List<Field> fieldList = new ArrayList<>(fields.length);        for (Field field : fields) {            FileHeader fileHeader = field.getAnnotation(FileHeader.class);            if (fileHeader == null) {                continue;            }            field.setAccessible(true);            headerList.add(fileHeader);            fieldList.add(field);        }        return Pair.of(headerList, fieldList);    }​    protected static String getData(FileHeader header, Object o) {        if (o == null) {            return "";        }        return o instanceof Date ? DateFormatUtils.format((Date) o, header.pattern()) : String.valueOf(o);    }​    protected static String getFileName(Class<?> clazz) {        FileName fileName = clazz.getAnnotation(FileName.class);        if (fileName == null) {            return "";        }        return fileName.addDate() ? fileName.value() + DateFormatUtils.format(new Date(), fileName.pattern()) : fileName.value();    }​    protected static String[] getHeader(Class<?> clazz) {        return getHeader(getHeaderField(clazz).getLeft());    }​    protected static String[] getHeader(List<FileHeader> headerList) {        String[] header = new String[headerList.size()];        int i = 0;        for (FileHeader fileHeader : headerList) {            header[i] = fileHeader.value();            i++;        }        return header;    }}