V2AS问路
踹掉后端，前端导出Excel！
阅读原文时间：2022年04月15日阅读：1

　　导出Excel文件这个功能，通常都是在后端实现返回前端一个下载链接，但有时候我们只想导出前端页面上已经有了的数据，不想再调后端导出接口浪费服务器资源，学习本文demo例子，我们踹掉后端，直接在前端导出Excel！

　　1、利用Blob对象构造一个a标签的href链接，从而实现文件下载，Excel支持html格式，因此我们只需要将构造好的html内容放到Blob对象中，即可下载Excel表格

　　2、利用base64编码构造一个a标签的href链接，从而实现文件下载，同上，我们需要将构造好的html内容URI编码拼到base64链接，即可下载Excel表格

//blob、base64转文件下载，通过A标签模拟点击,设置文件名  
/\*  
    万能流  application/octet-stream  
    word文件  application/msword  
    excel文件  application/vnd.ms-excel  
    txt文件  text/plain  
    图片文件  image/png、jpeg、gif、bmp  
 \*/  
function downloadByBlob(fileName, text) {  
    let a = document.createElement("a");  
    a.href = URL.createObjectURL(new Blob(\[text\], {type: "application/octet-stream"}));  
    a.download = fileName || 'Blob导出测试.txt';  
    a.click();  
    a.remove();  
    URL.revokeObjectURL(a.href);  
}  
function downloadByBase64(fileName, text) {  
    let a = document.createElement('a');  
    a.href = 'data:application/octet-stream;base64,' + window.btoa(unescape(encodeURIComponent(text)));  
    a.download = fileName || 'Base64导出测试.txt';  
    a.click();  
    a.remove();  
    URL.revokeObjectURL(a.href);  
}

　　封装导出Excel表格方法

//踹掉后端，前端导出Excel！  
function exportExcel(fileName,columns,datas){  
    //列名  
    let columnHtml = "";  
    columnHtml += "<tr style=\\"text-align: center;\\">\\n";  
    for (let key in columns) {  
        columnHtml += "<td style=\\"background-color:#bad5fd\\">"+columns\[key\]+"</td>\\n";  
    }  
    columnHtml += "</tr>\\n";

    //数据  
    let dataHtml = "";  
    for (let data of datas) {  
        dataHtml += "<tr style=\\"text-align: center;\\">\\n";  
        for (let key in columns) {  
            dataHtml += "<td>"+data\[key\]+"</td>\\n";  
        }  
        dataHtml += "</tr>\\n";  
    }

    //完整html  
    let excelHtml = "<html xmlns:o=\\"urn:schemas-microsoft-com:office:office\\"\\n" +  
        "      xmlns:x=\\"urn:schemas-microsoft-com:office:excel\\"\\n" +  
        "      xmlns=\\"http://www.w3.org/TR/REC-html40\\">\\n" +  
        "<head>\\n" +  
        "   <!-- 加这个，其他单元格带边框 -->" +  
        "   <xml>\\n" +  
        "        <x:ExcelWorkbook>\\n" +  
        "            <x:ExcelWorksheets>\\n" +  
        "                <x:ExcelWorksheet>\\n" +  
        "                    <x:Name></x:Name>\\n" +  
        "                    <x:WorksheetOptions>\\n" +  
        "                        <x:DisplayGridlines/>\\n" +  
        "                    </x:WorksheetOptions>\\n" +  
        "                </x:ExcelWorksheet>\\n" +  
        "            </x:ExcelWorksheets>\\n" +  
        "        </x:ExcelWorkbook>\\n" +  
        "   </xml>\\n" +  
        "   <style>td{font-family: \\"宋体\\";}</style>\\n" +  
        "</head>\\n" +  
        "<body>\\n" +  
        "<table border=\\"1\\">\\n" +  
        "    <thead>\\n" +  
        columnHtml +  
        "    </thead>\\n" +  
        "    <tbody>\\n" +  
        dataHtml +  
        "    </tbody>\\n" +  
        "</table>\\n" +  
        "</body>\\n" +  
        "</html>";

    //下载  
    downloadByBlob((fileName || "导出Excel")+".xls",excelHtml);  
}

　　导出txt文档

downloadByBlob("downloadByBlob-导出txt文档.txt","downloadByBlob\n导出txt简单测试\n");
downloadByBase64("downloadByBase64-导出txt文档.txt","downloadByBase64\n导出txt简单测试\n");

  

　　导出Excel表格

exportExcel("xx业务Excel导出", {"id": "编号", "name": "名字", "age": "年龄", "time": "参加工作时间"}, [{
"id": "A001",
"name": "张三",
"age": "18",
"time": new Date().toLocaleString()
},{
"id": "A002",
"name": "李四",
"age": "20",
"time": new Date().toLocaleString()
}]);

　　导出word文档也是一样

　　参考上我们之前的《FreeMarker模板引擎》，先画好我们想要的文档格式然后转成xml，调用我们封装好的方法，将构造好的xml内容转成文件，实现前端导出复杂格式文档！

　　如果有复杂数据，建议还是在后端操作，当然你也可以把数据返回前端在前端导出也行

　　前端导出Excel主要是利用Bolb、base64，以及Excel支持html格式的特性，这个特性不仅前端可以利用，后端也一样可以，这里也分享一下后端工具类，原理都是一样的

package cn.huanzi.qch.util;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.*;

/**
* Excel工具类
*/
public class ExcelUtil {

/\*\*  
 \* 导出  
 \* 无需依赖POI  
 \*/  
/\*  
    示例：  
    try {  
        //列名  
        LinkedHashMap<String, String> columns = new LinkedHashMap<>(4);  
        columns.put("id","编号");  
        columns.put("name","名字");  
        columns.put("age","年龄");  
        columns.put("time","参加工作时间");

        //数据  
        List<Map<String, Object>> datas = new ArrayList<>(3);  
        HashMap<String, Object> hashMap = new HashMap<>();  
        hashMap.put("id","A001");  
        hashMap.put("name","张三");  
        hashMap.put("age",18);  
        hashMap.put("time",new Date());  
        datas.add(hashMap);

        //带换行符：

        HashMap<String, Object> hashMap2 = new HashMap<>();  
        hashMap2.put("id","A002");  
        hashMap2.put("name","李四

李四1
李四2");
hashMap2.put("age",20);
hashMap2.put("time",new Date());
datas.add(hashMap2);

        HashMap<String, Object> hashMap3 = new HashMap<>();  
        hashMap3.put("id","A003");  
        hashMap3.put("name","王五");  
        hashMap3.put("age",25);  
        hashMap3.put("time",new Date());  
        datas.add(hashMap3);

        //导出  
        ExcelUtil.exportByResponse(this.getResponse(),"Excel导出测试",columns,datas);  
        //ExcelUtil.exportByFile(new File("D:\\\\XFT User\\\\Downloads\\\\Excel导出测试.xls"),columns,datas);  
    } catch (Exception e) {  
        e.printStackTrace();  
    }  
 \*/  
public static void exportByResponse(HttpServletResponse response, String fileName, LinkedHashMap<String, String> columns, List<Map<String, Object>> datas) throws Exception {  
    response.addHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");  
    response.setContentType("application/ms-excel");

    StringBuilder sb = exportOfData(columns, datas);

    OutputStream out = response.getOutputStream();  
    out.write(sb.toString().getBytes("UTF-8"));  
    out.flush();  
    out.close();  
}

public static void exportByFile(File file, LinkedHashMap<String, String> columns, List<Map<String, Object>> datas) {  
    StringBuilder sb = exportOfData(columns, datas);

    try (PrintWriter myFile = new PrintWriter(file,"UTF-8")) {  
        myFile.println(sb);  
    } catch (Exception e) {  
        System.err.println("exportByFile()，操作出错...");  
        e.printStackTrace();  
    }  
    System.out.println(file.getName() + "，操作完成！");  
}

//其他单元格无边框  
private static StringBuilder exportOfData(LinkedHashMap<String, String> columns, List<Map<String, Object>> datas) {  
    StringBuilder sb = new StringBuilder("<html xmlns:o=\\"urn:schemas-microsoft-com:office:office\\"" +  
            "      xmlns:x=\\"urn:schemas-microsoft-com:office:excel\\"" +  
            "      xmlns=\\"http://www.w3.org/TR/REC-html40\\">");

    //加这个，其他单元格带边框  
    sb.append("<head>" +  
            "    <xml>" +  
            "        <x:ExcelWorkbook>" +  
            "            <x:ExcelWorksheets>" +  
            "                <x:ExcelWorksheet>" +  
            "                    <x:Name></x:Name>" +  
            "                    <x:WorksheetOptions>" +  
            "                        <x:DisplayGridlines/>" +  
            "                    </x:WorksheetOptions>" +  
            "                </x:ExcelWorksheet>" +  
            "            </x:ExcelWorksheets>" +  
            "        </x:ExcelWorkbook>" +  
            "    </xml>" +  
            "   <style>td{font-family: \\"宋体\\";}</style>" +  
            "</head>");

    sb.append("<body>");

    sb.append("<table border=\\"1\\">");

    //列名  
    sb.append("<tr style=\\"text-align: center;\\">");  
    for (Map.Entry<String, String> entry : columns.entrySet()) {  
        sb.append("<td style=\\"background-color:#bad5fd\\">" + entry.getValue() + "</td>");  
    }  
    sb.append("</tr>");

    //数据  
    for (Map<String, Object> data : datas) {  
        sb.append("<tr style=\\"text-align: center;\\">");  
        for (Map.Entry<String, String> entry : columns.entrySet()) {  
            Object dataValue = data.get(entry.getKey());

            //如果是日期类型  
            if (dataValue instanceof java.util.Date) {  
                dataValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dataValue);  
            }  
            sb.append("<td>" + dataValue.toString() + "</td>");  
        }  
        sb.append("</tr>");  
    }

    sb.append("</table>");

    sb.append("</body>");

    sb.append("</html>");

    return sb;  
}

//前端导出Excel  
/\*  
    示例：  
     exportExcel("xx业务Excel导出", {"id": "编号", "name": "名字", "age": "年龄", "time": "参加工作时间"}, \[{  
        "id": "A001",  
        "name": "张三",  
        "age": "18",  
        "time": new Date().toLocaleString()  
    },{  
        "id": "A002",  
        "name": "李四",  
        "age": "20",  
        "time": new Date().toLocaleString()  
    }\]);  
 \*/  
/\*  
    //blob、base64转文件下载，通过A标签模拟点击,设置文件名  
    //万能流  application/octet-stream  
    //word文件  application/msword  
    //excel文件  application/vnd.ms-excel  
    //txt文件  text/plain  
    //图片文件  image/png、jpeg、gif、bmp  
    function downloadByBlob(fileName, text) {  
        let a = document.createElement("a");  
        a.href = URL.createObjectURL(new Blob(\[text\], {type: "application/octet-stream"}));  
        a.download = fileName || 'Blob导出测试.txt';  
        a.click();  
        a.remove();  
        URL.revokeObjectURL(a.href);  
    }  
    function downloadByBase64(fileName, text) {  
        let a = document.createElement('a');  
        a.href = 'data:application/octet-stream;base64,' + window.btoa(unescape(encodeURIComponent(text)));  
        a.download = fileName || 'Base64导出测试.txt';  
        a.click();  
        a.remove();  
        URL.revokeObjectURL(a.href);  
    }

    //踹掉后端，前端导出Excel！  
    function exportExcel(fileName,columns,datas){  
        //列名  
        let columnHtml = "";  
        columnHtml += "<tr style=\\"text-align: center;\\">\\n";  
        for (let key in columns) {  
            columnHtml += "<td style=\\"background-color:#bad5fd\\">"+columns\[key\]+"</td>\\n";  
        }  
        columnHtml += "</tr>\\n";

        //数据  
        let dataHtml = "";  
        for (let data of datas) {  
            dataHtml += "<tr style=\\"text-align: center;\\">\\n";  
            for (let key in columns) {  
                dataHtml += "<td>"+data\[key\]+"</td>\\n";  
            }  
            dataHtml += "</tr>\\n";  
        }

        //完整html  
        let excelHtml = "<html xmlns:o=\\"urn:schemas-microsoft-com:office:office\\"\\n" +  
                "      xmlns:x=\\"urn:schemas-microsoft-com:office:excel\\"\\n" +  
                "      xmlns=\\"http://www.w3.org/TR/REC-html40\\">\\n" +  
                "<head>\\n" +  
                "   <!-- 加这个，其他单元格带边框 -->" +  
                "   <xml>\\n" +  
                "        <x:ExcelWorkbook>\\n" +  
                "            <x:ExcelWorksheets>\\n" +  
                "                <x:ExcelWorksheet>\\n" +  
                "                    <x:Name></x:Name>\\n" +  
                "                    <x:WorksheetOptions>\\n" +  
                "                        <x:DisplayGridlines/>\\n" +  
                "                    </x:WorksheetOptions>\\n" +  
                "                </x:ExcelWorksheet>\\n" +  
                "            </x:ExcelWorksheets>\\n" +  
                "        </x:ExcelWorkbook>\\n" +  
                "   </xml>\\n" +  
                "   <style>td{font-family: \\"宋体\\";}</style>\\n" +  
                "</head>\\n" +  
                "<body>\\n" +  
                "<table border=\\"1\\">\\n" +  
                "    <thead>\\n" +  
                columnHtml +  
                "    </thead>\\n" +  
                "    <tbody>\\n" +  
                dataHtml +  
                "    </tbody>\\n" +  
                "</table>\\n" +  
                "</body>\\n" +  
                "</html>";

        //下载  
        downloadByBlob((fileName || "导出Excel")+".xls",excelHtml);  
    }  
 \*/

}

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章