编码技巧——使用原生Apache poi导出/导入多sheet、设置单元格格式、合并单元格

妖狐艹你老母 2024-04-24 14:42 5阅读 0赞

### 1. 背景 ###

基于springboot的工程项目，需求：

（1）查询到的多组查询结果导出为excel，将每个组的数据集放在一个sheet页；

（2）分组的数量无法预先确定，是根据实时的查询结果得到的；意味着sheet数量不确定；

（3）每一组数据的数据结构不一样，意味着每个sheet的表头列不确定，也是根据实时的查询结果得到的；

### 2. 方案 ###

之前介绍过easypoi对多sheet的导入导出，可参考我之前的文章[编码技巧——使用Easypoi导出Excel、多sheet、模板导出][Easypoi_Excel_sheet]；

但是这次的需求不同，**sheet数量和每个sheet的表头都不确定，无法通过预先定义的导出类+注解的方式调用easypoi的API来便捷导入导出**；

**解决方案**

（1）使用原生Apache poi的API，根据当前实时查询到的分组数据，遍历每组数据来创建对应的sheet；再根据每组数据的属性，来手动的设置表头；

（2）需要对表头设置样式，如加粗字体、设置底色；

（3）需要根据表头的字符长度，自动的设置单元格宽度，方便导出时查阅（无需手动拉宽）；

（4）因为导出时的每个sheet是手动创建的，在设置sheetName时，通过分组标识来匹配对应的sheetName，这么做可以方便导入解析，取出各个分组的数据；

（5）因为每个sheet的表头是手动创建的，并且要求表头顺序、数量在导入时不被篡改，需要提供每个sheet的表头防篡改校验；

### 3. 代码 ###

下面提供每个关键功能模块对应的代码（不提供完整的项目代码）；

注：文中默认的excel格式为xlsx；

#### （1）Excel工具类 ####

包含读取从起始坐标到终点坐标的矩形范围数据到二维数组的方法、excel表格cell数据读取等；

import com.google.common.collect.Lists;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.InputStream;
    import java.util.List;
    
    
    /**
     * @description 基于原生poi的excel工具类 解决easypoi/easyExcel解决不了的问题
     */
    @Slf4j
    public class ExcelReaderUtils {
    
        /**
         * excel文件类型后缀
         */
        public static final String OFFICE_EXCEL_XLSX = ".xlsx";
    
        /**
         * 读取excel行数的最大值，防止OOM
         */
        public static final int MAX_READABLE_LINE_COUNT = 10000;
    
        /**
         * 解析XSSFSheet，从起始行start到结束行end，取出【指定列】column的item放入list返回
         *
         * @param xssfSheet
         * @param column    列数,从0计数
         * @param rowStart  起始行(闭区间),从0计数
         * @param rowEnd    结束行(闭区间),从0计数
         * @return
         */
        public static List<String> getListFrXSSFSheetColumn(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;
        }
    
        /**
         * 解析XSSFSheet，从起始列start到结束列end，取出【指定行】row的item放入list返回
         *
         * @param xssfSheet
         * @param rowNum      行数,从0计数
         * @param columnStart 起始行(闭区间),从0计数
         * @param columnEnd   结束行(闭区间),从0计数
         * @return
         */
        public static List<String> getListFrXSSFSheetRow(XSSFSheet xssfSheet, int rowNum, int columnStart, int columnEnd) {
            List<String> result = Lists.newArrayList();
            try {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                XSSFCell xssfCell;
                for (int column = columnStart; column <= columnEnd; column++) {
                    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;
        }
    
        /**
         * 解析XSSFSheet，从起始行start到结束行end，取出从列columnStart到columnEnd的item放入list返回
         *
         * @param xssfSheet
         * @param columnStart 起始列数(闭区间),从0计数
         * @param columnEnd   截至列数(闭区间),从0计数
         * @param rowStart    起始行(闭区间),从0计数
         * @param rowEnd      结束行(闭区间),从0计数
         * @return
         */
        public static List<List<String>> getListFrXSSFSheet(XSSFSheet xssfSheet, int columnStart, int columnEnd, int rowStart, int rowEnd) {
            List<List<String>> result = Lists.newArrayList();
            try {
                XSSFRow xssfRow;
                for (int row = rowStart; row <= rowEnd; row++) {
                    List<String> dataInRow = Lists.newArrayList();
                    xssfRow = xssfSheet.getRow(row);
                    XSSFCell xssfCell;
                    for (int col = columnStart; col <= columnEnd; col++) {
                        xssfCell = xssfRow.getCell(col);
                        // 都转成String，避免小数精度
                        xssfCell.setCellType(CellType.STRING);
                        dataInRow.add(getCellValueByCell(xssfCell));
                    }
                    result.add(dataInRow);
                }
            } 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();
        }
    }

#### （2）遍历分组创建sheet ####

XSSFWorkbook workbook = new XSSFWorkbook();
            // 遍历分组数据 groupIdDataMap为groupby之后的Map
            if (MapUtils.isNotEmpty(groupIdDataMap)) {
    			groupIdDataMap.forEach((groupId, dataInGroup) -> {
    				// 将分组ID冗余到sheetName中方便导入时的解析
                    XSSFSheet sheet = workbook.createSheet(String.join("-", SHEET_NAME_PREFIX, String.valueOf(groupId)));
    				// 创建表头 第1行（初始下标为0）
    				List<String> headerNames = generateHeaderNames(dataInGroup);
                    buildTitle(workbook, sheet, headers, 1);				
                    // 生成excel数据二维数组
                    List<List<String>> rows = initRows(dataInGroup);
    				// 按行填充数据(第1行放说明、第2行放表头)
    				writeRows(sheet, 2, rows, 0, headers.size() - 1);
    			})
            }
    		return workbook;

#### （3）设置表头格式/自动宽度/边框/合并单元格/背景色 ####

/**
         * 设置sheet的表头 设置表头在第(startRow+1)行 设置表头宽度
         */
        private static void buildTitle(XSSFWorkbook workbook, XSSFSheet sheet, List<String> headers, int startRow) {
            // 第0行内容：固定提示文案 合并单元格（起始坐标和终点坐标）
            CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, headers.size() - 1);
            sheet.addMergedRegion(rangeAddress);
            XSSFRow row0 = sheet.createRow(0);
            XSSFCell cell0 = row0.createCell(0);
            cell0.setCellValue("导入须知：不能在该Excel模板中对表头信息进行增加、删除、修改或位置调整！");
            XSSFCellStyle headerCellStyle0 = workbook.createCellStyle();
            headerCellStyle0.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            headerCellStyle0.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell0.setCellStyle(headerCellStyle0);
    
            // 第1行内容：表头列从第1行开始
            XSSFCellStyle headerCellStyle = workbook.createCellStyle();
            // 字体格式
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            headerCellStyle.setFont(font);
            int columns = headers.size();
            XSSFRow row = sheet.createRow(startRow);
            // 背景色
            headerCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 边框线条宽度
            headerCellStyle.setBorderBottom(BorderStyle.MEDIUM);
            headerCellStyle.setBorderTop(BorderStyle.MEDIUM);
            headerCellStyle.setBorderLeft(BorderStyle.MEDIUM);
            headerCellStyle.setBorderRight(BorderStyle.MEDIUM);
            // 水平/垂直居中
            headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
            headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    
            for (int column = 0; column < columns; column++) {
                String headerName = headers.get(column);
                XSSFCell cell = row.createCell(column);
                XSSFRichTextString text = new XSSFRichTextString(headerName);
                cell.setCellValue(text);
                cell.setCellStyle(headerCellStyle);
                // 设置自动表头列宽度
                int colLength = cell.getStringCellValue().getBytes().length * 192;
                sheet.setColumnWidth(column, colLength);
            }
        }

#### （4）二维数据集写入excel表格 ####

/**
         * 填充数据行,数据可以是不对齐的
         *
         * @apiNote 列序号从0开始 要求 0 <= startColumnIndex <= endColumnIndex < 表头长度-1
         */
        void writeRows(XSSFSheet sheet, Integer startRowIndex, List<List<String>> rows, Integer startColumnIndex, Integer endColumnIndex) {
            if (CollectionUtils.isNotEmpty(rows)) {
    			// 写数据从第startRowIndex行开始
                for (int rownum = startRowIndex; rownum < rows.size(); rownum++) {
                    XSSFRow row = sheet.createRow(rownum);
                    for (int col = startColumnIndex; col <= endColumnIndex; col++) {
                        row.createCell(col).setCellValue(rows.get(rownum).get(col - startColumnIndex));
                    }
                }
            }
        }

#### （5）导入后读出多sheet ####

负责将HTTP请求的MultipartFile类以数据流的方式转换成Excel对应的Java对象；

将excel的多个sheet读出；

/**
         * 获取excel文件流，解析并校验上传文件，返回XSSFSheet
         */
        private List<XSSFSheet> checkExcelFile(MultipartFile excelFile) {
            XSSFWorkbook xssfWorkbook = Optional.ofNullable(ExcelReaderUtils.getXSSFWorkbookFrExcel(excelFile))
                    .orElseThrow(() -> new BizException(ResultCodeEnum.EXCEL_ANALYSIS_ERROR.getCode(), ResultCodeEnum.EXCEL_ANALYSIS_ERROR.getDesc()));
            Iterator<Sheet> sheetIterator = xssfWorkbook.sheetIterator();
            List<XSSFSheet> sheets = Lists.newArrayList();
            while (sheetIterator.hasNext()) {
                XSSFSheet sheet = (XSSFSheet) sheetIterator.next();
                sheets.add(sheet);
            }
            List<String> sheetNames = sheets.stream().map(XSSFSheet::getSheetName).collect(Collectors.toList());
            log.warn("解析上传excel成功 [sheetNum={} sheetNames={}]", sheets.size(), JSON.toJSONString(sheetNames));
            // 数量检测
            sheets.forEach(sheet -> {
                int recordNum = ExcelReaderUtils.getRowCount(sheet);
                log.warn("analyseExcel success. [records count={}]", recordNum);
                if (recordNum > ExcelReaderUtils.MAX_READABLE_LINE_COUNT) {
                    throw new BizException(ResultCodeEnum.EXCHCODE_UPLOAD_SIZE_OVERFLOW.getCode(), ResultCodeEnum.EXCHCODE_UPLOAD_SIZE_OVERFLOW.getDesc());
                }
            });
    
            return sheets;
        }

#### （6）校验表头 ####

复用表头的生成规则，取当前sheet的表头，做一下字符串匹配校验；

private void checkPointImportExcelHeader(Long modelIdInType, List<String> sortedHeaders, List<String> listFrXSSFSheetRow) {
            // 预期的表头 保证表头排序规则固定
            String standard = String.join("-", sortedHeaders);
    		// 当前输入读出来的表头
            String input = String.join("-", listFrXSSFSheetRow);
            if (!StringUtils.equals(standard, input)) {
                log.error("导入的excel表头被篡改 [standard={} input={}]", standard, input);
                throw new BizException("导入的excel表头被篡改 请重新下载模板后再上传!");
            }
        }

#### （7）读取数据 ####

for (XSSFSheet sheet : xssfSheets) {
                // 根据sheetName解析当前sheet页对应哪一个分组数据
                String sheetName = sheet.getSheetName();
                int rowCount = ExcelReaderUtils.getRowCount(sheet);
                String[] split = sheetName.split("-");
                if (split.length < N) {
                    log.error("表格sheet名称解析失败! [sheetName={}]", sheetName);
                    throw new BizException(ResultCodeEnum.EXCEL_ANALYSIS_ERROR.getCode(), ResultCodeEnum.EXCEL_ANALYSIS_ERROR.getDesc());
                }
                // 省略细节...
    			
                log.warn("开始处理sheet【{}】 rowCount={} ]", sheetName, rowCount);
                List<EvaluateModelDataDO> evaUpdateDomainList = Lists.newArrayList();
                List<PointModelDataDO> pointUpdateDomainList = Lists.newArrayList();
                if (EVALUATE_PREFIX.equals(modelType)) {
    				// 取出当前输入sheet的表头
    				List<String> listFrXSSFSheetRow = ExcelReaderUtils.getListFrXSSFSheetRow(sheet, 2, 1, 0, lastColumnIndex);
    				// 根据当前sheetName解析出的分组信息 生成表头 省略
    				List<String> sortedHeaders = generateStandardHeaders(sheetName);
                    // 校验表头是否篡改
                    checkEvaImportExcelHeader(modelIdInType, sortedHeaders, listFrXSSFSheetRow);
                    // 读出数据 矩阵转秩 / 注意：解析规则固定，因此不能删除列，不能修改列顺序；可以修改行顺序和删除列
                    List<List<String>> data = ExcelReaderUtils.getListFrXSSFSheet(sheet, 0, lastColumnIndex, 2, sheet.getLastRowNum());
                    data.forEach(rowData -> {
                        // 考虑：可能误操作在excel新增了不存在的行 跳过
                        ...
                        // 遍历数据处理
    					...
                    });
                }
            }

以上；本文只提供思路和注意的点，不提供完整代码；

[Easypoi_Excel_sheet]: https://blog.csdn.net/minghao0508/article/details/128101457