Feature request

[qstainless]

I understand that the main purpose of the library is speed (and it is awesome at that!). I have not seen these implemented, but would be useful if speed is not compromised, of course:

1. Set a sheet's zoom level
2. Protect/Uprotect cells/sheets

Thanks!

[aVadim483]

I can't say for sure, but it seems possible

[aVadim483]

https://github.com/aVadim483/fast-excel-writer/blob/master/docs/05-protection.md

[qstainless]

Thank you for this!! Protection feature is awesome!

I am able to protect my sheets, but am having trouble trying to unprotect specific cells: "Row number must be greater then written rows"

$sheet = $excel->sheet();
$sheet->protect();
$sheet->beginArea('A1');

$rowCounter = 3;

foreach ($data as $rowData) {
    ++rowCounter;

    foreach ($rowData as $column => $columnData) {

        $cell   = $column . $rowCounter;
        $unlock = false;

        // ... code to prepare cell value and styles

        if($column === 'C') {
            $unlock = true;
        }

        $sheet->setValue($cell, $value, $styles);
        $sheet->cell($cell)->applyUnlock($unlock);

    }
}

For context, the exception is triggered after the outer loop finishes (e.g. $rowCounter = 67) and I want to write to specific cells:

$totals => [
    'C4' => ['value' => '=SUM(F7:F{{rowCounter}})'],
    'C5' => ['value' => '=SUM(C7:C{{rowCounter}})'],
];

foreach ($totals as $cell => $cellData) {
    $value = str_replace('{{rowCounter}}', (string) $rowCounter, $cellData['value']);
    $sheet->setValue($cell, $value, $styles);
}

I have no issues when I don't try to unlock any cells: the sheet is fully protected. It seems that when I use cell() it precludes me from writing to cells after the last row was written (which is why I'm using setValue() to begin with).

What am I doing wrong?

Is there a correct way of doing this (unlock cells) when using setValue() instead of writeRow() or writeCell()?

Thanks in advance!

[aVadim483]

There are two ways to write to a XLSX-file in the library - sequential (row by row) and direct. When you use "row by row" writing, cells are written to the file as soon as you move to the next row. And you can no longer write something into the cells of the previous rows. This allows you to write quickly and save memory.

With direct writing, you first declare a writing area (or multiple areas) and write to it. In this case, you can write to any cells within the area.

So your code should be something like this:

// Start the first area
$area1 = $sheet->beginArea();
$rowCounter = 3;
foreach ($data as $rowData) {
    ++$rowCounter;
    foreach ($rowData as $column => $columnData) {
        $cell   = $column . $rowCounter;
        // ... code to prepare cell value and styles
        $value = '...';
        // write to the area, not the sheet
        $area1->setValue($cell, $value, $styles);
    }
    // you can unlock cell in the current row
    $area1->cell('C' . $rowCounter)->applyUnlock();
}

$totals = [
    'C4' => ['value' => '=SUM(F7:F{{rowCounter}})'],
    'C5' => ['value' => '=SUM(C7:C{{rowCounter}})'],
];

// Make the second area
$area2 = $sheet->makeArea('C4:C5');
foreach ($totals as $cell => $cellData) {
    $value = str_replace('{{rowCounter}}', (string) $rowCounter, $cellData['value']);
    $area2->setValue($cell, $value, $styles);
}

[aVadim483]

But update the library to the latest version

[qstainless]

Thank you for the detailed explanation!

[qstainless]

Thanks again for adding this feature and for taking the time to walk me through the proper use of $sheet and $area! Protecting sheets and unlocking specific cells works perfectly!!

Cheers!