使用POI进行Excel表格的基本导入导出

使用POI进行Excel表格的基本导入导出

1. 导包，采用maven添加依赖

	<!-- poi支持的jar包 -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.11</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.11</version>
    </dependency>

2. 导出为Execl

	@Test
    public void testWrite() throws Exception {
        //创建工作簿，SXSSFWorkbook支持大数据量的导出
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        //创建sheet表
        Sheet sheet = workbook.createSheet("99乘法表");
        for (int i = 1; i <= 9; i++) {
            //创建行
            Row row = sheet.createRow(i - 1);
            for (int j = 1; j <= i; j++) {
                Cell cell = row.createCell(j - 1);//创建单元格
                cell.setCellValue(j + "*" + i + "=" + (i * j));
            }
        }
        FileOutputStream os = new FileOutputStream("乘法表.xlsx");
        BufferedOutputStream bos = new BufferedOutputStream(os);
        workbook.write(bos);
        bos.flush();
        bos.close();
        os.close();
    }

3. 读取Excel

	@Test
    public void testRead() throws Exception {
        FileInputStream in = new FileInputStream("emp.xlsx");
        BufferedInputStream bis = new BufferedInputStream(in);
        //创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(bis);//注意07的版本使用这个
        //按索引读取sheet表
        XSSFSheet sheet = workbook.getSheetAt(0);
        //取得所有的行数
        int rowNum = sheet.getLastRowNum();
        for (int i = 2; i <= rowNum; i++) {
            //读取1行
            XSSFRow row = sheet.getRow(i);
            //读取每行的列数
            short cellNum = row.getLastCellNum();
            for (int j = 0; j < cellNum; j++) {
                //读取每个单元格
                XSSFCell cell = row.getCell(j);
                String value = getCellValue(cell);
                System.out.print(value + "\t\t\t");
            }
            System.out.println();
        }
    }

    private String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                // 如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(cell.getDateCellValue());
                } else {
                    //有小数保留两位，位数不够自动忽略
                    return new DecimalFormat("#.##").format(cell.getNumericCellValue());
                }
            case Cell.CELL_TYPE_STRING: // 字符串
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                return cell.getBooleanCellValue() + "";
            case Cell.CELL_TYPE_FORMULA: // 公式
                return cell.getCellFormula() + "";
            case Cell.CELL_TYPE_BLANK: // 空值
                return "";
            case Cell.CELL_TYPE_ERROR: // 故障
                return "非法字符";
            default:
                return "未知类型";
        }
    }