geckon01/PhpSpreadsheet
1
0
Fork 0
mirror of https://github.com/PHPOffice/PhpSpreadsheet.git synced 2026-09-04 06:33:33 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
master
PhpSpreadsheet/tests/PhpSpreadsheetTests/Style/ExportArrayTest.php
T
oleibman c35cc943e8 Hyperlink Styles 
Fix #1632. Excel automatically supplies a style for cells which contain hyperlinks (e.g. underlined, and blue text changing to purple after the link has been followed). PhpSpreadsheet cannot handle the style automatically. The user can, with some effort, specify a style for the cell which mimics Excel's choice (except for the color change after following).

Examining a sheet with a hyperlink created through Excel, it appears that Excel creates 3 different entries in styles.xml, one for cellStyleXfs, one for cellXfs, and one for cellStyles. It is difficult for me to figure out how they interrelate. This is especially so since PhpSpreadsheet outputs only 1 entry (for the default style) for each of cellStyles and cellStyleXfs. However, it appears that only the cellXfs entry is required, and, when it specifies a font whose color specifies `theme="10"` rather than an rgb value, the style works as expected.

In order to implement this, it is necessary to add a `theme` property, with setter and getter, to Style/Color. There are 12 possible values for theme, 0-11 representing 0=dk1 1=lt1 2=dk2 3=lt2 4-9=accent1-6 10=hlink 11=folHlink. This PR is mainly to allow the use of hlink, but the others are also usable if a use case arises for them. If a theme is set for Color, Xlsx Writer will use the theme rather than rgb when generating the color xml. Other writers will continue to use rgb rather than theme, so there is a use case for setting both if you want to generate both Xlsx and some other format.

The `theme` property will, for now, be ignored except for Font. There is probably a case to be made for using it for Fill, and maybe for Border and other areas that I haven't yet considered. I will wait for someone to make that case before adding those.

In order to make it as easy as possible to use this, a method `setHyperlinkTheme` is added to both Style/Color and Style/Font. The one in Color sets `theme` to the appropriate value. The one in Font calls the one in Color, and also sets `underline` on (this will be honored by other writers in addition to Xlsx).

Samples which use hyperlinks are updated to use `setHyperlinkTheme`. So is `Reader\Xlsx\HyperlinkTest`, with appropriate tests added.
2025-05-20 22:54:08 -07:00

324 lines
15 KiB
PHP
Raw Permalink Blame History

<?php
declare(strict_types=1);
namespace PhpOffice\PhpSpreadsheetTests\Style;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Protection;
use PHPUnit\Framework\TestCase;
class ExportArrayTest extends TestCase
{
public function testStyleCopy(): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$cell1 = $sheet->getCell('A1');
$cell1->setValue('Cell A1');
$cell1style = $cell1->getStyle();
$cell1style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$cell1style->getFont()->getColor()->setARGB('FFFF0000');
$cell1style->getFont()->setBold(true);
$cell1style->getFill()->setFillType(Fill::FILL_PATTERN_GRAY125);
$cell1style->getFill()->setStartColor(new Color('FF0000FF'));
$cell1style->getFill()->setEndColor(new Color('FF00FF00'));
$cell1style->getFont()->setUnderline(true);
self::assertEquals(Font::UNDERLINE_SINGLE, $cell1style->getFont()->getUnderline());
$cell1style->getProtection()->setHidden(Protection::PROTECTION_UNPROTECTED);
$cell1style->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
/** @var mixed[][] */
$styleArray = $cell1style->exportArray();
$cell2 = $sheet->getCell('B1');
$cell2->setValue('Cell B1');
$cell2style = $cell2->getStyle();
$cell2style->applyFromArray($styleArray);
self::AssertEquals($cell1style->getAlignment()->getHorizontal(), $cell2style->getAlignment()->getHorizontal());
self::AssertEquals($cell1style->getFont()->getColor()->getARGB(), $cell2style->getFont()->getColor()->getARGB());
self::AssertEquals($cell1style->getFont()->getBold(), $cell2style->getFont()->getBold());
self::AssertEquals($cell1style->getFont()->getUnderline(), $cell2style->getFont()->getUnderline());
self::AssertEquals($cell1style->getFill()->getFillType(), $cell2style->getFill()->getFillType());
self::AssertEquals($cell1style->getFill()->getStartColor()->getARGB(), $cell2style->getFill()->getStartColor()->getARGB());
self::AssertEquals($cell1style->getFill()->getEndColor()->getARGB(), $cell2style->getFill()->getEndColor()->getARGB());
self::AssertEquals($cell1style->getProtection()->getLocked(), $cell2style->getProtection()->getLocked());
self::AssertEquals($cell1style->getProtection()->getHidden(), $cell2style->getProtection()->getHidden());
self::AssertEquals($cell1style->getHashCode(), $cell2style->getHashCode());
self::AssertEquals($cell1style->getAlignment()->getHashCode(), $cell2style->getAlignment()->getHashCode());
self::AssertEquals($cell1style->getFont()->getHashCode(), $cell2style->getFont()->getHashCode());
self::AssertEquals($cell1style->getFill()->getHashCode(), $cell2style->getFill()->getHashCode());
self::AssertEquals($cell1style->getProtection()->getHashCode(), $cell2style->getProtection()->getHashCode());
$spreadsheet->disconnectWorksheets();
}
public function testStyleFromArrayCopy(): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$cell1 = $sheet->getCell('A1');
$cell1->setValue('Cell A1');
$cell1style = $cell1->getStyle();
$cell1style->getAlignment()->applyFromArray(['horizontal' => Alignment::HORIZONTAL_RIGHT]);
$cell1style->getFont()->getColor()->setARGB('FFFF0000');
$cell1style->getFont()->applyFromArray(['bold' => true]);
$cell1style->getFill()->applyFromArray(['fillType' => Fill::FILL_PATTERN_GRAY125]);
$cell1style->getFill()->getStartColor()->applyFromArray(['argb' => 'FF0000FF']);
$cell1style->getFill()->getEndColor()->setRGB('00FF00');
$cell1style->getFill()->setRotation(45);
$cell1style->getFont()->setUnderline(true);
self::assertEquals(Font::UNDERLINE_SINGLE, $cell1style->getFont()->getUnderline());
$cell1style->getProtection()->applyFromArray(['hidden' => Protection::PROTECTION_UNPROTECTED, 'locked' => Protection::PROTECTION_UNPROTECTED]);
/** @var mixed[][] */
$styleArray = $cell1style->exportArray();
$cell2 = $sheet->getCell('B1');
$cell2->setValue('Cell B1');
$cell2style = $cell2->getStyle();
$cell2style->applyFromArray($styleArray);
self::AssertEquals($cell1style->getAlignment()->getHorizontal(), $cell2style->getAlignment()->getHorizontal());
self::AssertEquals($cell1style->getFont()->getColor()->getARGB(), $cell2style->getFont()->getColor()->getARGB());
self::AssertEquals($cell1style->getFont()->getBold(), $cell2style->getFont()->getBold());
self::AssertEquals($cell1style->getFont()->getUnderline(), $cell2style->getFont()->getUnderline());
self::AssertEquals($cell1style->getFill()->getFillType(), $cell2style->getFill()->getFillType());
self::AssertEquals($cell1style->getFill()->getRotation(), $cell2style->getFill()->getRotation());
self::AssertEquals($cell1style->getFill()->getStartColor()->getARGB(), $cell2style->getFill()->getStartColor()->getARGB());
self::AssertEquals($cell1style->getFill()->getEndColor()->getARGB(), $cell2style->getFill()->getEndColor()->getARGB());
self::AssertEquals($cell1style->getProtection()->getLocked(), $cell2style->getProtection()->getLocked());
self::AssertEquals($cell1style->getProtection()->getHidden(), $cell2style->getProtection()->getHidden());
self::AssertEquals($cell1style->getFill()->getStartColor()->getHashCode(), $cell2style->getFill()->getStartColor()->getHashCode());
self::AssertEquals($cell1style->getFill()->getEndColor()->getHashCode(), $cell2style->getFill()->getEndColor()->getHashCode());
$spreadsheet->disconnectWorksheets();
}
public function testNumberFormat(): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$cell1 = $sheet->getCell('A1');
$cell1style = $cell1->getStyle();
$fmt2 = '$ #,##0.000';
$cell1style->getNumberFormat()->setFormatCode($fmt2);
$cell1style->getFont()->setUnderline('');
self::assertEquals(Font::UNDERLINE_NONE, $cell1style->getFont()->getUnderline());
$cell1->setValue(2345.679);
/** @var mixed[][] */
$styleArray = $cell1style->exportArray();
self::assertEquals('$ 2,345.679', $cell1->getFormattedValue());
$cell2 = $sheet->getCell('B1');
$cell2->setValue(12345.679);
$cell2style = $cell2->getStyle();
$cell2style->applyFromArray($styleArray);
self::assertEquals('$ 12,345.679', $cell2->getFormattedValue());
self::AssertEquals($cell1style->getNumberFormat()->getHashCode(), $cell2style->getNumberFormat()->getHashCode());
$spreadsheet->disconnectWorksheets();
}
public function testNumberFormatFromArray(): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$cell1 = $sheet->getCell('A1');
$cell1style = $cell1->getStyle();
$fmt2 = '$ #,##0.000';
$cell1style->getNumberFormat()->applyFromArray(['formatCode' => $fmt2]);
$cell1style->getFont()->setUnderline('');
self::assertEquals(Font::UNDERLINE_NONE, $cell1style->getFont()->getUnderline());
$cell1style->getBorders()->getTop()->setBorderStyle(Border::BORDER_THIN);
$cell1->setValue(2345.679);
/** @var mixed[][] */
$styleArray = $cell1style->exportArray();
self::assertEquals('$ 2,345.679', $cell1->getFormattedValue());
$cell2 = $sheet->getCell('B1');
$cell2->setValue(12345.679);
$cell2style = $cell2->getStyle();
$cell2style->applyFromArray($styleArray);
self::assertEquals('$ 12,345.679', $cell2->getFormattedValue());
self::AssertEquals($cell1style->getNumberFormat()->getHashCode(), $cell2style->getNumberFormat()->getHashCode());
self::AssertEquals($cell1style->getBorders()->getHashCode(), $cell2style->getBorders()->getHashCode());
self::AssertEquals($cell1style->getBorders()->getTop()->getHashCode(), $cell2style->getBorders()->getTop()->getHashCode());
self::AssertEquals($cell1style->getBorders()->getTop()->getBorderStyle(), $cell2style->getBorders()->getTop()->getBorderStyle());
$spreadsheet->disconnectWorksheets();
}
public function testStackedRotation(): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$cell1 = $sheet->getCell('A1');
$cell1->setValue('Cell A1');
$cell1style = $cell1->getStyle();
$cell1style->getAlignment()->setTextRotation(Alignment::TEXTROTATION_STACK_EXCEL);
self::assertEquals(Alignment::TEXTROTATION_STACK_PHPSPREADSHEET, $cell1style->getAlignment()->getTextRotation());
/** @var mixed[][] */
$styleArray = $cell1style->exportArray();
$cell2 = $sheet->getCell('B1');
$cell2->setValue('Cell B1');
$cell2style = $cell2->getStyle();
$cell2style->applyFromArray($styleArray);
self::AssertEquals($cell1style->getAlignment()->getTextRotation(), $cell2style->getAlignment()->getTextRotation());
$spreadsheet->disconnectWorksheets();
}
public function testFillColors(): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$cell1 = $sheet->getCell('A2');
$cell1style = $cell1->getStyle();
$cell1style->getFill()
->setFillType(Fill::FILL_PATTERN_GRAY125);
$cell1style->getFill()->getStartColor()
->setArgb('FF112233');
$styleArray = $cell1style->exportArray();
self::assertEquals(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 0.0,
'endColor' => ['argb' => 'FF000000', 'theme' => -1],
'startColor' => ['argb' => 'FF112233', 'theme' => -1],
],
$styleArray['fill'],
'changed start color with setArgb'
);
$cell1 = $sheet->getCell('A1');
$cell1style = $cell1->getStyle();
$cell1style->getFill()->setFillType(Fill::FILL_PATTERN_GRAY125);
$styleArray = $cell1style->exportArray();
self::assertEquals(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 0.0,
],
$styleArray['fill'],
'default colors'
);
$cell1 = $sheet->getCell('A3');
$cell1style = $cell1->getStyle();
$cell1style->getFill()
->setFillType(Fill::FILL_PATTERN_GRAY125);
$cell1style->getFill()->getEndColor()->setArgb('FF112233');
$styleArray = $cell1style->exportArray();
self::assertEquals(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 0.0,
'endColor' => ['argb' => 'FF112233', 'theme' => -1],
'startColor' => ['argb' => 'FFFFFFFF', 'theme' => -1],
],
$styleArray['fill'],
'changed end color with setArgb'
);
$cell1 = $sheet->getCell('A4');
$cell1style = $cell1->getStyle();
$cell1style->getFill()
->setFillType(Fill::FILL_PATTERN_GRAY125);
$cell1style->getFill()->setEndColor(new Color('FF0000FF'));
$styleArray = $cell1style->exportArray();
self::assertEquals(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 0.0,
'endColor' => ['argb' => 'FF0000FF', 'theme' => -1],
'startColor' => ['argb' => 'FFFFFFFF', 'theme' => -1],
],
$styleArray['fill'],
'changed end color with setEndColor'
);
$cell1 = $sheet->getCell('A5');
$cell1style = $cell1->getStyle();
$cell1style->getFill()->setFillType(Fill::FILL_PATTERN_GRAY125);
$cell1style->getFill()
->setStartColor(new Color('FF0000FF'));
$styleArray = $cell1style->exportArray();
self::assertEquals(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 0.0,
'startColor' => ['argb' => 'FF0000FF', 'theme' => -1],
'endColor' => ['argb' => 'FF000000', 'theme' => -1],
],
$styleArray['fill'],
'changed start color with setStartColor'
);
$cell1 = $sheet->getCell('A6');
$cell1->getStyle()->getFill()->applyFromArray(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 45.0,
'startColor' => ['argb' => 'FF00FFFF', 'theme' => -1],
]
);
$cell1style = $cell1->getStyle();
$styleArray = $cell1style->exportArray();
self::assertEquals(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 45.0,
'startColor' => ['argb' => 'FF00FFFF', 'theme' => -1],
'endColor' => ['argb' => 'FF000000', 'theme' => -1],
],
$styleArray['fill'],
'applyFromArray with startColor'
);
$cell1 = $sheet->getCell('A7');
$cell1->getStyle()->getFill()->applyFromArray(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
]
);
$cell1style = $cell1->getStyle();
$styleArray = $cell1style->exportArray();
self::assertEquals(
[
'fillType' => Fill::FILL_PATTERN_GRAY125,
'rotation' => 0.0,
],
$styleArray['fill'],
'applyFromArray without start/endColor'
);
$spreadsheet->disconnectWorksheets();
}
public function testQuotePrefix(): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getCell('A1')
->setValueExplicit('=1+2', DataType::TYPE_STRING);
self::assertSame('=1+2', $sheet->getCell('A1')->getCalculatedValue());
self::assertTrue($sheet->getStyle('A1')->getQuotePrefix());
$sheet->getCell('A2')->setValue('=1+2');
self::assertSame(3, $sheet->getCell('A2')->getCalculatedValue());
self::assertFalse($sheet->getStyle('A2')->getQuotePrefix());
/** @var mixed[][] */
$styleArray1 = $sheet->getStyle('A1')->exportArray();
/** @var mixed[][] */
$styleArray2 = $sheet->getStyle('A2')->exportArray();
$sheet->getStyle('B1')->applyFromArray($styleArray1);
$sheet->getStyle('B2')->applyFromArray($styleArray2);
self::assertTrue($sheet->getStyle('B1')->getQuotePrefix());
self::assertFalse($sheet->getStyle('B2')->getQuotePrefix());
$spreadsheet->disconnectWorksheets();
}
}
Reference in New Issue View Git Blame Copy Permalink