"Fossies" - the Fresh Open Source Software Archive 
Member "php_writeexcel-0.3.0/functions.writeexcel_utility.inc.php" (1 Nov 2005, 7672 Bytes) of package /linux/www/old/php_writeexcel-0.3.0.tar.gz:
As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PHP source code syntax highlighting (style:
standard) with prefixed line numbers and
code folding option.
Alternatively you can here
view or
download the uninterpreted source code file.
1 <?php
2
3 /*
4 * Copyleft 2002 Johann Hanne
5 *
6 * This is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU Lesser General Public
8 * License as published by the Free Software Foundation; either
9 * version 2.1 of the License, or (at your option) any later version.
10 *
11 * This software is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 * Lesser General Public License for more details.
15 *
16 * You should have received a copy of the GNU Lesser General Public
17 * License along with this software; if not, write to the
18 * Free Software Foundation, Inc., 59 Temple Place,
19 * Suite 330, Boston, MA 02111-1307 USA
20 */
21
22 /*
23 * This is the Spreadsheet::WriteExcel Perl package ported to PHP
24 * Spreadsheet::WriteExcel was written by John McNamara, jmcnamara@cpan.org
25 */
26
27 /*
28 * Converts numeric $row/$col notation to an Excel cell reference string in
29 * A1 notation.
30 */
31 function xl_rowcol_to_cell($row, $col, $row_abs=false, $col_abs=false) {
32
33 $row_abs = $row_abs ? '$' : '';
34 $col_abs = $col_abs ? '$' : '';
35
36 $int = floor($col / 26);
37 $frac = $col % 26;
38
39 $chr1 = ''; // Most significant character in AA1
40
41 if ($int > 0) {
42 $chr1 = chr(ord('A') + $int - 1);
43 }
44
45 $chr2 = chr(ord('A') + $frac);
46
47 // Zero index to 1-index
48 $row++;
49
50 return $col_abs.$chr1.$chr2.$row_abs.$row;
51 }
52
53 /*
54 * Converts an Excel cell reference string in A1 notation
55 * to numeric $row/$col notation.
56 *
57 * Returns: array($row, $col, $row_absolute, $col_absolute)
58 *
59 * The $row_absolute and $col_absolute parameters aren't documented because
60 * they are mainly used internally and aren't very useful to the user.
61 */
62 function xl_cell_to_rowcol($cell) {
63
64 preg_match('/(\$?)([A-I]?[A-Z])(\$?)(\d+)/', $cell, $reg);
65
66 $col_abs = ($reg[1] == "") ? 0 : 1;
67 $col = $reg[2];
68 $row_abs = ($reg[3] == "") ? 0 : 1;
69 $row = $reg[4];
70
71 // Convert base26 column string to number
72 // All your Base are belong to us.
73 $chars = preg_split('//', $col, -1, PREG_SPLIT_NO_EMPTY);
74 $expn = 0;
75 $col = 0;
76
77 while (sizeof($chars)>0) {
78 $char = array_pop($chars); // Least significant character first
79 $col += (ord($char) - ord('A') + 1) * pow(26, $expn);
80 $expn++;
81 }
82
83 // Convert 1-index to zero-index
84 $row--;
85 $col--;
86
87 return array($row, $col, $row_abs, $col_abs);
88 }
89
90 /*
91 * Increments the row number of an Excel cell reference string
92 * in A1 notation.
93 * For example C4 to C5
94 *
95 * Returns: a cell reference string in A1 notation.
96 */
97 function xl_inc_row($cell) {
98 list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
99 return xl_rowcol_to_cell(++$row, $col, $row_abs, $col_abs);
100 }
101
102 /*
103 * Decrements the row number of an Excel cell reference string
104 * in A1 notation.
105 * For example C4 to C3
106 *
107 * Returns: a cell reference string in A1 notation.
108 */
109 function xl_dec_row($cell) {
110 list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
111 return xl_rowcol_to_cell(--$row, $col, $row_abs, $col_abs);
112 }
113
114 /*
115 * Increments the column number of an Excel cell reference string
116 * in A1 notation.
117 * For example C3 to D3
118 *
119 * Returns: a cell reference string in A1 notation.
120 */
121 function xl_inc_col($cell) {
122 list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
123 return xl_rowcol_to_cell($row, ++$col, $row_abs, $col_abs);
124 }
125
126 /*
127 * Decrements the column number of an Excel cell reference string
128 * in A1 notation.
129 * For example C3 to B3
130 *
131 * Returns: a cell reference string in A1 notation.
132 */
133 function xl_dec_col($cell) {
134 list($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
135 return xl_rowcol_to_cell($row, --$col, $row_abs, $col_abs);
136 }
137
138 function xl_date_list($year, $month=1, $day=1,
139 $hour=0, $minute=0, $second=0) {
140
141 $monthdays=array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
142
143 // Leap years since 1900 (year is dividable by 4)
144 $leapyears=floor(($year-1900)/4);
145
146 // Non-leap years since 1900 (year is dividable by 100)
147 $nonleapyears=floor(($year-1900)/100);
148
149 // Non-non-leap years since 1900 (year is dividable by 400)
150 // (Yes, it MUST be "1600", not "1900")
151 $nonnonleapyears=floor(($year-1600)/400);
152
153 // Don't count the leap day of the specified year if it didn't
154 // happen yet (i.e. before 1 March)
155 //
156 // Please note that $leapyears becomes -1 for dates before 1 March 1900;
157 // this is not logical, but later we will add a day for Excel's
158 // phantasie leap day in 1900 without checking if the date is actually
159 // after 28 February 1900; so these two logic errors "neutralize"
160 // each other
161 if ($year%4==0 && $month<3) {
162 $leapyears--;
163 }
164
165 $days=365*($year-1900)+$leapyears-$nonleapyears+$nonnonleapyears;
166
167 for ($c=1;$c<$month;$c++) {
168 $days+=$monthdays[$c-1];
169 }
170
171 // Excel actually wants the days since 31 December 1899, not since
172 // 1 January 1900; this will also add this extra day
173 $days+=$day;
174
175 // Excel treats 1900 erroneously as a leap year, so we must
176 // add one day
177 //
178 // Please note that we DON'T have to check if the date is after
179 // 28 February 1900, because for such dates $leapyears is -1
180 // (see above)
181 $days++;
182
183 return (float)($days+($hour*3600+$minute*60+$second)/86400);
184 }
185
186 function xl_parse_time($time) {
187
188 if (preg_match('/(\d{1,2}):(\d\d):?((?:\d\d)(?:\.\d+)?)?(?:\s+)?(am|pm)?/i', $time, $reg)) {
189
190 $hours = $reg[1];
191 $minutes = $reg[2];
192 $seconds = $reg[3] || 0;
193 $meridian = strtolower($reg[4]) || '';
194
195 // Normalise midnight and midday
196 if ($hours == 12 && $meridian != '') {
197 $hours = 0;
198 }
199
200 // Add 12 hours to the pm times. Note: 12.00 pm has been set to 0.00.
201 if ($meridian == 'pm') {
202 $hours += 12;
203 }
204
205 // Calculate the time as a fraction of 24 hours in seconds
206 return (float)(($hours*3600+$minutes*60+$seconds)/86400);
207
208 } else {
209 return false; // Not a valid time string
210 }
211 }
212
213 /*
214 * Automagically converts almost any date/time string to an Excel date.
215 * This function will always only be as good as strtotime() is.
216 */
217 function xl_parse_date($date) {
218
219 $unixtime=strtotime($date);
220
221 $year=date("Y", $unixtime);
222 $month=date("m", $unixtime);
223 $day=date("d", $unixtime);
224 $hour=date("H", $unixtime);
225 $minute=date("i", $unixtime);
226 $second=date("s", $unixtime);
227
228 // Convert to Excel date
229 return xl_date_list($year, $month, $day, $hour, $minute, $second);
230 }
231
232 /*
233 * Dummy function to be "compatible" to Spreadsheet::WriteExcel
234 */
235 function xl_parse_date_init() {
236 // Erm... do nothing...
237 // strtotime() doesn't require anything to be initialized
238 // (do not ask me how to set the timezone...)
239 }
240
241 /*
242 * xl_decode_date_EU() and xl_decode_date_US() are mapped
243 * to xl_parse_date(); there seems to be no PHP function that
244 * differentiates between EU and US dates; I've never seen
245 * somebody using dd/mm/yyyy anyway, it always should be one of:
246 * - yyyy-mm-dd (international)
247 * - dd.mm.yyyy (european)
248 * - mm/dd/yyyy (english/US/british?)
249 */
250
251 function xl_decode_date_EU($date) {
252 return xl_parse_date($date);
253 }
254
255 function xl_decode_date_US($date) {
256 return xl_parse_date($date);
257 }
258
259 function xl_date_1904($exceldate) {
260
261 if ($exceldate < 1462) {
262 // date is before 1904
263 $exceldate = 0;
264 } else {
265 $exceldate -= 1462;
266 }
267
268 return $exceldate;
269 }
270
271 ?>