java excel导出例子

你的名字 2022-04-12 10:16 178阅读 0赞

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();
                }
            }
        }