springboot 生成各个版本excel和pdf以及遇到的问题

发表于

springboot 生成各个版本excel和pdf以及遇到的问题

springboot生成excel和pdf网上的资料并不是很多,大多数是springmvc生成excel和pdf的例子.或者是使用Response输出流直接去写,其实这里要介绍的是spring框架已经为我们考虑好了.了解springmvc组件结构的肯定知道view视图解析器组件,这里主要介绍的就是这个组件的实际应用并附上源代码以及使用过程中遇到的坑.
主要介绍的组件:

1. AbstractXlsView 旧版excel下载
2. AbstractXlsxView 新版excel下载
3. AbstractXlsxStreamingView excel大文件下载
4. AbstractPdfView pdf下载
4. PdfStreamingView pdf模板文件下载

maven依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!-- pdf start -->
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itextpdf</artifactId>
    <version>5.5.11</version>
</dependency>
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itext-asian</artifactId>
    <version>5.2.0</version>
</dependency>
<!-- pdf end -->
<!-- excel start-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.8</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.8</version>
</dependency>
<!-- excel end -->

旧版Excel(AbstractXlsView) - XlsView.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.data2_0.view;
import com.data2_0.utils.ExcelReportUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.servlet.view.document.AbstractXlsView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;
/**
 * excel视图解析器
 * Created by xiehui1956(@)gmail.com on 17-4-6.
 */
public class XlsView extends AbstractXlsView {
    @Override
    protected void buildExcelDocument(Map<String, Object> model
            , Workbook workbook
            , HttpServletRequest request, HttpServletResponse response) throws Exception {
        // change the file name
        response.setHeader("Content-Disposition", "attachment; filename=" + model.get("fileName") + ".xls");
        List<Map<String, Object>> content = (List<Map<String, Object>>) model.get("content");
        List<String> header = (List<String>) model.get("header");
        ExcelReportUtils.newInstance().reportExcel(workbook, header, content);
    }
}

新版Excel(AbstractXlsxView) - XlsxView.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.data2_0.view;
import com.data2_0.utils.ExcelReportUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.servlet.view.document.AbstractXlsxView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;
/**
 * Created by xiehui1956(@)gmail.com on 17-4-6.
 */
public class XlsxView extends AbstractXlsxView {
    @Override
    protected void buildExcelDocument(Map<String, Object> model
            , Workbook workbook
            , HttpServletRequest request, HttpServletResponse response) throws Exception {
        // change the file name
        response.setHeader("Content-Disposition", "attachment; filename=" + model.get("fileName") + ".xlsx");
        List<Map<String, Object>> content = (List<Map<String, Object>>) model.get("content");
        List<String> header = (List<String>) model.get("header");
        ExcelReportUtils.newInstance().reportExcel(workbook, header, content);
    }
}

大文件Excel(AbstractXlsxStreamingView) - XlsxStreamingView.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.data2_0.view;
import com.data2_0.utils.ExcelReportUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.servlet.view.document.AbstractXlsxStreamingView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;
/**
 * Created by xiehui1956(@)gmail.com on 17-4-6.
 */
public class XlsxStreamingView extends AbstractXlsxStreamingView {
    @Override
    protected void buildExcelDocument(Map<String, Object> model
            , Workbook workbook
            , HttpServletRequest request, HttpServletResponse response) throws Exception {
        // change the file name
        response.setHeader("Content-Disposition", "attachment; filename=" + model.get("fileName") + ".xlsx");
        List<Map<String, Object>> content = (List<Map<String, Object>>) model.get("content");
        List<String> header = (List<String>) model.get("header");
        ExcelReportUtils.newInstance().reportExcel(workbook, header, content);
    }
}

普通pdf下载

因为springboot中的视图解析器AbstractPdfView使用的是lowagie,这个东西对中文的支持有问题。但是视图解析器是封装好的,所以需要重构这个类。AbstractIText5PdfView.java这个类和原springboot封装的一样可以拷贝过来,调整下引用类(itextpdf)就行了.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package com.data2_0.view;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.pdf.PdfWriter;
import org.springframework.web.servlet.view.AbstractView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.util.Map;
/**
 * Created by xiehui1956(@)gmail.com on 17-4-14.
 */
public abstract class AbstractIText5PdfView extends AbstractView {
    public AbstractIText5PdfView() {
        setContentType("application/pdf");
    }
    @Override
    protected boolean generatesDownloadContent() {
        return true;
    }
    @Override
    protected final void renderMergedOutputModel(
            Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        // IE workaround: write into byte array first.
        ByteArrayOutputStream baos = createTemporaryOutputStream();
        // Apply preferences and build metadata.
        Document document = newDocument();
        PdfWriter writer = newWriter(document, baos);
        prepareWriter(model, writer, request);
        buildPdfMetadata(model, document, request);
        // Build PDF document.
        document.open();
        buildPdfDocument(model, document, writer, request, response);
        document.close();
        // Flush to HTTP response.
        writeToResponse(response, baos);
    }
    protected Document newDocument() {
        return new Document(PageSize.A4);
    }
    protected PdfWriter newWriter(Document document, OutputStream os) throws DocumentException {
        return PdfWriter.getInstance(document, os);
    }
    protected void prepareWriter(Map<String, Object> model, PdfWriter writer, HttpServletRequest request)
            throws DocumentException {
        writer.setViewerPreferences(getViewerPreferences());
    }
    protected int getViewerPreferences() {
        return PdfWriter.ALLOW_PRINTING | PdfWriter.PageLayoutSinglePage;
    }
    protected void buildPdfMetadata(Map<String, Object> model, Document document, HttpServletRequest request) {
    }
    protected abstract void buildPdfDocument(Map<String, Object> model, Document document, PdfWriter writer,
                                             HttpServletRequest request, HttpServletResponse response) throws Exception;
}

Pdf生成工具类PDFUtil.java 这个是在网上找的一个哥们写的感觉不错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.data2_0.utils;
import com.itextpdf.text.Chunk;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Font;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.BaseFont;
import java.io.IOException;
public class PDFUtil {
    // 基本字体和样式  
    public static BaseFont bfChinese;
    public static Font fontChinese;
    public static Font UNDER_LINE = null;
    static {
        try {
            // SIMKAI.TTF 默认系统语言，这里没使用第三方语言包
            bfChinese = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
            fontChinese = new Font(bfChinese, 14, Font.NORMAL);
            UNDER_LINE = new Font(bfChinese, 14, Font.UNDERLINE);
        } catch (DocumentException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    // 默认样式  
    public static Paragraph getParagraph(String context) {
        return getParagraph(context, fontChinese);
    }
    public static Paragraph getParagraph(Chunk chunk) {
        return new Paragraph(chunk);
    }
    // 指定字体样式  
    public static Paragraph getParagraph(String context, Font font) {
        return new Paragraph(context, font);
    }
}

真正业务处理的视图解析器PdfView.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
package com.data2_0.view;
import com.data2_0.utils.PDFUtil;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import org.apache.commons.lang3.StringUtils;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
 * Created by xiehui1956(@)gmail.com on 17-4-6.
 */
public class PdfView extends AbstractIText5PdfView {
    // 敬语
    public static final String honorific_content = "亲爱的朋友们,下面是你们要的数据：";
    @Override
    protected void buildPdfDocument(Map<String, Object> model
            , Document document
            , PdfWriter writer, HttpServletRequest request, HttpServletResponse response) throws Exception {
        response.setHeader("Content-disposition", "attachment; fileName=" + model.get("fileName") + ".pdf");
        List<Map<String, Object>> content = (List<Map<String, Object>>) model.get("content");
        List<String> headerList = (List<String>) model.get("header");
        document.open();
        // 默认A4 大小
        document.setPageSize(PageSize.A4);
        Paragraph title = PDFUtil.getParagraph(new Chunk(String.valueOf(model.get("fileName")), new Font(PDFUtil.bfChinese, 14, Font.BOLD)));
        title.setAlignment(Paragraph.ALIGN_CENTER);
        //中文 黑体
        document.add(title);
        Paragraph head = PDFUtil.getParagraph(honorific_content);
        head.setSpacingBefore(20);
        head.setSpacingAfter(10);
        document.add(head);
        // 表格标题
        PdfPTable table = new PdfPTable(headerList.size());
        table.setSpacingBefore(20);
        table.setSpacingAfter(30);
        for (String cellTitle : headerList) {
            table.addCell(PDFUtil.getParagraph(cellTitle));
        }
        //表格数据
        for (Iterator<Map<String, Object>> iterator = content.iterator(); iterator.hasNext(); ) {
            Map<String, Object> next = iterator.next();
            for (Map.Entry<String, Object> entry : next.entrySet()) {
                table.addCell(StringUtils.isBlank(String.valueOf(entry.getValue())) ? "" : String.valueOf(entry.getValue()));
            }
        }
        document.add(table);
        document.close();
    }
}

模板pdf下载

生成插件的中文支持和普通pdf生成类似,这里不再描述.
AbstractPdf5StamperView.java拷贝AbstractPdfStamperView.java调整类引用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package com.data2_0.view;
import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.PdfStamper;
import org.springframework.web.servlet.view.AbstractUrlBasedView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Map;
/**
 * Created by xiehui1956(@)gmail.com on 17-4-14.
 */
public abstract class AbstractPdf5StamperView extends AbstractUrlBasedView {
    public AbstractPdf5StamperView() {
        setContentType("application/pdf");
    }
    @Override
    protected boolean generatesDownloadContent() {
        return true;
    }
    @Override
    protected final void renderMergedOutputModel(
            Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        // IE workaround: write into byte array first.
        ByteArrayOutputStream baos = createTemporaryOutputStream();
        PdfReader reader = readPdfResource();
        PdfStamper stamper = new PdfStamper(reader, baos);
        mergePdfDocument(model, stamper, request, response);
        stamper.close();
        // Flush to HTTP response.
        writeToResponse(response, baos);
    }
    protected PdfReader readPdfResource() throws IOException {
        return new PdfReader(getApplicationContext().getResource(getUrl()).getInputStream());
    }
    protected abstract void mergePdfDocument(Map<String, Object> model, PdfStamper stamper,
                                             HttpServletRequest request, HttpServletResponse response) throws Exception;
}

真正处理业务逻辑的视图解析器PdfStreamingView.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.data2_0.view;
import com.itextpdf.text.pdf.AcroFields;
import com.itextpdf.text.pdf.PdfStamper;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Map;
/**
 * pdf模板生成
 * Created by xiehui1956(@)gmail.com on 17-4-6.
 */
public class PdfStreamingView extends AbstractPdf5StamperView {
    @Override
    protected void mergePdfDocument(Map<String, Object> model
            , PdfStamper stamper
            , HttpServletRequest request, HttpServletResponse response) throws Exception {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=" + model.get("fileName").toString() + ".pdf");
        AcroFields fields = stamper.getAcroFields();
        Map<String, String> data = (Map<String, String>) model.get("userInfo");
        for (String key : data.keySet()) {
            String value = data.get(key);
            fields.setField(key, value);
        }
        stamper.setFormFlattening(true);
    }
    
}

这里需要说明的是,视图解析器需要指定模板文件url.生成pdf模板文件的过程可以参考其他文章,将模板变量名和对应fields中的key对应着value就可以赋值上了。坑在与我使用的是jetty服务,但是jetty的文件目录在系统的/tmp目录下这个目录下是找不到模板文件的.下面会说道这个问题.

Bean注册

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Bean
public PdfView pdfView() {
    return new PdfView();
}
@Bean
public PdfStreamingView pdfStreamingView() {
    PdfStreamingView pdfStreamingView = new PdfStreamingView();
    pdfStreamingView.setUrl("template.pdf");
    return pdfStreamingView;
}
@Bean
public XlsView xlsView() {
    return new XlsView();
}
@Bean
public XlsxView xlsxView() {
    return new XlsxView();
}
@Bean
public XlsxStreamingView xlsxStreamingView() {
    return new XlsxStreamingView();
}

这里要说的是PdfStreamingView,刚说了这里需要指定模板路径。

下载Controller逻辑

DownloadController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package com.data2_0.controller;
import com.data2_0.annotation.GuestAction;
import com.data2_0.service.ReportRenderService;
import com.data2_0.utils.DateUtil;
import com.data2_0.view.PdfView;
import com.data2_0.view.XlsView;
import com.data2_0.view.XlsxStreamingView;
import com.data2_0.view.XlsxView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
 * Created by xiehui1956(@)gmail.com on 17-4-6.
 */
@Controller
@RequestMapping("/api")
public class DownloadController {
    @Autowired
    private ReportRenderService reportRenderService;
    @Autowired
    private XlsView xlsView;
    @Autowired
    private XlsxView xlsxView;
    @Autowired
    private XlsxStreamingView xlsxStreamingView;
    @Autowired
    private PdfView pdfView;
    /**
     * 下载
     *
     * @param model
     */
    @GuestAction
    @RequestMapping(value = "/download", method = RequestMethod.GET)
    public ModelAndView getDocument(Model model
            , @RequestParam Long menuId
            , @RequestParam(required = false, defaultValue = "") String type
            , @RequestParam @RequestBody String filterParam
            , @RequestParam String menuName) throws Exception {
        List<String> header = reportRenderService.queryDataFieldNameByConnId(menuId);
        List<Map<String, Object>> content = reportRenderService.findAllDataByMenuId(menuId, filterParam);
        // 报表名字+日期
        model.addAttribute("fileName", String.format("%s-%s", menuName, DateUtil.getDateFormatter(new Date(), "yyyy-MM-dd  HH:mm:ss")));
        model.addAttribute("header", header);
        model.addAttribute("content", content);
        switch (type) {
            case "xls":
                return new ModelAndView(xlsView);
            case "xlsx":
                return new ModelAndView(xlsxView);
            // 超级大的文件用这个
            case "xlsxs":
                return new ModelAndView(xlsxStreamingView);
            case "pdf":
                return new ModelAndView(pdfView);
            default:
                return new ModelAndView(xlsView);
        }
    }
}

pdf模板控制层ReportController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.data2_0.controller;
import com.data2_0.view.PdfStreamingView;
import com.google.common.collect.ImmutableMap;
import org.eclipse.jetty.server.Request;
import org.eclipse.jetty.server.handler.ContextHandler;
import org.eclipse.jetty.util.resource.PathResource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException;
import static javax.script.ScriptEngine.FILENAME;
/**
 * pdf模板生成
 * Created by xiehui1956(@)gmail.com on 17-4-12.
 */
@Controller
@RequestMapping("/api")
public class ReportController {
    private static final Logger LOGGER = LoggerFactory.getLogger(ReportController.class);
    @Autowired
    private PdfStreamingView pdfStreamingView;
    @RequestMapping("/pdf")
    public ModelAndView internal(Model model, HttpServletRequest request) throws IOException {
        ContextHandler.Context context = ((Request) request).getContext();
        ContextHandler contextHandler = context.getContextHandler();
        contextHandler.setBaseResource(new PathResource(new File("/home/bls/workspace/work/project/data.api2.0/src/main/resources/")));
        model.addAttribute("userInfo", ImmutableMap.of("name", "张三", "age", 20));
        model.addAttribute(FILENAME, "用户信息");
        return new ModelAndView(pdfStreamingView);
    }
}

这里需要注意下文件路径,resource路径设定为模板文件的路径.建议将该配置放到properties文件中管理,方便操作.
以上.