1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
<?php
/**
* PHPExcel
*
* Copyright (c) 2006 - 2009 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel_Cell
* @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.7.0, 2009-08-10
*/
/** PHPExcel root directory */
if (!defined('PHPEXCEL_ROOT')) {
/**
* @ignore
*/
define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
}
/** PHPExcel_Cell */
require_once PHPEXCEL_ROOT . 'PHPExcel/Cell.php';
/** PHPExcel_Cell_IValueBinder */
require_once PHPEXCEL_ROOT . 'PHPExcel/Cell/IValueBinder.php';
/** PHPExcel_Cell_DefaultValueBinder */
require_once PHPEXCEL_ROOT . 'PHPExcel/Cell/DefaultValueBinder.php';
/** PHPExcel_Style_NumberFormat */
require_once PHPEXCEL_ROOT . 'PHPExcel/Style/NumberFormat.php';
/** PHPExcel_Shared_Date */
require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Date.php';
/** PHPExcel_Shared_String */
require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/String.php';
/**
* PHPExcel_Cell_AdvancedValueBinder
*
* @category PHPExcel
* @package PHPExcel_Cell
* @copyright Copyright (c) 2006 - 2009 PHPExcel (http://www.codeplex.com/PHPExcel)
*/
class PHPExcel_Cell_AdvancedValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
/**
* Bind value to a cell
*
* @param PHPExcel_Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// sanitize UTF-8 strings
if (is_string($value)) {
$value = PHPExcel_Shared_String::SanitizeUTF8($value);
}
// Find out data type
$dataType = parent::dataTypeForValue($value);
// Style logic - strings
if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
// Check for percentage
if (preg_match('/^\-?[0-9]*\.?[0-9]*\s?\%$/', $value)) {
// Convert value to number
$cell->setValueExplicit( (float)str_replace('%', '', $value) / 100, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE );
return true;
}
// Check for time e.g. '9:45', '09:45'
if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d$/', $value)) {
list($h, $m) = explode(':', $value);
$days = $h / 24 + $m / 1440;
// Convert value to number
$cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3 );
return true;
}
// Check for date
if (strtotime($value) !== false) {
// make sure we have UTC for the sake of strtotime
$saveTimeZone = date_default_timezone_get();
date_default_timezone_set('UTC');
// Convert value to Excel date
$cell->setValueExplicit( PHPExcel_Shared_Date::PHPToExcel(strtotime($value)), PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 );
// restore original value for timezone
date_default_timezone_set($saveTimeZone);
return true;
}
}
// Style logic - Numbers
if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
// Leading zeroes?
if (preg_match('/^\-?[0]+[0-9]*\.?[0-9]*$/', $value)) {
// Convert value to string
$cell->setValueExplicit( $value, PHPExcel_Cell_DataType::TYPE_STRING);
// Set style
$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
return true;
}
}
// Not bound yet? Use parent...
return parent::bindValue($cell, $value);
}
}
|
