java poi 处理excel表格数据

目的：获取本地excel表格数据，修改其中的某个值，存入新的excel。

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
            <scope>test</scope>
        </dependency>

 

String filepath = "D:\\software\\t.xlsx";
FileInputStream excelFileInputStream = new FileInputStream(filepath);
XSSFWorkbook workbook = new XSSFWorkbook(excelFileInputStream);//拿到文件转化为javapoi可操纵类型
excelFileInputStream.close();
XSSFSheet sheet = workbook.getSheetAt(0);
// 获取行数
int rows = sheet.getLastRowNum();
// 获取列数
int clos = sheet.getRow(0).getPhysicalNumberOfCells();

XSSFWorkbook work = new XSSFWorkbook();
XSSFSheet she = work.createSheet("tt3");
for (int i = 1; i < rows; i++) {
     XSSFRow row = sheet.getRow(i);//得到行
     // 创建行
     XSSFRow nowRow = she.createRow(i);
     for(int j=0; j<clos; j++){
          XSSFCell cell = row.getCell(j);//得到列
          cell.setCellType(XSSFCell.CELL_TYPE_STRING);
          //System.out.println(cell.getStringCellValue());
          // 创建列
          XSSFCell nowCell = nowRow.createCell(j);
          nowCell.setCellType(XSSFCell.CELL_TYPE_STRING);
          nowCell.setCellValue(cell.getStringCellValue());
      }
      for(int j=0; j<clos; j++){
            XSSFCell cell = nowRow.getCell(j);//得到列
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);
            System.out.print(cell.getStringCellValue()+" ");
      }
      System.out.println();
}
//保存
FileOutputStream excelFileOutPutStream = new FileOutputStream("D:\\software\\tx.xlsx");//写数据到这个路径上
work.write(excelFileOutPutStream);
excelFileOutPutStream.flush();
excelFileOutPutStream.close();
System.out.println("done");

总结：

XSSFWorkbook，HSSFWorkbook每一个对应的版本都不一样，且对应的导出数据都有限制，详情参考：https://www.cnblogs.com/skyislimit/articles/10514719.html。