因业务需要，需要做一个报表导出的功能，因为公司把导出封装了一个框架。但是只是一级表头。现在做的是二级表头，所以需要重新开发。
如下图：
学习了一下poi基本知识，前面几篇有介绍，思路其实很简单，表头加表中数据。
下面直接上代码。
jsp页面：
Controller层：
@Controller
@RequestMapping("/monthMark.do")
public class MonthMarkController extends BaseController {
@Resource(name="monthMarkService")
private MonthMarkService monthMarkService;
/**
* 月度评分表的导出
* @author yh
* @param response
* @return
*/
@RequestMapping(params="method=getDownLoad")
public @ResponseBody String getDownLoad(HttpServletResponse response){
response.setContentType("application/binary;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
try {
ServletOutputStream out = response.getOutputStream();
String fileName = "月度评分表";
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes(), "ISO8859-1")+ ".xls");
/*String[][] titles = {{"公司名称","考核类型","年、月","总分","名次","时效","","","","成本","","安全","","火灾","参考值"},
{"","","","","","当月提供车辆满足率","发车及时率","在途时效准点率","延误影响频次次数","吨公里倍率","整车价快件满载率","重大异常事故率",
"重大安全快件事故数","火灾损毁全事故数","总货量（吨）"}};*/
monthMarkService.getDownLoad(/*titles, */out);
return "success";
} catch (Exception e) {
e.printStackTrace();
return "导出失败";
}
}
}
Service层：
import java.util.List;
import javax.servlet.ServletOutputStream;
import com.yunda.app.base.BaseService;
import com.yunda.transMonitor.kbSupplierCheck.vo.MonthMark;
public interface MonthMarkService extends BaseService<MonthMark> {
List<MonthMark> monthMarkList(MonthMark monthMark);
void getDownLoad(ServletOutputStream out);
}
ServiceImpl层：
@Service(value="monthMarkService")
@Transactional(rollbackFor = Exception.class)
public class MonthMarkServiceImpl implements MonthMarkService{
@Resource(name="monthMarkDao")
private MonthMarkDao monthMarkDao;
@Autowired
private MonthMarkMapper monthMarkMapper;
@Override
public List<MonthMark> monthMarkList(MonthMark monthMark) {
List<MonthMark> list = monthMarkDao.monthMarkList(monthMark);
return null==list ? new ArrayList<MonthMark>() : list;
}
@SuppressWarnings("resource")
@Override
public void getDownLoad(ServletOutputStream out){
try{
//创建一个workbook
Workbook wb = new HSSFWorkbook();
//创建一个sheet
Sheet sheet = wb.createSheet("sheet1");
//excel页面风格，设置了水平居中和垂直居中
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//row行
Row row = null;
//声明列cell
Cell cell = null;
//创建表头
createTableHead(sheet, row, cell, cellStyle);
//查询需要导出的数据
MonthMark monthMark = new MonthMark();
List<MonthMark> lists = monthMarkDao.monthMarkList(monthMark);
// 表头弄下来后，第二行结束，将i赋值为2
int i = 2;
// 填充数据
initTableData(lists, sheet, row, cell, cellStyle, i);
//将文件输出到客户端浏览器
try {
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}catch (Exception e){
e.printStackTrace();
try {
throw new Exception("导出excel失败！");
} catch (Exception e1) {
e1.printStackTrace();
}
}
}
private void initTableData(List<MonthMark> lists, Sheet sheet,Row row, Cell cell, CellStyle cellStyle, int i) {
//将查出的数据写入excel
for(MonthMark map : lists){
writeBody(map,sheet,row,cell,cellStyle,i);
i++;
}
}
/**
* 向excel中插入数据
* @param map
* @param sheet
* @param row
* @param cell
* @param cellStyle
* @param i
*/
private void writeBody(MonthMark map, Sheet sheet, Row row,Cell cell, CellStyle cellStyle, int i) {
row = sheet.createRow((short) i);
for (int j = 0; j < 14; j++) {
switch (j) {
case 0:
cell = row.createCell(j);
cell.setCellValue(null==map.getCO_NM()?"":map.getCO_NM().toString());
cell.setCellStyle(cellStyle);
break;
case 1:
cell = row.createCell(j);
cell.setCellValue(null==map.getCHK_TYP()?"":map.getCHK_TYP().toString());
cell.setCellStyle(cellStyle);
break;
case 2:
cell = row.createCell(j);
cell.setCellValue(null==map.getYR_MO()?"":map.getYR_MO());
cell.setCellStyle(cellStyle);
break;
case 3:
cell = row.createCell(j);
cell.setCellValue(null==map.getMARKS()?"":map.getMARKS().toString());
cell.setCellStyle(cellStyle);
break;
case 4:
cell = row.createCell(j);
cell.setCellValue(null==map.getRN()?"":map.getRN().toString());
cell.setCellStyle(cellStyle);
break;
case 5:
cell = row.createCell(j);
cell.setCellValue(null==map.getCAR_FULL_ADEQ()?"":map.getCAR_FULL_ADEQ().toString());
cell.setCellStyle(cellStyle);
break;
case 6:
cell = row.createCell(j);
cell.setCellValue(null==map.getCAR_TLNS()?"":map.getCAR_TLNS().toString());
cell.setCellStyle(cellStyle);
break;
case 7:
cell = row.createCell(j);
cell.setCellValue(null==map.getTLNS_RDY_PT()?"":map.getTLNS_RDY_PT().toString());
cell.setCellStyle(cellStyle);
break;
case 8:
cell = row.createCell(j);
cell.setCellValue(null==map.getDLYD_FRQC_CNT()?"":map.getDLYD_FRQC_CNT().toString());
cell.setCellStyle(cellStyle);
break;
case 9:
cell = row.createCell(j);
cell.setCellValue(null==map.getTON_KM_RT()?"":map.getTON_KM_RT().toString());
cell.setCellStyle(cellStyle);
break;
case 10:
cell = row.createCell(j);
cell.setCellValue(null==map.getITM_LOD_RT()?"":map.getITM_LOD_RT().toString());
cell.setCellStyle(cellStyle);
break;
case 11:
cell = row.createCell(j);
cell.setCellValue(null==map.getEXCP_ACCDT_RT()?"":map.getEXCP_ACCDT_RT().toString());
cell.setCellStyle(cellStyle);
break;
case 12:
cell = row.createCell(j);
cell.setCellValue(null==map.getSECR_ACCDT_CNT()?"":map.getSECR_ACCDT_CNT().toString());
cell.setCellStyle(cellStyle);
break;
case 13:
cell = row.createCell(j);
cell.setCellValue(null==map.getBRKG_ACCDT_CNT()?"":map.getBRKG_ACCDT_CNT().toString());
cell.setCellStyle(cellStyle);
break;
case 14:
cell = row.createCell(j);
cell.setCellValue(null==map.getTOT_WGT()?"":map.getTOT_WGT().toString());
cell.setCellStyle(cellStyle);
break;
}
}
}
/**
* 创建表头
* @param sheet
* @param row
* @param cell
* @param cellStyle
* @param list
*/
private void createTableHead(Sheet sheet, Row row, Cell cell,CellStyle cellStyle) {
row = sheet.createRow(0);
sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));
cell = row.createCell(0);
cell.setCellValue("公司名称");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));
cell = row.createCell(1);
cell.setCellValue("考核类型");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));
cell = row.createCell(2);
cell.setCellValue("年、月");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,1,3,3));
cell = row.createCell(3);
cell.setCellValue("总分");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,1,4,4));
cell = row.createCell(4);
cell.setCellValue("名次");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,5,8));
cell = row.createCell(5);
cell.setCellValue("时效");
cell.setCellStyle(cellStyle);
cell = row.createCell(6);
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,9,10));
cell = row.createCell(9);
cell.setCellValue("成本");
cell.setCellStyle(cellStyle);
cell = row.createCell(10);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,11,12));
cell = row.createCell(11);
cell.setCellValue("安全");
cell.setCellStyle(cellStyle);
cell = row.createCell(12);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,13,13));
cell = row.createCell(13);
cell.setCellValue("火灾");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,14,14));
cell = row.createCell(14);
cell.setCellValue("参考值");
cell.setCellStyle(cellStyle);
//--------------------创建第二行-------------------------------
row = sheet.createRow(1);
sheet.addMergedRegion(new CellRangeAddress(1,1,5,5));
cell = row.createCell(5);
cell.setCellValue("当月提供车辆满足率");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,6,6));
cell = row.createCell(6);
cell.setCellValue("发车及时率");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,7,7));
cell = row.createCell(7);
cell.setCellValue("在途时效准点率");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,8,8));
cell = row.createCell(8);
cell.setCellValue("延误影响频次次数");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,9,9));
cell = row.createCell(9);
cell.setCellValue("吨公里倍率");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,10,10));
cell = row.createCell(10);
cell.setCellValue("整车价快件满载率");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,11,11));
cell = row.createCell(11);
cell.setCellValue("重大异常事故率");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,12,12));
cell = row.createCell(12);
cell.setCellValue("重大快件安全事故数");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,13,13));
cell = row.createCell(13);
cell.setCellValue("火灾损毁全事故数");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,14,14));
cell = row.createCell(14);
cell.setCellValue("总货量（吨）");
cell.setCellStyle(cellStyle);
//设置列的长度
sheet.setColumnWidth(0, 8000);
sheet.setColumnWidth(5, 5000);
sheet.setColumnWidth(6, 5000);
sheet.setColumnWidth(7, 5000);
sheet.setColumnWidth(8, 5000);
sheet.setColumnWidth(9, 5000);
sheet.setColumnWidth(10, 5000);
sheet.setColumnWidth(11, 5000);
sheet.setColumnWidth(12, 5000);
sheet.setColumnWidth(13, 5000);
sheet.setColumnWidth(14, 5000);
}
主要就是这些代码，数据也是从数据库查询的，然后直接用了查询数据的那个方法导入并赋值数据。
最终实现结果如下图：
完美！！！欢迎前来探讨学习~~
后来又做了一个三级表头的导出，也将代码附上，仅供学习。
package com.yunda.transMonitor.transMontIndex.controller;
/**
* @Description 运输监控指标报表(集团、分拨)Controller层
* @author yh
* @date 2019-07-25
*/
@Controller
@RequestMapping("/transMontIndex.do")
@Transactional(rollbackFor = Exception.class)
public class TransMontIndexController extends BaseController {
@Autowired
private TransMontIndexService tMontIndexService;
Logger log = Logger.getLogger(TransMontIndexController.class);
/**
* @Description 主页菜单index页面跳转----运输监控指标报表(集团、分拨)
*/
@RequestMapping(method=RequestMethod.GET)
public String forwardToPage(HttpServletResponse response){
return "transMontIndex/transMontIndexList";
}
@RequestMapping(params="method=pageQuery")
public void transPageQuery(HttpServletResponse response,TransMontIndex vo){
Page page =null;
try {
page = tMontIndexService.pageQuery(vo);
} catch (Exception e) {
log.error(e);
}
sendJsonDataToClient(page,DateUtil.STD_SEC, response);
}
/**
* 导出
* @param response
* @return
*/
@RequestMapping(params="method=transExport")
public @ResponseBody String transExport(HttpServletResponse response){
response.setContentType("application/binary;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
try {
ServletOutputStream out = response.getOutputStream();
String fileName = "运输监控指标报表（集团、分拨）";
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes(), "ISO8859-1")+ ".xls");
tMontIndexService.transExport(out);
return "success";
} catch (Exception e) {
e.printStackTrace();
return "导出失败";
}
}
/**
* 删除数据
* @param ids
* @param response
* @throws Exception
*/
@RequestMapping(params="method=deleteDatas")
public void deleteDatas(@RequestParam("ids") String ids,HttpServletResponse response) throws Exception{
Message msg=null;
msg = tMontIndexService.deleteByIds(ids);
sendJsonDataToClient(msg,response);
}
}
package com.yunda.transMonitor.transMontIndex.service;
import java.util.List;
import javax.servlet.ServletOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
/**
* @Description service层实现类
* @author yh
* @date 2019-07-25
*/
@Service(value="transMontIndexService")
@Transactional(rollbackFor = Exception.class)
public class TransMontIndexService extends CommonService{
@Autowired
private TransMontIndexDao tDao;
/**
* 查询数据
* @param vo
* @return
* @throws Exception
*/
public Page pageQuery(TransMontIndex vo) throws Exception {
return tDao.pageQuery(vo);
}
/**
* 删除数据
* @param ids
* @return
*/
public Message deleteByIds(String ids) {
String idStr="";
if(!StringUtil.isNotNull(ids)){
return new Message(false,"删除失败!");
}else{
if(ids.endsWith(",")){
idStr = ids.substring(0,ids.length()-1);
}else{
idStr = ids;
}
}
tDao.deleteByIds(idStr);
return new Message(true,"删除成功!");
}
/**
* 导出
* @param out
*/
public void transExport(ServletOutputStream out) {
try{
//创建一个workbook、sheet
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("sheet1");
//excel页面风格，设置了水平居中和垂直居中
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
//声明行、列
Row row = null;
Cell cell = null;
//创建表头
createTableHead(sheet, row, cell, cellStyle);
//查询要导出的数据
TransMontIndex vo = new TransMontIndex();
List<TransMontIndex> lists = tDao.transExport(vo);
// 表头第三行结束，将i赋值为3
int i = 3;
// 填充数据
initTableData(lists, sheet, row, cell, cellStyle, i);
//将文件输出到客户端浏览器
try {
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}catch(Exception e){
e.printStackTrace();
try {
throw new Exception("导出excel失败！");
} catch (Exception e1) {
e1.printStackTrace();
}
}
}
private void initTableData(List<TransMontIndex> lists, Sheet sheet,Row row, Cell cell, CellStyle cellStyle, int i) {
//将查出的数据写入excel
for (TransMontIndex list : lists) {
writeBody(list,sheet,row,cell,cellStyle,i);
i++;
}
}
/**
* 向excel中插入数据
* @param list
* @param sheet
* @param row
* @param cell
* @param cellStyle
* @param i
*/
private void writeBody(TransMontIndex list, Sheet sheet, Row row,Cell cell, CellStyle cellStyle, int i) {
row = sheet.createRow((short)i);
for (int j = 0; j < 60; j++) {
switch(j){
case 0:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 1:
cell = row.createCell(j);
cell.setCellValue(null == list.getYear()?"":list.getYear());
cell.setCellStyle(cellStyle);
break;
case 2:
cell = row.createCell(j);
cell.setCellValue(null == list.getMonth()?"":list.getMonth());
cell.setCellStyle(cellStyle);
break;
case 3:
cell = row.createCell(j);
cell.setCellValue(null == list.getAll_zb_cars()?"":list.getAll_zb_cars());
cell.setCellStyle(cellStyle);
break;
case 4:
cell = row.createCell(j);
cell.setCellValue(null == list.getAll_kb_cars()?"":list.getAll_kb_cars());
cell.setCellStyle(cellStyle);
break;
case 5:
cell = row.createCell(j);
cell.setCellValue(null == list.getAll_wd_cars()?"":list.getAll_wd_cars());
cell.setCellStyle(cellStyle);
break;
case 6:
cell = row.createCell(j);
cell.setCellValue(null == list.getAll_cars_total()?"":list.getAll_cars_total());
cell.setCellStyle(cellStyle);
break;
case 7:
cell = row.createCell(j);
cell.setCellValue(null == list.getAll_zb_exp()?"":list.getAll_zb_exp());
cell.setCellStyle(cellStyle);
break;
case 8:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 9:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 10:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 11:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 12:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 13:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 14:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 15:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 16:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 17:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 18:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 19:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 20:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 21:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 22:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 23:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 24:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 25:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 26:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 27:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 28:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 29:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 30:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 31:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 32:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 33:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 34:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 35:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 36:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 37:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 38:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 39:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 40:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 41:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 42:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 43:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 44:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 45:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 46:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 47:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 48:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 49:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 50:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 51:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 52:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 53:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 54:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 55:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 56:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 57:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 58:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 59:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
case 60:
cell = row.createCell(j);
cell.setCellValue(null == list.getSRC_DBCT_CD()?"":list.getSRC_DBCT_CD());
cell.setCellStyle(cellStyle);
break;
}
}
}
/**
* 表头的创建
* @param sheet
* @param row
* @param cell
* @param cellStyle
*/
private void createTableHead(Sheet sheet, Row row, Cell cell,CellStyle cellStyle) {
row = sheet.createRow(0);
sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
cell = row.createCell(0);
cell.setCellValue("集团/分拨");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 2, 1, 1));
cell = row.createCell(1);
cell.setCellValue("年份");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 2, 2, 2));
cell = row.createCell(2);
cell.setCellValue("月份");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 10));
cell = row.createCell(3);
cell.setCellValue("发车数据");
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellStyle(cellStyle);
cell = row.createCell(6);
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellStyle(cellStyle);
cell = row.createCell(8);
cell.setCellStyle(cellStyle);
cell = row.createCell(9);
cell.setCellStyle(cellStyle);
cell = row.createCell(10);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 25));
cell = row.createCell(11);
cell.setCellValue("发车数据（ZZ-ZZ）");
cell.setCellStyle(cellStyle);
cell = row.createCell(12);
cell.setCellStyle(cellStyle);
cell = row.createCell(13);
cell.setCellStyle(cellStyle);
cell = row.createCell(14);
cell.setCellStyle(cellStyle);
cell = row.createCell(15);
cell.setCellStyle(cellStyle);
cell = row.createCell(16);
cell.setCellStyle(cellStyle);
cell = row.createCell(17);
cell.setCellStyle(cellStyle);
cell = row.createCell(18);
cell.setCellStyle(cellStyle);
cell = row.createCell(19);
cell.setCellStyle(cellStyle);
cell = row.createCell(20);
cell.setCellStyle(cellStyle);
cell = row.createCell(21);
cell.setCellStyle(cellStyle);
cell = row.createCell(22);
cell.setCellStyle(cellStyle);
cell = row.createCell(23);
cell.setCellStyle(cellStyle);
cell = row.createCell(24);
cell.setCellStyle(cellStyle);
cell = row.createCell(25);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 26, 43));
cell = row.createCell(26);
cell.setCellValue("在途数据（ZZ-ZZ）");
cell.setCellStyle(cellStyle);
cell = row.createCell(27);
cell.setCellStyle(cellStyle);
cell = row.createCell(28);
cell.setCellStyle(cellStyle);
cell = row.createCell(29);
cell.setCellStyle(cellStyle);
cell = row.createCell(30);
cell.setCellStyle(cellStyle);
cell = row.createCell(31);
cell.setCellStyle(cellStyle);
cell = row.createCell(32);
cell.setCellStyle(cellStyle);
cell = row.createCell(33);
cell.setCellStyle(cellStyle);
cell = row.createCell(34);
cell.setCellStyle(cellStyle);
cell = row.createCell(35);
cell.setCellStyle(cellStyle);
cell = row.createCell(36);
cell.setCellStyle(cellStyle);
cell = row.createCell(37);
cell.setCellStyle(cellStyle);
cell = row.createCell(38);
cell.setCellStyle(cellStyle);
cell = row.createCell(39);
cell.setCellStyle(cellStyle);
cell = row.createCell(40);
cell.setCellStyle(cellStyle);
cell = row.createCell(41);
cell.setCellStyle(cellStyle);
cell = row.createCell(42);
cell.setCellStyle(cellStyle);
cell = row.createCell(43);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 44, 51));
cell = row.createCell(44);
cell.setCellValue("卸车数据");
cell.setCellStyle(cellStyle);
cell = row.createCell(45);
cell.setCellStyle(cellStyle);
cell = row.createCell(46);
cell.setCellStyle(cellStyle);
cell = row.createCell(47);
cell.setCellStyle(cellStyle);
cell = row.createCell(48);
cell.setCellStyle(cellStyle);
cell = row.createCell(49);
cell.setCellStyle(cellStyle);
cell = row.createCell(50);
cell.setCellStyle(cellStyle);
cell = row.createCell(51);
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 52, 59));
cell = row.createCell(52);
cell.setCellValue("安全数据");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 60, 60));
cell = row.createCell(60);
cell.setCellValue("数据质量");
cell.setCellStyle(cellStyle);
//--------------------------创建第二行-------------------------------
row = sheet.createRow(1);
sheet.addMergedRegion(new CellRangeAddress(1,1,3,6));
cell = row.createCell(3);
cell.setCellValue("发车总数（趟）（全部打凭证的车辆）");
cell.setCellStyle(cellStyle);
/*cell = row.createCell(4);
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellStyle(cellStyle);
cell = row.createCell(6);
cell.setCellStyle(cellStyle);*/
sheet.addMergedRegion(new CellRangeAddress(1,1,7,10));
cell = row.createCell(7);
cell.setCellValue("运费（万元）（全部打凭证的车辆）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,11,13));
cell = row.createCell(11);
cell.setCellValue("发车总数（趟）（60-60）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,14,16));
cell = row.createCell(14);
cell.setCellValue("重量（万吨）（60-60）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,17,19));
cell = row.createCell(17);
cell.setCellValue("运费（万元）（60-60）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,20,22));
cell = row.createCell(20);
cell.setCellValue("装车时长（分钟）（60-60）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,23,25));
cell = row.createCell(23);
cell.setCellValue("待发时长（分钟）（60-60）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,26,28));
cell = row.createCell(26);
cell.setCellValue("在途延误率（%）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,29,31));
cell = row.createCell(29);
cell.setCellValue("延误且影响频次（%）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,32,34));
cell = row.createCell(32);
cell.setCellValue("在途延误时长（分钟）（均值）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,35,37));
cell = row.createCell(35);
cell.setCellValue("在途均速km/h（规定）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,38,40));
cell = row.createCell(38);
cell.setCellValue("在途均速km/h（实际）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,41,43));
cell = row.createCell(41);
cell.setCellValue("时效罚款(万元)（不含688）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,44,47));
cell = row.createCell(44);
cell.setCellValue("卸车等待时长（分钟）（均值）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,48,51));
cell = row.createCell(48);
cell.setCellValue("卸车时长（分钟）（均值）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,52,55));
cell = row.createCell(52);
cell.setCellValue("万公里故障率（%）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,56,59));
cell = row.createCell(56);
cell.setCellValue("万公里事故率（%）");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(1,1,60,60));
cell = row.createCell(60);
cell.setCellValue("时间抓取完整率（%）（60-60）");
cell.setCellStyle(cellStyle);
//--------------------------创建第三行-------------------------------
row = sheet.createRow(2);
sheet.addMergedRegion(new CellRangeAddress(2,2,3,3));
cell = row.createCell(3);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,4,4));
cell = row.createCell(4);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,5,5));
cell = row.createCell(5);
cell.setCellValue("网点");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,6,6));
cell = row.createCell(6);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,7,7));
cell = row.createCell(7);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,8,8));
cell = row.createCell(8);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,9,9));
cell = row.createCell(9);
cell.setCellValue("网点");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,10,10));
cell = row.createCell(10);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,11,11));
cell = row.createCell(11);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,12,12));
cell = row.createCell(12);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,13,13));
cell = row.createCell(13);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,14,14));
cell = row.createCell(14);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,15,15));
cell = row.createCell(15);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,16,16));
cell = row.createCell(16);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,17,17));
cell = row.createCell(17);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,18,18));
cell = row.createCell(18);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,19,19));
cell = row.createCell(19);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,20,20));
cell = row.createCell(20);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,21,21));
cell = row.createCell(21);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,22,22));
cell = row.createCell(22);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,23,23));
cell = row.createCell(23);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,24,24));
cell = row.createCell(24);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,25,25));
cell = row.createCell(25);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,26,26));
cell = row.createCell(26);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,27,27));
cell = row.createCell(27);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,28,28));
cell = row.createCell(28);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,29,29));
cell = row.createCell(29);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,30,30));
cell = row.createCell(30);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,31,31));
cell = row.createCell(31);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,32,32));
cell = row.createCell(32);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,33,33));
cell = row.createCell(33);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,34,34));
cell = row.createCell(34);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,35,35));
cell = row.createCell(35);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,36,36));
cell = row.createCell(36);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,37,37));
cell = row.createCell(37);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,38,38));
cell = row.createCell(38);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,39,39));
cell = row.createCell(39);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,40,40));
cell = row.createCell(40);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,41,41));
cell = row.createCell(41);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,42,42));
cell = row.createCell(42);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,43,43));
cell = row.createCell(43);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,44,44));
cell = row.createCell(44);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,45,45));
cell = row.createCell(45);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,46,46));
cell = row.createCell(46);
cell.setCellValue("网点");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,47,47));
cell = row.createCell(47);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,48,48));
cell = row.createCell(48);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,49,49));
cell = row.createCell(49);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,50,50));
cell = row.createCell(50);
cell.setCellValue("网点");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,51,51));
cell = row.createCell(51);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,52,52));
cell = row.createCell(52);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,53,53));
cell = row.createCell(53);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,54,54));
cell = row.createCell(54);
cell.setCellValue("网点");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,55,55));
cell = row.createCell(55);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,56,56));
cell = row.createCell(56);
cell.setCellValue("正班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,57,57));
cell = row.createCell(57);
cell.setCellValue("卡班");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,58,58));
cell = row.createCell(58);
cell.setCellValue("网点");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,59,59));
cell = row.createCell(59);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(2,2,60,60));
cell = row.createCell(60);
cell.setCellValue("合计");
cell.setCellStyle(cellStyle);
}
}
package com.yunda.transMonitor.transMontIndex.dao;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
@Repository
public class TransMontIndexDao extends JcdfDaoSupport {
@Autowired
private TransMontIndexMapper tMapper;
/**查询数据
* @param vo
* @return
*/
public Page pageQuery(TransMontIndex vo) throws Exception {
return this.pageQuery(tMapper, "pageQuery", vo);
}
/**
* 删除
* @param ids
*/
public int deleteByIds(String ids) {
return tMapper.deleteByIds(ids);
}
/**
* 导出
* @param transMontIndex
* @return
*/
public List<TransMontIndex> transExport(TransMontIndex transMontIndex) {
return tMapper.transExport(transMontIndex);
}
}
package com.yunda.transMonitor.transMontIndex.tmapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
public interface TransMontIndexMapper {
/**查询数据
* @param vo
* @return
*/
public List<Map<String, Object>> pageQuery(TransMontIndex vo);
/**
* 删除
* @param ids
* @return
*/
public int deleteByIds(@Param("ids")String ids);
/**
* 导出
* @param vo
* @return
*/
public List<TransMontIndex> transExport(TransMontIndex vo);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yunda.transMonitor.transMontIndex.tmapper.TransMontIndexMapper">
<sql id="queryData">
select '集团' src_dbct_cd,
substr(stats_mo,1,4) year,
substr(stats_mo,5,6) month,
sum(all_zb_cars) all_zb_cars,sum(all_kb_cars) all_kb_cars,sum(all_wd_cars) all_wd_cars,
sum(all_zb_cars+all_kb_cars+all_wd_cars) all_cars_total,
sum(round(all_zb_exp/10000,2)) all_zb_exp,sum(round(all_kb_exp/10000,2))
all_kb_exp,sum(round(all_wd_exp/10000,2)) all_wd_exp,
sum(round(all_zb_exp/10000+all_kb_exp/10000+all_wd_exp/10000,2))
all_exp_total,
sum(zb_cars) zb_cars,sum(kb_cars) kb_cars,
sum(zb_cars+kb_cars) cars_total,
sum(round(zb_net_wgt/10000000,2)) zb_net_wgt,sum(round(kb_net_wgt/10000000,2)) kb_net_wgt,
sum(round(ifnull(zb_net_wgt/10000000,0)+ifnull(kb_net_wgt/10000000,0),2))
wgt_total,
sum(round(zb_exp/10000,2)) zb_exp,sum(round(kb_exp/10000,2)) kb_exp,
sum(round(zb_exp/10000+kb_exp/10000,2)) exp_total,
sum(zb_lod_mnt) zb_lod_mnt,sum(kb_lod_mnt) kb_lod_mnt,
sum(ifnull(zb_lod_mnt,0)+ifnull(kb_lod_mnt,0)) lod_mnt_total,
sum(zb_wait_mnt) zb_wait_mnt,sum(kb_wait_mnt) kb_wait_mnt,
sum(ifnull(zb_wait_mnt,0)+ifnull(kb_wait_mnt,0)) wait_mnt_total,
concat(round(sum(zb_dlyd_cars/zb_cars)*100,2),'%') zb_dlyd_pt,
concat(round(sum(kb_dlyd_cars/kb_cars)*100,2),'%') kb_dlyd_pt,
concat(round(sum(ifnull(zb_dlyd_cars/zb_cars,0)+ifnull(kb_dlyd_cars/kb_cars,0))*100,2),'%')
dlyd_pt_total,
concat(round(sum(zb_frqc_dlyd_cars/zb_cars)*100,2),'%') zb_frqc_dlyd,
concat(round(sum(kb_frqc_dlyd_cars/kb_cars)*100,2),'%') kb_frqc_dlyd,
concat(round(sum(ifnull(zb_frqc_dlyd_cars/zb_cars,0)+ifnull(kb_frqc_dlyd_cars/kb_cars,0))*100,2),'%')
frqc_dlyd_total,
sum(zb_dlyd_mnt) zb_dlyd_mnt,sum(kb_dlyd_mnt) kb_dlyd_mnt,
sum(ifnull(zb_dlyd_mnt,0)+ifnull(kb_dlyd_mnt,0)) dlyd_mnt_total,
round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0),2) zb_stip_speed,
round(ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2) kb_stip_speed,
(round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0)+ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2))
stip_speed_total,
round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0),2) zb_actl_speed,
round(ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2) kb_actl_speed,
(round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0)+ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2))
actl_speed_total,
sum(round(zb_pnlt/10000,2)) zb_pnlt,sum(round(kb_pnlt/10000,2)) kb_pnlt,
sum(ifnull(round(zb_pnlt/10000,2),0)+ifnull(round(kb_pnlt/10000,2),0))
pnlt_total,
sum(zb_uld_wait_mnt) zb_uld_wait_mnt,sum(kb_uld_wait_mnt) kb_uld_wait_mnt,sum(wd_uld_wait_mnt) wd_uld_wait_mnt,
round((ifnull(sum(zb_uld_wait_mnt),0)+ifnull(sum(kb_uld_wait_mnt),0)+ifnull(sum(wd_uld_wait_mnt),0))/
(ifnull(sum(all_zb_cars),0)+ifnull(sum(all_kb_cars),0)+ifnull(sum(all_wd_cars),0)),2)
uld_wait_mnt_total,
sum(zb_uld_mnt) zb_uld_mnt,sum(kb_uld_mnt) kb_uld_mnt,sum(wd_uld_mnt) wd_uld_mnt,
sum(ifnull(zb_uld_mnt,0)+ifnull(kb_uld_mnt,0)+ifnull(wd_uld_mnt,0)) uld_mnt_total,
concat(round(sum(zb_brk_cars/zb_mlg/10000)*100,2),'%') zb_brk_cars,
concat(round(sum(kb_brk_cars/kb_mlg/10000)*100,2),'%') kb_brk_cars,
concat(round(sum(wd_brk_cars/wd_mlg/10000)*100,2),'%') wd_brk_cars,
concat((round(sum(ifnull(zb_brk_cars/zb_mlg/10000,0)+ifnull(kb_brk_cars/kb_mlg/10000,0)+ifnull(wd_brk_cars/wd_mlg/10000,0))*100,2)),'%')
brk_cars_total,
concat(round(sum(zb_accdt_cars/zb_mlg/10000)*100,2),'%') zb_accdt_cars,
concat(round(sum(kb_accdt_cars/kb_mlg/10000)*100,2),'%') kb_accdt_cars,
concat(round(sum(wd_accdt_cars/wd_mlg/10000)*100,2),'%') wd_accdt_cars,
concat((round(sum(ifnull(zb_accdt_cars/zb_mlg/10000,0)+ifnull(kb_accdt_cars/kb_mlg/10000,0)+ifnull(wd_accdt_cars/wd_mlg/10000,0))*100,2)),'%')
accdt_cars_total,
concat(round(sum(actl_tms/stip_tms)*100,2),'%') tms_total
from bdrpt.kd_car_trans_mont_stats
where 1=1
<if test="stats_mo != null and stats_mo != ''" >
and concat(SUBSTR(stats_mo,1,4),'-',SUBSTR(stats_mo,5,6))=#{stats_mo}
</if>
<if test='SRC_DBCT_CD == "2"'>
and src_dbct_cd = '分拨'
</if>
GROUP BY substr(stats_mo,5,6)
UNION
select l.location_name src_dbct_cd,
substr(stats_mo,1,4) year,
substr(stats_mo,5,6) month,
all_zb_cars,all_kb_cars,all_wd_cars,
(all_zb_cars+all_kb_cars+all_wd_cars) all_cars_total,
round(all_zb_exp/10000,2) all_zb_exp,round(all_kb_exp/10000,2)
all_kb_exp,round(all_wd_exp/10000,2) all_wd_exp,
round(all_zb_exp/10000+all_kb_exp/10000+all_wd_exp/10000,2)
all_exp_total,
zb_cars,kb_cars,
(zb_cars+kb_cars) cars_total,
round(zb_net_wgt/10000000,2) zb_net_wgt,round(kb_net_wgt/10000000,2) kb_net_wgt,
round(ifnull(zb_net_wgt/10000000,0)+ifnull(kb_net_wgt/10000000,0),2)
wgt_total,
round(zb_exp/10000,2) zb_exp,round(kb_exp/10000,2) kb_exp,
round(zb_exp/10000+kb_exp/10000,2) exp_total,
zb_lod_mnt,kb_lod_mnt,
(ifnull(zb_lod_mnt,0)+ifnull(kb_lod_mnt,0)) lod_mnt_total,
zb_wait_mnt,kb_wait_mnt,
(ifnull(zb_wait_mnt,0)+ifnull(kb_wait_mnt,0)) wait_mnt_total,
concat(round(zb_dlyd_cars/zb_cars*100,2),'%') zb_dlyd_pt,
concat(round(kb_dlyd_cars/kb_cars*100,2),'%') kb_dlyd_pt,
concat(round((ifnull(zb_dlyd_cars/zb_cars,0)+ifnull(kb_dlyd_cars/kb_cars,0))*100,2),'%')
dlyd_pt_total,
concat(round(zb_frqc_dlyd_cars/zb_cars*100,2),'%') zb_frqc_dlyd,
concat(round(kb_frqc_dlyd_cars/kb_cars*100,2),'%') kb_frqc_dlyd,
concat(round((ifnull(zb_frqc_dlyd_cars/zb_cars,0)+ifnull(kb_frqc_dlyd_cars/kb_cars,0))*100,2),'%')
frqc_dlyd_total,
zb_dlyd_mnt,kb_dlyd_mnt,
(ifnull(zb_dlyd_mnt,0)+ifnull(kb_dlyd_mnt,0)) dlyd_mnt_total,
round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0),2) zb_stip_speed,
round(ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2) kb_stip_speed,
(round(ifnull(sum(zb_sys_mlg)/sum(zb_stip_mnt),0)+ifnull(sum(kb_sys_mlg)/sum(kb_stip_mnt),0),2))
stip_speed_total,
round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0),2) zb_actl_speed,
round(ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2) kb_actl_speed,
(round(ifnull(sum(zb_est_mlg)/sum(zb_actl_mnt),0)+ifnull(sum(kb_est_mlg)/sum(kb_actl_mnt),0),2))
actl_speed_total,
round(zb_pnlt/10000,2) zb_pnlt,round(kb_pnlt/10000,2) kb_pnlt,
(ifnull(round(zb_pnlt/10000,2),0)+ifnull(round(kb_pnlt/10000,2),0))
pnlt_total,
zb_uld_wait_mnt,kb_uld_wait_mnt,wd_uld_wait_mnt,
round((ifnull(sum(zb_uld_wait_mnt),0)+ifnull(sum(kb_uld_wait_mnt),0)+ifnull(sum(wd_uld_wait_mnt),0))/
(ifnull(sum(all_zb_cars),0)+ifnull(sum(all_kb_cars),0)+ifnull(sum(all_wd_cars),0)),2)
uld_wait_mnt_total,
zb_uld_mnt,kb_uld_mnt,wd_uld_mnt,
(ifnull(zb_uld_mnt,0)+ifnull(kb_uld_mnt,0)+ifnull(wd_uld_mnt,0)) uld_mnt_total,
concat(round(zb_brk_cars/zb_mlg/10000*100,2),'%') zb_brk_cars,
concat(round(kb_brk_cars/kb_mlg/10000*100,2),'%') kb_brk_cars,
concat(round(wd_brk_cars/wd_mlg/10000*100,2),'%') wd_brk_cars,
concat((round((ifnull(zb_brk_cars/zb_mlg/10000,0)+ifnull(kb_brk_cars/kb_mlg/10000,0)+ifnull(wd_brk_cars/wd_mlg/10000,0))*100,2)),'%')
brk_cars_total,
concat(round(zb_accdt_cars/zb_mlg/10000*100,2),'%') zb_accdt_cars,
concat(round(kb_accdt_cars/kb_mlg/10000*100,2),'%') kb_accdt_cars,
concat(round(wd_accdt_cars/wd_mlg/10000*100,2),'%') wd_accdt_cars,
concat((round((ifnull(zb_accdt_cars/zb_mlg/10000,0)+ifnull(kb_accdt_cars/kb_mlg/10000,0)+ifnull(wd_accdt_cars/wd_mlg/10000,0))*100,2)),'%')
accdt_cars_total,
concat(round(actl_tms/stip_tms*100,2),'%') tms_total
from bdrpt.kd_car_trans_mont_stats t LEFT JOIN bdkdbase.location l on t.src_dbct_cd = l.location_xid
where 1=1
<if test="stats_mo != null and stats_mo != ''" >
and concat(SUBSTR(stats_mo,1,4),'-',SUBSTR(stats_mo,5,6))=#{stats_mo}
</if>
<if test='SRC_DBCT_CD == "1"'>
and src_dbct_cd = '集团'
</if>
<if test="SRC_DBCT_NM != null and SRC_DBCT_NM != ''">
and src_dbct_cd = #{SRC_DBCT_NM}
</if>
GROUP BY src_dbct_cd,substr(stats_mo,5,6)
</sql>
<!-- 查询数据 -->
<select id="pageQuery" resultType="map">
<include refid="queryData"></include>
</select>
<!-- 导出数据 -->
<select id="transExport" parameterType="com.yunda.transMonitor.transMontIndex.vo.TransMontIndex"
resultType="com.yunda.transMonitor.transMontIndex.vo.TransMontIndex">
<include refid="queryData"></include>
</select>
<!--删除 -->
<delete id="deleteByIds" >
delete from kd_car_trans_mont_stats where src_dbct_cd IN (${ids})
</delete>
</mapper>
//删除功能
function tMontIndexDelete() {
var grid = mini.get("transMontIndexDatagrid");
var rows = grid.getSelecteds();
if (rows.length > 0) {
mini.confirm("确定删除所选记录?", "确认", function(action){
if (action == "ok") {
var ids = [];
for (var i = 0; i < rows.length; i++) {
var r = rows[i];
ids.push(r.SRC_DBCT_CD);
}
var id = ids.join(',');
grid.loading("操作中，请稍后......");
$.ajax({
url: "transMontIndex.do?method=deleteDatas",
type: "POST",
data: {"ids":id},
success: function (data) {
grid.unmask();
var rd = $.parseJSON(data);
mini.alert(rd.msg, '提示消息', function(){
grid.reload();
});
}
});
}
});
} else {
mini.alert("请选中一条记录");
}
}
/*当【集团-分拨】查询框选择“集团”时，分拨中心置灰，不能输入。*/
function onvaluechanged(){
var select = mini.get("TRANS_FB").value;
var dbctNM = new mini.Form("#DBCT_NM");
if(select == "1"){
dbctNM.setEnabled(false);
}else{
dbctNM.setEnabled(true);
}
}
//导出
function tMontIndexExport(){
var total = mini.get("transMontIndexDatagrid").getTotalCount();
if (total <= 0) {
mini.alert("没有可以导出的数据!", "提示消息");
return;
}
mini.confirm("确定导出数据吗？", "提示消息", function(action) {
window.location.href='transMontIndex.do?method=transExport';
});
}
由于自己发现了一个错误，就是通过查询筛选，进行导出时，会把所有数据都导出来，并不合理，然后试着修改，部分修改代码如下：
另外前台传参的字符串可能是乱码，可以这样解决：
这样就解决通过筛选条件进行导出了，同样二级表头也需要修改，这里就不做一一截图了。
后来测出一个问题，查询条件是中文的话，页面能显示数据，但是导出后没有数据，并且是自己电脑可以导出，上测试环境就导不出数据。找了半天原因。
传中文查询乱码问题 则需要对要传的参数进行二次编码
例如 window.location.href ="/xx.jsp?name="+name+"";
这样子则会乱码
改成
window.location.href ="/xx.jsp?name="+ encodeURI(encodeURI(name))+"";
在接收的jsp页面 或者是controller 中进行解码
String name = java.net.URLDecoder.decode(request.getParameter(“name”), “utf-8”);
这样子即解决在用 window.location.href 传中文的乱码问题
扫一扫
1236
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
