1. Add export log information to the project
2. Import the jar file that operates excel files such as poi-*.jar in the project
Excel export is to pass parameters to the controller according to the foreground conditions, query the database according to the parameters, query the list collection, and generate excle data to download the list collection.
Code snippet:
Contorller.Java
/** * Export information* @param model */ @RequestMapping("exportCustomer.do") @SystemControllerLog(description = "Database Form ExportExcle") public void exportCustomer(ModelMap model) { //TODO If you need to add a condition //model.addAttribute("username", nameStr); //Get the data to be exported List List<CMcustomer> cusList=customerService.exportCustomer(model); //Use the method to generate excle template style HSSFWorkbook workbook = customerService.createExcel(cusList, request); SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // Define file name format try { // Define excle name ISO-8859-1 to prevent garbled String msg = new String( ("Customer Information_" + format.format(new Date()) + ".xls").getBytes(), "ISO-8859-1"); // Use the export time as the file name response.setContentType("application/vnd.ms-excel"); response.addHeader("Content-Disposition", "attachment;filename=" + msg); workbook.write(response.getOutputStream()); } catch (IOException e) { logger.error(e); } } 2. CreateExcel method in Service
public HSSFWorkbook createExcel(List<CMcustomer> cusList, HttpServletRequest request) { // Create a webbook, corresponding to an excel file HSSFWorkbook workbook = new HSSFWorkbook(); // Add a sheet in the webbook, corresponding to the sheet in the excel file HSSFSheet sheet = workbook.createSheet("Customer Information Table"); // Set column width 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); // Add row 0 of the header in the sheet HSSFRow row = sheet.createRow(0); // Create a cell, set the table header, set the table header to center HSSFCellStyle style = workbook.createCellStyle(); // Create a centered format style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // With border style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // Generate a font HSSFFont font = workbook.createFont(); // Font thickening font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Font size font.setFontHeightInPoints((short) 12); // Apply the font to the current style style.setFont(font); // Set the entire column to center or left separately 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("serial number"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("customer name"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("gender"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("status"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("phone"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("Mailbox"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("Address"); cell.setCellStyle(style); for (int i = 0; i < cusList.size(); i++) { String logTypeDis = ""; row = sheet.createRow(i + 1); CMcustomer cMcustomer = cusList.get(i); // Create a cell and set the value // The numbered column is on the left HSSFCell c1 = row.createCell(0); c1.setCellStyle(style2); c1.setCellValue(i); HSSFCell c2 = row.createCell(1); c2.setCellStyle(style1); c2.setCellValue(cMcustomer.getCustomername());//Customer name String sexStr = cMcustomer.getSex();//Gender 0: Female,1: Male String sex=""; if ("1".equals(sexStr)) { sex="Male"; } if ("0".equals(sexStr)) { sex="Female"; } HSSFCell c3 = row.createCell(2);//Gender c3.setCellStyle(style1); c3.setCellValue(sex); String statusStr = cMcustomer.getStatus();//Customer status 1. On-the-job, 2. Leave String status=""; if ("1".equals(statusStr)) { status="on-the-job"; } if ("2".equals(statusStr)) { status="resign"; } HSSFCell c4 = row.createCell(3);//Status c4.setCellStyle(style1); c4.setCellValue(status); String customerid = cMcustomer.getCustomerid();//Customer 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);//Telect 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);//Emailbox 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);//Address c7.setCellStyle(style1); c7.setCellValue(adderss); //Use the default format 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. Page jsp call
//Export information function exporBtn(){ $.ajax({ type:"POST", url:"<%=path%>/customer/exportCustomer.do", success:function(data){ window.open('<%=path%>/customer/exportCustomer.do'); } }); }The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.