Java写Excel ，各种版本

一、Java操作写入普通的03和07版本的Excel

 String  PATH="/公共路径/";
    @Test
    public void testWrite03() throws Exception {
        //1,创建一个工作簿
        Workbook workbook=new HSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet=workbook.createSheet("普通的统计表");
        //3,创建一个行(1.1)
        Row row1=sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一列标题");
        //(1.2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("12");
        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列标题时间");

        Cell cell22 = row2.createCell(1);
        String times = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(times);

        //生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"普通03的统计表03.xls");
        //输出
        workbook.write(fileOutputStream);
        //关闭留
        fileOutputStream.close();
        System.out.println("生成成功");
    }
    @Test
    public void testWrite07() throws Exception {
        //1,创建一个工作簿
        Workbook workbook=new XSSFWorkbook();
        //2.创建一个工作表
        Sheet sheet=workbook.createSheet("普通07统计表");
        //3,创建一个行(1.1)
        Row row1=sheet.createRow(0);
        //4.创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一列标题");
        //(1.2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("12");
        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列标题时间");

        Cell cell22 = row2.createCell(1);
        String times = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(times);

        //生成一张表(IO流)
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"普通07版本的统计表07.xlsx");
        //输出
        workbook.write(fileOutputStream);
        //关闭留
        fileOutputStream.close();
        System.out.println("生成成功");
    }

二、关于03和07版本的大数据写入并比较时间

 String  PATH="/公共路径/";
@Test
    public void  testWrite03BigData() throws Exception {
        //用了多少时间
        long begin=System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一张表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum=0;rowNum<65536;rowNum++){
            Row row = sheet.createRow(rowNum);
            for (int cellNum=0;cellNum<10;cellNum++){
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"testWrite03BigData大数据03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
    //耗时较长，优化
    @Test
    public void  testWrite07BigData() throws Exception {
        //耗时较长
        //用了多少时间
        long begin=System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建一张表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum=0;rowNum<65537;rowNum++){
            Row row = sheet.createRow(rowNum);
            for (int cellNum=0;cellNum<10;cellNum++){
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"testWrite07BigData大数据07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

三、07版本升级版的写入（SXSSFWorkbook）

 String  PATH="/公共路径/";
 @Test
    public void  testWrite07BigDataS() throws Exception {
        //耗时较长
        //用了多少时间
        long begin=System.currentTimeMillis();
        //创建一个工作簿
        Workbook workbook = new SXSSFWorkbook();
        //创建一张表
        Sheet sheet = workbook.createSheet();
        //写入数据
        for (int rowNum=0;rowNum<65537;rowNum++){
            Row row = sheet.createRow(rowNum);
            for (int cellNum=0;cellNum<10;cellNum++){
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream = new FileOutputStream(PATH+"testWrite07BigDataS大数据07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

用到的pom文件

<!--        xls(03)-->
      <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
<!--                xls(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>
        <!--  单元测试  test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>