cover

SpringBoot基础-word导出

word导出

AGi_

3901人浏览 · 2022-05-19 23:46:00

AGi_ · 2022-05-19 23:46:00 发布

引言

在我们做项目的时候会需要把数据库中的数据导出到word当中，这篇博客使用FreeMarker实现了word的导出。以下面word为例实现word的导出。

主要步骤如下：

创建数据库

创建student_word表，用来存储学生信息

CREATE TABLE `student_word` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `number` varchar(255) DEFAULT NULL COMMENT '编号',
  `img_url` varchar(255) DEFAULT NULL COMMENT '头像路径',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息';

创建score表，用来存储成绩

CREATE TABLE `score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `location` int(255) DEFAULT NULL COMMENT '位置1各科成绩，2总成绩',
  `name` varchar(255) DEFAULT NULL COMMENT '课程名称',
  `value` decimal(10,2) DEFAULT NULL COMMENT '得分',
  `student_word_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建SpringBoot项目

导入依赖

<properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
        <commons.io.version>2.5</commons.io.version>
        <commons.fileupload.version>1.3.3</commons.fileupload.version>
        <poi.version>4.1.2</poi.version>
    </properties>


    <dependencies>
        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!--io常用工具类 -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>${commons.io.version}</version>
        </dependency>
        <!--文件上传工具类 -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>${commons.fileupload.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.72</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</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.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- 日志 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-logging</artifactId>
            <exclusions>            <!-- 排除spring-boot-starter-logging中的全部依赖 -->
                <exclusion>
                    <groupId>*</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
            <scope>test</scope>     <!-- 打包的时候不打spring-boot-starter-logging.jar -->
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
        </dependency>
    </dependencies>

配置application.yml

mybatis:
  mapper-locations: classpath:/mybatis/**/*.xml
  type-aliases-package: com.example.note.domain
  map-underscore-to-camel-case: true
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  typeAliasesPackage: com.example.note.domain  #存放实体类的目录路径
  mapperLocations: classpath:mybatis/**/*.xml
  # 全局配置id自增  =>
  global-config:
    db-config:
      id-type: auto

server:
  port: 8080
spring:
  application:
    name: note-back
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    name: defaultDataSource
    password: '123456789'
    url: jdbc:mysql://localhost:3306/note?serverTimezone=UTC
    username: 'root'
  freemarker:
    cache: false
    charset: utf-8
    expose-request-attributes: true
    expose-session-attributes: true
    suffix: .ftl
    template-loader-path: classpath:/webapp/

编写项目

创建实体类

package com.example.noteback.studentword.domain;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("student_word")
public class Student {
    private Long id;
    private String name;
    private String number;
    private String imgUrl;
}

@Data
@TableName("score")
public class Score {
    private Long id;
    private Integer location;
    private String name;
    private Double value;
    private Long studentWordId;
}

创建Mapper接口

package com.example.noteback.studentword.mapper;

import com.example.noteback.studentword.domain.Score;
import com.example.noteback.studentword.domain.Student;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface StudentWordMapper {
    public Student getStudentById(@Param("id")Long id);
    public List<Score> getStudentsScoreListByStudentId(@Param("studentId")Long studentId);
}

创建StudentWordMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.noteback.studentword.mapper.StudentWordMapper">
    <select id="getStudentById" resultType="com.example.noteback.studentword.domain.Student">
        select * from student_word where id=#{id}
    </select>
    <select id="getStudentsScoreListByStudentId" resultType="com.example.noteback.studentword.domain.Score">
        select * from score where student_word_id=#{studentId}
    </select>

</mapper>

创建Service接口

package com.example.noteback.studentword.service;

import com.example.noteback.studentword.domain.Score;
import com.example.noteback.studentword.domain.Student;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface IStudentWordService {
    public Student getStudentById(Long id);
    public List<Score> getStudentsScoreListByStudentId(Long studentId);
}

创建ServiceImpl类

package com.example.noteback.studentword.service.impl;

import com.example.noteback.studentword.domain.Score;
import com.example.noteback.studentword.domain.Student;
import com.example.noteback.studentword.mapper.StudentWordMapper;
import com.example.noteback.studentword.service.IStudentWordService;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

public class StudentWordServiceImpl implements IStudentWordService {
    @Autowired
    private StudentWordMapper studentWordMapper;
    @Override
    public Student getStudentById(Long id) {
        return this.studentWordMapper.getStudentById(id);
    }

    @Override
    public List<Score> getStudentsScoreListByStudentId(Long studentId) {
        return this.studentWordMapper.getStudentsScoreListByStudentId(studentId);
    }
}

word导出

这步骤很简单，因为我们要使用FreeMarker进行，所以我们先得把word转成FreeMarker辨别出来的格式:

第一步，word打开我们要转的文件，另存为xml格式

第二部修改后缀为ftl，存放到resource目录下

上述步骤结束之后，正式开始编写word导出方法

package com.example.noteback.studentword.controller;

import com.example.noteback.studentword.domain.Score;
import com.example.noteback.studentword.domain.Student;
import com.example.noteback.studentword.service.impl.StudentWordServiceImpl;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import sun.misc.BASE64Encoder;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("studentWord")
public class StudentWordController {
    @Autowired
    private StudentWordServiceImpl studentWordService;

    @GetMapping("word")
    public HttpServletResponse word(@RequestParam("studentId") Long id,HttpServletResponse response) throws Exception {
        /** 初始化配置文件 **/
        Configuration configuration = new Configuration(Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
        /** 设置编码 **/
        configuration.setDefaultEncoding("utf-8");
        /** 我的ftl文件是放在D盘的**/
        String fileDirectory = "E:\\DATA\\IDEA-2022\\note\\note-back\\src\\main\\resources\\word\\";
        /** 加载文件 **/
        configuration.setDirectoryForTemplateLoading(new File(fileDirectory));
        /** 加载模板 **/
        Template template = configuration.getTemplate("student.ftl");
        /** 准备数据 **/
        Map<String, Object> dataMap = new HashMap<>();
        //学生信息
        Student student = this.studentWordService.getStudentById(id);
        dataMap.put("number", student.getNumber());
        dataMap.put("name", student.getName());
        dataMap.put("img", getImageStr(student.getImgUrl()));

        //得分
        List<Score> scoreList = this.studentWordService.getStudentsScoreListByStudentId(id);
        //非总分
        List<Map<String, Object>> mapList = new ArrayList<>();
        for (Score score : scoreList) {
            Map<String, Object> map = new HashMap<>();
            if (score.getLocation() == 1) {
                map.put("name", score.getName());
                map.put("value", score.getValue());

                mapList.add(map);
            } else if (score.getLocation() == 2) {
                //总分
                dataMap.put("total", score.getValue());
            }
        }
        dataMap.put("score", mapList);

        //* 指定输出word文件的路径 *
        String outFilePath = "E:\\DATA\\IDEA-2022\\export\\myFreeMarker" + (System.currentTimeMillis()) + ".doc";
        //创建 myFreeMarker"+(System.currentTimeMillis())+".doc 的映射对象
        File docFile = new File(outFilePath);
        //创建输出流
        FileOutputStream fos = new FileOutputStream(docFile);
        //创建缓冲器
        Writer out = new BufferedWriter(new OutputStreamWriter(fos, "utf-8"), 10240);
        //导出word
        template.process(dataMap, out);


        /*文件下载*/
        File file = new File(outFilePath);
        // 取得文件名。
        String filename = file.getName();
        //下载
        InputStream fis = new BufferedInputStream(new FileInputStream(outFilePath));
        byte[] buffer = new byte[fis.available()];
        fis.read(buffer);
        fis.close();
        // 清空response
        response.reset();
        // 设置response的Header
        response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
        response.addHeader("Content-Length", "" + file.length());
        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        response.setContentType("application/octet-stream");
        toClient.write(buffer);
        toClient.flush();
        toClient.close();

        //关闭缓冲器
        if (out != null) {
            out.close();
        }
        return response;
    }

    //图片转BASE64Encoder
    public static String getImageStr(String imgFile) {
        InputStream in = null;
        byte[] data = null;
        try {
            in = new FileInputStream(imgFile);
            data = new byte[in.available()];
            in.read(data);
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data);
    }
}

编辑student.ftl模板

源码

gitee公开仓库

华为开发者空间

华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。

更多推荐

cover

解析openGauss账本数据库

华为开发者空间

cover

GaussDB高校行走进哈尔滨，与两校学子共话数据库人才发展

华为开发者空间

cover

GaussDB数据库高可用部署方案

华为开发者空间

所有评论(0)

查看更多评论

AGi_

@weixin_46899412

已为社区贡献5条内容