现在要导出大概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());
}
}
//分页导出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());
}
}
CreateExcelFile这个个方法，是在那个包里？
你使用的是DWR?
poi的操作，我之前写过，参考下
http://blog.csdn.net/zy_281870667/article/details/51356713
//分页导出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());
}
}
简单点用PageOffice插件实现。