导入Excel数据到后台

需要的依赖

<!--导出到Excel-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-excelant</artifactId>
      <version>3.17</version>
    </dependency>

需要在web-INF中配置上传下载

<!--上传下载-->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <!-- 可以添加一个属性配置，用来限制上传文件的大小，当上传文件超出指定值时，无法上传 -->
        <!-- 单位B -->
        <property name="maxUploadSize" value="10285760"></property>
    </bean>

controller层代码

 @RequestMapping("/archives-add")
    public String archivesAdd(MultipartFile files) throws Exception {
        System.out.println(files);
        if(files != null){
            //创建一个集合,保存表中的数据
            List<Archives> archs = new ArrayList<>();
            //上传的文件对应的对象
            XSSFWorkbook workbook = null;
            String originalFilename = files.getOriginalFilename();
            File file = new File("C:\\Users\\Administrator\\Desktop\\项目",originalFilename);
            files.transferTo(file);

            //读取Excel表格文件
            InputStream inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            inputStream.close();
            //循环文件中的每个sheet
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                //循环读取Excel表格中每一行数据
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }

                    // 将单元格中的内容存入集合
                    Archives arch = new Archives();

                    XSSFCell cell = hssfRow.getCell(0);
                    if (cell == null) {
                        continue;
                    }
                    arch.setDnum(cell.getStringCellValue());

                    cell = hssfRow.getCell(1);
                    if (cell == null) {
                        continue;
                    }
                    arch.setLandline(((int)cell.getNumericCellValue()) + "");

                    cell = hssfRow.getCell(2);
                    if (cell == null) {
                        continue;
                    }
                    arch.setSchool(cell.getStringCellValue());

                    cell = hssfRow.getCell(3);
                    if (cell == null) {
                        continue;
                    }
                    arch.setZhuanye(cell.getStringCellValue());

                    cell = hssfRow.getCell(4);
                    if (cell == null) {
                        continue;
                    }
                    arch.setSosperson(cell.getStringCellValue());

                    cell = hssfRow.getCell(5);
                    if (cell == null) {
                        continue;
                    }
                    String str1 = cell.getStringCellValue();
                    Date parse = sdf.parse(str1);
                    arch.setBiyedate(parse);

                    cell = hssfRow.getCell(6);
                    if (cell == null) {
                        continue;
                    }
                    arch.setZzmm(cell.getStringCellValue());

                    cell = hssfRow.getCell(7);
                    if (cell == null) {
                        continue;
                    }
                    arch.setMinzu(cell.getStringCellValue());

                    cell = hssfRow.getCell(8);
                    if (cell == null) {
                        continue;
                    }
                    arch.setXueli(cell.getStringCellValue());

                    cell = hssfRow.getCell(9);
                    if (cell == null) {
                        continue;
                    }
                    arch.setEmail(cell.getStringCellValue());

                    cell = hssfRow.getCell(10);
                    if (cell == null) {
                        continue;
                    }
                    String empStr = "";
                    int cellType = cell.getCellType();
                    if(cellType == XSSFCell.CELL_TYPE_NUMERIC){
                        empStr = (int)cell.getNumericCellValue() + "";
                    }else {
                        empStr = cell.getStringCellValue();
                    }

                    Employee employee = employeeService.queryEmployeeByUsername(empStr);
                    arch.setEmpFk(employee.getEid());

                    cell = hssfRow.getCell(11);
                    if (cell == null) {
                        continue;
                    }
                    arch.setRemark(cell.getStringCellValue());

                    cell = hssfRow.getCell(12);
                    if (cell == null) {
                        continue;
                    }
                    String str2 = cell.getStringCellValue();
                    Date parse1 = sdf.parse(str2);
                    arch.setHirdate(parse1);

                    //读取出来的一个数据，封装到了一个对象，对象再放入集合
                    archs.add(arch);
                }
            }

            //保存集合数据
            System.out.println(1);
            System.out.println(archs);
            archService.save(archs);
        }
        return "/archives.jsp";
    }