Java实现更新excel文件内容

引入依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>

---

代码实现

基本思路

首先获取到需要更新到excel中的数据集合，然后获取到需要更新的excel文件，最后更新到excel中后下载excel。

---

获取excel文件

// 获取文件根目录
String rootPath = System.getProperty("user.dir");
// 获取文件名称
String fileName = rootPath+"/files/Xbar-R控制图.xlsx";
// 读取Excel文档
File finalXlsxFile = new File(modelFile);

---

poi常用方法

• Workbook

经常遇到的有通过上传文件MultipartFile与本地文件File来得到Workbook.

private static Workbook getWorkbook(MultipartFile mFile, File file) throws IOException {
    boolean fileNotExist = (file == null || !file.exists());
    if (mFile == null && fileNotExist) {
        return null;
    }
    // 解析表格数据
    InputStream in;
    String fileName;
    if (mFile != null) {
        // 上传文件解析
        in = mFile.getInputStream();
        fileName = getString(mFile.getOriginalFilename()).toLowerCase();
    } else {
        // 本地文件解析
        in = new FileInputStream(file);
        fileName = file.getName().toLowerCase();
    }
    Workbook book;
    if (fileName.endsWith(XLSX)) {
        book = new XSSFWorkbook(in);
    } else if (fileName.endsWith(XLS)) {
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
        book = new HSSFWorkbook(poifsFileSystem);
    } else {
        return null;
    }
    in.close();
    return book;
}

• Sheet

通过book.getSheetAt()来得到excel中的每一个sheet. 

for (int i = 0; i < book.getNumberOfSheets(); i++) {
    Sheet sheet = book.getSheetAt(i);
}

• Row与Cell

常用方法如下

// 首行下标
int rowStart = sheet.getFirstRowNum();
// 尾行下标
int rowEnd = sheet.getLastRowNum();
// 获取表头行
Row headRow = sheet.getRow(rowStart);
// 获取第x行
Row dataRow = sheet.getRow(x-1);


// 表头首列下标
int cellStart = headRow.getFirstCellNum();
// 表头尾列下标
int cellEnd = headRow.getLastCellNum();
// 获取表头第y列
Cell headCell = headRow.getCell(y-1);
// 获取第x行第y列
Cell dataCell = sheet.getRow(x-1).getCell(y-1);


// cell赋值
dataCell.setCellValue(123);

---

excel更新时公式不能更新

// 重新计算公式
workBook.setForceFormulaRecalculation(true);

---

 完整示例

示例如下

public static void updateResExcelEx(List<List<List<Float>>> excelList,
                                    String modelFile, String exportName, String userAgent,
                                    HttpServletResponse res) {
    OutputStream out = null;
    try {
        // 读取Excel文档
        File finalXlsxFile = new File(modelFile);
        Workbook workBook = getWorkbok(null, finalXlsxFile);
        int i = 0;
        for (List<List<Float>> varList : excelList) {
            Sheet sheet = workBook.getSheetAt(i);
            // 业务代码省略
        }
        // 重新计算公式
        workBook.setForceFormulaRecalculation(true);
        res.setCharacterEncoding("UTF-8");
        res.setHeader("content-type", "application/octet-stream; charset=utf-8");

        String dname="";
        try {
            if(userAgent.contains("MSIE")||userAgent.contains("Trident")) {//针对IE或IE为内核的浏览器
                dname=java.net.URLEncoder.encode(exportName,"UTF-8");
            }else {
                dname=new String(exportName.getBytes("UTF-8"),"ISO-8859-1");//谷歌控制版本
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        res.setHeader("Content-Disposition", "attachment;filename="+dname+".xlsx");
        out = res.getOutputStream();

        workBook.write(out);
        System.out.println("数据导出成功");
    } catch (Exception e) {
        System.out.println("updateResExcelEx 异常");
        e.printStackTrace();
    } finally{
        try {
            if(out != null){
                out.flush();
                out.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

---