java 导出excel

直入主题。上代码。

//导出excel

@RequestMapping(value="excel")

public void excel(String excelname,String title,@RequestParam("district3") String district3,@RequestParam("district4") String district4,@RequestParam("pname")String pname,@RequestParam("ename") String ename, OutputStream os,HttpServletResponse response) throws Exception{

response.setContentType("application/vnd.ms-excel");

//response.addHeader("Content-Disposition", "attachment;filename=\""+excelname+".xls\"");

String excelname_=new String(excelname.getBytes("gbk"), "ISO8859-1");

response.addHeader("Content-Disposition", "attachment; filename=\""

+ excelname_ + ".xls\"");

WritableWorkbook workbook = Workbook.createWorkbook(os);

WritableSheet sheet = workbook.createSheet(excelname, 0); //生成名为excelname的工作表，參数0表示这是第一页 

   WritableFont font1= new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD); 

WritableCellFormat format1=new WritableCellFormat(font1); 

format1.setAlignment(jxl.format.Alignment.CENTRE);//设置为居中

format1.setVerticalAlignment(VerticalAlignment.CENTRE); //设置垂直居中;

format1.setBorder(jxl.format.Border.RIGHT,jxl.format.BorderLineStyle.THIN,Colour.BLACK);

format1.setBorder(jxl.format.Border.BOTTOM,jxl.format.BorderLineStyle.NONE);

//给列设置宽度;  

sheet.setColumnView(0,10);

sheet.setColumnView(1,35);

        sheet.setColumnView(2, 35);  

        sheet.setColumnView(3, 15);  

        sheet.setColumnView(4, 15);  

        sheet.setColumnView(5, 15);

        sheet.setColumnView(6, 35);

        sheet.setColumnView(7, 35);

        sheet.setColumnView(8, 20);

        sheet.setColumnView(9, 35);

sheet.mergeCells(0,0,9,0);

Label label=new Label(0,0,title,format1); //创建表头, 在Label对象的构造子中指名单元格位置是第一列第一行(0,0) 

   sheet.addCell(label); 

   sheet.setRowView(0,800);//设置第一行高度

   

   //设置填报单位

   sheet.mergeCells(0,1,9,1);

   font1= new WritableFont(WritableFont.TIMES,14,WritableFont.BOLD); 

   format1=new WritableCellFormat(font1); 

   format1.setBorder(jxl.format.Border.RIGHT,jxl.format.BorderLineStyle.THIN,Colour.BLACK);

   format1.setVerticalAlignment(VerticalAlignment.CENTRE); //设置垂直居中;

label=new Label(0,1,"填报单位： （盖章）",format1); //创建表头, 在Label对象的构造子中指名单元格位置是第一列第一行(0,0) 

   sheet.addCell(label); 

   sheet.setRowView(1,600);//设置第一行高度

   

   sheet.setRowView(2,500);

font1=new WritableFont(WritableFont.createFont("楷体_GB2312"),12,WritableFont.BOLD);

WritableCellFormat format2=new WritableCellFormat(font1);

format2.setAlignment(jxl.format.Alignment.CENTRE);//设置为居中

format2.setVerticalAlignment(VerticalAlignment.CENTRE); //设置垂直居中;

//format2.set

//设置边框

   format2.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN,Colour.BLACK);

     

   int startindex=2;

label=new Label(0,startindex,"序号",format2); 

   sheet.addCell(label); 

label=new Label(1,startindex,"演练组织单位",format2);

sheet.addCell(label); 

label=new Label(2,startindex,"演练科目名称",format2);

sheet.addCell(label); 

label=new Label(3,startindex,"演练类型",format2);

sheet.addCell(label); 

label=new Label(4,startindex,"演练内容",format2);

sheet.addCell(label); 

label=new Label(5,startindex,"演练时间",format2);

sheet.addCell(label);

label=new Label(6,startindex,"演练地点",format2);

sheet.addCell(label);

label=new Label(7,startindex,"拟參加应急演练的单位及部门",format2);

sheet.addCell(label);

label=new Label(8,startindex,"拟參加演练的人数",format2);

sheet.addCell(label);

label=new Label(9,startindex,"备注",format2);

sheet.addCell(label);

/*生成一个保存数字的单元格 

必须使用Number的完整包路径，否则有语法歧义 

单元格位置是第二列。第一行，值为789.123 

jxl.write.Number number = new jxl.write.Number(2,0,789.123); 

sheet.addCell(number); */

jxl.write.Number number=null;

//从数据库查询数据

Map<String, String> map=new HashMap<String, String>();

Map<String, String> like=new HashMap<String, String>();

if(StringUtils.notNull(district3)){

map.put("s.district3", district3);

}

if(StringUtils.notNull(district3)){

map.put("s.district4", district4);

}

if(StringUtils.notNull(pname)){

map.put("plansdrillname", pname);

}

if(StringUtils.notNull(ename)){

like.put("s.enterprisename", ename);

}

List<CpPlansdrillDto> dtos = cpPlansdrillService.getStatisticsList(map, like, 1,10000);

font1=new WritableFont(WritableFont.createFont("楷体_GB2312"),10,WritableFont.NO_BOLD);

format2=new WritableCellFormat(font1);

format2.setAlignment(jxl.format.Alignment.CENTRE);//设置为居中

format2.setVerticalAlignment(VerticalAlignment.CENTRE); //设置垂直居中;

format2.setWrap(true);//自己主动换行

format2.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN,Colour.BLACK);

//数据保存

for (int i = 0; i <dtos.size(); i++) 

{    

CpPlansdrillDto m=dtos.get(i);

String data="";

if(m.getPlansdrilldate()!=null){

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  

java.util.Date date=new java.util.Date();  

data=sdf.format(date);

}

int pcount=0;

if(StringUtils.notNull(m.getPlansdrillcount())){

pcount=Integer.parseInt(m.getPlansdrillcount());

}

String deparmetn=StringUtils.notNull(m.getPlansdrilldepartment())?

m.getPlansdrilldepartment():"";

number = new jxl.write.Number(0,i+startindex+1,i+1,format2); 

   sheet.addCell(number);

label=new Label(1,i+startindex+1,m.getPlansdrillunits(),format2);

sheet.addCell(label);

label=new Label(2,i+startindex+1,m.getPlansdrillname(),format2);

sheet.addCell(label);

label=new Label(3,i+startindex+1,m.getPlansdrilltype().equals("1")?

"现场演练":"桌面演练",format2);

sheet.addCell(label);

label=new Label(4,i+startindex+1,m.getPlansdrillcontent().equals("1")?

"单项演练":"综合演练",format2);

sheet.addCell(label);

label=new Label(5,i+startindex+1,data,format2);  

   sheet.addCell(label); 

label=new Label(6,i+startindex+1,m.getPlansdrilladdress(),format2);

sheet.addCell(label);

label=new Label(7,i+startindex+1,deparmetn,format2);

sheet.addCell(label);

number=new jxl.write.Number(8,i+startindex+1,pcount,format2);

   sheet.addCell(number);

label=new Label(9,i+startindex+1,m.getNote(),format2);

sheet.addCell(label);

    }

workbook.write();

workbook.close();

os.close();

response.flushBuffer();

}