导出Excel流程

怼烎@ 2022-05-13 11:58 179阅读 0赞

一、后台代码

/** * 导出报表 * @return */
        @RequestMapping(value = "/exportUser")
        @ResponseBody
        public void export(HttpServletRequest request,HttpServletResponse response,Integer pageNum,Integer userId,String nickname,String sex,String realname,String grade) throws Exception {
            List<Map> income = userService.exportAllUser(pageNum,userId,nickname,sex,realname,grade);
            String[] headers = {
       "ID","电话","头像","昵称","地址","个性签名","性别","生日","上级用户ID","会员等级","真实姓名","身份证号","添加时间"};
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet();
            //设置列宽
            sheet.setDefaultColumnWidth((short) 18);
            //创建第一行的对象，第一行一般用于填充标题内容。从第二行开始一般是数据
            HSSFRow row = sheet.createRow(0);
            for (short i = 0; i < headers.length; i++) {
                //创建单元格，每行多少数据就创建多少个单元格
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                //给单元格设置内容
                cell.setCellValue(text);
            }
    
            //遍历集合，将每个集合元素对象的每个值填充到单元格中
            for(int i=0;i<income.size();i++){
                Map statisticsModel=income.get(i);
                //从第二行开始填充数据
                row = sheet.createRow(i+1);
                //该集合只记录数量和时间，这两个值来自statisticsModel。如果对象比较复杂，需要额外转换，比如Date类型的日期，int，float类型的数值
                List<String> datas=new ArrayList<>();
    
                datas.add(statisticsModel.get("userId").toString());
                datas.add(null==statisticsModel.get("phone")?"":statisticsModel.get("phone").toString());
                datas.add(statisticsModel.get("head").toString());
                datas.add(statisticsModel.get("nickname").toString());
                datas.add(null==statisticsModel.get("region")?"":statisticsModel.get("region").toString());
                datas.add(statisticsModel.get("signature").toString());
                String sexx = null;
                if((Integer) statisticsModel.get("sex")==0){
                    sexx="未知";
                }else if((Integer)statisticsModel.get("sex")==1){
                    sexx="男";
                }else if((Integer)statisticsModel.get("sex")==2){
                    sexx="女";
                }
                datas.add(sexx);
                datas.add(null==statisticsModel.get("birthday")?"":statisticsModel.get("birthday").toString());
                datas.add(null==statisticsModel.get("superior")?"":statisticsModel.get("superior").toString());
                String gradee=null;
                if ((Integer)statisticsModel.get("grade")==1){
                    gradee="普通会员";
                }else if((Integer)statisticsModel.get("grade")==2){
                    gradee="白银会员";
                }else if((Integer)statisticsModel.get("grade")==3) {
                    gradee="黄金会员";
                }else if((Integer)statisticsModel.get("grade")==4) {
                    gradee="铂金会员";
                }
                datas.add(gradee);
                datas.add(null==statisticsModel.get("realname")?"":statisticsModel.get("realname").toString());
                datas.add(null==statisticsModel.get("idcard")?"":statisticsModel.get("idcard").toString());
                datas.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(statisticsModel.get("createTime")));
    
                for (int j=0;j<datas.size();j++) {
                    String string=datas.get(j);
                    HSSFCell cell = row.createCell(j);
                    HSSFRichTextString richString = new HSSFRichTextString(string);
                    HSSFFont font3 = workbook.createFont();
                    //定义Excel数据颜色，这里设置为蓝色
    // font3.setColor(HSSFColor.BLUE.index);
                    richString.applyFont(font3);
                    cell.setCellValue(richString);
                }
    
            }
            String file = "UserInformation"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename="+file);//Excel文件名
            try {
                response.flushBuffer();
            } catch (IOException e) {
                e.printStackTrace();
            }
            //将workbook中的内容写入输出流中
            try {
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
    
        }

二、前台代码

//导出
            function tableToExcel(){ 
                window.location.href = "user/exportUser";
            }

注：此处不能用ajax请求后台

虽然没有复杂的样式设置，但是很好用

ps：![这里写图片描述][70]

[70]: /images/20220513/c4f18b0094d34c90b9f7fb7b79e6b655.png