Javascript Json To Excel File Download
Solution 1:
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=,".
var CSV = 'sep=,' + '\r\n\n';
Working example with change here: https://jsfiddle.net/1ecj1rtz/.
Spent some time figuring this out, and therefore answering old thread to help others save some time.
Solution 2:
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.
Just add the class in your JS library and call:
var myTestXML = new myExcelXML(myJsonArray);
myTestXML.downLoad();
My myExcelXML Class:
let myExcelXML = (function() {
letWorkbook, 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">';
constWorkbookEnd = '</ss:Workbook>';
let fs, SheetName = 'SHEET 1',
styleID = 1, columnWidth = 80,
fileName = "Employee_List", uri, link;
classmyExcelXML {
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() {
constWorksheet = 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);
}
getfileName() {
return fileName;
}
setfileName(n) {
fileName = n;
}
getSheetName() {
returnSheetName;
}
setSheetName(n) {
SheetName = n;
}
getstyleID() {
return styleID;
}
setstyleID(n) {
styleID = n;
}
}
const myXMLStyles = function(id) {
letStyles = '<ss:Styles><ss:Style ss:ID="' + id + '"><ss:Font ss:Bold="1"/></ss:Style></ss:Styles>';
returnStyles;
}
const myXMLWorkSheet = function(name, o) {
constTable = myXMLTable(o);
letWorksheetStart = '<ss:Worksheet ss:Name="' + name + '">';
constWorksheetEnd = '</ss:Worksheet>';
returnWorksheetStart + Table + WorksheetEnd;
}
const myXMLTable = function(o) {
letTableStart = '<ss:Table>';
constTableEnd = '</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;
}
returnTableStart + TableEnd;
}
const myXMLColumn = function(w) {
return'<ss:Column ss:AutoFitWidth="0" ss:Width="' + w + '"/>';
}
const myXMLHead = function(id, h) {
letHeadStart = '<ss:Row ss:StyleID="' + id + '">';
constHeadEnd = '</ss:Row>';
for (let i = 0; i < h.length; i++) {
constCell = myXMLCell(h[i].toUpperCase());
HeadStart += Cell;
}
returnHeadStart + HeadEnd;
}
const myXMLRow = function(r, h) {
letRowStart = '<ss:Row>';
constRowEnd = '</ss:Row>';
for (let i = 0; i < h.length; i++) {
constCell = myXMLCell(r[h[i]]);
RowStart += Cell;
}
returnRowStart + RowEnd;
}
const myXMLCell = function(n) {
letCellStart = '<ss:Cell>';
constCellEnd = '</ss:Cell>';
constData = myXMLData(n);
CellStart += Data;
returnCellStart + CellEnd;
}
const myXMLData = function(d) {
letDataStart = '<ss:Data ss:Type="String">';
constDataEnd = '</ss:Data>';
returnDataStart + 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) {
returnflatten(obj1);
} else {
return obj1;
}
} else {
return obj1;
}
}
return myExcelXML;
})();
Solution 3:
I have used the following code Javascript JSON to Excel or CSV file download
change file extension only (reports.xlsx or reports.CSV)
<scripttype="text/javascript"src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.1/xlsx.full.min.js"></script><script>functionExportData()
{
filename='reports.xlsx';
data=[{Market: "IN", NewArrivals: "6", UpcomingAppointments: "2", Pending - 1st Attempt: "4"},
{Market: "KS/MO", NewArrivals: "4", UpcomingAppointments: "4", Pending - 1st Attempt: "2"},
{Market: "KS/MO", NewArrivals: "4", UpcomingAppointments: "4", Pending - 1st Attempt: "2"},
{Market: "KS/MO", NewArrivals: "4", UpcomingAppointments: "4", Pending - 1st Attempt: "2"}]
var ws = XLSX.utils.json_to_sheet(data);
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "People");
XLSX.writeFile(wb,filename);
}
</script>Solution 4:
I know its a little late to answer but I have found an nice angular library that does all the hard work it self.
GITHUB: ngJsonExportExcel
Library Direct Download : Download
Filesaver JS : Download
How to use?
- Include the module in you app
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 : 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"
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>
Solution 5:
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.
Post a Comment for "Javascript Json To Excel File Download"