I am trying to lock specific cells(Row 1 & Column 1 cells) in the given code. The problem I am facing is that the whole sheet is getting locked instead of the specific cells. I have tried out unlocking all cells by default and locking the specific ones. Still the problem persists. I am looking for a solution that would be as optimal as possible. Also, I have a question. Can we do this using xlsx library too? Does that library offer such advanced features?
downloadTable() {
const data = this.formatExcelData(this.dialogData.template);
// Create a new workbook and worksheet
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Sheet1');
// Protect the worksheet but allow selecting both locked and unlocked cells
worksheet.protect('your_password', {selectLockedCells: false});
// Convert JSON data to worksheet columns dynamically
if (data.length > 0) {
const keys = Object.keys(data[0]);
worksheet.columns = keys.map((key, i) => {
return {
header: key,
key: key,
width: i==0 ? 8 : i==1 ? 13 : i==6||i==7 ? 11 : i==8||i==9 ? 22 : i==10||i==11 ? 13 : i==12 ? 17 : 11,
}
});
}
// Add JSON data as rows
data.forEach(row => {
worksheet.addRow(row);
});
// Apply merging to specified standby rows
this.standbyRows.forEach(rowIndex => {
worksheet.mergeCells(rowIndex + 2, 3, rowIndex + 2, 13); // Merges columns 3 to 13 (1-based index)
const cell = worksheet.getCell(rowIndex + 2, 3); // The top-left cell of the merged range
cell.alignment = {
horizontal: 'center', // Center horizontally
vertical: 'middle' // Center vertically
};
});
// Unprotect all cells in the worksheet
worksheet.eachRow((row) => {
row.eachCell((cell) => {
cell.protection = {
locked: false
};
});
});
worksheet.getRow(1).height = 30;
worksheet.getRow(1).eachCell((cell, colNumber) => {
debugger
cell.protection = { locked: true }; // Lock all cells in the first row
if(colNumber <= 13) {
// Set the background color, text color, bold font, and borders
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: '4286F5' }, // Blue background similar to your image
};
cell.font = {
bold: true,
color: { argb: 'FFFFFF' }, // White text color
size: 12, // Optional, adjust font size if needed
};
cell.alignment = {
horizontal: 'center',
vertical: 'bottom',
};
cell.border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
};
}
});
// Lock the first column (column A)
worksheet.getColumn(1).eachCell((cell) => {
cell.protection = { locked: true }; // Lock all cells in the first column
});
// Generate and download the Excel file
workbook.xlsx.writeBuffer().then((buffer) => {
const blob = new Blob([buffer], { type: 'application/octet-stream' });
const link = document.createElement('a');
link.href = URL.createObjectURL(blob);
link.download = 'CEMENT_QUALITY_DASHBOARD.xlsx';
link.click();
});
}
ANY HELP WOULD BE APPRECIATED