jsp利用pol通过servlet来实现Excel导入数据库

第一步需要的架包：

第二步，选择要导入的文件并上传到指定位置，需要用到问文件上传的包jspsmartupload.jar，
html代码略；
上传文件的代码（在servlet中）：

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        response.setCharacterEncoding("utf-8");
        String type = request.getParameter("type");
        /*
        *进行文件的上传：
        */
        if (type.equals("excToMqsql")) {
            delStaffInfo();
            SmartUpload su = new SmartUpload();
            su.initialize(this.getServletConfig(), request, response);
            // 设定允许上传的文件（通过扩展名限制）
            su.setAllowedFilesList("xls,xlsx");
            try {
                su.upload();
                Files files = su.getFiles();
                String temp = "";
                String str = "";
                for (int i = 0; i < files.getCount(); i++) {
                    File file = files.getFile(i);
                    /*
                     * 通过获取文件的后缀来设定不同的文件类型 
                     * str = file.getFileExt();
                     * System.out.println(str); if ("xls".equals(str)) {
                     * 
                     * temp = "/upload/staff.xls"; } else { temp =
                     * "/upload/staff.xls"; }
                     */
                    temp = "/upload/sub_staff.xls";
                    file.saveAs(temp, SmartUpload.SAVE_VIRTUAL);
                }
                System.out.println("上传成功!");
                **//调用读取文件的方法;**
                InSub es = new InSub();
                es.getDate(request, response);
            } catch (SmartUploadException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

第三步:对上传的文件进行读取:
次方法无论你上传的Excel文件的格式是xls还是xlsx,都可以读取到其中的信息

    private void getDate(HttpServletRequest request,
            HttpServletResponse response) {
        // TODO Auto-generated method stub
        String errMessage = null;
        boolean flags = false;
        staffBean staff = new staffBean();
        try {
            java.io.File f = new java.io.File(request.getSession()
                    .getServletContext().getRealPath("")
                    + "\\upload\\sub_staff.xls");
            //
            InputStream is = new FileInputStream(f);
            Workbook wb = WorkbookFactory.create(is);

            for (int i = 0, len = wb.getNumberOfSheets(); i < len; i++) {
                Sheet sheet = wb.getSheetAt(i);
                for (int j = 1; j <= sheet.getLastRowNum(); j++) {// 令i=1表示除去标题一行
                    if (sheet == null) {
                        return;
                    }
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        return;
                    }
                    // 读取每一个单元格
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        Cell cell = row.getCell(k);
                        if (cell == null) {
                            return;
                        }
                        if (k == 0)//
                        {
                            String str = getValue(cell);
                            staff.setName(str);
                            //System.out.println(str);
                            continue;
                        }
                        if (k == 1) {
                            // staff.setIdcard(getValue(cell));
                            String str = getValue(cell);
                            staff.setIdcard(str);
                            //System.out.println(str);
                            continue;
                        }
                    }
                    // System.out.println("-------------");
                    // 导入数据
                    flags = getStaffInfo(staff);
                }
                System.out.println(flags);
                //此处为导入成功的一个跳转
                if (flags) {
                    System.out.println("Excel表导入成功!");
                    response.sendRedirect("CompareServlet?type=showsub");

                } else {
                    System.out.println("Excel表未导入成功!");
                    errMessage = "导入Excel表未成功,请重新导入！";
                    request.setAttribute("error", errMessage);
                    request.getRequestDispatcher("../tips.jsp").forward(
                            request, response);
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

//**获取单元格值得方法**
    public void init() throws ServletException {
        // Put your code here
    }

    private String getValue(Cell cell) {
        String result = "";

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            result = cell.getBooleanCellValue() + "";
            break;
        case Cell.CELL_TYPE_STRING:
            result = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            result = cell.getCellFormula();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            // 可能是普通数字，也可能是日期
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                result = DateUtil.getJavaDate(cell.getNumericCellValue())
                        .toString();
            } else {
                result = cell.getNumericCellValue() + "";
            }
            break;
        }
        return result;
    }

第四步,sql语句的添加元素的方法:

    public boolean getStaffInfo(staffBean staff) {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement pt = null;
        ResultSet rs = null;
        String sql = "insert into sub_staff(name,idcard) values (?,?)";
        jdbcDtiver jdbc = new jdbcDtiver();//创建数据库的连接
        conn = jdbc.Driver();
        try {
            pt = conn.prepareStatement(sql);
            pt.setString(1, staff.getName());
            pt.setString(2, staff.getIdcard());
            int n = pt.executeUpdate();
            if (n > 0) {
                flag = true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return flag;
    }

以上就是jsp利用架包实现Excel数据导入到MySQL数据中的步骤及代码,希望对大家有所帮助