把EXCEL用程序导入到ORACLE中(SpringMVC+MyBatis)

- 日理万妓 2022-08-07 15:52 94阅读 0赞

# [把EXCEL用程序导入到ORACLE中(SpringMVC+MyBatis)        ][EXCEL_ORACLE_SpringMVC_MyBatis_] #

分类：            [ spring][spring] 2014-11-13 20:38 409人阅读 [评论][Link 1](0) 收藏 [举报][Link 2]

**\[java\]** [ view plain][view plain] [copy][view plain] [print][view plain] [?][view plain]

**前提：项目中需要把EXCEL数据批量导入oracle中两张表中。如是用到了poi技术。分别导入poi-3.11-beta2.jar和poi-ooxml-schemas-3.9.jar这两个包。EXCEL数据如下![Center][]**

**第一步：修改spring框架配置文件。 springmvc-servlet.xml加上：**

<bean id="multipartResolver"   class="org.springframework.web.multipart.commons.CommonsMultipartResolver"  p:defaultEncoding="utf-8" />

**第一步：添加页面jsp。view\_user\_batchadd.jsp**

< %@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>  
< %@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>     
< !DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
< html>  
< %   
String importMsg="";   
if(request.getSession().getAttribute("msg")!=null)\{   
importMsg=request.getSession().getAttribute("msg").toString();   
\}   
request.getSession().setAttribute("msg", "");   
%>  
< head>  
    <title>批量导入用户</title>  
< meta http-equiv="Content-Type" content="text/html; charset=UTF-8">  
   <script type="text/javascript" src="<%=request.getContextPath()%>/view/js/jquery.js"></script>  
< body>  
    <form action="<%=request.getContextPath()%>/manager/index/batchimport" method="post" enctype="multipart/form-data" name="batchAdd" οnsubmit="return check();">  
        <div style="margin: 30px;"><input id="excel\_file" type="file" name="filename" accept="xls" size="50"/>  
        <div><input id="excel\_file" type="file" name="filename"  size="50"/>  
        <input id="excel\_button" type="submit" value="导入Excel"/></div>  
        <font id="importMsg" color="red"><%=importMsg%></font><input type="hidden"/>   
    </form>  
< /body>  
< script type="text/javascript">  
    function check() \{   
     var excel\_file = $("\#excel\_file").val();   
     if (excel\_file == "" || excel\_file.length == 0) \{   
         alert("请选择文件路径！");   
         return false;   
     \} else \{   
        return true;   
    \}   
\}  
     
    $(document).ready(function () \{   
          var msg="";   
          if($("\#importMsg").text()!=null)\{   
                 
              msg=$("\#importMsg").text();   
          \}   
          if(msg!="")\{   
              alert(msg);   
          \}   
        \});   
< /script>  
< /html>

**第三步：填写控制器：UserLoginController.java**  
  
/\*\*  
\* 2014-8-30 下午2:52:49  
\* TODO  用户登录 Controller  
\*  
\*/  
@Controller  
@RequestMapping("/index")  
public class UserLoginController  \{  
    private Log log = LogFactory.getLog(UserLoginController.class);  
     
    @Autowired  
    private UserLoginService userLoginService;  
     
    @Autowired  
    private UserInfoService userInfoService;  
     
    @RequestMapping(value = "/batchimport", method = RequestMethod.POST)  
    public ModelAndView batchimport(@RequestParam("filename") MultipartFile file,HttpServletRequest request,HttpServletResponse response) throws Exception\{  
        log.info("UserLoginController ..batchimport() start");  
        //判断文件名是否为空  
        if(file==null) return null;  
         
        //获取文件名  
        String name=file.getOriginalFilename();  
         
        //判断文件大小、即名称  
        long size=file.getSize();  
        if(name==null || ("").equals(name) && size==0) return null;  
         
        try \{  
            //把文件转换成字节流形式  
            InputStream in = file.getInputStream();  
            int i=userLoginService.batchImport(name,file);  
            int j=userInfoService.batchImport(name,file);  
            if(i>0 && j>0)\{  
                 String Msg ="批量导入EXCEL成功！";  
                 request.getSession().setAttribute("msg",Msg);     
            \}else\{  
                 String Msg ="批量导入EXCEL失败！";  
                 request.getSession().setAttribute("msg",Msg);  
            \}  
        \} catch (IOException e) \{  
            e.printStackTrace();  
        \}  
        return null;  
    \}  
     
\}

注：我这个Controller的方法里面处理了两个接口实现类。我这里就写一个

**第三步：实现类：UserLoginServiceImpl.java**

public int batchImport(String name,MultipartFile file) throws Exception \{

//处理EXCEL  
       ReadExcel readExcel=new ReadExcel();  
        //获得解析excel方法  
        List<User> userList=readExcel.getExcelInfo(name,file);  
         
        //把excel信息添加到数据库中  
        List<UserLogin> LoginList=new ArrayList<UserLogin>();  
         
        for(User user:userList)\{  
            LoginList.add(user.getUserLogin());  
        \}  
        return userLoginDao.saveBatch(LoginList);//添加登录信息  
    \}

**第四步：处理EXCEL类：ReadExcel.java**

public class ReadExcel \{  
     
      //总行数  
      private int totalRows = 0;   
      
      //总条数  
      private int totalCells = 0;  
       
      //错误信息接收器  
      private String errorMsg;  
             
      //构造方法  
      public ReadExcel()\{\}  
       
      //得到总行数  
      public int getTotalRows()  \{ return totalRows;\}  
       
      //得到总列数  
      public int getTotalCells() \{  return totalCells;\}  
       
      public String getErrorInfo() \{ return errorMsg; \}   
       
    /\*\*  
     \* 描述：验证EXCEL文件  
     \* @param filePath  
     \* @return  
     \*/  
    public boolean validateExcel(String filePath)\{  
          if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath)))\{   
              errorMsg = "文件名不是excel格式";   
              return false;   
          \}   
          return true;  
    \}  
       
     
    /\*\*描述 :读EXCEL文件  
     \* @param fielName  
     \* @return  
     \*/  
    public List<User> getExcelInfo(String fileName,MultipartFile Mfile)\{  
         
        //把spring文件上传的MultipartFile转换成File  
         CommonsMultipartFile cf= (CommonsMultipartFile)Mfile;  
         DiskFileItem fi = (DiskFileItem)cf.getFileItem();  
         File file = fi.getStoreLocation();  
          
        List<User> userList=new ArrayList<User>();  
        InputStream is = null;   
        try\{  
            //验证文件名是否合格  
            if(!validateExcel(fileName))\{  
                return null;  
            \}  
            //判断文件时2003版本还是2007版本  
            boolean isExcel2003 = true;  
            if(WDWUtil.isExcel2007(fileName))\{  
                isExcel2003 = false;   
            \}  
            is = new FileInputStream(file);  
            userList=getExcelInfo(is, isExcel2003);  
            is.close();  
        \}catch(Exception e)\{  
            e.printStackTrace();  
        \}  
        finally\{  
            if(is !=null)  
            \{  
                try\{  
                    is.close();  
                \}catch(IOException e)\{  
                    is = null;     
                    e.printStackTrace();   
                \}  
            \}  
        \}  
        return userList;  
    \}  
    /\*\*  
     \* 此方法两个参数InputStream是字节流。isExcel2003是excel是2003还是2007版本  
     \* @param is  
     \* @param isExcel2003  
     \* @return  
     \* @throws IOException  
     \*/  
    public  List<User> getExcelInfo(InputStream is,boolean isExcel2003)\{  
         
         List<User> userList=null;  
         try\{  
             /\*\* 根据版本选择创建Workbook的方式 \*/  
             Workbook wb = null;  
             //当excel是2003时  
             if(isExcel2003)\{  
                 wb = new HSSFWorkbook(is);  
             \}  
             else\{  
                 wb = new XSSFWorkbook(is);  
             \}  
             userList=readExcelValue(wb);  
         \}  
         catch (IOException e)  \{   
             e.printStackTrace();   
         \}   
         return userList;  
    \}  
    /\*\*  
     \* 读取Excel里面的信息  
     \* @param wb  
     \* @return  
     \*/  
    private List<User> readExcelValue(Workbook wb)\{  
         //得到第一个shell   
         Sheet sheet=wb.getSheetAt(0);  
          
         //得到Excel的行数  
         this.totalRows=sheet.getPhysicalNumberOfRows();  
          
         //得到Excel的列数(前提是有行数)  
         if(totalRows>=1 && sheet.getRow(0) != null)\{  
              this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();  
         \}  
          
         List<User> userList=new ArrayList<User>();  
         User user;            //用户bean（组成：UserInfo+UserLogin）  
         UserInfo userInfo; //用户基本信息bean  
         UserLogin userLogin;    //用户登录bean  
          
         //循环Excel行数,从第二行开始。标题不入库  
         for(int r=1;r<totalRows;r++)  
         \{  
             Row row = sheet.getRow(r);  
             if (row == null) continue;  
              
             user=new User();  
             userInfo=new UserInfo();  
             userLogin=new UserLogin();  
              
             //循环Excel的列  
             for(int c = 0; c <this.totalCells; c++)  
             \{     
                 Cell cell = row.getCell(c);  
                 if (null != cell)   
                 \{  
                     //第一列  
                     if(c==0)\{  
                         //获得第一列<用户名>，放到到用户基本信息bean中。  
                        userInfo.setUserName(cell.getStringCellValue());  
                     \}  
                     //获得第二列<手机号>，放到到用户登录bean中。作为登录账号及密码  
                     else if(c==1)\{  
                         /\*\*  
                          \* 处理：使用POI读excel文件，当遇到特殊格式的字串，比如“13612345678”，等等，  
                          \* 这样的本来是一个字符串，但是POI在读的时候总是以数值型识别，由此，这样的电话号码读出来后总是1.3XXX+E4  
                          \*/  
                        DecimalFormat df = new DecimalFormat("\#");  
                        String cellValue=df.format(cell.getNumericCellValue());  
                         userLogin.setAccount(cellValue);  
                         userLogin.setPwd(cellValue);  
                      
                          
                     \}  
                     //第三列目前不入库，只是展示即可  
  
                     //第四列<用户地址>,放到到用户基本信息bean中。  
                     else if(c==3)\{  
                        userInfo.setCompanyAdd(cell.getStringCellValue());  
                     \}  
                 \}  
             \}  
             //添加其他值，入库时需要  
             userLogin.setUserId(Utils.getzId());//存放用户ID  
             userLogin.setInsertTime(Utils.getshortDate());//注册时间  
             userLogin.setUserRole("2"); //默认导入的用户都为供应商级别  
              
             userInfo.setUserInfoid(Utils.getzId());//存放用户ID  
             userInfo.setUserId(userLogin.getUserId());//基本信息的用户ID  
             
             user.setUserInfo(userInfo);  
             user.setUserLogin(userLogin);  
             
             userList.add(user);  
         \}  
         return userList;  
    \}  
\}  
/\*\*   
\* @描述：工具类  
\* 检验是否是EXCEL文件  
\*/    
class WDWUtil   
\{   
     // @描述：是否是2003的excel，返回true是2003  
    public static boolean isExcel2003(String filePath)  \{   
        return filePath.matches("^.+\\\\.(?i)(xls)$");   
    \}   
   
     //@描述：是否是2007的excel，返回true是2007  
    public static boolean isExcel2007(String filePath)  \{   
        return filePath.matches("^.+\\\\.(?i)(xlsx)$");   
    \}   
\}

[EXCEL_ORACLE_SpringMVC_MyBatis_]: http://blog.csdn.net/zlxdream815/article/details/41086995
[spring]: http://blog.csdn.net/zlxdream815/article/category/1285946
[Link 1]: http://blog.csdn.net/zlxdream815/article/details/41086995#comments
[Link 2]: http://blog.csdn.net/zlxdream815/article/details/41086995#report
[view plain]: http://blog.csdn.net/zlxdream815/article/details/41086995#
[Center]: /images/20220805/bff8db14701a4b1d82dc72d2d5d1f9a8.png