json-jquery-struct 解析例子(map) - 批量导入解析和批量导出

1、点击批量导入，先做个弹出层
   弹出层里面选择文件和返回信息
   确定导入
   返回信息，追加到div里面展示

$.getJSON(encodeURI('xxxAction!toImportExcel.action?filePat
h='+filePath),function(result){
	$.each(result.ERROR_LIST,function(key,value){
		$("#message").append("<font 
	color='red'>"+key+":"+value.errorInfor+"</font><br/>");
	});
});

ACTION - service 主要调用组件类，封装了map集合,列出错误格式集合或正确导入集合

private JSONObject jsonObject = new JSONObject();
//get-set method JSONObject 
// 获取 excel path路径
String filePath = new String(ServletActionContext.getRequest().getParameter("filePath").getBytes("iso8859-1"),"utf-8");
// 获取 map 集合
Map<String,List<ExcelBean>> map = 
	this.customerService.toImportExcel(filePath );
//遍历 map
Iterator it = map.entrySet().iterator();
	List<ExcelBean> errorList = null;
	List<ExcelBean> successList = null;
	while(it.hasNext()){
		Map.Entry mapEntry = (Map.Entry)it.next();
		if(mapEntry.getKey().equals("fail")){
			errorList = (List<ExcelBean>)mapEntry.getValue();
		}else if(mapEntry.getKey().equals("success")){
			successList = (List<ExcelBean>)mapEntry.getValue();
		}
	}
// 返回错误集合 或 操作 批量插入
if(errorList.size() != 0){
    		System.out.println("有错误excel格式,不执行批量操作,返回错误列表");
    		ServletActionContext.getResponse().setCharacterEncoding("UTF-8");
    		JSONArray jsonArray = JSONArray.fromObject(errorList);
    		jsonObject.put("ERROR_LIST", jsonArray);
    		ServletActionContext.getResponse().getWriter().write(jsonObject.toSt
			ring());
    		System.out.println("json :" + jsonObject.toString());
    		return null;
    	}else{
//    		调用dao层执行批量插入操作
    		System.out.println("调用dao层执行批量插入操作");
//    		封装解析成功的数据格式返回
//    		dosomething ...
    		return null;
    	}

xml配置

xml 配置层
<!-- cheng ren yin (structs-jquery-json) -->
<result-types>
	<result-type name="json" 
	class="com.opensymphony.webwork.dispatcher.json.JSONResult"></result-type>
</result-types>
<!-- cry -->
<action name="toExportExcel" class="xxxAction" 
method="toExportExcel"></action>
<action name="toImportExcel" class="xxxxAction" method="toImportExcel">
	<result name="success" type="json"/>
</action>

解析xml组件，把错误的放到 错误集合，正确的放到正确集合

/**
 * ClassName:ImportExcelUtil
 * Project: 
 * Company: 
 *
 * @author   程仁银
 * @version  
 * @since    Ver 1.1
 * @Date	 2011	Apr 20, 2011		10:41:21 AM
 * @see
 */
public class ImportExcelUtil
{
    private HSSFWorkbook book = null;
    
    private HSSFSheet sheet = null;
    
    private HSSFRow row = null;// 行对象
    
    public Map<String,List<ExcelBean>> readTemplateFile(String path)
    {
    	HSSFWorkbook work=null;
    	Map<String,List<ExcelBean>> map = new HashMap<String, 
		List<ExcelBean>>();
        List<ExcelBean> listFail = new ArrayList<ExcelBean>();
        List<ExcelBean> listSuccess = new ArrayList<ExcelBean>();;
        try
        {
            FileInputStream file = new FileInputStream(path);
            work=new HSSFWorkbook(file);
            HSSFSheet sheet=work.getSheetAt(0);
    		HSSFRow row=null;//行对像;
    		HSSFCell cell=null;//列对像;
    		
    		int rowNum ;
    		rowNum=sheet.getLastRowNum();//得到总行数
    		System.out.println("总行数为"+rowNum);
    		for(int i=1;i<=rowNum;i++)
    		{
    			ExcelBean errorBean = new ExcelBean();
    			ExcelBean successBean = new ExcelBean();
    			
    			System.out.println("第 "+i+" 行");
    			row=sheet.getRow(i);
    			
    			//获取名称
    			cell=row.getCell((short)0);
    			String name=this.chickCell(cell);
    			System.out.println("名称 = "+ name);
    			if(("".equals(name) || (null == name))){
    				errorBean.setErrorInfor("第 "+i+" 行 名称"+name+" 格式错误
					!");
    			}else{
    				successBean.setName(name);
    			}
    			//获取客户名称
    			cell=row.getCell((short)1);
    			String nameCn=this.chickCell(cell);
    			System.out.println("客户名称 = "+ nameCn);
    			if(("".equals(nameCn) || (null == nameCn))){
    				errorBean.setErrorInfor("第 "+i+" 行 客户名称"+nameCn+" 格式
					错误!");
    			}else{
    				successBean.setNameCn(nameCn);
    			}
    			//获取邮箱
    			cell=row.getCell((short)2);
    			String email=this.chickCell(cell);
    			System.out.println("邮箱 = "+ email);
    			String check = 
				"^([a-z0-9A-Z]+[-|\\.]?)+[a-z0-9A-Z]@([a-z0-9A-Z]+(-[a-z0-9A-Z]+
				)?\\.)+[a-zA-Z]{2,}$";
    			Pattern regex = Pattern.compile(check);
    			Matcher matcher = regex.matcher(email);
    			boolean isMatched = matcher.matches();
    			if(isMatched){
    				successBean.setEmail(email);
    			}else{
    				errorBean.setErrorInfor("第 "+i+" 行 邮箱"+email+" 格式错误
					!");
    			}
    			//获取电话
    			cell=row.getCell((short)3);
    			String tel=this.chickCell(cell);
    			System.out.println("电话 = "+ tel);
    			String checkTel = 
				"^(13[4,5,6,7,8,9]|15[0,3,8,9,1,7]|188|187)\\d{8}$";
    			Pattern regexTel = Pattern.compile(checkTel);
    			Matcher matcherTel = regexTel.matcher(tel);
    			boolean isMatchedTel = matcherTel.matches();
    			if(isMatchedTel){
    				successBean.setTel(tel);
    			}else{
    				errorBean.setErrorInfor("第 "+i+" 行 电话"+tel+" 格式错误
					!");
    			}
    			//客户主管
    			int responsible = 0 ;
    			cell=row.getCell((short)4);
    			String khzg=this.chickCell(cell);
    			if(("".equals(khzg) || (null == khzg))){
    				errorBean.setErrorInfor("第 "+i+" 行 客户主管"+khzg+" 格式错
					误!");
    			}else{
    				successBean.setFlag(khzg=="是"?"0":"1");
    			}
    			System.out.println("客户主管 = "+ responsible);
    			
    			if(successBean.getEmail() != null && successBean.getFlag() != 
				null &&
    					successBean.getName() != null && successBean.getNameCn() 
						!= null 
    					&& successBean.getTel() != null){
    				listSuccess.add(successBean);
    			}else if(successBean.getEmail() != null || successBean.getFlag() 
				!= null ||
    					successBean.getName() != null || successBean.getNameCn() 
						!= null 
    					|| successBean.getTel() != null){
    				listFail.add(errorBean);
    			}
    			System.out.println("----------------------------------");
    		}
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        /*System.out.println("正确的 " + listSuccess.size());
        for(ExcelBean eb:listFail){
        	System.out.println(eb.getEmail()+" "+ eb.getFlag() +" "+eb.getName() 
			+" "+eb.getNameCn() +" "+eb.getTel() );
        }
        System.out.println("错误的 " + listFail.size());
        for(ExcelBean ebc:listSuccess){
        	System.out.println(ebc.getEmail()+" "+ ebc.getFlag() +" 
			"+ebc.getName() +" "+ebc.getNameCn() +" "+ebc.getTel() );
        }*/
        map.put("success",listSuccess);
        map.put("fail",listFail);
        return map;
    }
    
    private  String chickCell(HSSFCell cell)
	{
//		判断是否为String型
		String all="";
		if(cell.getCellType()==cell.CELL_TYPE_STRING){
				all=cell.getStringCellValue();
				//all=all.replaceAll(",","").trim();
				all.trim();
		}else{
			//判断是否为数值型				
			if(cell.getCellType()==cell.CELL_TYPE_NUMERIC){
				//判断是否为日期型
				if(HSSFDateUtil.isCellDateFormatted(cell))
				{
					Date date=cell.getDateCellValue();
					SimpleDateFormat formatter = new 
					SimpleDateFormat("yyyy-MM-dd"); 
					all=formatter.format(date);
					
				}else{//一般数值型
					double d=cell.getNumericCellValue();
					all=Double.toString(d);
					}	
				}
			}
		return all;
	}
}

POJO

public class ExcelBean implements Serializable{
	private String name ;
	private String nameCn ;
	private String email ;
	private String tel ;
	private String flag ;
	private String errorInfor ;

批量导出 代码 - 根据 sql 语句导出EXCEL

/**
 * ClassName:ExportExcelUtil
 * Project: 
 * Company: xxxxxxxx 人才库 ....
 *
 * @author   程仁银
 * @version  
 * @since    Ver 1.1
 * @Date	 2011	Apr 20, 2011		2:21:45 AM
 * @see
 */
public class ExportExcelUtil {
	private static String shtName = "";
	private static String[] cNames = null;
	private static String[] cLabels = null;
	private static int rpp = 200;
	private static HSSFCellStyle style = null;

	/**
	 * 通过给定的Sql导出Excel文件到Response输出流，需要指定Connection 
	 * 
	 * @param response
	 *            HttpServletResponse Response 
	 * @param conn
	 *            Connection 指定的数据库连接
	 * @param sqlStr
	 *            String 查询的Sql语句
	 * @param sheetName
	 *            String 导出的Excel Sheet名称 
	 * @param columnNames
	 *            String[] 导出的 Excel 列名称
	 * @param rowPerPage
	 *            int 每一个Sheet页的行数 
	 * @throws SQLException
	 *             48.
	 */
	public static void export(Connection conn,
			String sqlStr, String sheetName, String columnNames[],
			int rowPerPage,HttpServletResponse response) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = conn.prepareStatement(sqlStr);
		rs = ps.executeQuery();

		ResultSetMetaData rsmd = rs.getMetaData();
		if (rowPerPage <= 10000 && rowPerPage >= 1) {
			rpp = rowPerPage;
		}
		if (!"".equals(sheetName) && null != sheetName) {
			shtName = sheetName;
		} else {
			shtName = rsmd.getTableName(0);
		}
		cNames = getColumnNames(rsmd);
		if (null != columnNames) {
			cLabels = columnNames; // compare( columnNames ) ;
		} else {
			cLabels = cNames;
		}

		HSSFWorkbook wb = new HSSFWorkbook();
		style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		HSSFSheet sheet = createSheet(wb, 1);
		setSheetColumnTitle(sheet);
		int rowCnt = 0;
		int sheetNum = 2;

		while (rs.next()) {
			if (rowCnt == rpp) {
				sheet = createSheet(wb, sheetNum);
				setSheetColumnTitle(sheet);
				rowCnt = 0;
				sheetNum++;
			}
			HSSFRow row = sheet.createRow(rowCnt + 1);
			for (int i = 0; i < cNames.length; i++) {

				HSSFCell cell = row.createCell((short) i);
//				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				String val = rs.getString(cNames[i]);
				if (null == val) {
					val = "";
				}
				cell.setCellValue(val.toUpperCase());
			}
			rowCnt++;
		}
		try {
			OutputStream os = response.getOutputStream();
			response.reset();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment; 
			filename="+getFileName(shtName));
			wb.write(os);
//			FileOutputStream fos = new FileOutputStream(new 
File("d:\\","saveName.xls"));
//			fos.close();
//			wb.write(fos);
			if (conn != null) {
				conn.close();
			}
		} catch (IOException ex) {
			MyLogger.logserverLogger.info("Export Excel file error ! " + 
			ex.getMessage());
		}
	}

	/**
	 *设置Sheet页的列属性
	 * @param sht
	 * HSSFSheet 124.
	 */
	private static void setSheetColumnTitle(HSSFSheet sht) {
		HSSFRow row = sht.createRow(0);
		for (int i = 0; i < cLabels.length; i++) {
			HSSFCell cell = row.createCell((short) (i));
//			cell.setEncoding(HSSFCell.ENCODING_COMPRESSED_UNICODE);
			cell.setCellValue(cLabels[i]);
			cell.setCellStyle(style);
		}
	}

	/**
	 *  获得源数据中的列名称
	 * @param rsmd
	 * ResultSetMetaData
	 * @return String[] 139.
	 */
	private static String[] getColumnNames(ResultSetMetaData rsmd) {
		try {
			StringBuffer result = new StringBuffer("");
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				result.append(rsmd.getColumnLabel(i)).append(",");
			}
			if (result.length() > 0) {
				return result.substring(0, result.length() - 
				1).toString().split(",");
			}
		} catch (Exception e) {
			return null;
		}
		return null;
	}

	/**
	 *创建一个Sheet页并返回该对象 
	 * @param wb
	 * HSSFWorkbook 
	 * @param seq int 
	 * @return HSSFSheet 
	 */
	private static HSSFSheet createSheet(HSSFWorkbook wb, int seq) {
		int sup = seq * rpp;
		int sub = (seq - 1) * rpp + 1;
		if (sub < 1) {
			sub = 1;
		}
		return wb.createSheet(shtName + "(" + sub + "-" + sup + ")");
	}
	
	private static String getFileName(String tableName) {
		return tableName + new java.util.Date().getTime() + ".xls";
	}
}

========================dao
 @Override
    public void toExportExcel(HttpServletResponse response) {
    	System.out.println("************ - ExportExcelUtil ....");
    	ExportExcelUtil eeu = new ExportExcelUtil();
    	 String[] columnNames = {"名称","邮箱","电话 "};
    	 String QUERY_EXP_SQL = " 自定义，要把这上面几个列查询出来 ";
    	 try {
				eeu.export(this.getSession().connection(), QUERY_EXP_SQL, 
				"excelName_", columnNames,100000,response);
			} catch (SQLException e) {
				e.printStackTrace();
			}
    }

//后期修改
$(function(){
    		$('#sub').click(function()
    		{
    			var filePath = $('#fileId').val();
    			var theform = $('#fileId').val();
    			if(theform==''){
    				alert('请选择excel文件！');
					return   false;
    			}
				if(theform.match(/^(.*)(\.)(.{1,8})$/)[3] != 'xls')   
				{
					alert('文件格式不对，请重新选择！');   
					return   false;   
				}
				// import excel
	    		$.getJSON(encodeURI('customToImportExcel!toImportExcel.action?filePath='+filePath),function(result){
					$.each(result.ERROR_LIST,function(key,value){
						if(value.state=='0'){
							if(key==0){
								//$("#success").append("<font color='red'>错误"+(value.totalRow-result.ERROR_LIST.length)+"行"+"</font><br/>");
								for(var i=0;i<result.ERROR_LIST.length;i++){
									var obj = {};
									for(var j in result.ERROR_LIST[i])   
				                    {
				                        if(j == 'errorInfor')   
				                        {
				                            obj.errorInfor = result.ERROR_LIST[i][j];   
				                        }
				                    }
				                    $("#success").append("<font color='red'>"+obj.errorInfor+"</font><br/>");
								}
							}
							
						}else
						{	
							if(key==0){
							$("#success").append("<font color='red'>成功导入"+(value.totalRow-result.ERROR_LIST.length)+"行"+"</font><br/>");
								for(var i=0;i<result.ERROR_LIST.length;i++){
									var obj = {};
									for(var j in result.ERROR_LIST[i])   
				                    {
				                        if(j == 'errorInfor')   
				                        {
				                            obj.errorInfor = result.ERROR_LIST[i][j];   
				                        }
				                    }
				                   $("#success").append("<font color='red'>"+obj.errorInfor+"</font><br/>");
								}
						}
						}
					});
	     	 	});
    		});
    	});

评论

2 楼 snowday88 2011-07-01  

还有这样在页面迭代也是有问题的！太麻烦！可以直接把集合传过来！

1 楼 snowday88 2011-07-01  

王国波：这样做导入是有问题的！如果项目部署在服务器 在本地机导入的话，它的file控件获取path是获取不到的！所以应该先把文件上传然后再解析。
上传有两种方式 异步和同步  异步使用ajaxfileupload.js插件实现ajax文件上传。 同步的话 可以使用struts2文件上传