Excel导入、导出

需要导入的依赖

 <!--导入依赖-->
    <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>
        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>

写入数据

public class ExcelWriteTest {
    String PATH = "D:\\workspace_idea\\Test_Nan\\excel_poi";
    @Test
    public void testWrite03() throws IOException {
        //创建一个工作簿03
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("统计表03");
        //创建第一行
        Row row1 = sheet.createRow(0);

        //创建一个单元格(1,1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增");
        //创建一个单元格(1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("20");

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

        //生成一个表（IO）
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试03.xls");
        //输出
        workbook.write(fileOutputStream);

        //关闭流
        fileOutputStream.close();
        System.out.println("测试03.xls 生成完毕！");
    }
    @Test
    public void testWrite07() throws IOException {
        // 创建一个工作簿07
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("统计表07");
        // 创建第一行
        Row row1 = sheet.createRow(0);

        // 创建一个单元格(1,1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日新增");
        // 创建一个单元格(1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("25");

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

        // 生成一个表（IO）
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试07.xlsx");
        // 输出
        workbook.write(fileOutputStream);

        // 关闭流
        fileOutputStream.close();
        System.out.println("测试07.xlsx 生成完毕！");
    }
    @Test
    public void testWrite03MaxData() throws IOException {
        // 开始时间
        long begin = System.currentTimeMillis();
        // 创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建表
        HSSFSheet 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 + "testWrite03MaxData.xls");
        // 输出
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
    @Test
    public void testWrite07MaxData() throws IOException {
        // 开始时间
        long begin = System.currentTimeMillis();
        // 创建一个工作簿
        XSSFWorkbook 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 outputStream = new FileOutputStream(PATH + "_testWrite07MaxData.xls");
        // 输出
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
    @Test
    public void testWrite07MaxDataS() throws IOException {
        // 开始时间
        long begin = System.currentTimeMillis();
        // 创建一个工作簿
        SXSSFWorkbook 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 outputStream = new FileOutputStream(PATH + "_testWrite07MaxDataS.xls");
        // 输出
        workbook.write(outputStream);
        // 释放资源
        outputStream.close();
        // 清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
}

读出数据

public class ExcelReadTest {
    String PATH = "D:\\workspace_idea\\Test_Nan";
    @Test
    public void testRead03() throws Exception {
        // 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "\\world.xlsx");
        // 创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        // 获取标题内容
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 获取最上面的行内容
        XSSFRow rowTitle = sheet.getRow(0);
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        if(rowTitle != null){
            for(int cellNum = 0; cellNum < cellCount; cellNum++){
                Cell cell = rowTitle.getCell(cellNum);
                if(cell != null){
                    int cellType = cell.getCellType();
                    System.out.print(cell.getStringCellValue() + " | ");
                }
            }
            System.out.println();
        }
        // 获取表中信息内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for(int rowNum = 1;rowNum < rowCount ; rowNum++){
            Row rowData = sheet.getRow(rowNum);
            if(rowData != null){
                // 读取列
                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.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(DateUtil.isCellDateFormatted(cell)){
                                    System.out.print("【日期】");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else{
                                    // 防止数字过长
                                    System.out.print("【数字转换为字符串】");
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            // 异常
                            case Cell.CELL_TYPE_ERROR:
                                System.out.print("【ERROR】");
                                break;
                            default:
                                System.out.println("数据类型都不匹配");
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        // 释放资源
        inputStream.close();
    }
}

Excel表

java控制台输出效果：