jxl 分sheet从数据库导出到excel
package com.system.dao;
import java.io.*;
import java.sql.*;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.jsp.PageContext;
import javax.sql.rowset.CachedRowSet;
import org.apache.taglibs.standard.tag.el.core.OutTag;
import com.sun.rowset.CachedRowSetImpl;
import com.system.DB.DBManager;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class getExcel {
private static Connection conn = null;
private static PreparedStatement pstmt = null;
private static ResultSet rs = null;
private static CachedRowSet crs;
private WritableCell labelCF11;
@SuppressWarnings("static-access")
public void getExcel(String sql) {
try {
conn = DBManager.getDBManager().connection;
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
crs = new CachedRowSetImpl();
crs.populate(rs);
System.out.println(null == crs);
return;
} catch (Exception e) {
// TODO: handle exception
return;
}
}
public void getExcelResult(String sql, ServletOutputStream os)
throws SQLException, IOException, WriteException {
// 首先获取结果集
// 这里获取RowSet的方法
// List crs = this.getResult(sql);
// 然后将结果集转化为Excel输出
// 初始化工作
List list = (List) conn.prepareStatement(sql);
int length = list.size();
int sheetSize = 40000;
int sheetNum = 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(int k=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 = new jxl.write.WritableFont(jxl.write.WritableFont.ARIAL, 11,jxl.write.WritableFont.BOLD, false);
//设置CellFormat
jxl.write.WritableCellFormat wcfF = new jxl.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 = new Label(0, 0, "账期",wcfF);
Label labelCF2 = new Label(1, 0, "网格",wcfF);
Label labelCF3 = new Label(2, 0, "号码",wcfF);
Label labelCF4 = new Label(3, 0, "入网时间",wcfF);
Label labelCF5 = new Label(4, 0, "网络类型",wcfF);
Label labelCF6 = new Label(5, 0, "渠道类型1",wcfF);
Label labelCF7 = new Label(6, 0, "渠道类型2",wcfF);
Label labelCF8 = new Label(7, 0, "渠道代码",wcfF);
Label labelCF9=new Label(8, 0, "渠道名称",wcfF);
Label labelCF10=new Label(9, 0, "城市规划",wcfF);
Label labelCF11=new Label(10, 0, "基站计费规划",wcfF);
Label labelCF12=new Label(11,0, "上月应收",wcfF);
Label labelCF13=new Label(12,0, "上月主营",wcfF);
Label labelCF14=new Label(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 = new jxl.write.WritableFont(jxl.write.WritableFont.ARIAL, 11,jxl.write.WritableFont.NO_BOLD, false);
//设置CellFormat
jxl.write.WritableCellFormat wcfF2 = new jxl.write.WritableCellFormat(wf1);
/////////////循环写excel主体开始////////////
for(int i = k*sheetSize; i< (k+1)*sheetSize ; i++ ){
if(i<length ){
list = (List) list.get(i);
Label data1 = new Label(0, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data2 = new Label(1, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data3 = new Label(2, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data4 = new Label(3, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data5 = new Label(4, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data6 = new Label(5, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data7 = new Label(6, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data8 = new Label(7, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data9 = new Label(8, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data10 = new Label(9, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data11 = new Label(10, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data12 = new Label(11, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data13 = new Label(12, i+1-(k*sheetSize) , (""+list.get(i)),wcfF2);
Label data14 = new Label(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();
}
}如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
public static void main(String[] args) throws Exception { WritableWorkbook wwb = Workbook.createWorkbook(new File("test.xls")); for (int i = 0; i < 10; i++) { WritableSheet sheet = wwb.createSheet("测试" + i, i); Label lable = new Label(0, 0, "测试内容" + i); sheet.addCell(lable); } wwb.write(); wwb.close(); System.out.println("完成"); }可以写入多个sheet的
问，不能正确分sheet导出
在技术问答中发这个没头没脑的东西是要提问，还是要分享？