Springmvc+POI导入Excel表到数据库

Springmvc+POI导入Excel表到数据库， 第一次使用POI和上传文件，中间遇到了很多坎坷，不过最后还是完成了，记录一下。

先直接上代码吧，后续的问题我等会在讲。

首先需要有jar包,我没有用maven，百度都有包。




在使用POI时，因为现在是4.1版本的，我使用最新版的时候，字节符的输入流出了错，所有后来我又换了
POI3.10版的，就成功了!
JSP页面标签与JS调用

 				<input type="file"  name="file" id="excel_file" accept="xlsx" >
                  <input type="button"  onclick="check()"  id="excel_button"value="导入">

JS代码

<script>
        function check() {
            var excel_file=$("#excel_file").val();
            if(excel_file==""||excel_file.length==0){
                layer.msg("请选择文件路径！",{icon:3});
            return false;
            }else if(excel_file.lastIndexOf('.xls')==-1||excel_file.lastIndexOf('.xlsx')==-1){
                layer.msg("附件格式不符,请上传Excel表格",{icon:7});
                return false;
            }else{
                upload();
                parent.layer.closeAll();
                return true;
            }
        }
         function upload() {
            var formData= new FormData($("#upload")[0]);
            $.ajax({
                url:"/addPUser.do"
               ,type:"post"
               ,data: formData
                ,async:false
                ,cache:false
                ,contentType:false
                ,processData: false
                ,beforeSend : function() {
                    console.log("正在进行，请稍候");
                }
                ,success : function(e) {
                    if (e == "01") {
                        alert("导入成功");
                    } else {
                        alert("导入失败，请按照模板填值");
                    }
                }
            });
        }
    </script>

Controller层

 @RequestMapping("/addPUser")  //Excel批量添加用户
    @ResponseBody
    public  String addPUser(@RequestParam MultipartFile file) throws IOException {
        String flag = "02";// 上传标志
        if (!file.isEmpty()) {
            try {
                String originalFilename = file.getOriginalFilename();// 原文件名字
                System.out.println("文件名："+originalFilename);
                InputStream in=file.getInputStream();
                User user=new User();
               // flag=re.readExcel1(in,originalFilename);
                List<ArrayList<Object>> list=null;
                if (originalFilename.endsWith(".xls")) {
                    list = Excel.readExcel2003(in);
                } else {
                    list = Excel.readExcel2007(in);
                }
                for (int i=0,j=list.size();i<j;i++){
                    List<Object> row = list.get(i);
                    user.setuUsername(row.get(0).toString());
                    user.setuPassword(row.get(1).toString());
                    user.setuPhone(row.get(2).toString());
                    user.setuSex(row.get(3).toString());
                    user.setuOrganizationid(Integer.parseInt(row.get(4).toString()));
                    user.setuLocal(row.get(5).toString());
                    int a=ump.insertSelective(user);
                    if(a>0){
                      flag="01";
                    }
                }


            } catch (Exception e) {
                flag = "03";// 上传出错
                e.printStackTrace();
            }
    }
        return flag;
    }

至此，如果插入数据成功返回flag到前端JSP页面判断.

Sevice业务层
两个工具类，兼容两个版本xslx2007+，和2003的.xsl格式

 public static ArrayList<ArrayList<Object>> readExcel2007(InputStream is) {
        try {
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> colList;
            XSSFWorkbook wb = new XSSFWorkbook(is);
            System.out.println("获取到了字符流");
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            Object value = null;

            for (int i = sheet.getFirstRowNum() + 1, rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null) {
                    if (i != sheet.getPhysicalNumberOfRows()) {
                        rowList.add(colList);
                    }
                    return rowList;
                } else {
                    rowCount++;
                }
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        if (j != row.getLastCellNum()) {
                            colList.add("");
                        }
                        continue;
                    }

                    if (null != cell) {
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                                    value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                                    break;
                                } else {
                                    Double d = cell.getNumericCellValue();
                                    DecimalFormat df = new DecimalFormat("#.##");
                                    value = df.format(d);
                                }
                                break;

                            case HSSFCell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;

                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                value = cell.getBooleanCellValue() + "";
                                break;

                            case HSSFCell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula() + "";
                                break;

                            case HSSFCell.CELL_TYPE_BLANK:
                                value = "";
                                break;

                            case HSSFCell.CELL_TYPE_ERROR:
                                value = "非法字符";
                                break;

                            default:
                                value = "未知类型";
                                break;
                        }

                    }
                    colList.add(value);
                }
                rowList.add(colList);
            }
            if (is != null) {
                is.close();
            }
            return rowList;
        } catch (Exception e) {
            System.out.println("exception");
            return null;
        }

    }


    public static ArrayList<ArrayList<Object>> readExcel2003(InputStream is) {
        try {
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> colList;
            HSSFWorkbook wb = new HSSFWorkbook(is);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;
            HSSFCell cell;
            Object value = null;
            for (int i = sheet.getFirstRowNum() + 1, rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null) {
                    if (i != sheet.getPhysicalNumberOfRows()) {// 判断是否是最后一行
                        rowList.add(colList);
                    }
                    return rowList;
                } else {
                    rowCount++;
                }
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        if (j != row.getLastCellNum()) {
                            colList.add("");
                        }
                        continue;
                    }
                    if (null != cell) {
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                                    value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                                    break;
                                } else {
                                    Double d = cell.getNumericCellValue();
                                    DecimalFormat df = new DecimalFormat("#.##");
                                    value = df.format(d);
                                }
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                value = cell.getBooleanCellValue() + "";
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula() + "";
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                value = "";
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                value = "非法字符";
                                break;
                            default:
                                value = "未知类型";
                                break;
                        }

                    }
                    colList.add(value);
                }
                rowList.add(colList);
            }
            if (is != null) {
                is.close();
            }
            return rowList;
        } catch (Exception e) {
            return null;
        }
    }

配置Spingmvc的上传文件配置

    <bean id="multipartResolver"
          class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="defaultEncoding" value="utf-8"></property>
    <property name="maxUploadSize" value="20000000"></property> <!-- 最大上传文件大小 20000kb，注意是多个文件的和-->
    <property name="maxInMemorySize" value="10960"></property>
    </bean>

代码差不多就结束了.