We have screens that heavily use Excel operations in our project. These operations were performed using ActiveXObject("Excel.Application"). Since we had to switch to the Edge browser, we had to change these usages in all of these screens. Instead, I wrote a JS Proxy using the ExcelJS package to perform these operations and facilitated the transition. Otherwise, we would have had to change these codes one by one on 3000 screens, which would have taken a very long time. Currently, my solution is working but it consumes too much memory. For 6 mb, it consumes approximately 800 mb of memory. This is a serious problem for us. Because users are logging in via terminal, and thus they are collectively consuming the resources of the Terminal. I looked to see if there was a problem with the Proxy, or if I made a mistake there, but I couldn't find anything. I just ran a test with a simple HTML page with ExcelJS, having 20,000 rows and 50 columns. The same problem existed there as well. Both on the ExcelJS side and the FileSaver.js side, which I use for saving, had excessive memory consumption problems. After the process is completed, the memory does not free up, it waits for a long time in that state. This causes the out of memory error. How can I overcome this problem? It needs to work on the client-side because refactoring the codes is extremely difficult. Is there any way around it?
I tried this and memory usage increased by approximately 700mb.
<input type="button" value="Click me to start ExcelExport and wait 15-20 secs (there is no visual progress)" onclick="RunExcelJSExport()"></input>
//https://codepen.io/DanIgnatov/pen/vbdLjx
var dataSource = [];
for(var i = 0; i < 20000; i++) {
dataSource[i] = {};
for(var j = 0; j < 50; j++) {
dataSource[i]["f" + j] = i + j;
}
}
function RunExcelJSExport(){
var i, j, cell;
alert('Starting...');
performance.clearMarks();
performance.clearMeasures();
performance.mark("ExportToExcel start");
var workbook = new ExcelJS.Workbook();
var worksheet = workbook.addWorksheet('sheet 42');
var headerRow = worksheet.addRow();
var border = {
top: {style:'thin'},
left: {style:'thin'},
bottom: {style:'thin'},
right: {style:'thin'}
};
var properties = Object.keys(dataSource[0]);
for(i = 0; i < properties.length; i++) {
cell = headerRow.getCell(i + 1);
cell.value = "F" + i;
cell.font = cell.font || {};
cell.font.bold = true;
cell.border = border;
}
for(i = 0; i < dataSource.length; i++) {
var dataRow = worksheet.addRow();
for(j = 0; j < properties.length; j++) {
cell = dataRow.getCell(j + 1);
cell.value = dataSource[i][properties[j]];
cell.border = border;
if(cell.value % 5 === 0) {
cell.font = cell.font || {};
cell.font.underline = true;
}
if(cell.value % 7 === 0) {
cell.font = cell.font || {};
cell.font.bold = true;
}
if(cell.value % 11 === 0) {
cell.font = cell.font || {};
cell.font.italic = true;
}
var color = cell.value % 99;
if(color > 9) {
if(cell.value % 9 === 0) {
cell.fill = cell.fill || {};
cell.fill ={
type: 'pattern',
pattern: 'solid',
fgColor: { argb:'FFFF' + color + 'FF' }
};
}
if(cell.value % 13 === 0) {
cell.fill = cell.fill || {};
cell.fill ={
type: 'pattern',
pattern: 'solid',
fgColor: { argb:'FF' + color + 'FF00' }
};
}
}
}
}
workbook.xlsx.writeBuffer()
.then(function(buffer) {
saveAs(new Blob([buffer], { type: 'application/octet-stream' }), 'test.xlsx');
performance.mark("ExportToExcel end");
performance.measure("ExportToExcel", "ExportToExcel start", "ExportToExcel end");
const elapsed = performance.getEntriesByName("ExportToExcel")[0].duration;
console.log("ExportToExcel: " + performance.getEntriesByName("ExportToExcel")[0].duration);
alert('Finished in ' + elapsed + 'ms.');
});
}