1. 云图网首页
  2. 技术专区
  3. 数据库

学到了_SpringBoot项目使用Poi将数据库中含有富文本的数据导出成Word文件

• 数据库


场景描述

用户填写表单的内容中有富文本信息，在导出时，希望富文本的标签生效，本来想偷懒不管标签，就导出成excel的，但作为有追求的开发者，还是希望可以实现这样的需求
看了一些模板引擎，比如说freemaker、easy-poi、poi-tl这些，浅看了一下发现都是类似于先占位再填值的操作，好像不太行
然后有前辈说可以用Poi来实现，其实就是手写Html

一顿操作

pom中添加依赖，由于我的数据是从数据库来的，所以加了数据库的一些依赖

<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>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--spring-data-jpa-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!--druid连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.23</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
        <dependency>
            <groupId>org.jsoup</groupId>
            <artifactId>jsoup</artifactId>
            <version>1.10.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>20.0</version>
        </dependency>

准备css文件（可以直接拿我的这个哟）

/*/
* Quill Editor v1.3.7
* https://quilljs.com/
* Copyright (c) 2014, Jason Chen
* Copyright (c) 2013, salesforce.com
*/
.ql-container {
box-sizing: border-box;
font-family: Helvetica, Arial, sans-serif;
font-size: 13px;
height: 100%;
margin: 0px;
position: relative;
}
.ql-container.ql-disabled .ql-tooltip {
visibility: hidden;
}
.ql-container.ql-disabled .ql-editor ul[data-checked] > li::before {
pointer-events: none;
}
.ql-clipboard {
left: -100000px;
height: 1px;
overflow-y: hidden;
position: absolute;
top: 50%;
}
.ql-clipboard p {
margin: 0;
padding: 0;
}
.ql-editor {
box-sizing: border-box;
line-height: 1.42;
height: 100%;
outline: none;
overflow-y: auto;
padding: 12px 15px;
tab-size: 4;
-moz-tab-size: 4;
text-align: left;
white-space: pre-wrap;
word-wrap: break-word;
}
.ql-editor > * {
cursor: text;
}
.ql-editor p,
.ql-editor ol,
.ql-editor ul,
.ql-editor pre,
.ql-editor blockquote,
.ql-editor h1,
.ql-editor h2,
.ql-editor h3,
.ql-editor h4,
.ql-editor h5,
.ql-editor h6 {
margin: 0;
padding: 0;
counter-reset: list-1 list-2 list-3 list-4 list-5 list-6 list-7 list-8 list-9;
}
.ql-editor ol,
.ql-editor ul {
padding-left: 1.5em;
}
.ql-editor ol > li,
.ql-editor ul > li {
list-style-type: none;
}
.ql-editor ul > li::before {
content: '/2022';
}
.ql-editor ul[data-checked=true],
.ql-editor ul[data-checked=false] {
pointer-events: none;
}
.ql-editor ul[data-checked=true] > li *,
.ql-editor ul[data-checked=false] > li * {
pointer-events: all;
}
.ql-editor ul[data-checked=true] > li::before,
.ql-editor ul[data-checked=false] > li::before {
color: #777;
cursor: pointer;
pointer-events: all;
}
.ql-editor ul[data-checked=true] > li::before {
content: '/2611';
}
.ql-editor ul[data-checked=false] > li::before {
content: '/2610';
}
.ql-editor li::before {
display: inline-block;
white-space: nowrap;
width: 1.2em;
}
.ql-editor li:not(.ql-direction-rtl)::before {
margin-left: -1.5em;
margin-right: 0.3em;
text-align: right;
}
.ql-editor li.ql-direction-rtl::before {
margin-left: 0.3em;
margin-right: -1.5em;
}
.ql-editor ol li:not(.ql-direction-rtl),
.ql-editor ul li:not(.ql-direction-rtl) {
padding-left: 1.5em;
}
.ql-editor ol li.ql-direction-rtl,
.ql-editor ul li.ql-direction-rtl {
padding-right: 1.5em;
}
.ql-editor ol li {
counter-reset: list-1 list-2 list-3 list-4 list-5 list-6 list-7 list-8 list-9;
counter-increment: list-0;
}
.ql-editor ol li:before {
content: counter(list-0, decimal) '. ';
}
.ql-editor ol li.ql-indent-1 {
counter-increment: list-1;
}
.ql-editor ol li.ql-indent-1:before {
content: counter(list-1, lower-alpha) '. ';
}
.ql-editor ol li.ql-indent-1 {
counter-reset: list-2 list-3 list-4 list-5 list-6 list-7 list-8 list-9;
}
.ql-editor ol li.ql-indent-2 {
counter-increment: list-2;
}
.ql-editor ol li.ql-indent-2:before {
content: counter(list-2, lower-roman) '. ';
}
.ql-editor ol li.ql-indent-2 {
counter-reset: list-3 list-4 list-5 list-6 list-7 list-8 list-9;
}
.ql-editor ol li.ql-indent-3 {
counter-increment: list-3;
}
.ql-editor ol li.ql-indent-3:before {
content: counter(list-3, decimal) '. ';
}
.ql-editor ol li.ql-indent-3 {
counter-reset: list-4 list-5 list-6 list-7 list-8 list-9;
}
.ql-editor ol li.ql-indent-4 {
counter-increment: list-4;
}
.ql-editor ol li.ql-indent-4:before {
content: counter(list-4, lower-alpha) '. ';
}
.ql-editor ol li.ql-indent-4 {
counter-reset: list-5 list-6 list-7 list-8 list-9;
}
.ql-editor ol li.ql-indent-5 {
counter-increment: list-5;
}
.ql-editor ol li.ql-indent-5:before {
content: counter(list-5, lower-roman) '. ';
}
.ql-editor ol li.ql-indent-5 {
counter-reset: list-6 list-7 list-8 list-9;
}
.ql-editor ol li.ql-indent-6 {
counter-increment: list-6;
}
.ql-editor ol li.ql-indent-6:before {
content: counter(list-6, decimal) '. ';
}
.ql-editor ol li.ql-indent-6 {
counter-reset: list-7 list-8 list-9;
}
.ql-editor ol li.ql-indent-7 {
counter-increment: list-7;
}
.ql-editor ol li.ql-indent-7:before {
content: counter(list-7, lower-alpha) '. ';
}
.ql-editor ol li.ql-indent-7 {
counter-reset: list-8 list-9;
}
.ql-editor ol li.ql-indent-8 {
counter-increment: list-8;
}
.ql-editor ol li.ql-indent-8:before {
content: counter(list-8, lower-roman) '. ';
}
.ql-editor ol li.ql-indent-8 {
counter-reset: list-9;
}
.ql-editor ol li.ql-indent-9 {
counter-increment: list-9;
}
.ql-editor ol li.ql-indent-9:before {
content: counter(list-9, decimal) '. ';
}
.ql-editor .ql-indent-1:not(.ql-direction-rtl) {
padding-left: 3em;
}
.ql-editor li.ql-indent-1:not(.ql-direction-rtl) {
padding-left: 4.5em;
}
.ql-editor .ql-indent-1.ql-direction-rtl.ql-align-right {
padding-right: 3em;
}
.ql-editor li.ql-indent-1.ql-direction-rtl.ql-align-right {
padding-right: 4.5em;
}
.ql-editor .ql-indent-2:not(.ql-direction-rtl) {
padding-left: 6em;
}
.ql-editor li.ql-indent-2:not(.ql-direction-rtl) {
padding-left: 7.5em;
}
.ql-editor .ql-indent-2.ql-direction-rtl.ql-align-right {
padding-right: 6em;
}
.ql-editor li.ql-indent-2.ql-direction-rtl.ql-align-right {
padding-right: 7.5em;
}
.ql-editor .ql-indent-3:not(.ql-direction-rtl) {
padding-left: 9em;
}
.ql-editor li.ql-indent-3:not(.ql-direction-rtl) {
padding-left: 10.5em;
}
.ql-editor .ql-indent-3.ql-direction-rtl.ql-align-right {
padding-right: 9em;
}
.ql-editor li.ql-indent-3.ql-direction-rtl.ql-align-right {
padding-right: 10.5em;
}
.ql-editor .ql-indent-4:not(.ql-direction-rtl) {
padding-left: 12em;
}
.ql-editor li.ql-indent-4:not(.ql-direction-rtl) {
padding-left: 13.5em;
}
.ql-editor .ql-indent-4.ql-direction-rtl.ql-align-right {
padding-right: 12em;
}
.ql-editor li.ql-indent-4.ql-direction-rtl.ql-align-right {
padding-right: 13.5em;
}
.ql-editor .ql-indent-5:not(.ql-direction-rtl) {
padding-left: 15em;
}
.ql-editor li.ql-indent-5:not(.ql-direction-rtl) {
padding-left: 16.5em;
}
.ql-editor .ql-indent-5.ql-direction-rtl.ql-align-right {
padding-right: 15em;
}
.ql-editor li.ql-indent-5.ql-direction-rtl.ql-align-right {
padding-right: 16.5em;
}
.ql-editor .ql-indent-6:not(.ql-direction-rtl) {
padding-left: 18em;
}
.ql-editor li.ql-indent-6:not(.ql-direction-rtl) {
padding-left: 19.5em;
}
.ql-editor .ql-indent-6.ql-direction-rtl.ql-align-right {
padding-right: 18em;
}
.ql-editor li.ql-indent-6.ql-direction-rtl.ql-align-right {
padding-right: 19.5em;
}
.ql-editor .ql-indent-7:not(.ql-direction-rtl) {
padding-left: 21em;
}
.ql-editor li.ql-indent-7:not(.ql-direction-rtl) {
padding-left: 22.5em;
}
.ql-editor .ql-indent-7.ql-direction-rtl.ql-align-right {
padding-right: 21em;
}
.ql-editor li.ql-indent-7.ql-direction-rtl.ql-align-right {
padding-right: 22.5em;
}
.ql-editor .ql-indent-8:not(.ql-direction-rtl) {
padding-left: 24em;
}
.ql-editor li.ql-indent-8:not(.ql-direction-rtl) {
padding-left: 25.5em;
}
.ql-editor .ql-indent-8.ql-direction-rtl.ql-align-right {
padding-right: 24em;
}
.ql-editor li.ql-indent-8.ql-direction-rtl.ql-align-right {
padding-right: 25.5em;
}
.ql-editor .ql-indent-9:not(.ql-direction-rtl) {
padding-left: 27em;
}
.ql-editor li.ql-indent-9:not(.ql-direction-rtl) {
padding-left: 28.5em;
}
.ql-editor .ql-indent-9.ql-direction-rtl.ql-align-right {
padding-right: 27em;
}
.ql-editor li.ql-indent-9.ql-direction-rtl.ql-align-right {
padding-right: 28.5em;
}
.ql-editor .ql-video {
display: block;
max-width: 100%;
}
.ql-editor .ql-video.ql-align-center {
margin: 0 auto;
}
.ql-editor .ql-video.ql-align-right {
margin: 0 0 0 auto;
}
.ql-editor .ql-bg-black {
background-color: #000;
}
.ql-editor .ql-bg-red {
background-color: #e60000;
}
.ql-editor .ql-bg-orange {
background-color: #f90;
}
.ql-editor .ql-bg-yellow {
background-color: #ff0;
}
.ql-editor .ql-bg-green {
background-color: #008a00;
}
.ql-editor .ql-bg-blue {
background-color: #06c;
}
.ql-editor .ql-bg-purple {
background-color: #93f;
}
.ql-editor .ql-color-white {
color: #fff;
}
.ql-editor .ql-color-red {
color: #e60000;
}
.ql-editor .ql-color-orange {
color: #f90;
}
.ql-editor .ql-color-yellow {
color: #ff0;
}
.ql-editor .ql-color-green {
color: #008a00;
}
.ql-editor .ql-color-blue {
color: #06c;
}
.ql-editor .ql-color-purple {
color: #93f;
}
.ql-editor .ql-font-serif {
font-family: Georgia, Times New Roman, serif;
}
.ql-editor .ql-font-monospace {
font-family: Monaco, Courier New, monospace;
}
.ql-editor .ql-size-small {
font-size: 0.75em;
}
.ql-editor .ql-size-large {
font-size: 1.5em;
}
.ql-editor .ql-size-huge {
font-size: 2.5em;
}
.ql-editor .ql-direction-rtl {
direction: rtl;
text-align: inherit;
}
.ql-editor .ql-align-center {
text-align: center;
}
.ql-editor .ql-align-justify {
text-align: justify;
}
.ql-editor .ql-align-right {
text-align: right;
}
.ql-editor.ql-blank::before {
color: rgba(0,0,0,0.6);
content: attr(data-placeholder);
font-style: italic;
left: 15px;
pointer-events: none;
position: absolute;
right: 15px;
}

css文件要放在工具类同包下的resources下
像这样：
学到了_SpringBoot项目使用Poi将数据库中含有富文本的数据导出成Word文件

工具类代码

exportHtmlToWord方法和exportToWord方法思路是一致的，只是exportToWord中有我需要的列表参数，同时我需要把文件导出到浏览器的输出流中，所以重写了一下exportHtmlToWord方法

package com.example.exportword.util;
import com.example.exportword.entity.MyzfwFileExchange;
import com.google.common.io.Resources;
import org.apache.poi.poifs.filesystem.DirectoryEntry;
import org.apache.poi.poifs.filesystem.DocumentEntry;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
public class WordUtil {
/**
* 导出富文本内容到word
* @param content 输出内容
* @param title 文本标题（注意不是文件标题，是文本标题）      
* @throws Exception
*/
public void exportHtmlToWord(String content, String title) throws Exception {
// 拼接html格式内容
StringBuffer sbf = new StringBuffer();
BufferedInputStream bufferedInputStream = (BufferedInputStream) Resources.getResource("css/quill.core.css").getContent();
byte[] bs = new byte[1024];
//        InputStream resourceAsStream = this.getClass().getResourceAsStream("resources/css/quill.bubble.css");
// 这里拼接一下html标签,便于word文档能够识别
sbf.append("<html " +
"xmlns:v=/"urn:schemas-microsoft-com:vml/" xmlns:o=/"urn:schemas-microsoft-com:office:office/" xmlns:w=/"urn:schemas-microsoft-com:office:word/" xmlns:m=/"http://schemas.microsoft.com/office/2004/12/omml/" xmlns=/"http://www.w3.org/TR/REC-html40/"" + //将版式从web版式改成页面试图
">");
sbf.append("<head>" +
"<!--[if gte mso 9]><xml><w:WordDocument><w:View>Print</w:View><w:TrackMoves>false</w:TrackMoves><w:TrackFormatting/><w:ValidateAgainstSchemas/><w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid><w:IgnoreMixedContent>false</w:IgnoreMixedContent><w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText><w:DoNotPromoteQF/><w:LidThemeOther>EN-US</w:LidThemeOther><w:LidThemeAsian>ZH-CN</w:LidThemeAsian><w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript><w:Compatibility><w:BreakWrappedTables/><w:SnapToGridInCell/><w:WrapTextWithPunct/><w:UseAsianBreakRules/><w:DontGrowAutofit/><w:SplitPgBreakAndParaMark/><w:DontVertAlignCellWithSp/><w:DontBreakConstrainedForcedTables/><w:DontVertAlignInTxbx/><w:Word11KerningPairs/><w:CachedColBalance/><w:UseFELayout/></w:Compatibility><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><m:mathPr><m:mathFont m:val=/"Cambria Math/"/><m:brkBin m:val=/"before/"/><m:brkBinSub m:val=/"--/"/><m:smallFrac m:val=/"off/"/><m:dispDef/><m:lMargin m:val=/"0/"/> <m:rMargin m:val=/"0/"/><m:defJc m:val=/"centerGroup/"/><m:wrapIndent m:val=/"1440/"/><m:intLim m:val=/"subSup/"/><m:naryLim m:val=/"undOvr/"/></m:mathPr></w:WordDocument></xml><![endif]-->" +
"");
sbf.append("<style>");
while (bufferedInputStream.read(bs) != -1) {
sbf.append(new String(bs));
}
sbf.append("</style></head>");
//        sbf.append("<style>").append().append("</style>");
sbf.append("<body><div class=/"ql-editor/">");
// 富文本内容
sbf.append("<h1 class=/"ql-align-center/">").append(title).append("</h1>");
sbf.append(content);
sbf.append("</div></body></html>");
// 必须要设置编码,避免中文就会乱码
byte[] b = sbf.toString().getBytes("GBK");
// 将字节数组包装到流中
ByteArrayInputStream bais = new ByteArrayInputStream(b);
POIFSFileSystem poifs = new POIFSFileSystem();
DirectoryEntry directory = poifs.getRoot();
// 这代码不能省略,否则导出乱码。
DocumentEntry documentEntry = directory.createDocument("WordDocument", bais);
File file = new File("E://test//123.doc");
FileOutputStream fileOutputStream = new FileOutputStream(file);
poifs.writeFilesystem(fileOutputStream);
bais.close();
fileOutputStream.close();
}
public void exportToWord(List<MyzfwFileExchange> myzfwFileExchanges,String title, HttpServletRequest request, HttpServletResponse response) throws IOException {
// 拼接html格式内容
StringBuffer sbf = new StringBuffer();
byte[] bs = new byte[1024];
//获取css文件
BufferedInputStream bufferedInputStream = (BufferedInputStream) Resources.getResource("css/quill.core.css").getContent();
//        InputStream resourceAsStream = this.getClass().getResourceAsStream("resources/css/quill.bubble.css");
// 这里拼接一下html标签,便于word文档能够识别
sbf.append("<html " +
"xmlns:v=/"urn:schemas-microsoft-com:vml/" xmlns:o=/"urn:schemas-microsoft-com:office:office/" xmlns:w=/"urn:schemas-microsoft-com:office:word/" xmlns:m=/"http://schemas.microsoft.com/office/2004/12/omml/" xmlns=/"http://www.w3.org/TR/REC-html40/"" + //将版式从web版式改成页面试图
">");
sbf.append("<head>" +
"<!--[if gte mso 9]><xml><w:WordDocument><w:View>Print</w:View><w:TrackMoves>false</w:TrackMoves><w:TrackFormatting/><w:ValidateAgainstSchemas/><w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid><w:IgnoreMixedContent>false</w:IgnoreMixedContent><w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText><w:DoNotPromoteQF/><w:LidThemeOther>EN-US</w:LidThemeOther><w:LidThemeAsian>ZH-CN</w:LidThemeAsian><w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript><w:Compatibility><w:BreakWrappedTables/><w:SnapToGridInCell/><w:WrapTextWithPunct/><w:UseAsianBreakRules/><w:DontGrowAutofit/><w:SplitPgBreakAndParaMark/><w:DontVertAlignCellWithSp/><w:DontBreakConstrainedForcedTables/><w:DontVertAlignInTxbx/><w:Word11KerningPairs/><w:CachedColBalance/><w:UseFELayout/></w:Compatibility><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><m:mathPr><m:mathFont m:val=/"Cambria Math/"/><m:brkBin m:val=/"before/"/><m:brkBinSub m:val=/"--/"/><m:smallFrac m:val=/"off/"/><m:dispDef/><m:lMargin m:val=/"0/"/> <m:rMargin m:val=/"0/"/><m:defJc m:val=/"centerGroup/"/><m:wrapIndent m:val=/"1440/"/><m:intLim m:val=/"subSup/"/><m:naryLim m:val=/"undOvr/"/></m:mathPr></w:WordDocument></xml><![endif]-->" +
"");
sbf.append("<style>");
while (bufferedInputStream.read(bs) != -1) {
sbf.append(new String(bs));
}
sbf.append("</style></head>");
//        sbf.append("<style>").append().append("</style>");
sbf.append("<body><div class=/"ql-editor/">");
// 列表内容
sbf.append("<h1 class=/"ql-align-center/">").append(title).append("</h1>");
for (int i = 0; i < myzfwFileExchanges.size(); i++) {
MyzfwFileExchange data = myzfwFileExchanges.get(i);
sbf.append("<br><br><h2>").append("第").append(i).append("条数据").append("</h2>");
sbf.append("<p>").append("标题:").append(data.getTitle());
sbf.append("<br>文件类型:").append(data.getType());
sbf.append("<br>共享时间:").append(data.getSharedTime());
sbf.append("<br>截止时间:").append(data.getExpireTime());
sbf.append("<br>正文内容：").append(data.getContent());
sbf.append("</p>");
}
sbf.append("</div></body></html>");
// 必须要设置编码,避免中文就会乱码
byte[] b = sbf.toString().getBytes("GBK");
// 将字节数组包装到流中
ByteArrayInputStream bais = new ByteArrayInputStream(b);
POIFSFileSystem poifs = new POIFSFileSystem();
DirectoryEntry dir = poifs.getRoot();
dir.createDocument("WordDocument", bais);
request.setCharacterEncoding("utf-8");
response.setContentType("application/msword");
response.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes("GB2312"), "iso8859-1") + ".doc");
ServletOutputStream os = response.getOutputStream();
poifs.writeFilesystem(os);
bais.close();
os.close();
poifs.close();
//如果需要输出成文件的话，就不要写在response的输出流里了，打开下面的注释，直接写成本地文件
//        File file = new File("E://test//123.doc");
//        FileOutputStream fileOutputStream = new FileOutputStream(file);
//        poifs.writeFilesystem(fileOutputStream);
//        bais.close();
//        fileOutputStream.close();
}
}

最后正常导出的文件像是这样:
可以看到html标签是生效的，因为字体变成红色了
学到了_SpringBoot项目使用Poi将数据库中含有富文本的数据导出成Word文件

又是学到了的一天，祝你快乐！

原创文章，作者：306829225，如若转载，请注明出处：https://blog.ytso.com/267249.html

赞 (0)
0
上一篇 2022年6月14日
下一篇 2022年6月14日

相关推荐

发表回复

登录后才能评论