PIO使用实例

一 什么是PIO

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office文档的功能。

PIO是一个java程序操作excel文档的实现类，运用了面向对象的思想，将每个excel文件封装成一个对象，通过这个对象对excel进行读和写的操作。

 

二 使用实例

1） 所需依赖

        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.5</version>
        </dependency>
        <!--PIO工具类-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>

2) 实例1：创建一个新的excel文件

 @Test
    public void test() throws IOException {
            
        OutputStream out = new FileOutputStream("D:\\test.xlsx");
        Workbook wb = new HSSFWorkbook();
        // 创建单元格格式
        CellStyle style =  wb.createCellStyle();
        // 格式设置为时间格式
        style.setDataFormat(wb.createDataFormat().getFormat("yyyy-MM-dd"));
        Sheet sHeet =  wb.createSheet();
        Row row = sHeet.createRow(0);
        Cell cell = row.createCell(0);
        System.out.println(new DateTime().toString("yyyy-MM-dd"));
        cell.setCellValue(new Date());
        // cell.setCellStyle(style);
        row.createCell(1).setCellValue(12.3);
        row.createCell(2).setCellValue(10);
        row.createCell(3).setCellValue(true);

        wb.write(out);
        out.close();
    }

3) 实例二：读取excel文件

    @Test
    public void test1() throws IOException {

        InputStream in = new FileInputStream("D:\\test.xlsx");
        Workbook wb = new HSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(0);

        // 获取的单元格数据格式为Date类型的
        Date date = row.getCell(0).getDateCellValue();
        // 获取的单元格数据格式为数值型
        double intt = row.getCell(1).getNumericCellValue();
        // 获取的单元格数据格式为数值型
        double d = row.getCell(2).getNumericCellValue();
        // 获取的单元格数据格式为Boolean类型
        Boolean bool = row.getCell(3).getBooleanCellValue();

        System.out.println(new DateTime().toString("yyyy-MM-dd")+" :"+intt+" :"+d+" :"+bool);

        in.close();
    }

    // 空行空单元格获取对象时,值为null

4) 实例三:批量向excel写入数据

@Test
    public void test2() throws IOException {
        OutputStream out = new FileOutputStream("D:\\test.xlsx");
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        System.out.println(new DateTime().toString("hh:mm:ss"));
        for (int i = 0;i< 65536;i++){
            Row row = sheet.createRow(i);
            for (int j=0 ; j<10;j++){
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }
        System.out.println(new DateTime().toString("hh:mm:ss"));
        wb.write(out);
        out.close();

    }

5) 实例4:按数据格式读取不同数据.

 @Test
    public void test3() throws IOException {

        InputStream in = new FileInputStream("D:\\test.xlsx");
        Workbook wb = new HSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(0);

        // 获取总行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum=0;rowNum<20;rowNum++){
            Row row1 = sheet.getRow(rowNum);
            // 获取首行总列数
            int cellcount = row1.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum <20;cellNum++){
                Cell cell = row1.getCell(cellNum);
                if (cell!=null){

                    // 获取单元格格式
                    int cellType = cell.getCellType();
                    String cellValue = "";

                    // 根据不同格式进行数据的获取处理
                    switch (cellType){
                        case HSSFCell.CELL_TYPE_STRING:
                            System.out.print("[字符串]");
                            String s = cell.getStringCellValue();
                            cellValue = s;
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            System.out.print("[计算]");
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            System.out.print("[为空]");
                            cellValue = "";
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            System.out.print("[布尔]");
                            Boolean b = cell.getBooleanCellValue();
                            cellValue = b.toString();
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            System.out.print("[数值]");
                            // 判断是否为日期
                            if (HSSFDateUtil.isCellDateFormatted(cell)){
                                System.out.print("日期");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            }else{
                                System.out.print("小数");
                                double cellvalue = cell.getNumericCellValue();
                                cellValue = String.valueOf(cellvalue);
                            }
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            System.out.println("发生错误");
                            break;
                    }
                    System.out.print(cellValue+" | ");
                }
            }
            System.out.println();
        }
        in.close();
    }

三 常用方法

PIO使用详解地址:

https://blog.csdn.net/qq_21137441/article/details/79226171