根据列表导出数据Excel表格

此博客介绍了如何使用Spring Boot API实现导出工作工程师列表数据,包括筛选条件和全量数据的区别,以及如何生成包含工程师信息、接单情况等在内的Excel文件,展示了数据处理和Excel操作的编程技巧。
摘要由CSDN通过智能技术生成
@ApiOperation(value = "导出列表数据", notes = "", produces = "application/octet-stream")
    @RequestMapping(value = "/exportList", method = {RequestMethod.POST})
    public void exportList(@RequestBody QueryData2 queryData, HttpServletResponse response, String flag) {
        WorkEngineerListResult2 result = new WorkEngineerListResult2();

        WorkMainInfoQuery workMainInfoQuery = new WorkMainInfoQuery();
        String fileName ="";
        if ("1".equals(flag)) {
            fileName="当前数据.xlsx";
            workMainInfoQuery.setPageNo(null);
            workMainInfoQuery.setPageSize(null);
            workMainInfoQuery.setStartRow(null);
        } else {
            fileName="全部数据.xlsx";
            workMainInfoQuery.setPageNo(null);
            workMainInfoQuery.setPageSize(null);
            workMainInfoQuery.setStartRow(null);
        }


        workMainInfoQuery.setProcessEngineer(queryData.getProcessEngineer());
        workMainInfoQuery.setOnlineTime(queryData.getOnlineTime());
        workMainInfoQuery.setJobs(queryData.getJobs());
        workMainInfoQuery.setBeginDate(queryData.getStartApplicationTime());
        workMainInfoQuery.setEndDate(queryData.getEndApplicationTime());

        SimplePage simplePage = null;

        if("1".equals(flag)){
            //当前记录
            simplePage = workMainInfoService.getEngineerList(queryData,true);
        }else {
            //全部记录
            queryData.setCategory("全部类别");
            queryData.setJobs("全部岗位");
            queryData.setOrderCycle(0);
            queryData.setProcessEngineer("all");
            queryData.setStartApplicationTime(null);
            queryData.setEndApplicationTime(null);
            queryData.setOnlineTime(null);
            simplePage = workMainInfoService.getEngineerList(queryData,false);
        }


        result.setDataList((List<WorkEngineerList2>) simplePage.getList());

        List<WorkEngineerList2> list = result.getDataList();

        String tableHeader[]={"工程师","接单","工单分布","岗位","在线时长","贡献问题","用户评价"};
        short cellNumber =(short) tableHeader.length;
        //创建一个excel
        HSSFWorkbook workbook = new HSSFWorkbook();
        //设置表头类型
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);// 单元格底部边框
        style.setBorderLeft(BorderStyle.THIN);// 单元格左侧边框
        style.setBorderTop(BorderStyle.THIN);// 单元格上侧边框
        style.setBorderRight(BorderStyle.THIN);// 单元格右侧边框
        style.setAlignment(HorizontalAlignment.CENTER);//居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);


        //设置字体
        HSSFFont font = workbook.createFont();
        //创建一个sheet
        HSSFSheet sheet = workbook.createSheet("1");


        //将表头写进excel
        sheet = getHssfSheet(sheet, tableHeader, cellNumber, font, style);

        int startRow =1;
        int endRow = 0;
        int startCol =0;
        int endCol = 0;
        int all_total = 0;

        int k = 1;

        for (WorkEngineerList2 data : list) {

            sheet.setColumnWidth(0, 30*256);
            sheet.setColumnWidth(1, 20*256);
            sheet.setColumnWidth(2, 100*256);
            sheet.setColumnWidth(3, 60*256);
            sheet.setColumnWidth(4, 20*256);
            sheet.setColumnWidth(5, 20*256);
            sheet.setColumnWidth(6, 20*256);
            // 获取下一行
            HSSFRow rows = sheet.createRow(sheet.getLastRowNum() + 1);
            rows.setHeight((short)430);

            String engineer = data.getEngineer();
            if(engineer==null){
                engineer = "";
            }
            //工程师
            HSSFCell cell1 = rows.createCell((short) 0);
            cell1.setCellValue(engineer);
            cell1.setCellStyle(style);

            Long workNum1 = data.getWorkNum();
            if(workNum1==null){
                workNum1 = 0L;
            }
            //接单
            HSSFCell cell2 = rows.createCell((short) 1);
            cell2.setCellValue(workNum1);
            cell2.setCellStyle(style);

            String workOrder = data.getWorkOrder();
            if(workOrder==null){
                workOrder = "";
            }
            //工单分布
            HSSFCell cell3 = rows.createCell((short) 2);
            cell3.setCellValue(workOrder);
            cell3.setCellStyle(style);

            String jobs = data.getJobs();
            if(jobs==null){
                jobs = "";
            }
            //岗位
            HSSFCell cell4 = rows.createCell((short) 3);
            cell4.setCellValue(jobs);
            cell4.setCellStyle(style);

            Integer onlineTime = data.getOnlineTime();
            if(onlineTime==null){
                onlineTime = 0;
            }
            //在线时长
            HSSFCell cell5 = rows.createCell((short) 4);
            cell5.setCellValue(onlineTime);
            cell5.setCellStyle(style);

            Integer problem = data.getProblem();
            if(problem==null){
                problem = 0;
            }
            //贡献问题
            HSSFCell cell6 = rows.createCell((short) 5);
            cell6.setCellValue(problem);
            cell6.setCellStyle(style);

            Float averageScore = data.getAverageScore();
            String s = String.valueOf(averageScore);
            if(s==null){
                s = "";
            }
            //用户评价
            HSSFCell cell7 = rows.createCell((short) 6);
            cell7.setCellValue(s);
            cell7.setCellStyle(style);
        }
        // 得到总行数
//        int rowNum = sheet.getLastRowNum();
//        // 正文内容应该从第二行开始,第一行为表头的标题
//        for (int i = 1; i <= rowNum;) {
//            startRow = i;
//            HSSFRow row = sheet.getRow(i);
//            String gdlx = row.getCell(1).getStringCellValue();
//            int count = 1;
//            if (rowNum>1) {
//                for (int j = i; j <= rowNum-1; j++) {
//                    row = sheet.getRow(j + 1);
//                    String gdlx_next = row.getCell(1).getStringCellValue();
//                    if (gdlx.equals(gdlx_next)) {
//                        count++;
//                    }
//                }
//            }
//            endRow = startRow +count-1;
//            if(count>1){
//                //合并单元格,四个参数依次为:起始行,终止行,起始列,终止列
//                CellRangeAddress region1 = new CellRangeAddress(startRow, endRow, (short) 0, (short) 0);
//                CellRangeAddress region2 = new CellRangeAddress(startRow, endRow, (short) 1, (short) 1);
//                //把合并后的单元格添加进sheet对象
//                sheet.addMergedRegion(region1);
//                sheet.addMergedRegion(region2);
//            }
//            i=i+count;
//        }
//        //总计
//        HSSFRow rows = sheet.createRow(sheet.getLastRowNum() + 1);
//        rows.setHeight((short)400);
//        HSSFCell cell1 = rows.createCell((short) 0);
//        cell1.setCellValue("总计");
//        cell1.setCellStyle(style);
//        //数量
//        HSSFCell cell2 = rows.createCell((short) 1);
//        cell2.setCellValue(all_total);
//        cell2.setCellStyle(style);
//        HSSFCell cell3 = rows.createCell((short) 2);
//        cell3.setCellStyle(style);
//        HSSFCell cell4 = rows.createCell((short) 3);
//        cell4.setCellStyle(style);
//        //合并总计单元格
//        CellRangeAddress region2 = new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 1, 3);
//        sheet.addMergedRegion(region2);


        try {

            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


private static HSSFSheet getHssfSheet (HSSFSheet sheet,String tableHeader[],short cellNumber,HSSFFont font,HSSFCellStyle style)

{

    HSSFHeader header = sheet.getHeader();

    header.setCenter("");

    HSSFRow row = sheet.createRow(0);

    row.setHeight((short)400);

    //sheet的头

    for(int k = 0;k < cellNumber;k++){

        HSSFCell cell = row.createCell((short) k);//创建第0行第k列

        cell.setCellValue(tableHeader[k]);//设置第0行第k列的值

        sheet.setColumnWidth((short)k,(short)7000);//设置列的宽度

        font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.

        font.setFontHeight((short)350); //设置单元字体高度

        style.setFont(font);//设置字体风格

        cell.setCellStyle(style);

    }

    return  sheet;

}

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值