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;
}实例：
导出后为：
扫一扫
470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
