poi技术操作Excel

操作excel一般适用poi 或者阿里巴巴的EseyExcel

 <dependencies>

        <!--xls-03-->
        <!--03版excel只能放65536行数据-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

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

 		<!--时间格式-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>

public class ExcelWrite {

    String PATH = "E:\\rayc-location\\SpringBoot\\springboot01\\poi";


    /**
     * 生成03  .xls文件
     * @throws Exception
     */
    @Test
    public void testWrite03() throws Exception {
//        1、创建一个工作薄
        Workbook workbook = new HSSFWorkbook();
//        2、创建一个工作表sheet1/sheet2/sheet3
        Sheet sheet = workbook.createSheet("统计表");
//        3、创建一个行
        Row row1 = sheet.createRow(0);

//        4、创建一个单元格（1,1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增观众");
//        （1,2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("666");

        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
//        （2,1）
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:sss");
        cell22.setCellValue(time);

//        生成一张表（IO） 03版本.xls
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/统计表.xls");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("文件输出完毕");
    }

    /**
     * 生成07  .xlsx文件
     * @throws Exception
     */
    @Test
    public void testWrite07() throws Exception {
//        1、创建一个工作薄
        Workbook workbook = new XSSFWorkbook();
//        2、创建一个工作表sheet1/sheet2/sheet3
        Sheet sheet = workbook.createSheet("统计表");
//        3、创建一个行
        Row row1 = sheet.createRow(0);

//        4、创建一个单元格（1,1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增观众");
//        （1,2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("666");

        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
//        （2,1）
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:sss");
        cell22.setCellValue(time);

//        生成一张表（IO） 03版本.xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/统计表07.xlsx");
        //输出
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("文件输出完毕");
    }


}

///大数据导出//

public class WriteBigData {
    String PATH = "E:\\rayc-location\\SpringBoot\\springboot01\\poi";

    /**
     * 03大数据导出  只能65536行  速度较快 ，多了抛异常  2S
     */
    @Test
    public void write03BigData() throws IOException {
        long begin = System.currentTimeMillis();
        //创建工作薄
        Workbook workbook = new HSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet();
        for (int i = 0; i < 65536 ; i++) {
            Row row = sheet.createRow(i);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/write03BigData.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000+"：耗时");
    }


    /**
     * 07大数据导出  可大于65536行  速度较很慢  11S
     */
    @Test
    public void write07BigData() throws IOException {
        long begin = System.currentTimeMillis();
        //创建工作薄
        Workbook workbook = new XSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet();
        for (int i = 0; i < 100000 ; i++) {
            Row row = sheet.createRow(i);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/write07BigData.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000+"：耗时");
    }


    /**
     * 07大数据升级版导出  可大于65536行  速度较快  7S
     * 生成临时文件存放在内存中，可以删除
     */
    @Test
    public void write07BigDataS() throws IOException {
        long begin = System.currentTimeMillis();
        //创建工作薄
        Workbook workbook = new SXSSFWorkbook();
        //创建工作表
        Sheet sheet = workbook.createSheet();
        for (int i = 0; i < 100000 ; i++) {
            Row row = sheet.createRow(i);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/write07BigDataS2.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000+"：耗时");
    }


}

excel文件读取/

public class ExcelRead {

    String PATH = "E:\\rayc-location\\SpringBoot\\springboot01\\poi";


    /**
     * 读取03  .xls文件
     */
    @Test
    public void testRead03() throws Exception {
        //获取输入流
        FileInputStream fileInputStream = new FileInputStream(PATH + "/统计表.xls");
        //创建工作薄
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //得到工作表
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row row = sheet.getRow(0);
        //得到列
        Cell cell = row.getCell(1);
        //得到（1,1）单元格
        //String
//        System.out.println(cell.getStringCellValue());
        //String
        System.out.println(cell.getNumericCellValue());
        fileInputStream.close();
    }


    /**
     * 按类型读取
     * 读取03  .xls文件
     */
    @Test
    public void cellTypeRead03() throws Exception {
        //获取输入流
        FileInputStream fileInputStream = new FileInputStream(PATH + "/统计表.xls");
        //创建工作薄
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //得到工作表
        Sheet sheet = workbook.getSheetAt(0);

        //得到 第一行，通常是名称列表
        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 stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue+" | ");
                }
            }
            System.out.println();
        }

        //得到表中内容 第二行开始
        //一个多少行
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row rowData  = sheet.getRow(rowNum);
            if(rowData != null){
                //读取列
                int cellCount = rowData.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.print("["+(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://BOOLEAN
                                System.out.println("BOOLEAN");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK://NULL
                                System.out.println("NULL");
                                break;
                            case Cell.CELL_TYPE_NUMERIC://数字（日期、普通数字）
                                if(HSSFDateUtil.isCellDateFormatted(cell)){//日期
                                    System.out.println("NUMERIC-日期");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else{
                                    System.out.println("NUMERIC-数字");
                                    //转正字符串输出，不是日期防止数字过长
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case Cell.CELL_TYPE_ERROR://ERROR
                                System.out.println("ERROR");
                                break;
                        }
                        System.out.println(cellValue+"--");
                    }


                }
                System.out.println();
            }
        }
        fileInputStream.close();
    }



}

公式计算