package com.goafanti.common.utils; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.goafanti.common.model.TOrderBillNew; import com.goafanti.customer.bo.CustomerListOut; import com.goafanti.order.bo.OrderReportBo; import com.goafanti.order.bo.TOrderBonusBo; import com.goafanti.order.bo.TOrderNewBo; import com.goafanti.order.bo.TOrderTaskListBo; import com.goafanti.order.enums.BonusSubject; import com.goafanti.order.enums.LiquidationNewState; import com.goafanti.order.enums.OrderNewState; import com.goafanti.order.enums.ProjectNewStage; import com.goafanti.techproject.enums.TaskState; public class ExportExcelUtil { //设置单元格格式 public static XSSFCellStyle setCellStyle1(XSSFWorkbook workbook){ // 设置字体 XSSFFont font = workbook.createFont(); // font.setColor(HSSFFont.COLOR_RED); font.setFontName("微软雅黑"); font.setFontHeightInPoints((short) 22);//字体大小 // 设置样式 XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFont(font); //字体 // 设置单元格内容水平对其方式 // XSSFCellStyle.ALIGN_CENTER 居中对齐 // XSSFCellStyle.ALIGN_LEFT 左对齐 // XSSFCellStyle.ALIGN_RIGHT 右对齐 cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置自动换行 cellStyle.setWrapText(true); return cellStyle; } public static XSSFCellStyle setCellStyle2(XSSFWorkbook workbook){ // 设置字体 XSSFFont font = workbook.createFont(); // font.setColor(HSSFFont.COLOR_RED); font.setFontName("微软雅黑"); font.setFontHeightInPoints((short) 12);//字体大小 // 设置样式 XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFont(font); //字体 // 设置单元格内容水平对其方式 // XSSFCellStyle.ALIGN_CENTER 居中对齐 // XSSFCellStyle.ALIGN_LEFT 左对齐 // XSSFCellStyle.ALIGN_RIGHT 右对齐 cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置自动换行 cellStyle.setWrapText(true); return cellStyle; } //下载课表 public static void download(String path, HttpServletResponse response,String filename) { try { // path是指欲下载的文件的路径。 File file = new File(path); // 取得文件名。 // 以流的形式下载文件。 InputStream fis = new BufferedInputStream(new FileInputStream(path)); byte[] buffer = new byte[fis.available()]; fis.read(buffer); fis.close(); // 清空response response.reset(); // 设置response的Header response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(),"iso-8859-1")); //防止中文乱码或者中文忽略的情况 response.addHeader("Content-Length", "" + file.length()); OutputStream toClient = new BufferedOutputStream( response.getOutputStream()); response.setContentType("application/octet-stream;charset=utf-8"); toClient.write(buffer); toClient.flush(); toClient.close(); } catch (IOException ex) { ex.printStackTrace(); } } public static void download(String path, HttpServletResponse response) { try { // path是指欲下载的文件的路径。 File file = new File(path); // 取得文件名。 String filename = file.getName(); // 以流的形式下载文件。 InputStream fis = new BufferedInputStream(new FileInputStream(path)); byte[] buffer = new byte[fis.available()]; fis.read(buffer); fis.close(); // 清空response response.reset(); // 设置response的Header response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(),"iso-8859-1")); response.addHeader("Content-Length", "" + file.length()); OutputStream toClient = new BufferedOutputStream( response.getOutputStream()); response.setContentType("application/octet-stream;charset=utf-8"); toClient.write(buffer); toClient.flush(); toClient.close(); } catch (IOException ex) { ex.printStackTrace(); } } //=========================================================此处分割线===========================================================================// /** * 导出部门流水数据 * @param map 数据 * @param comment 表头(列名) * @param filePath 生成文件路径 */ public static XSSFWorkbook exportDeptBillInfo(Map> map,String[] comment,String filePath){ //map 数据 ,comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); int count = 0; //遍历map for (Map.Entry> entry : map.entrySet()) { //2.创建一个sheet XSSFSheet sheet = wb.createSheet(entry.getKey()); //3。创建一个row XSSFRow row = sheet.createRow(0); //4.创建一个cell XSSFCell cell = row.createCell(0); cell.setCellValue(entry.getKey()+" 流水记录"); count ++; XSSFCellStyle style1 = setCellStyle1(wb); cell.setCellStyle(style1); //合并列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格 //创建表头 XSSFRow row2 = sheet.createRow(1); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row2.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } //填充表格数据 //int rowmnum = sheet.getLastRowNum();//获得当前行数据 List list = entry.getValue(); if(list == null){ continue; } for (TOrderBillNew ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o ; switch (comment[i]) { case "平台流水编号": o = ob.getBillNo(); break; case "平台流水时间": o = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ob.getCreateTime()); break; case "交易金额(万元)": o = ob.getTransactionAmount().toString() ; break; case "付款方": o = ob.getPayerName(); break; case "收款方": o = ob.getPayeeName(); break; case "流水科目": if(0 == ob.getTransactionSubject()){ o = "代收"; }else if(1 == ob.getTransactionSubject()){ o = "代付"; }else if(2 == ob.getTransactionSubject()){ o = "退款"; }else{ o = "未定义"; } break; case "交易渠道": if(0 == ob.getTransactionChannel()){ o = "微信"; }else if(1 == ob.getTransactionChannel()){ o = "支付宝"; }else if(2 == ob.getTransactionChannel()){ o = "银联"; }else{ o = "未定义"; } break; case "交易时间": o = ob.getFinancialPayTimes(); break; case "财务流水号": o = ob.getFinancialPayNo();break; case "订单编号": o = ob.getOrderNo(); break; case "订单负责人": o = ob.getSaleName(); break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } } return wb; /*OutputStream os = new ByteArrayOutputStream(); try { wb.write(os); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } //将表格数据放入文件中 try { FileOutputStream fos = new FileOutputStream(filePath); wb.write(fos); fos.close(); System.out.println("已经创建文件"); } catch (Exception e) { System.out.println("发生了异常"); e.printStackTrace(); }*/ } /** * 导出技术员奖金统计 * @param map * @param comment * @return */ public static XSSFWorkbook exportDeptBonusInfoT(Map> map,String[] comment){ //map 数据 ,comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); int count = 0; //遍历map for (Map.Entry> entry : map.entrySet()) { //2.创建一个sheet XSSFSheet sheet = wb.createSheet(entry.getKey()); //3。创建一个row XSSFRow row = sheet.createRow(0); //4.创建一个cell XSSFCell cell = row.createCell(0); cell.setCellValue(entry.getKey()+" 奖金记录"); count ++; XSSFCellStyle style1 = setCellStyle1(wb); cell.setCellStyle(style1); //合并列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格 //创建表头 XSSFRow row2 = sheet.createRow(1); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row2.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } //填充表格数据 //int rowmnum = sheet.getLastRowNum();//获得当前行数据 List list = entry.getValue(); if(list == null){ continue; } for (TOrderBonusBo ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o = ""; switch (comment[i]) { case "任务编号": o = ob.getTid().toString(); break; case "任务名称": o = ob.getCommodityName(); break; case "订单编号": o = ob.getOrderNo(); break; case "任务负责人": o = ob.getTargetName(); break; case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break; case "任务数量": o = ob.getCommodityQuantity().toString(); break; case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break; case "受理日期": o = ob.getAcceptTime(); break; case "评审日期": o = ob.getReviewTime(); break; case "下证日期": o = ob.getLicenceTime();break; case "订单结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break; case "发放类型": o = BonusSubject.getValueByCode(ob.getBonusSubject()); break; case "发放状态": if(0 == ob.getGrantStatus()){ o = "未发放"; }else if(1 == ob.getGrantStatus()){ o = "已发放"; } break; case "奖金创建时间": o = ob.getStartTime1(); break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } } return wb; /*OutputStream os = new ByteArrayOutputStream(); try { wb.write(os); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } //将表格数据放入文件中 try { FileOutputStream fos = new FileOutputStream(filePath); wb.write(fos); fos.close(); System.out.println("已经创建文件"); } catch (Exception e) { System.out.println("发生了异常"); e.printStackTrace(); }*/ } /** * 导出订单数据 * @param list * @param date * @return */ public static XSSFWorkbook exportOrderInfo(List list,String date){ //list 数据 ,comment表头 (列名称) String[] comment = {"序号","订单部门","订单类型","签单人员","签单数量","累计签单金额(万元)"}; //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); //遍历map //2.创建一个sheet XSSFSheet sheet = wb.createSheet(date + "订单统计"); //3。创建一个row XSSFRow row = sheet.createRow(0); //4.创建一个cell XSSFCell cell = row.createCell(0); cell.setCellValue(date + "订单统计"); XSSFCellStyle style1 = setCellStyle1(wb); cell.setCellStyle(style1); //合并列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格 //创建表头 XSSFRow row2 = sheet.createRow(1); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row2.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } //填充表格数据 //int rowmnum = sheet.getLastRowNum();//获得当前行数据 for (OrderReportBo ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o = ""; switch (comment[i]) { case "序号": o = ob.getId().toString(); break; case "订单部门": o = ob.getDeptName(); break; case "订单类型": o = (ob.getType() == 1 ? "科技项目" : "认证项目"); break; case "签单人员": o = ob.getSaleName(); break; case "签单数量": o = ob.getProofCount().toString(); break; case "累计签单金额(万元)": o =ob.getAmount().toString(); break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } return wb; /*OutputStream os = new ByteArrayOutputStream(); try { wb.write(os); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } //将表格数据放入文件中 try { FileOutputStream fos = new FileOutputStream(filePath); wb.write(fos); fos.close(); System.out.println("已经创建文件"); } catch (Exception e) { System.out.println("发生了异常"); e.printStackTrace(); }*/ } public static XSSFWorkbook exportOrderDunData(List list,String[] comment,String filePath){ //map 数据 comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); //2.创建一个sheet XSSFSheet sheet = wb.createSheet(); //创建表头 XSSFCellStyle style1 = setCellStyle1(wb); XSSFRow row = sheet.createRow(0); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } for (TOrderNewBo ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o ; switch (comment[i]) { case "订单编号": o = ob.getOrderNo(); break; case "签单时间": o = ob.getSignDate(); break; case "客户名称": o = ob.getUserName(); break; case "订单状态": o = OrderNewState.getValueByCode(ob.getOrderStatus());break; case "签单金额(万元)": o = ob.getTotalAmount().toString(); break; case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); break; case "结算状态": o = LiquidationNewState.getValueByCode(ob.getLiquidationStatus()); break; case "应收款(万元)": o = ob.getAccountsReceivable().toString(); break; case "已收款(万元)": o = ob.getSettlementAmount().toString(); break; case "未收款(万元)": o = ob.getUncollectedAmount().toString(); break; case "订单部门": o =ob.getDepName(); break; case "订单负责人": o =ob.getSalesmanName(); break; case "催收科目": o = getDunSuBubject(ob.getDunSubject()); break; case "催款启动日期": o = ob.getStartDate(); break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } return wb; } private static String getDunSuBubject(String dunSubject) { if ("12".equals(dunSubject)) return "订单审核通过"; if ("20".equals(dunSubject)) return "项目待提交"; if ("21".equals(dunSubject)) return "项目已提交"; if ("22".equals(dunSubject)) return "项目评审"; if ("23".equals(dunSubject)) return "项目立项"; if ("24".equals(dunSubject)) return "项目公示"; if ("25".equals(dunSubject)) return "项目抽查"; if ("26".equals(dunSubject)) return "项目备案"; if ("27".equals(dunSubject)) return "项目下证"; if ("28".equals(dunSubject)) return "项目验收"; if ("29".equals(dunSubject)) return "项目拨款"; return ""; } public static XSSFWorkbook exportTaskHoursListData(List list, String[] comment, String filePath) { //map 数据 comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); //2.创建一个sheet XSSFSheet sheet = wb.createSheet(); //创建表头 XSSFCellStyle style1 = setCellStyle1(wb); XSSFRow row = sheet.createRow(0); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } for (TOrderTaskListBo ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o ; switch (comment[i]) { //"任务编号","任务名称","订单编号","业务别类","客户名称","任务负责人","任务状态","工时" case "任务编号": o = ob.getId(); break; case "任务名称": o = ob.getTaskName(); break; case "订单编号": o = ob.getOrderNo(); break; case "业务别类": o = ob.getCname();break; case "订单部门": o = ob.getDepName();break; case "客户名称": o = ob.getUserName(); break; case "任务负责人": o = ob.getReceiverName(); break; case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); break; case "工时": o = ob.getHours().toString(); break; case "合同编号":o =ob.getContractNo();break; case "签单日期":o =ob.getSignTime();break; case "派单日期":o =ob.getCreteTime();break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } return wb; } public static XSSFWorkbook exportSelectMyTaskList(List list, String[] comment, String filePath) { //map 数据 comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); //2.创建一个sheet XSSFSheet sheet = wb.createSheet(); //创建表头 XSSFCellStyle style1 = setCellStyle1(wb); XSSFRow row = sheet.createRow(0); for(int i = 0; i < comment.length; i++){ sheet.autoSizeColumn(i); sheet.autoSizeColumn(i, true); sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } for (TOrderTaskListBo ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o="" ; switch (comment[i]) { //"任务编号","任务名称","订单编号","业务别类","客户名称","任务状态","项目状态","分配时间","订单部门" case "任务编号": o = ob.getId(); setWidth(sheet, i, o,1); break; case "任务名称": o = ob.getTaskName(); setWidth(sheet, i, o,1);break; case "订单编号": o = ob.getOrderNo();setWidth(sheet, i, o,1); break; case "业务别类": o = ob.getCname();setWidth(sheet, i, o,1);break; case "客户名称(软著数/下证数)": if(null!=ob.getTaskName()&&ob.getTaskName().contains("软件著作权")){ o =ob.getUserName()+"("+ob.getCommodityQuantity()+"/"+ob.getAlreadyNumber()+")"; }else { o = ob.getUserName(); } setWidth(sheet, i, o,2);break; case "任务状态": o = TaskState.getValueByCode(ob.getTaskStatus()); setWidth(sheet, i, o,1);break; case "项目状态": o = ProjectNewStage.getValueByCode(ob.getProjectStatus()); setWidth(sheet, i, o,1);break; case "分配时间": o = ob.getTaskDate();setWidth(sheet, i, o,1); break; case "订单部门": o = ob.getDepName();setWidth(sheet, i, o,1);break; default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } return wb; } private static void setWidth(XSSFSheet sheet, int i, String o,int x) { if (StringUtils.isNotBlank(o)) { sheet.setColumnWidth(i, o.getBytes().length*x*280); } } /** * 年度导出数据模板 * @return */ public static XSSFWorkbook downOrderModelExcel() { String[] comment = {"姓名","电话","月份","项目类型","签单数量","累计签单金额(万元)"}; String[] d1 = {"张三","13275161283","1","科技项目","2","12"}; String[] d2 = {"张三","13275161283","1","认证项目","0.3","3"}; //map 数据 comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); //2.创建一个sheet XSSFSheet sheet = wb.createSheet(); //创建表头 XSSFCellStyle style1 = setCellStyle1(wb); XSSFRow row = sheet.createRow(0); XSSFRow row1 = sheet.createRow(1); XSSFRow row2 = sheet.createRow(2); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } for(int i = 0; i < d1.length; i++){ XSSFCell c = row1.createCell(i); c.setCellValue(d1[i]); c.setCellStyle(style1); } for(int i = 0; i < d2.length; i++){ XSSFCell c = row2.createCell(i); c.setCellValue(d2[i]); c.setCellStyle(style1); } return wb; } /** * 导出营销部部门奖金的数据 * @param map * @return */ public static XSSFWorkbook exportDeptBonusInfoS(Map> map){ String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额(万元)", "首付款(万元)", "已收(万元)","已退(万元)","订单状态","项目状态","发放类型", "校对后单量(个)","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量", "奖金创建时间"}; //map 数据 ,comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); int count = 0; //遍历map for (Map.Entry> entry : map.entrySet()) { //2.创建一个sheet XSSFSheet sheet = wb.createSheet(entry.getKey()); //3。创建一个row XSSFRow row = sheet.createRow(0); //4.创建一个cell XSSFCell cell = row.createCell(0); cell.setCellValue(entry.getKey()+" 奖金记录"); count ++; XSSFCellStyle style1 = setCellStyle1(wb); cell.setCellStyle(style1); //合并列 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, comment.length-1));//合并单元格 //创建表头 XSSFRow row2 = sheet.createRow(1); for(int i = 0; i < comment.length; i++){ sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row2.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } //填充表格数据 //int rowmnum = sheet.getLastRowNum();//获得当前行数据 List list = entry.getValue(); if(list == null){ continue; } for (TOrderBonusBo ob : list) { //新增一行 // String comment[] = {"订单编号", "订单负责人", "签单日期", "客户名称", "签单金额", "首付款", "已收","已退","订单状态","项目状态","发放类型", // "校对后单量","发放状态","当月认证项目数量","当月科技项目数量","年度认证项目数量","年度科技项目数量"}; XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); r.createCell(0).setCellValue(ob.getOrderNo()); r.createCell(1).setCellValue(ob.getSalesName()); r.createCell(2).setCellValue(ob.getSignTime()); r.createCell(3).setCellValue(ob.getBuyName()); r.createCell(4).setCellValue(ob.getTotalAmount().toString()); r.createCell(5).setCellValue(ob.getFirstAmount().toString()); r.createCell(6).setCellValue(ob.getSettlementAmount().toString()); r.createCell(7).setCellValue(ob.getRefundAmount().toString()); r.createCell(8).setCellValue(OrderNewState.getValueByCode(ob.getOrderStatus())); r.createCell(9).setCellValue(ProjectNewStage.getValueByCode(ob.getProjectStatus())); r.createCell(10).setCellValue(BonusSubject.getValueByCode(ob.getBonusSubject())); r.createCell(11).setCellValue(ob.getProofCount().toString()); r.createCell(12).setCellValue(ob.getGrantStatus() == 0? "未发放":"已发放"); r.createCell(13).setCellValue(ob.getRenzm()); r.createCell(14).setCellValue(ob.getKejim()); r.createCell(15).setCellValue(ob.getRenzy()); r.createCell(16).setCellValue(ob.getRenzy()); r.createCell(17).setCellValue(ob.getStartTime1()); } } return wb; } public static XSSFWorkbook exportSelectPrivateOrganizationCustomer(List list, String[] comment, String filePath) { //map 数据 comment表头 (列名称) //1,创建一个workbook,对应的是一个excel文件 XSSFWorkbook wb = new XSSFWorkbook(); //2.创建一个sheet XSSFSheet sheet = wb.createSheet(); //创建表头 XSSFCellStyle style1 = setCellStyle1(wb); XSSFRow row = sheet.createRow(0); for(int i = 0; i < comment.length; i++){ sheet.autoSizeColumn(i); sheet.autoSizeColumn(i, true); sheet.setColumnWidth(i, 3000);//设置列宽 XSSFCell c = row.createCell(i); c.setCellValue(comment[i]); c.setCellStyle(style1); } for (CustomerListOut ob : list) { //新增一行 XSSFRow r = sheet.createRow(sheet.getLastRowNum()+1); for (int i = 0; i < comment.length; i++) { //当前列的只 String o="" ; switch (comment[i]) { //"客户名称","地区","联系人","联系电话","社会性质","客户初始时间","剩余私有天数","最新跟进时间","客户等级" case "客户名称": o = ob.getName(); break; case "地区": o = ob.getProvince()+"-"+ob.getCity()+"-"+ob.getArea(); break;// case "联系人": o = ob.getContacts(); break; case "联系电话": o = ob.getContactMobile();break; case "社会性质": o = getSocietyTagName(ob.getSocietyTag());break; case "客户初始时间": o = ob.getCreateTime();break; case "剩余私有天数": o = ob.getSurplusFollowTime();break; case "最新跟进时间": o = ob.getLastSignTime();break; case "客户等级": o = getLevelName(ob.getLevel());break;// default: o = ""; break; } XSSFCell c = r.createCell(i); c.setCellValue(o); } } return wb; } private static String getLevelName(Integer s){ if(s==null)return ""; if(s.equals(0))return "一般客户"; if(s.equals(1))return "意向客户"; if(s.equals(2))return "重点客户"; return ""; } private static String getSocietyTagName(String s){ if(s==null)return ""; if(s.equals("0"))return "社会企业"; if(s.equals("1"))return "科研单位"; if(s.equals("2"))return "高等院校"; if(s.equals("3"))return "科研专家"; if(s.equals("4"))return "科技达人"; if(s.equals("5"))return "服务机构"; if(s.equals("6"))return "政府部门"; if(s.equals("7"))return "商会"; if(s.equals("8"))return "协会"; if(s.equals("9"))return "其他组织"; if(s.equals("10"))return "其他个人"; return ""; } }