基于Spring MVC实现基于form表单上传Excel文件，批量导入数据

在pom.xml中引入:

        <!--处理2003 excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <!--处理2007 excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

applicationContext.xml：

    <!--上传组件-->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="defaultEncoding" value="utf-8"></property>
        <!--1024*1024*5即5M-->
        <property name="maxUploadSize" value="5242880"></property>
    </bean>

 

页面：

<div>
    <form method="post" action="/Cyberspace/main/informationBatchAdd.do" enctype="multipart/form-data">
        <input type="file" id="excelFile" name="file"/>
        <button type="submit">批量上传</button>
    </form>
</div>

Java后台代码：

@RequestMapping(value = "informationBatchAdd", method = RequestMethod.POST)
    @ResponseBody
    public void informationBatchAdd(MultipartHttpServletRequest request) {
       
        try {
            //得到上传的文件
            MultipartFile fileFile = request.getFile("file");
            //转换成输入流
            InputStream in = fileFile.getInputStream();
            XSSFWorkbook readWb = new XSSFWorkbook(in);
            /*HSSFWorkbook readWb = new HSSFWorkbook(in);*/
            //遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
            for (int i = 0; i < readWb.getNumberOfSheets(); i++) {
                XSSFSheet sheet = readWb.getSheetAt(i);
                // 循环行Row
                for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    XSSFRow hssfRow = sheet.getRow(rowNum);
                    if (hssfRow != null) {
                        for (int colNum = 0; colNum < hssfRow.getPhysicalNumberOfCells(); colNum++) {
                            System.out.println(hssfRow.getCell(colNum));
                        }
                        //赋值实例对象 做插入数据库操作
　　　　　　　　　　　　　　...
                    }
                }
            }
           
        } catch (Exception e) {
           
            e.printStackTrace();
        }
        
    }