excel导出功能

最新推荐文章于 2023-12-29 16:15:09 发布

成长的小菜鸟

最新推荐文章于 2023-12-29 16:15:09 发布

阅读量805

点赞数

分类专栏：项目总结

本文链接：https://blog.csdn.net/weixin_35756522/article/details/77991247

版权

项目总结专栏收录该内容

20 篇文章 0 订阅

订阅专栏

1：pom.xml导入依赖

  <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>  //本地仓库位置
            <version>${poi.version}</version>
        </dependency>

2：controller

    @ApiOperation(value = "分析数据表格导出", httpMethod = "GET", notes = " 说明：导出CSV格式数据", response = String.class)
    @RequestMapping(value = "/passengerFlowDataGridExport", method = RequestMethod.GET)
    @ResponseBody
    public String passengerFlowDataGridExport(@ApiParam(required = true, name = "dateType", value = "日期类型", defaultValue = "day",

    allowableValues = "day") @RequestParam(required = true) String dateType,
                              @ApiParam(required = false, name = "startTime", value = "开始时间", defaultValue = "2017-06-10")

                             @RequestParam(required = false) String startTime,
                             @ApiParam(required = false, name = "endTime", value = "结束时间", defaultValue = "2017-06-11")

                             @RequestParam(required = false) String endTime,
                             @ApiParam(required = true, name = "storeCode", value = "门店编码", defaultValue = "871L")

                             @RequestParam(required = true) String storeCode
    ){
        long begin = System.currentTimeMillis();
        logger.info("dataGridExport dateType:{},startTime:{},endTime:{},storeCode:{}" , dateType ,startTime ,endTime ,storeCode);
        StorefrontQueryParam queryParam = new StorefrontQueryParam(dateType, startTime, endTime, storeCode);
        storefrontPassengerFlowAnalysisService.dataGridExport(queryParam, response);
        logger.info("dataGridExport costs:" + (System.currentTimeMillis() - begin) + " ms");
        return null;
    }

3:service:

  @Override
    //数据导出
    public void dataGridExport(StorefrontQueryParam queryParam, HttpServletResponse response) {
        long begin = System.currentTimeMillis();
        //待导出的数据
        StorefrontPassengerFlowAnalysisDataGridVO storefrontPassengerFlowAnalysisDataGridVO = dataGrid(queryParam);
        //定义文件名
        String fileName = queryParam.getStoreCode() + "_" + DayUtil.dateFormatLong().format(new Date()) + "_店面进店人次";
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet("进店人次数据表格");
        int rowIndex = 0;
        SXSSFRow headerDataRow = sheet.createRow(rowIndex);
        createExcelHeader(workbook,headerDataRow);//创建表头
        rowIndex++; //行号递增
        if(storefrontPassengerFlowAnalysisDataGridVO !=null && storefrontPassengerFlowAnalysisDataGridVO.getData() != null){
            for (Map<String, Object> timeQuantumMap : storefrontPassengerFlowAnalysisDataGridVO.getData()){
                SXSSFRow dataRow = sheet.createRow(rowIndex);
                int columnIndex = 0;
                SXSSFCell timeQuantumCell = dataRow.createCell(columnIndex);
                timeQuantumCell.setCellValue(timeQuantumMap.get("timeQuantum") !=null? timeQuantumMap.get("timeQuantum").toString():"");
                columnIndex++;
                SXSSFCell inCountCell = dataRow.createCell(columnIndex);
                inCountCell.setCellValue(timeQuantumMap.get("inCount") !=null? timeQuantumMap.get("inCount").toString():"");
                columnIndex++;
                SXSSFCell percentageCell = dataRow.createCell(columnIndex);
                percentageCell.setCellValue(timeQuantumMap.get("percentage") !=null? timeQuantumMap.get("percentage").toString():"");
                rowIndex++;
            }
        }
        try {
            // 设置response参数，可以打开下载页面
            response.reset();
            ServletOutputStream outputStream = response.getOutputStream();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
            workbook.write(outputStream);
            workbook.close();
            workbook.dispose();
            outputStream.flush();
            outputStream.close();
            logger.info("end dataGridExport costs:{} ms",(System.currentTimeMillis() - begin));
        } catch (IOException e) {
            logger.error("dataGridExport error:", ExceptionUtils.getStackTrace(e));
        }
    }

dataGrid:

 /**
     *
     * @param queryParam 查询参数
     * @return
     */
    @Override
    //表格数据
    public StorefrontPassengerFlowAnalysisDataGridVO dataGrid(StorefrontQueryParam queryParam) {
        long begin = System.currentTimeMillis();
        StorefrontBaseProcessor processor;
        StorefrontPassengerFlowAnalysisDataGridVO storefrontPassengerFlowAnalysisDataGridVO = new StorefrontPassengerFlowAnalysisDataGridVO();

        //单天处理
        if (queryParam.getStorefrontQueryDateType()== StorefrontAnalysisQueryDateType.DAY_ONE){
            List<Map<String, Object>> statByHourMap = storefrontAnalysisDao.statByHour(queryParam.getStoreCode(), queryParam.getStartTimeStr(), queryParam.getEndTimeStr());
            processor = new StorefrontPassengerFlowDayProcessor(queryParam,statByHourMap);
        }
        //多天处理
        else if (queryParam.getStorefrontQueryDateType()== StorefrontAnalysisQueryDateType.DAY_MORE){
            List<Map<String, Object>> statByDayMap = storefrontAnalysisDao.statByDay(queryParam.getStoreCode(), queryParam.getStartTimeStr(), queryParam.getEndTimeStr());
            processor = new StorefrontPassengerFlowDayProcessor(queryParam,statByDayMap);
        }
        else{
            throw new ValidationException("不支持的查询方式！");
        }
        //获取返回的结果
        List<Map<String, Object>> dataGrids = processor.getDataGrid();
        storefrontPassengerFlowAnalysisDataGridVO.setData(dataGrids);
        storefrontPassengerFlowAnalysisDataGridVO.setRecordsTotal(dataGrids.size());
        logger.info("end dataGrid costs:{} ms",(System.currentTimeMillis() - begin));
        return storefrontPassengerFlowAnalysisDataGridVO;
    }

 /**
     * 创建Excel表头
     *
     * @param dataRow 行数据
     */
    public void createExcelHeader(SXSSFWorkbook workbook,SXSSFRow dataRow) {
        int columnIndex = 0;//列计数据
        SXSSFCell channelCell = dataRow.createCell(columnIndex);
        channelCell.setCellValue("时间段");
        columnIndex++;
        SXSSFCell inCountCell = dataRow.createCell(columnIndex);
        inCountCell.setCellValue("进店人次");
        columnIndex++;
        SXSSFCell percentage = dataRow.createCell(columnIndex);
        percentage.setCellValue("进店人次占比");
    }