springmvc實現導出數據信息為excle表格示例代碼

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導出就是根據前台條件將參數傳到controller，根據參數去數據庫中進行查詢，查詢出list集合，將list集合生成excle數據下載。

代碼片段：

Contorller.Java

 /** * 導出信息* @param model */ @RequestMapping("exportCustomer.do") @SystemControllerLog(description = "數據庫表單導出Excle") public void exportCustomer(ModelMap model) { //TODO 如需添加條件//model.addAttribute("username", nameStr); //獲取需要導出的數據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) { // 創建一個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); // 創建單元格，並設置表頭，設置表頭居中HSSFCellStyle style = workbook.createCellStyle(); // 創建一個居中格式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("狀態"); 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); // 創建單元格，並設置值// 編號列居左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();//客戶狀態1.在職，2.離職String status=""; if ("1".equals(statusStr)) { status="在職"; } if ("2".equals(statusStr)) { status="離職"; } HSSFCell c4 = row.createCell(3);//狀態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'); } }); }

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