2 android 0930 android_0930 于 2016.09.21 08:40 提问

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
android_0930   2016.09.23 15: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());
        }
    }

ljheee
ljheee   Rxr 2016.09.21 08:44

CreateExcelFile这个个方法,是在那个包里?

android_0930
android_0930 CreateExcelFile cf = new CreateExcelFile(); cf.WriteExecelNew(rs, filedir + "易混车检索.xls", "易混车检索", colNames, colNames); //调用的代码写在一个js的<%%>里面的。调用CreateExcelFile类的WriteExecelNew()方法的时候,引入了CreateExcelFile所在的包。
大约一年之前 回复
zy_281870667
zy_281870667   Rxr 2016.09.21 09:10

你使用的是DWR?

poi的操作,我之前写过,参考下
http://blog.csdn.net/zy_281870667/article/details/51356713

android_0930
android_0930 在js里面嵌入了java代码。希望还是用jxl,然后在原来代码的基础上进行修改。
大约一年之前 回复
android_0930
android_0930   2016.09.23 15: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 静态写死了,而非动态**
        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();
            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;
            }
        }**//每个sheet可以容纳60000条数据**

        workbook.write(); 
        workbook.close();
    }
    catch (Exception ex)
    {
        System.out.println(ex.toString());
    }
}
qq_36030659
qq_36030659   2017.05.23 11:54

简单点用PageOffice插件实现。

Csdn user default icon
上传中...
上传图片
插入图片