根据数据导出指定格式文件

本文主要介绍目录导出类型为sql，excel，word，以及将多个word打包为zip的功能

前言

本文主要介绍的是基于服务端导出文件到本地的功能，而不是直接下载到本地，直接下载到本地跟实际业务差距很大

导出sql

目前导出sql功能支持mysql和pgsql，创建sql语句代码如下，主要通过反射获取对应的属性，注释，长度，并且通过StringBuffer拼接生成对应的sql语句

 /**
     *
     * @param type 导出的sql类型
     * @param list 数据集合
     * @param person 建表模板实体类
     * @param <T> 实体类
     * @return 建表语句
     */
    public static <T> StringBuffer getCreateSql(String type, List<T> list,Class<T> person) {
        String tableName = "tb_" +person.getSimpleName().toLowerCase();
        //1、获取指定的属性
        Field[] fields = person.getDeclaredFields();
        StringBuffer column = new StringBuffer();


        StringBuffer sql = new StringBuffer();
        if (type.toUpperCase().equals("MYSQL")) {
            for (Field field : fields) {
                // 2、通过getAnnotation⽅法获取该属性上指定的注解，注意给的参数
                ColumnInfo annotation = field.getAnnotation(ColumnInfo.class);

                //3、获取注解中的值
                column.append("\n " + annotation.column() + " " + annotation.type() + "(" + annotation.length() + ")" + " default null comment " + "'" + annotation.columnName() + "'" + ",");


            }
            column.replace(column.length() - 1, column.length(), "");
            sql.append("\n DROP TABLE IF EXISTS `" + tableName + "`; ")
                    .append(" \n CREATE TABLE `" + tableName + "`  (")
                    .append(" \n id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,")
                    .append(column)
                    .append(" \n ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci;\n");
        }
        if (type.toUpperCase().equals("PGSQL")) {
            // 字段注释
            StringBuffer comment = new StringBuffer();

            for (Field field : fields) {
                // 2、通过getAnnotation⽅法获取该属性上指定的注解，注意给的参数
                ColumnInfo annotation = field.getAnnotation(ColumnInfo.class);

                //3、获取注解中的值
                if (!annotation.type().equals("int") && !annotation.type().equals("bigint")) {
                    column.append("\n " + annotation.column() + " " + annotation.type() + "(" + annotation.length() + ")" + " default null " + ",");

                } else {
                    column.append("\n " + annotation.column() + " " + annotation.type() +  " default null " + ",");

                }
                comment.append("comment on column "+tableName+"." + annotation.column() + " is " + "'"+annotation.columnName()+ "'"+ ";\n");

            }
            column.replace(column.length() - 1, column.length(), "");
            sql.append("\n DROP TABLE IF EXISTS " + tableName + "; ")
                    .append(" \n CREATE TABLE " + tableName + "  (")
                    .append(" \n id serial NOT NULL  PRIMARY KEY ,")
                    .append(column)
                    .append(");\n")
                    .append("\n"+comment+ "\n");
        }
        for (T user : list) {
            Class<?> aClass = user.getClass();
            StringBuffer filedTypes = new StringBuffer();
            StringBuffer filedValues = new StringBuffer();
            for (Field field : aClass.getDeclaredFields()) {
                field.setAccessible(true);
                filedTypes.append(field.getAnnotation(ColumnInfo.class).column() + ",");
                try {
                    if (field.getAnnotation(ColumnInfo.class).type().equals("varchar")) {
                        filedValues.append("'"+field.get(user) + "'" + ",");
                    }else {
                        filedValues.append(field.get(user) + ",");
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }

            filedTypes.replace(filedTypes.length() - 1, filedTypes.length(), "");
            filedValues.replace(filedValues.length() - 1, filedValues.length(), "");

            sql.append("insert into " + tableName + "(" + filedTypes + ")" + " values" + "(" + filedValues + ");\n");

        }
        return sql;
    }

说明：以上代码中使用到了ColumnInfo类，ColumnInfo是自定义的注解，主要用来设置属性对应sql中的类型，长度，字段名

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public@interface ColumnInfo {
    String columnName();
    String type();
    String column();
    int length();
}

导出sql的实体类
其中columnName 代表 注释，type 代表类型，column 代表对应字段名，length代表长度

@Data
@ToString
public class ExportResourceCatalogueDetail {

    /**
     * 数据资源目录名称
     */
    @ExcelProperty(value = "数据资源目录名称",index = 0)
    @ColumnInfo(columnName = "数据资源目录名称", type = "varchar", column = "res_name", length = 255)
    private String resName;

    /**
     * 目录分类
     */
    @ExcelProperty(value = "目录分类",index = 1)
    @ColumnInfo(columnName = "目录分类", type = "bigint", column = "category_id", length = 20)
    private Long categoryId;

    /**
     * 目录分类代码(分类code)
     */
    @ExcelProperty(value = "目录分类代码(分类code)",index = 2)
    @ColumnInfo(columnName = "目录分类代码", type = "varchar", column = "category_code", length = 255)
    private String categoryCode;

    /**
     * 资源提供方
     */

    @ExcelProperty(value = "资源提供方",index = 3)
    @ColumnInfo(columnName = "资源提供方", type = "varchar", column = "supplier_cn", length = 255)
    private String supplierCN;

    /**
     * 数据资源编码(资源code)
     */
    @ExcelProperty(value = "数据资源编码(资源code)",index = 4)
    @ColumnInfo(columnName = "数据资源编码", type = "varchar", column = "res_code", length = 255)
    private String resCode;

    /**
     * 资源类型(挂载数据类型, 0-库表, 1-文件) 资源数量
     */
    @ExcelProperty(value = "资源类型", index = 5)
    @ColumnInfo(columnName = "资源类型", type = "varchar", column = "resType_cn", length = 255)
    private String resTypeCN;


    /**
     * 编目类型(1-手工,2-机器)
     */
    @ExcelProperty(value = "编目类型(1-手工,2-机器)", index = 6)
    @ColumnInfo(columnName = "编目类型", type = "varchar", column = "origin_type_cn", length = 255)
    private String originTypeCN;
    /**
     * 数据库类型
     */
    @ExcelProperty(value = "数据源类型", index = 7)
    @ColumnInfo(columnName = "数据源类型", type = "varchar", column = "source_type_cn", length = 255)
    private String sourceTypeCN;

    /**
     * 数据库名
     */
    @ExcelProperty(value = "数据库名",index = 8)
    @ColumnInfo(columnName = "数据库名", type = "varchar", column = "schema_name", length = 255)
    private String schemaName;

    /**
     * 表名
     */
    @ExcelProperty(value = "表名",index = 9)
    @ColumnInfo(columnName = "表名", type = "varchar", column = "data_table", length = 255)
    private String dataTable;

    /**
     *  数据资源描述
     */
    @ExcelProperty(value = "数据资源描述",index = 10)
    @ColumnInfo(columnName = "数据资源描述", type = "varchar", column = "res_desc", length = 255)
    private String resDesc;

    /**
     * 共享属性
     */
    @ExcelProperty(value = "共享属性",index = 11)
    @ColumnInfo(columnName = "共享属性", type = "varchar", column = "sharing_type_cn", length = 255)
    private String sharingTypeCN;

    /**
     * 共享条件说明
     */
    @ExcelProperty(value = "共享条件/不予共享依据",index = 12)
    @ColumnInfo(columnName = "共享条件说明", type = "varchar", column = "sharing_condintion", length = 255)
    private String sharingCondintion;



    /**
     * 使用者数量
     */
    @ExcelProperty(value = "使用者数量",index = 13)
    @ColumnInfo(columnName = "使用者数量", type = "int", column = "sub_num", length = 2)
    private Integer subNum;

    /**
     * 是否向社会开放
     */

    /**
     * 开放条件说明
     */

    /**
     *  关联业务场景描述
     */
    @ExcelProperty(value = "关联业务场景描述",index = 14)
    @ColumnInfo(columnName = "关联业务场景描述", type = "varchar", column = "business_scenario", length = 255)
    private String businessScenario;


    /**
     * 同步机制
     */
    @ExcelProperty(value = "同步机制",index = 15)
    @ColumnInfo(columnName = "同步机制", type = "varchar", column = "sync_mechanism_cn", length = 255)
    private String syncMechanismCN;

    /**
     * 更新周期
     */
    @ExcelProperty(value = "更新周期", index = 16)
    @ColumnInfo(columnName = "更新周期", type = "varchar", column = "update_interval_cn", length = 255)
    private String updateIntervalCN;

    /**
     * 信息资源信息(库表)
     */
    @ExcelIgnore
    @ColumnInfo(columnName = "信息资源信息(库表)", type = "varchar", column = "colDetail_cn", length = 255)
    private String colDetailCN;

    /**
     * 开放类型(是否向社会开放)
     */
    @ExcelProperty(value = "开放类型(是否向社会开放)",index = 17)
    @ColumnInfo(columnName = "开放类型(是否向社会开放)", type = "varchar", column = "open_type_cn", length = 255)
    private String openTypeCN;

    /**
     * 开放条件说明
     */
    @ExcelProperty(value = "开放条件说明",index = 18)
    @ColumnInfo(columnName = "开放条件说明", type = "varchar", column = "open_condintion", length = 255)
    private String openCondintion;


}

通过以上的方法生成sql语句后，将sql语句作为参数传入到以下方法，即可导出sql脚本

 /**
     * 将生成等String字符串 写进sql文件
     *
     * @param str  String字符串
     * @param fileName sql文件名
     */
    public static void StringToSql(String str, String fileName, HttpServletResponse response) {

        try{
            response.reset();
            // 指定下载的文件名--设置响应头
            response.setCharacterEncoding("UTF-8");
            response.addHeader("charset", "UTF-8");

            String encode = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());

            response.setHeader("Content-Disposition", "attachment;filename=" + encode);
            response.setContentType("application/octet-stream");
            response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.addHeader("Access-Control-Allow-Headers", "Content-Disposition");


            byte[] sourceByte = str.getBytes();
            OutputStream os = null;
            if (null != sourceByte) {
                try {
                    os = response.getOutputStream();
                    os.write(sourceByte);
                    os.flush();
                    System.out.println("生成成功!!");
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    // 关闭文件输出流
                    try {
                        if (os != null) {
                            os.close();
                        }
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }

总结：导出sql脚本基本完成，后续会进行更新维护，并且反射获取属性值目前必须每个属性都加上@ColumnInfo注解，不加上会报错，后续会进行修改，整体的代码就如上所示

导出Excel

本文使用EasyExcel导出Excel，该框架能够快速的构建Excel文件，直接将数据传入即可，代码如下：

  /**
     * 下载excel文件
     * 临时，文件导出
     *
     * @param response
     * @param fileName 文件名
     */
    protected void downLoadSourceExcel(HttpServletResponse response, String fileName, String sheetName, List list, Class tClass) {
        try {
            log.info("start_downLoading");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.addHeader("charset", "UTF-8");
            response.addHeader("Pragma", "no-cache");
//            String encodeName = null;
//            encodeName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition", "attachment; filename=" + "export.xlsx");
            EasyExcel.write(response.getOutputStream(), tClass).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).doWrite(list);
            log.info("end_downLoaded");
        } catch (IOException e) {
            log.error("Excel导出失败", e);
        }
    }

导出Word

Word导出是使用WordExportUtil.exportWord07方法，直接根据模板将数据填充到模板中
，语法为{{对应的变量名}}

代码如下：

    /**
     * 下载word文件(生成单个word文档，多个表格数据)
     * 临时，文件导出
     *
     * @param response
     * @param fileName 文件名
     */

    protected void downLoadSourceWord(HttpServletResponse response, String fileName, String modelName, List list,HttpServletRequest request) {
        try {
            log.info("start_downLoaded");

            List docList = new ArrayList();
            //准备数据,一个list对应一页doc的文档

            List<Map<String, Object>> maps = MapUtil.toListMap(list);
            for (Map<String, Object> map : maps) {
                docList.add(map);

            }
            //重置响应对象
            response.reset();
            // 指定下载的文件名--设置响应头
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + "export"+".doc");
            response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.addHeader("Access-Control-Allow-Headers", "Content-Disposition");

            XWPFDocument doc = WordExportUtil.exportWord07("doc/template.doc",docList);
            doc.write(response.getOutputStream());
            doc.close();
            log.info("end_downLoaded");
        } catch (Exception e) {
            log.error("Word导出失败", e);
        }
    }

注意：这里是将List<实体类>转换为List<Map<String, Object>>集合的k为属性名，v为属性值的数据，再将数据保存到list中，最后将list作为参数传到方法中
"doc/template.doc"为resource路径下的模板文件地址，doclist保存了map集合

WordExportUtil.exportWord07("doc/template.doc",docList);

导出Zip

首先，导出zip先要生成多个word再将word导出为zip形式，这就要将文件保存到一个临时的文件目录，最后再根据这个目录导出zip。
这就带来了一个问题，文件目录地址肯定不能写死，只能动态获取，不然可移植性会很差。
但是，想要获取项目的绝对路径，也是有环境限制的，这里主要说明idea运行项目和jar包运行路径的区别
如果使用idea启动项目，这时候想获得resource下的文件，可以用以下方法。

this.getClass().getResource("/static/1.doc");

但是，将项目打成jar包然后在cmd中再运行，会出现找不到该路径，由于会在jar包存储路径中生成jar同名文件，此时使用以上方法会找不到该路径文件

此时可以获取jar包的路径
使用以下方法获取，有可能会出现乱码

String path = this.getClass().getProtectionDomain().getCodeSource().getLocation().getFile();
 try {
            path = java.net.URLDecoder.decode(path, "UTF-8");
            log.info("utf-8 path: " + path);
        } catch (java.io.UnsupportedEncodingException ex) {
            log.error(ex.getLocalizedMessage());
        }

解决了路径问题，基本上就没有问题了。
代码如下：

  // 打成jar包路径不能直接通过request.getSession()访问
            String realPath = acquireJarPath();
            String parentPath = realPath + "table";

            try {
                parentPath = java.net.URLDecoder.decode(parentPath, "UTF-8");
            } catch (java.io.UnsupportedEncodingException ex) {
                log.error(ex.getLocalizedMessage());
            }

            File file = new File(parentPath);
            if (!file.exists()) {
                file.mkdir();
            }
            log.info("临时文件路径为：" + parentPath);

            DelAllFileUtil.delAllFile(parentPath);
            int i = 0;
            for (ExportResourceCatalogueDetail exportResourceCatalogueDetail : exportResourceCatalogueDetails) {
                getOneDoc(exportResourceCatalogueDetail, request, parentPath, versionIds.get(i++));
            }
            ZipExportUtil.zipExport(parentPath, response);

以上代码所用到的内部方法以及工具类方法如下：
ZipExportUtil 导入Zip工具类

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import static org.springframework.util.StreamUtils.BUFFER_SIZE;

/**
 * zip打包下载工具
 *
 */
public class ZipExportUtil {
    /**
     * 打包
     * @param path 文件夹路径
     */
    public static void zipExport(String path, HttpServletResponse response){
        try {
            response.setContentType("application/DOWLOAD");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + ("export" + ".zip"));
            ServletOutputStream out = response.getOutputStream();
            toZip(path, out, true);
//            DelAllFileUtil.delAllFile("./src/main/resources/temporary");
        } catch (Exception e) {
            throw new RuntimeException( e.getMessage());
        }
    }
    /**
     * 打压缩包导出
     *
     * @param srcDir
     * @param out
     * @param keepDirStructure
     * @throws RuntimeException
     */
    public static void toZip(String srcDir, OutputStream out, boolean keepDirStructure) throws RuntimeException {
        ZipOutputStream zos = null;
        try {
            zos = new ZipOutputStream(out);
            File sourceFile = new File(srcDir);
            compress(sourceFile, zos, sourceFile.getName(), keepDirStructure);
        } catch (Exception e) {
            throw new RuntimeException("zip error from ZipUtils", e);
        } finally {
            if (zos != null) {
                try {
                    zos.close();
                } catch (IOException e) {
                    throw new RuntimeException( e.getMessage());
                }
            }
        }
    }

    /**
     * 执行压缩
     *
     * @param sourceFile
     * @param zos
     * @param name
     * @param keepDirStructure
     * @throws Exception
     */
    private static void compress(File sourceFile, ZipOutputStream zos, String name, boolean keepDirStructure)
            throws Exception {
        byte[] buf = new byte[BUFFER_SIZE];
        if (sourceFile.isFile()) {
            // 向zip输出流中添加一个zip实体，构造器中name为zip实体的文件的名字
            zos.putNextEntry(new ZipEntry(name));
            // copy文件到zip输出流中
            int len;
            FileInputStream in = new FileInputStream(sourceFile);
            while ((len = in.read(buf)) != -1) {
                zos.write(buf, 0, len);
            }
            // Complete the entry
            zos.closeEntry();
            in.close();
        } else {
            File[] listFiles = sourceFile.listFiles();
            if (listFiles == null || listFiles.length == 0) {
                // 需要保留原来的文件结构时,需要对空文件夹进行处理
                if (keepDirStructure) {
                    // 空文件夹的处理
                    zos.putNextEntry(new ZipEntry(name + "/"));
                    // 没有文件，不需要文件的copy
                    zos.closeEntry();
                }
            } else {
                for (File file : listFiles) {
                    // 判断是否需要保留原来的文件结构
                    if (keepDirStructure) {
                        // 注意：file.getName()前面需要带上父文件夹的名字加一斜杠,
                        // 不然最后压缩包中就不能保留原来的文件结构,即：所有文件都跑到压缩包根目录下了
                        compress(file, zos, name + "/" + file.getName(), keepDirStructure);
                    } else {
                        compress(file, zos, file.getName(), keepDirStructure);
                    }
                }
            }
        }
    }
}

获取单个doc文件：

 /**
     * 根据数据目录信息导出单个文件（单个表格数据）
     *
     * @param user       保存数据的实体类
     * @param request    请求
     * @param parentPath 指定生成的临时文件路径
     */
    protected void getOneDoc(ExportResourceCatalogueDetail user, HttpServletRequest request, String parentPath, Long versionId) {

        try {
            log.info("start_downLoaded");

            List docList = new ArrayList();
            Map<String, Object> map = MapUtil.toMap(user);
            docList.add(map);

            String fileName = parentPath + "/" + versionId + ".docx";
            log.info("生成的文件路径为：" + fileName);
            FileOutputStream fileOutputStream = new FileOutputStream(fileName);

            //读取模板,放置数据,然后从response获取输出流导出
            XWPFDocument doc = WordExportUtil.exportWord07("doc/template.doc", docList);
            doc.write(fileOutputStream);
            doc.close();
            log.info("end_downLoaded");
        } catch (Exception e) {
            log.error("Word导出失败", e);
        }
    }

获取jar包的绝对路径：

  /**
     * 获取jar包中的绝对路径
     *
     * @return jar包中的绝对路径
     */
    protected String acquireJarPath() {
        //jar包编译后的相对路径
        String path = this.getClass().getProtectionDomain().getCodeSource().getLocation().getFile();
        //消除乱码
        try {
            path = java.net.URLDecoder.decode(path, "UTF-8");
        } catch (java.io.UnsupportedEncodingException ex) {
            log.error(ex.getLocalizedMessage());
        }
        //根据路径获取目标文件
        java.io.File jarFile = new java.io.File(path);
        //获取文件的绝对路径
        String jarFilepath = jarFile.getAbsolutePath();
        //输出内容：//jarFilepath:  C:\Users\Mr.Tan\Documents\IT\work\HB\danger-backend\danger-client\cipher-client\target\file:\C:\Users\Mr.Tan\Documents\IT\work\HB\danger-backend\danger-client\cipher-client\target\cipher-client.jar!\BOOT-INF\classes!
        //我们需要得到 file 的上级目录
        int end = jarFilepath.indexOf("file");
        if (end > 0) {
            return jarFilepath.substring(0, end);
            //输出结果：C:\Users\Mr.Tan\Documents\IT\work\HB\danger-backend\danger-client\cipher-client\target\
        }
        return jarFilepath + "\\";
    }

详细代码可以参考以下连接：
导出文件详细代码