Excel 文件导入导出【POI】

pom 依赖

<!-- POI依赖 poi的包 3.15版本后单元格类型获取方式有调整 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.14</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>

1.Excel 导出

Service 层

public void exportExcel(HttpServletResponse response) throws IOException {
        //1创建一个 workbook， 对应一个 excel文件
        // HSSFWorkbook -> xls
        // XSSFWorkbook -> xlsx
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2在 workbook 里添加一个 sheet
        HSSFSheet sheet01 = workbook.createSheet("这是Sheet01");
        //3在 sheet01 里添加 表头第 0 行
        HSSFRow row = sheet01.createRow(0);
        //4创建单元格风格 进行设置
        HSSFCellStyle style = workbook.createCellStyle();
        //设置单元格居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //设置表头 列名
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("姓名");
        cell = row.createCell(2);
        cell.setCellValue("年龄");
        cell = row.createCell(3);
        cell.setCellValue("是否删除");
        cell = row.createCell(4);
        cell.setCellValue("创建时间");
        cell = row.createCell(5);
        cell.setCellValue("更新时间");

        //获取集合
        List<LiuMpDemo> list = liuMpDemoMapper.selectList(null);
        //遍历集合    创建单元格  并设置值
        for (int i = 0; i < list.size(); i++) {
            row = sheet01.createRow(i + 1);
            LiuMpDemo one = list.get(i);
            row.createCell(0).setCellValue(one.getId());
            row.createCell(1).setCellValue(one.getName());
            row.createCell(2).setCellValue(one.getAge());
            row.createCell(3).setCellValue(one.getIsDeleted());
            row.createCell(4).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(one.getCreateTime()));
            row.createCell(5).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(one.getUpdateTime()));
        }

        ServletOutputStream outputStream = response.getOutputStream();
        response.reset();
        long timeMillis = System.currentTimeMillis();
        String nowDate = new SimpleDateFormat("yyyy-MM-dd HH-MM-ss").format(new Date());
        response.setHeader("Content-disposition","attachment; filename="+nowDate+timeMillis+".xls");
        response.setContentType("application/msexcel");
        workbook.write(outputStream);
        outputStream.close();
    }

Controller层

@GetMapping("/export")
    public void exportExcel(HttpServletResponse response){
        try {
            liuMpDemoService.exportExcel(response);
        } catch (IOException e) {
            System.out.println("IO异常了老铁！！！");
            e.printStackTrace();
        }
    }

【数据库数据】

【导出文件】

2.Excel 导入

Service层

public String importExcel(MultipartFile file) throws Exception {
        Workbook workbook = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(".xls")){
            workbook = new HSSFWorkbook(file.getInputStream());
        }else if (fileName.endsWith(".xlsx")){
            workbook = new XSSFWorkbook(file.getInputStream());
        }else {
            throw new Exception("该文件不是Excel文件");
        }

        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum == 0){
            throw new Exception("文件内没有数据 请确认后重试！");
        }

        for (int i = 0; i < lastRowNum; i++) {
            Row row = sheet.getRow(i + 1);
            if (row != null){
                //创建一个对象
                LiuMpDemo one = new LiuMpDemo();
                //1读取Excel获取id 并给对象赋值
//                String id = getCellValue(row.getCell(0));
//                one.setId(id);
                //2读取Excel获取name 并给对象赋值
                String name = getCellValue(row.getCell(1));
                one.setName(name);
                //3读取Excel获取age 并给对象赋值
                String age = getCellValue(row.getCell(2));
                if (StringUtils.isEmpty(age)){
                    one.setAge(Integer.valueOf(age));
                }
                //4读取Excel获取isDeleted 并给对象赋值
                String isDeleted = getCellValue(row.getCell(3));
                if (StringUtils.isEmpty(isDeleted)){
                    one.setIsDeleted(Integer.valueOf(isDeleted));
                }
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                String createTime = getCellValue(row.getCell(4));
                if (StringUtils.isEmpty(createTime)){
                    Date date = format.parse(createTime);
                    one.setCreateTime(date);
                }
                String updateTime = getCellValue(row.getCell(5));
                if (StringUtils.isEmpty(updateTime)){
                    Date date = format.parse(updateTime);
                    one.setUpdateTime(date);
                }
                liuMpDemoMapper.insert(one);
            }
        }
        return null;
    }

· 用来获取到 excel 文件内容的方法，导入时将excel内容转为Srting，放在service层可以调用到的地方即可

public String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                // 数字
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                // 字符串
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                // Boolean
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue() + "";
                    break;
                // 公式
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula() + "";
                    break;
                // 空值
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                // 故障
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

Controller层

@PostMapping("/import")
    public String importExcel(@RequestParam("file") MultipartFile file){
        ModelAndView modelAndView = new ModelAndView();
        try {
            liuMpDemoService.importExcel(file);
        } catch (Exception e) {
            modelAndView.addObject("msg", e.getMessage());
            e.printStackTrace();
        }
        return "导入成功";
    }

【导入前数据库】

【导入的文件】

【导入后数据库】

此处为NULL应该是填入数据时有问题，注意拿放时的正确性即可