头部列自定义内容导出,采用POI

controller层调用方法:
    @LoginCheck(description = true)
    @RequestMapping(value = "/driveRateDetailExportExcel")
    @SystemControllerLog(description = "导出",modelName="试驾率明细")
    @ResponseBody
    public void driveRateDetailExportExcel(TCarline model, HttpServletResponse response, HttpServletRequest request) {
       try {
            //获得所有的车系信息
            TCarline queryCarline = new TCarline();
            queryCarline.setIsdrive(1);
            List carlineList = this.iCarlineService.selectCarlineListByObj(queryCarline);
            String json = this.iTrialDriveService.getDriveRateDetailDateStr(paramMap);
            if(StringUtils.isBlank(json)){
                response_write("
                return;
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            HSSFWorkbook wb = new HSSFWorkbook();      
            HSSFSheet sheet = wb.createSheet("试驾率明细信息");      
            HSSFCellStyle style = wb.createCellStyle(); // 样式对象      

            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直      
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平      
            HSSFRow row = sheet.createRow((short) 0);      
            HSSFRow row2 = sheet.createRow((short) 1);      
            //起始行,起始列,结束行,结束列      
            sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 0));      
            sheet.addMergedRegion(new Region(0, (short) 1, 1, (short) 1));      
            sheet.addMergedRegion(new Region(0, (short) 2, 1, (short) 2));      
            sheet.addMergedRegion(new Region(0, (short) 3, 1, (short) 3));      
            HSSFCell ce = row.createCell(0);      
            ce.setCellValue("RBO"); // 表格的第一行第一列显示的数据      
            ce.setCellStyle(style); // 样式,居中      
            HSSFCell ce1 = row.createCell(1);      
            ce1.setCellValue("小区"); // 表格的第一行第一列显示的数据      
            ce1.setCellStyle(style); // 样式,居中
            HSSFCell ce2 = row.createCell(2);      
            ce2.setCellValue("经销商名称"); // 表格的第一行第一列显示的数据      
            ce2.setCellStyle(style); // 样式,居中
            HSSFCell ce3 = row.createCell(3);      
            ce3.setCellValue("经销商代码"); // 表格的第一行第一列显示的数据      
            ce3.setCellStyle(style); // 样式,居中
            if (carlineList != null && carlineList.size() > 0) {
                for (int i = 0; i < carlineList.size(); i++) { // 循环9次,每一次都要跨单元格显示      
                    TCarline headObj = carlineList.get(i);
                    // 计算从那个单元格跨到那一格      
                    int celln = 0;      
                    int celle = 0;      
                    if (i == 0) {      
                        celln = 3;      
                        celle = 4;      
                    } else {      
                        celln = (i * 3 +3);      
                        celle = (i * 3 + 4);      
                    }      
                    // 单元格合并      
                    // 四个参数分别是:起始行,起始列,结束行,结束列      
                    sheet.addMergedRegion(new Region(0, (short) (celln + 1), 0, (short) (celle + 2)));      
                    HSSFCell cell = row.createCell((celln + 1));      
                    cell.setCellValue(headObj.getName()); // 跨单元格显示的数据      
                    cell.setCellStyle(style); // 样式      
                    // 不跨单元格显示的数据,如:分两行,上一行分别两格为一格,下一行就为两格,“数量”,“金额”      
                    HSSFCell cell1 = row2.createCell(celle);      
                    HSSFCell cell2 = row2.createCell((celle + 1));      
                    HSSFCell cell3 = row2.createCell((celle + 2));      
                    cell1.setCellValue("有效试驾次数");      
                    cell1.setCellStyle(style);      
                    cell2.setCellValue("潜客数");      
                    cell2.setCellStyle(style);   
                    cell3.setCellValue("试驾率");      
                    cell3.setCellStyle(style);  
                }
                //全车型
                // 四个参数分别是:起始行,起始列,结束行,结束列  
                sheet.addMergedRegion(new Region(0, (short) ((carlineList.size()+1) * 3 + 1), 0, (short) ((carlineList.size()+1) * 3 + 3)));
                HSSFCell cell = row.createCell((carlineList.size()+1) * 3 + 1);      
                cell.setCellValue("全车型"); // 跨单元格显示的数据      
                cell.setCellStyle(style); // 样式
                HSSFCell cell1 = row2.createCell(carlineList.size() * 3 + 4);      
                HSSFCell cell2 = row2.createCell(carlineList.size() * 3 + 5);      
                HSSFCell cell3 = row2.createCell(carlineList.size() * 3 + 6);      
                cell1.setCellValue("有效试驾次数");      
                cell1.setCellStyle(style);      
                cell2.setCellValue("潜客数");      
                cell2.setCellStyle(style);   
                cell3.setCellValue("试驾率");      
                cell3.setCellStyle(style);
                int rowNum = 1;
                //循环json数据
                if(json != null && !"".equals(json.trim())){
                    JSONArray jsonArray = JSONArray.parseArray(json.toString());
                    if(jsonArray != null && jsonArray.size() > 0){
                         for(int i=0;i
                             Row dataRow = sheet.createRow(++rowNum);
                             JSONObject job = jsonArray.getJSONObject(i);
                             String rboValue = job.get("rboName").toString();
                             Cell rboValueCell = dataRow.createCell(0);
                             rboValueCell.setCellValue(rboValue);
                             String smallregionNameValue = "";
                             if(job.get("smallregionName") != null && !"".equals(job.get("smallregionName"))){
                                 smallregionNameValue = job.get("smallregionName").toString();
                             }
                             Cell smallregionNameCell = dataRow.createCell(1);
                             smallregionNameCell.setCellValue(smallregionNameValue);
                             String namechnNameValue = job.get("namechnName").toString();
                             Cell namechnNameCell = dataRow.createCell(2);
                             namechnNameCell.setCellValue(namechnNameValue);
                             String namechnCodeValue = "";
                             if(job.get("namechnCode") != null && !"".equals(job.get("namechnCode"))){
                                 namechnCodeValue = job.get("namechnCode").toString();
                             }
                             Cell namechnCodeCell = dataRow.createCell(3);
                             namechnCodeCell.setCellValue(namechnCodeValue);
                             for(int j = 0;j < carlineList.size(); j++){
                                 TCarline model1 = carlineList.get(j);
                                 Cell yxsjsCell = null;
                                 Cell qksCell = null;
                                 Cell rateCell = null;
                                 if(j == 0){
                                     yxsjsCell = dataRow.createCell(j+4);
                                     qksCell = dataRow.createCell(j+5);
                                     rateCell = dataRow.createCell(j+6);
                                 }else{
                                     yxsjsCell = dataRow.createCell(j*3+4);
                                     qksCell = dataRow.createCell(j*3+5);
                                     rateCell = dataRow.createCell(j*3+6);
                                 }
                                 yxsjsCell.setCellStyle(style); // 样式  
                                 qksCell.setCellStyle(style); // 样式  
                                 rateCell.setCellStyle(style); // 样式  
                                 //有效试驾数
                                 int yxsjsValue = 0;
                                 if(job.get("yxsjcs_"+model1.getId()) != null && !"null".equals(job.get("yxsjcs_"+model1.getId()))){
                                     yxsjsValue = Integer.parseInt(job.get("yxsjcs_"+model1.getId()).toString());
                                 }
                                 yxsjsCell.setCellValue(yxsjsValue);
                                 //潜客数
                                 int qksValue = 0;
                                 if(job.get("qks_"+model1.getId()) != null && !"null".equals(job.get("qks_"+model1.getId()))){
                                     qksValue = Integer.parseInt(job.get("qks_"+model1.getId()).toString());
                                 }
                                 qksCell.setCellValue(qksValue);
                                 //试驾率
                                 String rate = "0%";
                                 if(yxsjsValue > 0 && qksValue > 0){
                                     rate = numberFormat((float)yxsjsValue/(float)qksValue * 100);
                                 }
                                 rateCell.setCellValue(rate);
                             }
                             //全车型
                             Cell yxsjsCell = dataRow.createCell(carlineList.size() * 3 + 4);
                             Cell qksCell = dataRow.createCell(carlineList.size() * 3 + 5);
                             Cell rateCell = dataRow.createCell(carlineList.size() * 3 + 6);
                             //有效试驾数
                             int yxsjsValue = 0;
                             if(job.get("yxsjcs_qcx") != null && !"null".equals(job.get("yxsjcs_qcx"))){
                                 yxsjsValue = Integer.parseInt(job.get("yxsjcs_qcx").toString());
                             }
                             yxsjsCell.setCellValue(yxsjsValue);
                             //潜客数
                             int qksValue = 0;
                             if(job.get("qks_qcx") != null && !"null".equals(job.get("qks_qcx"))){
                                 qksValue = Integer.parseInt(job.get("qks_qcx").toString());
                             }
                             qksCell.setCellValue(qksValue);
                             //试驾率
                             String rate = "0%";
                             if(yxsjsValue > 0 && qksValue > 0){
                                 rate = numberFormat((float)yxsjsValue/(float)qksValue * 100);
                             }
                             rateCell.setCellValue(rate);
                        }
                    }
                }
            }

            response.reset();    
            response.setContentType("application/x-msdownload");    
            response.setHeader("Content-Disposition","attachment; filename="+new String("试驾率明细信息列表数据".getBytes("gb2312"),"ISO-8859-1")+sdf.format(new Date())+".xls");    
            ServletOutputStream outStream=null;    
            try{    
                outStream = response.getOutputStream();    
                wb.write(outStream);    
            }catch(Exception e)    
            {    
             e.printStackTrace();    
            }finally{    
                outStream.close();    
            }    

        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage());
            response_write("
        }

获取数据(Service层):
@Override
    public String getDriveRateDetailDateStr(Map paramMap) throws Exception {
        try {
            //获得所有的车系信息
            TCarline queryCarline = new TCarline();
            queryCarline.setIsdrive(1);
            List carlineList = this.tCarlineMapper.selectCarlineListByObj(queryCarline);
            //加载数据
            StringBuilder sumYxsjcsBuilder = new StringBuilder();
            StringBuilder sumQksBuilder = new StringBuilder();

            if(carlineList != null && carlineList.size() > 0){
                for (TCarline tCarline : carlineList) {
                    sumYxsjcsBuilder.append("SUM(CASE WHEN carline_model.t_carline_id = '"+tCarline.getId()+"' THEN 1 ELSE 0 END) AS 'yxsjcs_"+tCarline.getId()+"',");
                    sumQksBuilder.append("SUM(CASE WHEN dealer_potentialcustomer.t_carline_id = '"+tCarline.getId()+"' THEN dealer_potentialcustomer.number ELSE 0 END) AS 'qks_"+tCarline.getId()+"',");
                }
                sumYxsjcsBuilder.append("SUM(1) AS yxsjcs_qcx");
                sumQksBuilder.append("SUM(dealer_potentialcustomer.number) AS qks_qcx");
                paramMap.put("sumYxsjcs", sumYxsjcsBuilder.toString());
                paramMap.put("sumQks", sumQksBuilder.toString());
            }
            //设置limit值
            List> carDriveMap = this.iCarDriveMapper.getDriveRateDetailDate(paramMap);
            //拼接json数据格式
            if(carDriveMap != null && carDriveMap.size() > 0){
                String json = JSONObject.toJSONString(carDriveMap);
                return json;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

实例:
这里写图片描述
导出后为:
这里写图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值