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>

点击按钮后的js代码

/**
     * @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);
        }
    }

请求的后台方法1

/*
     * 具体导入操作
     */
    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);
    }

请求的后台方法2

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

其他关联方法