踹掉後端，前端匯出Excel！

qch發表於2022-04-15
後端前端Excel

　　前言

　　匯出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格式的特性，這個特性不僅前端可以利用，後端也一樣可以，這裡也分享一下後端工具類，原理都是一樣的

踹掉後端，前端匯出Excel！ 
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);

            //帶換行符：&#10;
            HashMap<String, Object> hashMap2 = new HashMap<>();
            hashMap2.put("id","A002");
            hashMap2.put("name","李四&#10;李四1&#10;李四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);
        }
     */
}
View Code

相關文章