0

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

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.