Apache POI插件Feb 14,2020 最新版使用随手记

最新推荐文章于 2024-09-07 10:33:59 发布

园园的铁粉QAQ

最新推荐文章于 2024-09-07 10:33:59 发布

阅读量697

点赞数 1

分类专栏： Apache POI 文章标签： java poi ooxml excel spring boot

本文链接：https://blog.csdn.net/addice1019/article/details/106607114

版权

Apache POI 专栏收录该内容

1 篇文章

订阅专栏

一、引入插件

        <!-- Apache POI插件 (Feb 14, 2020)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

二、创建一个工作簿

1.绘制Excel(Map<String, List> tableMap) 参数JSON结构

//Map<String, List<EzStdLayersLayerAndFields>> tableMap
{
  "表A": [
    {
      "TYPE": "VARCHAR",
      "FIELDNAME": "TYPE",
      "ALIASNAME": "类型"
    },
    {
      "TYPE": "VARCHAR",
      "FIELDNAME": "NAME",
      "ALIASNAME": "名称"
    },
    {
      "TYPE": "VARCHAR",
      "FIELDNAME": "ALIAS",
      "ALIASNAME": "别名"
    }
  ]
}

2.实现代码

public static void 绘制Excel(Map<String, List<LayerAndFields>> tableMap) {
        //新建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //新建工作表
        for (Map.Entry<String, List<LayerAndFields>> table : tableMap.entrySet()) {
            //设置工作表名
            String sheetName = table.getKey();
            //工作表名称必须去除windows非法字符，用'&' 替代
            Pattern pattern = Pattern.compile("[\\s\\\\/:\\*\\?\\\"<>\\|]");
            Matcher matcher = pattern.matcher(sheetName);

            XSSFSheet sheet = workbook.createSheet(matcher.replaceAll("&"));
            //创建行
            //设置第一行表头
            row = sheet.createRow(0);//创建第一行表头

            //设置表头 样式背景颜色 - 灰色 ,列宽基数：0.72
            XSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            /* 旧版style.setFillPattern(CellStyle.SOLID_FOREGROUND);
               新版style.setFillPattern(FillPatternType.SOLID_FOREGROUND);*/
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell = row.createCell(0, CellType.STRING);
            cell.setCellValue("序号");
            sheet.setColumnWidth(0, (int) ((5 + 0.72) * 256));//5字符
            cell.setCellStyle(style);

            style = workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell = row.createCell(1, CellType.STRING);
            cell.setCellValue("字段名称");
            sheet.setColumnWidth(1, (int) ((25 + 0.72) * 256));//25字符
            cell.setCellStyle(style);

            style = workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell = row.createCell(2, CellType.STRING);
            cell.setCellValue("字段别名");
            sheet.setColumnWidth(2, (int) ((25 + 0.72) * 256));//25字符
            cell.setCellStyle(style);

            //除第一行以外，开始绘制第二行到最后
            int rowNum = 0;
            for (LayerAndFields fieldsInRow : table.getValue()) {

                row = sheet.createRow(++rowNum);
                cell = row.createCell(0, CellType.NUMERIC);
                cell.setCellValue(rowNum);

                cell = row.createCell(1, CellType.STRING);
                cell.setCellValue(fieldsInRow.getFieldName());

                cell = row.createCell(2, CellType.STRING);
                cell.setCellValue(fieldsInRow.getAliasName());

            }
        }

        try {

            FileOutputStream outputStream = new FileOutputStream(new File("src/main/resources/数据清单.xlsx"));
            workbook.write(outputStream);

            workbook.close();//关闭工作簿
            outputStream.close();//关闭流
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

原MySQL数据

在这里插入图片描述

最终Excel结果

在这里插入图片描述

三、总结

注意新旧版本设置格式的区别

			/*旧版CellStyle  新版XSSFCellStyle 或 HSSFCellStyle*/
           XSSFCellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            /* 旧版style.setFillPattern(CellStyle.SOLID_FOREGROUND);
               新版style.setFillPattern(FillPatternType.SOLID_FOREGROUND);*/
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell = row.createCell(0, CellType.STRING);
            cell.setCellValue("序号");
            sheet.setColumnWidth(0, (int) ((5 + 0.72) * 256));//5字符
            cell.setCellStyle(style);

除了Springboot引入poi以外，还要引入poi-ooxml
工作表名称不能包含 / \ : * " < > | ，必须去除windows非法字符，否则报异常

java.lang.IllegalArgumentException: Invalid char (/) found at index (2) in sheet name '非法/字符*名称示例"'