FreeMarker模板引擎

　　前言

　　FreeMarker是一款模板引擎,即一种基于模板和要改变的数据，并用来生成输出文本的通用工具。

　　官方文档：http://freemarker.foofun.cn

　　

　　日常开发中，我们会有这样的需求：

　　1、在页面上填写数据并导出word文档、后台批量设置数据并导出Excel文档（例如我们之前的博客记录：html页面转PDF、图片操作记录）

　　2、写一个代码生成工具（例如我们之前的博客记录：写一个简单的代码生成器）

 

　　这种情况下我们可以用FreeMarker模板引擎来实现，本文记录FreeMarker简单操作。

 

　　代码编写

　　maven引入依赖

<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.31</version>
</dependency>

 

　　Word/Excel操作

　　Word操作

　　例如，要导出员工档案，格式如下：

 

 

 　　我们先按照格式要求，写好word文档，然后另存为xml，得到模板文件

 

 

 　　用idea打开word.xml，格式化一下，然后根据FreeMarker的语法表达式读取、设置值

 

 

 　　封装一个方法

    //Word文档操作
    private static void createWord(){
        System.out.println("开始Word文档操作...");

        //指定TemplateLoading模板根路径
        String rootPath = "E:\\Java\\test\\";
        //模板文件
        String templatePath = "word.xml";
        //最终输出文件路径
        String outFilePath = rootPath+"word_by_freemarker.docx";
        //数据
        Map<String, Object> data = new HashMap<>();
        data.put("company","某某公司");
        data.put("number","0001");
        data.put("name","huanzi-qch");
        data.put("phone","15600000000");
        data.put("department","软件开发部");
        data.put("post","开发工程师");
        ArrayList<Map<String, String>> works = new ArrayList<>();
        Map<String, String> work1 = new HashMap<>();
        work1.put("company","某某单位1");
        work1.put("time","2018-01 - 2019-01");
        work1.put("department","研发1部");
        work1.put("post","开发工程师");
        works.add(work1);
        Map<String, String> work2 = new HashMap<>();
        work2.put("company","某某单位2");
        work2.put("time","2019-01 - 2020-01");
        work2.put("department","研发2部");
        work2.put("post","开发工程师");
        works.add(work2);
        data.put("works",works);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本与pom文件相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //处理（数据）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Word文档操作结束！");
    }

 

　　效果

 

 

 

 

 

　　Excel操作

　　Excel操作同理，也是先创建一个例子，另存为xml格式，再根据FreeMarker的语法表达式设置值

　　按照字段名，建Excel例子时直接把取值代码写进去，转成xml后就可能省一点时间

 

 

 

 

 

 

 　　同样，idea打开后格式化一下，遍历设置值

 

 

 　　封装一个方法

    //Excel文档操作
    private static void createExcel(){
        System.out.println("开始Excel文档操作...");

        //指定TemplateLoading模板根路径
        String rootPath = "E:\\Java\\test\\";
        //模板文件
        String templatePath = "excel.xml";
        //最终输出文件路径
        String outFilePath = rootPath+"excel_by_freemarker.xlsx";
        //数据
        Map<String, Object> data = new HashMap<>();
        ArrayList<Map<String, String>> persons = new ArrayList<>();
        Map<String, String> person1 = new HashMap<>();
        person1.put("name","huanzi-qch1");
        person1.put("phone","15600000000");
        person1.put("company","某某单位1");
        person1.put("time","2018-01 - 2019-01");
        person1.put("department","研发1部");
        person1.put("post","开发工程师");
        persons.add(person1);
        Map<String, String> person2 = new HashMap<>();
        person2.put("name","huanzi-qch2");
        person2.put("phone","15600000000");
        person2.put("company","某某单位2");
        person2.put("time","2019-01 - 2020-01");
        person2.put("department","研发2部");
        person2.put("post","开发工程师");
        persons.add(person2);
        data.put("persons",persons);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本与pom文件相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //处理（数据）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Excel文档操作结束！");
    }

 

　　效果

 

 

 

 

　　2021-10-29更新：如果需要调整文档，直接打开我们的xml模板进行格式调整、如果添加图片，在模板中插入图片调整好格式，另存为xml格式，找到图片的base64编码，替换成动态参数即可（注：如果有使用模板语句关键字，要先删掉再打开xml模板调整格式）

<#list listItem as item>

</#list>

 

 

 

 　　图片转base64

    public static void main(String[] args) {
        System.out.println(imgFileToBase64("D:\\XFT User\\Pictures\\logo.png"));
    }

    /**
     * 图片转为base64
     */
    private static String imgFileToBase64(String imgFile){
        byte[] data = null;

        try(InputStream in = new FileInputStream(imgFile);) {
            data = new byte[in.available()];
            in.read(data);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return new BASE64Encoder().encode(data);
    } 

 

 

　　代码生成器

　　代码生成器，先把我们之前写好的JDBC连接数据库工具类、字符串处理工具类、表结构信息实体类以及获取表结构信息的方法先拿过来

    /**
     * 程序自动设置
     */
    private static String tableName;//表名
    private static String tableComment;//表注释

    /**
     * 数据连接相关，需要手动设置
     */
    private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";
    private static final String DRIVER_CLASSNAME = "com.mysql.cj.jdbc.Driver";

    /**
     * 字符串处理工具类
     */
    private static class StringUtil {
        /**
         * 数据库类型->JAVA类型
         *
         * @param dbType 数据库类型
         * @return JAVA类型
         */
        private static String typeMapping(String dbType) {
            String javaType;
            if ("int|integer".contains(dbType)) {
                javaType = "Integer";
            } else if ("float|double|decimal|real".contains(dbType)) {
                javaType = "Double";
            } else if ("date|time|datetime|timestamp".contains(dbType)) {
                javaType = "Date";
            } else {
                javaType = "String";
            }
            return javaType;
        }

        /**
         * 驼峰转换为下划线
         */
        private static String underscoreName(String camelCaseName) {
            StringBuilder result = new StringBuilder();
            if (camelCaseName != null && camelCaseName.length() > 0) {
                result.append(camelCaseName.substring(0, 1).toLowerCase());
                for (int i = 1; i < camelCaseName.length(); i++) {
                    char ch = camelCaseName.charAt(i);
                    if (Character.isUpperCase(ch)) {
                        result.append("_");
                        result.append(Character.toLowerCase(ch));
                    } else {
                        result.append(ch);
                    }
                }
            }
            return result.toString();
        }

        /**
         * 首字母大写
         */
        private static String captureName(String name) {
            char[] cs = name.toCharArray();
            cs[0] -= 32;
            return String.valueOf(cs);

        }

        /**
         * 下划线转换为驼峰
         */
        private static String camelCaseName(String underscoreName) {
            StringBuilder result = new StringBuilder();
            if (underscoreName != null && underscoreName.length() > 0) {
                boolean flag = false;
                for (int i = 0; i < underscoreName.length(); i++) {
                    char ch = underscoreName.charAt(i);
                    if ("_".charAt(0) == ch) {
                        flag = true;
                    } else {
                        if (flag) {
                            result.append(Character.toUpperCase(ch));
                            flag = false;
                        } else {
                            result.append(ch);
                        }
                    }
                }
            }
            return result.toString();
        }
    }

    /**
     * JDBC连接数据库工具类
     */
    private static class DBConnectionUtil {

        static {
            // 1、加载驱动
            try {
                Class.forName(DRIVER_CLASSNAME);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }

        /**
         * 返回一个Connection连接
         */
        static Connection getConnection() {
            Connection conn = null;
            // 2、连接数据库
            try {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }

        /**
         * 关闭Connection，Statement连接
         */
        public static void close(Connection conn, Statement stmt) {
            try {
                conn.close();
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        /**
         * 关闭Connection，Statement，ResultSet连接
         */
        public static void close(Connection conn, Statement stmt, ResultSet rs) {
            try {
                close(conn, stmt);
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 表结构信息实体类
     */
    private static class TableInfo {
        private String columnName;//字段名
        private String dataType;//字段类型
        private String columnComment;//字段注释
        private String columnKey;//主键
        private String extra;//主键类型

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getDataType() {
            return dataType;
        }

        public void setDataType(String dataType) {
            this.dataType = dataType;
        }

        public String getColumnComment() {
            return columnComment;
        }

        public void setColumnComment(String columnComment) {
            this.columnComment = columnComment;
        }

        public String getColumnKey() {
            return columnKey;
        }

        public void setColumnKey(String columnKey) {
            this.columnKey = columnKey;
        }

        public String getExtra() {
            return extra;
        }

        public void setExtra(String extra) {
            this.extra = extra;
        }
    }

    /**
     * 获取表结构信息
     * 目前仅支持mysql
     */
    private static List<TableInfo> getTableInfo() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<TableInfo> list = new ArrayList<>();
        try {
            conn = DBConnectionUtil.getConnection();

            //表字段信息
            String sql = "select column_name,data_type,column_comment,column_key,extra from information_schema.columns where table_schema = (select database()) and table_name=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                TableInfo tableInfo = new TableInfo();
                //列名，全部转为小写
                tableInfo.setColumnName(rs.getString("column_name").toLowerCase());
                //列类型
                tableInfo.setDataType(rs.getString("data_type"));
                //列注释
                tableInfo.setColumnComment(rs.getString("column_comment"));
                //主键
                tableInfo.setColumnKey(rs.getString("column_key"));
                //主键类型
                tableInfo.setExtra(rs.getString("extra"));
                list.add(tableInfo);
            }

            //表注释
            sql = "select table_comment from information_schema.tables where table_schema = (select database()) and table_name=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                //表注释
                tableComment = rs.getString("table_comment");
            }
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            if(rs != null){
                DBConnectionUtil.close(conn, ps, rs);
            }
        }
        return list;
    }

　　从原来的代码生成器那里拿一个entity.tlf模板，小改动一下（主要是循环、if判断那里不一样）

package cn.huanzi.qch.baseadmin.sys.${entityToLowerCase}.pojo;

import lombok.Data;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

/**
 * ${tableComment} 实体类
 *
 * ${author}
 * ${date}
 */
@Entity
@Table(name = "${tableName}")
@Data
public class ${entity} implements Serializable {
  <#list tableInfos as tableInfo>
    <#if tableInfo.columnKey == "PRI">@Id</#if>
    <#if tableInfo.extra == "auto_increment">@GeneratedValue(strategy= GenerationType.IDENTITY)</#if>
    private ${tableInfo.dataType} ${tableInfo.columnName};//${tableInfo.columnComment}
  </#list>
}

　　封装一个方法

    //代码生成
    private static void autoGenerator(String tName){
        System.out.println("开始代码生成操作...");
        tableName = tName;

        //指定TemplateLoading模板根路径
        String rootPath = "E:\\Java\\test\\";
        //模板文件
        String templatePath = "entity.tlf";
        //最终输出文件路径
        String outFilePath = rootPath+"entity.java";
        //数据
        Map<String, Object> data = new HashMap<>();
        //驼峰标识映射后的表名
        String captureName = StringUtil.captureName(StringUtil.camelCaseName(tableName));
        //获取表信息，并进行处理
        List<TableInfo> tableInfoList = getTableInfo();
        ArrayList<Map<String, String>> tableInfos = new ArrayList<>();
        for (TableInfo info : tableInfoList) {
            HashMap<String, String> hashMap = new HashMap<>();
            hashMap.put("columnName", StringUtil.camelCaseName(info.getColumnName()));
            hashMap.put("dataType", StringUtil.typeMapping(info.getDataType()));
            hashMap.put("columnComment", info.getColumnComment());
            hashMap.put("columnKey", info.getColumnKey());
            hashMap.put("extra", info.getExtra());
            tableInfos.add(hashMap);
        }
        data.put("entityToLowerCase",captureName.toLowerCase());
        data.put("tableComment",tableComment);
        data.put("author","作者：Auto Generator By 'huanzi-qch'");
        data.put("date","生成日期："+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        data.put("tableName",tableName);
        data.put("entity",captureName);
        data.put("tableInfos",tableInfos);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本与pom文件相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //处理（数据）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("代码生成操作结束！");
    }

 

　　效果

 

 

 

 

 

 

　　完整代码

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import freemarker.template.Version;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Date;

public class Test {

    //Word文档操作
    private static void createWord(){
        System.out.println("开始Word文档操作...");

        //指定TemplateLoading模板根路径
        String rootPath = "E:\\Java\\test\\";
        //模板文件
        String templatePath = "word.xml";
        //最终输出文件路径
        String outFilePath = rootPath+"word_by_freemarker.docx";
        //数据
        Map<String, Object> data = new HashMap<>();
        data.put("company","某某公司");
        data.put("number","0001");
        data.put("name","huanzi-qch");
        data.put("phone","15600000000");
        data.put("department","软件开发部");
        data.put("post","开发工程师");
        ArrayList<Map<String, String>> works = new ArrayList<>();
        Map<String, String> work1 = new HashMap<>();
        work1.put("company","某某单位1");
        work1.put("time","2018-01 - 2019-01");
        work1.put("department","研发1部");
        work1.put("post","开发工程师");
        works.add(work1);
        Map<String, String> work2 = new HashMap<>();
        work2.put("company","某某单位2");
        work2.put("time","2019-01 - 2020-01");
        work2.put("department","研发2部");
        work2.put("post","开发工程师");
        works.add(work2);
        data.put("works",works);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本与pom文件相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //处理（数据）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Word文档操作结束！");
    }

    //Excel文档操作
    private static void createExcel(){
        System.out.println("开始Excel文档操作...");

        //指定TemplateLoading模板根路径
        String rootPath = "E:\\Java\\test\\";
        //模板文件
        String templatePath = "excel.xml";
        //最终输出文件路径
        String outFilePath = rootPath+"excel_by_freemarker.xlsx";
        //数据
        Map<String, Object> data = new HashMap<>();
        ArrayList<Map<String, String>> persons = new ArrayList<>();
        Map<String, String> person1 = new HashMap<>();
        person1.put("name","huanzi-qch1");
        person1.put("phone","15600000000");
        person1.put("company","某某单位1");
        person1.put("time","2018-01 - 2019-01");
        person1.put("department","研发1部");
        person1.put("post","开发工程师");
        persons.add(person1);
        Map<String, String> person2 = new HashMap<>();
        person2.put("name","huanzi-qch2");
        person2.put("phone","15600000000");
        person2.put("company","某某单位2");
        person2.put("time","2019-01 - 2020-01");
        person2.put("department","研发2部");
        person2.put("post","开发工程师");
        persons.add(person2);
        data.put("persons",persons);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本与pom文件相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //处理（数据）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Excel文档操作结束！");
    }

    //代码生成
    private static void autoGenerator(String tName){
        System.out.println("开始代码生成操作...");
        tableName = tName;

        //指定TemplateLoading模板根路径
        String rootPath = "E:\\Java\\test\\";
        //模板文件
        String templatePath = "entity.tlf";
        //最终输出文件路径
        String outFilePath = rootPath+"entity.java";
        //数据
        Map<String, Object> data = new HashMap<>();
        //驼峰标识映射后的表名
        String captureName = StringUtil.captureName(StringUtil.camelCaseName(tableName));
        //获取表信息，并进行处理
        List<TableInfo> tableInfoList = getTableInfo();
        ArrayList<Map<String, String>> tableInfos = new ArrayList<>();
        for (TableInfo info : tableInfoList) {
            HashMap<String, String> hashMap = new HashMap<>();
            hashMap.put("columnName", StringUtil.camelCaseName(info.getColumnName()));
            hashMap.put("dataType", StringUtil.typeMapping(info.getDataType()));
            hashMap.put("columnComment", info.getColumnComment());
            hashMap.put("columnKey", info.getColumnKey());
            hashMap.put("extra", info.getExtra());
            tableInfos.add(hashMap);
        }
        data.put("entityToLowerCase",captureName.toLowerCase());
        data.put("tableComment",tableComment);
        data.put("author","作者：Auto Generator By 'huanzi-qch'");
        data.put("date","生成日期："+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        data.put("tableName",tableName);
        data.put("entity",captureName);
        data.put("tableInfos",tableInfos);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本与pom文件相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //处理（数据）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("代码生成操作结束！");
    }

    /**
     * 程序自动设置
     */
    private static String tableName;//表名
    private static String tableComment;//表注释

    /**
     * 数据连接相关，需要手动设置
     */
    private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";
    private static final String DRIVER_CLASSNAME = "com.mysql.cj.jdbc.Driver";

    /**
     * 字符串处理工具类
     */
    private static class StringUtil {
        /**
         * 数据库类型->JAVA类型
         *
         * @param dbType 数据库类型
         * @return JAVA类型
         */
        private static String typeMapping(String dbType) {
            String javaType;
            if ("int|integer".contains(dbType)) {
                javaType = "Integer";
            } else if ("float|double|decimal|real".contains(dbType)) {
                javaType = "Double";
            } else if ("date|time|datetime|timestamp".contains(dbType)) {
                javaType = "Date";
            } else {
                javaType = "String";
            }
            return javaType;
        }

        /**
         * 驼峰转换为下划线
         */
        private static String underscoreName(String camelCaseName) {
            StringBuilder result = new StringBuilder();
            if (camelCaseName != null && camelCaseName.length() > 0) {
                result.append(camelCaseName.substring(0, 1).toLowerCase());
                for (int i = 1; i < camelCaseName.length(); i++) {
                    char ch = camelCaseName.charAt(i);
                    if (Character.isUpperCase(ch)) {
                        result.append("_");
                        result.append(Character.toLowerCase(ch));
                    } else {
                        result.append(ch);
                    }
                }
            }
            return result.toString();
        }

        /**
         * 首字母大写
         */
        private static String captureName(String name) {
            char[] cs = name.toCharArray();
            cs[0] -= 32;
            return String.valueOf(cs);

        }

        /**
         * 下划线转换为驼峰
         */
        private static String camelCaseName(String underscoreName) {
            StringBuilder result = new StringBuilder();
            if (underscoreName != null && underscoreName.length() > 0) {
                boolean flag = false;
                for (int i = 0; i < underscoreName.length(); i++) {
                    char ch = underscoreName.charAt(i);
                    if ("_".charAt(0) == ch) {
                        flag = true;
                    } else {
                        if (flag) {
                            result.append(Character.toUpperCase(ch));
                            flag = false;
                        } else {
                            result.append(ch);
                        }
                    }
                }
            }
            return result.toString();
        }
    }

    /**
     * JDBC连接数据库工具类
     */
    private static class DBConnectionUtil {

        static {
            // 1、加载驱动
            try {
                Class.forName(DRIVER_CLASSNAME);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }

        /**
         * 返回一个Connection连接
         */
        static Connection getConnection() {
            Connection conn = null;
            // 2、连接数据库
            try {
                conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }

        /**
         * 关闭Connection，Statement连接
         */
        public static void close(Connection conn, Statement stmt) {
            try {
                conn.close();
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        /**
         * 关闭Connection，Statement，ResultSet连接
         */
        public static void close(Connection conn, Statement stmt, ResultSet rs) {
            try {
                close(conn, stmt);
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 表结构信息实体类
     */
    private static class TableInfo {
        private String columnName;//字段名
        private String dataType;//字段类型
        private String columnComment;//字段注释
        private String columnKey;//主键
        private String extra;//主键类型

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getDataType() {
            return dataType;
        }

        public void setDataType(String dataType) {
            this.dataType = dataType;
        }

        public String getColumnComment() {
            return columnComment;
        }

        public void setColumnComment(String columnComment) {
            this.columnComment = columnComment;
        }

        public String getColumnKey() {
            return columnKey;
        }

        public void setColumnKey(String columnKey) {
            this.columnKey = columnKey;
        }

        public String getExtra() {
            return extra;
        }

        public void setExtra(String extra) {
            this.extra = extra;
        }
    }

    /**
     * 获取表结构信息
     * 目前仅支持mysql
     */
    private static List<TableInfo> getTableInfo() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<TableInfo> list = new ArrayList<>();
        try {
            conn = DBConnectionUtil.getConnection();

            //表字段信息
            String sql = "select column_name,data_type,column_comment,column_key,extra from information_schema.columns where table_schema = (select database()) and table_name=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                TableInfo tableInfo = new TableInfo();
                //列名，全部转为小写
                tableInfo.setColumnName(rs.getString("column_name").toLowerCase());
                //列类型
                tableInfo.setDataType(rs.getString("data_type"));
                //列注释
                tableInfo.setColumnComment(rs.getString("column_comment"));
                //主键
                tableInfo.setColumnKey(rs.getString("column_key"));
                //主键类型
                tableInfo.setExtra(rs.getString("extra"));
                list.add(tableInfo);
            }

            //表注释
            sql = "select table_comment from information_schema.tables where table_schema = (select database()) and table_name=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            while (rs.next()) {
                //表注释
                tableComment = rs.getString("table_comment");
            }
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            if(rs != null){
                DBConnectionUtil.close(conn, ps, rs);
            }
        }
        return list;
    }

    public static void main(String[] args) {
//        createWord();
//        createExcel();
//        autoGenerator("tb_user");
    }
}

 

　　后记

　　通过FreeMarker，按照固定格式，快速生成Word、Excel文档，或者生成代码，简单高效。

　　生成文档，数据来源可能是直接读库获取，也有可能是要用户在页面上填写，再传入后台，这时候就可以将我们的模板文件，另存为html格式，小调整之后就可以展示给用户，最大程度保证了用户看到的文档页面跟生成、导出的文档格式是一致的。

 

　　更新

　　2022-02-17更新：调整word的xml模板表格单元格宽度，修改网格值即可：<w:tcPr> -> <w:gridSpan w:val="3"/>