------------------------------------------jsp页面
<form action="tporder_downAllExcel" id="form0">
<button type="submit" class="btn btn-info" >点击下载</button></form>
-------------------------------------------struts2
// 获得文件名,字符集编码
private String xlsName;
public String getXlsName() throws UnsupportedEncodingException {
return new String(xlsName.getBytes("UTF-8"), "ISO8859-1");
}
// 获得输入流对象
private InputStream downAllExcelStream;
public InputStream getDownAllExcel() {
return downAllExcelStream;
}
// 设置下载流对象
public String downAllExcel() throws WriteException, IOException {
xlsName = "统配报表.xls";
downAllExcelStream = this.getAllExcel();
return "downAllExcel";
}
//label样式
public static Label lFormat(int a,int b,String str) throws WriteException{
//10号宋体,不加粗,非斜体
WritableFont font = new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.NO_BOLD,false);
WritableCellFormat format = new WritableCellFormat(font);
format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); //BorderLineStyle边框
format.setWrap(false);//不自动换行
format.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
Label l = new Label(b-1,a-1,str,format);
return l;
}
public static void aLabelToSheet(Label l,WritableSheet s){//lable添加到sheet
try {
s.addCell(l);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取所有门店的统配的Excel
public InputStream getAllExcel() throws IOException, WriteException {
List<Orderxz> olist;//从数据库取出下载的对象集合
if(as.getQyzrcode().hashCode()==0){
olist=tporderEbi.getxiazai(date.replace("-", "."),tgm.getFlid());
}else{
olist=tporderEbi.getxzai(as.getQyzrcode(),tgm.getFlid());
}
// 遍历数据,填充到Excel表中
// 创建Excel
// 获得输出流
OutputStream bos = new ByteArrayOutputStream();
// 创建Excel
WritableWorkbook b = Workbook.createWorkbook(bos);
// 创建sheet,0代表第一页
WritableSheet s = b.createSheet("统配订货明细", 0);
s.setColumnView(0, 8);//列宽
s.setColumnView(1, 10);
s.setColumnView(2, 30);
s.setColumnView(3, 15);
s.setColumnView(4, 45);
s.setColumnView(5, 15);
s.setColumnView(6, 45);
s.setColumnView(7, 15);
s.setColumnView(8, 15);
s.setColumnView(9, 15);
s.setColumnView(10, 25);
s.setColumnView(11, 25);
Label l;
// 报表中含有的标题
String[] title = { "序号","店号", "店名", "商品类别id", "商品类别名称","商品八位码","商品名称", "订货量", "订货状态", "商品售价",
"保存时间", "到货时间"};
for (int i = 1; i < title.length + 1; i++) {
s.setRowView(0, 2 * 256);//第一行行高
l = lFormat(1, i, title[i - 1]);//lable样式
aLabelToSheet(l, s);//lable添加到sheet
}
int i = 1;
for(Orderxz z:olist){
List<String> list = new ArrayList<String>();
String index = "" + i;
list.add(index);
list.add(z.getStorecode().trim());
list.add(z.getName().trim());
list.add(z.getLbid().toString().trim());
list.add(z.getLbname().trim());
list.add(z.getGoodscode().trim());
list.add(z.getGoodsname().trim());
list.add(z.getQty().toString().trim());
if(z.getSftj()==0){
list.add("已保存");
}else if(z.getSftj()==1){
list.add("已提交");
}
list.add(z.getRtlprc().toString().trim());
list.add(z.getBcsj().trim());
list.add(z.getDhtime().trim());
for (int j = 1; j <= list.size(); j++) {
s.setRowView(i, (int)(1.5 * 256));//设置每行行高
l = JxlUtil.lFormat(1 + i, j, list.get(j - 1));
JxlUtil.aLabelToSheet(l, s);
}
i++;
}
b.write();
b.close();
// 输出流转输入流
ByteArrayInputStream bis = new ByteArrayInputStream(
((ByteArrayOutputStream) bos).toByteArray());
return bis;
}