json-jquery-struct 解析例子(map) - 批量导入解析和批量导出
1、点击批量导入，先做个弹出层 弹出层里面选择文件和返回信息 确定导入 返回信息，追加到div里面展示
$.getJSON(encodeURI('xxxAction!toImportExcel.action?filePath='+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 );//遍历 mapIterator 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.toString()); 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" method="toExportExcel"></action><action name="toImportExcel" method="toImportExcel"><result name="success" type="json"/></action>
解析xml组件，把错误的放到 错误集合，正确的放到正确集合
/** * ClassName:ImportExcelUtil * Project: * Company: * * @author 程仁银 * @version * @since Ver 1.1 * @Date 2011Apr 20, 201110: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 2011Apr 20, 20112: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/>");}}}}); }); }); }); 1 楼 snowday88 2011-07-01 王国波：这样做导入是有问题的！如果项目部署在服务器 在本地机导入的话，它的file控件获取path是获取不到的！所以应该先把文件上传然后再解析。