POI

把excel数据批量导入数据库

1.导入jar包

//这三个是用POI必须要用的jar包
<!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
    <dependency>
        <groupId>commons-fileupload</groupId>
        <artifactId>commons-fileupload</artifactId>
        <version>1.3.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.15</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/stax/stax-api -->
    <dependency>
        <groupId>stax</groupId>
        <artifactId>stax-api</artifactId>
        <version>1.0</version>
    </dependency>

2.以springmvc为例子配置文件

//设置最大值B为单位
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="maxUploadSize" value="10000000" />
</bean>

3.前端的抒写方式

//enctype="multipart/form-data"必须写，不然没有值
<form action="${pageContext.request.contextPath}/file/upload" method="post" enctype="multipart/form-data">
    <input type="file" name="excelFile" >
    <input type="submit" value="确定">

4.controller层接受处理excel

    //list用来放数据
    List<Dept> list = new Arraylist<Dept>();
    //获取流对象
    InputStream is = exclelFile.getInputStream();
    //获得excel对象
    XSSFWorkbook excel = new XSSFWorkbook(is);
    //获得当前excel有多少页
    for(int i = 0 ; i < excel.getNumberOfSheets() ; i++){
        //判断当前页是否为空，为空直接continue
        if(excel.getSheetAt(i) == null){
            continue;
        }else{
            //判断当前页有多少行
            for(int j = 0; j < excel.getSheetAt(i).getLastRowNumber ; j++){
                //excel.getsheetAt(i).getRow(j).getCell(0)代表第i页第j行第0列
                list.add(new Dept(excel.getsheetAt(i).getRow(j).getCell(0),excel.getSheetAt(i).getRow(j).getCell(1)));
            }
        }
    }
}

5.EXCEL表格：

Tables
研发一部	北京
研发二部	上海
研发三部	武汉