POI和easyExcel

添加依赖

    <dependencies>
        <!--java 万物皆对象-->
        <!--xls(03)-->
        <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>

        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

测试写入操作

	String PATH = "F:\\lite\\demo\\excel-poi\\";

    @Test
    public void testWrite03() throws IOException {
        // 1、创建一个工作簿 03
        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(666);

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

        //生成一张表 (IO流) 03 版本就是使用 xls
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "My统计表03.xls");

        workbook.write(fileOutputStream);
        // 关闭流
        fileOutputStream.close();

        System.out.println("My统计表03 生成完毕");
    }

    @Test
    public void testWrite07() throws IOException {
        // 1、创建一个工作簿 07
        Workbook workbook = new XSSFWorkbook();
        // 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(666);

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

        //生成一张表 (IO流) 07 版本就是使用xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "My统计表07.xlsx");

        workbook.write(fileOutputStream);
        // 关闭流
        fileOutputStream.close();

        System.out.println("My统计表07 生成完毕");
    }

注意对象的一个区别，文件的后缀！！

大文件写HSSF

	@Test
    public void testWrite03BigData() throws IOException {
        // 开始时间
        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 outputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
        workbook.write(outputStream);
        outputStream.close();
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin)/1000);
    }

大文件写XSSF

	// 耗时较长
    @Test
    public void testWrite07BigData() throws IOException {
        // 开始时间
        long begin = System.currentTimeMillis();
        // 创建一个簿
        Workbook workbook = new XSSFWorkbook();
        // 创建表
        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 outputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin)/1000);
    }

大文件写SXSSF

	@Test
    public void testWrite07BigDataS() throws IOException {
        // 开始时间
        long begin = System.currentTimeMillis();


        // 创建一个簿
        Workbook workbook = new SXSSFWorkbook();
        // 创建表
        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 outputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        // 清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin)/1000);
    }

读取

	@Test
    public void testRead03() throws IOException {

        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH+"My统计表03.xls");

        // 1、创建一个工作簿 ,使用excel 能操作的 他都可以操作
        Workbook workbook = new HSSFWorkbook(inputStream);
        // 2、得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 3、得到行
        Row row = sheet.getRow(0);
        // 4、得到列
        Cell cell = row.getCell(1);

        // getStringCellValue 字符串类型
        // System.out.println(cell.getStringCellValue());
        System.out.println(cell.getNumericCellValue());
        inputStream.close();
    }

    @Test
    public void testRead07() throws IOException {

        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH+"My统计表07.xlsx");

        // 1、创建一个工作簿 ,使用excel 能操作的 他都可以操作
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 2、得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 3、得到行
        Row row = sheet.getRow(0);
        // 4、得到列
        Cell cell = row.getCell(1);

        // getStringCellValue 字符串类型
        // System.out.println(cell.getStringCellValue());
        System.out.println(cell.getNumericCellValue());
        inputStream.close();
    }

判断类型

@Test
    public void testCell() throws IOException {

        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "user.xls");

        // 1、创建一个工作簿 ,使用excel 能操作的 他都可以操作
        Workbook workbook = new HSSFWorkbook(inputStream);
        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 cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
            System.out.println();
        }

        // 获取数据
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 0; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                int cellCount = rowTitle.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 HSSFCell.CELL_TYPE_STRING: // 字符串
                                System.out.println("【String】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // 布尔
                                System.out.println("【BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空
                                System.out.println("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字(日期、普通数字)
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
                                    System.out.println("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyy-MM-dd");
                                } else {
                                    // 不是日期格式，防止数字过长
                                    System.out.println("【转换为字符输出】");
                                    cell.setCellValue(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: // 错误
                                System.out.println("【ERROR】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }

        }
        inputStream.close();
    }