1.导入jxl的架包
2.
String title ="统计表";
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();
response.setCharacterEncoding("ISO8859-1");
response.setHeader("Content-Disposition", "attachment; filename="+new String(title.getBytes("GBK"), "ISO8859-1")+".xls");//fileChName为下载时用户看到的文件名
response.setContentType("application/msexcel");// 定义输出类型
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/octet-stream");
WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
WritableSheet wsheet = wbook.createSheet(title, 0); // sheet名称
//设置每列的宽度为自适应
wsheet.setColumnView(0, 6);
wsheet.setColumnView(1, 20);
wsheet.setColumnView(2, 20);
wsheet.setColumnView(3, 12);
wsheet.setColumnView(4, 18);
wsheet.setColumnView(5, 20);
wsheet.setColumnView(6, 13);
wsheet.setColumnView(7, 10);
wsheet.setColumnView(8, 10);
wsheet.setColumnView(9, 10);
wsheet.setColumnView(10, 35);
wsheet.setRowView(0, 500);
// 设置excel标题
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat wcfFC = new WritableCellFormat(wfont);
wcfFC.setAlignment(Alignment.CENTRE);
//wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.GRAY_50);
Label label = new Label(0, 0, title, wcfFC);
wsheet.mergeCells(0,0, 10, 0);//合并垮了3行。
wsheet.addCell(label);
//设置内容属性
WritableCellFormat wcf = new WritableCellFormat();
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.GRAY_50);
WritableFont font = new jxl.write.WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD,false);
WritableCellFormat twcf = new WritableCellFormat(font);
twcf.setAlignment(Alignment.CENTRE);
twcf.setVerticalAlignment(VerticalAlignment.CENTRE);
twcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.GRAY_50);
// 开始生成主体内容
wsheet.addCell(new Label(0, 1, "序号", twcf));
wsheet.addCell(new Label(1, 1, "编号", twcf));
wsheet.addCell(new Label(2, 1, "名称", twcf));
wsheet.addCell(new Label(3, 1, "日期", twcf));
wsheet.addCell(new Label(4, 1, "时间", twcf));
wsheet.addCell(new Label(5, 1, "证号", twcf));
wsheet.addCell(new Label(6, 1, "项目", twcf));
wsheet.addCell(new Label(7, 1, "人数", twcf));
wsheet.addCell(new Label(8, 1, "实人数", twcf));
wsheet.addCell(new Label(9, 1, "金额", twcf));
wsheet.addCell(new Label(10, 1, "备注", twcf));
//循环添加内容
int index=0;
int num=0;
//循环添加内容
for(int i=0;i<list.size();i++)
{
Bills bill=list.get(i);
List<Orders> orderList=orderService.findOrderByBillId(bill.getBillId());
for (Orders orders: orderList){
wsheet.addCell(new Label(0, index+2, ""+(i+1), wcf));
wsheet.addCell(new Label(1, index+2, bill.getBillSn(), wcf));
wsheet.addCell(new Label(2, index+2, bill.getAgencyName(), wcf));
wsheet.addCell(new Label(3, index+2, sf.format(bill.getReserveTime()) , wcf));
wsheet.addCell(new Label(4, index+2, bill.getReendDate()+"-"+bill.getRestartDate(), wcf));
wsheet.addCell(new Label(5, index+2, "", wcf));
wsheet.addCell(new Label(6, index+2,"", wcf));
wsheet.addCell(new Label(7, index+2, orders.getReserveNum()+"", wcf));
wsheet.addCell(new Label(8, index+2, orders.getRealityNum()+"", wcf));
wsheet.addCell(new Label(9,index+2, orders.getPay()+"", wcf));
wsheet.addCell(new Label(10,index+2, "", wcf));
index++;
}
wsheet.mergeCells(0, num+2, 0, index+1);
wsheet.mergeCells(1, num+2, 1, index+1);
wsheet.mergeCells(2, num+2, 2, index+1);
wsheet.mergeCells(3, num+2, 3, index+1);
wsheet.mergeCells(4, num+2, 4, index+1);
wsheet.mergeCells(5, num+2, 5, index+1);
num=index;
}
wbook.write();
wbook.close();
os.close();