public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
BusPensionStandardInfoForm busPensionStandardInfoForm = (BusPensionStandardInfoForm)form;
Long stLevel = getLongReturnNull(request, "stLevel");
BusPensionStandardInfoQueryBean busPensionStandardInfoQueryBean = new BusPensionStandardInfoQueryBean();
if(busPensionStandardInfoForm != null)
BeanUtils.copyProperties(busPensionStandardInfoQueryBean, busPensionStandardInfoForm);
busPensionStandardInfoQueryBean.setUseFlag(Long.valueOf(1));
busPensionStandardInfoQueryBean.setStLevel(stLevel);
Page page = PageFactory.getPage(request);
//导出的数据信息
List<?> list = busPensionStandardInfoService.queryBusPensionStandardInfo(busPensionStandardInfoQueryBean, page);
//创建头标题
String [] arr={"年度","标准"};
List<String[]> listArr=new ArrayList<String[]>();
String [] arrStr=new String[2];
for(int i=0;i<list.size();i++){
BusPensionStandardInfo busPensionStandardInfo=(BusPensionStandardInfo) list.get(i);
arrStr[0]=busPensionStandardInfo.getYear();
arrStr[1]=busPensionStandardInfo.getStandardName();
listArr.add(arrStr);
}
//创建sheet页名称
String sheetName="中央救助标准";
String fileName="救助标准";
createExcel(listArr,arr,sheetName,fileName,response);
return null;
};
/**
* 导出Excel
* @param list:结果集合 样式{[1,2,3],[4,5,6],[7,8.9]}
* @param arr:标题数组[姓名,年龄,学历]
* @param sheetName:工作表名称
* @param fileName 导出文件名
* @param response:返回相应
*/
public static void createExcel(List list,String[] arr,String sheetName,String fileName,HttpServletResponse response){
sheetName = sheetName!=null && !sheetName.equals("")?sheetName:"sheet1";
WritableWorkbook wook = null;//可写的工作薄对象
try {
//wook = Workbook.createWorkbook(new File(filePath));//指定导出的目录和文件名 如:D:\\test.xls
//设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
OutputStream os = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel; charset=GBK");
response.setHeader("Content-Disposition", new String(("attachment; filename=" + fileName +".xls").getBytes("GBK"), "ISO-8859-1"));
wook = Workbook.createWorkbook(os);
//设置头部字体格式
WritableFont font = new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD,
false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
//应用字体
WritableCellFormat wcfh = new WritableCellFormat(font);
//设置其他样式
wcfh.setAlignment(Alignment.CENTRE);//水平对齐
wcfh.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
wcfh.setBorder(Border.ALL, BorderLineStyle.THIN);//边框
wcfh.setBackground(Colour.LIGHT_GREEN);//背景色
wcfh.setWrap(false);//不自动换行
//设置内容日期格式
DateFormat df = new DateFormat("yyyy-MM-dd");
//应用日期格式
WritableCellFormat wcfc = new WritableCellFormat(df);
wcfc.setAlignment(Alignment.CENTRE);
wcfc.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
wcfc.setBorder(Border.ALL, BorderLineStyle.THIN);//边框
wcfc.setWrap(false);//不自动换行
//普通内容设置为文本格式
WritableCellFormat wcfptc = new WritableCellFormat(NumberFormats.TEXT);
wcfptc.setAlignment(Alignment.CENTRE);
wcfptc.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
wcfptc.setBorder(Border.ALL, BorderLineStyle.THIN);//边框
wcfptc.setWrap(false);//不自动换行
//创建工作表
WritableSheet sheet = wook.createSheet(sheetName, 0);
SheetSettings setting = sheet.getSettings();
setting.setVerticalFreeze(1);//冻结窗口头部
int columnIndex = 0; //列索引
List<String> methodNameList = new ArrayList<String>();
if(arr!=null){
//开始导出表格头部
for (int i =0;i<arr.length;i++) {
sheet.setColumnView(i, 30);
// 应用wcfh样式创建单元格
sheet.addCell(new Label(columnIndex, 0, arr[i], wcfh));
//记录字段的顺序,以便于导出的内容与字段不出现偏移
methodNameList.add(arr[i]);
columnIndex++;
}
if(list!=null && list.size()>0){
//导出表格内容
int count=1;
for (int i = 0,len = list.size(); i < len; i++) {
Object []arr2=(Object[]) list.get(i);
for(int j=0;j<arr2.length;j++){
//sheet.addCell(new Label(j, count, arr2[j]==null?"":arr2[j].toString(), wcfc));
sheet.addCell(new Label(j, count, arr2[j]==null?"":arr2[j].toString(), wcfptc));
}
count++;
/*Map<String, Object> map = (Map<String, Object>) list.get(i);
Set<String> set = map.keySet();
for (Iterator<String> it = set.iterator();it.hasNext();) {
for (int j = 0; j < methodNameList.size(); j++) {
String key = it.next();
sheet.addCell(new Label(j, i+1, map.get(key).toString(), wcfc));
}
}*/
}
}
wook.write();
wook.close();
os.close();
System.out.println("导出Excel成功!");
}else{
throw new Exception("传入参数不合法");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
try {
if(wook!=null){
wook.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
}