导出代码实现

导出代码实现

    @SuppressWarnings("resource")
    @RequestMapping(value = "/maintenanceExport", method = RequestMethod.POST)
    @ApiOperation(value = "维保记录导出", notes = "维保记录导出")
    public ResultDTO<String> export(HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {
        VehicleMaintenanceQuery vehicleMaintenanceQuery = new VehicleMaintenanceQuery();
        vehicleMaintenanceQuery.setMerchantId(loginInfoService.getMerchantId());
        if(StringUtils.isNotBlank(request.getParameter("startDate")) && StringUtils.isNotBlank(request.getParameter("endDate"))){
            vehicleMaintenanceQuery.setStartDate(sdf.parse(request.getParameter("startDate")));
            vehicleMaintenanceQuery.setEndDate(sdf.parse(request.getParameter("endDate")));
        }
        vehicleMaintenanceQuery.setMaintenanceAddress(StringUtils.isBlank(request.getParameter("maintenanceAddress"))?"":request.getParameter("maintenanceAddress"));
        vehicleMaintenanceQuery.setPlateNumber(StringUtils.isBlank(request.getParameter("plateNumber"))?"":request.getParameter("plateNumber"));
        vehicleMaintenanceQuery.setType(StringUtils.isBlank(request.getParameter("type"))?"":request.getParameter("type"));
        vehicleMaintenanceQuery.setInvoiceStatus(StringUtils.isBlank(request.getParameter("invoiceStatus"))?"":request.getParameter("invoiceStatus"));
        vehicleMaintenanceQuery.setPageSize(100000);
        if (vehicleMaintenanceQuery.getEndDate() != null) {
            Date dateAdd = new Date(vehicleMaintenanceQuery.getEndDate().getTime() + 24 * 3600 * 1000);
            vehicleMaintenanceQuery.setEndDate(dateAdd);
        }
        logger.info("vehicleMaintenanceQuery={}", vehicleMaintenanceQuery);
        BizReturn<PageResult<VehicleMaintenanceDto>> bizReturn = magicCubeMaintenanceService.queryMaintenanceInfo(vehicleMaintenanceQuery);
        List<VehicleMaintenanceDto> maintenanceDtos = bizReturn.R.getData();

        String fileName = "维保记录导出_" + sdf.format(new Date()) + ".xlsx";
        response.setHeader("Content-disposition",
                "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));// 设置文件头编码格式
        response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");// 设置类型
        response.setHeader("Cache-Control", "no-cache");// 设置头
        response.setDateHeader("Expires", 0);// 设置日期头

        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFSheet sheet = workbook.createSheet();
        CellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

        int rowNum = 1;
        if (maintenanceDtos != null && maintenanceDtos.size() > 0) {
            for (VehicleMaintenanceDto vehicleMaintenanceDto : maintenanceDtos) {
                ExcelUtil.excelHeadMaintenanceRepair(sheet, cellStyle);
                Row row = sheet.createRow(rowNum);
                ExcelUtil.setMaintenanceRepair(vehicleMaintenanceDto, row);
                rowNum++;
            }
        } else {
            VehicleMaintenanceDto vehicleMaintenanceDto = new VehicleMaintenanceDto();
            ExcelUtil.excelHeadMaintenanceRepair(sheet, cellStyle);
            Row row = sheet.createRow(rowNum);
            ExcelUtil.setMaintenanceRepair(vehicleMaintenanceDto, row);
            rowNum++;
        }
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
        return null;
    }

以下代码实现:

ExcelUtil.excelHeadMaintenanceRepair(sheet, cellStyle);
Row row = sheet.createRow(rowNum);
ExcelUtil.setMaintenanceRepair(vehicleMaintenanceDto, row);

ExcelUtil的工具类实现
ExcelUtil.excelHeadMaintenanceRepair(sheet, cellStyle);方法实现

public static void excelHeadMaintenanceRepair(XSSFSheet sheet, CellStyle cellStyle) {
        cellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
        Row row = sheet.createRow(0);

        Cell cel0 = row.createCell(0);
        cel0.setCellStyle(cellStyle);
        cel0.setCellValue("流水号");

        Cell cell = row.createCell(1);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("车牌号");

        Cell cel2 = row.createCell(2);
        cel2.setCellStyle(cellStyle);
        cel2.setCellValue("维修保养类型");

        Cell cel3 = row.createCell(3);
        cel3.setCellStyle(cellStyle);
        cel3.setCellValue("车辆行驶里程");

        Cell cel4 = row.createCell(4);
        cel4.setCellStyle(cellStyle);
        cel4.setCellValue("费用总计");

        Cell cel5 = row.createCell(5);
        cel5.setCellStyle(cellStyle);
        cel5.setCellValue("收票状态");

        Cell cel6 = row.createCell(6);
        cel6.setCellStyle(cellStyle);
        cel6.setCellValue("维保时间");

        Cell cel7 = row.createCell(7);
        cel7.setCellStyle(cellStyle);
        cel7.setCellValue("维修厂名称");

        Cell cel8 = row.createCell(8);
        cel8.setCellStyle(cellStyle);
        cel8.setCellValue("操作人");

        Cell cel9 = row.createCell(9);
        cel9.setCellStyle(cellStyle);
        cel9.setCellValue("创建时间");
    }

ExcelUtil.setMaintenanceRepair(vehicleMaintenanceDto, row);的实现

/**
     * 维保记录导出
     * @param vehicleMaintenanceDto
     * @param row
     */
    public static void setMaintenanceRepair(VehicleMaintenanceDto vehicleMaintenanceDto, Row row){
        Cell cel0 = row.createCell(0);
        if(StringUtils.isEmpty(vehicleMaintenanceDto.getMaintenanceCode())){
            cel0.setCellValue("");
        }else{
            cel0.setCellValue(vehicleMaintenanceDto.getMaintenanceCode());
        }
        Cell cell = row.createCell(1);
        if(StringUtils.isEmpty(vehicleMaintenanceDto.getPlateNumber())){
            cell.setCellValue("");
        }else{
            cell.setCellValue(vehicleMaintenanceDto.getPlateNumber());
        }
        Cell cel2 = row.createCell(2);
        if(StringUtils.isEmpty(vehicleMaintenanceDto.getType())){
            cel2.setCellValue("");
        }else if("1".equals(vehicleMaintenanceDto.getType())){
            cel2.setCellValue("维修");
        }else if("0".equals(vehicleMaintenanceDto.getType())){
            cel2.setCellValue("保养");
        }
        Cell cel3 = row.createCell(3);
        if(vehicleMaintenanceDto.getMileage() == null){
            cel3.setCellValue("");
        }else{
            cel3.setCellValue(vehicleMaintenanceDto.getMileage().toString());
        }
        Cell cel4 = row.createCell(4);
        if(StringUtils.isEmpty(vehicleMaintenanceDto.getTotalCost())){
            cel4.setCellValue("");
        }else{
            cel4.setCellValue(vehicleMaintenanceDto.getTotalCost());
        }
        Cell cel5 = row.createCell(5);
        if(StringUtils.isEmpty(vehicleMaintenanceDto.getInvoiceStatus())){
            cel5.setCellValue("");
        }else if("1".equals(vehicleMaintenanceDto.getInvoiceStatus())){
            cel5.setCellValue("已开票");
        }else if("0".equals(vehicleMaintenanceDto.getInvoiceStatus())){
            cel5.setCellValue("未开票");
        }
        Cell cel6 = row.createCell(6);
        if(vehicleMaintenanceDto.getMaintenanceDate() == null){
            cel6.setCellValue("");
        }else{
            cel6.setCellValue(simpleDateFormat.format(vehicleMaintenanceDto.getMaintenanceDate()));
        }
        Cell cel7 = row.createCell(7);
        if(StringUtils.isEmpty(vehicleMaintenanceDto.getMaintenanceAddress())){
            cel7.setCellValue("");
        }else{
            cel7.setCellValue(vehicleMaintenanceDto.getMaintenanceAddress());
        }
        Cell cel8 = row.createCell(8);
        if(StringUtils.isEmpty(vehicleMaintenanceDto.getUpdateMan())){
            cel8.setCellValue("");
        }else{
            cel8.setCellValue(vehicleMaintenanceDto.getUpdateMan());
        }
        Cell cel9 = row.createCell(9);
        if(vehicleMaintenanceDto.getInputDate() == null){
            cel9.setCellValue("");
        }else{
            cel9.setCellValue(sdf.format(vehicleMaintenanceDto.getInputDate()));
        }
    }