java使用jxl进行Excel导入导出

导入Excel

/**
	 * 
	 * @author ym
	 * 描述：导入excel文件
	 * @param i_class：导入的文件中存放实体类的class
	 * @param map：导入的模板（excel里的表头和实体类里的列对应）
	 * @param file：需要导入的文件
	 */
	public static Object Import(Class i_class,Map<String,String> map, File file){
		List<Object> list = new ArrayList<Object>();
		int wrong=0;
		String[] attrs = null;
		Workbook wb = null;
		StringBuffer message = new StringBuffer();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		try {
			wb = Workbook.getWorkbook(file);
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		//得到所有工作表
		Sheet[] sheets = wb.getSheets();
		//判断所有工作表中有无错误的列名
		List<String[]> ColNames=new ArrayList<String[]>();
		String wrongCols="出错列名为：";
		for(Sheet s : sheets){
			//获取这一行的单元格
			Cell[] cells = s.getRow(0);
			attrs = new String[cells.length];
			//判断excel中的列和实体类中的属性是否对照
			for(int i=0;i<cells.length;i++){
				Cell c = cells[i];
				if(!map.containsKey(c.getContents())){
					if (!wrongCols.equals("出错列名为："))
						wrongCols+=",";
					wrongCols+=c.getContents();
				}else{
					attrs[i]=map.get(c.getContents());
				}
			}
			ColNames.add(attrs);
		}
		//当列名有误时返回特定字符
		if(!wrongCols.equals("出错列名为：")){
			return wrongCols;
		}
		String MethodName;
		Method method;
		//判断有无数据类型错误
		for(int t=0;t<sheets.length;t++){
			int row = 1;
			//单元格数据内容
			Cell[] cells = sheets[t].getRow(row++);
			//列名
			Cell[] top = sheets[t].getRow(0);
			attrs = ColNames.get(t);
			int result=0;
			for(int i=0;i<top.length;i++){
				if("".equals(top[i])){
					result++;
				}
			}
			if(result!=top.length){
				message.append("第"+(t+1)+"张工作表：");
			}
			while(cells!=null&&cells.length>0){
				try {
					Object o = i_class.newInstance();
					for(int i=0;i<attrs.length;i++){
						if(cells[i].getContents()==null||"".equals(cells[i].getContents())){
							continue;
						}
						MethodName = "set"+attrs[i];
						//Integer类型
						try{
							method = i_class.getMethod(MethodName,Integer.class);
							Integer.parseInt(cells[i].getContents());
							continue;
						}catch(NoSuchMethodException e){
							
						}catch(Exception e){
							wrong++;
							message.append(attrs[i]+"列，"+row+"行；");
							continue;
						}
						//Double类型
						try{
							method = i_class.getMethod(MethodName, Double.class);
							Double.parseDouble(cells[i].getContents());
							continue;
						}catch(NoSuchMethodException e){
							
						}catch(Exception e1){
							e1.printStackTrace();
							wrong++;
							message.append(attrs[i]+"列，"+row+"行；");
							continue;
						}
						//Date类型
						try{
							method = i_class.getMethod(MethodName, Date.class);
							format.format(format.parse(cells[i].getContents()));
							continue;
						}catch(NoSuchMethodException e){
							
						}catch(Exception e2){
							wrong++;
							message.append(attrs[i]+"列，"+row+"行；");
							continue;
						}
						//String类型
						try{
							method = i_class.getMethod(MethodName, String.class);
						}catch(NoSuchMethodException e){
							wrong++;
							message.append(attrs[i]+"列，"+row+"行；");
						}
					}
					cells = sheets[t].getRow(row++);
				} catch (InstantiationException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				}  catch (Exception e) {
					break;
				}
			}
			message.append("\n");
		}
		if(wrong>0){
			return "数据出错总数为："+wrong+"\n分别为："+message.toString();
		}
		
		//验证文件无错误后，开始插入
		for(int t=0;t<sheets.length;t++){
			attrs = ColNames.get(t);
			Cell[] cells = sheets[t].getRow(1);
			int row = 2;
			while(cells!=null&&cells.length>0){
				try {
					Object o = i_class.newInstance();
					for(int i=0;i<attrs.length;i++){
						if(cells[i].getContents()==null||"".equals(cells[i].getContents())){
							continue;
						}
						MethodName = "set"+attrs[i];
						//得到该属性对应的set方法，参数有可能是Integer、Double、Date、String
						try{
							method = i_class.getMethod(MethodName,Integer.class);
							method.invoke(o, Integer.parseInt(cells[i].getContents()));
						}catch(Exception e){
							try{
								method = i_class.getMethod(MethodName, Double.class);
								method.invoke(o, Double.parseDouble(cells[i].getContents()));
							}catch(Exception e1){
								try{
									method = i_class.getMethod(MethodName, Date.class);
									method.invoke(o, format.format(format.parse(cells[i].getContents())));
								}catch(Exception e2){
									method = i_class.getMethod(MethodName, String.class);
									method.invoke(o, cells[i].getContents());
								}
							}
						}
					}
					list.add(o);
					cells = sheets[t].getRow(row++);
				} catch (InstantiationException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} catch (Exception e) {
					break;
				}
			}
		}
		return list;
	}

导出Execl

/**
	 * 
	 * @author ym
	 * 描述：导出Excel文件
	 * @param list：实体类的集合
	 * @param path：导出excel文件的路径名
	 * @param name：导出excel文件的文件名
	 * @param columnName：要导出的列名（开头第一个字母大写）
	 */
	public static void Export(List list, String path, String name, String[] columnName, String[] columnTitle){
		OutputStream os = null;
		try {
			os = new FileOutputStream(path+name+".xls");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Class bean = list.get(0).getClass();
		try {
			//建立excel文件 
			WritableWorkbook wbook = Workbook.createWorkbook(os);
			String bean_name = bean.getName();
			bean_name = bean_name.substring(bean_name.lastIndexOf(".")+1);
			//生成名为bean_name所代表的工作表，参数0表示这是第一页
			WritableSheet wsheet = wbook.createSheet(bean_name, 0);
			//是否显示网格
			//wsheet.getSettings().setShowGridLines(true); 
			//设置列宽
			//wsheet.getSettings().setDefaultColumnWidth(10);
			//设置行高
			//wsheet.getSettings().setDefaultRowHeight(15);
			//设置Excel字体
			WritableFont wfont = new WritableFont(WritableFont.ARIAL, 9,  
					WritableFont.BOLD, false,  
					jxl.format.UnderlineStyle.NO_UNDERLINE,  
					jxl.format.Colour.BLACK);
			WritableCellFormat titleFormat = new WritableCellFormat(wfont);
			titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
			//设置Excel表头，如果标题为null则用列名代替标题  
			if(columnTitle==null){
				columnTitle = columnName;
			}
			//循环设置列名
			for (int i = 0; i < columnTitle.length; i++) {
				Label excelTitle = new Label(i, 0, columnTitle[i], titleFormat);  
				wsheet.addCell(excelTitle);  
			}
			//用于循环时Excel的行号
			int line = 1;
			String value;
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			for(Object b:list){
				for(int i=0;i<columnName.length;i++){
					Method method = bean.getMethod("get"+columnName[i]);
					//判断是否是日期属性
					Object o = method.invoke(b);
					if(o!=null){
						if(o instanceof Date){
							value = format.format(format.parse(o.toString()));
						}else{
							value = o.toString();
						}
					}else{
						value="";
					}
					//根据不同单元格样式要求可以定义多个WritableCellFormat进行设定
					Label content = new Label(i, line, value);  
					wsheet.addCell(content);  
				}
				line++;
			}
			wbook.write();   
			wbook.close();  
			os.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}