poi学习

poi是apache读写excel等文档的包

xls(03版)最多存65536行 超出抛异常

xlsx(07版)没有行数限制

添加依赖

 <!-- xls 03版execl-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

<!-- xlsx 07版execl-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

一：写入

execl文件生成

//1.创建工作簿(HSSFWorkbook操作03版xls 若换成XSSFWorkbook其实就是操作07版xlsx)
        Workbook workbook = new HSSFWorkbook();
        //Workbook workbook1 = new XSSFWorkbook();
        //Workbook workbook2 = new SXSSFWorkbook();
    //2.创建工作表
        Sheet sheet = workbook.createSheet("xls统计表");
        //3.创建一行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一个");
        //5.生成一张表(IO流)
        FileOutputStream fileOutputStream = new FileOutputStream("保存exels.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        //用SXSSFWorkbook的话得清除临时文件
        //((SXSSFWorkbook) workbook2).dispose();

第1步中用HSSFWorkbook对象是创建xls

若用XSSFWorkbook对象是创建xlsx 所有操作步骤都一样

HSSFWorkbook XSSFWorkbook区别：
1.HSSFWorkbook生成xls XSSFWorkbook生成xlsx
2.HSSFWorkbook数据不能超过65536行 XSSFWorkbook可以
3.HSSFWorkbook是把数据全部读取到缓存再写入 数据量大的话也很快
   XSSFWorkbook数据量大的话耗时较长 耗内存较大

SXSSFWorkbook是XSSFWorkbook的升级版 写入数据更快 内存更少
它默认把100条放内存 其余数据放临时文件中
最后要清除临时文件

二：读取

  public void testRead03() throws Exception{
        //获取工作流
        FileInputStream fileInputStream = new FileInputStream(PATH + "03xls.xls");
//        1.获取工作簿(HSSFWorkbook操作03版execl)
        Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Workbook workbook1 = new XSSFWorkbook();
//        Workbook workbook2 = new SXSSFWorkbook();
        //2.获取表
        Sheet sheet = workbook.getSheetAt(0);
        //3.获取行列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
        //4.关闭流
        fileInputStream.close();
    }

批量读取

   /**
     * 重点！！（提取excel文档所有数据 可以把这段代码作为工具类 入参文件路径）
     * 
     * @throws Exception
     */
    @Test
    public void testCell03() throws Exception{
        //获取工作流
        FileInputStream fileInputStream = new FileInputStream(PATH + "03xls.xls");
//        1.获取工作簿(HSSFWorkbook操作03版execl)
        Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Workbook workbook1 = new XSSFWorkbook();
//        Workbook workbook2 = new SXSSFWorkbook();
        //2.获取表
        Sheet sheet = workbook.getSheetAt(0);
        //3.获取第一行数据
        Row rowTitle = sheet.getRow(0);
        if(rowTitle!=null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();//获取行中有多少列 要记住
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell!=null){
                    int cellType = cell.getCellType();//获取表中值类型
                    String cellValue = cell.getStringCellValue();
                    System.out.println(cellValue+" | ");
                }
            }
        }
        //4.获取第一行下所有的数据(读取不同类型用string接收)
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData!=null){
                //读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();//获取行中有多少列 要记住
                for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
                    System.out.println("["+(rowNum+1)+"-"+(cellNum+1)+"]");//打印在第几行几列
                    Cell cell = rowData.getCell(cellNum);
                    if (cell!=null){
                        int cellType = cell.getCellType();//获取表中值类型
                        String cellValue="";

                        switch (cellType){
                            case Cell.CELL_TYPE_STRING: //字符串
                                System.out.println("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_BOOLEAN: //布尔类型
                                System.out.println("[Boolean]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK: //空
                                System.out.println("[Blank]");
                                break;
                            case Cell.CELL_TYPE_NUMERIC: //数字(日期、数字)
                                System.out.println("[NUMERIC]");
                                if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    System.out.println("[日期]");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");//date转string
                                }else {
                                    //不是日期格式 防止数字过长 转为字符串输出
                                    System.out.println("[转为字符串输出]");
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    cellValue=cell.toString();
                                }
                                break;
                            case Cell.CELL_TYPE_ERROR: //错误
                                System.out.println("[数据类型错误]");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }

            }
        }
        //5.关闭流
        fileInputStream.close();

    }