visualizer / vendor / phpoffice / phpspreadsheet / samples / Calculations / DateTime / DATEVALUE.php

DATEVALUE.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.7.1, at vendor/phpoffice/phpspreadsheet/samples/Calculations/DateTime/DATEVALUE.php

40 lines 1.5 KB

Raw Download Zip

1	<?php
2
3	use PhpOffice\PhpSpreadsheet\Spreadsheet;
4
5	require __DIR__ . '/../../Header.php';
6
7	$helper->log('Converts a date in the form of text to a serial number.');
8
9	// Create new PhpSpreadsheet object
10	$spreadsheet = new Spreadsheet();
11	$worksheet = $spreadsheet->getActiveSheet();
12
13	// Add some data
14	$testDates = ['26 March 2012', '29 Feb 2012', 'April 1, 2012', '25/12/2012',
15	'2012-Oct-31', '5th November', 'January 1st', 'April 2012',
16	'17-03', '03-2012', '29 Feb 2011', '03-05-07',
17	'03-MAY-07', '03-13-07',
18	];
19	$testDateCount = count($testDates);
20
21	for ($row = 1; $row <= $testDateCount; ++$row) {
22	$worksheet->setCellValue('A' . $row, $testDates[$row - 1]);
23	$worksheet->setCellValue('B' . $row, '=DATEVALUE(A' . $row . ')');
24	$worksheet->setCellValue('C' . $row, '=B' . $row);
25	}
26
27	$worksheet->getStyle('C1:C' . $testDateCount)
28	->getNumberFormat()
29	->setFormatCode('yyyy-mmm-dd');
30
31	// Test the formulae
32	$helper->log('<strong>Warning: </strong>The PhpSpreadsheet DATEVALUE() function accepts a wider range of date formats than MS Excel DATEFORMAT() function.');
33	for ($row = 1; $row <= $testDateCount; ++$row) {
34	$helper->log('Date String: ' . $worksheet->getCell('A' . $row)->getFormattedValue());
35	$helper->log('Formula: ' . $worksheet->getCell('B' . $row)->getValue());
36	$helper->log('Excel DateStamp: ' . $worksheet->getCell('B' . $row)->getFormattedValue());
37	$helper->log('Formatted DateStamp' . $worksheet->getCell('C' . $row)->getFormattedValue());
38	$helper->log('');
39	}
40