java修改Excel文件内容

本地版

背景:将一个excel中第三列的数据提取出中文,并填写到第四列中

参考代码如下:

try {

            //文件路径
            String filePath = "C:\\Users\\admin\\Desktop\\test.xlsx";
            FileInputStream inputStream = new FileInputStream(filePath);
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            Workbook workbook = reader.getWorkbook();
            Sheet sheetAt = workbook.getSheetAt(0);
            //获取总行数
            int lastRowNum = sheetAt.getLastRowNum();
            logger.info("文件总行数:{}", lastRowNum);

            //遍历所有行:从第2列开始,第1列是标题
            for (int i = 1; i < lastRowNum; i++) {

                Row row = sheetAt.getRow(i);
                if (Objects.isNull(row)) continue;

                //获取第3列数据
                Cell cell = row.getCell(2);
                String str = cell.getStringCellValue();
                logger.info("文本内容:{}", str);
                //提取文本的中文
                String str2 = str.replaceAll("[^\u4E00-\u9FA5]", "");
                logger.info("中文内容:{}", str2);
                //修改本列内容
                //cell.setCellValue("设置新的内容");

                if (StringUtils.isNotBlank(str2)) {
                    //写入第四列中
                    Cell add = row.createCell(3);
                    add.setCellValue(str2);
                }
            }

            //将结果输出
            ExcelWriter writer = reader.getWriter();
            
            //将结果输出新文件中
            // writer.flush(new File("C:\\Users\\admin\\Desktop\\结果".xlsx"));
            
            //将结果覆盖到源文件
            writer.flush(new File(filePath));

            inputStream.close();
            reader.close();

        } catch (Exception e) {

            e.printStackTrace();
}

接口版本

demo如下:

    @PostMapping("test")
    public void test(HttpServletResponse response, @RequestParam("file") MultipartFile file) {
        try {

            InputStream inputStream = file.getInputStream();
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            Workbook workbook = reader.getWorkbook();
            Sheet sheetAt = workbook.getSheetAt(0);
            //获取总行数
            int lastRowNum = sheetAt.getLastRowNum();
            logger.info("文件总行数:{}", lastRowNum);

            //遍历所有行:从第2列开始,第1列是标题
            for (int i = 1; i < lastRowNum; i++) {

                Row row = sheetAt.getRow(i);
                if (Objects.isNull(row)) continue;

                //获取第3列数据
                Cell cell = row.getCell(2);
                String str = cell.getStringCellValue();
                logger.info("文本内容:{}", str);
                //提取文本的中文
                String str2 = str.replaceAll("[^\u4E00-\u9FA5]", "");
                logger.info("中文内容:{}", str2);
                //修改本列内容
                //cell.setCellValue("设置新的内容");

                if (StringUtils.isNotBlank(str2)) {
                    //写入第四列中
                    Cell add = row.createCell(3);
                    add.setCellValue(str2);
                }
            }

            //将结果输出
            ExcelWriter writer = reader.getWriter();
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
            out.flush();

            reader.close();
            inputStream.close();
            out.close();

        } catch (Exception e) {

            e.printStackTrace();
        }


    }