Sprinboot实现文件的上传与下载

最新推荐文章于 2024-08-14 11:58:59 发布

三点水不是水

最新推荐文章于 2024-08-14 11:58:59 发布

阅读量330

点赞数 8

分类专栏： springboot poi easyExcel 文章标签： spring boot mybatis maven

本文链接：https://blog.csdn.net/qq_46019299/article/details/135134174

版权

springboot 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

poi

1 篇文章 0 订阅

订阅专栏

easyExcel

1 篇文章 0 订阅

订阅专栏

本文介绍了如何使用SpringBoot、MyBatisPlus和EasyExcel库将Excel文件的内容写入MySQL数据库，并演示了如何导出数据库数据到Excel。涉及数据库表结构配置、依赖管理以及上传下载文件的控制器和服务实现。

摘要由CSDN通过智能技术生成

需求1：依据给定的excel，将其内容写入mysql数据库；
在这里插入图片描述第一步：在mysql中建数据表，注意字段的命名，主键设置自增长，数据库表结构如下:
第二步：添加pom依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

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

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>


        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
            <version>2.1.6.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>



    </dependencies>

第三步：借助mybatis逆向工程自动生成代码
①配置数据源，连接本地数据库
在这里插入图片描述

②选中对应数据表
②填写包名，勾选需要生成的文件
在这里插入图片描述
第四步：上传文件并写入数据库核心逻辑
FormulaController.java部分代码：

@RequestMapping(value = "/uploadFiles")
    public @ResponseBody
    Map<String ,Object> uploadExcl(HttpServletRequest request, @RequestParam("file") MultipartFile file){
        Map<String ,Object> result = new HashMap<>();
        String path = request.getSession().getServletContext().getRealPath("/");
        try{
            // 如果文件不为空，写入上传路径
            if(!file.isEmpty()){
                result = formulaService.uploadFiles(file);
            }else {
                result.put("code","1");
                result.put("message","上传文件为空！");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        if (result.get("code").equals("0")){
            //根据时间戳创建新的文件名，这样即便是第二次上传相同名称的文件，也不会把第一次的文件覆盖了
            //也可以用UUID创建
            String fileName = System.currentTimeMillis() + file.getOriginalFilename();
            //通过req.getServletContext().getRealPath("") 获取当前项目的真实路径，然后拼接前面的文件名
            String destFileName = request.getContextPath()+ "uploaded" + File.separator + fileName;
            System.out.println(request.getServletPath());
            System.out.println(request.getServletContext());
            System.out.println(request.getServletContext().getRealPath(""));
            System.out.println(request.getServletContext().getRealPath("/"));
            System.out.println(request.getContextPath());
            System.out.println(destFileName);
            //第一次运行的时候，这个文件所在的目录往往是不存在的，这里需要创建一下目录
            File destFile = new File(destFileName);
            destFile.getParentFile().mkdirs();
            System.out.println(destFile);
            //把浏览器上传的文件复制到希望的位置
            try {
                file.transferTo(destFile);
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.out.println(fileName);
        }
        return result;
    }

FormulaServiceImpl.java部分代码

    @Override
    public Map<String, Object> uploadFiles(MultipartFile file) {
        Map<String,Object> ruslt = new HashMap<>();
        try {
            String fileName = file.getOriginalFilename();
            //判断文件格式并获取工作簿
            Workbook workbook;
            if(fileName.endsWith("xls")){
                workbook = new HSSFWorkbook(file.getInputStream());
            }else if(fileName.endsWith("xlsx")){
                workbook = new XSSFWorkbook(file.getInputStream());
            } else {
                ruslt.put("code","1");
                ruslt.put("message","文件格式非excl");
                return ruslt;
            }
            //判断第一页不为空
            if(null != workbook.getSheetAt(0)){
                //读取excl第二行，从1开始
                for(int rowNumofSheet = 1;rowNumofSheet <=workbook.getSheetAt(0).getLastRowNum();rowNumofSheet++){
                    if (null != workbook.getSheetAt(0).getRow(rowNumofSheet)) {
                        //定义行，并赋值
                        Row aRow = workbook.getSheetAt(0).getRow(rowNumofSheet);
                        Formula formula = new Formula();
                        System.out.println(aRow.getLastCellNum());
                        for(int cellNumofRow=0;cellNumofRow<aRow.getLastCellNum();cellNumofRow++){
                            //读取rowNumOfSheet值所对应行的数据
                            //获得行的列数
                            Cell xCell = aRow.getCell(cellNumofRow);
                            xCell.setCellType(CellType.STRING);
                            Object cell_val;
                            if(cellNumofRow == 1){
                                if(xCell != null && !xCell.toString().trim().isEmpty()){
                                    cell_val = xCell.getStringCellValue();
                                    if(cell_val != null){
                                        String temp = (String)cell_val;
                                        formula.setPatientId(Integer.parseInt(temp));
                                    }
                                }
                            }
                            if(cellNumofRow == 2){
                                if(xCell != null && !xCell.toString().trim().isEmpty()){
                                    cell_val = xCell.getStringCellValue();
                                    if(cell_val != null){
                                        String medname = (String)cell_val;
                                        formula.setMedicineName(medname);
                                    }
                                }
                            }
                            if(cellNumofRow == 3){
                                if(xCell != null && !xCell.toString().trim().isEmpty()){
                                    cell_val = xCell.getStringCellValue();
                                    if(cell_val != null){
                                        String gfh = (String)cell_val;
                                        formula.setNormalizeName(gfh);
                                    }
                                }
                            }
                            if(cellNumofRow == 4){
                                if(xCell != null && !xCell.toString().trim().isEmpty()){
                                    cell_val = xCell.getStringCellValue();
                                    if(cell_val != null){
                                        String fl = (String)cell_val;
                                        formula.setClassification(fl);
                                    }
                                }
                            }


                        }
                        formulaDao.insert(formula);
                    }

                }
                ruslt.put("code","0");
                ruslt.put("message","成功插入数据库！");
            }else {
                ruslt.put("code","1");
                ruslt.put("message","第一页EXCL无数据！");
            }
        }catch (Exception e){
            e.printStackTrace();
            ruslt.put("code","1");
            ruslt.put("message",e.getMessage());
        }
        return ruslt;
    }

部分页面代码：
home.html

<a href="/upload2">上传文件</a>&nbsp;&nbsp;&nbsp;<a th:href="@{'/results/download'}">导出文件</a>

upload2.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

<div class="panel panel-primary">
    <!-- .panel-heading 面板头信息。 -->
    <div class="panel-heading">
        <!-- .panel-title 面板标题。 -->
        <h1 class="panel-title">上传excel并插入到数据库</h1>
    </div>
</div>
<form class="form-horizontal" action="/formula/uploadFiles" enctype="multipart/form-data" method="post">
    <div class="form-group">
        <div class="input-group col-md-4">
                    <span class="input-group-addon">
                <i class="glyphicon glyphicon-search"></i>
            </span>
            <input class="form-control" placeholder="请选择文件" type="file" name="file"/>
        </div>
    </div>
    <div class="form-group">
        <div class="col-md-4">
            <div class="btn-group btn-group-justified" >
                <div class="btn-group" >
                    <button type="submit" class="btn btn-success" id="submitbtn">
                        <span class="glyphicon glyphicon-share"></span>&nbsp;文件上传</button>
                </div>
            </div>
        </div>
    </div>
</form>

需求2：将数据库的数据导出并写入excel
导出文件模板如下：
在这里插入图片描述同理，重复上述第一步和第三步，为导出结果创建实体类和相关代码；
实体类需要添加@Data和@ExcelProperty的注解，便于数据写入excel。本项目的results实体类如下：

@Data
public class Results implements Serializable {
    private static final long serialVersionUID = -15605474560361154L;

    @ExcelProperty(value="ID", index=0)
    private Integer rid;
    @ExcelProperty(value="住院号", index=1)
    private Integer pid;
    @ExcelProperty(value="中药名称", index=2)
    private String name;
    @ExcelProperty(value="基本信息代码", index=3)
    private String infoCodes;
    @ExcelProperty(value="药性代码", index=4)
    private String propertyCodes;
    @ExcelProperty(value="功能代码", index=5)
    private String functionCodes;
    @ExcelProperty(value="主治代码", index=6)
    private String treatmentCodes;
    @ExcelProperty(value="禁忌代码", index=7)
    private String tabooCodes;
    @ExcelProperty(value="贮藏方式代码", index=8)
    private String storageCodes;


    public Integer getRid() {
        return rid;
    }

    public void setRid(Integer rid) {
        this.rid = rid;
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getInfoCodes() {
        return infoCodes;
    }

    public void setInfoCodes(String infoCodes) {
        this.infoCodes = infoCodes;
    }

    public String getPropertyCodes() {
        return propertyCodes;
    }

    public void setPropertyCodes(String propertyCodes) {
        this.propertyCodes = propertyCodes;
    }

    public String getFunctionCodes() {
        return functionCodes;
    }

    public void setFunctionCodes(String functionCodes) {
        this.functionCodes = functionCodes;
    }

    public String getTreatmentCodes() {
        return treatmentCodes;
    }

    public void setTreatmentCodes(String treatmentCodes) {
        this.treatmentCodes = treatmentCodes;
    }

    public String getTabooCodes() {
        return tabooCodes;
    }

    public void setTabooCodes(String tabooCodes) {
        this.tabooCodes = tabooCodes;
    }

    public String getStorageCodes() {
        return storageCodes;
    }

    public void setStorageCodes(String storageCodes) {
        this.storageCodes = storageCodes;
    }

}

数据导出的核心逻辑：
①ResultsDao.java中添加导出方法

void downloadExcel(HttpServletResponse response);

②ResultsService.java中添加导出方法

//导出
 void downloadExcel(HttpServletResponse response);

③ResultsServiceImpl.java中实现导出逻辑

@Override
    public void downloadExcel(HttpServletResponse response) {
        try {
            List<Results> rlist = new ArrayList<>();
            rlist = resultsDao.getList();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("编码结果", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream()).head(Results.class).excelType(ExcelTypeEnum.XLSX).sheet("编码信息").doWrite(rlist);
        } catch (Exception e) {
            System.err.println("导出文件异常"+e);
        }

    }

④ResultsController.java

@GetMapping("/download")
    public void downloadExcel(HttpServletResponse response) {

        resultsService.downloadExcel(response);
    }

遇到的问题：
①数据库语法错误：check the manual that corresponds to your MySQL server version for the right syntax to use near 'function, treatment, taboo, storage)
解决办法：修改function, treatment, taboo, storage这四个字段的名字