Apache POI操作xlsx

一、读取xlsx文件

1、pom.xml

<dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
</dependencies>

2、测试类

public class PoiDemo {
    @Test
    public void exportExcel()throws Exception{
        //获取类路径下文件的文件流
        InputStream is = Class.forName("com.mytest.PoiDemo").getClassLoader().getResourceAsStream("poi.xlsx");
        //创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        //获取工作表，即可以根据工作表的顺序获取，也可以根据工作表的名称获取
        XSSFSheet sheet = workbook.getSheetAt(0);

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        //遍历工作表获的行对象
        for (Row row : sheet) {
            //遍历行对象获取单元格对象
            for (Cell cell : row) {
                //判断是否为数字
                if (cell.getCellType()== XSSFCell.CELL_TYPE_NUMERIC){
                    //判断是否为日期
                    if (HSSFDateUtil.isCellDateFormatted(cell)){
                        double dateDouble = cell.getNumericCellValue();
                        //获取Date对象
                        Date javaDate = HSSFDateUtil.getJavaDate(dateDouble);
                        //转为字符串格式日期
                        String formatStr = dateFormat.format(javaDate);
                        System.out.print(formatStr+" ");
                    }else {
                        double numericCellValue = cell.getNumericCellValue();
                        System.out.print(numericCellValue+" ");
                    }
                    //判断是否为布尔值
                } else if(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN){
                    System.out.print(cell.getBooleanCellValue()+" ");
                    //判断是否为公式
                }else if (cell.getCellType()==Cell.CELL_TYPE_FORMULA){
                    System.out.println(cell.getCellFormula().toString());
                }else{
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue+" ");
                }

            }
            System.out.println();
        }
        workbook.close();
        is.close();
    }
}