poi 导入excel (懒人拷贝代码用)（jdbctemplate）oms版本
相关内容：
1，jdbcTemplate 批量导入
2，js原始产生form 动态产生文件选择框
3，反射机制调用set方法
<div class="l-bar-separator"></div> <div class="group"><a class="link add" href="javascript:void(0);" id="importExcel"><span></span>导入excel</a></div>
<script type="text/javascript"> $(function() { //导入基础数据 $("#importExcel").click(function() { var url = __ctx+ '/ytdyjcompare/Ytdyjykzzb/ytdyjykzzb/importExcel.ht'; importExcel(url);//导入数据 }); }); /** * 产生导入对话框，并打开 * @param actionUrl 请求的Url */ function importExcel(actionUrl) { var f = document.createElement("form"); var i = document.createElement("input"); f.name = 'myFrom'; f.action = actionUrl; f.method = "post"; i.type = "file"; i.name = "xmlFile"; $(i).css("display", "none"); $(i).attr("onchange", "importData('" + actionUrl + "')"); //$(i).attr("accept", ".xls"); $(i).attr("accept", [ '.xls', '.xlsx' ]); $(f).attr("enctype", "multipart/form-data"); f.appendChild(i); document.body.appendChild(f); i.click(); } /** *提交表单 */ function importData(actionUrl) { //同步提交 //$("form[name='myFrom']").submit(); //异步提交 var nmiframe = document.forms['myFrom']; var formData = new FormData(nmiframe); $.ajax({ url : actionUrl, type : 'POST', data : formData, async : false, cache : false, contentType : false, processData : false, success : function(returndata) { //alert(1); alert(returndata); window.location.reload(true);//重新加载页面 }, error : function(returndata) {// alert(2); alert(returndata); } }); } </script>
/** * @param request * @param response * @throws Exception * 执行导入操作 */ @ResponseBody @RequestMapping("importExcel") public void importExcel(MultipartHttpServletRequest request, HttpServletResponse response) throws Exception { String rq = request.getParameter("rq"); System.out.println("can you see me"); // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); MultipartFile fileLoad = request.getFile("xmlFile"); ResultMessage resultMessage = null; String result = "导入数据成功"; try { String fileType = fileLoad.getOriginalFilename().substring(fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length()); Workbook wb = null; if (fileType.equals("xls")) { wb = new HSSFWorkbook(fileLoad.getInputStream()); } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(fileLoad.getInputStream()); } else { throw new Exception("读取的不是excel文件"); } this.importExcelHandle(request, wb); if (result.contains("成功")) { resultMessage = new ResultMessage(ResultMessage.Success, "导入成功!"); } else { resultMessage = new ResultMessage(ResultMessage.Fail, result); } writeResultMessage(response.getWriter(), resultMessage); } catch (Exception ex) { ex.printStackTrace(); resultMessage = new ResultMessage(ResultMessage.Fail, result); resultMessage.setMessage("导入失败--" + ex.getMessage()); response.getWriter().print(resultMessage); } }
/* * 具体导入操作 */ public void importExcelHandle(MultipartHttpServletRequest request, Workbook wb) throws Exception { SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // 读取了模板内所有sheet内容 HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0); HSSFCell cell = null; // 读取excel，保存与map中 // Map<String, String> excelMap = new HashMap<String, String>(); // 获取最大行 // int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); int physicalNumberOfRows = sheet.getLastRowNum(); Map<String, String> titleVarMap = this.getTitleVarMap(); Class<?> cls = Class.forName("com.ytd.ytdyjcompare.model.Ytdyjykzzb.Ytdyjykzzb"); Method[] ms = cls.getMethods(); List<Ytdyjykzzb> list = new ArrayList<Ytdyjykzzb>(); // 遍历每一行 for (int i = 0; i < physicalNumberOfRows; i++) { Object o = cls.newInstance(); // 遍历每一列 for (int j = 0; j < titleVarMap.size(); j++) { cell = sheet.getRow(i + 1).getCell(j); cell.setCellType(Cell.CELL_TYPE_STRING); String colVal = cell.getStringCellValue(); // 查找对应实体类属性 String key = titleVarMap.get("var" + j); if (StringUtil.isNotEmpty(key)) { // 遍历类的所有方法名 for (int k = 0; k < ms.length; k++) { // excel标题与类属性相同的时候 String setType = ms[k].getReturnType().getName(); Method method = null; // 获取get方法 String tmp = "get" + firstLetterName(key); if (tmp.equals(ms[k].getName()) && StringUtil.isNotEmpty(colVal)) { if ("java.lang.boolean".equals(setType)) { method = cls.getMethod("set" + firstLetterName(key), Boolean.class); method.invoke(o, colVal); } else if ("java.lang.String".equals(setType)) { method = cls.getMethod("set" + firstLetterName(key), String.class); method.invoke(o, colVal); } else if ("java.lang.Long".equals(setType)) { method = cls.getMethod("set" + firstLetterName(key), Long.class); method.invoke(o, Long.parseLong(colVal)); } else if ("int".equals(setType)) { method = cls.getMethod("set" + firstLetterName(key), Integer.class); method.invoke(o, Integer.parseInt(colVal)); } else if ("java.util.Date".equals(setType)) { method = cls.getMethod("set" + firstLetterName(key), Date.class); method.invoke(o, sdf.parse(colVal)); } else if ("java.lang.Double".equals(setType)) { method = cls.getMethod("set" + firstLetterName(key), Double.class); method.invoke(o, Double.parseDouble(colVal)); } else if ("java.lang.Float".equals(setType)) { method = cls.getMethod("set" + firstLetterName(key), Float.class); method.invoke(o, Float.parseFloat(colVal)); } } } } } Ytdyjykzzb e = (Ytdyjykzzb) o; Long id = UniqueIdUtil.genId(); e.setId(id); SysUser sysUser = ContextUtil.getCurrentUser(); e.setDrr(sysUser.getFullname()); e.setDrsj(new Date()); e.setSfydb("否"); list.add(e); } this.insertData(list); }
public void insertData(List<Ytdyjykzzb> list) { String sql = "INSERT INTO W_YTDYJYKZZB (ID,F_SJDM,F_CZM,F_SJDH,F_DRR,F_DRSJ,F_SFYDB) values (?, ?, ?, ?,?,to_date(?,'yyyy-MM-dd HH24:mi:ss'),?)"; final List<Ytdyjykzzb> list2 = list; jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() { public int getBatchSize() { return list2.size(); } public void setValues(PreparedStatement ps, int index) throws SQLException { SimpleDateFormat sdfTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); int j = 1; Ytdyjykzzb e = list2.get(index); System.out.print(e); ps.setObject(j++, e.getId()); ps.setObject(j++, e.getSjdm()); ps.setObject(j++, e.getCzm()); ps.setObject(j++, e.getSjdh()); ps.setObject(j++, e.getDrr()); ps.setObject(j++,e.getDrsj()==null?null:sdfTime.format(e.getDrsj())); ps.setObject(j++, e.getSfydb()); } }); } /** * @return list添加的顺序就是Excel标题的顺序 */ public Map<String, String> getTitleVar() { List<String> title = new ArrayList<String>(); title.add("数据点名"); title.add("厂站名"); title.add("数据点号"); // 有序的保存与map中 Map<String, String> titleMap = new HashMap<String, String>(); for (int i = 0; i < title.size(); i++) { titleMap.put("var" + i, title.get(i)); } return titleMap; } /** * @return list添加的顺序就是Excel标题的顺序 */ public Map<String, String> getTitleVarMap() { List<String> title = new ArrayList<String>(); title.add("sjdm"); title.add("czm"); title.add("sjdh"); // 有序的保存与map中 Map<String, String> titleVarMap = new HashMap<String, String>(); for (int i = 0; i < title.size(); i++) { titleVarMap.put("var" + i, title.get(i)); } return titleVarMap; } /** * @param name * @return * String //首字母大写 * */ public static String firstLetterName(String name) { name = name.substring(0, 1).toUpperCase() + name.substring(1); return name; }
