java获取excel模板生成excel并提示用户下载或在线打开
我采用的用jxl来处理excel的处理首先准备工作，需要的jar包(附近里)
现在开始着手代码
编写你的excle模板
主要方法如下
我这儿是生成采购单报表 obj 为采购单， objList为采购单详细，out为输出流。这儿就是保存到客户端的关键
public static int ExcTempPort(Object obj,List<Object> objlist,OutputStream out)
{
int result=1;//生成excel是否成功标志
String obj_name=obj.getClass().getSimpleName();
String temp_path=null;
try {
temp_path=ReportUtil.class.getResource("/").toURI().getPath()+"/report/temp_buy.xls";
//我模板是放在src--》report文件下的
} catch (URISyntaxException e1) {
// TODO Auto-generated catch block
}
try {
Workbookworkbook=Workbook.getWorkbook(new File(temp_path));
//File file=new File("D:/"+obj_name+".xls");
//这儿是设置生成excel保存的方式。如果取消注释。那就是保存到硬盘上的，用out就是输出流
WritableWorkbook wwb = Workbook.createWorkbook(out, workbook);
WritableSheet wws = wwb.getSheet("Sheet1");
//格式化Excel中日前格式
DateFormat df = new DateFormat("yyyy-MM-dd");
WritableCellFormat wcfDF = new WritableCellFormat(df);
//设置单元格样式
WritableFont font2=new WritableFont(WritableFont.createFont("宋体"),18,WritableFont.BOLD);
WritableFont font = new WritableFont(WritableFont.createFont("宋体"),12);
WritableFont font3=new WritableFont(WritableFont.createFont("宋体"),16,WritableFont.BOLD);
WritableCellFormat cellFormat1 = new WritableCellFormat(font);
WritableCellFormat cellFormat2=new WritableCellFormat(font2);
WritableCellFormat cellFormat3=new WritableCellFormat(font3);
WritableCellFormat cellFormat4=new WritableCellFormat(font);
cellFormat1.setAlignment(jxl.format.Alignment.CENTRE);// 设置文本对其方式，左对齐还是右对齐
cellFormat2.setAlignment(jxl.format.Alignment.CENTRE);// 设置文本对其方式，左对齐还是右对齐
cellFormat2.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); // Border是jxl.format.Border
cellFormat2.setBackground(Colour.BLUE);
cellFormat3.setBackground(Colour.RED);
cellFormat4.setBackground(Colour.GRAY_25);
cellFormat4.setAlignment(jxl.format.Alignment.CENTRE);
//生成采购单excel报表
Buy buy=(Buy)obj;
//供应商
Label la=(Label) wws.getCell(0,0);//获取第一行第一列
la.setCellFormat(cellFormat2);//设置样式
wws.mergeCells(2, 1, 4, 1);//这是合并行列。意思就是把第二行的第三到第二行的的第五列合并。这儿的行和列下标从0开始
Label a1=new Label(2,1,buy.getFactory()==null?"无":buy.getFactory().getFactory03());//这儿是 把第二行的第三列天填入数据
wws.addCell(a1);//把列添加到Sheet1中
//单据编号
wws.mergeCells(6, 1, 8, 1);
Label a2=new Label(6,1,(buy.getBuy02()==null||buy.getBuy02()=="")?"无":buy.getBuy02());
wws.addCell(a2);
int cell=objlist.size()+3;
//采购单详细
for(int i=3;i<objlist.size()+3;i++)
{
BuyMx buymx=(BuyMx) objlist.get(i-3);
Number b1=new Number(0,i,buymx.getBuyMx01()<1?0:buymx.getBuyMx01());
wws.addCell(b1);
Label b2=new Label(1,i,buymx.getGoods()==null?"无":buymx.getGoods().getGoods03());
wws.addCell(b2);
Label b3=new Label(2,i,buymx.getGoods()==null?"无":buymx.getGoods().getGoods07());
wws.addCell(b3);
Number b4=new Number(3,i,buymx.getBuyMx02()<1?0:buymx.getBuyMx02());
wws.addCell(b4);
Number b5=new Number(4,i,buymx.getBuyMx03()<1?0:buymx.getBuyMx03());
wws.addCell(b5);
Label b6=new Label(5,i,buymx.getGoods()==null?"无":buymx.getGoods().getGoods10());
wws.addCell(b6);
DateTime b7=new DateTime(6,i,buy.getBuy08()==null?new Date():buy.getBuy08(),wcfDF);
wws.addCell(b7);
Label b8=new Label(7,i,(buy.getBuy09()==null||buy.getBuy09()=="")?"无":buy.getBuy09());
wws.addCell(b8);
Label b9=new Label(8,i,(buy.getBuy10()==null||buy.getBuy10()=="")?"无":buy.getBuy10());
wws.addCell(b9);
}
Labelc=new Label(1,cell,"备注：");
wws.addCell(c);
wws.mergeCells(2, cell, 8, cell);
Label c0=new Label(2,cell,(buy.getBuy10()==null||buy.getBuy10()=="")?"无":buy.getBuy10());
wws.addCell(c0);
Label c1_1=new Label(0,cell+1,"下单日期：");
wws.addCell(c1_1);
wws.mergeCells(1, cell+1, 2, cell+1);
DateTime c1=new DateTime(1,cell+1,buy.getBuy07()==null?new Date():buy.getBuy07(),wcfDF);
wws.addCell(c1);
Label c2_2=new Label(3,cell+1,"交货日期：");
System.out.println(buy.getBuy08());
wws.addCell(c2_2);
wws.mergeCells(4, cell+1, 5, cell+1);
DateTime c2=new DateTime(4,cell+1,buy.getBuy08()==null?new Date():buy.getBuy08(),wcfDF);
wws.addCell(c2);
Label c3_3=new Label(6,cell+1,"经手人：");
wws.addCell(c3_3);
wws.mergeCells(7, cell+1, 8, cell+1);
Label c3=new Label(7,cell+1,(buy.getBuy09()==null||buy.getBuy09()=="")?"无":buy.getBuy09());
wws.addCell(c3);
}
wwb.write();
wwb.close();
workbook.close();
out.close();
} catch (BiffException e) {
// TODO Auto-generated catch block
System.out.println(e.toString());
result=1;
} catch (IOException e) {
// TODO Auto-generated catch block
System.out.println(e.toString());
result=1;
} catch (WriteException e) {
// TODO Auto-generated catch block
System.out.println(e.toString());
result=1;
}
return result;
}
上面是工具类下面的是action代码
String filenames = String.valueOf(System.currentTimeMillis()) + "_"
+ stockin.getClass().getSimpleName() + ".xls";//设置excel的名称
response.setContentType("application/vnd.ms-excel;charset=GBK");
response.addHeader("Content-Disposition","attachment; filename=\""+ new String(filenames.getBytes(), "iso8859-1")+ "\"");//这儿处理中文名。以及返回类型的申明
OutputStream os = response.getOutputStream();//输出到客户端
ReportUtil.ExcTempPort(stockin, stockInMxlist, os);//调用工具类
页:
[1]