java导出excel文件------第三种方法

最新推荐文章于 2024-05-10 11:37:26 发布

置顶 undefined_dream

最新推荐文章于 2024-05-10 11:37:26 发布

阅读量777

点赞数 1

分类专栏：技术帖经验帖 java 工作笔记

本文链接：https://blog.csdn.net/u011104294/article/details/68927386

版权

经验帖同时被 3 个专栏收录

24 篇文章 0 订阅

订阅专栏

工作笔记

22 篇文章 0 订阅

订阅专栏

技术帖

16 篇文章 0 订阅

订阅专栏

1.第三种方法直接上代码，这里还是用的spring mvc进行的请求，主要看代码注解，代码如下：

   @RequestMapping(value = "/exportComwithdrawalList",method=RequestMethod.POST)
    public void exportComwithdrawalList(
            HttpServletRequest request,
            HttpServletResponse response,
            String startTime,
            String endTime,
            String communityName,
            String processingmark) throws UnsupportedEncodingException {

        log.info("comwithdrawals/selectComwithdrawalList start");
        ComWithdrawalsVo comWithdrawalsVo= new ComWithdrawalsVo();
        comWithdrawalsVo.setStartTime(startTime);
        comWithdrawalsVo.setEndTime(endTime);
        comWithdrawalsVo.setCommunityName(communityName);
        comWithdrawalsVo.setProcessingMark(processingmark);
        // 获取数据列表
        List<ComWithdrawalsVo> comwithdrawalList = this.comWithdrawalsService.selectComwithdrawalList(comWithdrawalsVo);
        log.info("rechargeService.selectRechargeList end");

        log.info("excel导出开始 start");
        HSSFWorkbook wb = new HSSFWorkbook();
        try{

            //设置request编码和类型
            request.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-download");

            String filedisplay = "社区提现记录.xls";//为excel起名字

            filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename="+ filedisplay);//创建excel文件

            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("社区提现记录");
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow((int) 0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

            HSSFCell cell = row.createCell( 0);
            cell.setCellValue("社区名称");
            cell.setCellStyle(style);
            sheet.setColumnWidth(0, (25 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(1);
            cell.setCellValue("提现金额");
            cell.setCellStyle(style);
            sheet.setColumnWidth(1, (20 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(2);
            cell.setCellValue("申请时间");
            cell.setCellStyle(style);
            sheet.setColumnWidth(2, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(3);
            cell.setCellValue("汇款时间");
            cell.setCellStyle(style);
            sheet.setColumnWidth(3, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(4);
            cell.setCellValue("交易流水号");
            cell.setCellStyle(style);
            sheet.setColumnWidth(4, (80 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(5);
            cell.setCellValue("回单图片");
            cell.setCellStyle(style);
            sheet.setColumnWidth(5, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(6);
            cell.setCellValue("备注");
            cell.setCellStyle(style);
            sheet.setColumnWidth(6, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(7);
            cell.setCellValue("状态");
            cell.setCellStyle(style);
            sheet.setColumnWidth(7, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(8);
            cell.setCellValue("银行卡号");
            cell.setCellStyle(style);
            sheet.setColumnWidth(8, (60 * 256));  //设置列宽

            cell = row.createCell(9);
            cell.setCellValue("持卡人姓名");
            cell.setCellStyle(style);
            sheet.setColumnWidth(9, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(10);
            cell.setCellValue("开户银行");
            cell.setCellStyle(style);
            sheet.setColumnWidth(10, (45 * 256));  //设置列宽

            cell = row.createCell(11);
            cell.setCellValue("店主身份证");
            cell.setCellStyle(style);
            sheet.setColumnWidth(11, (60 * 256));  //设置列宽

            // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
                int index = 1;
                if(comwithdrawalList != null && comwithdrawalList.size() > 0){
                    for (int i = 0; i < comwithdrawalList.size(); i++)
                    {
                        ComWithdrawalsVo comWithdrawalsVo2 = comwithdrawalList.get(i);
                        // 第四步，创建单元格，并设置值
                        row = sheet.createRow(i+1);
                        row.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getCommunityName());//社区名称
                        row.createCell(1, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getReflectMoney());//提现金额
                        row.createCell(2, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getApplicationTimeStr());   //申请时间
                        row.createCell(3, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getPaymentTimeStr());  //汇款时间
                        row.createCell(4, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBatchNo());  //交易流水号

                        row.createCell(5, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getPaymentImg());  //回单图片
                        row.createCell(6, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getReflectMemo());  //备注
                        String resultbj=comWithdrawalsVo2.getProcessingMark();
                        if("1".equals(resultbj)){
                            row.createCell(7, HSSFCell.CELL_TYPE_STRING).setCellValue("未处理");  //状态
                        }
                        if("2".equals(resultbj)){
                            row.createCell(7, HSSFCell.CELL_TYPE_STRING).setCellValue("已汇款");  //状态
                        }

                        row.createCell(8, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBankCard());  //银行卡号
                        row.createCell(9, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBankUsername());  //持卡人姓名
                        row.createCell(10, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBankName());  //开户银行
                        row.createCell(11, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getIdNumber());  //店主身份证号
                    }
                }
            // 第六步，将文件存到指定位置
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.close();

        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

至此，代码结束。

总结，博客中的三种导出excel的方法已经全部完工，小伙伴们可以根据自己的需要和喜好选择相应的方法。

1.第三种方法直接上代码，这里还是用的spring mvc进行的请求，主要看代码注解，代码如下：

   @RequestMapping(value = "/exportComwithdrawalList",method=RequestMethod.POST)
    public void exportComwithdrawalList(
            HttpServletRequest request,
            HttpServletResponse response,
            String startTime,
            String endTime,
            String communityName,
            String processingmark) throws UnsupportedEncodingException {

        log.info("comwithdrawals/selectComwithdrawalList start");
        ComWithdrawalsVo comWithdrawalsVo= new ComWithdrawalsVo();
        comWithdrawalsVo.setStartTime(startTime);
        comWithdrawalsVo.setEndTime(endTime);
        comWithdrawalsVo.setCommunityName(communityName);
        comWithdrawalsVo.setProcessingMark(processingmark);
        // 获取数据列表
        List<ComWithdrawalsVo> comwithdrawalList = this.comWithdrawalsService.selectComwithdrawalList(comWithdrawalsVo);
        log.info("rechargeService.selectRechargeList end");

        log.info("excel导出开始 start");
        HSSFWorkbook wb = new HSSFWorkbook();
        try{

            //设置request编码和类型
            request.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-download");

            String filedisplay = "社区提现记录.xls";//为excel起名字

            filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename="+ filedisplay);//创建excel文件

            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("社区提现记录");
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow((int) 0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

            HSSFCell cell = row.createCell( 0);
            cell.setCellValue("社区名称");
            cell.setCellStyle(style);
            sheet.setColumnWidth(0, (25 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(1);
            cell.setCellValue("提现金额");
            cell.setCellStyle(style);
            sheet.setColumnWidth(1, (20 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(2);
            cell.setCellValue("申请时间");
            cell.setCellStyle(style);
            sheet.setColumnWidth(2, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(3);
            cell.setCellValue("汇款时间");
            cell.setCellStyle(style);
            sheet.setColumnWidth(3, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(4);
            cell.setCellValue("交易流水号");
            cell.setCellStyle(style);
            sheet.setColumnWidth(4, (80 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(5);
            cell.setCellValue("回单图片");
            cell.setCellStyle(style);
            sheet.setColumnWidth(5, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(6);
            cell.setCellValue("备注");
            cell.setCellStyle(style);
            sheet.setColumnWidth(6, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(7);
            cell.setCellValue("状态");
            cell.setCellStyle(style);
            sheet.setColumnWidth(7, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(8);
            cell.setCellValue("银行卡号");
            cell.setCellStyle(style);
            sheet.setColumnWidth(8, (60 * 256));  //设置列宽

            cell = row.createCell(9);
            cell.setCellValue("持卡人姓名");
            cell.setCellStyle(style);
            sheet.setColumnWidth(9, (15 * 256));  //设置列宽，50个字符宽

            cell = row.createCell(10);
            cell.setCellValue("开户银行");
            cell.setCellStyle(style);
            sheet.setColumnWidth(10, (45 * 256));  //设置列宽

            cell = row.createCell(11);
            cell.setCellValue("店主身份证");
            cell.setCellStyle(style);
            sheet.setColumnWidth(11, (60 * 256));  //设置列宽

            // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
                int index = 1;
                if(comwithdrawalList != null && comwithdrawalList.size() > 0){
                    for (int i = 0; i < comwithdrawalList.size(); i++)
                    {
                        ComWithdrawalsVo comWithdrawalsVo2 = comwithdrawalList.get(i);
                        // 第四步，创建单元格，并设置值
                        row = sheet.createRow(i+1);
                        row.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getCommunityName());//社区名称
                        row.createCell(1, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getReflectMoney());//提现金额
                        row.createCell(2, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getApplicationTimeStr());   //申请时间
                        row.createCell(3, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getPaymentTimeStr());  //汇款时间
                        row.createCell(4, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBatchNo());  //交易流水号

                        row.createCell(5, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getPaymentImg());  //回单图片
                        row.createCell(6, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getReflectMemo());  //备注
                        String resultbj=comWithdrawalsVo2.getProcessingMark();
                        if("1".equals(resultbj)){
                            row.createCell(7, HSSFCell.CELL_TYPE_STRING).setCellValue("未处理");  //状态
                        }
                        if("2".equals(resultbj)){
                            row.createCell(7, HSSFCell.CELL_TYPE_STRING).setCellValue("已汇款");  //状态
                        }

                        row.createCell(8, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBankCard());  //银行卡号
                        row.createCell(9, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBankUsername());  //持卡人姓名
                        row.createCell(10, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getBankName());  //开户银行
                        row.createCell(11, HSSFCell.CELL_TYPE_STRING).setCellValue(comWithdrawalsVo2.getIdNumber());  //店主身份证号
                    }
                }
            // 第六步，将文件存到指定位置
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.close();

        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

至此，代码结束。

总结，博客中的三种导出excel的方法已经全部完工，小伙伴们可以根据自己的需要和喜好选择相应的方法。