將Oracle數據庫中的數據寫入Excel，oracleexcel

將Oracle數據庫中的數據寫入Excel，oracleexcel

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

以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的幫助，同時也希望多多支持幫客之家！