踹掉后端，前端导出Excel
Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说踹掉后端，前端导出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格式的特性，这个特性不仅前端可以利用，后端也一样可以，这里也分享一下后端工具类，原理都是一样的
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);
}
*/
}版权声明
作者：huanzi-qch
出处：https://www.cnblogs.com/huanzi-qch
若标题中有“转载”字样，则本文版权归原作者所有。若无转载字样，本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接，否则保留追究法律责任的权利.
今天的分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。
