• 工具类实现导出复杂excel、word

    1、加入准备的工具类

    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3.  
    4.  
    5. import java.util.Map;
    6.  
    7. public interface TemplateRenderer {
    8.  
    9.     Writable render(Map dataSource) throws Throwable;
    10.  
    11. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3. import java.util.Map;
    4.  
    5. public interface ExportedFileNameFactory {
    6.  
    7.     String getName(Map dataSource);
    8.  
    9. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3. import java.io.IOException;
    4. import java.io.OutputStream;
    5.  
    6. public interface Writable {
    7.  
    8.     void write(OutputStream outputStream) throws IOException;
    9.  
    10. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3.  
    4. import org.slf4j.Logger;
    5. import org.slf4j.LoggerFactory;
    6.  
    7. import java.io.File;
    8. import java.io.FileOutputStream;
    9. import java.util.Map;
    10.  
    11. public abstract class AbsExporter implements TemplateRenderer {
    12.  
    13.     private static final Logger LOGGER = LoggerFactory.getLogger(AbsExporter.class);
    14.  
    15.     public void doExport(Map dataSource, File exportedFile) throws Throwable {
    16.         try(FileOutputStream fos = new FileOutputStream(exportedFile)) {
    17.             Writable writable = this.render(dataSource);
    18.             writable.write(fos);
    19.         }
    20.     }
    21.  
    22.     public abstract String getTargetFileSuffix();
    23.  
    24.     public void afterExport() {}
    25. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3.  
    4. import org.slf4j.Logger;
    5. import org.slf4j.LoggerFactory;
    6.  
    7. import java.io.BufferedOutputStream;
    8. import java.io.File;
    9. import java.io.FileOutputStream;
    10. import java.util.ArrayList;
    11. import java.util.List;
    12. import java.util.Map;
    13. import java.util.UUID;
    14. import java.util.zip.ZipOutputStream;
    15.  
    16. public class ExportProcess {
    17.  
    18.     private static final Logger LOGGER = LoggerFactory.getLogger(ExportProcess.class);
    19.  
    20.     /**
    21.      * 要导出的数据源
    22.      */
    23.     private List> dataSourceList = new ArrayList<>();
    24.  
    25.     /**
    26.      * 是否为多文件导出
    27.      */
    28.     private boolean multiFile;
    29.  
    30.     /**
    31.      * 导出器
    32.      */
    33.     private AbsExporter exporter;
    34.  
    35.     /**
    36.      * 导出文件名
    37.      */
    38.     private String exportedFilename;
    39.  
    40.     /**
    41.      * 导出为多文件时的文件名命名工厂
    42.      */
    43.     private ExportedFileNameFactory nameFactory;
    44.  
    45.     private ExportProcess(Map dataSource, AbsExporter exporter, String exportedFilename) {
    46.         this.dataSourceList.add(dataSource);
    47.         this.multiFile = false;
    48.         this.exporter = exporter;
    49.         this.exportedFilename = exportedFilename;
    50.     }
    51.  
    52.     private ExportProcess(List> dataSourceList, AbsExporter exporter, String exportedFilename, ExportedFileNameFactory nameFactory) {
    53.         this.dataSourceList.addAll(dataSourceList);
    54.         this.multiFile = true;
    55.         this.exporter = exporter;
    56.         this.exportedFilename = exportedFilename;
    57.         this.nameFactory = nameFactory;
    58.     }
    59.  
    60.     public static ExportProcess newProcess(Map dataSource, AbsExporter exporter, String exportedFilename) {
    61.         return new ExportProcess(dataSource, exporter, exportedFilename);
    62.     }
    63.  
    64.     public static ExportProcess newProcess(List> dataSourceList, AbsExporter exporter, String exportedFilename, ExportedFileNameFactory nameFactory) {
    65.         return new ExportProcess(dataSourceList, exporter, exportedFilename, nameFactory);
    66.     }
    67.  
    68.     public ExportResult export() {
    69.         ExportResult exportResult = new ExportResult(this.multiFile ? exportAsZipFile() : exportAsSingleFile());
    70.         this.exporter.afterExport();
    71.         return exportResult;
    72.     }
    73.  
    74.     /**
    75.      * 导出为单文件
    76.      * @return 导出结果
    77.      */
    78.     private File exportAsSingleFile() {
    79.         Map dataSource = this.dataSourceList.get(0);
    80.         // 导出文件所在目录路径
    81.         String exportedFileDirPath = FileUtils.filePathJoin(FileUtils.TEMP_FILE_PATH, "exportedFileDir" + UUID.randomUUID().toString());
    82.         // 创建导出文件所在目录
    83.         File exportedFileDir = FileUtils.createDir(exportedFileDirPath);
    84.         String exportedFilePath = FileUtils.filePathJoin(exportedFileDirPath, this.exportedFilename + this.exporter.getTargetFileSuffix());
    85.         File exportedFile = new File(exportedFilePath);
    86.         try {
    87.             this.exporter.doExport(dataSource, exportedFile);
    88.             return exportedFile;
    89.         } catch (Throwable t) {
    90.             LOGGER.error(t.getMessage(), t);
    91.             FileUtils.deleteDir(exportedFileDir);
    92.         }
    93.         return null;
    94.     }
    95.  
    96.     /**
    97.      * 导出为压缩文件
    98.      * @return 导出结果
    99.      */
    100.     private File exportAsZipFile() {
    101.         String tempFileDirPath = FileUtils.filePathJoin(FileUtils.TEMP_FILE_PATH, "tempFile" + UUID.randomUUID().toString());
    102.         File tempFileDir = FileUtils.createDir(tempFileDirPath);
    103.         // 导出文件所在目录路径
    104.         String exportedFileDirPath = FileUtils.filePathJoin(FileUtils.TEMP_FILE_PATH, "exportedFileDir" + UUID.randomUUID().toString());
    105.         // 创建导出文件所在目录
    106.         File exportedFileDir = FileUtils.createDir(exportedFileDirPath);
    107.         File exportedFile = new File(FileUtils.filePathJoin(exportedFileDirPath, this.exportedFilename + ".zip"));
    108.         try {
    109.             for (Map dataSource : this.dataSourceList) {
    110.                 this.exporter.doExport(dataSource, new File(FileUtils.filePathJoin(tempFileDirPath, this.nameFactory.getName(dataSource) + this.exporter.getTargetFileSuffix())));
    111.             }
    112.             try (ZipOutputStream out = new ZipOutputStream(new FileOutputStream(exportedFile));
    113.                  BufferedOutputStream bos = new BufferedOutputStream(out)) {
    114.                 FileUtils.zipDir(tempFileDirPath, out, bos);
    115.             }
    116.             return exportedFile;
    117.         } catch (Throwable t) {
    118.             LOGGER.error(t.getMessage(), t);
    119.             FileUtils.deleteDir(exportedFileDir);
    120.         } finally {
    121.             FileUtils.deleteDir(tempFileDir);
    122.         }
    123.         return null;
    124.     }
    125. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3.  
    4. import eu.bitwalker.useragentutils.Browser;
    5. import eu.bitwalker.useragentutils.UserAgent;
    6. import org.apache.commons.lang3.StringUtils;
    7. import org.slf4j.Logger;
    8. import org.slf4j.LoggerFactory;
    9.  
    10. import javax.servlet.http.HttpServletRequest;
    11. import javax.servlet.http.HttpServletResponse;
    12. import java.io.*;
    13. import java.net.URLEncoder;
    14.  
    15. public class ExportResult {
    16.  
    17.     private static final Logger LOGGER = LoggerFactory.getLogger(ExportResult.class);
    18.  
    19.     private File exportedFile;
    20.  
    21.     ExportResult(File exportedFile) {
    22.         this.exportedFile = exportedFile;
    23.     }
    24.  
    25.     public File getExportedFile() {
    26.         if (null == this.exportedFile) {
    27.             throw new NullPointerException("exportedFile 为 null");
    28.         }
    29.         return exportedFile;
    30.     }
    31.  
    32.     public void download(HttpServletRequest request, HttpServletResponse response) {
    33.         File exportedFile = getExportedFile();
    34.         // 用于清除首部的空白行
    35.         response.reset();
    36.         response.setContentType("application/x-download; charset=utf-8");
    37.         setFileDownloadHeader(request, response, this.exportedFile.getName());
    38.         doDownload(response, exportedFile);
    39.     }
    40.  
    41.     private void setFileDownloadHeader(HttpServletRequest request, HttpServletResponse response, String filename) {
    42.         //获取浏览器信息
    43.         String ua = request.getHeader("USER-AGENT");
    44.         //转成UserAgent对象
    45.         UserAgent userAgent = UserAgent.parseUserAgentString(ua);
    46.         //获取浏览器信息
    47.         Browser browser = userAgent.getBrowser();
    48.         //浏览器名称
    49.         String browserName = browser.getName();
    50.         String encodedFilename;
    51.         try {
    52.             encodedFilename = URLEncoder.encode(filename, "UTF8");
    53.             if (StringUtils.contains(browserName, "Internet Explorer")) {
    54.                 response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFilename + "\"");
    55.             } else if (StringUtils.contains(browserName, "Chrome") || StringUtils.contains(browserName, "Firefox")) {
    56.                 response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFilename);
    57.             } else {// 其他浏览器
    58.                 response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFilename + "\"");
    59.             }
    60.         } catch (UnsupportedEncodingException e) {
    61.             LOGGER.error(e.getMessage(), e);
    62.         }
    63.     }
    64.  
    65.     private void doDownload(HttpServletResponse response, File exportedFile) {
    66.         OutputStream os = null;
    67.         byte[] buffer = new byte[1024];
    68.         BufferedInputStream bis = null;
    69.         FileInputStream exportedFileInputStream = null;
    70.         try {
    71.             exportedFileInputStream = new FileInputStream(exportedFile);
    72.             response.addHeader("content-length", exportedFileInputStream.available() + "");
    73.             os = response.getOutputStream();
    74.             bis = new BufferedInputStream(exportedFileInputStream);
    75.             int i = bis.read(buffer);
    76.             while (i != -1) {
    77.                 os.write(buffer, 0, i);
    78.                 i = bis.read(buffer);
    79.             }
    80.             os.flush();
    81.         } catch (IOException e) {
    82.             LOGGER.error(e.getMessage(), e);
    83.         } finally {
    84.             if (exportedFileInputStream != null) {
    85.                 try {
    86.                     exportedFileInputStream.close();
    87.                 } catch (IOException e) {
    88.                     LOGGER.error(e.getMessage(), e);
    89.                 }
    90.             }
    91.             if (bis != null) {
    92.                 try {
    93.                     bis.close();
    94.                 } catch (IOException e) {
    95.                     LOGGER.error(e.getMessage(), e);
    96.                 }
    97.             }
    98.             if (os != null) {
    99.                 try {
    100.                     os.close();
    101.                 } catch (IOException e) {
    102.                     LOGGER.error(e.getMessage(), e);
    103.                 }
    104.             }
    105.             // 下载完成后删除临时文件
    106.             if (exportedFile.exists()) {
    107.                 File exportedFileDir = exportedFile.getParentFile();
    108.                 FileUtils.deleteDir(exportedFileDir);
    109.             }
    110.         }
    111.     }
    112. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3. import org.apache.commons.lang3.StringUtils;
    4.  
    5. import java.io.*;
    6. import java.util.zip.ZipEntry;
    7. import java.util.zip.ZipOutputStream;
    8.  
    9.  
    10. public class FileUtils {
    11.  
    12.     static {
    13.         // 当文件系统中没有nhtemp文件夹的时候，创建
    14.         File sf = new File(FileUtils.filePathJoin(System.getProperty("java.io.tmpdir"), "nhtemp"));
    15.         if (!sf.exists()) {
    16.             sf.mkdirs();
    17.         }
    18.     }
    19.  
    20.     /**
    21.      * 临时文件夹，在临时文件夹中创建nhtemp，用来保存所有使用本工具类创建的文件，以便于统一清空临时文件夹,并且已经包含了文件分割符号，请注意
    22.      */
    23.     public static final String TEMP_FILE_PATH = FileUtils.filePathJoin(System.getProperty("java.io.tmpdir"), "nhtemp");
    24.  
    25.     /**
    26.      * 向文件写入数据
    27.      *
    28.      * @param is
    29.      * @param file
    30.      * @throws IOException
    31.      */
    32.     public static void writeToFile(InputStream is, File file) throws IOException {
    33.         FileOutputStream fs = null;
    34.         try {
    35.             fs = new FileOutputStream(file);
    36.             byte[] buffer = new byte[1024];
    37.             int byteread = 0;
    38.             while ((byteread = is.read(buffer)) != -1) {
    39.                 fs.write(buffer, 0, byteread);
    40.             }
    41.         } catch (IOException e) {
    42.             throw e;
    43.         } finally {
    44.             if (fs != null) {
    45.                 fs.close();
    46.             }
    47.             is.close();
    48.         }
    49.     }
    50.  
    51.     /**
    52.      * 删除文件夹（会删除文件夹下所有的文件）
    53.      *
    54.      * @param dir
    55.      * @return
    56.      */
    57.     public static boolean deleteDir(File dir) {
    58.         if (dir.isDirectory()) {
    59.             String[] children = dir.list();
    60.             //递归删除目录中的子目录下
    61.             for (int i = 0; i < children.length; i++) {
    62.                 boolean success = deleteDir(new File(dir, children[i]));
    63.                 if (!success) {
    64.                     return false;
    65.                 }
    66.             }
    67.         }
    68.         // 目录此时为空，可以删除
    69.         return dir.delete();
    70.     }
    71.  
    72.     public static File createDir(String dirPath) {
    73.         File dir = new File(dirPath);
    74.         //如果文件夹不存在
    75.         if (!dir.exists()) {
    76.             //创建文件夹
    77.             dir.mkdir();
    78.         }
    79.         return dir;
    80.     }
    81.  
    82.     public static void zipDir(String directoryName, ZipOutputStream zos, BufferedOutputStream bos) {
    83.         File file = new File(directoryName);
    84.         if (file.exists()) {
    85.             File[] fileList = file.listFiles();
    86.             assert fileList != null;
    87.             for (File f : fileList) {
    88.                 // 压缩单个文件到 zos
    89.                 String zipName = f.getName();
    90.                 try {
    91.                     zos.putNextEntry(new ZipEntry(zipName));
    92.                     int len;
    93.                     FileInputStream is = new FileInputStream(f);
    94.                     BufferedInputStream bis = new BufferedInputStream(is);
    95.                     byte[] bytes = new byte[1024];
    96.                     while ((len = bis.read(bytes)) != -1) {
    97.                         bos.write(bytes, 0, len);
    98.  
    99.                     }
    100.                     bos.flush();
    101.                     zos.flush();
    102.  
    103. //                    结束当前压缩文件的添加
    104.                     bis.close();
    105.                     is.close();
    106.                 } catch (IOException e) {
    107.                     e.printStackTrace();
    108.  
    109.                 }
    110.  
    111.             }
    112.  
    113.         }
    114.     }
    115.  
    116.     /**
    117.      * 路径拼接工具方法
    118.      * @param filePath 文件路径
    119.      * @return 拼接结果
    120.      */
    121.     public static String filePathJoin(String... filePath) {
    122.         return StringUtils.join(filePath, File.separator);
    123.     }
    124.  
    125. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3. import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil;
    4. import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
    5. import org.apache.poi.ss.usermodel.Workbook;
    6. import org.slf4j.Logger;
    7. import org.slf4j.LoggerFactory;
    8.  
    9. import java.util.Map;
    10.  
    11. public class VelocityTemplateExporter extends AbsExporter {
    12.  
    13.     private static final Logger LOGGER = LoggerFactory.getLogger(VelocityTemplateExporter.class);
    14.  
    15.     private String templateFilename;
    16.  
    17.     public VelocityTemplateExporter(String templateFilename) {
    18.         this.templateFilename = templateFilename;
    19.     }
    20.  
    21.     @Override
    22.     public String getTargetFileSuffix() {
    23.         return ".xlsx";
    24.     }
    25.  
    26.     @Override
    27.     public Writable render(Map dataSource) {
    28.         String html = VelocityUtils.render(this.templateFilename + ".vm", dataSource);
    29.         LOGGER.trace("渲染的html为：\n{}", html);
    30.         Workbook workbook = ExcelXorHtmlUtil.htmlToExcel(html, ExcelType.XSSF);
    31.         if (null == workbook) {
    32.             throw new NullPointerException("workbook 为 null");
    33.         }
    34.         return new WorkbookWrapper(workbook);
    35.     }
    36. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3. import org.apache.velocity.VelocityContext;
    4. import org.apache.velocity.app.Velocity;
    5. import org.apache.velocity.exception.VelocityException;
    6.  
    7. import java.io.StringWriter;
    8. import java.util.Map;
    9. import java.util.Properties;
    10.  
    11. public class VelocityUtils {
    12.  
    13.     /**
    14.      * 模板文件所在目录
    15.      */
    16.     private static final String TEMPLATE_FILE_DIR = FileUtils.filePathJoin("file", "velocityTemp");
    17.  
    18.     static {
    19.         //初始化参数
    20.         Properties properties = new Properties();
    21.         //设置 velocity 资源加载方式为 class
    22.         properties.setProperty("resource.loader", "class");
    23.         //设置 velocity 资源加载方式为 class 时的处理类
    24.         properties.setProperty("class.resource.loader.class", "org.apache.velocity.runtime.resource.loader.ClasspathResourceLoader");
    25.         // 执行初始化
    26.         Velocity.init(properties);
    27.     }
    28.  
    29.     /**
    30.      * 渲染对应模板，并输出渲染结果
    31.      * @param templateFileName 模板文件名
    32.      * @param velocityContext 上下文对象，即渲染使用的数据源
    33.      * @return 渲染结果
    34.      */
    35.     public static String render(String templateFileName, VelocityContext velocityContext) throws VelocityException {
    36.         StringWriter writer = new StringWriter();
    37.         Velocity.mergeTemplate(FileUtils.filePathJoin(TEMPLATE_FILE_DIR, templateFileName), "UTF-8", velocityContext, writer);
    38.         return writer.toString();
    39.     }
    40.  
    41.     /**
    42.      * 渲染对应模板，并输出渲染结果
    43.      * @param templateFileName 模板文件名
    44.      * @param renderDataSource 渲染使用的数据源
    45.      * @return 渲染结果
    46.      */
    47.     public static String render(String templateFileName, Map renderDataSource) throws VelocityException {
    48.         VelocityContext velocityContext = new VelocityContext(renderDataSource);
    49.         return render(templateFileName, velocityContext);
    50.     }
    51. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3. import org.apache.poi.ss.usermodel.Workbook;
    4.  
    5. import java.io.IOException;
    6. import java.io.OutputStream;
    7.  
    8. public class WorkbookWrapper implements Writable {
    9.  
    10.     private Workbook workbook;
    11.  
    12.     public WorkbookWrapper(Workbook workbook) {
    13.         this.workbook = workbook;
    14.     }
    15.  
    16.     @Override
    17.     public void write(OutputStream outputStream) throws IOException {
    18.         this.workbook.write(outputStream);
    19.     }
    20. }
    1. package com.ly.cloud.utils.exportUtil;
    2.  
    3. import cn.afterturn.easypoi.excel.ExcelExportUtil;
    4. import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
    5. import org.apache.poi.ss.usermodel.Workbook;
    6.  
    7. import java.util.Map;
    8. import java.util.function.Function;
    9.  
    10. public class ExcelTemplateExporter extends AbsExporter {
    11.  
    12.     private TemplateExportParams templateExportParams;
    13.  
    14.     private Function afterRender;
    15.  
    16.     public ExcelTemplateExporter(String templateFilename) {
    17.         this(templateFilename, null);
    18.     }
    19.  
    20.     public ExcelTemplateExporter(String templateFilename, Function afterRender) {
    21.         this.templateExportParams = new TemplateExportParams("file/excelTemp/" + templateFilename + ".xlsx");
    22.         this.afterRender = afterRender;
    23.     }
    24.  
    25.     @Override
    26.     public Writable render(Map dataSource) {
    27.         Workbook workbook = ExcelExportUtil.exportExcel(this.templateExportParams, dataSource);
    28.         if (null == workbook) {
    29.             throw new NullPointerException("workbook 为 null");
    30.         }
    31.         if (this.afterRender != null) {
    32.             workbook = this.afterRender.apply(workbook);
    33.         }
    34.         return new WorkbookWrapper(workbook);
    35.     }
    36.  
    37.     @Override
    38.     public String getTargetFileSuffix() {
    39.         return ".xlsx";
    40.     }
    41. }
    1. package com.lili.exportUtil;
    2.  
    3. import org.apache.poi.xwpf.usermodel.XWPFDocument;
    4.  
    5. import java.io.IOException;
    6. import java.io.OutputStream;
    7.  
    8. public class XWPFDocumentWrapper implements Writable {
    9.  
    10.     private XWPFDocument xwpfDocument;
    11.  
    12.     public XWPFDocumentWrapper(XWPFDocument xwpfDocument) {
    13.         this.xwpfDocument = xwpfDocument;
    14.     }
    15.  
    16.     @Override
    17.     public void write(OutputStream outputStream) throws IOException {
    18.         this.xwpfDocument.write(outputStream);
    19.     }
    20. }
    1. package com.lili.exportUtil;
    2.  
    3. import cn.afterturn.easypoi.word.WordExportUtil;
    4.  
    5. import org.apache.poi.xwpf.usermodel.XWPFDocument;
    6.  
    7. import java.util.Map;
    8.  
    9. public class WordTemplateExporter extends AbsExporter {
    10.  
    11.     private String templateFilePath;
    12.  
    13.     public WordTemplateExporter(String templateFilename) {
    14.         this.templateFilePath = "file/excelTemp/" + templateFilename + ".docx";
    15.     }
    16.  
    17.     @Override
    18.     public String getTargetFileSuffix() {
    19.         return ".docx";
    20.     }
    21.  
    22.     @Override
    23.     public Writable render(Map dataSource) throws Exception {
    24.         XWPFDocument xwpfDocument = WordExportUtil.exportWord07(templateFilePath, dataSource);
    25.         XWPFDocumentWrapper xwpfDocumentWrapper = new XWPFDocumentWrapper(xwpfDocument);
    26.         return xwpfDocumentWrapper;
    27.     }
    28. }

    2、引入所需依赖

    1.         <dependency>
    2.             <groupId>cn.afterturngroupId>
    3.             <artifactId>easypoi-webartifactId>
    4.             <version>3.2.0version>
    5.         dependency>
    6.         <dependency>
    7.             <groupId>eu.bitwalkergroupId>
    8.             <artifactId>UserAgentUtilsartifactId>
    9.             <version>1.21version>
    10.         dependency>
    11.         <dependency>
    12.             <groupId>org.apache.velocitygroupId>
    13.             <artifactId>velocity-engine-coreartifactId>
    14.             <version>2.0version>
    15.         dependency>
    16.         <dependency>
    17. 			<groupId>org.jsoupgroupId>
    18. 			<artifactId>jsoupartifactId>
    19. 			<version>1.12.1version>
    20. 		dependency>

    3、导出案例

    3.1、导出以下excel

    方式一：

    操作步骤：

    放的位置要和工具类设置位置一样即可。

    然后编写下载接口即可完成。

    访问接口即可下载成功，如下

    方式二：

    操作步骤：

    新建vm文件。放入合适位置（位置取自工具类，可以自行调整）

    vm文件内容示例：

    1. ## 各个列的样式，主要是加上边框
    2. #set($style = 'style="border: 1; height:50;width:12"')
    3. #set($height = 'style="height:30;font-size:9"')
    4. #set($fontSize = 'style="font-size: 20;"')
    5. ## 方法，如果存在则显示否则显示空
    6. #macro(displayValue $value)
    7.     #if($value)
    8.         $value
    9.     #else
    10.     #end
    11. #end
    12.  
    13. ## sheetName 必须存在
    14. <table sheetName="心理咨询来访者登记表">
    15.     <tr>
    16.         <th colspan="6" $fontSize>心理咨询来访者登记表th>
    17.     tr>
    18.     <tr>
    19.         <th $height colspan="6">为使咨询更有效率，节约你的时间，希望朋友们在咨询前能详细提供如下资料，
    20.             我们承诺进行严格保密！th>
    21.     tr>
    22.     <tr>
    23.         <th $style>姓名th>
    24.         <th $style>#displayValue($xm)th>
    25.         <th $style>性别th>
    26.         <th $style>#displayValue($xb)th>
    27.         <th $style>年龄th>
    28.         <th $style>#displayValue($age)th>
    29.     tr>
    30.     <tr>
    31.         <th $style>院系th>
    32.         <th $style>#displayValue($yx)th>
    33.         <th $style>班级th>
    34.         <th $style>#displayValue($bj)th>
    35.         <th $style>寝室th>
    36.         <th $style>#displayValue($qs)th>
    37.     tr>
    38.     <tr>
    39.         <th $style>联系电话th>
    40.         <th $style>#displayValue($lxfs)th>
    41.         <th colspan="2" $style>辅导员/班主任th>
    42.         <th colspan="2" $style>#displayValue($fdyxm)th>
    43.     tr>
    44.     <tr>
    45.         <th colspan="3" $style>如果您有紧急情况，希望我们与谁联系th>
    46.         <th colspan="3" $style>#displayValue($jjlxr)th>
    47.     tr>
    48.     <tr>
    49.         <th $style>
    50.             家庭情况（请简要介绍您的家庭背景和成长经历）
    51.         th>
    52.         <th $style colspan="5">#displayValue($jtqk)th>
    53.     tr>
    54.     <tr>
    55.         <th $style>
    56.             咨询问题（你困惑或难以摆脱的问题是什么？）
    57.         th>
    58.         <th $style colspan="5">#displayValue($zxwt)th>
    59.     tr>
    60.     <tr>
    61.         <th $style>
    62.             你期望达到的咨询目的
    63.         th>
    64.         <th $style colspan="5">#displayValue($zxmd)th>
    65.     tr>
    66.     <tr>
    67.         <th colspan="2" $style>以前是否接受过心理咨询或治疗th>
    68.         <th  $style>#displayValue($sfkgys)th>
    69.         <th colspan="2" $style>是否做过相关的心理测验th>
    70.         <th  $style>#displayValue($fxlcy)th>
    71.     tr>
    72.     <tr>
    73.         <th colspan="2" $style>若做过，结果是：th>
    74.         <th colspan="4" $style>#displayValue($xlcyjg)th>
    75.     tr>
    76.     <tr>
    77.         <th colspan="2" $style>预约咨询时间(至少需提前三天预约)th>
    78.         <th colspan="4" $style>#displayValue($yysj) #displayValue($yysd)th>
    79.     tr>
    80. table>

    这里无非是自己将页面通过tr,th并且合并单元格的方式画出来。

    使用方法：

    结果如下：

    总结：

    方式一：适合那些单条数据，没有变化的excel，直接制作好excel文件，放入项目里面即可完成

    方式二：vm模板可以定义方法与变量，比较适合动态导出sql，可以根据不同map的结果导出不同样子的excel。

    3.2、导出以下excel

    这种就很常见了，平常简单的导出都是这个样子的。今天就用这个工具类来实现一下

    方式一：还是直接放入项目里面的方式

    编写接口进行下载测试：

    下载结果如下：

    注意：这里开头要加{{fe:maplist  结尾要加}}，其余就是t.变量名字。其中maplist跟接口设置数据的key对应即可

    方式二：采用vm的方式

    这个时候就用到foreach循环了，vm文件如下

    然后修改接口的实现类

    结果如下：

    总结：

    两种方式都很不错，可以实现效果，但是前者针对那种一成不变的或者简单的很方便。后者对于那种内部循环的就很见效了，比如下面这个。

    红圈的部分就是需要根据次数来循环的部分，这个时候就非常适合用vm模板的方式了。

    拓展

    这个vm文件可自行扩展。非常灵活。下面是我用过的几个vm实战例子

    1. ## 二级列（等级）标题 list
    2. #set($subColumnTitleList = [])
    3. ## 数据索引 list
    4. #set($dataIndexList = [])
    5. ## 各个列的样式，主要是加上边框
    6. #set($style = 'style="border: 1"')
    7. #set($styleW = 'style="border: 1 solid black; width: 25;"')
    8. #set($fontSize = 'style="border: 1 solid black; font-size: 20;"')
    9. #set($bgck = 'style="border: 1 solid black; background-color: yellow;"')
    10. #set($colspanValue = $lbmc.size() + 1)
    11. ## sheetName 必须存在
    12. <table sheetName="columnsExportTest">
    13.     <tr>
    14.         <th colspan="$num" $fontSize>$titleth>
    15.     tr>
    16.     <tr>
    17.         <th colspan="$num" $style style="font-weight: bold">单位:XXX技术学院th>
    18.     tr>
    19.     <tr>
    20.         <th rowspan="2" $style>院系th>
    21.         <th colspan="$colspanValue" $style>学校总人数th>
    22.         #foreach($column in $columns)
    23.             #if($column.children)
    24. ##                <th colspan="$column.children.size()" $style>$column.titleth>
    25.                 #foreach($subColumn in $column.children)
    26.                     <th rowspan="1" colspan="2" $style>$subColumn.titleth>
    27.                     ## 因为 add 方法有返回值，所以这里需要用 html 的方式注释一下，下面同理
    28.                     
    29.                     
    30.                     
    31.                     
    32.                 #end
    33.             #else
    34.                 <th rowspan="1" colspan="2" $style>$column.titleth>
    35.                 
    36.                 
    37.                 
    38.                 
    39.             #end
    40.         #end
    41.         <th colspan="2" $style>合计th>
    42.     tr>
    43.     <tr>
    44.         #foreach($mc in $lbmc)
    45.             <th $style>$mcth>
    46.         #end
    47.         <th $style>合计th>
    48.         #if($subColumnTitleList)
    49.             #set($count = 1)
    50.             #foreach($subColumnTitle in $subColumnTitleList)
    51.                 #if ($count % 2 == 0)
    52.                     <th $bgck>$subColumnTitleth>
    53.                 #else
    54.                     <th $style>$subColumnTitleth>
    55.                 #end
    56.                 #set($count = $count + 1)
    57.             #end
    58.         #end
    59.         <th $style>人数th>
    60.         <th $bgck>发放金额th>
    61.     tr>
    62.     #foreach($record in $dataSource)
    63.         <tr>
    64.             <td $styleW>$record.BMMCtd>
    65.             #set($sum = 0)
    66.             #foreach($dm in $lbdm)
    67.                 <td $style>$record.get($dm)td>
    68.                 #set($currentValue = $record.get($dm))  ## 获取当前值
    69.                 #set($sum = $sum + $currentValue)  ## 累加值到 $sum 变量
    70.             #end
    71.             <td $style>$sumtd>
    72.             #set($count = 1)
    73.             #foreach($dataIndex in $dataIndexList)
    74.                 #if($count % 2 == 0)
    75.                     <td $bgck>$record.get($dataIndex)td>
    76.                 #else
    77.                     <td $style>$record.get($dataIndex)td>
    78.                 #end
    79.                 #set($count = $count + 1)
    80.             #end
    81.             #if($record.BMMC == "合计")
    82.                 <td colspan="1" $style>$record.HJRCtd>
    83.                 <td colspan="1" $bgck>$record.HJJEtd>
    84.             #else
    85.                 <td $style>$record.HJRCtd>
    86.                 <td $bgck>$record.HJJEtd>
    87.             #end
    88.         tr>
    89.     #end
    90. table>
    1. ## 各个列的样式，主要是加上边框
    2. #set($style = 'style="border: 1; height:50;width:18"')
    3. #set($bold = 'style="border: 1; height:50;width:18;font-weight: bold;"')
    4. #set($fontSize = 'style="font-size: 20;"')
    5. #macro(displayValue $value)
    6.     #if($value)
    7.         $value
    8.     #else
    9.     #end
    10. #end
    11. ## sheetName 必须存在
    12. <table sheetName="心理咨询个案记录表">
    13.     <tr>
    14.         <th colspan="6" $fontSize>心理咨询个案记录表th>
    15.     tr>
    16.     <tr>
    17.         <th $style>咨询师th>
    18.         <th $style>#displayValue($ZXS)th>
    19.         <th $style>咨询次数th>
    20.         <th $style>#displayValue($dataSource.size())th>
    21.         <th $style>来访日期th>
    22.         <th $style>#displayValue($dataSource.get(0).ZXSJ)th>
    23.     tr>
    24.     <tr>
    25.         <th $style>来访者th>
    26.         <th $style>#displayValue($LFZ)th>
    27.         <th $style>性别th>
    28.         <th $style>#displayValue($XB)th>
    29.         <th $style>年龄th>
    30.         <th $style>#displayValue($AGE)th>
    31.     tr>
    32.     <tr>
    33.         <th $style>系部班级th>
    34.         <th $style>#displayValue($XBBJ)th>
    35.         <th $style>辅导员/班主任th>
    36.         <th $style>#displayValue($FDYXM)th>
    37.         <th $style>联系人及联系方式th>
    38.         <th $style>#displayValue($JJLXR)th>
    39.     tr>
    40.     #foreach($record in $dataSource)
    41.         <tr>
    42.             <th  rowspan="11" $bold>#displayValue($record.title)th>
    43.             <th $bold colspan="5">表现出的问题th>
    44.         tr>
    45.         <tr>
    46.             <th $style>来访者自述th>
    47.             <th colspan="4" $style>#displayValue($record.LFZZS)th>
    48.         tr>
    49.         <tr>
    50.             <th $bold colspan="5">问题原因th>
    51.         tr>
    52.         <tr>
    53.             <th $style>促使因素th>
    54.             <th colspan="4" $style>#displayValue($record.CSYS)th>
    55.         tr>
    56.         <tr>
    57.             <th $style>先前因素th>
    58.             <th colspan="4" $style>#displayValue($record.XQYS)th>
    59.         tr>
    60.         <tr>
    61.             <th $style>社会因素th>
    62.             <th colspan="4" $style>#displayValue($record.SHYS)th>
    63.         tr>
    64.         <tr>
    65.             <th $style>健康状况及治疗史th>
    66.             <th colspan="4" $style>#displayValue($record.JKZTJZLS)th>
    67.         tr>
    68.         <tr>
    69.             <th $bold colspan="5">分析、评估与咨询方案th>
    70.         tr>
    71.         <tr>
    72.             <th $style>评估诊断th>
    73.             <th colspan="4" $style>#displayValue($record.PGZD)th>
    74.         tr>
    75.         <tr>
    76.             <th $style>咨询目标th>
    77.             <th colspan="4" $style>#displayValue($record.ZXMB)th>
    78.         tr>
    79.         <tr>
    80.             <th $style>咨询方法th>
    81.             <th colspan="4" $style>#displayValue($record.ZXFF)th>
    82.         tr>
    83.     #end
    84. table>

    3.3、导出word

    接口：

    结果：

  • 相关阅读:
    Jenkins 添加 Slave Agent 节点时报类文件不匹配错误
    Python3.11教程4：异常处理
    程序员与产品之间应该如何配合，什么时候技术为重，什么时候产品为重？
    SQL中：check与 contriate语句约束的区别
    三 TypeScript变量
    PyTorch入门之【dataset】
    2023.11.13 信息学日志
    极致cms1:1精仿资源哟源码/资源网系统/下载教程娱乐网源码
    【零基础入门MyBatis系列】第十五篇——分页插件与注解式开发
    springboot如何集成swagger，swagger如何为所有API添加token参数，swagger常用注解，简介明了，举例说明
  • 原文地址：https://blog.csdn.net/qijing19991210/article/details/136656476