Excel读写(PIO)

  /**
     * Excel 写
     */
    @Test
    public void ExcelTest() throws Exception {

        String PATH = "D://";

        //开始时间 毫秒
        long begin = System.currentTimeMillis();
        System.out.println(begin);
        //创建工作簿
//        HSSFWorkbook workbook = new HSSFWorkbook();
        Workbook workbook = new SXSSFWorkbook(); //加速版
        //创建工作表
        Sheet sheet = workbook.createSheet("Excel表格");
        //写入数据    rowNum行数    cellNum 列数
        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 + "Excel测试1.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        //结束时间 毫秒
        long end = System.currentTimeMillis();
        System.out.println((double) (end - begin) / 1000);

    }


    
    /**
     * Excel 读
     */
    @Test
    public void ExcelTest3() throws Exception {

        String PATH = "D:\\";
        //获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "Excel测试1.xlsx");
        //创建工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        //得到表
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行数据(标题)
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            //获取有多少列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int i = 0; i < cellCount; i++) {
                Cell cell = rowTitle.getCell(i);
                if (cell != null) {
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.println(cellValue + " | ");
                }
            }
            System.out.println();
        }
        //获表中内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                //读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int i = 0; i < cellCount; i++) {
                    System.out.println("[" + (i + 1) + "-" + (i + 1) + "]");

                    Cell cell = rowData.getCell(i);
                    //匹配列的数据类型
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";

                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING: //字符串
                                log.info("String");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔值
                                log.info("Boolean");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空
                                log.info("");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数值(日期，普通数值)
                                log.info("Number");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    log.info("日期");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    //不是日期格式，防止数字过长
                                    log.info("转换为字符串输出");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                log.info("数据类型错误");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        inputStream.close();
    }
}