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

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

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