How to make excel columns readonly via XLSXWriter or a subclass?

So, we are working with a PHP project, we use Yii, but that is not very important to the question. We use XLSXWriter (see https://github.com/mk-j/PHP_XLSXWriter/blob/master/xlsxwriter.class.php) to export. It is not a very new library and it is not being maintained very frequently, however, the latest requests for Excel exporting involved:

support for hiding fields

support for readonly fields

These are not being supported by XLSXWriter out-of-the-box, so we consider switching to PHPSpreadsheet as an alternative, but even if we do so, we would prefer to first have a quick solution involving XLSXWriter, to avoid benchmarking and testing all the exports and to meet a deadline. Hence, I've been looking into ways of making the requests work with what we currently have and I have seen that

initializeSheet hard-codes hidden="false" to the col tag as per $sheet->file_writer->write( '<col collapsed="false" hidden="false" max="'.($i+1).'" min="'.($i+1).'" style="0" customWidth="true" width="'.floatval($column_width).'"/>');

writeSheetRow does not pass column widths to it, as per $this->initializeSheet($sheet_name);

So in order to make it work, I implemented a subclass, which solves the hiding of the fields, as long as you pass a column width of 0 for them:

<?php class ExcelWriterCustomized extends XLSXWriter { protected $colWidths = []; public function getSheets() { return $this->sheets; } public function setColWidths($colWidths) { $this->colWidths = $colWidths; } protected function initializeSheet($sheet_name, $col_widths=array(), $auto_filter=false, $freeze_rows=false, $freeze_columns=false ) { //if already initialized if ($this->current_sheet==$sheet_name || isset($this->sheets[$sheet_name])) return; $sheet_filename = $this->tempFilename(); $sheet_xmlname = 'sheet' . (count($this->sheets) + 1).".xml"; $this->sheets[$sheet_name] = (object)array( 'filename' => $sheet_filename, 'sheetname' => $sheet_name, 'xmlname' => $sheet_xmlname, 'row_count' => 0, 'file_writer' => new XLSXWriter_BuffererWriter($sheet_filename), 'columns' => array(), 'merge_cells' => array(), 'max_cell_tag_start' => 0, 'max_cell_tag_end' => 0, 'auto_filter' => $auto_filter, 'freeze_rows' => $freeze_rows, 'freeze_columns' => $freeze_columns, 'finalized' => false, ); $rightToLeftValue = $this->isRightToLeft ? 'true' : 'false'; $sheet = &$this->sheets[$sheet_name]; $tabselected = count($this->sheets) == 1 ? 'true' : 'false';//only first sheet is selected $max_cell=XLSXWriter::xlsCell(self::EXCEL_2007_MAX_ROW, self::EXCEL_2007_MAX_COL);//XFE1048577 $sheet->file_writer->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"); $sheet->file_writer->write('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'); $sheet->file_writer->write( '<sheetPr filterMode="false">'); $sheet->file_writer->write( '<pageSetUpPr fitToPage="false"/>'); $sheet->file_writer->write( '</sheetPr>'); $sheet->max_cell_tag_start = $sheet->file_writer->ftell(); $sheet->file_writer->write('<dimension ref="A1:' . $max_cell . '"/>'); $sheet->max_cell_tag_end = $sheet->file_writer->ftell(); $sheet->file_writer->write( '<sheetViews>'); $sheet->file_writer->write( '<sheetView colorId="64" defaultGridColor="true" rightToLeft="'.$rightToLeftValue.'" showFormulas="false" showGridLines="true" showOutlineSymbols="true" showRowColHeaders="true" showZeros="true" tabSelected="' . $tabselected . '" topLeftCell="A1" view="normal" windowProtection="false" workbookViewId="0" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100">'); if ($sheet->freeze_rows && $sheet->freeze_columns) { $sheet->file_writer->write( '<pane ySplit="'.$sheet->freeze_rows.'" xSplit="'.$sheet->freeze_columns.'" topLeftCell="'.self::xlsCell($sheet->freeze_rows, $sheet->freeze_columns).'" activePane="bottomRight" state="frozen"/>'); $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell($sheet->freeze_rows, 0).'" activeCellId="0" pane="topRight" sqref="'.self::xlsCell($sheet->freeze_rows, 0).'"/>'); $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell(0, $sheet->freeze_columns).'" activeCellId="0" pane="bottomLeft" sqref="'.self::xlsCell(0, $sheet->freeze_columns).'"/>'); $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell($sheet->freeze_rows, $sheet->freeze_columns).'" activeCellId="0" pane="bottomRight" sqref="'.self::xlsCell($sheet->freeze_rows, $sheet->freeze_columns).'"/>'); } elseif ($sheet->freeze_rows) { $sheet->file_writer->write( '<pane ySplit="'.$sheet->freeze_rows.'" topLeftCell="'.self::xlsCell($sheet->freeze_rows, 0).'" activePane="bottomLeft" state="frozen"/>'); $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell($sheet->freeze_rows, 0).'" activeCellId="0" pane="bottomLeft" sqref="'.self::xlsCell($sheet->freeze_rows, 0).'"/>'); } elseif ($sheet->freeze_columns) { $sheet->file_writer->write( '<pane xSplit="'.$sheet->freeze_columns.'" topLeftCell="'.self::xlsCell(0, $sheet->freeze_columns).'" activePane="topRight" state="frozen"/>'); $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell(0, $sheet->freeze_columns).'" activeCellId="0" pane="topRight" sqref="'.self::xlsCell(0, $sheet->freeze_columns).'"/>'); } else { // not frozen $sheet->file_writer->write( '<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>'); } $sheet->file_writer->write( '</sheetView>'); $sheet->file_writer->write( '</sheetViews>'); $sheet->file_writer->write( '<cols>'); $i=0; if (!empty($col_widths)) { foreach($col_widths as $column_width) { $hidden = (($col_widths[$i] ?? 1) == 0) ? "true" : "false"; $sheet->file_writer->write( '<col collapsed="false" hidden="' . $hidden . '" max="'.($i+1).'" min="'.($i+1).'" style="0" customWidth="true" width="'.floatval($column_width).'"/>'); $i++; } } $sheet->file_writer->write( '<col collapsed="false" hidden="false" max="1024" min="'.($i+1).'" style="0" customWidth="false" width="11.5"/>'); $sheet->file_writer->write( '</cols>'); $sheet->file_writer->write( '<sheetData>'); } public function writeSheetRow($sheet_name, array $row, $row_options=null) { if (empty($sheet_name)) return; $this->initializeSheet($sheet_name, $this->colWidths); $sheet = &$this->sheets[$sheet_name]; if (count($sheet->columns) < count($row)) { $default_column_types = $this->initializeColumnTypes( array_fill($from=0, $until=count($row), 'GENERAL') );//will map to n_auto $sheet->columns = array_merge((array)$sheet->columns, $default_column_types); } if (!empty($row_options)) { $ht = isset($row_options['height']) ? floatval($row_options['height']) : 12.1; $customHt = isset($row_options['height']) ? true : false; $hidden = isset($row_options['hidden']) ? (bool)($row_options['hidden']) : false; $collapsed = isset($row_options['collapsed']) ? (bool)($row_options['collapsed']) : false; $sheet->file_writer->write('<row collapsed="'.($collapsed ? 'true' : 'false').'" customFormat="false" customHeight="'.($customHt ? 'true' : 'false').'" hidden="'.($hidden ? 'true' : 'false').'" ht="'.($ht).'" outlineLevel="0" r="' . ($sheet->row_count + 1) . '">'); } else { $sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . ($sheet->row_count + 1) . '">'); } $style = &$row_options; $c=0; foreach ($row as $v) { $number_format = $sheet->columns[$c]['number_format']; $number_format_type = $sheet->columns[$c]['number_format_type']; $cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle( $number_format, json_encode(isset($style[0]) ? $style[$c] : $style) ); $this->writeCell($sheet->file_writer, $sheet->row_count, $c, $v, $number_format_type, $cell_style_idx); $c++; } $sheet->file_writer->write('</row>'); $sheet->row_count++; $this->current_sheet = $sheet_name; } private function initializeColumnTypes($header_types) { $column_types = array(); foreach($header_types as $v) { $number_format = self::numberFormatStandardized($v); $number_format_type = self::determineNumberFormatType($number_format); $cell_style_idx = $this->addCellStyle($number_format, $style_string=null); $column_types[] = array('number_format' => $number_format,//contains excel format like 'YYYY-MM-DD HH:MM:SS' 'number_format_type' => $number_format_type, //contains friendly format like 'datetime' 'default_cell_style' => $cell_style_idx, ); } return $column_types; } private static function numberFormatStandardized($num_format) { if ($num_format=='money') { $num_format='dollar'; } if ($num_format=='number') { $num_format='integer'; } if ($num_format=='string') $num_format='@'; else if ($num_format=='integer') $num_format='0'; else if ($num_format=='date') $num_format='YYYY-MM-DD'; else if ($num_format=='datetime') $num_format='YYYY-MM-DD HH:MM:SS'; else if ($num_format=='time') $num_format='HH:MM:SS'; else if ($num_format=='price') $num_format='#,##0.00'; else if ($num_format=='dollar') $num_format='[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00'; else if ($num_format=='euro') $num_format='#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]'; $ignore_until=''; $escaped = ''; for($i=0,$ix=strlen($num_format); $i<$ix; $i++) { $c = $num_format[$i]; if ($ignore_until=='' && $c=='[') $ignore_until=']'; else if ($ignore_until=='' && $c=='"') $ignore_until='"'; else if ($ignore_until==$c) $ignore_until=''; if ($ignore_until=='' && ($c==' ' || $c=='-' || $c=='(' || $c==')') && ($i==0 || $num_format[$i-1]!='_')) $escaped.= "\\".$c; else $escaped.= $c; } return $escaped; } private static function determineNumberFormatType($num_format) { $num_format = preg_replace("/\[(Black|Blue|Cyan|Green|Magenta|Red|White|Yellow)\]/i", "", $num_format); if ($num_format=='GENERAL') return 'n_auto'; if ($num_format=='@') return 'n_string'; if ($num_format=='0') return 'n_numeric'; if (preg_match('/[H]{1,2}:[M]{1,2}(?![^"]*+")/i', $num_format)) return 'n_datetime'; if (preg_match('/[M]{1,2}:[S]{1,2}(?![^"]*+")/i', $num_format)) return 'n_datetime'; if (preg_match('/[Y]{2,4}(?![^"]*+")/i', $num_format)) return 'n_date'; if (preg_match('/[D]{1,2}(?![^"]*+")/i', $num_format)) return 'n_date'; if (preg_match('/[M]{1,2}(?![^"]*+")/i', $num_format)) return 'n_date'; if (preg_match('/$(?![^"]*+")/', $num_format)) return 'n_numeric'; if (preg_match('/%(?![^"]*+")/', $num_format)) return 'n_numeric'; if (preg_match('/0(?![^"]*+")/', $num_format)) return 'n_numeric'; return 'n_auto'; } private function addCellStyle($number_format, $cell_style_string) { $number_format_idx = self::add_to_list_get_index($this->number_formats, $number_format); $lookup_string = $number_format_idx.";".$cell_style_string; $cell_style_idx = self::add_to_list_get_index($this->cell_styles, $lookup_string); return $cell_style_idx; } }

This successfully hides the columns I wanted to hide, but I'm unsure how to make cells or columns readonly. In writeStylesXML I noticed the part of

$file->write(' <protection locked="true" hidden="false"/>');

but I'm unsure how to use it and whether it is even making a column/cell readonly. Is there a way to make a column or cell readonly via either XLSXWriter or a subclass? I already have a subclass overriding some parts that are not being supported by the actual class, so I'm open to any suggestions.

EDIT

As per Olivier's answer, I patched finalizeSheet as per

protected function finalizeSheet($sheet_name) { if (empty($sheet_name) || $this->sheets[$sheet_name]->finalized) return; $sheet = &$this->sheets[$sheet_name]; $sheet->file_writer->write( '</sheetData>'); $sheet->file_writer->write( '<sheetProtection sheet="true" password="cb83" objects="true" scenarios="true"/>'); if (!empty($sheet->merge_cells)) { $sheet->file_writer->write( '<mergeCells>'); foreach ($sheet->merge_cells as $range) { $sheet->file_writer->write( '<mergeCell ref="' . $range . '"/>'); } $sheet->file_writer->write( '</mergeCells>'); } $max_cell = self::xlsCell($sheet->row_count - 1, count($sheet->columns) - 1); if ($sheet->auto_filter) { $sheet->file_writer->write( '<autoFilter ref="A1:' . $max_cell . '"/>'); } $sheet->file_writer->write( '<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/>'); $sheet->file_writer->write( '<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>'); $sheet->file_writer->write( '<pageSetup blackAndWhite="false" cellComments="none" copies="1" draft="false" firstPageNumber="1" fitToHeight="1" fitToWidth="1" horizontalDpi="300" orientation="portrait" pageOrder="downThenOver" paperSize="1" scale="100" useFirstPageNumber="true" usePrinterDefaults="false" verticalDpi="300"/>'); $sheet->file_writer->write( '<headerFooter differentFirst="false" differentOddEven="false">'); $sheet->file_writer->write( '<oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader>'); $sheet->file_writer->write( '<oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter>'); $sheet->file_writer->write( '</headerFooter>'); $sheet->file_writer->write('</worksheet>'); $max_cell_tag = '<dimension ref="A1:' . $max_cell . '"/>'; $padding_length = $sheet->max_cell_tag_end - $sheet->max_cell_tag_start - strlen($max_cell_tag); $sheet->file_writer->fseek($sheet->max_cell_tag_start); $sheet->file_writer->write($max_cell_tag.str_repeat(" ", $padding_length)); $sheet->file_writer->close(); $sheet->finalized=true; }

But I am unsure how could I make individual columns locked. Tried doing

$value = '<protection hidden="false" locked="true"/>' . $value;

inside writeCell to see whether that's going to lock a cell. But it added it into the value. Maybe I'm missing something obvious.

EDIT2

finalizeSheet is not automatically called and since it's protected, in order to call it, in the subclass I have overriden as instructed by Olivier in his answer as well as a helper function, namely

public function finalize($sheet_name) { $this->finalizeSheet($sheet_name); }

And I call it at the end of the export, after writing the lines, like

$this->excelWriter->finalize(); $this->excelWriter->close();

where excelWriter is an instance of another class, whose functions invoked here are

public function finalize() { $this->workbook->finalize($this->currentSheet); } public function close() { $this->workbook->writeToFile($this->filename); if ($this->forceDownload) { header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); header("Content-Disposition: attachment; filename=\"{$this->webfilename}.xlsx\""); header('Content-Length: ' . filesize($this->filename)); readfile($this->filename); } return $this->workbook; }

and now it's invoked successfully, and, as Olivier claimed (correctly), it's making everything readonly. So I will need to unlock the cells that are ought to be editable. However, I'm not sure how to do that.