编码技巧——导出工具类

忘是亡心i 2023-09-29 08:46 9阅读 0赞

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

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

#### 1. 依赖 ####

<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. 结构 ####

![015dd6d00081473ab0acbdc01de7fcca.png][]

支持多种数据集导出，包括.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教程™][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_ -]

因此，仅对于导出数据存储上，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的区别][POI_Excel_workbook]

[015dd6d00081473ab0acbdc01de7fcca.png]: https://img-blog.csdnimg.cn/015dd6d00081473ab0acbdc01de7fcca.png
[POI_ -POI]: https://www.yiibai.com/apache_poi/apache_poi_core_classes.html
[csv_xlsx_ -]: https://www.zhihu.com/question/378183873
[POI_Excel_workbook]: https://www.cnblogs.com/kiko2014551511/p/11531676.html