数据导入导出（Excel表格）

将数据库的数据导入导出成excel 表格

所需要的maven 依赖

        <!--导入导出所用到的包-->
		<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>

导出

前端代码
通过get的方式导出

<button onclick="exportData()">数据导出</button>
<script>
function exportData(){
    let a = document.createElement("a");
    a.href="/exportExcel";
    a.target="_blank";
    a.click();
}
</script>

控制器接口

@GetMapping("exportExcel")
    public ResponseEntity<Resource> exportExcel() {
        ByteArrayResource resource = new ByteArrayResource(userService.exportExcel().toByteArray());
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.add("Content-Disposition", "attachment; filename="
                + new String("用户档案.xls".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        return ResponseEntity.ok()
                .headers(headers)
                .contentType(MediaType.APPLICATION_OCTET_STREAM)
                .body(resource);
    }

service

public ByteArrayOutputStream exportExcel() {
        //从数据库将数据查询出来
        List<User> users = userMapper.exportExcel();
        Workbook workbook = createExcel(users);
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return outputStream;
}


public Workbook createExcel(List<User> users) {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        CreationHelper helper = workbook.getCreationHelper();
        //格式化时间
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-MM-dd"));
        
        Row headRow = sheet.createRow(0);
        headRow.createCell(0).setCellValue("用户ID");
        headRow.createCell(1).setCellValue("用户名");
        headRow.createCell(2).setCellValue("创建时间");
        headRow.createCell(3).setCellValue("密码");
        
        //当前行数
        int col = 1;
        for (User u : users) {
            Row row = sheet.createRow(col);
            row.createCell(0).setCellValue(u.getId());
            row.createCell(1).setCellValue(u.getUsername());
            Cell cell2 = row.createCell(2);
            cell2.setCellStyle(cellStyle);
            cell2.setCellValue(u.getCreateTime());
            row.createCell(3).setCellValue(e.getPassword());
            col++;
        }
        return workbook;
    }

导入

前端代码

<form onsubmit="return false" id="up-excel-form" enctype="multipart/form-data">
    <p>请选择Excel文件：</p><input name="excel" type="file"  accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" id="fileSelect" runat="server"><br>
    <button id="but-c" onclick="upExcel()">确定</button>
</form>
<script>
function importExcel() {
    let excel = $("#fileSelect").val();
    if (excel === "") {
        $("#up-excel-warning").text("请选择Excel文件!");
    } else {
        let formData = new FormData($("#up-excel-form")[0]);
        $.ajax({
            type: "post",
            url: "/importExcel",
            contentType: false,
            processData: false,
            data: formData,
            dataType: "text",
            complete: function (e) {
                let msg = e.responseText;
                let status = e.status;
                if (status === 200) {
                    $("#up-excel-warning").css("color: #00ff00").text(msg);
                } else {
                    $("#up-excel-warning").text("文件内容格式不正确!");
                }
            }
        })
    }
}
</script>

Controller

    @PostMapping("importExcel")
    @ResponseBody
    public ResponseEntity<String> importExcel(@RequestParam("excel") MultipartFile excel) {
        HttpStatus status;
        String msg;
        try {
            userService.importExcel(excel);
            status = HttpStatus.OK;
            msg = "导入信息成功!";
        } catch (IOException | InvalidFormatException e) {
            status = HttpStatus.BAD_REQUEST;
            msg = "文件格式或文件内容格式不正确!";
        }
        return new ResponseEntity<>(msg, status);
    }

Service

public void importExcel(MultipartFile excel) throws IOException, InvalidFormatException {
    List<User> users = parseExcel(excel);
    //循环向数据库插入或批量插入就懒得写了
}

public List<User> parseExcel(MultipartFile file) throws IOException, InvalidFormatException {
        List<User> users;
        Workbook workbook;
        Sheet sheet;
        String filename = file.getOriginalFilename() == null ? "" : file.getOriginalFilename();
        if (filename.endsWith(".xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
            workbook = new HSSFWorkbook(fs);
            // 只解析Excel文件的第一个Sheet
            sheet = workbook.getSheetAt(0);
            users = parseSheet(sheet);
            fs.close();
        } else if (filename.endsWith(".xlsx")) {
            OPCPackage pkg = OPCPackage.open(file.getInputStream());
            workbook = new XSSFWorkbook(pkg);
            // 只解析Excel文件的第一个Sheet
            sheet = workbook.getSheetAt(0);
            users = parseSheet(sheet);
            pkg.close();
        } else {
            logger.error("excel error!");
            throw new IOException();
        }
        return excelVos;
}

private List<User> parseSheet(Sheet sheet){
    List<User> users = new ArrayList<>();
    int n = sheet.getPhysicalNumberOfRows();
    for (int i = 1; i < n; i++) {
        Row row = sheet.getRow(i);
        User u = new User();
        int j = 0;
        //该转什么类型的数据就自己转
        u.setId(cellToString(row.getCell(j++)));
        u.setUsername(cellToString(row.getCell(j++)));
        u.setPassword(cellToString(row.getCell(j++)));
        u.setCreateTime(cellToString(row.getCell(j++)));
        users.add(u);
    }
    return users;
}

private String cellToString(Cell cell) {
    switch (cell.getCellType()) {
        case STRING:
            return cell.getStringCellValue();
        case NUMERIC:
            return String.valueOf(cell.getNumericCellValue());
        default:
            return "";
        }
}

写的也差不多了，数据有图片的也可以查考的我将图片转字符串存excel表格
图片，字符串互相转换