Front-end export Excel
I wrote A js export excel code before, and it has been used well, but recently it suddenly reported an error. After studying for A long time, I found that this problem would occur when the table column number exceeds 26 columns, which is also the excel column is A-Z, followed by AA,AB,AC… In this case, if the number of columns exceeds 26, the front-end export will report an error, so we added a special processing, and modified the column header in the generated sheet when the number exceeds 26 columns, to share with you
this.handleExport = function () {
// _this.onShowItemList is the list of data to be exported
var downloadItemList = angular.copy(_this.onShowItemList);
downloadItemList.forEach(function (item) {
Object.keys(item).forEach(function (key) {
var val = 0;
// If you use a comma, it will be a problem
if (!isNaN(Number(item[key])) || item[key].indexOf(', ') > -1) {
val = item[key].replace(/,/g.' ');
item[key] = val;
}
})
})
generateExcel(downloadItemList, _this.menuName + '.xlsx');
}
/ * * *@listItems Data to export *@excelName Export the name of Excel */
function generateExcel(listItems, excelName) {
_this.exportAllData = [];
_this.exportAllData.push(['number']);
// If there are multiple list headers, use the else part of the list
if (_this.titleList3 && _this.titleList3.length > 0 && _this.titleList2 && _this.titleList2.length > 0) {
_this.exportAllData.push([1]);
_this.exportAllData.push([2]);
_this.titleList3.forEach(function (item) {
_this.exportAllData[0].push(item);
})
_this.titleList2.forEach(function (item) {
_this.exportAllData[1].push(item);
})
angular.forEach(_this.titleList, function (a, b, c) {
_this.exportAllData[2].push(a); })}else if (_this.titleList2 && _this.titleList2.length > 0) {
_this.exportAllData.push([1]);
_this.titleList2.forEach(function (item) {
_this.exportAllData[0].push(item);
})
angular.forEach(_this.titleList, function (a, b, c) {
_this.exportAllData[1].push(a); })}else {
angular.forEach(_this.titleList, function (a, b, c) {
_this.exportAllData[0].push(a);
})
}
angular.forEach(listItems, function (value, key) {
var num = Object.keys(_this.itemList[_this.itemList.length - 1]).length;
_this.exportAllData[key + num] = [key + num];
angular.forEach(_this.valueList, function (a, b, c) { _this.exportAllData[key + num].push(value[a]); })})var str = ' ';
for (var i = 0; i < _this.exportAllData.length; i++) {
for (var item in _this.exportAllData[i]) {
var newStr = ' ';
if (_this.exportAllData[i][item] && _this.exportAllData[i][item].length > 0) {
newStr = isNaN(_this.exportAllData[i][item]) ? (_this.exportAllData[i][item] || ' ') : _this.exportAllData[i][item].replace(', '.', ')}else {
newStr = _this.exportAllData[i][item] || ' ';
}
str += newStr + '\t,';
}
str += '\n';
}
exportExcel(str, excelName);
}
function csv2sheet(csv) {
var sheet = {}; // The sheet to be generated
csv = csv.split('\n');
csv.forEach(function (row, i) {
row = row.split(', ');
var tmpArr = [];
row.forEach(function (item) {
tmpArr.push(item.trim());
});
row = tmpArr;
// The number of columns exceeds 26
var charCode = row.length > 26 ? String.fromCharCode(65 + row.length/26 - 1) + String.fromCharCode(65 + row.length%26 - 1) : String.fromCharCode(65 + row.length - 1);
if (i == 0) sheet['! ref'] = 'A1:' + charCode + (csv.length - 1);
row.forEach(function (col, j) {
var code = j > 26 ? String.fromCharCode(65 + row.length/26 - 1) + String.fromCharCode(65 + j%26) : String.fromCharCode(65 + j);
If so, the current cell is treated as nnumber.
// The export files can be sorted directly
if (col.indexOf(The '%') > -1&&!isNaN(col.trim().replace(The '%'.' '))) {
var percentValue = col.trim();
sheet[code + (i + 1)] = {
v: Number(percentValue.substring(0, percentValue.length - 1)) / 100.t: 'n'.z: '0.00%'
};
} else {
sheet[code + (i + 1)] = {v: Number((col).trim()) || col.trim(), t: 'n'}; }}); });return sheet;
}
// Turn a sheet into a blob object for the final Excel file and download it using the URL. CreateObjectURL
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {}}; workbook.Sheets[sheetName] = sheet;// Generate excel configuration items
var wopts = {
bookType: 'xlsx'.// The type of file to generate
bookSST: false.// Whether to generate Shared String tables, the official interpretation is that the generation speed slows down, but there is better compatibility on earlier versions of IOS devices
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"});
// String to ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i ! = s.length; ++i) view[i] = s.charCodeAt(i) &0xFF;
return buf;
}
return blob;
}
function openDownloadDialog(url, saveName) {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // Create a blob address
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ' '; // HTML5 new attribute to specify the file name to save, can not be suffix, note, file:/// mode does not take effect
var event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click'.true.false.window.0.0.0.0.0.false.false.false.false.0.null);
}
aLink.dispatchEvent(event);
}
function exportExcel(csv, excelName) {
var sheet = csv2sheet(csv);
var blob = sheet2blob(sheet);
openDownloadDialog(blob, excelName);
}
Copy the codeAt that time, the data exported by default was a string type. As a result, after exporting excel, the data could not be sorted according to the number, so I changed it. Now I meet the need to modify it, so I record it and welcome everyone to leave a comment in the comment section. ❀ ❀ ❀