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

数据库  /  管理员 发布于 6年前   226

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做页面。

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