PHPExcel套用儲存格格式的方法
DATE如下
--
Write a date or time into a cell
In Excel, dates and Times are stored as numeric values counting the number of days elapsed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can verify this in Microsoft Office Excel by entering that date in a cell and afterwards changing the number format to 'General' so the true numeric value is revealed. Likewise, '3:15 AM' is represented as 0.135417.
PhpSpreadsheet works with UST (Universal Standard Time) date and Time values, but does no internal conversions; so it is up to the developer to ensure that values passed to the date/time conversion functions are UST.
Writing a date value in a cell consists of 2 lines of code. Select the method that suits you the best. Here are some examples:
// MySQL-like timestamp '2008-12-31' or date string
\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );
$spreadsheet->getActiveSheet()
->setCellValue('D1', '2008-12-31');
$spreadsheet->getActiveSheet()->getStyle('D1')
->getNumberFormat()
->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
// PHP-time (Unix time)
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600)
$spreadsheet->getActiveSheet()
->setCellValue('D1', \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($time));
$spreadsheet->getActiveSheet()->getStyle('D1')
->getNumberFormat()
->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
// Excel-date/time
$spreadsheet->getActiveSheet()->setCellValue('D1', 39813)
$spreadsheet->getActiveSheet()->getStyle('D1')
->getNumberFormat()
->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
The above methods for entering a date all yield the same result. \PhpOffice\PhpSpreadsheet\Style\NumberFormat
provides a lot of pre-defined date formats.
The \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel()
method will also work with a PHP DateTime object.
Similarly, times (or date and time values) can be entered in the same fashion: just remember to use an appropriate format code.
Note:
See section "Using value binders to facilitate data entry" to learn more about the AdvancedValueBinder used in the first example. Excel can also operate in a 1904-based calendar (default for workbooks saved on Mac). Normally, you do not have to worry about this when using PhpSpreadsheet.
--
NumberFormat參考這裡
https://github.com/PHPOffice/PhpSpreadsheet/blob/master/src/PhpSpreadsheet/Style/NumberFormat.php
--
https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/
留言列表