1 : <?php
2 : /**
3 : * Roman de Renart
4 : *
5 : * PHP version 5
6 : *
7 : * @category Rdr
8 : * @package Edit
9 : * @author Michel Corne <mcorne@yahoo.com>
10 : * @copyright 2010 Michel Corne
11 : * @license http://www.opensource.org/licenses/bsd-license.php The BSD License
12 : * @link http://roman-de-renart.blogspot.com/
13 : * @version SVN: $Id$
14 : */
15 :
16 : require_once 'InputOutput.php';
17 :
18 : /**
19 : * Worksheet processor
20 : *
21 : * @category Rdr
22 : * @package Edit
23 : * @author Michel Corne <mcorne@yahoo.com>
24 : * @copyright 2010 Michel Corne
25 : * @license http://www.opensource.org/licenses/bsd-license.php The BSD License
26 : */
27 :
28 : class Worksheet extends InputOutput
29 : {
30 : /**
31 : * The column separator
32 : */
33 : const COLUMN_SEPARATOR = "\t";
34 :
35 : /**
36 : * The error message reported when a column number is out of range
37 : */
38 : const ERR_COLUMN_NUMBER = 'column number out of range';
39 :
40 : /**
41 : * The message reported when a worksheet is truncated successfully
42 : */
43 : const MSG_FILE_TRUNCATED = '%s truncated successfully';
44 :
45 : /**
46 : * The string delimiter
47 : */
48 : const STRING_DELIMITER = '"';
49 :
50 : /**
51 : * The number of columns
52 : * @var array
53 : */
54 : protected $columnCount = 0;
55 :
56 : /**
57 : * The column headers
58 : * @var array
59 : */
60 : protected $columnHeaders = array();
61 :
62 : /**
63 : * The worksheet file name
64 : * @var string
65 : */
66 : protected $worksheetFile = '';
67 :
68 : /**
69 : * The worksheet lines
70 : * @var array
71 : */
72 : protected $lines = array();
73 :
74 : /**
75 : * Constructor
76 : *
77 : * @param array $config the configuration directives
78 : * @param string $worksheetFile the name of the worksheet file
79 : * @param array $columnHeaders the column headers
80 : * @return void
81 : */
82 : public function __construct($config, $worksheetFile = null, $columnHeaders = null)
83 : {
84 66 : parent::__construct($config);
85 :
86 66 : $worksheetFile and $this->worksheetFile = $worksheetFile;
87 66 : $columnHeaders and $this->columnHeaders = $columnHeaders;
88 66 : $this->columnCount = count($this->columnHeaders);
89 66 : }
90 :
91 : /**
92 : * Explodes a line into a row of cell
93 : *
94 : * @param string $line the line to explode
95 : * @return array the cell values
96 : */
97 : public function explodeLine($line)
98 : {
99 16 : $row = explode(self::COLUMN_SEPARATOR, $line);
100 16 : $row = array_pad($row, $this->columnCount, '');
101 :
102 16 : return $this->arrayMap('trimCell', $row);
103 : }
104 :
105 : /**
106 : * Returs the lines of the worksheet
107 : *
108 : * @return array the lines of the worksheet
109 : * @access public
110 : */
111 : public function getLines()
112 : {
113 5 : return $this->lines;
114 : }
115 :
116 : /**
117 : * Implodes a row of cells into a line
118 : *
119 : * @param array $row the row of cells to implode
120 : * @return string the line
121 : */
122 : public function implodeRow($row)
123 : {
124 17 : $row = $this->arrayMap('quoteCell', $row);
125 :
126 17 : return implode(self::COLUMN_SEPARATOR, $row);
127 : }
128 :
129 : /**
130 : * Initializes the worksheet with blank lines
131 : *
132 : * @param int $lineCount the number of lines to initialize
133 : * @return void
134 : */
135 : public function initLines($lineCount)
136 : {
137 16 : $this->lines = array_fill(0, $lineCount, '');
138 16 : }
139 :
140 : /**
141 : * Verifies a column number is valid
142 : *
143 : * @param integer $columnNumber the column number
144 : * @return true
145 : * @throws Exception an exception is thrown by abort() if the column number is out of range
146 : */
147 : public function isValidColumn($columnNumber)
148 : {
149 12 : is_numeric($columnNumber) and
150 12 : $columnNumber >= 0 and
151 12 : $columnNumber < $this->columnCount or
152 3 : $this->abort(self::ERR_COLUMN_NUMBER);
153 :
154 12 : return true;
155 : }
156 :
157 : /**
158 : * Encloses a cell string value with a delimiter
159 : *
160 : * @param string $cell the cell value
161 : * @return string the enclosed value if applicable
162 : */
163 : public function quoteCell($cell)
164 : {
165 18 : empty($cell) or is_numeric($cell) or
166 12 : $cell = self::STRING_DELIMITER . $cell . self::STRING_DELIMITER;
167 :
168 18 : return $cell;
169 : }
170 :
171 : /**
172 : * Reads the cells a column
173 : *
174 : * @param int $columnNumber the column number
175 : * @return array the column cell values
176 : */
177 : public function readColumn($columnNumber)
178 : {
179 4 : $this->isValidColumn($columnNumber);
180 :
181 4 : $column = array();
182 :
183 4 : foreach($this->lines as $line) {
184 4 : $row = $this->explodeLine($line);
185 4 : $column[] = $row[$columnNumber];
186 4 : }
187 :
188 4 : return $column;
189 : }
190 :
191 : /**
192 : * Reads the cells of one or more columns
193 : *
194 : * @param array $columnNamesToNumbers the list of columns to read
195 : * array(<column-name> => <column-number>, ...)
196 : * @return array the list of columns and their values
197 : * array(<column-name> => array(<value-0>, <value-1>, ...), ...)
198 : */
199 : public function readColumns($columnNamesToNumbers)
200 : {
201 2 : return array_map(array($this, 'readColumn'), $columnNamesToNumbers);
202 : }
203 :
204 : /**
205 : * Reads all the rows of the worksheet
206 : *
207 : * @return array the rows of the worksheet,
208 : * array(
209 : * 0 => array(<value-0>, <value-1>, ...),
210 : * 1 => array(<value-0>, <value-1>, ...),
211 : * ...)
212 : */
213 : public function readRows()
214 : {
215 8 : return $this->arrayMap('explodeLine', $this->lines);
216 : }
217 :
218 : /**
219 : * Reads the worksheet from the file
220 : *
221 : * The column headers (first line) are removed.
222 : *
223 : * @return void
224 : */
225 : public function readSheet()
226 : {
227 6 : $this->lines = $this->readFile($this->worksheetFile, true);
228 : // removes column columnHeaders: first line
229 6 : array_shift($this->lines);
230 6 : }
231 :
232 : /**
233 : * Strips delimiters from the beginning and end of a cell value
234 : *
235 : * @param string $cell the cell value to trim
236 : * @return string the trimmed value
237 : */
238 : public function trimCell($cell)
239 : {
240 17 : return trim($cell, self::STRING_DELIMITER);
241 : }
242 :
243 : /**
244 : * Truncates a worksheet to its column header
245 : *
246 : * @return string a message reporting that the worksheet is truncated
247 : */
248 : public function truncateSheet()
249 : {
250 1 : $this->lines = array();
251 1 : $this->writeSheet();
252 :
253 1 : return sprintf(self::MSG_FILE_TRUNCATED, basename($this->worksheetFile));
254 : }
255 :
256 : /**
257 : * Writes values into a column
258 : *
259 : * @param array $values the list of values
260 : * @param int $columnNumber the number of the column to write
261 : * @return void
262 : */
263 : public function writeColumn($values, $columnNumber)
264 : {
265 8 : $this->isValidColumn($columnNumber);
266 :
267 : // reads each line of the worksheet
268 8 : foreach($this->lines as $idx => &$line) {
269 : // explodes the line
270 8 : $row = $this->explodeLine($line);
271 : // updates the line with the cell value for that column
272 8 : isset($values[$idx]) and $row[$columnNumber] = $values[$idx];
273 : // implodes the line
274 8 : $line = $this->implodeRow($row);
275 8 : }
276 8 : }
277 :
278 : /**
279 : * Writes one or more columns
280 : *
281 : * @param array $columns the list of columns and their values
282 : * array(<column-name> => array(<value-0>, <value-1>, ...), ...)
283 : * @param array $columnNamesToNumbers the list of columns to write
284 : * array(<column-name> => <column-number>, ...)
285 : * @return void
286 : */
287 : public function writeColumns($columns, $columnNamesToNumbers)
288 : {
289 : // extracts the columns to write by their names
290 3 : $columns = array_intersect_key($columns, $columnNamesToNumbers);
291 : // sorts by column names
292 3 : ksort($columns);
293 3 : ksort($columnNamesToNumbers);
294 : // write the values of each column
295 3 : array_map(array($this, 'writeColumn'), $columns, $columnNamesToNumbers);
296 3 : }
297 :
298 : /**
299 : * Writes rows of one or more cells into the worksheet
300 : *
301 : * @param array $rows the rows to write
302 : * @return void
303 : */
304 : public function writeRows($rows)
305 : {
306 : // reads each line of the worksheet
307 15 : foreach($this->lines as $idx => &$line) {
308 : // explodes the line
309 15 : $row = $this->explodeLine($line);
310 :
311 15 : if (isset($rows[$idx])) {
312 : // there is a row of cells for that line number
313 : // updates line with the new cell values
314 15 : $row = $rows[$idx] + $row;
315 15 : ksort($row);
316 15 : }
317 :
318 : // implodes the line
319 15 : $line = $this->implodeRow($row);
320 15 : }
321 15 : }
322 :
323 : /**
324 : * Writes the worksheet into the file
325 : *
326 : * The column headers are added as the first line.
327 : *
328 : * @return void
329 : */
330 : public function writeSheet()
331 : {
332 8 : $columnHeaders = $this->implodeRow($this->columnHeaders);
333 8 : $lines = array_merge(array($columnHeaders), $this->lines);
334 8 : $this->writeFile($this->worksheetFile, $lines);
335 8 : }
|
