java 实现用户自由选择字段实现导出EXCEL表格
package com.thinkgem.jeesite.common.utils.excel; import java.io.File; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.ibatis.ognl.Ognl; import org.apache.ibatis.ognl.OgnlException; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Sheet; import com.thinkgem.jeesite.common.utils.DateUtils; import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField; import com.thinkgem.jeesite.modules.account.entity.Account; import com.thinkgem.jeesite.modules.account.service.AccountService; public class ExcelAccount { /** * 导出excel表格方法一 * * @param response * @param accountService * @param account * @param data */ public void exportExcel(HttpServletResponse response, AccountService accountService, Account account, String data) { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("前台用户表数据"); HSSFRow row = sheet.createRow(0); // Sheet样式 HSSFCellStyle style = wb.createCellStyle();//设置标题样式 String[] splitData = data.split(","); Font font = wb.createFont(); font.setFontHeightInPoints((short) 10); font.setBoldweight((short) 600); font.setColor(HSSFColor.DARK_TEAL.index); style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 style.setFont(font); sheet.autoSizeColumn(1, true); row.setHeightInPoints(26); HSSFCellStyle centerstyle = wb.createCellStyle();//设置普通表格样式 //centerstyle.setFont(font); //centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 //centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 //centerstyle.setWrapText(true); // centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); //centerstyle.setBorderLeft((short) 1); //centerstyle.setRightBorderColor(HSSFColor.BLACK.index); //centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 // centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色． //centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色． String fileName = "前台用户数据.xls"; setResponseHeader(response, fileName); if (splitData != null && splitData.length > 0) { int i = 0; for (String str : splitData) { HSSFCell cell = row.createCell(i); if ("loginName".equals(str)) { cell.setCellValue("用户登录名"); } else if ("companyCnName".equals(str)) { cell.setCellValue("企业名称(中文)"); cell.setCellStyle(style); } else if ("companyEnName".equals(str)) { cell.setCellValue("企业名称(英文)"); } else if ("companyCnAddress".equals(str)) { cell.setCellValue("企业地址(中文)"); } else if ("cnZhuYingProd".equals(str)) { cell.setCellValue("主营产品(中文)"); } else if ("companyPhone".equals(str)) { cell.setCellValue("公司电话"); } else if ("fzrName".equals(str)) { cell.setCellValue("外贸负责人姓名"); } else if ("fzrSex".equals(str)) { cell.setCellValue("外贸负责人性别"); } else if ("fzrPosition".equals(str)) { cell.setCellValue("外贸负责人职位"); } else if ("fzrMobile".equals(str)) { cell.setCellValue("外贸负责人手机"); } else if ("fzrEmail".equals(str)) { cell.setCellValue("外贸负责人邮箱"); } else if ("fzrQq".equals(str)) { cell.setCellValue("外贸负责人QQ"); } else if ("name".equals(str)) { cell.setCellValue("姓名"); } else if ("sex".equals(str)) { cell.setCellValue("性别"); } else if ("mobile".equals(str)) { cell.setCellValue("手机号码"); } else if ("email".equals(str)) { cell.setCellValue("邮箱"); } else if ("emailStatus".equals(str)) { cell.setCellValue("邮箱状态"); } else if ("birthday".equals(str)) { cell.setCellValue("生日"); } else if ("accountType".equals(str)) { cell.setCellValue("用户类型"); } else if ("address".equals(str)) { cell.setCellValue("个人地址"); } else if ("qq".equals(str)) { cell.setCellValue("个人qq"); } else if ("companyUrl".equals(str)) { cell.setCellValue("公司网站"); } else if ("registerType".equals(str)) { cell.setCellValue("注册类型"); } else if ("shiBie".equals(str)) { cell.setCellValue("识别码"); } else if ("tuiJianId".equals(str)) { cell.setCellValue("推荐id"); } cell.setCellStyle(style); i++; } } List<Account> findAccountList = accountService.findAccountList(account); if (findAccountList != null && findAccountList.size() > 0) { int m = 1; for (Account at : findAccountList) { row = sheet.createRow(m); int n = 0; for (String str : splitData) { String cellVal = ""; if ("loginName".equals(str)) { cellVal = at.getLoginName(); } else if ("companyCnName".equals(str)) { cellVal = at.getCompanyCnName(); } else if ("companyEnName".equals(str)) { cellVal = at.getCompanyEnName(); } else if ("companyCnAddress".equals(str)) { cellVal = at.getCompanyCnAddress(); } else if ("cnZhuYingProd".equals(str)) { cellVal = at.getCnZhuYingProd(); } else if ("companyPhone".equals(str)) { cellVal = at.getCompanyPhone(); } else if ("fzrName".equals(str)) { cellVal = at.getFzrName(); } else if ("fzrSex".equals(str)) { if (at.getFzrSex() != null) { if ("1".equals(at.getFzrSex())) { cellVal = "男"; } else if ("2".equals(at.getFzrSex())) { cellVal = "女"; } } else { cellVal = ""; } } else if ("fzrPosition".equals(str)) { cellVal = at.getFzrPosition(); } else if ("fzrMobile".equals(str)) { cellVal = at.getFzrMobile(); } else if ("fzrEmail".equals(str)) { cellVal = at.getFzrEmail(); } else if ("fzrQq".equals(str)) { cellVal = at.getFzrQq(); } else if ("name".equals(str)) { cellVal = at.getName(); } else if ("sex".equals(str)) { if (at.getSex() != null) { if ("1".equals(at.getSex())) { cellVal = "男"; } else if ("2".equals(at.getSex())) { cellVal = "女"; } } else { cellVal = ""; } } else if ("mobile".equals(str)) { cellVal = at.getMobile(); } else if ("email".equals(str)) { cellVal = at.getEmail(); } else if ("emailStatus".equals(str)) { if (at.getEmailStatus() != null) { if (at.getEmailStatus() == 0) { cellVal = "未验证"; } else if (at.getEmailStatus() == 1) { cellVal = "已验证"; } else { cellVal = ""; } } else { cellVal = ""; } } else if ("birthday".equals(str)) { if (at.getBirthday() != null) { cellVal = DateUtils.formatDate(at.getBirthday(), "yyyy-MM-dd"); } else { cellVal = null; } } else if ("accountType".equals(str)) { if (at.getAccountType() != null) { if (at.getAccountType() == 1) { cellVal = "企业用户"; } else if (at.getAccountType() == 2) { cellVal = "个人用户"; } else { cellVal = ""; } } else { cellVal = ""; } } else if ("address".equals(str)) { cellVal = at.getAddress(); } else if ("qq".equals(str)) { cellVal = at.getQq(); } else if ("companyUrl".equals(str)) { cellVal = at.getCompanyUrl(); } else if ("registerType".equals(str)) { cellVal = at.getRegisterType(); } else if ("shiBie".equals(str)) { cellVal = at.getShiBie(); } else if ("tuiJianId".equals(str)) { cellVal = String.valueOf(at.getTuiJianId()); } //row.createCell(n).setCellStyle(centerstyle); HSSFCell createCell = row.createCell(n); createCell.setCellValue(cellVal); createCell.setCellStyle(centerstyle); //row.createCell(n).setCellValue(cellVal); n++; } m++; } } OutputStream os = response.getOutputStream(); wb.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } public void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(), "ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } /** * 导出excel表格方法二 * * @param response * @param accountService * @param account * @param data */ public void exportAllExcel(HttpServletResponse response, AccountService accountService, Account account, String data) { String[] splitData = data.split(","); List<Account> findAccountList = accountService.findAccountList(account); String[][] datas = new String[findAccountList.size() + 1][splitData.length]; String titles[] = new String[splitData.length]; datas[0] = titles; Field[] fields = Account.class.getDeclaredFields(); int titlesIndex = 0; for (Field filed : fields) { for (String filedName : splitData) { if (filed.getName().equals(filedName)) { ExcelField annotation = filed.getAnnotation(ExcelField.class); String title = annotation.title(); titles[titlesIndex++] = title; } } } for (int i = 0; i < findAccountList.size(); i++) { Account ac = findAccountList.get(i); String[] row = new String[splitData.length]; for (int j = 0; j < splitData.length; j++) { try { row[j] = Ognl.getValue(ac, splitData[j]).toString(); } catch (OgnlException e) { row[j] = ""; } } datas[i + 1] = row; } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("前台用户表数据"); HSSFRow createRow = sheet.createRow(0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i < titles.length; i++) { HSSFCell cell = createRow.createCell(i); cell.setCellStyle(style); cell.setCellValue(titles[i]); } for (int i = 1; i < datas.length; i++) { HSSFRow row = sheet.createRow(i); String[] tls = datas[i]; for (int j = 0; j < tls.length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(tls[j]); } } } }
控制层调用上面方法
@RequestMapping(value = "exportAccountList", method = RequestMethod.POST) public String exportAccountList(Account account,HttpServletRequest request,HttpServletResponse response,RedirectAttributes redirectAttributes,String data){ try { ExcelAccount els = new ExcelAccount(); els.exportExcel(response, accountService, account, data); return null; } catch (Exception e) { e.printStackTrace(); addMessage(redirectAttributes, "导出前台用户失败！失败信息：" + e.getMessage()); } return "redirect:"+adminPath+"/account/list?repage"; }
