I am working on an excel with OfficeJS web addin. I have a fully loaded excel with data across different sheets and formulae.
I want to download an exact copy of the current workbook without formulae , After extensive research I’ve learnt that using OfficeJS it is not possible to create a new excel while already working on an excell workbook as the context change will crash the excel application.
So i was trying to implement my requirement with the help of 3rd party libraries like xlsx, xlsx-populate and ExcelJS.
Using ExcelJS i was able to download a copy of the current workbook but was not able to copy the cell formatting, fonts, colors etc.
Is there a better front end library or a better way to achieve my requirement of downloading an exact copy of my current workbook.
Note: I am using the taskpane to load Angular components and download the file with the help of OfficeJS.
I’ve pasted the code that I tried below. Please suggest if modifying my code will help me or I need to use a better library for my requiement.
async downloadWorkbook3() {
try {
await Excel.run(async(context) => {
const currentWorkbook = context.workbook;
const sheets = currentWorkbook.worksheets;
sheets.load('items/name,items/visibility');
await context.sync();
// Create a new ExcelJS workbook
const newWorkbook = new ExcelJS.Workbook();
for (const sheet of sheets.items) {
if (sheet.visibility !== 'Visible') {
continue; // Skip hidden sheets
}
const worksheet = sheet;
worksheet.load(['name']);
await context.sync();
const usedRange = worksheet.getUsedRange();
usedRange.load(['values']);
await context.sync();
// Load formats separately
usedRange.load(['format/*']);
await context.sync();
// Create a new worksheet in the ExcelJS workbook
const newSheet = newWorkbook.addWorksheet(worksheet.name);
const values = usedRange.values;
for (let row = 0; row < values.length; row++) {
for (let col = 0; col < values[row].length; col++) {
const value = values[row][col];
const newCell = newSheet.getRow(row + 1).getCell(col + 1);
newCell.value = value;
const cell = usedRange.getCell(row, col);
cell.load(['format/fill', 'format/font', 'format/borders']);
await context.sync();
newCell.fill = this.convertFill(cell.format.fill);
newCell.font = this.convertFont(cell.format.font);
}
}
}
// Write the workbook to a Blob
const buffer = await newWorkbook.xlsx.writeBuffer();
const blob = new Blob([buffer], {
type: 'application/octet-stream'
});
// Create a download link and trigger the download
const link = document.createElement('a');
link.href = URL.createObjectURL(blob);
link.download = 'Workbook_Copy.xlsx';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
});
} catch (error) {
console.error('Error downloading workbook:', error);
}
}
convertFill(fill: Excel.RangeFill): ExcelJS.Fill {
if (!fill || !fill.color) return {
type: 'pattern',
pattern: 'none'
};
return {
type: 'pattern',
pattern: 'solid',
fgColor: {
argb: fill.color.replace('#', '')
}
};
}
// Convert Office.js font to ExcelJS font
convertFont(font: Excel.RangeFont): Partial < ExcelJS.Font > {
if (!font) return {};
return {
name: font.name,
size: font.size,
bold: font.bold,
italic: font.italic,
color: font.color ? {
argb: font.color.replace('#', '')
} : undefined
};
}