技术博客
首页 所有文章 标签 分类 关于
技术博客

目录

一篇文章教你使用JAVA实现导入导出POI

 收录于 导入导出 Java Excel
   约 3227 字   预计阅读 7 分钟 
https://bing.ee123.net/img/rand?artid=131665714

一篇文章教你使用JAVA实现导入导出（POI）

文章目录

1、场景准备

我们先准备好 我们需要使用的模板、导入的依赖

模板:自己自定义即可 例如

https://i-blog.csdnimg.cn/blog_migrate/cdbc203e3f4391721d6ed2c2aebc00ce.png

依赖

<!-- excel工具 -->
<dependency>
	 <groupId>org.apache.poi</groupId>
	 <artifactId>poi-ooxml</artifactId>
</dependency>

2、代码展示

2.1、实现简单导入导出

  由于考虑到大家宝贵的时间简单的就直接略过了(因为大部分框架都是简单一点的导入导出都已经实现自带)
我们直接来相对复杂一点的业务导出导入

2.2、实现复杂业务导出导入（思路展示）

2.2.1、导出

  由于我们大部分操作都是直接代码实现的所以我们在模板上不需要费太多的功夫,像我上面的模板或者自定义一个模板都行
这次我们复杂的业务导出主要是实现导出时自带下拉框以及三级联动下拉框

思路

1.我们先使用流式操作拿到模板文件以及文件内容
try (Workbook workbook = new XSSFWorkbook(Files.newInputStream(Paths.get(templatePath)))) {

} catch (IOException e) {
    System.out.println("excel文件解析错误")
}
2.拿到excel模板文件后 我们直接进行操作 这里我是用了封装方法 方便后续多次进行相同操作
/**
     * excel单元格变下拉框方法
     *
     * @param array    导出下拉框的数据
     * @param sheet1   引用的sheet
     * @param sheet2   导出的sheet
     * @param workbook 文件
     * @param index    在那一列引用
     * @param name     导出后的excel的下拉命名空间
     * @param value    导出后到sheet2的命名范围
     * @param indexCol 导出后文件赋值到sheet2的那一列
     */
    private static void assignmentSheet(String[] array, Sheet sheet1, Sheet sheet2, Workbook workbook, int index, String name, String value, int indexCol) {
        // 在第二个sheet页中设置下拉框的选项
        for (int i = 0; i < array.length; i++) {
            Row row;
            if (null == sheet2.getRow(i)) {
                row = sheet2.createRow(i);
            } else {
                row = sheet2.getRow(i);
            }
            Cell cell = row.createCell(indexCol);
            cell.setCellValue(array[i]);
        }
        // 检查是否已经存在具有相同名称的命名范围
        boolean isExistingNamedRange = false;
        for (Name existingNamedRange : workbook.getAllNames()) {
            if (existingNamedRange.getNameName().equals(name)) {
                isExistingNamedRange = true;
                break;
            }
        }

        // 如果存在相同名称的命名范围，则删除它
        if (isExistingNamedRange) {
            workbook.removeName(name);
        }

        // 创建新的命名范围
        Name namedRange = workbook.createName();
        namedRange.setNameName(name);
        namedRange.setRefersToFormula("Sheet2!$" + value + "$1:$" + value + "$" + array.length);

        // 循环赋值单元格
        // 在第一个sheet页创建下拉框的选项列表
        DataValidationHelper validationHelper = sheet1.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(3, 52, index, index);
        DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(name);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);
        // 将下拉框的选项应用到指定单元格
        sheet1.addValidationData(validation);
    }
引用方法 参数作用请看方法的@param
        try (Workbook workbook = new XSSFWorkbook(Files.newInputStream(Paths.get(templatePath)))) {
            Sheet sheet1 = workbook.getSheet("Sheet1");
            Sheet sheet2 = workbook.getSheet("Sheet2");
            assignmentSheet(这是是你希望填充到excel序列的数据(格式为数组), sheet1, sheet2, workbook, 6, "rectUnit", "H", 7);
            // 保存工作簿 保存到指定地址后续可使用浏览器方法进行下载
            try (OutputStream os = new FileOutputStream(templatePath)) {
                workbook.write(os);
            }
        } catch (IOException e) {
           System.out.println("excel文件解析错误")
        }
注:如果你自定义的模板没有Sheet1和Sheet2,会直接报错可使用创建create而不是获取get
例如
	workbook.createSheet(这里写你的sheet页的名称);
	如上我们就可以在指定范围第三行到五十三行第六列全部覆盖下拉框

https://i-blog.csdnimg.cn/blog_migrate/b59d96aa132653b1d4b1834b4187f597.png

这是一个简单的案例 
我们现在来实现今天的最终目的 导出后在excel表格中直接实现三级联动
要想实现导出三级联动 我们必须知道在excel中怎么实现三级联动 然后在去以java代码实现excel中的三级联动

思路

1.excel是怎么实现三级联动下拉框的
2.java代码模拟excel实下拉以及思路展示
3.效果展示

在excel中我们需要按一下步骤来实现下拉三级联动

1.定义一、二、三级所有需要的数据

https://i-blog.csdnimg.cn/blog_migrate/c92bf26b2653092c8ae67f38ef54673a.png

这里定义数据我们可以看到我们二级和一级对应的 三级适合二级对应的 这是关键点

2.定义名称管理器

https://i-blog.csdnimg.cn/blog_migrate/b6f93fba720653786ce7d84e28e8a537.png

点击新增

https://i-blog.csdnimg.cn/blog_migrate/4217dc75a00607eea7ae9c43d70227a4.png

这里是重点 然后我们将三级也按同理新增进行即可

3.单元格引用
	一级的我们直接引用所需的列表数据即可
	注意二级引用一级的值(即一级所在的单元格)这里需要用到excel自带的函数INDIRECT
	我们在二级的地方引用一级单元格的内容，例如一级的单元格在C5

https://i-blog.csdnimg.cn/blog_migrate/7d56f0351d925b34ef50aaae9c9e7ff7.png

 	INDIRECT：它用于返回一个由文本字符串指定的单元格引用所对应的单元格的内容
 4.三级同理即可
 5.这些步骤全部搞完后,你就会发现我们已经实现了三级联动效果 只不过数据是写死在excel中的

在java中我们实现excel的三级联动

1.我们先创建一个sheet页来存储我们要放置的三级数据

https://i-blog.csdnimg.cn/blog_migrate/0a6e6ac5fb9286ca4ba45a6bc18950a0.png

2.开始往sheet中填充数据，

这里我们使用的是

3.由于是数据是树形 我们就采用遍历赋值
这里需要注意一点,java中给excel命名管理器赋值名称时有很多禁词,如果你使用了禁词进行命名,将会引发报错，所以我们写一个方法来过滤掉禁词
/**
  * 正则替换
  *
  * @param input 字符串
  * @return 字符串
  */
 public static String replaceDigits(String input) {
     return input.replaceAll("\\d", "").replace("、", "");
 }
4.注意我们除了给单元格循环赋值以外 我们还要一直循环创建命名空间值就是他的子级,名称就是当前元素（一定要过滤掉特殊符号）
5.引用子级填充好的范围作为父级的值(如果是按列进行排序的话,需要转为ASCLL码)
我们用字符串拼接强行替换成excel的范围引用(不同得可以直接去excel得命名管理器中使用一下范围选择 然后看看选择后的值，大家就会心领神会了)
 namedRange.setRefersToFormula("数据源!$" + (char) (abc + 64) + "$2:$" + (char) (abc + 64) + "$" + (array[i].getChildren().size() + 1));

6.（单元格赋值命名范围赋值完整代码[逻辑简单易懂]）完整代码
private void extracted(Workbook workbook, Sheet sheet) {
    // 创建数据源工作表
    Sheet dataSourceSheet = workbook.createSheet("数据源");
    workbook.setSheetHidden(workbook.getSheetIndex(dataSourceSheet),true);
    List<ResoTreeSelect> resoTreeSelects = problemClassification.selectProblemTreeList(new ResoProblemClassification());
    ResoTreeSelect[] array = resoTreeSelects.toArray(new ResoTreeSelect[0]);
    int jj = 0;
    int abc = 1;
    int bdf = 0;
    // 填充一级选项数据
    for (int i = 0; i < array.length; i++) {
        Row row;
        Cell cell;
        if (null == dataSourceSheet.getRow(0)) {
            row = dataSourceSheet.createRow(0);
        } else {
            row = dataSourceSheet.getRow(0);
        }
        cell = row.createCell(i);
        cell.setCellValue(replaceDigits(array[i].getLabel()));
        // 创建一级对应命名管理器
        // 创建新的命名范围
        Name namedRange = workbook.createName();
        // 将中文名称转换为合法的英文标识符
        namedRange.setNameName(replaceDigits(array[i].getLabel()));
        namedRange.setRefersToFormula("数据源!$" + (char) (abc + 64) + "$2:$" + (char) (abc + 64) + "$" + (array[i].getChildren().size() + 1));
        abc++;
        // 遍历二级
        if (array[i].getChildren().size() > 0) {
            // 证明有子级
            for (int i1 = 0; i1 < array[i].getChildren().size(); i1++) {
                if (null == dataSourceSheet.getRow(i1 + 1)) {
                    row = dataSourceSheet.createRow(i1 + 1);
                } else {
                    row = dataSourceSheet.getRow(i1 + 1);
                }
                cell = row.createCell(i);
                cell.setCellValue(replaceDigits(array[i].getChildren().get(i1).getLabel()));

                if (null == dataSourceSheet.getRow(jj + 10)) {
                    row = dataSourceSheet.createRow(jj + 10);
                } else {
                    row = dataSourceSheet.getRow(jj + 10);
                }
                cell = row.createCell(0);
                cell.setCellValue(replaceDigits(array[i].getChildren().get(i1).getLabel()));
                // 创建一二级对应命名管理器
                // 创建新的命名范围
                Name namedRange1 = workbook.createName();
                String name1 = array[i].getChildren().get(i1).getLabel();
                // 将中文名称转换为合法的英文标识符
                namedRange1.setNameName(replaceDigits(name1));
                namedRange1.setRefersToFormula("数据源!$" + (char) 66 + "$" + (bdf + 11) + ":$" + (char) ((array[i].getChildren().get(i1).getChildren().size() + 1) + 64) + "$" + (bdf + 11));
                if (array[i].getChildren().get(i1).getChildren().size() > 0) {
                    // 编辑三级
                    for (int i2 = 0; i2 < array[i].getChildren().get(i1).getChildren().size(); i2++) {
                        cell = row.createCell(i2 + 1);
                        cell.setCellValue(array[i].getChildren().get(i1).getChildren().get(i2).getLabel());
                    }
                }
                bdf++;
                jj++;
            }
        }
    }
}
7.到目前为止我们的单元格赋值和命名管理器新增全部做完我们直接在需要使用的单元格直接引用即可
7.1 给一级赋值 (没什么讲究 赋值之前对应的一级即可)
7.2 赋值二级三级
/**
  *  引用单元格内容 去命名管理器查询对应序列值
  * @param sheet1 单元格所在的sheet页
  * @param index 选择父级后子级的结果出现在那一列
  * @param col 引用的一级或者两级在那一列
  */
 private static void assignmentSheet(Sheet sheet1, int index, String col) {

     // 在第一个sheet页创建下拉框的选项列表
     DataValidationHelper validationHelper = sheet1.getDataValidationHelper();
     CellRangeAddressList addressList = new CellRangeAddressList(3, 52, index, index);
     // 假设您要根据第一列（A列）的值动态更改下拉列表的值
     String formula = "INDIRECT(" + col + "这里填写你是从那一行开始(注意这里excel会智能往下填充不需要遍历))";
     DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
     DataValidation validation = validationHelper.createValidation(constraint, addressList);
     // 将下拉框的选项应用到指定单元格
     sheet1.addValidationData(validation);
 }
8.引用方法

https://i-blog.csdnimg.cn/blog_migrate/3b2804b5772e2d2fe8a5c84d554d61a8.png

9.对excel的操作已经结束保存到本地目录文件夹
// 保存工作簿
try (OutputStream os = new FileOutputStream(templatePath)) {
     workbook.write(os);
 }
10.效果

https://i-blog.csdnimg.cn/blog_migrate/4b09e58edac3798fef163b44edee0d33.png

这样我们就已经实现了复杂业务导出

2.2.2 、导入

导入就比导出简单多了

思路

1.拿到excel文件
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(MultipartFileToFile(file)));
2.定义集合，直接遍历excel每一行拿到每一列的值新增进集合中即可
注：excel默认导入进行会进行数据类型区分 我们直接定死数据类型防止报错
cell.setCellType(CellType.STRING);
3.完整代码
public TableDataInfo importData(MultipartFile file, boolean updateSupport) {
     List<RtProbProblemAccount> list = new ArrayList<RtProbProblemAccount>();
     try {
         XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(MultipartFileToFile(file)));
         // 获取文件中sheet下标为0的sheet页
         Sheet sheet = xssfWorkbook.getSheetAt(0);
         int rowCount = sheet.getLastRowNum() + 1; // 获取行数（注意要加1）
         Row row;
         Cell cell;
         for (int i = 3; i < rowCount; i++) {
             RtProbProblemAccount problemAccount = new RtProbProblemAccount();
             problemAccount.setProblemStatus("1");
             row = sheet.getRow(i);
             // 实例化问题对象
             boolean boo = true;
             for (int j = 0; j < 21; j++) {
                 cell = row.getCell(j);
                 cell.setCellType(CellType.STRING);
                 switch (j) {
                     case 0:
                         if (StringUtils.isEmpty(cell.getStringCellValue())) {
                             boo = false;
                         }
                         problemAccount.setTitle(cell.getStringCellValue());
                         break;
                     case 4:
                         if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                             problemAccount.setOtherProblems(cell.getStringCellValue());
                         }
                         break;
                     case 5:
                         if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                             problemAccount.setDescribe(cell.getStringCellValue());
                         }
                         break;
                     case 7:
                         if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                             problemAccount.setQualitativeBasis(cell.getStringCellValue());
                         }
                         break;
                     case 8:
                         if (StringUtils.isNotEmpty(cell.getStringCellValue())) {
                             problemAccount.setAuditOpinion(cell.getStringCellValue());
                         }
                         break;
                 }
             }
             if (boo) {
                 list.add(problemAccount);
             }
         }
     } catch (Exception e) {
         e.printStackTrace();
 }
4.可以灵活改动代码 产生不一样的效果 例如上面代码 我设置如果第一列为空 我就不加进集合中 如需要持久化 可以在集合全部新增完成后进行批量操作

制作不易 还望点赞 最后希望大家脑袋都不想太死 代码有很多可以变动 优化的地方 有时间的可以试试

更新于 2023-07-12
 开发语言, Java, Excel
返回 | 主页