easypoi excel模板导出pdf

实现效果

下载免费版spire 用于excel转pdf

spire官方maven仓库或者官网下载
找到仓库目录，选择spire.xls.free-3.9.1.jar完成下载

下载完成以后添加到springboot的resources下，同时准备的excel模板文件（提取码zZzZ）也添加到这里。

gradle添加依赖

dependencies {
    //easypoi
    implementation group: 'cn.afterturn', name: 'easypoi-spring-boot-starter', version: '4.2.0'
    //spire
    compile files('src/main/resources/lib/spire.xls.free-3.9.1.jar')
}

编写代码

实体类

package org.example.base.entity.muskmelon;

import lombok.*;
import org.example.base.entity.BaseEntity;

import javax.persistence.*;
import java.util.List;

/**
 * @author l
 * @date Created in 2021/4/22 18:08
 * school
 */
@NoArgsConstructor
@Entity
@Getter
@Setter
@Table(name = "school_form")
@Builder(toBuilder = true)
@AllArgsConstructor
@ToString
public class SchoolForm extends BaseEntity {



    /**
     * @date 18:13 2021/4/22
     * 学期
     **/
    private String semester;

    /**
     * @date 18:13 2021/4/22
     * 学期ID
     **/
    private String semesterId;

    /**
     * @date 16:00 2021/4/23
     * 校长
     **/
    private String principal;

    /**
     * @date 16:00 2021/4/23
     * 学校
     **/
    private String school;

    /**
     * @date 18:13 2021/4/22
     * 职工人数
     **/
    private int workTotal;

    /**
     * @date 18:13 2021/4/22
     * 总人数
     **/
    private int studentTotal;

    @OneToMany
    private List<TeacherDetailForm> teacherDetailForms;

}

package org.example.base.entity.muskmelon;

import lombok.*;
import org.example.base.entity.BaseEntity;

import javax.persistence.*;

/**
 * @author l
 * @date Created in 2021/5/7 11:24
 */
@NoArgsConstructor
@Entity
@Getter
@Setter
@Table(name = "teacher_detail_form")
@Builder(toBuilder = true)
@AllArgsConstructor
public class TeacherDetailForm extends BaseEntity {


    /**
     * @date 14:37 2021/5/7
     * 工号
     **/
    private String workId;


    /**
     * @date 11:26 2021/5/20
     * 教师
     **/
    private String teacher;

    /**
     * @date 14:38 2021/5/7
     * 工龄
     **/
    private String workDate;


    /**
     * @date 14:38 2021/5/7
     * 学科
     **/
    private Integer type;



}

package org.example.base.entity;

import lombok.Data;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * @author l
 * @date Created in 2021/1/14 15:50
 */
@MappedSuperclass
@Data
public class BaseEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @CreationTimestamp
    @Column(name = "create_time")
    private LocalDateTime createTime;
    @UpdateTimestamp
    @Column(name = "update_time")
    private LocalDateTime updateTime;


}

dao层

package org.example.base.dao;

import org.example.base.entity.muskmelon.SchoolForm;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author l
 * @date Created in 2021/4/23 15:47
 */
@Repository
public interface SchoolFormRepository extends JpaRepository<SchoolForm, Long>, JpaSpecificationExecutor<SchoolForm> {


   SchoolForm findBySemesterId(String semesterId);


}

实现

package org.example.base.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.util.PoiMergeCellUtil;
import com.spire.xls.FileFormat;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.example.base.dao.SchoolFormRepository;
import org.example.base.entity.muskmelon.SchoolForm;
import org.example.base.entity.muskmelon.TeacherDetailForm;
import org.example.base.vo.SchoolFormDto;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

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

/**
 * @author l
 * @date Created in 2021/5/8 18:19
 */
@Controller
@AllArgsConstructor
@RequestMapping("/download")
public class ExcelController {

    private final SchoolFormRepository schoolFormRepository;

    @PostMapping("/pdf")
    public void pdf(HttpServletResponse response, String id) throws Exception {
        SchoolForm form = schoolFormRepository.findBySemesterId(id);
        if (form == null) {
            return;
        }
        HashMap<String, Object> topMap = new HashMap<>(4);
        topMap.put("principal", form.getPrincipal());
        topMap.put("school", form.getSchool());
        topMap.put("workTotal", form.getWorkTotal());
        topMap.put("studentTotal", form.getStudentTotal());
        topMap.put("semester", form.getSemester());
        List<TeacherDetailForm> teachers = form.getTeacherDetailForms();
        ArrayList<Map<String, Object>> objects = new ArrayList<>();
        for (TeacherDetailForm s : teachers) {
            HashMap<String, Object> innerMap = new HashMap<>(5);
            innerMap.put("semester", form.getSemester());
            innerMap.put("teacher", s.getTeacher());
            innerMap.put("type", SchoolFormDto.getTypeName(s.getType()));
            innerMap.put("workId", s.getWorkId());
            innerMap.put("workDate", s.getWorkDate());
            objects.add(innerMap);
        }
        topMap.put("list", objects);
        TemplateExportParams params = new TemplateExportParams("excel/schoolform.xlsx");
        Workbook workbook = ExcelExportUtil.exportExcel(params, topMap);
        response.setContentType("application/pdf;charset=utf-8");
        //纵向合并相同内容的单元格
        PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), 3, 0);
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
        com.spire.xls.Workbook workbook1 = new com.spire.xls.Workbook();
        workbook1.loadFromStream(inputStream);
        //设置字体格式，linux服务器上，要在/usr/share/fonts/ 目录下检查该字体集是否存在
        //不存在要安装宋体，否者中文乱码
        Font font = new Font("宋体",Font.PLAIN,16);
        workbook1.createFont(font);
        //pdf 自适应屏幕大小
        workbook1.getConverterSetting().setSheetFitToWidth(true);
        workbook1.saveToStream(response.getOutputStream(), FileFormat.PDF);
    }

}

简单页面测试下载

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>资料下载</title>
</head>
<body>
<h1>HELLO WORLD!</h1>
<div style="margin-top: 40px" align="center">
    <form id="download" class="layui-hide" action="/tea/download/pdf?id=2021" method="post"></form>
    <button onclick="downLoad()">模板下载</button>
</div>
<script type="application/javascript">
    function downLoad(){
        let form = document.getElementById('download');
        form.submit();
    }
</script>
</body>
</html>