//导出 :通过sxss实现大数据量的导出,get方式
@Override
public Response exportTransPerspectiveData( ReqGetTransPerspectiveDataDTO req, HttpServletResponse response) throws Exception {
DelivertoolResult result = getExportPerspectiveData(req);//获取数据源
if (result.getRs()==0) {
return Response.status(Response.Status.BAD_REQUEST).entity(result.getRsdesp()).build();
} else {
try {
SXSSFWorkbook wb = new SXSSFWorkbook();
wb.setCompressTempFiles(true);
Sheet sheet = wb.createSheet("sheet0");
Object resultMessage = result.getResultMessage();
if (null == resultMessage || resultMessage.equals("[]")) {
} else {
List<String> keyList = realTimeDataService.getRtdataKeys("transPerspective");//获取自定义列表字段key用于拼get方法
List<String> nameList = realTimeDataService.getRtdataNames("transPerspective");//获取自定义列名
generateTitle(nameList,sheet);
List<ResGetRootPerspectiveDataDTO> rowDataList = JSON.parseArray(resultMessage.toString(), ResGetRootPerspectiveDataDTO.class);
int i = 1;
for (ResGetRootPerspectiveDataDTO dto : rowDataList) {
Row row = sheet.createRow(i);
int j = 0;
for (String key : keyList) {
row.createCell(j++).setCellValue(executeGetter(dto, key,dto.getPin(),j));
}
i++;
}
}
//SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//String fileName = format.format(new Date()) + "_转化透视报表.xlsx";
String time = req.getDateRange();
if(time.equals("today")){
time="今日";
}else if(time.equals("yesterday")){
time="昨日";
}else if(time.equals("thisweek")){
time="本周";
}else if(time.equals("lastweek")){
time="上周";
}else if(time.equals("thismonth")){
time="本月";
}
String level1 = req.getLevel1();
level1 = formateLevelName(level1);
String fileName = time + "_" + level1;
String level2 = req.getLevel2();
if(StringUtils.isNotBlank(level2)){
level2 = formateLevelName(level2);
fileName = fileName + "_"+level2;
}
fileName = fileName + ".xlsx";
ServletOutputStream outputStream = response.getOutputStream();
response.setBufferSize(1024*10240);//差不多10M的数据量,再大下载的EXCEL就会显示为压缩包,要根据文件大小自行设置,因为大小不止和行数有关,还和列数以及其中的内容有关
wb.write(outputStream);
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/vnd.ms-excel");
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
return Response.status(Response.Status.BAD_REQUEST).entity(null).build();
}
}
return Response.status(Response.Status.OK).entity(null).build();
}
//对特定的表格值进行处理,j表示第几列,如果j是第一列,且数据类型为站点类型,则第一列的值为站点“ID+"_"+站点名称”
public String executeGetter(Object obj, String key,String pin,int j) {
try {
if(null!=pin && pin.equals("Y") && j==1){
Object value = obj.getClass().getMethod("get" + ShortStringTool.uppercaseFirstLetter(key)).invoke(obj);
String siteId = (String) obj.getClass().getMethod("getCodeId").invoke(obj);
if (value == null) {
return "";
} else {
if(StringUtils.isNotBlank(siteId) && !siteId.equals("total")){
return siteId + "_" + value;
}else{
return "" + value;
}
}
}else{
Object value = obj.getClass().getMethod("get" + ShortStringTool.uppercaseFirstLetter(key)).invoke(obj);
if (value == null) {
return "";
} else {
return "" + value;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
public void generateTitle(List<String> nameList, Sheet sheet) {
Row row = sheet.createRow(0);
int j = 0;
for (String name : nameList) {
row.createCell(j++).setCellValue(name); // 转化表头
}
}
@Override
public Response exportTransPerspectiveData( ReqGetTransPerspectiveDataDTO req, HttpServletResponse response) throws Exception {
DelivertoolResult result = getExportPerspectiveData(req);//获取数据源
if (result.getRs()==0) {
return Response.status(Response.Status.BAD_REQUEST).entity(result.getRsdesp()).build();
} else {
try {
SXSSFWorkbook wb = new SXSSFWorkbook();
wb.setCompressTempFiles(true);
Sheet sheet = wb.createSheet("sheet0");
Object resultMessage = result.getResultMessage();
if (null == resultMessage || resultMessage.equals("[]")) {
} else {
List<String> keyList = realTimeDataService.getRtdataKeys("transPerspective");//获取自定义列表字段key用于拼get方法
List<String> nameList = realTimeDataService.getRtdataNames("transPerspective");//获取自定义列名
generateTitle(nameList,sheet);
List<ResGetRootPerspectiveDataDTO> rowDataList = JSON.parseArray(resultMessage.toString(), ResGetRootPerspectiveDataDTO.class);
int i = 1;
for (ResGetRootPerspectiveDataDTO dto : rowDataList) {
Row row = sheet.createRow(i);
int j = 0;
for (String key : keyList) {
row.createCell(j++).setCellValue(executeGetter(dto, key,dto.getPin(),j));
}
i++;
}
}
//SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//String fileName = format.format(new Date()) + "_转化透视报表.xlsx";
String time = req.getDateRange();
if(time.equals("today")){
time="今日";
}else if(time.equals("yesterday")){
time="昨日";
}else if(time.equals("thisweek")){
time="本周";
}else if(time.equals("lastweek")){
time="上周";
}else if(time.equals("thismonth")){
time="本月";
}
String level1 = req.getLevel1();
level1 = formateLevelName(level1);
String fileName = time + "_" + level1;
String level2 = req.getLevel2();
if(StringUtils.isNotBlank(level2)){
level2 = formateLevelName(level2);
fileName = fileName + "_"+level2;
}
fileName = fileName + ".xlsx";
ServletOutputStream outputStream = response.getOutputStream();
response.setBufferSize(1024*10240);//差不多10M的数据量,再大下载的EXCEL就会显示为压缩包,要根据文件大小自行设置,因为大小不止和行数有关,还和列数以及其中的内容有关
wb.write(outputStream);
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/vnd.ms-excel");
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
return Response.status(Response.Status.BAD_REQUEST).entity(null).build();
}
}
return Response.status(Response.Status.OK).entity(null).build();
}
//对特定的表格值进行处理,j表示第几列,如果j是第一列,且数据类型为站点类型,则第一列的值为站点“ID+"_"+站点名称”
public String executeGetter(Object obj, String key,String pin,int j) {
try {
if(null!=pin && pin.equals("Y") && j==1){
Object value = obj.getClass().getMethod("get" + ShortStringTool.uppercaseFirstLetter(key)).invoke(obj);
String siteId = (String) obj.getClass().getMethod("getCodeId").invoke(obj);
if (value == null) {
return "";
} else {
if(StringUtils.isNotBlank(siteId) && !siteId.equals("total")){
return siteId + "_" + value;
}else{
return "" + value;
}
}
}else{
Object value = obj.getClass().getMethod("get" + ShortStringTool.uppercaseFirstLetter(key)).invoke(obj);
if (value == null) {
return "";
} else {
return "" + value;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
public void generateTitle(List<String> nameList, Sheet sheet) {
Row row = sheet.createRow(0);
int j = 0;
for (String name : nameList) {
row.createCell(j++).setCellValue(name); // 转化表头
}
}