SpringBoot基础-word导出

引言

在我们做项目的时候会需要把数据库中的数据导出到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项目 

导入依赖

1.8UTF-8UTF-82.3.7.RELEASE2.51.3.34.1.2org.apache.poipoi-ooxml4.1.2commons-iocommons-io${commons.io.version}commons-fileuploadcommons-fileupload${commons.fileupload.version}org.springframework.bootspring-boot-starter-freemarkerorg.springframework.bootspring-boot-starter-jdbcorg.springframework.bootspring-boot-starter-thymeleaforg.springframework.bootspring-boot-starter-webcom.alibabafastjson1.2.72com.baomidoumybatis-plus-boot-starter3.4.2org.mybatis.spring.bootmybatis-spring-boot-starter2.1.4org.springframework.bootspring-boot-devtoolsruntimetruemysqlmysql-connector-javaruntimeorg.projectlomboklomboktrueorg.springframework.bootspring-boot-starter-testtestorg.junit.vintagejunit-vintage-engineorg.springframework.bootspring-boot-starter-logging            **test     ch.qos.logbacklogback-classic

配置application.yml

mybatis:mapper-locations: classpath:/mybatis/**/*.xmltype-aliases-package: com.example.note.domainmap-underscore-to-camel-case: true
mybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpltypeAliasesPackage: com.example.note.domain  #存放实体类的目录路径mapperLocations: classpath:mybatis/**/*.xml# 全局配置id自增  =>global-config:db-config:id-type: autoserver:port: 8080
spring:application:name: note-backdatasource:driver-class-name: com.mysql.cj.jdbc.Drivername: defaultDataSourcepassword: '123456789'url: jdbc:mysql://localhost:3306/note?serverTimezone=UTCusername: 'root'freemarker:cache: falsecharset: utf-8expose-request-attributes: trueexpose-session-attributes: truesuffix: .ftltemplate-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 getStudentsScoreListByStudentId(@Param("studentId")Long studentId);
}

创建StudentWordMapper.xml

select * from student_word where id=#{id}select * from score where student_word_id=#{studentId}

创建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 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 {@Autowiredprivate StudentWordMapper studentWordMapper;@Overridepublic Student getStudentById(Long id) {return this.studentWordMapper.getStudentById(id);}@Overridepublic List 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 {@Autowiredprivate 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 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 scoreList = this.studentWordService.getStudentsScoreListByStudentId(id);//非总分List> mapList = new ArrayList<>();for (Score score : scoreList) {Map 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);//导出wordtemplate.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();// 清空responseresponse.reset();// 设置response的Headerresponse.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;}//图片转BASE64Encoderpublic 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公开仓库

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！