@RequestMapping("export_excel")
@ResponseBody
public AjaxResult exportExcel(HttpServletRequest request,BaseParams param, String keyword,String prog,String bgTime,String edTime,
HttpServletResponse response) throws FileUploadException, IOException{
AjaxResult<Object> result = new AjaxResult<Object>();
if(StringUtils.isEmpty(bgTime) || StringUtils.isEmpty(edTime)){
result.setMsg("筛选时间不能为空!");
result.setSuccess(false);
return result;
}
List<Date> dates = null;
try {
dates = DateKit.getDates(bgTime, edTime);
} catch (ParseException e1) {
e1.printStackTrace();
}
Workbook workbook = new XSSFWorkbook();
workbook.createCellStyle().setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
Sheet sheet = workbook.createSheet("门店播放器使用状态数据明细");
int j = -1;
sheet.setColumnWidth(0, 3000);
sheet.setColumnWidth(1, 3000);
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 6000);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(5, 5000);
Row row = sheet.createRow(0);
row.createCell(++j).setCellValue("日期");
row.createCell(++j).setCellValue("店铺编码");
row.createCell(++j).setCellValue("店铺名称");
row.createCell(++j).setCellValue("开始运行时间");
row.createCell(++j).setCellValue("断开时间");
row.createCell(++j).setCellValue("播放时长(分钟)");
Long userId = UserContext.getCurrent() != null ? UserContext.getCurrent() : LONG_INSTALL;
List<HmShopUseStatusDetailListVo> data = new ArrayList<HmShopUseStatusDetailListVo>();
//查询出要导出用户下的所有门店当前的登录状态
Map<Long,HmShopUseStatusListVo> map = new HashMap<Long,HmShopUseStatusListVo>();
List<HmShopUseStatusListVo> list = hmShopService.shopUserStatusExportList(userId, prog, keyword);
for (HmShopUseStatusListVo hmShopUseStatusListVo : list) {
map.put(hmShopUseStatusListVo.getShopId(), hmShopUseStatusListVo);
}
//查询出所有数据
List<HmShopUseStatusDetailListVo> shopDetails = hmShopService.findAllByDate(userId, bgTime, edTime);
if(shopDetails != null && shopDetails.size() > 0){
for (HmShopUseStatusDetailListVo detail : shopDetails) {
String thisProg = map.get(detail.getShopId()).getProg();
String shopCode = map.get(detail.getShopId()).getShopCode();
String shopName = map.get(detail.getShopId()).getShopName();
if(detail.getUserId() != null){
detail.setProg(thisProg);
data.add(detail);
}else{
detail.setShopCode(shopCode);
detail.setShopName(shopName);
detail.setLoginTime(null);
detail.setProg(thisProg);
data.add(detail);
}
}
}
int i = 1;
String shopCode2 = null;
for (HmShopUseStatusDetailListVo vo : data) {
String shopCode1 = vo.getShopCode();
j = -1;
Row bodyRow = sheet.createRow(i);
bodyRow.createCell(++j).setCellValue(vo.getNowDate().toString() != null ? vo.getNowDate().toString() : "");
bodyRow.createCell(++j).setCellValue(vo.getShopCode() != null ? vo.getShopCode() : "");
bodyRow.createCell(++j).setCellValue(vo.getShopName()!= null ? vo.getShopName() : "");
if(vo.getlTime() != null){
vo.setLoginTime(DateKit.getStringFromDate14(vo.getlTime()));
}
bodyRow.createCell(++j).setCellValue(vo.getLoginTime() != null ? vo.getLoginTime() : "未登录");
if(vo.getLoginTime() == null){
bodyRow.createCell(++j).setCellValue("未登录");
bodyRow.createCell(++j).setCellValue("未登录");
if(vo.getLoginTime() == null){
XSSFCell curCell = (XSSFCell) bodyRow.getCell(3);
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.RED.index);//绿 字
cellStyle.setFont(font);
curCell.setCellStyle(cellStyle);
}
if(vo.getqTime() == null){
XSSFCell curCell = (XSSFCell) bodyRow.getCell(4);
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.RED.index);//绿 字
cellStyle.setFont(font);
curCell.setCellStyle(cellStyle);
}
if(vo.getDuration() == null){
XSSFCell curCell = (XSSFCell) bodyRow.getCell(5);
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.RED.index);//绿 字
cellStyle.setFont(font);
curCell.setCellStyle(cellStyle);
}
}else{
if(vo.getqTime() != null){
vo.setQuitTime(DateKit.getStringFromDate14(vo.getqTime()));
}
//当前时间
Date now = new Date();
SimpleDateFormat sf = new SimpleDateFormat("yyyyMMdd");
//获取今天的日期
String nowDay = sf.format(now);
//对比的时间
String day = sf.format(DateKit.getDate(vo.getLoginTime()));//vo.getLoginTime();
if(!shopCode1.equals(shopCode2) && "online".equals(vo.getProg()) && day.equals(nowDay)){
bodyRow.createCell(++j).setCellValue(vo.getQuitTime() != null ? vo.getQuitTime() : "正在运行");
bodyRow.createCell(++j).setCellValue((vo.getDuration() != null ? vo.getDuration().toString() : "正在运行"));
if(vo.getqTime() == null){
XSSFCell curCell = (XSSFCell) bodyRow.getCell(4);
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.GREEN.index);//绿 字
cellStyle.setFont(font);
curCell.setCellStyle(cellStyle);
}
if(vo.getDuration() == null){
XSSFCell curCell = (XSSFCell) bodyRow.getCell(5);
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.GREEN.index);//绿 字
cellStyle.setFont(font);
curCell.setCellStyle(cellStyle);
}
}else{
bodyRow.createCell(++j).setCellValue(vo.getQuitTime() != null ? vo.getQuitTime() : "非正常关闭");
bodyRow.createCell(++j).setCellValue((vo.getDuration() != null ? vo.getDuration().toString() : "非正常关闭"));
if(vo.getqTime() == null){
XSSFCell curCell = (XSSFCell) bodyRow.getCell(4);
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.RED.index);//绿 字
cellStyle.setFont(font);
curCell.setCellStyle(cellStyle);
}
if(vo.getDuration() == null){
XSSFCell curCell = (XSSFCell) bodyRow.getCell(5);
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
font.setColor(HSSFColor.RED.index);//绿 字
cellStyle.setFont(font);
curCell.setCellStyle(cellStyle);
}
}
}
shopCode2 = shopCode1;
i++;
}
try {
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename="+ new String("门店播放器使用状态.xlsx".getBytes("UTF-8"), "iso-8859-1"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
result.setMsg("数据导出成功!");
result.setSuccess(true);
return result;
}
Java Excel导出随记
最新推荐文章于 2022-08-01 15:50:46 发布