EasyExcel

github地址: https://github.com/alibaba/easyexcel




07版本

    <dependencies>
        <!--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>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

代码

public class ExcelWriteTest {
    String path = "E:\\workspaces-idea\\EasyExcel\\";
    @Test
    public void test07() throws Exception {
        //创建excel 07版本 工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建工作表 sheet页
        Sheet sheet = workbook.createSheet("第一个sheet页");
        //创建 第一行
        Row row1 = sheet.createRow(0);
        //创建第一行 第一列(1,1 第一个)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一列:A1");
        // 1,2
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("第一行第二列:B1");
        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        Cell cell22 = row2.createCell(1);
        String date = new DateTime().toString("yyyy-MM-DD HH:mm:ss");
        cell22.setCellValue(date);
        //输出
        FileOutputStream fileOutputStream = new FileOutputStream(path + "loong的第一个表格03.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("07.xlsx 输出完毕");
    }
}

大文件写入

    @Test
    public void test07BigData() throws IOException {
        //时间
        Long start = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        //超过65536 异常
        //java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
        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("创建结束!");
        FileOutputStream fileOutputStream = new FileOutputStream(path + "BigData03.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        Long end = System.currentTimeMillis();
        System.out.println((end-start)/1000);
    }

读取excel

03版

String path = "E:\\workspaces-idea\\EasyExcel\\";
    @Test
    public void testRead03() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path + "loong的第一个表格03.xls");

        //创建excel 03版本 工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        //拿到sheet  通过下标  第0个
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(1);

        //输出 拿到的第一个格子
        System.out.println(cell.getStringCellValue());
        inputStream.close();
    }

07版本

 @Test
    public void testRead07() throws Exception {
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path + "loong的第一个表格07.xlsx");

        //创建excel 03版本 工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        //拿到sheet  通过下标  第0个
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(1);

        //输出 拿到的第一个格子
        System.out.println(cell.getStringCellValue());
        inputStream.close();
    }