将Oracle数据库中的数据写入Excel

将Oracle数据库中的数据写入Excel

1.准备工作

　　Oracle数据库“TBYZB_FIELD_PRESSURE”表中数据如图：

 

　　Excel模板(201512.xls)：

 

2.任务说明

　　我们要完成的任务就是将表“TBYZB_FIELD_PRESSURE”中的数据，按照Excel模板(201512.xls)的样式导入到一个新的Excel中。即：Excel模板(201512.xls)不改变，生成一个和它一样的Excel并且导入数据。

 

3.关键代码

// 使用FieldPressEntity中的每一个entity，一个entity包含了所有属性
    public void insertintoExcel(String yyyy, String mm) throws Exception {
        List<FieldPressEntity> result = tyFieldPressDao.search(yyyy, mm);
        // 读取Excel的模板
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File(
                "D:/201512.xls")));
        HSSFSheet sheet = null;
        // 读取sheet的模板
        sheet = workbook.getSheetAt(0);
        // 定义行
        HSSFRow row;
        // 定义单元格
        HSSFCell cell;
        // for循环，循环目标为行循环
        for (int i = 0; i < result.size(); i++) {
            System.out.println(result.size());
            // 给e循环赋值
            FieldPressEntity e = result.get(i);
            // 循环行
            row = sheet.getRow(3 + i);
            // 给行内的单元格赋值
            cell = row.getCell(1);
            cell.setCellValue(e.getH17());
            System.out.println(i + "i内+" + e.getH17());
            cell = row.getCell(2);
            cell.setCellValue(e.getH18());
            System.out.println(i + "i内+" + e.getH18());
            cell = row.getCell(3);
            cell.setCellValue(e.getH19());
            System.out.println(i + "i内+" + e.getH19());
            cell = row.getCell(4);
            cell.setCellValue(e.getH20());
            System.out.println(i + "i内+" + e.getH20());
            cell = row.getCell(5);
            cell.setCellValue(e.getH21());
            System.out.println(i + "i内+" + e.getH21());
            cell = row.getCell(6);
            cell.setCellValue(e.getH22());
            System.out.println(i + "i内+" + e.getH22());
            cell = row.getCell(7);
            cell.setCellValue(e.getH23());
            System.out.println(i + "i内+" + e.getH23());
            cell = row.getCell(8);
            cell.setCellValue(e.getH00());
            System.out.println(i + "i内+" + e.getH00());
            cell = row.getCell(9);
            cell.setCellValue(e.getH01());
            System.out.println(i + "i内+" + e.getH01());
            cell = row.getCell(10);
            cell.setCellValue(e.getH02());
            System.out.println(i + "i内+" + e.getH02());
            cell = row.getCell(11);
            cell.setCellValue(e.getH03());
            System.out.println(i + "i内+" + e.getH03());
            cell = row.getCell(12);
            cell.setCellValue(e.getH04());
            System.out.println(i + "i内+" + e.getH04());
            cell = row.getCell(13);
            cell.setCellValue(e.getH05());
            System.out.println(i + "i内+" + e.getH05());
            cell = row.getCell(14);
            cell.setCellValue(e.getH06());
            System.out.println(i + "i内+" + e.getH06());
            cell = row.getCell(15);
            cell.setCellValue(e.getH07());
            System.out.println(i + "i内+" + e.getH07());
            cell = row.getCell(16);
            cell.setCellValue(e.getH08());
            System.out.println(i + "i内+" + e.getH08());
            cell = row.getCell(17);
            cell.setCellValue(e.getH09());
            System.out.println(i + "i内+" + e.getH09());
            cell = row.getCell(18);
            cell.setCellValue(e.getH10());
            System.out.println(i + "i内+" + e.getH10());
            cell = row.getCell(19);
            cell.setCellValue(e.getH11());
            System.out.println(i + "i内+" + e.getH11());
            cell = row.getCell(20);
            cell.setCellValue(e.getH12());
            System.out.println(i + "i内+" + e.getH12());
            cell = row.getCell(21);
            cell.setCellValue(e.getH13());
            System.out.println(i + "i内+" + e.getH13());
            cell = row.getCell(22);
            cell.setCellValue(e.getH14());
            System.out.println(i + "i内+" + e.getH14());
            cell = row.getCell(23);
            cell.setCellValue(e.getH15());
            System.out.println(i + "i内+" + e.getH15());
            cell = row.getCell(24);
            cell.setCellValue(e.getH16());
            System.out.println(i + "i内+" + e.getH16());
            cell = row.getCell(25);
            cell.setCellValue(e.getDaily_sum());
            System.out.println(i + "i内+" + e.getDaily_sum());
            cell = row.getCell(26);
            cell.setCellValue(e.getDaily_avg());
            System.out.println(i + "i内+" + e.getDaily_avg());
            cell = row.getCell(27);
            cell.setCellValue(e.getDaily_max());
            System.out.println(i + "i内+" + e.getDaily_max());
            cell = row.getCell(28);
            cell.setCellValue(e.getDaily_min());
            System.out.println(i + "i内+" + e.getDaily_min());
        }
        // 写入一个新的Excel表内
        FileOutputStream out = new FileOutputStream(new File("E:/"+yyyy+mm+".xls"));
        // Excel表写入完成
        workbook.write(out);
        // Excel表退出
        out.close();
    }

 

总结：1.我们这个项目用的是ssh架构，如果想使用以上代码，需要按照ssh的规范，定义dao action service entity四个包，如果需要页面操作还需要js做页面。

               2.关于Excel的各种操作，详情请转到http://www.yiibai.com/apache_poi/apache_poi_java_excel.html