jxl生成表格并输出供下载
ArrayList<HashMap<String,String>> detailList =*********；
String VIRTUAL_PATH_LOCAL = request.getSession().getServletContext().getRealPath("/");
//open file.
WritableWorkbook book = Workbook.createWorkbook(new File(VIRTUAL_PATH_LOCAL + "download\\remittance\\TFHL_customer.xls"));
WritableSheet sheet = book.createSheet("Sheet",0);
SheetSettings setting = sheet.getSettings();
setting.setBottomMargin(0.1d);
setting.setTopMargin(0.2d);
setting.setLeftMargin(0.5d);
setting.setRightMargin(0.5d);
setting.setPaperSize(PaperSize.A4);
resultPage = "/download/remittance/TFHL_customer.xls";
//总行数
int totalNum=detailList.size();
//总页数
int pageNum=0;
//这里要求打印出来的每一页都显示20行，所以要判断该票退单联的明细的长度是否能被20整除，如果不能则totalNum就要在整除20的基础上加1
if(totalNum%20>0){
pageNum=(totalNum/20)+1;
}else{
pageNum=totalNum/20;
}
//每一页的结束行
int endNum=0;
//每一页的开始行
int beginNum=0;
int j=0;
int curLine=0;
if(pageNum>0){
for(int q=0;q<pageNum;q++){
if(q<totalNum/20){
beginNum=(q*20);
endNum=(beginNum+20)-1;
}else{
beginNum=(q*20);
endNum=totalNum-1;
}
//create Sheet named "Sheet_1". 0 means this is 1st page.
//setting.setScaleFactor(100);
//open file.
//创建表单头行样式，20号，不加粗，无边框
jxl.write.WritableFont wfc2B =new jxl.write.WritableFont(WritableFont.ARIAL,20,
WritableFont.NO_BOLD,false,
UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
//创建表单第二行ya
jxl.write.WritableCellFormat wcfFc1B =new jxl.write.WritableCellFormat(wfc2B);
wcfFc1B.setAlignment(jxl.format.Alignment.LEFT);
//（创建样式1------11号字体，不加粗，无边框，无下划线，黑色字体）
jxl.write.WritableFont wfcx =new jxl.write.WritableFont(WritableFont.ARIAL,12,
WritableFont.NO_BOLD,false,
UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
//设置退付汇联号为右对齐
jxl.write.WritableCellFormat BHFont=new jxl.write.WritableCellFormat(wfcx);
BHFont.setAlignment(jxl.format.Alignment.RIGHT);
//这只表格标题的对齐方式为居中对齐，并且有边框线
jxl.write.WritableCellFormat fontHead=new jxl.write.WritableCellFormat(wfcx);
fontHead.setAlignment(jxl.format.Alignment.CENTRE);
fontHead.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
//最下面字符的字符格式(左对齐)
jxl.write.WritableCellFormat BHFont1=new jxl.write.WritableCellFormat(wfcx);
BHFont1.setAlignment(jxl.format.Alignment.LEFT);
//用于合并单元格时文字居中对齐
jxl.write.WritableCellFormat wcfFctop =new jxl.write.WritableCellFormat(wfcx);
wcfFctop.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
wcfFctop.setAlignment(jxl.format.Alignment.CENTRE);
wcfFctop.setVerticalAlignment(VerticalAlignment.CENTRE);
Label elabel1a = new Label(0, curLine, "上海欣哲国际货物运输代理有限公司：",wcfFc1B);
sheet.addCell(elabel1a);
sheet.mergeCells(0, curLine,5, curLine);
curLine +=1;
Label elabel2a = new Label(0, curLine, "徐汇分部：恒丰路601号2楼2216、2217",BHFont1);
sheet.addCell(elabel2a);
sheet.mergeCells(0, curLine,8, curLine);
curLine +=1;
Label elabel3a = new Label(0, curLine, "二、“签发情况”栏由签发人员填写填写签发成功与否及其他相关信息；",BHFont1);
sheet.addCell(elabel3a);
sheet.mergeCells(0, curLine,8, curLine);
curLine +=1;
Label elabel3a_1 = new Label(0, curLine, " “ 打印情况”栏由打印人员填写打印成功与否及打印数量统计等信息。",BHFont1);
sheet.addCell(elabel3a_1);
sheet.mergeCells(0, curLine,8, curLine);
curLine +=1;
Label elabel4a = new Label(0, curLine, "上海海关进出口报关单证明联签发、打印申请表",wcfFc1B);
sheet.addCell(elabel4a);
sheet.mergeCells(0, curLine,8, curLine);
curLine +=1;
sheet.setRowView(curLine, 300);
Label elabe5a = new Label(0, curLine, "经营单位名称：",BHFont1);
sheet.addCell(elabe5a);
sheet.mergeCells(0, curLine,8, curLine);
sheet.setRowView(curLine, 350);
curLine +=1;
Label elabe6a = new Label(0, curLine, "经营单位外汇管理类别:",BHFont1);
sheet.addCell(elabe6a);
sheet.mergeCells(0, curLine,2, curLine);
Label elabe6d = new Label(3, curLine, "",fontHead);
sheet.addCell(elabe6d);
sheet.mergeCells(3, curLine,3, curLine);
Label elabe6e = new Label(4, curLine, "A类",BHFont1);
sheet.addCell(elabe6e);
sheet.mergeCells(4, curLine,4, curLine);
Label elabe6f = new Label(5, curLine, "",fontHead);
sheet.addCell(elabe6f);
sheet.mergeCells(5, curLine,5, curLine);
Label elabe6g = new Label(6, curLine, "B类,C类",BHFont1);
sheet.addCell(elabe6g);
sheet.mergeCells(6, curLine,6, curLine);
Label elabe6h = new Label(7, curLine, "",BHFont1);
sheet.addCell(elabe6h);
sheet.mergeCells(7, curLine,7, curLine);
Label elabe6i = new Label(8, curLine, "",BHFont1);
sheet.addCell(elabe6i);
sheet.mergeCells(8, curLine,8, curLine);
curLine +=1;
Label elabe7a = new Label(0, curLine, "说明：",BHFont1);
sheet.addCell(elabe7a);
sheet.mergeCells(0, curLine,8, curLine);
sheet.setRowView(curLine, 300);
curLine ++;
Label elabe8a = new Label(0, curLine, "A类企业不提供进口付汇联、出口结汇联打印。 ",BHFont1);
sheet.addCell(elabe8a);
sheet.mergeCells(0, curLine,8, curLine);
sheet.setRowView(curLine, 300);
curLine ++;
Label elabe9a = new Label(0, curLine, "B、C类企业需打印进口付汇联、出口结汇联的，应提供外汇管理类别证明。 ",BHFont1);
sheet.addCell(elabe9a);
sheet.mergeCells(0, curLine,8, curLine);
curLine +=2;
//输出TFHL号
/*sheet.setRowView(curLine, 250);*/
sheet.setRowView(curLine, 300);
Label elabe2 = new Label(6, curLine, "编号:"+TDLHm.get("OrderCode"),BHFont);
sheet.addCell(elabe2);
sheet.mergeCells(6, curLine,8, curLine);
curLine ++;
sheet.setRowView(curLine, 300);
jxl.write.WritableCellFormat wcfFcx =new jxl.write.WritableCellFormat(wfcx);
wcfFcx.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
wcfFcx.setAlignment(jxl.format.Alignment.CENTRE);
wcfFcx.setVerticalAlignment(VerticalAlignment.CENTRE);
wcfFcx.setWrap(true);// 可自动换行
sheet.setColumnView(3,32);
Label elabel11= new Label(3, curLine, "证明联类别",fontHead);
sheet.addCell(elabel11);
sheet.mergeCells(3, curLine, 6, curLine);
curLine++;
sheet.setRowView(curLine, 300);
sheet.setColumnView(0,5);
Label elabel3= new Label(0, curLine-1, "序号",wcfFctop);
sheet.addCell(elabel3);
sheet.mergeCells(0, curLine-1, 0, curLine);
sheet.setColumnView(1,20);
Label elabel4= new Label(1, curLine-1, "报关单编号",wcfFctop);
sheet.addCell(elabel4);
sheet.mergeCells(1, curLine-1, 1, curLine);
sheet.setColumnView(2,15);
Label elabel5= new Label(2, curLine-1, "贸易方式",wcfFctop);
sheet.addCell(elabel5);
sheet.mergeCells(2, curLine-1, 2, curLine);
Label elabel6= new Label(7, curLine-1, "签发情况",wcfFctop);
sheet.addCell(elabel6);
sheet.mergeCells(7, curLine-1, 7, curLine);
Label elabel7= new Label(8, curLine-1, "打印情况",wcfFctop);
sheet.addCell(elabel7);
sheet.mergeCells(8, curLine-1, 8, curLine);
sheet.setColumnView(3,8);
//经营单位栏只作显示看，不打印出来
Label elabel9= new Label(9, curLine-1, "经营单位",wcfFctop);
sheet.addCell(elabel9);
sheet.mergeCells(9, curLine-1, 9, curLine);
sheet.setColumnView(9,30);
Label elabel156= new Label(3, curLine, "付汇联",fontHead);
sheet.addCell(elabel156);
sheet.mergeCells(3, curLine, 3, curLine);
sheet.setColumnView(4,8);
Label elabel12= new Label(4, curLine, "结汇联",fontHead);
sheet.addCell(elabel12);
sheet.mergeCells(4, curLine, 4, curLine);
sheet.setColumnView(5,8);
Label elabel13= new Label(5, curLine, "退税联",fontHead);
sheet.addCell(elabel13);
sheet.mergeCells(5, curLine, 5, curLine);
sheet.setColumnView(6,8);
Label elabel14= new Label(6, curLine, "核销联",fontHead);
sheet.addCell(elabel14);
sheet.mergeCells(6, curLine, 6, curLine);
curLine++;
sheet.setRowView(curLine, 300);
jxl.write.WritableFont wfcx1 =new jxl.write.WritableFont(WritableFont.ARIAL,10,
WritableFont.NO_BOLD,false,
UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);
jxl.write.WritableCellFormat wcfFcx1 =new jxl.write.WritableCellFormat(wfcx1);
wcfFcx1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
wcfFcx1.setAlignment(jxl.format.Alignment.CENTRE);
wcfFcx1.setVerticalAlignment(VerticalAlignment.CENTRE);
int f=0;
for(int i=beginNum; i<=endNum;i++){
f++;
j=i+1;
HashMap<String,String> detailHm=detailList.get(i);
sheet.setRowView(curLine, 400);
//编号
Label elabel140= new Label(0, curLine,""+j+"",wcfFcx);
sheet.addCell(elabel140);
sheet.mergeCells(0, curLine, 0, curLine);
Label elabel141= new Label(1, curLine,""+detailHm.get("DeclearNO")+"",wcfFcx1);
sheet.addCell(elabel141);
sheet.mergeCells(1, curLine, 1, curLine);
//贸易方式
Label elabel142= new Label(2, curLine,""+detailHm.get("TradeType")+"",wcfFcx);
sheet.addCell(elabel142);
sheet.mergeCells(2, curLine, 2, curLine);
//付汇联
Label elabel143= new Label(3, curLine,"",wcfFcx);
sheet.addCell(elabel143);
sheet.mergeCells(3, curLine, 3, curLine);
//结汇联
Label elabel144= new Label(4, curLine,"",wcfFcx);
sheet.addCell(elabel144);
sheet.mergeCells(4, curLine, 4, curLine);
//退税联,TradeTypeID为贸易方式为：一般贸易，货样广告品Ｂ，进料对口，进料非对口
Label elabel145= new Label(5, curLine,"",wcfFcx);
sheet.addCell(elabel145);
sheet.mergeCells(5, curLine, 5, curLine);
//核销联 进料对口，进料非对口，
Label elabel146= new Label(6, curLine,"",wcfFcx);
sheet.addCell(elabel146);
sheet.mergeCells(6, curLine, 6, curLine);
//签发情况
Label elabel147= new Label(7, curLine,"",wcfFcx);
sheet.addCell(elabel147);
sheet.mergeCells(7, curLine, 7, curLine);
//打印情况
Label elabel148= new Label(8, curLine,"",wcfFcx);
sheet.addCell(elabel148);
sheet.mergeCells(8, curLine, 8, curLine);
//经营单位栏只作显示看，不打印出来
Label elabel149= new Label(9, curLine,""+detailHm.get("JDCustomerName")+"",wcfFcx);
sheet.addCell(elabel149);
sheet.mergeCells(9, curLine, 9, curLine);
curLine++;
}
while(f<20){
sheet.setRowView(curLine, 400);
//编号
Label elabel140F= new Label(0, curLine,"",wcfFcx);
sheet.addCell(elabel140F);
sheet.mergeCells(0, curLine, 0, curLine);
//报关单号
Label elabel141F= new Label(1, curLine,"",wcfFcx1);
sheet.addCell(elabel141F);
sheet.mergeCells(1, curLine, 1, curLine);
//贸易方式
Label elabel142F= new Label(2, curLine,"",wcfFcx);
sheet.addCell(elabel142F);
sheet.mergeCells(2, curLine, 2, curLine);
//付汇联
Label elabel143G= new Label(3, curLine,"",wcfFcx);
sheet.addCell(elabel143G);
sheet.mergeCells(3, curLine, 3, curLine);
//结汇联
Label elabel144F= new Label(4, curLine,"",wcfFcx);
sheet.addCell(elabel144F);
sheet.mergeCells(4, curLine, 4, curLine);
//退税联
Label elabel145F= new Label(5, curLine,"",wcfFcx);
sheet.addCell(elabel145F);
sheet.mergeCells(5, curLine, 5, curLine);
//核销联
Label elabel146F= new Label(6, curLine,"",wcfFcx);
sheet.addCell(elabel146F);
sheet.mergeCells(6, curLine, 6, curLine);
//签发情况
Label elabel147F= new Label(7, curLine,"",wcfFcx);
sheet.addCell(elabel147F);
sheet.mergeCells(7, curLine, 7, curLine);
//打印情况
Label elabel148F= new Label(8, curLine,"",wcfFcx);
sheet.addCell(elabel148F);
sheet.mergeCells(8, curLine, 8, curLine);
//经营单位
Label elabel149F= new Label(9, curLine,"",wcfFcx);
sheet.addCell(elabel149F);
sheet.mergeCells(9, curLine, 9, curLine);
f++;
curLine++;
}
sheet.setRowView(curLine, 300);
jxl.write.WritableCellFormat wcfFc1 =new jxl.write.WritableCellFormat(wfcx);
wcfFc1.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
wcfFc1.setAlignment(jxl.format.Alignment.LEFT);
wcfFc1.setVerticalAlignment(VerticalAlignment.CENTRE);
Label elabeln1= new Label(0, curLine, "申请单位：上海欣哲国际货物运输代理有限公司",wcfFc1);
sheet.addCell(elabeln1);
sheet.mergeCells(0, curLine, 3, curLine);
Label elabeln2= new Label(4, curLine, "经办人章:",wcfFc1);
sheet.addCell(elabeln2);
sheet.mergeCells(4, curLine, 8, curLine+1);
curLine++;
sheet.setRowView(curLine, 300);
Label elabeln3= new Label(0, curLine, "（公章）",wcfFc1);
sheet.addCell(elabeln3);
sheet.mergeCells(0, curLine, 3, curLine);
curLine+=2;
Label elaben6 = new Label(0, curLine, "打印人员:"+username,BHFont1);
sheet.addCell(elaben6);
sheet.mergeCells(0, curLine,2, curLine);
Label elaben7 = new Label(3, curLine, "盖章人员:",BHFont1);
sheet.addCell(elaben7);
sheet.mergeCells(3, curLine,5, curLine);
Label elaben8 = new Label(6, curLine, "签收人员:",BHFont1);
sheet.addCell(elaben8);
sheet.mergeCells(6, curLine,8, curLine);
curLine++;
sheet.setRowView(curLine, 300);
Label elaben9 = new Label(0, curLine, "日期: "+time,BHFont1);
sheet.addCell(elaben9);
sheet.mergeCells(0, curLine,2, curLine);
Label elaben10 = new Label(3, curLine, "日期:",BHFont1);
sheet.addCell(elaben10);
sheet.mergeCells(3, curLine,5, curLine);
Label elaben11 = new Label(6, curLine, "日期:",BHFont1);
sheet.addCell(elaben11);
sheet.mergeCells(6, curLine,8, curLine);
setting.setPrintArea(0,0,12, curLine);
curLine+=2;
sheet.setRowView(curLine, 300);
Label elaben112 = new Label(0, curLine, "填写说明：",BHFont1);
sheet.addCell(elaben112);
sheet.mergeCells(0, curLine,1, curLine);
curLine++;
sheet.setRowView(curLine, 300);
Label elaben113 = new Label(0, curLine, "一、本申请表一式三联。一联由海关留存，一联由打印部门留存，一联由企业留存。",BHFont1);
sheet.addCell(elaben113);
sheet.mergeCells(0, curLine,8, curLine);
curLine++;
sheet.setRowView(curLine, 300);
Label elaben114 = new Label(0, curLine, "二、“签发情况”栏由签发人员填写签发成功与否及其他相关信息；“打印情况”栏",BHFont1);
sheet.addCell(elaben114);
sheet.mergeCells(0, curLine,9, curLine);
curLine++;
Label elaben1145 = new Label(0, curLine, "由打印人员填写打印成功与否及打印数量统计等信息。",BHFont1);
sheet.addCell(elaben1145);
sheet.mergeCells(0, curLine,9, curLine);
curLine++;
Label elaben115 = new Label(0, curLine, "",BHFont1);
sheet.addCell(elaben115);
sheet.mergeCells(0, curLine,0, curLine);
sheet.addRowPageBreak(curLine+1);
setting.setPrintArea(0,0,8, curLine);
curLine++;
}
}
book.write();
book.close();
}catch(Exception e){
e.printStackTrace();
GlobalFunc.printErrorInfo(getClass().getName(),
request.getParameter("action"),
GlobalFunc.getUserName(request));
request.setAttribute(Constant.ERROR_EXCEPTION, "请联系管理员！");
resultPage = Constant.ERROR_PAGE;
}
return resultPage;
