springmvc實現(xiàn)導出數(shù)據(jù)信息為excle表格示例代碼

更新時間：2017年01月05日 16:13:09 作者：GreenRookie

本篇文章主要介紹了springmvc實現(xiàn)導出數(shù)據(jù)信息為excle表格，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧。

1.項目增加導出日志信息

2.項目中導入poi-*.jar等操作excel文件的jar文件

poi-3.7-20120326.jar
poi-excelant-3.7-20101029.jar
poi-ooxml-3.7.jar
poi-ooxml-schemas-3.7.jar

Excel導出就是根據(jù)前臺條件將參數(shù)傳到controller，根據(jù)參數(shù)去數(shù)據(jù)庫中進行查詢，查詢出list集合，將list集合生成excle數(shù)據(jù)下載。

代碼片段：

Contorller.Java

/** 
   * 導出信息 
   * @param model 
   */ 
  @RequestMapping("exportCustomer.do") 
  @SystemControllerLog(description = "數(shù)據(jù)庫表單導出Excle") 
  public void exportCustomer(ModelMap model) { 
    //TODO 如需添加條件 
    //model.addAttribute("username", nameStr); 
    //獲取需要導出的數(shù)據(jù)List 
    List<CMcustomer> cusList=customerService.exportCustomer(model); 
      //使用方法生成excle模板樣式 
    HSSFWorkbook workbook = customerService.createExcel(cusList, request); 
    SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定義文件名格式 
 
    try { 
    //定義excle名稱 ISO-8859-1防止名稱亂碼 
      String msg = new String( 
          ("客戶信息_" + format.format(new Date()) + ".xls").getBytes(), 
          "ISO-8859-1"); 
      // 以導出時間作為文件名 
      response.setContentType("application/vnd.ms-excel"); 
      response.addHeader("Content-Disposition", "attachment;filename=" 
          + msg); 
      workbook.write(response.getOutputStream()); 
    } catch (IOException e) { 
      logger.error(e); 
    } 
  }

2.Service中createExcel方法

public HSSFWorkbook createExcel(List<CMcustomer> cusList, 
    HttpServletRequest request) { 
 
    // 創(chuàng)建一個webbook，對應一個excel文件 
    HSSFWorkbook workbook = new HSSFWorkbook(); 
    // 在webbook中添加一個sheet,對應excel文件中的sheet 
    HSSFSheet sheet = workbook.createSheet("客戶信息表"); 
    // 設置列寬 
    sheet.setColumnWidth(0, 25 * 100); 
    sheet.setColumnWidth(1, 35 * 100); 
    sheet.setColumnWidth(2, 35 * 100); 
    sheet.setColumnWidth(3, 40 * 100); 
    sheet.setColumnWidth(4, 45 * 100); 
    sheet.setColumnWidth(5, 45 * 100); 
    sheet.setColumnWidth(6, 50 * 100); 
    sheet.setColumnWidth(7, 80 * 100); 
    sheet.setColumnWidth(8, 35 * 100); 
    sheet.setColumnWidth(9, 40 * 100); 
    // 在sheet中添加表頭第0行 
    HSSFRow row = sheet.createRow(0); 
    // 創(chuàng)建單元格，并設置表頭，設置表頭居中 
    HSSFCellStyle style = workbook.createCellStyle(); 
    // 創(chuàng)建一個居中格式 
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
    // 帶邊框 
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
    // 生成一個字體 
    HSSFFont font = workbook.createFont(); 
    // 字體增粗 
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
    // 字體大小 
    font.setFontHeightInPoints((short) 12); 
    // 把字體應用到當前的樣式 
    style.setFont(font); 
 
    // 單獨設置整列居中或居左 
    HSSFCellStyle style1 = workbook.createCellStyle(); 
    style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
    HSSFCellStyle style2 = workbook.createCellStyle(); 
    style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); 
 
    HSSFCellStyle style3 = workbook.createCellStyle(); 
    style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); 
    HSSFFont hssfFont = workbook.createFont(); 
    hssfFont.setColor(HSSFFont.COLOR_RED); 
    hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
    style3.setFont(hssfFont); 
 
    HSSFCellStyle style4 = workbook.createCellStyle(); 
    style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); 
    HSSFFont hssfFont1 = workbook.createFont(); 
    hssfFont1.setColor(HSSFFont.COLOR_NORMAL); 
    hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
    style4.setFont(hssfFont1); 
 
    HSSFCell cell = row.createCell(0); 
    cell.setCellValue("序號"); 
    cell.setCellStyle(style); 
 
    cell = row.createCell(1); 
    cell.setCellValue("客戶姓名"); 
    cell.setCellStyle(style); 
 
    cell = row.createCell(2); 
    cell.setCellValue("性別"); 
    cell.setCellStyle(style); 
 
    cell = row.createCell(3); 
    cell.setCellValue("狀態(tài)"); 
    cell.setCellStyle(style); 
 
    cell = row.createCell(4); 
    cell.setCellValue("電話"); 
    cell.setCellStyle(style); 
 
    cell = row.createCell(5); 
    cell.setCellValue("郵箱"); 
    cell.setCellStyle(style); 
 
    cell = row.createCell(6); 
    cell.setCellValue("地址"); 
    cell.setCellStyle(style); 
    for (int i = 0; i < cusList.size(); i++) { 
      String logTypeDis = ""; 
      row = sheet.createRow(i + 1); 
      CMcustomer cMcustomer = cusList.get(i); 
      // 創(chuàng)建單元格，并設置值 
      // 編號列居左 
      HSSFCell c1 = row.createCell(0); 
      c1.setCellStyle(style2); 
      c1.setCellValue(i); 
      HSSFCell c2 = row.createCell(1); 
      c2.setCellStyle(style1); 
      c2.setCellValue(cMcustomer.getCustomername());//客戶姓名 
 
      String sexStr = cMcustomer.getSex();//性別 0:女,1:男 
      String sex=""; 
      if ("1".equals(sexStr)) { 
        sex="男"; 
      } 
      if ("0".equals(sexStr)) { 
        sex="女"; 
      } 
      HSSFCell c3 = row.createCell(2);//性別 
      c3.setCellStyle(style1); 
      c3.setCellValue(sex); 
       
      String statusStr = cMcustomer.getStatus();//客戶狀態(tài)1.在職，2.離職 
      String status=""; 
      if ("1".equals(statusStr)) { 
        status="在職"; 
      } 
      if ("2".equals(statusStr)) { 
        status="離職"; 
      } 
      HSSFCell c4 = row.createCell(3);//狀態(tài) 
      c4.setCellStyle(style1); 
      c4.setCellValue(status); 
      String customerid = cMcustomer.getCustomerid();//客戶id 
      List<CMphone> phoneList = cMphoneMapper.selectByCustomerid(customerid); 
      String phone=""; 
      if (phoneList!=null&&phoneList.size()>0) { 
        for (int j = 0; j < phoneList.size(); j++) { 
          phone = phoneList.get(j).getPhone(); 
        } 
      } 
      HSSFCell c5 = row.createCell(4);//電話 
      c5.setCellStyle(style1); 
      c5.setCellValue(phone); 
      List<CMemail> emailList = cMemailMapper.selectAll(customerid); 
      String email=""; 
      if (emailList!=null&&emailList.size()>0) { 
        for (int j = 0; j < emailList.size(); j++) { 
          email = emailList.get(j).getEmail(); 
        } 
      } 
      HSSFCell c6 = row.createCell(5);//郵箱 
      c6.setCellStyle(style1); 
      c6.setCellValue(email); 
      CMaddress cMaddress=new CMaddress(); 
      cMaddress.setCustomerid(customerid); 
    List<CMaddress> adderssList = cMaddressMapper.selectAll(cMaddress); 
      String adderss=""; 
      if (adderssList!=null&&adderssList.size()>0) { 
        for (int j = 0; j < adderssList.size(); j++) { 
          adderss = adderssList.get(j).getAddress(); 
        } 
      } 
      HSSFCell c7 = row.createCell(6);//地址 
      c7.setCellStyle(style1); 
      c7.setCellValue(adderss); 
 
      //使用默認格式 
      row.createCell(1).setCellValue(cMcustomer.getCustomername()); 
      row.createCell(2).setCellValue(sex); 
      row.createCell(3).setCellValue(status); 
      row.createCell(4).setCellValue(phone); 
      row.createCell(5).setCellValue(email); 
      row.createCell(6).setCellValue(adderss); 
    } 
    return workbook; 
}

3.頁面jsp調用

//導出信息 
    function exporBtn(){ 
    $.ajax({ 
      type:"POST", 
      url:"<%=path%>/customer/exportCustomer.do", 
      success:function(data){ 
        window.open('<%=path%>/customer/exportCustomer.do'); 
      } 
       
    }); 
  }

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: