Excel导出报表

最新推荐文章于 2024-11-25 15:42:10 发布

原创最新推荐文章于 2024-11-25 15:42:10 发布 · 315 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #报表

JAVA后端专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种使用Java实现Excel批量导出的方法，包括如何处理大量数据分页导出，设置Excel文件名及响应头，创建并填充工作表，以及如何处理日期格式化等问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

excel导出

function exportExcel() {
    var location="${pageContext.request.contextPath}/对应Controller/exportExcel?";
    var param=$("#id").serialize();(form表单的id)
    window.location.href=location+param;
}

@RequestMapping("/exportExcel") 
        public void createExcel(PayBalance payBalance,HttpServletRequest request,HttpServletResponse response) throws WriteException,IOException, IllegalAccessException, InvocationTargetException{

            response.setHeader("Content-Disposition", "attachment;filename=" + new String("财务对账报表".getBytes("gb2312"), "ISO8859-1") + ".xls");
            OutputStream os=response.getOutputStream();
            //创建工作薄
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            long **limiteLineNum** = 65535;//65536 65535
            Map<Integer, Object> listMap = tbPayBalanceService.**dataGridReportMap**(payBalance, limiteLineNum);
            if(listMap.isEmpty()){
                  WritableSheet sheet = workbook.createSheet("Sheet 1",0);
                  //创建要显示的内容,创建一个单元格，第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容
                    //        x_y             y-1 x-1
                    Label opsq1_1 = new Label(0,0,"订单号");
                    sheet.addCell(opsq1_1);
             .............
                    Label taotalRefundAm1_10 = new Label(9,0,"退款金额");
                    sheet.addCell(taotalRefundAm1_10);

            }else{
                  SimpleDateFormat sf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                  for (Map.Entry<Integer, Object> entry : listMap.entrySet()) {  
                    List<PayBalance> reportList =   (List<PayBalance>) entry.getValue();
                    //创建新的一页
                      WritableSheet sheet = workbook.createSheet("Sheet "+(entry.getKey()+1),entry.getKey());
                      Label opsq1_1 = new Label(0,0,"订单号");
                        sheet.addCell(opsq1_1);
                      .............
                        Label taotalRefundAm1_10 = new Label(9,0,"退款金额");
                        sheet.addCell(taotalRefundAm1_10);
                        for(int i=0;i<reportList.size();i++){
                            Label opsq_1 = new Label(0,i+1,reportList.get(i).getOpSq());
                            sheet.addCell(opsq_1);
                        .........
                            Label paidAmount_4 = new Label(3,i+1,reportList.get(i).getPaidAm().toString());            
                            sheet.addCell(paidAmount_4);
                          .........

                            Label checkResult_6 = new Label(5,i+1,reportList.get(i).getCheckResult());
                            if("1".equals(checkResult_6.getContents())){
                                sheet.addCell( new Label(5,i+1,"匹配"));
                            }
                            else{
                                sheet.addCell(new Label(5,i+1,"不匹配"));
                            }
                            Label paidDate_7 = new Label(6,i+1,sf.format(reportList.get(i).getPaidDate()));
                            sheet.addCell(paidDate_7);
                      .........
                        }
                  }
            }
            //把创建的内容写入到输出流中，并关闭输出流
            workbook.write();
            workbook.close();
            os.close();
        }

public Map<Integer, Object> **dataGridReportMap**(PayBalance payBalance,
                long rowsNum) {
            Map<Integer, Object> listMap = new HashMap<Integer, Object>();
            List<PayBalance> reportList =dataGridReportList(payBalance);
            int size = reportList.size();//目标的有多少个
            if(size > 0 && reportList != null){
                //rowsNum:每个文件的最大条数
                if(rowsNum >= size){
                    listMap.put(0, reportList);
                }else{
                    long num = 0;//要生成的文件个数
                    //商
                    long reportNum_ = size / rowsNum;
                    //余数
                    long add_1Factor = size % rowsNum;
                    if(add_1Factor != 0){
                        num = reportNum_ + 1;
                    }else{
                        num = reportNum_;
                    }
                    for(long i = 0;i < num;i ++){
                        List<PayBalance> reportList_ = new ArrayList<PayBalance>();
                        if(i != num - 1){
                            reportList_ = reportList.subList((int)i*(int)(rowsNum), (int)(rowsNum)*(int)(i+1));
                        }
                        if(i == num - 1){
                            reportList_ = reportList.subList((int)i*(int)(rowsNum), reportList.size());
                        }
                        listMap.put((int)i, reportList_);
                    }
                }
            }
            return listMap;
        }

字符串相等的判断，日期的格式化超过Excel的最大条数65535就新增一页。

简单情况（直接导出）

@RequestMapping("/exportExcel")
    public void createExcel(RefundLog refundLog, HttpServletRequest request,
            HttpServletResponse response) throws WriteException, IOException,
            IllegalAccessException, InvocationTargetException {

        response.setHeader("Content-Disposition", "attachment;filename=" + new String("退款明细查询页面".getBytes("gb2312"), "ISO8859-1") + ".xls");
        OutputStream os=response.getOutputStream();
         //创建工作薄
        WritableWorkbook workbook = Workbook.createWorkbook(os);
        //创建新的一页
        WritableSheet sheet = workbook.createSheet("First Sheet",0);
        //创建要显示的内容,创建一个单元格，第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容
        SimpleDateFormat sf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<RefundLog> refList=refundLogService.dataGridlist(refundLog);

        Label opSq = new Label(0,0,"订单号");
        sheet.addCell(opSq);
       ...............
        Label refundId = new Label(9,0,"退款序号");
        sheet.addCell(refundId);
        if(null!=refList&&refList.size()>0){
            for(int i=0;i<refList.size();i++){
                Label opSq1 = new Label(0,i+1,refList.get(i).getOpSq());
                sheet.addCell(opSq1);
              .........
                Label createDate1 = new Label(6,i+1,sf.format(refList.get(i).getCreateDate()));
                sheet.addCell(createDate1);
                if(!StringUtils.isEmpty(refList.get(i).getUpdateDate())){
                    Label updateDate1 = new Label(7,i+1,sf.format(refList.get(i).getUpdateDate()));
                    sheet.addCell(updateDate1);
                }else{
                    Label updateDate1 = new Label(7,i+1,"");
                    sheet.addCell(updateDate1);
                }
               ....
            }
        }
      //把创建的内容写入到输出流中，并关闭输出流
        workbook.write();
        workbook.close();
        os.close();
    }