java中使用poi导入Excel表格

一.pom.xml中导入所需要的依赖:

<!--读取excel文件-->
        //操作03版的Excel,即以.xls结尾的excel表
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        //操作07版的Excel,即以.xlsx结尾的Excel表
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

二.编写前端代码:

     //HTML:       
<input id="articleImageFile" name="excelFile" type="file" class="form-control" style="width: 300px; display: inline;" /> <a class="btn btn-warning" id = "import" onclick="importExcel()" shiro:hasPermission="system:student:import"> <i class="fa fa-upload"></i> 导入</a>
//js代码:
<script>
 function importExcel() {
            var formData = new FormData();
            var name = $("#articleImageFile").val();
            formData.append("file", $("#articleImageFile")[0].files[0]);
            formData.append("name", name);//这个地方可以传递多个参数
            $.ajax({
                url: '/system/student/importExcel',               
                type: 'POST',
                async: false,
                data: formData,
                // 告诉jQuery不要去处理发送的数据
                processData: false,
                // 告诉jQuery不要去设置Content-Type请求头
                contentType: false,
                beforeSend: function () {
                    console.log("正在进行,请稍候");
                },
                success: function (responseStr) {
                    if (responseStr == "上传成功") {
                        alert("导入成功");
                    } else {
                        alert("导入失败");
                    }
                }
            });
        }

    </script>
三.controller层代码:
  @RequestMapping("/importExcel")
    @ResponseBody
    public String importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse
            response) {
        System.out.println("file"+file.getSize());
        try {
            // @RequestParam("file") MultipartFile file 是用来接收前端传递过来的文件
            // 1.创建workbook对象,读取整个文档
            InputStream inputStream = file.getInputStream();
            //POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            // 2.读取页脚sheet
            XSSFSheet sheetAt = wb.getSheetAt(0);

            // 3.循环读取某一行
            int index = 0;
            for (Row row : sheetAt) {
                // 4.读取每一行的单元格
                if (index == 0) {
                    index++;
                    continue;
                }
//创建一个学生对象 SysStudent student
= new SysStudent();

//将Excel表中单元格的值与学生对象的值对应 student.setName(row.getCell(
0).getStringCellValue());
//因为学号是数字,Excel默认是数字类型,我的数据库是字符串类型,所以需要设置下类型 row.getCell(
1).setCellType(CellType.STRING); student.setStuId(row.getCell(1).getStringCellValue()); student.setIdentity(row.getCell(2).getStringCellValue()); student.setDescription(row.getCell(3).getStringCellValue()); student.setProvince(row.getCell(4).getStringCellValue());
//将对应好的学生对象存入数据库中 sysStudentService.insertSysStudent(student); row.getCell(
0).setCellType(CellType.STRING); String stringCellValue = row.getCell(0).getStringCellValue(); row.getCell(1).setCellType(CellType.STRING); String stringCellValue2 = row.getCell(1).getStringCellValue(); row.getCell(2).setCellType(CellType.STRING); String stringCellValue3 = row.getCell(2).getStringCellValue(); row.getCell(3).setCellType(CellType.STRING); String stringCellValue4 = row.getCell(3).getStringCellValue(); row.getCell(4).setCellType(CellType.STRING); String stringCellValue5 = row.getCell(4).getStringCellValue(); // 写多少个具体看大家上传的文件有多少列..... // 后台测试是否读取到数据,及数据的正确性 System.out.println(stringCellValue); System.out.println(stringCellValue2); System.out.println(stringCellValue3); System.out.println(stringCellValue4); System.out.println(stringCellValue5); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return "上传成功"; }

 

posted @ 2020-06-11 10:36  li&り&Lee  阅读(999)  评论(0编辑  收藏  举报