JAVA导出Excel（支持多sheet）
一、批量导出：
/** * * @Title: expExcel * @Description: 批量导出客户信息 * @param @param params * @param @param request * @param @param response * @param @throws Exception * @return void * @throws */ @RequestMapping("expExcel") public void expExcel(QueryCustomParam params,HttpServletRequest request,HttpServletResponse response) throws Exception{ User u = getUser(request.getSession());//SessionUtils.getUser(request.getSession()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd hh:mm"); //获取网站部署路径(通过ServletContext对象)，用于确定下载文件位置，从而实现下载 String path = request.getServletContext().getRealPath("/"); List<CustomResp> list = null; List<ContactsResp> list2 = null; String fileName = "我的客户导出"; String url = path + "download\\" + fileName + ".xls"; params.setEmpIds(String.valueOf(u.getEmpId())); QueryContactsParam params2 = new QueryContactsParam(); params2.setEmpId(Long.valueOf(u.getEmpId())); params2.setCorpId(Long.valueOf(u.getCorpId())); params2.setFlag("4"); try { list = customService.selectMyCustom(params); //我的客户 list2 = contactsService.selectContactsList(params2); //查询我的客户包含的所有联系人信息 //数据 List<String[]> dataset = new ArrayList<String[]>(); for (int i = 0; i < list.size(); i++) { String[] arr = new String[16]; arr[0] = list.get(i).getName()==null ? "" : list.get(i).getName().toString(); arr[1] = list.get(i).getShortName()==null ? "" : list.get(i).getShortName().toString(); arr[2] = list.get(i).getNumber()==null ? "" : list.get(i).getNumber().toString(); arr[3] = list.get(i).getAddress()==null ? "" : list.get(i).getAddress().toString(); arr[4] = list.get(i).getUrl()==null ? "" : list.get(i).getUrl().toString(); arr[5] = list.get(i).getDescription()==null ? "" : list.get(i).getDescription().toString(); arr[6] = list.get(i).getHighseasName()==null ? "" : list.get(i).getHighseasName().toString(); arr[7] = list.get(i).getHighseasDate()==null ? "" : sdf2.format(list.get(i).getHighseasDate()); arr[8] = list.get(i).getEmpName()==null ? "" : list.get(i).getEmpName().toString(); arr[9] = list.get(i).getOrganName()==null ? "" : list.get(i).getOrganName().toString(); arr[10] = ""; //领取日期 暂无数据 arr[11] = list.get(i).getExpireDate()==null ? "" : sdf2.format(list.get(i).getExpireDate()); arr[12] = "4"; //联系人数量 arr[13] = list.get(i).getCreateDate()==null ? "" : sdf2.format(list.get(i).getCreateDate()); arr[14] = list.get(i).getCustomStatusName()==null ? "" : list.get(i).getCustomStatusName().toString(); arr[15] = list.get(i).getCustomLevelName()==null ? "" : list.get(i).getCustomLevelName().toString(); dataset.add(arr); } //数据 List<String[]> dataset2 = new ArrayList<String[]>(); for (int i = 0; i < list2.size(); i++) { String[] arr2 = new String[16]; arr2[0] = list2.get(i).getName()==null ? "" : list2.get(i).getName().toString(); arr2[1] = list2.get(i).getSex()==null ? "" : getSex(list2.get(i).getSex()); arr2[2] = list2.get(i).getBirthday()==null ? "" : sdf.format(list2.get(i).getBirthday()); arr2[3] = ""; arr2[4] = list2.get(i).getOrganName()==null ? "" : list2.get(i).getOrganName().toString(); arr2[5] = list2.get(i).getPosition()==null ? "" : list2.get(i).getPosition().toString(); arr2[6] = list2.get(i).getCompany()==null ? "" : list2.get(i).getCompany().toString(); arr2[7] = list2.get(i).getAddress()==null ? "" : list2.get(i).getAddress().toString(); arr2[8] = list2.get(i).getUrl()==null ? "" : list2.get(i).getUrl().toString(); arr2[9] = list2.get(i).getInterest()==null ? "" : list2.get(i).getInterest().toString(); arr2[10] = list2.get(i).getRemark()==null ? "" : list2.get(i).getRemark().toString(); arr2[11] = list2.get(i).getCreateDate()==null ? "" : sdf2.format(list2.get(i).getCreateDate()); arr2[12] = list2.get(i).getCustomName()==null? "" : list2.get(i).getCustomName(); arr2[13] = list2.get(i).getEmpName()==null? "" : list2.get(i).getEmpName(); arr2[14] = list2.get(i).getContactsRoleName()==null? "" : list2.get(i).getContactsRoleName(); arr2[15] = list2.get(i).getContactsRelationName()==null? "" : list2.get(i).getContactsRelationName(); dataset2.add(arr2); } //表头 String[] handers1 = {"客户全称","客户简称","客户编号","地址","网址","介绍","公海名称","加入公海时间","跟进人","跟进人部门","领取日期","到期日期","联系人数量","创建日期","客户状态","客户分级" }; String[] handers2 = {"姓名","性别","生日","手机","部门","职务","公司","公司地址","公司网站","兴趣爱好","备注","创建日期","客户简称","归属人","角色关系","亲密程度" }; //对象 ExcelExp e1 = new ExcelExp("我的客户", handers1, dataset); ExcelExp e2 = new ExcelExp("客户联系人", handers2, dataset2); List<ExcelExp> mysheet = new ArrayList<ExcelExp>(); mysheet.add(e1); mysheet.add(e2); ExcelExportUtil.exportManySheetExcel(url, mysheet); //生成sheet ExcelExportUtil.getExcel(url, fileName, response); //下载sheet } catch (IOException e) { e.printStackTrace(); } }
Excel对象：
public class ExcelExp { private String fileName;// sheet的名称 private String[] handers;// sheet里的标题 private List<String[]> dataset;// sheet里的数据集 public ExcelExp(String fileName, String[] handers, List<String[]> dataset) { this.fileName = fileName; this.handers = handers; this.dataset = dataset; } //getter、setter方法 }
生成Excel：
/** * * @Title: exportManySheetExcel * @Description: 可生成单个、多个sheet * @param @param file 导出文件路径 * @param @param mysheets * @return void * @throws */ public static void exportManySheetExcel(String file, List<ExcelExp> mysheets){ HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄 List<ExcelExp> sheets = mysheets; //表头样式 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 //字体样式 HSSFFont fontStyle = wb.createFont(); fontStyle.setFontName("微软雅黑"); fontStyle.setFontHeightInPoints((short)12); fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(fontStyle); for(ExcelExp excel: sheets){ //新建一个sheet HSSFSheet sheet = wb.createSheet(excel.getFileName());//获取该sheet名称 String[] handers = excel.getHanders();//获取sheet的标题名 HSSFRow rowFirst = sheet.createRow(0);//第一个sheet的第一行为标题 //写标题 for(int i=0;i<handers.length;i++){ //获取第一行的每个单元格 HSSFCell cell = rowFirst.createCell(i); //往单元格里写数据 cell.setCellValue(handers[i]); cell.setCellStyle(style); //加样式 sheet.setColumnWidth(i, 4000); //设置每列的列宽 } //写数据集 List<String[]> dataset = excel.getDataset(); for(int i=0;i<dataset.size();i++){ String[] data = dataset.get(i);//获取该对象 //创建数据行 HSSFRow row = sheet.createRow(i+1); for(int j=0;j<data.length;j++){ //设置对应单元格的值 row.createCell(j).setCellValue(data[j]); } } } // 写文件 try { FileOutputStream out = new FileOutputStream(new File(file)); out.flush(); wb.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
