POI与Excel

先来说说什么是POI

POI

写操作

操作03版的 **.xls (最多有65536行)

HSSFWorkbook

    @Test//65536
    public void testWrite03() throws IOException {
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("03工作统计表");
        //创建一行
        Row row0 = sheet.createRow(0);
        //创建一个单元格
        Cell cell00 = row0.createCell(0);
        cell00.setCellValue("今天的观众");
        Cell cell01 = row0.createCell(1);
        cell01.setCellValue("123233");

        Row row1 = sheet.createRow(1);
        Cell cell10 = row1.createCell(0);
        cell10.setCellValue("时间");
        Cell cell11 = row1.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell11.setCellValue(s);

        //生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(Path + "/03工作统计表.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        System.out.println("over");
    }

• 效果图

操作07版的 **.xlsx

XSSFWorkbook

    @Test
    public void testWrite07() throws IOException {
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("07工作统计表");
        //创建一行
        Row row0 = sheet.createRow(0);
        //创建一个单元格
        Cell cell00 = row0.createCell(0);
        cell00.setCellValue("今天的观众");
        Cell cell01 = row0.createCell(1);
        cell01.setCellValue(13333);

        Row row1 = sheet.createRow(1);
        Cell cell10 = row1.createCell(0);
        cell10.setCellValue("时间");
        Cell cell11 = row1.createCell(1);
        String s = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell11.setCellValue(s);

        //生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(Path + "/07工作统计表.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();

        System.out.println("over");
    }

大数据写入

SXSSFWorkbook

    @Test
    public void  bigWrite07S() throws IOException {
        Random random = new Random();
        long start = DateTime.now().getMillis();
        //创建工作簿
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("03big");

        for (int rowNum = 0; rowNum < 100000; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 20; cellNum++) {
                int i = random.nextInt(100);
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(i);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(Path + "/07Sbig工作簿.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        ((SXSSFWorkbook)workbook).dispose();
        long end = DateTime.now().getMillis();

        System.out.println("over");
        System.out.println((double)(end-start)/1000);

    }

读操作

读取数据类型多种

    @Test   //类型
    public void testCellType() throws IOException {
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream("E:\\JavaProgram\\IdeaJava\\ks_order\\POI_EasyExcel\\07不同数据类型的读取.xlsx");
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Row rowTitle = sheet.getRow(0);
        if (rowTitle!=null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int rowNum = 0; rowNum < cellCount; rowNum++) {
                Cell cell = rowTitle.getCell(rowNum);
                if (cell!=null){
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+" | ");
                }
            }
        }

        //读取表的内容
        int rows = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row!=null){
                int cells = row.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cells; cellNum++) {
                    System.out.print("("+rowNum+","+cellNum+")-->");
                    Cell cell = row.getCell(cellNum);
                    String cellValue = "";
                    //匹配该cell的类型
                    if (cell!=null){
                        int cellType = cell.getCellType();
                        switch (cellType){
                            case Cell.CELL_TYPE_STRING:
                                System.out.print("[string]:");
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                System.out.print("[BOOLEAN]:");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                System.out.print("[BLANK]:");
                                break;
                            case Cell.CELL_TYPE_NUMERIC: //日期,数字
                                System.out.print("[NUMERIC]:");
                                if (HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.print("[Date]:");
                                    Date dateCellValue = cell.getDateCellValue();
                                    String s = new DateTime(dateCellValue).toString("yyyy/MM/dd HH:mm:ss");
                                    cellValue = s;
                                }else{
                                    System.out.print("[Number]:");
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    cellValue = cell.getStringCellValue();
                                }
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                System.out.print("[ERROR]:");
                                break;
                        }
                        System.out.println(cellValue);
                    }

                }
            }
        }

        fileInputStream.close();
    }

效果

计算(公式)

==XSSFFormulaEvaluator ==

    @Test //计算
    public void testEven() throws IOException {
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream("E:\\JavaProgram\\IdeaJava\\ks_order\\POI_EasyExcel\\07.xlsx");
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);

        //得到计算公式
        XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook)workbook);

        //输出单元格的内容
        int cellType = cell.getCellType();
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA:
                String formula = cell.getCellFormula();
                System.out.println(formula);

                //计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String s = evaluate.formatAsString();
                System.out.println(s);
                break;
        }
    }

EasyExcel

写操作

https://www.yuque.com/easyexcel/doc/write

读操作

https://www.yuque.com/easyexcel/doc/read