现在要导出大概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());
}
}