编码技巧——导出工具类

七海健人

已于 2022-04-18 11:17:44 修改

阅读量374

点赞数 1

分类专栏：代码技巧文章标签： java

于 2022-04-17 15:03:07 首次发布

本文链接：https://blog.csdn.net/minghao0508/article/details/123972742

版权

代码技巧专栏收录该内容

50 篇文章 12 订阅

订阅专栏

在开发过程中，对于一些B端的业务，使用者是内部的运营管理同事或CP，基本的功能就是一些表单的查询、查看、结果导出、配置、审核等；对于查询和导出功能，经常需要对一些数据导出到Excel文件，或将一个Excel文件的数据读取到数据库；

在java操作Excel可以使用POI组件；本篇给出相关的代码示例，支持多类型的结果集导、输入流转Excel并读取Excel文件；

1. 依赖

<!--Excel-Apache poi-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${apache.poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${apache.poi.ooxml.version}</version>
</dependency>

<apache.poi.version>4.0.1</apache.poi.version>
<apache.poi.ooxml.version>3.17</apache.poi.ooxml.version>

2. 结构

支持多种数据集导出，包括.xlsx和.csv，因此涉及导出工具工厂、导出工具抽象模板、加在导出实体需导出字段的注解、导出工具实现类；

（1）注解：

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author AA
 * ExcelUtils工具的相关注解,加在需要导出的DO的属性上; 打了注解，才导出; alias不为"",则设置属性别名;
 */

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelExport {

    /**
     * 别名
     */
    String alias() default "";

    /**
     * 默认表格填充值
     */
    String defaultCeilValue() default "";
}

（2）工厂：

/**
 * @author AA
 * @description 文件导出工厂类, 当前支持excel即cvs格式导出
 * @date 2021/3/9
 */
public class FileExporterFactory {

    /**
     * 根据文件类型和导出数据实体类获取导出工具实例
     */
    public static AbstractFileExporter createExporter(String type, Class clazz) {
        // .cvs格式
        if (AbstractFileExporter.OFFICE_EXCEL_CSV.equals(type)) {
            CsvExporter csvExporter = new CsvExporter();
            csvExporter.init(clazz, type);
            return csvExporter;
        }
        // .xlsx格式
        if (AbstractFileExporter.OFFICE_EXCEL_XLSX.equals(type)) {
            ExcelExporter excelExporter = new ExcelExporter();
            excelExporter.init(clazz, type);
            return excelExporter;
        }
        return null;
    }

}

（3）抽象模板：

package com.AA.common.utils.exporter;

import com.AA.common.utils.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @author AA
 * @description 文件导出模板抽象类
 * @date 2021/3/9
 */
@Slf4j
public abstract class AbstractFileExporter {

    /**
     * 文件类型后缀
     */
    public static final String OFFICE_EXCEL_XLSX = ".xlsx";
    public static final String OFFICE_EXCEL_CSV = ".csv";

    /**
     * 表格头缓存,用map实现
     */
    private static HashMap<Class, List<String>> headersMap = new HashMap<>();

    /**
     * 导出数据对应的实体类
     */
    Class clazz;

    /**
     * 导出文件类型
     */
    String fileType;

    /**
     * 初始化导出工具
     */
    abstract void initExporter(Class clazz);

    /**
     * 填充数据行,数据可以是不对齐的
     */
    abstract void writeRows(List<List<String>> rows);

    /**
     * 根据导出数据对应的实体类,初始化导出工具
     */
    void init(Class clazz, String fileType) {
        this.clazz = clazz;
        this.fileType = fileType;
        initExporter(clazz);
    }

    /**
     * 数据集转化为导出数据对象
     */
    public void appendData(List<?> data) {
        List<List<String>> rows = convertToList(data);
        writeRows(rows);
    }

    /**
     * 写输出流
     */
    abstract public void writeStream(OutputStream outputStream);

    /**
     * 数据集转换为二维表格数据,属性为列
     */
    private List<List<String>> convertToList(List<?> data) {
        final AtomicReference<Integer> rowRef = new AtomicReference<>();
        List<List<String>> result = new LinkedList<>();
        for (int i = 0; i < data.size(); i++) {
            rowRef.set(i);

            List<String> rowItems = Stream.of(clazz.getDeclaredFields())
                    .parallel()
                    // 先反射获取T的所有成员Field
                    .peek(field -> field.setAccessible(true))
                    // 打了注解才导出
                    .filter(field -> {
                        ExcelExport excelExport = field.getAnnotation(ExcelExport.class);
                        return (null != excelExport);
                    })
                    .map(field -> {
                        final String defaultCeilValue = field.getAnnotation(ExcelExport.class).defaultCeilValue();
                        try {
                            Object item = data.get(rowRef.get());
                            // 通过field.get(Object)反射获取Object的成员对象
                            Object value = field.get(item);
                            // 1. 为空值时填充表格默认值
                            if (value == null) {
                                return defaultCeilValue;
                            }
                            // 2. 对于Date类，转换Date类型的格式 yyyy-MM-dd HH:mm:ss
                            if (field.getType().equals(Date.class)) {
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat(DateUtils.DATETIME_FORMAT_1);
                                return simpleDateFormat.format((Date) value);
                            }
                            // [warning]防止格式自动转换,加了转义符
                            return value.toString() + "\t";
                        } catch (IllegalAccessException e) {
                            log.error("IllegalAccessException! {}", e.getMessage());
                            return null;
                        }
                    })
                    .collect(Collectors.toList());
            result.add(rowItems);
        }
        return result;
    }

    /**
     * 获取属性名-列名
     */
    public static synchronized List<String> getHeaders(Class<?> clazz) {
        List<String> cachedHeaders = headersMap.get(clazz);
        if (CollectionUtils.isNotEmpty(cachedHeaders)) {
            return cachedHeaders;
        }
        List<String> headers = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        Stream.of(fields).forEach(field -> {
            // 每个属性获取其注解
            field.setAccessible(true);
            ExcelExport excelExport = field.getAnnotation(ExcelExport.class);
            // 未打注解则跳过该属性
            if (null == excelExport) {
                return;
            }
            // 否则取别名
            headers.add(StringUtils.isBlank(excelExport.alias()) ? field.getName() : excelExport.alias());
        });
        headersMap.put(clazz, headers);
        return headers;
    }

    /**
     * 获取编码的文件名
     */
    public String getFileName(String fileName) {
        if (StringUtils.isEmpty(this.fileType)) {
            log.error("init before getFileName!");
            throw new RuntimeException();
        }
        fileName = StringUtils.isEmpty(fileName) ? StringUtils.EMPTY : fileName;
        String originFileName = fileName + DateUtil.toStringByFormat(new Date(), DateUtil.DATETIME_FORMAT_2).trim() + fileType;
        try {
            return new String(originFileName.getBytes("GBK"), StandardCharsets.ISO_8859_1);
        } catch (UnsupportedEncodingException e) {
            log.error("failed to encode export fileName!");
            throw new RuntimeException();
        }
    }
}

（4）实现类-导出为excel：

package com.AA.common.utils.exporter;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * @author AA
 * @description excel导出工具
 * @date 2021/3/9
 */
@Slf4j
public class ExcelExporter extends AbstractFileExporter {

    /**
     * excel文件对象
     */
    private XSSFWorkbook workbook;

    /**
     * sheet单张表
     */
    XSSFSheet sheet;

    /**
     * 初始化导出工具
     *
     * @param clazz
     */
    @Override
    void initExporter(Class clazz) {
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet();
        List<String> headers = getHeaders(clazz);
        buildTitle(sheet, headers);
    }

    /**
     * 填充数据行,数据可以是不对齐的
     *
     * @param rows
     */
    @Override
    void writeRows(List<List<String>> rows) {
        int columns = getHeaders(clazz).size();
        for (int rownum = 0; rownum < rows.size(); rownum++) {
            XSSFRow row = sheet.createRow(rownum + 1);
            for (int col = 0; col < columns; col++) {
                row.createCell(col).setCellValue(rows.get(rownum).get(col));
            }
        }
    }

    /**
     * 写输出流
     *
     * @param outputStream
     */
    @Override
    public void writeStream(OutputStream outputStream) {
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            log.error("failed to writeStream of Excel! e:{}", e.getMessage(), e);
        }
    }

    /**
     * 设置sheet的表头
     */
    private static void buildTitle(XSSFSheet sheet, List<String> headers) {
        int columns = headers.size();
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < columns; i++) {
            XSSFCell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers.get(i));
            cell.setCellValue(text);
        }
    }
}

注意几种excel对象的不同：

第一种：HSSFWorkbook

针对EXCEL 2003版本，扩展名为.xls，此种的局限就是导出的行数最多为65535行。因为导出行数受限，不足7万行，所以一般不会发送内存溢出(OOM)的情况；

第二种：XSSFWorkbook

这种形式的出现是由于第一种HSSF的局限性产生的，因为其导出行数较少，XSSFWorkbook应运而生，其对应的是EXCEL2007+ ，扩展名为.xlsx ，最多可以导出104万行，不过这样就伴随着一个问题–OOM内存溢出。因为使用XSSFWorkbook创建的book sheet row cell 等是存在内存中的，并没有持久化到磁盘上，那么随着数据量的增大，内存的需求量也就增大。那么很有可能出现 OOM了，那么怎么解决呢？

第三种:SXSSFWorkbook 　要求poi.jar 3.8+

SXSSFWorkbook可以根据行数将内存中的数据持久化写到文件中。

此种的情况就是设置最大内存条数，比如设置最大内存量为5000行， new SXSSFWookbook(5000)，当行数达到 5000 时，把内存持久化写到文件中，以此逐步写入，避免OOM。这样就完美解决了大数据下导出的问题；

API文档：POI核心类 -POI教程™

应用中我们一般使用后2种，本篇讲的是XSSFWorkbook，而不推荐旧格式的HSSFWorkbook，因为打开文件可能存在兼容性问题、单个sheet的总行书受限65535；

（5）实现类-导出为csv：

package com.AA.common.utils.exporter;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import java.io.*;
import java.nio.charset.Charset;
import java.util.List;

/**
 * @author AA
 * @description cvs文件导出工具
 * @date 2021/3/9
 */
@Slf4j
public class CsvExporter extends AbstractFileExporter {

    /**
     * 字节流
     */
    private ByteArrayOutputStream byteArrayOutputStream;

    /**
     * 字节流输出
     */
    private PrintWriter printWriter;

    /**
     * 初始化导出工具
     *
     * @param clazz
     */
    @Override
    void initExporter(Class clazz) {
        byteArrayOutputStream = new ByteArrayOutputStream(20 * 1024 * 1024);
        printWriter = new PrintWriter(new OutputStreamWriter(byteArrayOutputStream, Charset.forName("GBK")));
        List<String> headers = getHeaders(clazz);
        printWriter.println(StringUtils.join(headers, ","));
    }

    /**
     * 填充数据行,数据可以是不对齐的
     *
     * @param rows
     */
    @Override
    void writeRows(List<List<String>> rows) {
        rows.forEach(row -> {
            printWriter.println(StringUtils.join(row, ","));
        });
        printWriter.flush();
    }

    /**
     * 写输出流
     *
     * @param outputStream
     */
    @Override
    public void writeStream(OutputStream outputStream) {
        if (outputStream == null) {
            log.warn("outputStream is null!");
            return;
        }
        byte[] bytes = byteArrayOutputStream.toByteArray();
        try {
            outputStream.write(bytes);
        } catch (IOException e) {
            log.error("failed to write ByteArrayStream! e:{}", e.getMessage(), e);
        } finally {
            try {
                printWriter.close();
                byteArrayOutputStream.close();
            } catch (IOException e) {
                log.error("failed to close ByteArrayStream! e:{}", e.getMessage(), e);
            }
        }
    }
}

csv和xlsx有什么分別?

一个是逗号分割的文本文件，一个是标准的表格文件。CSV本质上就是一个文本文件，无任何格式，但Excel却是一个带有格式的数据表格文件。Excel有最大行数限制，但CSV没有。二者可以相互转化。

参考：csv和xlsx有什么分別? - 知乎

因此，仅对于导出数据存储上，csv文件相比xlsx，占用内存更小，导出更快；07版以后的Excel本质上是xml，这一点可以在修改后缀为.rar验证；

（6）从excel读取数据的工具类：

import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.*;

/**
 * @author AA
 * @description excel解析工具类
 */
@Slf4j
public class ExcelReaderUtil {

    /**
     * excel文件类型后缀
     */
    public static final String OFFICE_EXCEL_XLSX = ".xlsx";

    /**
     * 读取excel行数的最大值，防止OOM
     */
    private static final int MAX_READABLE_LINE_COUNT = 1000;

    /**
     * 解析XSSFSheet，从起始行start到结束行end，取出指定列column的item放入list返回
     *
     * @param xssfSheet
     * @param column    列数,从0计数
     * @param rowStart  起始行(闭区间)
     * @param rowEnd    结束行(开区间)
     * @return
     */
    public static List<String> getListFrXSSFSheet(XSSFSheet xssfSheet, int column, int rowStart, int rowEnd) {
        List<String> result = Lists.newArrayList();
        try {
            XSSFRow xssfRow;
            XSSFCell xssfCell;
            for (int row = rowStart; row < rowEnd; row++) {
                xssfRow = xssfSheet.getRow(row);
                xssfCell = xssfRow.getCell(column);
                //都转成String，避免小数精度
                xssfCell.setCellType(CellType.STRING);
                result.add(getCellValueByCell(xssfCell));
            }
        } catch (Exception e) {
            log.error("failed to read data from excel file!");
            throw new RuntimeException();
        }
        return result;
    }

    /**
     * 获取单元格各类型值，返回字符串类型
     *
     * @param cell
     * @return
     */
    private static String getCellValueByCell(XSSFCell cell) {
        //判断是否为null或空串
        if (cell == null || ("").equals(cell.toString().trim())) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellTypeEnum();
        // 以下是判断数据的类型
        switch (cellType) {
            // 数字
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            // 字符串
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            // Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            // 空值
            case BLANK:
                cellValue = "";
                break;
            // 故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 解析.xlsx文件，转成XSSFWorkbook
     *
     * @param excelFile
     * @return
     */
    public static XSSFWorkbook getXSSFWorkbookFrExcel(MultipartFile excelFile) {
        log.warn("receive excel file, file size:[{} MB]", excelFile.getSize() / (1000 * 1024));
        XSSFWorkbook wb;
        try {
            InputStream is = excelFile.getInputStream();
            wb = new XSSFWorkbook(is);
            is.close();
        } catch (Exception e) {
            log.error("get XSSFWorkbook from excelFile error! e:{} message:{}", e, e.getMessage());
            return null;
        }
        return wb;
    }

    /**
     * 获取XSSFSheet的行数
     *
     * @param xssfSheet
     * @return
     */
    public static int getRowCount(XSSFSheet xssfSheet) {
        return xssfSheet.getPhysicalNumberOfRows();
    }

    /**
     * 获取xssfSheet的列数(默认第一行)
     *
     * @param xssfSheet
     * @return
     */
    public static int getColumnCount(XSSFSheet xssfSheet) {
        return xssfSheet.getRow(0).getPhysicalNumberOfCells();
    }
}

导出查询结果的代码示例：

    /**
     * 导出查询记录
     */
    @Override
    public boolean exportQryRecords(HttpServletResponse response, AwardSendRecordQryCondDTO qryCondDTO) {
        // 导出类型可配置
        String exportType = ConfigManager.getString(ApplicationConstants.FILE_EXPORT_TYPE_KEY, ApplicationConstants.FILE_EXPORT_TYPE);
        AbstractFileExporter exporter = Optional.ofNullable(FileExporterFactory.createExporter(exportType, AwardSendRecordDTO.class))
                .orElseThrow(() -> new BusinessException(ResultCodeEnum.SERVER_BUSYNESS));
        // 获取数据集
        RowBounds rowBounds = PageUtils.getRowBound();
        List<AwardSendRecordDO> dataTemp;
        int total = 0;
        int maxExportItemNum = ConfigManager.getInteger(ApplicationConstants.FILE_EXPORT_ITEMNUM_MAX_KEY, ApplicationConstants.FILE_EXPORT_ITEMNUM_MAX);
        try {
            while (true) {
                Page<AwardSendRecordDO> pageData = awardSendRecordDAO.pageQueryConditionallyAsc(rowBounds, qryCondDTO);
                dataTemp = pageData.getResult();
                if (CollectionUtils.isEmpty(dataTemp)) {
                    log.warn("query result empty.");
                    break;
                }
                // 做多导出(默认)20W数据
                if (total + pageData.size() > maxExportItemNum) {
                    log.warn("reach max export size.[total={} dataTempSize={}]", total, pageData.size());
                    break;
                }
                List<AwardSendRecordDTO> awardSendRecords = dataTemp.stream()
                        .map(awardSendRecordDO -> beanConvertMapper.convertToAwardSendRecordDTO(awardSendRecordDO))
                        .collect(Collectors.toList());
                // 增量填充数据
                exporter.appendData(awardSendRecords);
                total += awardSendRecords.size();
                // 翻页
                rowBounds = PageUtils.next(rowBounds);
            }
            log.warn("query export data end.[currentPage={} total={}]", rowBounds.getOffset(), total);
            // 导出文件
            String fileName = exporter.getFileName("奖品发放记录");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            OutputStream outputStream = response.getOutputStream();
            response.flushBuffer();
            exporter.writeStream(outputStream);
            // 写完数据关闭流
            outputStream.close();
            return true;
        } catch (Exception e) {
            log.error("exportQryRecords error! e:{}", e);
            return false;
        }
    }

查询结果的实体：

@Data
@Slf4j
public class AwardSendRecordDTO {

    /**
     * 流水单号
     */
    @ExcelExport(alias = "商户流水号")
    private String requestNo;

    /**
     * 用户ID
     */
    @ExcelExport
    private String openid;

    /**
     * 应用ID
     */
    private String appId;

    /**
     * 奖品模板ID
     */
    @ExcelExport(alias = "奖品id")
    private Long awardId;

    /**
     * 奖品模板名称
     */
    private String awardName;

    /**
     * 会员产品id
     */
    private Integer productId;

    /**
     * 会员产品name
     */
    private String productName;

    /**
     * 发放时间
     */
    @ExcelExport(alias = "发放时间")
    private Date sendTime;

    /**
     * 发放形式（1-系统发放、2-手动发放）
     */
    private Integer sendType;

}

存在的不足：导出工具和Class绑定，也就是必须要定义出导出类来对应Excel；

参考：POI导出Excel的三种workbook的区别