jxl 分sheet从数据库导出到excel? 400 报错
jxl 分sheet从数据库导出到excel? 400 报错
@一堆BUG 你好，想跟你请教个问题：我写的代码不能正确分sheet从数据库中导出数据，求解答
packagecom.system.dao;
importjava.io.*;
importjava.sql.*;
importjava.util.List;
importjavax.servlet.ServletOutputStream;
importjavax.servlet.jsp.PageContext;
importjavax.sql.rowset.CachedRowSet;
importorg.apache.taglibs.standard.tag.el.core.OutTag;
importcom.sun.rowset.CachedRowSetImpl;
importcom.system.DB.DBManager;
importjxl.Workbook;
importjxl.write.Label;
importjxl.write.WritableCell;
importjxl.write.WritableSheet;
importjxl.write.WritableWorkbook;
importjxl.write.WriteException;
publicclassgetExcel {
privatestaticConnection conn =null;
privatestaticPreparedStatement pstmt =null;
privatestaticResultSet rs =null;
privatestaticCachedRowSet crs;
privateWritableCell labelCF11;
@SuppressWarnings("static-access")
publicvoidgetExcel(String sql) {
try{
conn = DBManager.getDBManager().connection;
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
crs =newCachedRowSetImpl();
crs.populate(rs);
System.out.println(null== crs);
return;
}catch(Exception e) {
// TODO: handle exception
return;
}
}
publicvoidgetExcelResult(String sql, ServletOutputStream os)
throwsSQLException, IOException, WriteException {
// 首先获取结果集
// 这里获取RowSet的方法
// List crs = this.getResult(sql);
// 然后将结果集转化为Excel输出
// 初始化工作
List list = (List) conn.prepareStatement(sql);
intlength = list.size();
intsheetSize =40000;
intsheetNum =1;
if(length % sheetSize >0) {
sheetNum = length / sheetSize +1;
}else{
sheetNum = length / sheetSize;
}
System.out.println(length);
// 创建可写工作薄
////////查询结束///////////////
//导出excel的名称
String fileName ="test_list.xls";
//创建可写工作薄
jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(os);
for(intk=0; k<sheetNum ; k++){
/////////创建sheet[k]开始/////////////
//创建可写工作表
jxl.write.WritableSheet ws = wwb.createSheet(("sheet"+k), k);
//设定第一行的行高
ws.setRowView(0,400);
//将第一列的宽度设为20
ws.setColumnView(0,15);
ws.setColumnView(1,30);
ws.setColumnView(2,30);
ws.setColumnView(3,30);
ws.setColumnView(4,30);
ws.setColumnView(5,15);
ws.setColumnView(6,30);
ws.setColumnView(7,15);
//////////////设置标题开始////////////////
//设置写入字体
jxl.write.WritableFont wf =newjxl.write.WritableFont(jxl.write.WritableFont.ARIAL,11,jxl.write.WritableFont.BOLD,false);
//设置CellFormat
jxl.write.WritableCellFormat wcfF =newjxl.write.WritableCellFormat(wf);
//用于Number的格式
//jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0.00");
//jxl.write.WritableCellFormat priceformat = new jxl.write.WritableCellFormat(nf);
// 把水平对齐方式指定为左对齐
wcfF.setAlignment(jxl.format.Alignment.LEFT);
// 把垂直对齐方式指定为居中对齐
wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
//设置列名
Label labelCF1 =newLabel(0,0,"账期",wcfF);
Label labelCF2 =newLabel(1,0,"网格",wcfF);
Label labelCF3 =newLabel(2,0,"号码",wcfF);
Label labelCF4 =newLabel(3,0,"入网时间",wcfF);
Label labelCF5 =newLabel(4,0,"网络类型",wcfF);
Label labelCF6 =newLabel(5,0,"渠道类型1",wcfF);
Label labelCF7 =newLabel(6,0,"渠道类型2",wcfF);
Label labelCF8 =newLabel(7,0,"渠道代码",wcfF);
Label labelCF9=newLabel(8,0,"渠道名称",wcfF);
Label labelCF10=newLabel(9,0,"城市规划",wcfF);
Label labelCF11=newLabel(10,0,"基站计费规划",wcfF);
Label labelCF12=newLabel(11,0,"上月应收",wcfF);
Label labelCF13=newLabel(12,0,"上月主营",wcfF);
Label labelCF14=newLabel(13,0,"最大基站",wcfF);
//绑定值
ws.addCell(labelCF1);
ws.addCell(labelCF2);
ws.addCell(labelCF3);
ws.addCell(labelCF4);
ws.addCell(labelCF5);
ws.addCell(labelCF6);
ws.addCell(labelCF7);
ws.addCell(labelCF8);
ws.addCell(labelCF9);
ws.addCell(labelCF10);
ws.addCell(labelCF11);
ws.addCell(labelCF12);
ws.addCell(labelCF13);
ws.addCell(labelCF14);
//////////////设置标题结束////////////////
jxl.write.WritableFont wf1 =newjxl.write.WritableFont(jxl.write.WritableFont.ARIAL,11,jxl.write.WritableFont.NO_BOLD,false);
//设置CellFormat
jxl.write.WritableCellFormat wcfF2 =newjxl.write.WritableCellFormat(wf1);
/////////////循环写excel主体开始////////////
for(inti = k*sheetSize; i< (k+1)*sheetSize ; i++ ){
if(i<length ){
list = (List) list.get(i);
Label data1 =newLabel(0, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data2 =newLabel(1, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data3 =newLabel(2, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data4 =newLabel(3, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data5 =newLabel(4, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data6 =newLabel(5, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data7 =newLabel(6, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data8 =newLabel(7, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data9 =newLabel(8, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data10 =newLabel(9, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data11 =newLabel(10, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data12 =newLabel(11, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data13 =newLabel(12, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data14 =newLabel(13, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
ws.addCell(data1);
ws.addCell(data2);
ws.addCell(data3);
ws.addCell(data4);
ws.addCell(data5);
ws.addCell(data6);
ws.addCell(data7);
ws.addCell(data8);
ws.addCell(data9);
ws.addCell(data10);
ws.addCell(data11);
ws.addCell(data12);
ws.addCell(data13);
ws.addCell(data14);
}else{
break;
}
}
/////////////循环写excel主体结束////////////
/////////创建sheet[kk]结束/////////////
}
//我猜测数据太多时，可能会导致内存溢出
wwb.write();
wwb.close();
os.flush();
os.close();
}
}
展开
收起
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关问答
问答排行榜
最热
最新
推荐问答
