自定义表格模板数据的导入导出

• 业务场景

自定义表格模板

• 环境准备

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>

POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 

• 主要方法

//即获取工作表中所有的合并单元格的数量。mergedRegions表示合并的区域，numMergedRegions表示合并的区域的数量。
int numMergedRegions = sheet.getNumMergedRegions();

//工作表中获取第j个合并区域的单元格范围
CellRangeAddress range = sheet.getMergedRegion(j);

//断给定的行索引和列索引是否在指定的单元格范围内
Boolean flag = range.isInRange(rowIndex, columnIndex)

//获取合并单元格范围中第一个单元格的字符串值
int rowFirst = range.getFirstRow();
int colFirst = range.getFirstColumn();
sheet.getRow(rowFirst).getCell(colFirst).getStringCellValue()

//创建一个模板导出参数对象，并指定导出的文件路径，并设置为使用模板导出
TemplateExportParams template = new TemplateExportParams(path, true);

//模板数据的插入（map必须是<String,Object>，模板中用{{}}来引用map数据）
HashMap<String, Object> map = new HashMap<>();
map.put("tableName", "表名");
map.put("tableType", "表类型");
map.put("description", "表描述");
Workbook workbook = ExcelExportUtil.exportExcel(template, map);

• 接收函数

public void importData(@PathVariable("path") String path) {
    // 解析文件路径，获取文件后缀名
    String[] split = path.split(".");
    String suffix = split[split.length - 1];
    // 根据后缀名创建对应的Workbook对象
    try(InputStream is = new FileInputStream(path);){
        Workbook wb = null;
        if ("xls".equals(suffix)) {
            wb = new XSSFWorkbook(is);
        }else {
            wb = new HSSFWorkbook(is);
        }
        // 解析Excel文件内容
        // 获取表头内容
        List<List<JSONObject>> list = MergeExcelUtil.readMergeExcel(wb, 0, 1, 6);
        //合并单元格按左上角第一个单元格取值
        //表类型
        String type = list.get(0).get(1).getString("value");
        //描述
        String description = list.get(0).get(4).getString("value");
        //表名
        String tableName = list.get(1).get(2).getString("value");
        //...
        //分录数据
        ExcelUtil<TableModel> util = new ExcelUtil<TableModel>(TableModel.class);
        // 获取excel数据
        List<TableModel> excelList = util.importExcel(is, 6);
        // 处理数据等操作
        //...
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

• 获取合并单元格数据方法（readMergeExcel）

public static List<List<JSONObject>> readMergeExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
    //返回对象
    List<List<JSONObject>> results = new ArrayList<>();
    //读取sheet
    Sheet sheet = wb.getSheetAt(sheetIndex);
    //获取所有合并单元格数量
    int numMergedRegions = sheet.getNumMergedRegions();
    //存放已经保存过的合并单元格
    ArrayList<Integer> mergeCell = new ArrayList<>();
    //读取行
    for (int i = startReadLine; i <= tailLine; i++) {
        Row row = sheet.getRow(i);
        //创建一个list集合，用于存放每一行的数据
        List<JSONObject> rowData = new ArrayList<>();
        //遍历所有单元格
        for (Cell cell:row) {
            //判断是否是合并单元格
            if(isMergedRegion(sheet,i,cell.getColumnIndex(),numMergedRegions)){
                //获取合并单元格的值
                JSONObject cellData = getMergedRegionJsonValue(sheet, i, cell.getColumnIndex(), numMergedRegions, mergeCell);
                rowData.add(cellData);
            } else {
                JSONObject cellData = new JSONObject();
                cellData.put("value", getCellValue(cell));
                rowData.add(cellData);
            }
        }

    }
   return results;
}

private static JSONObject getMergedRegionJsonValue(Sheet sheet, int rowIndex, int columnIndex,int numMergedRegions,List<Integer> mergeCell) {
    for (int j = 0; j < numMergedRegions; j++) {
        //获取合并单元格
        CellRangeAddress range = sheet.getMergedRegion(j);
        //判断当前单元格是否在合并单元格中
        if (range.isInRange(rowIndex, columnIndex)) {
            //判断该合并单元格的值是否已经存在
            if(mergeCell.contains(j))
                continue;
            //获取合并单元格的起始行和起始列
            int rowFirst = range.getFirstRow();
            int colFirst = range.getFirstColumn();
            //添加该数据到list集合中
            JSONObject cellData = new JSONObject();
            cellData.put("value", getCellValue(sheet.getRow(rowFirst).getCell(colFirst)));
            mergeCell.add(j);
            return cellData;
        }
    }
    return null;
}

public static String getCellValue(Cell cell) {
    if (cell == null) {return "";}
    else if (cell.getCellType() == CellType.STRING) {      //字符类型
        return cell.getStringCellValue();
    }else if (cell.getCellType() == CellType.BOOLEAN) {    //布尔类型
        return String.valueOf(cell.getBooleanCellValue());
    }else if (cell.getCellType() ==  CellType.FORMULA) {    //计算公式类型
        return cell.getCellFormula();
    }else if (cell.getCellType() ==  CellType.NUMERIC) {    //数字类型
        return String.valueOf(cell.getNumericCellValue());
    }else {
        return "";
    }
}

• 判断是否是合并单元格方法（isMergedRegion）

private static boolean isMergedRegion(Sheet sheet, int rowIndex, int columnIndex,int numMergedRegions) {
    for (int j = 0; j < numMergedRegions; j++) {
        CellRangeAddress range = sheet.getMergedRegion(j);
        if (range.isInRange(rowIndex,columnIndex)) {
            return true;
        }
    }
    return false;
}

• 按照模板导出

准备好模板（我使用的easypoi模板导出功能，模板中{{}}中的字段，与代码中传入的map的key对应）

    public void export(HttpServletResponse response, Map<String,Object> data) {
    //准备数据
    data.put("tableName", "表名");
    data.put("tableType", "表类型");
    data.put("description", "表描述");
    data.put("tableNameC", "表中文名");
    data.put("key","主键" );
    data.put("index","索引");
    // 获取模板文件
    TemplateExportParams template = new TemplateExportParams("static/excelTemplate/DataRule2.xlsx", true);
    // 获取workbook对象，这样就完成了表头数据的插入
    Workbook workbook = ExcelExportUtil.exportExcel(template, data);
    // 获取sheet对象,再对sheet进行操作
    Sheet sheet = workbook.getSheetAt(0);

    //将分录添加进去
    //准备数据
    List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
    for(int i=0;i<10;i++){
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("attribute", "属性名"+i);
        map.put("colName", "列名"+i);
        map.put("type", "类型"+i);
        map.put("isNull", "空否"+i);
        map.put("default", "默认"+i);
        map.put("note", "备注"+i);
        list.add(map);
    }
    //第七行开始是分录
    int j = 6;
    //循环插入分录
    for(Map<String,Object> child:list){
        Row tableRow = sheet.createRow(j);
        Cell tableCell1 = tableRow.createCell(1);
        Cell tableCell2 = tableRow.createCell(2);
        Cell tableCell3 = tableRow.createCell(3);
        Cell tableCell4 = tableRow.createCell(4);
        Cell tableCell5 = tableRow.createCell(5);
        Cell tableCell6 = tableRow.createCell(6);
        tableCell1.setCellValue(child.get("attribute").toString());
        tableCell2.setCellValue(child.get("colName").toString());
        tableCell3.setCellValue(child.get("type").toString());
        tableCell4.setCellValue(child.get("isNull").toString());
        tableCell5.setCellValue(child.get("default").toString());
        tableCell6.setCellValue(child.get("note").toString());
    }
    // 设置文件名
    String excelName = "DataRule";
    // 重置响应对象
    response.reset();
    // 当前日期，用于导出文件名称
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd hh:mm:ss");
    String dateStr = "[" + excelName + "-" + sdf.format(new Date()) + "]";
    // 指定下载的文件名--设置响应头
    response.setHeader("Content-Disposition", "attachment;filename=" + dateStr + ".xls");
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    response.setHeader("Pragma", "no-cache");
    response.setHeader("Cache-Control", "no-cache");
    response.setDateHeader("Expires", 0);
    try (OutputStream out = response.getOutputStream();) {
        workbook.write(out);
        out.flush();
        workbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

在使用EasyPoi进行模板导出时，一般需要以下步骤：

1. 创建一个模板Excel文件，可以在模板文件中定义Excel的格式、样式、表头等内容。

2. 使用EasyPoi提供的工具类，如TemplateExportUtil，来进行导出操作。可以设置导出参数，如模板文件路径、数据源等。

3. 将数据源中的数据填充到模板文件中对应的位置，并生成新的Excel文件。