Issues in sampleSpreadsheet2.php - Failed Issues - Inspection of "[Rector] Add Rector to CI for PHP upgrades code to..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Pull Request — master (#3876)

by Abdul Malik

created 2024-01-25 20:55 UTC

samples/templates/sampleSpreadsheet2.php (1 issue)

Labels

Bug 1

Severity

Major 1

<?php

// Create new Spreadsheet object
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Shared\Date;
filter:
    dependency_paths: ["lib/*"]
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\NumberFormat;
use PhpOffice\PhpSpreadsheet\Style\Protection;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;

$helper->log('Create new Spreadsheet object');
$spreadsheet = new Spreadsheet();

// Set document properties
$helper->log('Set document properties');
$spreadsheet->getProperties()->setCreator('Maarten Balliauw')
    ->setLastModifiedBy('Maarten Balliauw')
    ->setTitle('Office 2007 XLSX Test Document')
    ->setSubject('Office 2007 XLSX Test Document')
    ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
    ->setKeywords('office 2007 openxml php')
    ->setCategory('Test result file');

// Create a first sheet, representing sales data
$helper->log('Add some data');
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setCellValue('B1', 'Invoice');
$date = new DateTime('now');
$date->setTime(0, 0, 0);
$spreadsheet->getActiveSheet()->setCellValue('D1', Date::PHPToExcel($date));
$spreadsheet->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_XLSX15);
$spreadsheet->getActiveSheet()->setCellValue('E1', '#12566');

$spreadsheet->getActiveSheet()->setCellValue('A3', 'Product Id');
$spreadsheet->getActiveSheet()->setCellValue('B3', 'Description');
$spreadsheet->getActiveSheet()->setCellValue('C3', 'Price');
$spreadsheet->getActiveSheet()->setCellValue('D3', 'Amount');
$spreadsheet->getActiveSheet()->setCellValue('E3', 'Total');

$spreadsheet->getActiveSheet()->setCellValue('A4', '1001');
$spreadsheet->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
$spreadsheet->getActiveSheet()->setCellValue('C4', '20');
$spreadsheet->getActiveSheet()->setCellValue('D4', '1');
$spreadsheet->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');

$spreadsheet->getActiveSheet()->setCellValue('A5', '1012');
$spreadsheet->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
$spreadsheet->getActiveSheet()->setCellValue('C5', '22');
$spreadsheet->getActiveSheet()->setCellValue('D5', '2');
$spreadsheet->getActiveSheet()->setCellValue('E5', '=IF(D5<>"",C5*D5,"")');

$spreadsheet->getActiveSheet()->setCellValue('E6', '=IF(D6<>"",C6*D6,"")');
$spreadsheet->getActiveSheet()->setCellValue('E7', '=IF(D7<>"",C7*D7,"")');
$spreadsheet->getActiveSheet()->setCellValue('E8', '=IF(D8<>"",C8*D8,"")');
$spreadsheet->getActiveSheet()->setCellValue('E9', '=IF(D9<>"",C9*D9,"")');

$spreadsheet->getActiveSheet()->setCellValue('D11', 'Total excl.:');
$spreadsheet->getActiveSheet()->setCellValue('E11', '=SUM(E4:E9)');

$spreadsheet->getActiveSheet()->setCellValue('D12', 'VAT:');
$spreadsheet->getActiveSheet()->setCellValue('E12', '=E11*0.21');

$spreadsheet->getActiveSheet()->setCellValue('D13', 'Total incl.:');
$spreadsheet->getActiveSheet()->setCellValue('E13', '=E11+E12');

// Add comment
$helper->log('Add comments');

$spreadsheet->getActiveSheet()->getComment('E11')->setAuthor('PhpSpreadsheet');
$commentRichText = $spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFontOrThrow()->setBold(true);
$spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');

$spreadsheet->getActiveSheet()->getComment('E12')->setAuthor('PhpSpreadsheet');
$commentRichText = $spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFontOrThrow()->setBold(true);
$spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');

$spreadsheet->getActiveSheet()->getComment('E13')->setAuthor('PhpSpreadsheet');
$commentRichText = $spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFontOrThrow()->setBold(true);
$spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');
$spreadsheet->getActiveSheet()->getComment('E13')->setWidth('100pt');
$spreadsheet->getActiveSheet()->getComment('E13')->setHeight('100pt');
$spreadsheet->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');
$spreadsheet->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');

// Add rich-text string
$helper->log('Add rich-text string');
$richText = new RichText();
$richText->createText('This invoice is ');

$payable = $richText->createTextRun('payable within thirty days after the end of the month');
$payable->getFontOrThrow()->setBold(true);
$payable->getFontOrThrow()->setItalic(true);
$payable->getFontOrThrow()->setColor(new Color(Color::COLOR_DARKGREEN));

$richText->createText(', unless specified otherwise on the invoice.');

$spreadsheet->getActiveSheet()->getCell('A18')->setValue($richText);

// Merge cells
$helper->log('Merge cells');
$spreadsheet->getActiveSheet()->mergeCells('A18:E22');
$spreadsheet->getActiveSheet()->mergeCells('A28:B28'); // Just to test...
$spreadsheet->getActiveSheet()->unmergeCells('A28:B28'); // Just to test...
// Protect cells
$helper->log('Protect cells');
$spreadsheet->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$spreadsheet->getActiveSheet()->protectCells('A3:E13', 'PhpSpreadsheet');

// Set cell number formats
$helper->log('Set cell number formats');
$spreadsheet->getActiveSheet()->getStyle('E4:E13')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_EUR_INTEGER);

// Set column widths
$helper->log('Set column widths');
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);

// Set fonts
$helper->log('Set fonts');
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(Font::UNDERLINE_SINGLE);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);

$spreadsheet->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);

$spreadsheet->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);

// Set alignments
$helper->log('Set alignments');
$spreadsheet->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$spreadsheet->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

$spreadsheet->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_JUSTIFY);
$spreadsheet->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

$spreadsheet->getActiveSheet()->getStyle('B5')->getAlignment()->setShrinkToFit(true);

// Set thin black border outline around column
$helper->log('Set thin black border outline around column');
$styleThinBlackBorderOutline = [
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => 'FF000000'],
        ],
    ],
];
$spreadsheet->getActiveSheet()->getStyle('A4:E10')->applyFromArray($styleThinBlackBorderOutline);

// Set thick brown border outline around "Total"
$helper->log('Set thick brown border outline around Total');
$styleThickBrownBorderOutline = [
    'borders' => [
        'outline' => [
            'borderStyle' => Border::BORDER_THICK,
            'color' => ['argb' => 'FF993300'],
        ],
    ],
];
$spreadsheet->getActiveSheet()->getStyle('D13:E13')->applyFromArray($styleThickBrownBorderOutline);

// Set fills
$helper->log('Set fills');
$spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFill()->setFillType(Fill::FILL_SOLID);
$spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFill()->getStartColor()->setARGB('FF808080');

// Set style for header row using alternative method
$helper->log('Set style for header row using alternative method');
$spreadsheet->getActiveSheet()->getStyle('A3:E3')->applyFromArray(
    [
        'font' => [
            'bold' => true,
        ],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_RIGHT,
        ],
        'borders' => [
            'top' => [
                'borderStyle' => Border::BORDER_THIN,
            ],
        ],
        'fill' => [
            'fillType' => Fill::FILL_GRADIENT_LINEAR,
            'rotation' => 90,
            'startColor' => [
                'argb' => 'FFA0A0A0',
            ],
            'endColor' => [
                'argb' => 'FFFFFFFF',
            ],
        ],
    ]
);

$spreadsheet->getActiveSheet()->getStyle('A3')->applyFromArray(
    [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
        ],
        'borders' => [
            'left' => [
                'borderStyle' => Border::BORDER_THIN,
            ],
        ],
    ]
);

$spreadsheet->getActiveSheet()->getStyle('B3')->applyFromArray(
    [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
        ],
    ]
);

$spreadsheet->getActiveSheet()->getStyle('E3')->applyFromArray(
    [
        'borders' => [
            'right' => [
                'borderStyle' => Border::BORDER_THIN,
            ],
        ],
    ]
);

// Unprotect a cell
$helper->log('Unprotect a cell');
$spreadsheet->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);

// Add a hyperlink to the sheet
$helper->log('Add a hyperlink to an external website');
$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('https://www.example.com');
$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
$spreadsheet->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

$helper->log('Add a hyperlink to another cell on a different worksheet within the workbook');
$spreadsheet->getActiveSheet()->setCellValue('E27', 'Terms and conditions');
$spreadsheet->getActiveSheet()->getCell('E27')->getHyperlink()->setUrl("sheet://'Terms and conditions'!A1");
$spreadsheet->getActiveSheet()->getCell('E27')->getHyperlink()->setTooltip('Review terms and conditions');
$spreadsheet->getActiveSheet()->getStyle('E27')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

// Add a drawing to the worksheet
$helper->log('Add a drawing to the worksheet');
$drawing = new Drawing();
$drawing->setName('Logo');
$drawing->setDescription('Logo');
$drawing->setPath(__DIR__ . '/../images/officelogo.jpg');
$drawing->setHeight(36);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

// Add a drawing to the worksheet
$helper->log('Add a drawing to the worksheet');
$drawing = new Drawing();
$drawing->setName('Paid');
$drawing->setDescription('Paid');
$drawing->setPath(__DIR__ . '/../images/paid.png');
$drawing->setCoordinates('B15');
$drawing->setOffsetX(110);
$drawing->setRotation(25);
$drawing->getShadow()->setVisible(true);
$drawing->getShadow()->setDirection(45);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

// Add a drawing to the worksheet
$helper->log('Add a drawing with Japanese file name to the worksheet');
$drawing = new Drawing();
$drawing->setName('PhpSpreadsheet logo');
$drawing->setDescription('PhpSpreadsheet logo');
$drawing->setPath(__DIR__ . '/../images/サンプル.png');
$drawing->setHeight(36);
$drawing->setCoordinates('D24');
$drawing->setOffsetX(10);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

// Play around with inserting and removing rows and columns
$helper->log('Play around with inserting and removing rows and columns');
$spreadsheet->getActiveSheet()->insertNewRowBefore(6, 10);
$spreadsheet->getActiveSheet()->removeRow(6, 10);
$spreadsheet->getActiveSheet()->insertNewColumnBefore('E', 5);
$spreadsheet->getActiveSheet()->removeColumn('E', 5);

// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
$helper->log('Set header/footer');
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &N');

// Set page orientation and size
$helper->log('Set page orientation and size');
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_PORTRAIT);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);

// Rename first worksheet
$helper->log('Rename first worksheet');
$spreadsheet->getActiveSheet()->setTitle('Invoice');

// Create a new worksheet, after the default sheet
$helper->log('Create a second Worksheet object');
$spreadsheet->createSheet();

// Llorem ipsum...
$sLloremIpsum = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Vivamus eget ante. Sed cursus nunc semper tortor. Aliquam luctus purus non elit. Fusce vel elit commodo sapien dignissim dignissim. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Curabitur accumsan magna sed massa. Nullam bibendum quam ac ipsum. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Proin augue. Praesent malesuada justo sed orci. Pellentesque lacus ligula, sodales quis, ultricies a, ultricies vitae, elit. Sed luctus consectetuer dolor. Vivamus vel sem ut nisi sodales accumsan. Nunc et felis. Suspendisse semper viverra odio. Morbi at odio. Integer a orci a purus venenatis molestie. Nam mattis. Praesent rhoncus, nisi vel mattis auctor, neque nisi faucibus sem, non dapibus elit pede ac nisl. Cras turpis.';

// Add some data to the second sheet, resembling some different data types
$helper->log('Add some data');
$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->setCellValue('A1', 'Terms and conditions');
$spreadsheet->getActiveSheet()->setCellValue('A3', $sLloremIpsum);
$spreadsheet->getActiveSheet()->setCellValue('A4', $sLloremIpsum);
$spreadsheet->getActiveSheet()->setCellValue('A5', $sLloremIpsum);
$spreadsheet->getActiveSheet()->setCellValue('A6', $sLloremIpsum);

// Set the worksheet tab color
$helper->log('Set the worksheet tab color');
$spreadsheet->getActiveSheet()->getTabColor()->setARGB('FF0094FF');

// Set alignments
$helper->log('Set alignments');
$spreadsheet->getActiveSheet()->getStyle('A3:A6')->getAlignment()->setWrapText(true);

// Set column widths
$helper->log('Set column widths');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(80);

// Set fonts
$helper->log('Set fonts');
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(Font::UNDERLINE_SINGLE);

$spreadsheet->getActiveSheet()->getStyle('A3:A6')->getFont()->setSize(8);

// Add a drawing to the worksheet
$helper->log('Add a drawing with space and # in filename to the worksheet');
$drawing = new Drawing();
$drawing->setName('Terms and conditions');
$drawing->setDescription('Terms and conditions');
$drawing->setPath(__DIR__ . '/../images/terms con#ditions.jpg');
$drawing->setCoordinates('B14');
$drawing->setWorksheet($spreadsheet->getActiveSheet());

// Repeat drawing to the worksheet
$helper->log('Repeat drawing from other worksheet on this one');
$drawing = new Drawing();
$drawing->setName('PhpSpreadsheet logo');
$drawing->setDescription('PhpSpreadsheet logo');
$drawing->setPath(__DIR__ . '/../images/サンプル.png');
$drawing->setHeight(36);
$drawing->setCoordinates('B18');
$drawing->setOffsetX(10);
$drawing->setWorksheet($spreadsheet->getActiveSheet());

// Set page orientation and size
$helper->log('Set page orientation and size');
$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);

// Rename second worksheet
$helper->log('Rename second worksheet');
$spreadsheet->getActiveSheet()->setTitle('Terms and conditions');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

return $spreadsheet;


1			<?php
2
3			// Create new Spreadsheet object
4			use PhpOffice\PhpSpreadsheet\RichText\RichText;
5			use PhpOffice\PhpSpreadsheet\Shared\Date;
			0 ignored issues – show Bug introduced 2024-01-25 20:53 UTC by Report Bug Copy Issue Report Show Similar Issues like this The type `PhpOffice\PhpSpreadsheet\Shared\Date` was not found. Maybe you did not declare it correctly or list all dependencies? The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. `excluded_paths: ["lib/"]`, you can move it to the dependency path list as follows: filter: dependency_paths: ["lib/"] For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths Loading history...
6			use PhpOffice\PhpSpreadsheet\Spreadsheet;
7			use PhpOffice\PhpSpreadsheet\Style\Alignment;
8			use PhpOffice\PhpSpreadsheet\Style\Border;
9			use PhpOffice\PhpSpreadsheet\Style\Color;
10			use PhpOffice\PhpSpreadsheet\Style\Fill;
11			use PhpOffice\PhpSpreadsheet\Style\Font;
12			use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
13			use PhpOffice\PhpSpreadsheet\Style\Protection;
14			use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
15			use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
16
17			$helper->log('Create new Spreadsheet object');
18			$spreadsheet = new Spreadsheet();
19
20			// Set document properties
21			$helper->log('Set document properties');
22			$spreadsheet->getProperties()->setCreator('Maarten Balliauw')
23			->setLastModifiedBy('Maarten Balliauw')
24			->setTitle('Office 2007 XLSX Test Document')
25			->setSubject('Office 2007 XLSX Test Document')
26			->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
27			->setKeywords('office 2007 openxml php')
28			->setCategory('Test result file');
29
30			// Create a first sheet, representing sales data
31			$helper->log('Add some data');
32			$spreadsheet->setActiveSheetIndex(0);
33			$spreadsheet->getActiveSheet()->setCellValue('B1', 'Invoice');
34			$date = new DateTime('now');
35			$date->setTime(0, 0, 0);
36			$spreadsheet->getActiveSheet()->setCellValue('D1', Date::PHPToExcel($date));
37			$spreadsheet->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_XLSX15);
38			$spreadsheet->getActiveSheet()->setCellValue('E1', '#12566');
39
40			$spreadsheet->getActiveSheet()->setCellValue('A3', 'Product Id');
41			$spreadsheet->getActiveSheet()->setCellValue('B3', 'Description');
42			$spreadsheet->getActiveSheet()->setCellValue('C3', 'Price');
43			$spreadsheet->getActiveSheet()->setCellValue('D3', 'Amount');
44			$spreadsheet->getActiveSheet()->setCellValue('E3', 'Total');
45
46			$spreadsheet->getActiveSheet()->setCellValue('A4', '1001');
47			$spreadsheet->getActiveSheet()->setCellValue('B4', 'PHP for dummies');
48			$spreadsheet->getActiveSheet()->setCellValue('C4', '20');
49			$spreadsheet->getActiveSheet()->setCellValue('D4', '1');
50			$spreadsheet->getActiveSheet()->setCellValue('E4', '=IF(D4<>"",C4*D4,"")');
51
52			$spreadsheet->getActiveSheet()->setCellValue('A5', '1012');
53			$spreadsheet->getActiveSheet()->setCellValue('B5', 'OpenXML for dummies');
54			$spreadsheet->getActiveSheet()->setCellValue('C5', '22');
55			$spreadsheet->getActiveSheet()->setCellValue('D5', '2');
56			$spreadsheet->getActiveSheet()->setCellValue('E5', '=IF(D5<>"",C5*D5,"")');
57
58			$spreadsheet->getActiveSheet()->setCellValue('E6', '=IF(D6<>"",C6*D6,"")');
59			$spreadsheet->getActiveSheet()->setCellValue('E7', '=IF(D7<>"",C7*D7,"")');
60			$spreadsheet->getActiveSheet()->setCellValue('E8', '=IF(D8<>"",C8*D8,"")');
61			$spreadsheet->getActiveSheet()->setCellValue('E9', '=IF(D9<>"",C9*D9,"")');
62
63			$spreadsheet->getActiveSheet()->setCellValue('D11', 'Total excl.:');
64			$spreadsheet->getActiveSheet()->setCellValue('E11', '=SUM(E4:E9)');
65
66			$spreadsheet->getActiveSheet()->setCellValue('D12', 'VAT:');
67			$spreadsheet->getActiveSheet()->setCellValue('E12', '=E11*0.21');
68
69			$spreadsheet->getActiveSheet()->setCellValue('D13', 'Total incl.:');
70			$spreadsheet->getActiveSheet()->setCellValue('E13', '=E11+E12');
71
72			// Add comment
73			$helper->log('Add comments');
74
75			$spreadsheet->getActiveSheet()->getComment('E11')->setAuthor('PhpSpreadsheet');
76			$commentRichText = $spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun('PhpSpreadsheet:');
77			$commentRichText->getFontOrThrow()->setBold(true);
78			$spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
79			$spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');
80
81			$spreadsheet->getActiveSheet()->getComment('E12')->setAuthor('PhpSpreadsheet');
82			$commentRichText = $spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun('PhpSpreadsheet:');
83			$commentRichText->getFontOrThrow()->setBold(true);
84			$spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun("\r\n");
85			$spreadsheet->getActiveSheet()->getComment('E12')->getText()->createTextRun('Total amount of VAT on the current invoice.');
86
87			$spreadsheet->getActiveSheet()->getComment('E13')->setAuthor('PhpSpreadsheet');
88			$commentRichText = $spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun('PhpSpreadsheet:');
89			$commentRichText->getFontOrThrow()->setBold(true);
90			$spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun("\r\n");
91			$spreadsheet->getActiveSheet()->getComment('E13')->getText()->createTextRun('Total amount on the current invoice, including VAT.');
92			$spreadsheet->getActiveSheet()->getComment('E13')->setWidth('100pt');
93			$spreadsheet->getActiveSheet()->getComment('E13')->setHeight('100pt');
94			$spreadsheet->getActiveSheet()->getComment('E13')->setMarginLeft('150pt');
95			$spreadsheet->getActiveSheet()->getComment('E13')->getFillColor()->setRGB('EEEEEE');
96
97			// Add rich-text string
98			$helper->log('Add rich-text string');
99			$richText = new RichText();
100			$richText->createText('This invoice is ');
101
102			$payable = $richText->createTextRun('payable within thirty days after the end of the month');
103			$payable->getFontOrThrow()->setBold(true);
104			$payable->getFontOrThrow()->setItalic(true);
105			$payable->getFontOrThrow()->setColor(new Color(Color::COLOR_DARKGREEN));
106
107			$richText->createText(', unless specified otherwise on the invoice.');
108
109			$spreadsheet->getActiveSheet()->getCell('A18')->setValue($richText);
110
111			// Merge cells
112			$helper->log('Merge cells');
113			$spreadsheet->getActiveSheet()->mergeCells('A18:E22');
114			$spreadsheet->getActiveSheet()->mergeCells('A28:B28'); // Just to test...
115			$spreadsheet->getActiveSheet()->unmergeCells('A28:B28'); // Just to test...
116			// Protect cells
117			$helper->log('Protect cells');
118			$spreadsheet->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
119			$spreadsheet->getActiveSheet()->protectCells('A3:E13', 'PhpSpreadsheet');
120
121			// Set cell number formats
122			$helper->log('Set cell number formats');
123			$spreadsheet->getActiveSheet()->getStyle('E4:E13')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_EUR_INTEGER);
124
125			// Set column widths
126			$helper->log('Set column widths');
127			$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
128			$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(12);
129			$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
130
131			// Set fonts
132			$helper->log('Set fonts');
133			$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');
134			$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);
135			$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
136			$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(Font::UNDERLINE_SINGLE);
137			$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
138
139			$spreadsheet->getActiveSheet()->getStyle('D1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
140			$spreadsheet->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(Color::COLOR_WHITE);
141
142			$spreadsheet->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);
143			$spreadsheet->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);
144
145			// Set alignments
146			$helper->log('Set alignments');
147			$spreadsheet->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
148			$spreadsheet->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
149			$spreadsheet->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
150
151			$spreadsheet->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_JUSTIFY);
152			$spreadsheet->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
153
154			$spreadsheet->getActiveSheet()->getStyle('B5')->getAlignment()->setShrinkToFit(true);
155
156			// Set thin black border outline around column
157			$helper->log('Set thin black border outline around column');
158			$styleThinBlackBorderOutline = [
159			'borders' => [
160			'outline' => [
161			'borderStyle' => Border::BORDER_THIN,
162			'color' => ['argb' => 'FF000000'],
163			],
164			],
165			];
166			$spreadsheet->getActiveSheet()->getStyle('A4:E10')->applyFromArray($styleThinBlackBorderOutline);
167
168			// Set thick brown border outline around "Total"
169			$helper->log('Set thick brown border outline around Total');
170			$styleThickBrownBorderOutline = [
171			'borders' => [
172			'outline' => [
173			'borderStyle' => Border::BORDER_THICK,
174			'color' => ['argb' => 'FF993300'],
175			],
176			],
177			];
178			$spreadsheet->getActiveSheet()->getStyle('D13:E13')->applyFromArray($styleThickBrownBorderOutline);
179
180			// Set fills
181			$helper->log('Set fills');
182			$spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFill()->setFillType(Fill::FILL_SOLID);
183			$spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFill()->getStartColor()->setARGB('FF808080');
184
185			// Set style for header row using alternative method
186			$helper->log('Set style for header row using alternative method');
187			$spreadsheet->getActiveSheet()->getStyle('A3:E3')->applyFromArray(
188			[
189			'font' => [
190			'bold' => true,
191			],
192			'alignment' => [
193			'horizontal' => Alignment::HORIZONTAL_RIGHT,
194			],
195			'borders' => [
196			'top' => [
197			'borderStyle' => Border::BORDER_THIN,
198			],
199			],
200			'fill' => [
201			'fillType' => Fill::FILL_GRADIENT_LINEAR,
202			'rotation' => 90,
203			'startColor' => [
204			'argb' => 'FFA0A0A0',
205			],
206			'endColor' => [
207			'argb' => 'FFFFFFFF',
208			],
209			],
210			]
211			);
212
213			$spreadsheet->getActiveSheet()->getStyle('A3')->applyFromArray(
214			[
215			'alignment' => [
216			'horizontal' => Alignment::HORIZONTAL_LEFT,
217			],
218			'borders' => [
219			'left' => [
220			'borderStyle' => Border::BORDER_THIN,
221			],
222			],
223			]
224			);
225
226			$spreadsheet->getActiveSheet()->getStyle('B3')->applyFromArray(
227			[
228			'alignment' => [
229			'horizontal' => Alignment::HORIZONTAL_LEFT,
230			],
231			]
232			);
233
234			$spreadsheet->getActiveSheet()->getStyle('E3')->applyFromArray(
235			[
236			'borders' => [
237			'right' => [
238			'borderStyle' => Border::BORDER_THIN,
239			],
240			],
241			]
242			);
243
244			// Unprotect a cell
245			$helper->log('Unprotect a cell');
246			$spreadsheet->getActiveSheet()->getStyle('B1')->getProtection()->setLocked(Protection::PROTECTION_UNPROTECTED);
247
248			// Add a hyperlink to the sheet
249			$helper->log('Add a hyperlink to an external website');
250			$spreadsheet->getActiveSheet()->setCellValue('E26', 'www.phpexcel.net');
251			$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setUrl('https://www.example.com');
252			$spreadsheet->getActiveSheet()->getCell('E26')->getHyperlink()->setTooltip('Navigate to website');
253			$spreadsheet->getActiveSheet()->getStyle('E26')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
254
255			$helper->log('Add a hyperlink to another cell on a different worksheet within the workbook');
256			$spreadsheet->getActiveSheet()->setCellValue('E27', 'Terms and conditions');
257			$spreadsheet->getActiveSheet()->getCell('E27')->getHyperlink()->setUrl("sheet://'Terms and conditions'!A1");
258			$spreadsheet->getActiveSheet()->getCell('E27')->getHyperlink()->setTooltip('Review terms and conditions');
259			$spreadsheet->getActiveSheet()->getStyle('E27')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
260
261			// Add a drawing to the worksheet
262			$helper->log('Add a drawing to the worksheet');
263			$drawing = new Drawing();
264			$drawing->setName('Logo');
265			$drawing->setDescription('Logo');
266			$drawing->setPath(__DIR__ . '/../images/officelogo.jpg');
267			$drawing->setHeight(36);
268			$drawing->setWorksheet($spreadsheet->getActiveSheet());
269
270			// Add a drawing to the worksheet
271			$helper->log('Add a drawing to the worksheet');
272			$drawing = new Drawing();
273			$drawing->setName('Paid');
274			$drawing->setDescription('Paid');
275			$drawing->setPath(__DIR__ . '/../images/paid.png');
276			$drawing->setCoordinates('B15');
277			$drawing->setOffsetX(110);
278			$drawing->setRotation(25);
279			$drawing->getShadow()->setVisible(true);
280			$drawing->getShadow()->setDirection(45);
281			$drawing->setWorksheet($spreadsheet->getActiveSheet());
282
283			// Add a drawing to the worksheet
284			$helper->log('Add a drawing with Japanese file name to the worksheet');
285			$drawing = new Drawing();
286			$drawing->setName('PhpSpreadsheet logo');
287			$drawing->setDescription('PhpSpreadsheet logo');
288			$drawing->setPath(__DIR__ . '/../images/サンプル.png');
289			$drawing->setHeight(36);
290			$drawing->setCoordinates('D24');
291			$drawing->setOffsetX(10);
292			$drawing->setWorksheet($spreadsheet->getActiveSheet());
293
294			// Play around with inserting and removing rows and columns
295			$helper->log('Play around with inserting and removing rows and columns');
296			$spreadsheet->getActiveSheet()->insertNewRowBefore(6, 10);
297			$spreadsheet->getActiveSheet()->removeRow(6, 10);
298			$spreadsheet->getActiveSheet()->insertNewColumnBefore('E', 5);
299			$spreadsheet->getActiveSheet()->removeColumn('E', 5);
300
301			// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
302			$helper->log('Set header/footer');
303			$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
304			$spreadsheet->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $spreadsheet->getProperties()->getTitle() . '&RPage &P of &N');
305
306			// Set page orientation and size
307			$helper->log('Set page orientation and size');
308			$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_PORTRAIT);
309			$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
310
311			// Rename first worksheet
312			$helper->log('Rename first worksheet');
313			$spreadsheet->getActiveSheet()->setTitle('Invoice');
314
315			// Create a new worksheet, after the default sheet
316			$helper->log('Create a second Worksheet object');
317			$spreadsheet->createSheet();
318
319			// Llorem ipsum...
320			$sLloremIpsum = 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Vivamus eget ante. Sed cursus nunc semper tortor. Aliquam luctus purus non elit. Fusce vel elit commodo sapien dignissim dignissim. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Curabitur accumsan magna sed massa. Nullam bibendum quam ac ipsum. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Proin augue. Praesent malesuada justo sed orci. Pellentesque lacus ligula, sodales quis, ultricies a, ultricies vitae, elit. Sed luctus consectetuer dolor. Vivamus vel sem ut nisi sodales accumsan. Nunc et felis. Suspendisse semper viverra odio. Morbi at odio. Integer a orci a purus venenatis molestie. Nam mattis. Praesent rhoncus, nisi vel mattis auctor, neque nisi faucibus sem, non dapibus elit pede ac nisl. Cras turpis.';
321
322			// Add some data to the second sheet, resembling some different data types
323			$helper->log('Add some data');
324			$spreadsheet->setActiveSheetIndex(1);
325			$spreadsheet->getActiveSheet()->setCellValue('A1', 'Terms and conditions');
326			$spreadsheet->getActiveSheet()->setCellValue('A3', $sLloremIpsum);
327			$spreadsheet->getActiveSheet()->setCellValue('A4', $sLloremIpsum);
328			$spreadsheet->getActiveSheet()->setCellValue('A5', $sLloremIpsum);
329			$spreadsheet->getActiveSheet()->setCellValue('A6', $sLloremIpsum);
330
331			// Set the worksheet tab color
332			$helper->log('Set the worksheet tab color');
333			$spreadsheet->getActiveSheet()->getTabColor()->setARGB('FF0094FF');
334
335			// Set alignments
336			$helper->log('Set alignments');
337			$spreadsheet->getActiveSheet()->getStyle('A3:A6')->getAlignment()->setWrapText(true);
338
339			// Set column widths
340			$helper->log('Set column widths');
341			$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(80);
342
343			// Set fonts
344			$helper->log('Set fonts');
345			$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setName('Candara');
346			$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
347			$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
348			$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(Font::UNDERLINE_SINGLE);
349
350			$spreadsheet->getActiveSheet()->getStyle('A3:A6')->getFont()->setSize(8);
351
352			// Add a drawing to the worksheet
353			$helper->log('Add a drawing with space and # in filename to the worksheet');
354			$drawing = new Drawing();
355			$drawing->setName('Terms and conditions');
356			$drawing->setDescription('Terms and conditions');
357			$drawing->setPath(__DIR__ . '/../images/terms con#ditions.jpg');
358			$drawing->setCoordinates('B14');
359			$drawing->setWorksheet($spreadsheet->getActiveSheet());
360
361			// Repeat drawing to the worksheet
362			$helper->log('Repeat drawing from other worksheet on this one');
363			$drawing = new Drawing();
364			$drawing->setName('PhpSpreadsheet logo');
365			$drawing->setDescription('PhpSpreadsheet logo');
366			$drawing->setPath(__DIR__ . '/../images/サンプル.png');
367			$drawing->setHeight(36);
368			$drawing->setCoordinates('B18');
369			$drawing->setOffsetX(10);
370			$drawing->setWorksheet($spreadsheet->getActiveSheet());
371
372			// Set page orientation and size
373			$helper->log('Set page orientation and size');
374			$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_LANDSCAPE);
375			$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
376
377			// Rename second worksheet
378			$helper->log('Rename second worksheet');
379			$spreadsheet->getActiveSheet()->setTitle('Terms and conditions');
380
381			// Set active sheet index to the first sheet, so Excel opens this as the first sheet
382			$spreadsheet->setActiveSheetIndex(0);
383
384			return $spreadsheet;
385

PHPOffice / PhpSpreadsheet

Pull Request — master (#3876)

samples/templates/sampleSpreadsheet2.php (1 issue)

Labels

Severity

Introduced By

Duplication Side-by-Side

Filter issues like