android_0930
2016-09-21 00:40
采纳率: 50%
浏览 2.5k
已采纳

java web,利用JXL导出数据为Excel,支持多个sheet导出,求大神快来

现在要导出大概10万条数据,超出65336,所以想试着改成分页(多个sheet)的形式导出数据,不知道如何实现。自己修改后的代码有问题的。
js调用类中的方法:

 CreateExcelFile cf = new CreateExcelFile();
        cf.WriteExecelNew(rs, filedir + "易混车检索.xls", "易混车检索", colNames, colNames);

旧代码,不支持分页的,只能生成一个sheet
CreateExcelFile.java

    public void WriteExecelNew(ResultSet rs, String filename, String tilte,
            String[] colunames, String[] columns) {
        try {
            int columncount = columns.length;
            String sfilename = "c:\\output.xls";
            if (filename != null) {
                if (!filename.equals("")) {
                    sfilename = filename;
                }
            }

            WritableWorkbook workbook = Workbook.createWorkbook(new File(
                    sfilename));
            WritableSheet sheet = workbook.createSheet(tilte, 0);
            sheet.mergeCells(0, 0, columncount - 1, 0);// 合并单元格
            WritableFont times16font = new WritableFont(WritableFont.TIMES, 16,
                    WritableFont.BOLD, true);
            WritableCellFormat times16format = new WritableCellFormat(
                    times16font);
            times16format.setAlignment(jxl.format.Alignment.CENTRE);// //把水平对齐方式指定为居中
            times16format
                    .setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 把垂直对齐方式指定为居中

            // 标题头
            Label label = new Label(0, 0, tilte, times16format);
            sheet.setRowView(0, 800);
            sheet.addCell(label);

            int row = 1;
            WritableCellFormat titielformat = new WritableCellFormat();
            titielformat.setBackground(jxl.format.Colour.GREY_25_PERCENT);
            titielformat.setBorder(jxl.format.Border.ALL,
                    jxl.format.BorderLineStyle.THIN);
            titielformat.setAlignment(jxl.format.Alignment.CENTRE);
            titielformat
                    .setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            // 写表头
            for (int i = 0; i < columncount; i++) {
                Label labeltmp = new Label(i, row, colunames[i], titielformat);
                sheet.addCell(labeltmp);

                // Number number = new Number(5, 6, 3.1459);
                // sheet.addCell(number);
            }
            row = 2;
            if (rs != null) {
                WritableCellFormat tformat = new WritableCellFormat();
                tformat.setBorder(jxl.format.Border.ALL,
                        jxl.format.BorderLineStyle.THIN);
                rs.beforeFirst();
                while (rs.next()) {
                    for (int i = 0; i < columncount; i++) {
                        Label labeltmp = new Label(i, row, rs.getString(i + 1),
                                tformat);
                        sheet.addCell(labeltmp);
                    }
                    row = row + 1;
                }
            }

            workbook.write();
            workbook.close();
        } catch (Exception ex) {
            System.out.println(ex.toString());
        }
    }

尝试修改为支持分页的,但是循环条件写的不对,以及如何判断前一页已经达到65536,开始写入下一页

 public void WriteExecelSheetNew(ResultSet rs, String filename, String tilte,
            String[] colunames, String[] columns) {
            try {
            int columncount = columns.length;
            String sfilename = "c:\\output.xls";
            if (filename != null) {
                if (!filename.equals("")) {
                    sfilename = filename;
                }
            }
            WritableWorkbook workbook = Workbook.createWorkbook(new File(sfilename));
            rs.last();
            int reslength = rs.getRow();

            //设置每个sheet显示的记录数
            int sheetSize = 65536;
            //有多少个sheet
            int sheetNum = 1;
            //计算要创建的sheet个数
            if(reslength%sheetSize > 0){
                sheetNum = reslength/sheetSize+1;
            }else{
                sheetNum = reslength/sheetSize;
            } 
            System.out.println("页数:"+sheetNum);
            for (int i = 0; i < sheetNum; i++) { 

                WritableSheet sheet = workbook.createSheet(tilte+i, i);  
                sheet.mergeCells(0, 0, columncount - 1, 0);// 合并单元格
                WritableFont times16font = new WritableFont(WritableFont.TIMES, 16,WritableFont.BOLD, true);
                WritableCellFormat times16format = new WritableCellFormat(times16font);
                times16format.setAlignment(jxl.format.Alignment.CENTRE);// //把水平对齐方式指定为居中
                times16format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 把垂直对齐方式指定为居中
                // 标题头
                Label label = new Label(0, 0, tilte, times16format);
                sheet.setRowView(0, 800);
                sheet.addCell(label);

                int row = 1;
                WritableCellFormat titielformat = new WritableCellFormat();
                titielformat.setBackground(jxl.format.Colour.GREY_25_PERCENT);
                titielformat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
                titielformat.setAlignment(jxl.format.Alignment.CENTRE);
                titielformat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
                // 写表头
                for (int j = 0; j < columncount; j++) {
                Label labeltmp = new Label(j, row, colunames[j], titielformat);
                    sheet.addCell(labeltmp);
                }

                 row = 2;
                if (rs != null) {
                    WritableCellFormat tformat = new WritableCellFormat();
                    tformat.setBorder(jxl.format.Border.ALL,
                            jxl.format.BorderLineStyle.THIN);
                    rs.beforeFirst();
                    //int count = 0;
                    while (rs.next()) {
                        //count++;
                        for(int k = i*sheetSize;k<i*sheetSize+sheetSize&&k<reslength;k++){
                            for (int j = 0; j < columncount; j++) {
                            Label labelt = new Label(j, row, rs.getString(j + 1),tformat);
                            sheet.addCell(labelt);
                        }
                        }
                        row = row + 1;

                    }
                }
                }
                workbook.write();
                workbook.close();
                }
                catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

5条回答 默认 最新

  • android_0930 2016-09-23 07:59
    最佳回答
     //分页导出Excel ---song
    
        public void WriteExecel_Old(ResultSet rs, String filename,String tilte,String[] colunames,String[] columns)
        {
            try
            {
                int columncount = columns.length;
                String sfilename ="c:\\output.xls";
                if (filename!=null)
                {
                    if (!filename.equals(""))
                    {
                        sfilename = filename;
                    }
                }       
    
                WritableWorkbook workbook = Workbook.createWorkbook(new File(sfilename));
                WritableSheet sheet = workbook.createSheet(tilte, 0);
                WritableSheet sheet2 = workbook.createSheet("sheet2", 1);
                WritableSheet sheet3 = workbook.createSheet("sheet3", 2);
                WritableSheet sheet4 = workbook.createSheet("sheet4", 3);
                sheet.mergeCells(0,0,columncount-1,0);//合并单元格
                /******************************************/
                sheet2.mergeCells(0,0,columncount-1,0);//合并单元格
                sheet3.mergeCells(0,0,columncount-1,0);//合并单元格
                sheet4.mergeCells(0,0,columncount-1,0);//合并单元格
                /******************************************/
                WritableFont times16font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true);
                WritableCellFormat times16format = new WritableCellFormat (times16font);
                times16format.setAlignment(jxl.format.Alignment.CENTRE);// //把水平对齐方式指定为居中
                times16format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //把垂直对齐方式指定为居中 
    
                //标题头
                Label label = new Label(0, 0, tilte,times16format);
                /******************************************/
                Label label2 = new Label(0, 0, tilte,times16format);
                Label label3 = new Label(0, 0, tilte,times16format);
                Label label4 = new Label(0, 0, tilte,times16format);
                /******************************************/
                sheet.setRowView(0, 800);
                sheet.addCell(label); 
                /******************************************/
                sheet2.setRowView(0, 800);
                sheet2.addCell(label2);
                sheet3.setRowView(0, 800);
                sheet3.addCell(label3);
                sheet4.setRowView(0, 800);
                sheet4.addCell(label4);
                /******************************************/            
                int row = 1;
                WritableCellFormat titielformat = new WritableCellFormat();
                titielformat.setBackground(jxl.format.Colour.GREY_25_PERCENT);
                titielformat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                //写表头
                for (int i=0;i<columncount;i++)
                {
                    Label labeltmp = new Label(i,row,colunames[i],titielformat);
                    Label labeltmp2 = new Label(i,row,colunames[i],titielformat);
                    Label labeltmp3 = new Label(i,row,colunames[i],titielformat);
                    Label labeltmp4 = new Label(i,row,colunames[i],titielformat);
                    sheet.addCell(labeltmp);
                    sheet2.addCell(labeltmp2);
                    sheet3.addCell(labeltmp3);
                    sheet4.addCell(labeltmp4);
                    //Number number = new Number(5, 6, 3.1459); 
                    //sheet.addCell(number);
                }
                row = 2;
                int myRow1 = 1;
                int myRow2 = 1;
                int myRow3 = 1;
                if (rs!=null)
                {
                    WritableCellFormat tformat = new WritableCellFormat();
                    tformat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);              
                    rs.beforeFirst();
    //              System.out.println("---------------");
                    while (rs.next())
                    {
                        if(row<=60000){
                            for (int i=0;i<columncount;i++)
                            {
                                Label labeltmp = new Label(i,row,rs.getString(i+1),tformat);
                                sheet.addCell(labeltmp);
                            }
                        } else if(row>60000 && row <=120000) {
                            for (int i=0;i<columncount;i++)
                            {
                                Label labeltmp = new Label(i, myRow1, rs.getString(i+1), tformat);
                                sheet2.addCell(labeltmp);
                            }
                            myRow1++;
                        } else if(row>120000 && row <=180000) {
                            for (int i=0;i<columncount;i++)
                            {
                                Label labeltmp = new Label(i, myRow2, rs.getString(i+1), tformat);
                                sheet3.addCell(labeltmp);
                            }
                            myRow2++;
                        } else {
                            for (int i=0;i<columncount;i++)
                            {
                                Label labeltmp = new Label(i, myRow3, rs.getString(i+1), tformat);
                                sheet4.addCell(labeltmp);
                            }
                            myRow3++;
                        }
    
                        row = row +1;
                    }
                }
    
                workbook.write(); 
                workbook.close();
            }
            catch (Exception ex)
            {
                System.out.println(ex.toString());
            }
        }
    
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
查看更多回答(4条)

相关推荐 更多相似问题