Javascript JSON 到 Excel 文件下载
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28892885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Javascript JSON to Excel file download
提问by Elango
I have Json data and i need convert json data to Excel file using javascript,
我有 Json 数据，我需要使用 javascript 将 json 数据转换为 Excel 文件，
Reference URL : http://jsfiddle.net/hybrid13i/JXrwM/
参考网址：http: //jsfiddle.net/hybrid13i/JXrwM/
i am using this code:
我正在使用此代码：
function JSONToTSVConvertor(JSONData, ReportTitle, ShowLabel, myTemplateName){
//If JSONData is not an object then JSON.parse will parse the JSON string in an Object
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var TSV = '';
//Set Report title in first row or line
//TSV += ReportTitle + '\r\n\n';
//This condition will generate the Label/Header
if (ShowLabel) {
var row = "";
//This loop will extract the label from 1st index of on array
for (var index in arrData[0]) {
//Now convert each value to string and tab-seprated
row += index + ' ';
}
row = row.slice(0, -1);
//append Label row with line break
TSV += row + '\r\n';
}
//1st loop is to extract each row
for (var i = 0; i < arrData.length; i++) {
var row = "";
//2nd loop will extract each column and convert it in string tab-seprated
for (var index in arrData[i]) {
row += '"' + arrData[i][index] + '" ';
}
row.slice(0, row.length - 1);
//add a line break after each row
TSV += row + '\r\n';
}
if (TSV == '') {
alert("Invalid data");
return;
}
var blob = new Blob([TSV], {type: "data:text/tsv;charset=utf-8"});
//Generate a file name
var fileName = myTemplateName;
//this will remove the blank-spaces from the title and replace it with an underscore
fileName += ReportTitle.replace(/ /g,"_");
saveAs(blob, ""+fileName+".tsv");
}
this sample code work to csv and tsv format. and i need to Excel format i don't think any idea please help me. pls suggest some example code. Thanks...
此示例代码适用于 csv 和 tsv 格式。我需要 Excel 格式我不认为有任何想法请帮助我。请建议一些示例代码。谢谢...
回答by Dmitry
CSV, as said, is excel file itself. But, in many locales, csv generated by the script above is opened incorrectly, where excel puts everything into 1 cell. Small modification of original script helps: just replace header with "sep=,".
如前所述，CSV 是 excel 文件本身。但是，在许多语言环境中，上面脚本生成的 csv 被错误地打开，其中 excel 将所有内容放入 1 个单元格中。对原始脚本的小修改有帮助：只需将标题替换为“sep=,”。
var CSV = 'sep=,' + '\r\n\n';
Working example with change here: https://jsfiddle.net/1ecj1rtz/.
此处更改的工作示例：https: //jsfiddle.net/1ecj1rtz/。
Spent some time figuring this out, and therefore answering old thread to help others save some time.
花了一些时间来解决这个问题，因此回答旧线程以帮助其他人节省一些时间。
回答by M.A.K. Ripon
I've created a class to export json data to excel file. I'll be happy if some productive edit is made in my code.
我创建了一个类来将 json 数据导出到 excel 文件。如果在我的代码中进行了一些富有成效的编辑，我会很高兴。
Just add the class in your JS library and call:
只需在您的 JS 库中添加该类并调用：
var myTestXML = new myExcelXML(myJsonArray);
myTestXML.downLoad();
My myExcelXML Class:
我的 myExcelXML 类：
let myExcelXML = (function() {
let Workbook, WorkbookStart = '<?xml version="1.0"?><ss:Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">';
const WorkbookEnd = '</ss:Workbook>';
let fs, SheetName = 'SHEET 1',
styleID = 1, columnWidth = 80,
fileName = "Employee_List", uri, link;
class myExcelXML {
constructor(o) {
let respArray = JSON.parse(o);
let finalDataArray = [];
for (let i = 0; i < respArray.length; i++) {
finalDataArray.push(flatten(respArray[i]));
}
let s = JSON.stringify(finalDataArray);
fs = s.replace(/&/gi, '&');
}
downLoad() {
const Worksheet = myXMLWorkSheet(SheetName, fs);
WorkbookStart += myXMLStyles(styleID);
Workbook = WorkbookStart + Worksheet + WorkbookEnd;
uri = 'data:text/xls;charset=utf-8,' + encodeURIComponent(Workbook);
link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = fileName + ".xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
get fileName() {
return fileName;
}
set fileName(n) {
fileName = n;
}
get SheetName() {
return SheetName;
}
set SheetName(n) {
SheetName = n;
}
get styleID() {
return styleID;
}
set styleID(n) {
styleID = n;
}
}
const myXMLStyles = function(id) {
let Styles = '<ss:Styles><ss:Style ss:ID="' + id + '"><ss:Font ss:Bold="1"/></ss:Style></ss:Styles>';
return Styles;
}
const myXMLWorkSheet = function(name, o) {
const Table = myXMLTable(o);
let WorksheetStart = '<ss:Worksheet ss:Name="' + name + '">';
const WorksheetEnd = '</ss:Worksheet>';
return WorksheetStart + Table + WorksheetEnd;
}
const myXMLTable = function(o) {
let TableStart = '<ss:Table>';
const TableEnd = '</ss:Table>';
const tableData = JSON.parse(o);
if (tableData.length > 0) {
const columnHeader = Object.keys(tableData[0]);
let rowData;
for (let i = 0; i < columnHeader.length; i++) {
TableStart += myXMLColumn(columnWidth);
}
for (let j = 0; j < tableData.length; j++) {
rowData += myXMLRow(tableData[j], columnHeader);
}
TableStart += myXMLHead(1, columnHeader);
TableStart += rowData;
}
return TableStart + TableEnd;
}
const myXMLColumn = function(w) {
return '<ss:Column ss:AutoFitWidth="0" ss:Width="' + w + '"/>';
}
const myXMLHead = function(id, h) {
let HeadStart = '<ss:Row ss:StyleID="' + id + '">';
const HeadEnd = '</ss:Row>';
for (let i = 0; i < h.length; i++) {
const Cell = myXMLCell(h[i].toUpperCase());
HeadStart += Cell;
}
return HeadStart + HeadEnd;
}
const myXMLRow = function(r, h) {
let RowStart = '<ss:Row>';
const RowEnd = '</ss:Row>';
for (let i = 0; i < h.length; i++) {
const Cell = myXMLCell(r[h[i]]);
RowStart += Cell;
}
return RowStart + RowEnd;
}
const myXMLCell = function(n) {
let CellStart = '<ss:Cell>';
const CellEnd = '</ss:Cell>';
const Data = myXMLData(n);
CellStart += Data;
return CellStart + CellEnd;
}
const myXMLData = function(d) {
let DataStart = '<ss:Data ss:Type="String">';
const DataEnd = '</ss:Data>';
return DataStart + d + DataEnd;
}
const flatten = function(obj) {
var obj1 = JSON.parse(JSON.stringify(obj));
const obj2 = JSON.parse(JSON.stringify(obj));
if (typeof obj === 'object') {
for (var k1 in obj2) {
if (obj2.hasOwnProperty(k1)) {
if (typeof obj2[k1] === 'object' && obj2[k1] !== null) {
delete obj1[k1]
for (var k2 in obj2[k1]) {
if (obj2[k1].hasOwnProperty(k2)) {
obj1[k1 + '-' + k2] = obj2[k1][k2];
}
}
}
}
}
var hasObject = false;
for (var key in obj1) {
if (obj1.hasOwnProperty(key)) {
if (typeof obj1[key] === 'object' && obj1[key] !== null) {
hasObject = true;
}
}
}
if (hasObject) {
return flatten(obj1);
} else {
return obj1;
}
} else {
return obj1;
}
}
return myExcelXML;
})();
回答by Vikas Bansal
I know its a little late to answer but I have found an nice angularlibrary that does all the hard work it self.
我知道回答有点晚了，但我找到了一个很好的angular图书馆，它可以自己完成所有艰苦的工作。
GITHUB: ngJsonExportExcel
GITHUB：ngJsonExportExcel
Library Direct Download : Download
库直接下载 :下载
Filesaver JS : Download
文件保护 JS :下载
How to use?
如何使用？
- Include the module in you app
- 在您的应用程序中包含该模块
var myapp = angular.module('myapp', ['ngJsonExportExcel'])
var myapp = angular.module('myapp', ['ngJsonExportExcel'])
- Add a export button and use the
ng-json-export-exceldirective and pass data into the directive
- 添加导出按钮并使用
ng-json-export-excel指令并将数据传递到指令中
ng-json-export-excel : it is the directive name
data : it is the data that will be exported (JSON)
report-fields :
pass the column name and the keys that are present in your JSON e.g. customer_name": "Customer Name"
ng-json-export-excel ：它是指令名称
data : 它是将要导出的数据 (JSON)
报告字段：
传递列名和 JSON 中存在的键，例如 customer_name": "Customer Name"
HTML
HTML
<button ng-json-export-excel data="dataList" report-fields="{'uesr.username': 'Heder 1', keyjson2: 'Header 2', keyjson3: 'Head 3'}" filename =" 'export-excel' " separator="," class="css-class"></button>
回答by Eamonn O'Brien-Strain
Excel is a very complex format with many versions. If you really need to do this I would investigate some of the JavaScript libraries that others have written. Do a Google search for "javascript excel writer" to see some examples.
Excel 是一种非常复杂的格式，有很多版本。如果您真的需要这样做，我会调查其他人编写的一些 JavaScript 库。在 Google 上搜索“javascript excel writer”以查看一些示例。
回答by philipeachille
This code snippet is using node.js with the excel4node and express modules in order to convert JSON data to an Excel file and send it to the client, using Javascript.
此代码片段将 node.js 与 excel4node 和 express 模块一起使用，以便使用 Javascript 将 JSON 数据转换为 Excel 文件并将其发送到客户端。
const xl = require('excel4node');
const express = require('express');
const app = express();
var json = [{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road, West Bengal 734013, India","Speed":0},{"Vehicle":"Supra","Date":"30, Jul 2013 07:53 AM","Location":"Sec-45, St. Angel's School, Gurgaon, Haryana, India","Speed":58},{"Vehicle":"Land Cruiser","Date":"30, Jul 2013 09:35 AM","Location":"DLF Phase I, Marble Market, Gurgaon, Haryana, India","Speed":83},{"Vehicle":"Suzuki Swift","Date":"30, Jul 2013 12:02 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Civic","Date":"30, Jul 2013 12:00 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Accord","Date":"30, Jul 2013 11:05 AM","Location":"DLF Phase IV, Super Mart 1, Gurgaon, Haryana, India","Speed":71}]
const createSheet = () => {
return new Promise(resolve => {
// setup workbook and sheet
var wb = new xl.Workbook();
var ws = wb.addWorksheet('Sheet');
// Add a title row
ws.cell(1, 1)
.string('Vehicle')
ws.cell(1, 2)
.string('Date')
ws.cell(1, 3)
.string('Location')
ws.cell(1, 4)
.string('Speed')
// add data from json
for (let i = 0; i < json.length; i++) {
let row = i + 2
ws.cell(row, 1)
.string(json[i].Vehicle)
ws.cell(row, 2)
.date(json[i].Date)
ws.cell(row, 3)
.string(json[i].Location)
ws.cell(row, 4)
.number(json[i].Speed)
}
resolve( wb )
})
}
app.get('/excel', function (req, res) {
createSheet().then( file => {
file.write('ExcelFile.xlsx', res);
})
});
app.listen(3040, function () {
console.log('Excel app listening on port 3040');
});
