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

2017-01-13 11:41:14来源:作者:人点击

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();  }

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

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持第七城市!

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台