POI导出EXCEL下载

心已赠人 2021-11-26 09:34 420阅读 0赞

Controller层

//获取指定日期内的预约数据
    @RequestMapping("getExcel")
        public void getReserveExcel(@RequestParam String dateBegin,
                                    @RequestParam String dateEnd,
                                    HttpServletResponse response) {
            Long dateBeginStamp=LocalDateUtils.isoToMills(dateBegin);
            Long dateEndStamp=LocalDateUtils.isoToMills(dateEnd);
            HSSFWorkbook wb=reserveService.getReservedExcel(dateBeginStamp, dateEndStamp);//获取工作簿
            OutputStream fos = null;
            try {
                response.setContentType("application/ms-excel;charset=UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename="
                        .concat(String.valueOf(URLEncoder.encode(wb.getSheetName(0)+".xlsx", "UTF-8"))));//设置文件名，现在是以第一张表为名称
                fos = response.getOutputStream();
                wb.write(fos);
            } catch (Exception ex) {
                ex.printStackTrace();
            }finally {
                try {
                    if (fos != null) {
                        fos.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

Service层

--------------------

public HSSFWorkbook getReservedExcel(Long dateBegin, Long dateEnd) {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            String fileName = LocalDateUtils.formatTimestamp(dateBegin, "yyyy年MM月dd日") +
                    "-" + LocalDateUtils.formatTimestamp(dateEnd, "yyyy年MM月dd日") +
                    "预约数据报表";
            List<Reserve> reserveList=reserveRepository.findAllByReserveConsumeDateGreaterThanEqualAndReserveConsumeDateLessThanEqualOrderByIdDesc(dateBegin,dateEnd);
            if(reserveList.size()==0){
                throw new ServiceException(ServiceError.ERROR_RESERVE_DATE);
            }
            HSSFSheet sheet=hssfWorkbook.createSheet(fileName);//创建工作簿
            HSSFCellStyle setBorder = hssfWorkbook.createCellStyle();//样式
            setBorder.setBorderBottom(BorderStyle.THIN); //下边框
            setBorder.setBorderLeft(BorderStyle.THIN);//左边框
            setBorder.setBorderTop(BorderStyle.THIN);//上边框
            setBorder.setBorderRight(BorderStyle.THIN);//右边框
            setBorder.setAlignment(HorizontalAlignment.CENTER); // 居中
    
            HSSFRow titleRow=sheet.createRow(0);
            titleRow.createCell(0).setCellValue("订单号");
            titleRow.createCell(1).setCellValue("客户姓名");
            titleRow.createCell(2).setCellValue("客户手机");
            titleRow.createCell(3).setCellValue("购买套餐内容");
            titleRow.createCell(4).setCellValue("预约时间");
            titleRow.createCell(5).setCellValue("预约型号");
            titleRow.createCell(6).setCellValue("备注");
            int lastRowNum=0;
            HSSFRow dataRow=null;
            for (Reserve reserve:reserveList){
                lastRowNum=sheet.getLastRowNum();
                dataRow=sheet.createRow(lastRowNum + 1);
                dataRow.createCell(0).setCellValue(reserve.getOrderNo());
                dataRow.createCell(1).setCellValue(reserve.getContactName());
                dataRow.createCell(2).setCellValue(reserve.getContactMobile());
                dataRow.createCell(3).setCellValue(reserve.getSpecName());
                dataRow.createCell(4).setCellValue(LocalDateUtils.formatTimestamp(reserve.getReserveConsumeDate(),"yyyy年MM月dd日"));
                dataRow.createCell(5).setCellValue(reserve.getInventoryTypeName());
                dataRow.createCell(6).setCellValue(reserve.getMemo());
                dataRow.getCell(0).setCellStyle(setBorder);
                dataRow.getCell(1).setCellStyle(setBorder);
                dataRow.getCell(2).setCellStyle(setBorder);
                dataRow.getCell(3).setCellStyle(setBorder);
                dataRow.getCell(4).setCellStyle(setBorder);
                dataRow.getCell(5).setCellStyle(setBorder);
                dataRow.getCell(6).setCellStyle(setBorder);
            }
            for(int i=0;i<=6;i++){
                titleRow.getCell(i).setCellStyle(setBorder);//边框
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 3);//列宽
            }
            return hssfWorkbook;
        }