mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-18 18:41:18 +00:00
oleibman
fb55d1b6e4
Redo of PR #4799 due to failed attempt to resolve merge conflict. Fix #4798. Partially address #3961. Ods Reader supports very little related to styling. Ods Writer supports many styling details, but has not heretofore supported Number Formatting. 3961 addresses both issues; I created 4798 specifically for the Writer side. Writing number formats to Excel is pretty simple - you just supply a string and that is used directly in the Xml. Ods is much more complicated - it requires Xml nodes that give a complete description of the styling. For that reason, it is difficult and painstaking to convert from the string that Excel (and PhpSpreadsheet) uses to what Ods requires. This PR provides code to support almost all the styles defined as constants in Style/NumberFormat. It also allows the user to add code to handle otherwise unhandled styles. New Sample55_DefinedStyles demonstrates the use of all the constant styles, plus the addition of a couple of custom styles. I may be amenable to adding some unsupported styles to the built-in list, but the custom style option will always be around in case I am being slow or unreasonable. This PR does not fully support Ods Reader handling of styles. However, based on the new Writer output, it will often be able to guess the true type of numeric items and assign an appropriate style for that type. So, for example, if it can identify the field as a date, it will assign a date style. It will not always match the style in the sheet being read, but it is a big advance from just formatting the data as a generic number.
93 lines
3.6 KiB
PHP
93 lines
3.6 KiB
PHP
<?php
|
|
|
|
require __DIR__ . '/../Header.php';
|
|
/** @var PhpOffice\PhpSpreadsheet\Helper\Sample $helper */
|
|
|
|
use PhpOffice\PhpSpreadsheet\RichText\RichText;
|
|
use PhpOffice\PhpSpreadsheet\RichText\TextElement;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
|
|
$spreadsheet = new Spreadsheet();
|
|
|
|
$helper->log('First sheet - protected, sorts not allowed');
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->setTitle('sorttrue');
|
|
$sheet->getCell('A1')->setValue(10);
|
|
$sheet->getCell('A2')->setValue(5);
|
|
$sheet->getCell('B1')->setValue(15);
|
|
$protection = $sheet->getProtection();
|
|
$protection->setPassword('testpassword');
|
|
$protection->setSheet(true);
|
|
$protection->setInsertRows(true);
|
|
$protection->setFormatCells(true);
|
|
$protection->setObjects(true);
|
|
$protection->setAutoFilter(false);
|
|
$protection->setSort(true);
|
|
$comment = $sheet->getComment('A1');
|
|
$text = new RichText();
|
|
$text->addText(new TextElement('Sort options should be grayed out. Sheet password to remove protections is testpassword for all sheets.'));
|
|
$comment->setText($text)->setHeight('120pt')->setWidth('120pt');
|
|
|
|
$helper->log('Second sheet - protected, sorts allowed, but no permitted range defined');
|
|
$sheet = $spreadsheet->createSheet();
|
|
$sheet->setTitle('sortfalse');
|
|
$sheet->getCell('A1')->setValue(10);
|
|
$sheet->getCell('A2')->setValue(5);
|
|
$sheet->getCell('B1')->setValue(15);
|
|
$protection = $sheet->getProtection();
|
|
$protection->setPassword('testpassword');
|
|
$protection->setSheet(true);
|
|
$protection->setInsertRows(true);
|
|
$protection->setFormatCells(true);
|
|
$protection->setObjects(true);
|
|
$protection->setAutoFilter(false);
|
|
$protection->setSort(false);
|
|
$comment = $sheet->getComment('A1');
|
|
$text = new RichText();
|
|
$text->addText(new TextElement('Sort options not grayed out, but no permissible sort range.'));
|
|
$comment->setText($text)->setHeight('120pt')->setWidth('120pt');
|
|
|
|
$helper->log('Third sheet - protected, sorts allowed, but only on permitted range A:A, no range password needed');
|
|
$sheet = $spreadsheet->createSheet();
|
|
$sheet->setTitle('sortfalsenocolpw');
|
|
$sheet->getCell('A1')->setValue(10);
|
|
$sheet->getCell('A2')->setValue(5);
|
|
$sheet->getCell('C1')->setValue(15);
|
|
$protection = $sheet->getProtection();
|
|
$protection->setPassword('testpassword');
|
|
$protection->setSheet(true);
|
|
$protection->setInsertRows(true);
|
|
$protection->setFormatCells(true);
|
|
$protection->setObjects(true);
|
|
$protection->setAutoFilter(false);
|
|
$protection->setSort(false);
|
|
$sheet->protectCells('A:A');
|
|
$comment = $sheet->getComment('A1');
|
|
$text = new RichText();
|
|
$text->addText(new TextElement('Column A may be sorted without a password. No sort for any other column.'));
|
|
$comment->setText($text)->setHeight('120pt')->setWidth('120pt');
|
|
|
|
$helper->log('Fourth sheet - protected, sorts allowed, but only on permitted range A:A, and range password needed');
|
|
$sheet = $spreadsheet->createSheet();
|
|
$sheet->setTitle('sortfalsecolpw');
|
|
$sheet->getCell('A1')->setValue(10);
|
|
$sheet->getCell('A2')->setValue(5);
|
|
$sheet->getCell('C1')->setValue(15);
|
|
$protection = $sheet->getProtection();
|
|
$protection->setPassword('testpassword');
|
|
$protection->setSheet(true);
|
|
$protection->setInsertRows(true);
|
|
$protection->setFormatCells(true);
|
|
$protection->setObjects(true);
|
|
$protection->setAutoFilter(false);
|
|
$protection->setSort(false);
|
|
$sheet->protectCells('A:A', 'sortpw', false, 'sortrange');
|
|
$comment = $sheet->getComment('A1');
|
|
$text = new RichText();
|
|
$text->addText(new TextElement('Column A may be sorted with password sortpw. No sort for any other column.'));
|
|
$comment->setText($text)->setHeight('120pt')->setWidth('120pt');
|
|
|
|
// Save
|
|
$helper->write($spreadsheet, __FILE__, ['Xls', 'Xlsx']);
|
|
$spreadsheet->disconnectWorksheets();
|