26653c4971966715d43035627ca3add7485b5c9f.svn-base 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Reader
  23. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version ##VERSION##, ##DATE##
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. /**
  30. * @ignore
  31. */
  32. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  33. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  34. }
  35. /**
  36. * PHPExcel_Reader_SYLK
  37. *
  38. * @category PHPExcel
  39. * @package PHPExcel_Reader
  40. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  41. */
  42. class PHPExcel_Reader_SYLK extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
  43. {
  44. /**
  45. * Input encoding
  46. *
  47. * @var string
  48. */
  49. private $_inputEncoding = 'ANSI';
  50. /**
  51. * Sheet index to read
  52. *
  53. * @var int
  54. */
  55. private $_sheetIndex = 0;
  56. /**
  57. * Formats
  58. *
  59. * @var array
  60. */
  61. private $_formats = array();
  62. /**
  63. * Format Count
  64. *
  65. * @var int
  66. */
  67. private $_format = 0;
  68. /**
  69. * Create a new PHPExcel_Reader_SYLK
  70. */
  71. public function __construct() {
  72. $this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
  73. }
  74. /**
  75. * Validate that the current file is a SYLK file
  76. *
  77. * @return boolean
  78. */
  79. protected function _isValidFormat()
  80. {
  81. // Read sample data (first 2 KB will do)
  82. $data = fread($this->_fileHandle, 2048);
  83. // Count delimiters in file
  84. $delimiterCount = substr_count($data, ';');
  85. if ($delimiterCount < 1) {
  86. return FALSE;
  87. }
  88. // Analyze first line looking for ID; signature
  89. $lines = explode("\n", $data);
  90. if (substr($lines[0],0,4) != 'ID;P') {
  91. return FALSE;
  92. }
  93. return TRUE;
  94. }
  95. /**
  96. * Set input encoding
  97. *
  98. * @param string $pValue Input encoding
  99. */
  100. public function setInputEncoding($pValue = 'ANSI')
  101. {
  102. $this->_inputEncoding = $pValue;
  103. return $this;
  104. }
  105. /**
  106. * Get input encoding
  107. *
  108. * @return string
  109. */
  110. public function getInputEncoding()
  111. {
  112. return $this->_inputEncoding;
  113. }
  114. /**
  115. * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
  116. *
  117. * @param string $pFilename
  118. * @throws PHPExcel_Reader_Exception
  119. */
  120. public function listWorksheetInfo($pFilename)
  121. {
  122. // Open file
  123. $this->_openFile($pFilename);
  124. if (!$this->_isValidFormat()) {
  125. fclose ($this->_fileHandle);
  126. throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
  127. }
  128. $fileHandle = $this->_fileHandle;
  129. rewind($fileHandle);
  130. $worksheetInfo = array();
  131. $worksheetInfo[0]['worksheetName'] = 'Worksheet';
  132. $worksheetInfo[0]['lastColumnLetter'] = 'A';
  133. $worksheetInfo[0]['lastColumnIndex'] = 0;
  134. $worksheetInfo[0]['totalRows'] = 0;
  135. $worksheetInfo[0]['totalColumns'] = 0;
  136. // Loop through file
  137. $rowData = array();
  138. // loop through one row (line) at a time in the file
  139. $rowIndex = 0;
  140. while (($rowData = fgets($fileHandle)) !== FALSE) {
  141. $columnIndex = 0;
  142. // convert SYLK encoded $rowData to UTF-8
  143. $rowData = PHPExcel_Shared_String::SYLKtoUTF8($rowData);
  144. // explode each row at semicolons while taking into account that literal semicolon (;)
  145. // is escaped like this (;;)
  146. $rowData = explode("\t",str_replace('¤',';',str_replace(';',"\t",str_replace(';;','¤',rtrim($rowData)))));
  147. $dataType = array_shift($rowData);
  148. if ($dataType == 'C') {
  149. // Read cell value data
  150. foreach($rowData as $rowDatum) {
  151. switch($rowDatum{0}) {
  152. case 'C' :
  153. case 'X' :
  154. $columnIndex = substr($rowDatum,1) - 1;
  155. break;
  156. case 'R' :
  157. case 'Y' :
  158. $rowIndex = substr($rowDatum,1);
  159. break;
  160. }
  161. $worksheetInfo[0]['totalRows'] = max($worksheetInfo[0]['totalRows'], $rowIndex);
  162. $worksheetInfo[0]['lastColumnIndex'] = max($worksheetInfo[0]['lastColumnIndex'], $columnIndex);
  163. }
  164. }
  165. }
  166. $worksheetInfo[0]['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($worksheetInfo[0]['lastColumnIndex']);
  167. $worksheetInfo[0]['totalColumns'] = $worksheetInfo[0]['lastColumnIndex'] + 1;
  168. // Close file
  169. fclose($fileHandle);
  170. return $worksheetInfo;
  171. }
  172. /**
  173. * Loads PHPExcel from file
  174. *
  175. * @param string $pFilename
  176. * @return PHPExcel
  177. * @throws PHPExcel_Reader_Exception
  178. */
  179. public function load($pFilename)
  180. {
  181. // Create new PHPExcel
  182. $objPHPExcel = new PHPExcel();
  183. // Load into this instance
  184. return $this->loadIntoExisting($pFilename, $objPHPExcel);
  185. }
  186. /**
  187. * Loads PHPExcel from file into PHPExcel instance
  188. *
  189. * @param string $pFilename
  190. * @param PHPExcel $objPHPExcel
  191. * @return PHPExcel
  192. * @throws PHPExcel_Reader_Exception
  193. */
  194. public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
  195. {
  196. // Open file
  197. $this->_openFile($pFilename);
  198. if (!$this->_isValidFormat()) {
  199. fclose ($this->_fileHandle);
  200. throw new PHPExcel_Reader_Exception($pFilename . " is an Invalid Spreadsheet file.");
  201. }
  202. $fileHandle = $this->_fileHandle;
  203. rewind($fileHandle);
  204. // Create new PHPExcel
  205. while ($objPHPExcel->getSheetCount() <= $this->_sheetIndex) {
  206. $objPHPExcel->createSheet();
  207. }
  208. $objPHPExcel->setActiveSheetIndex( $this->_sheetIndex );
  209. $fromFormats = array('\-', '\ ');
  210. $toFormats = array('-', ' ');
  211. // Loop through file
  212. $rowData = array();
  213. $column = $row = '';
  214. // loop through one row (line) at a time in the file
  215. while (($rowData = fgets($fileHandle)) !== FALSE) {
  216. // convert SYLK encoded $rowData to UTF-8
  217. $rowData = PHPExcel_Shared_String::SYLKtoUTF8($rowData);
  218. // explode each row at semicolons while taking into account that literal semicolon (;)
  219. // is escaped like this (;;)
  220. $rowData = explode("\t",str_replace('¤',';',str_replace(';',"\t",str_replace(';;','¤',rtrim($rowData)))));
  221. $dataType = array_shift($rowData);
  222. // Read shared styles
  223. if ($dataType == 'P') {
  224. $formatArray = array();
  225. foreach($rowData as $rowDatum) {
  226. switch($rowDatum{0}) {
  227. case 'P' : $formatArray['numberformat']['code'] = str_replace($fromFormats,$toFormats,substr($rowDatum,1));
  228. break;
  229. case 'E' :
  230. case 'F' : $formatArray['font']['name'] = substr($rowDatum,1);
  231. break;
  232. case 'L' : $formatArray['font']['size'] = substr($rowDatum,1);
  233. break;
  234. case 'S' : $styleSettings = substr($rowDatum,1);
  235. for ($i=0;$i<strlen($styleSettings);++$i) {
  236. switch ($styleSettings{$i}) {
  237. case 'I' : $formatArray['font']['italic'] = true;
  238. break;
  239. case 'D' : $formatArray['font']['bold'] = true;
  240. break;
  241. case 'T' : $formatArray['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  242. break;
  243. case 'B' : $formatArray['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  244. break;
  245. case 'L' : $formatArray['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  246. break;
  247. case 'R' : $formatArray['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  248. break;
  249. }
  250. }
  251. break;
  252. }
  253. }
  254. $this->_formats['P'.$this->_format++] = $formatArray;
  255. // Read cell value data
  256. } elseif ($dataType == 'C') {
  257. $hasCalculatedValue = false;
  258. $cellData = $cellDataFormula = '';
  259. foreach($rowData as $rowDatum) {
  260. switch($rowDatum{0}) {
  261. case 'C' :
  262. case 'X' : $column = substr($rowDatum,1);
  263. break;
  264. case 'R' :
  265. case 'Y' : $row = substr($rowDatum,1);
  266. break;
  267. case 'K' : $cellData = substr($rowDatum,1);
  268. break;
  269. case 'E' : $cellDataFormula = '='.substr($rowDatum,1);
  270. // Convert R1C1 style references to A1 style references (but only when not quoted)
  271. $temp = explode('"',$cellDataFormula);
  272. $key = false;
  273. foreach($temp as &$value) {
  274. // Only count/replace in alternate array entries
  275. if ($key = !$key) {
  276. preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/',$value, $cellReferences,PREG_SET_ORDER+PREG_OFFSET_CAPTURE);
  277. // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way
  278. // through the formula from left to right. Reversing means that we work right to left.through
  279. // the formula
  280. $cellReferences = array_reverse($cellReferences);
  281. // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent,
  282. // then modify the formula to use that new reference
  283. foreach($cellReferences as $cellReference) {
  284. $rowReference = $cellReference[2][0];
  285. // Empty R reference is the current row
  286. if ($rowReference == '') $rowReference = $row;
  287. // Bracketed R references are relative to the current row
  288. if ($rowReference{0} == '[') $rowReference = $row + trim($rowReference,'[]');
  289. $columnReference = $cellReference[4][0];
  290. // Empty C reference is the current column
  291. if ($columnReference == '') $columnReference = $column;
  292. // Bracketed C references are relative to the current column
  293. if ($columnReference{0} == '[') $columnReference = $column + trim($columnReference,'[]');
  294. $A1CellReference = PHPExcel_Cell::stringFromColumnIndex($columnReference-1).$rowReference;
  295. $value = substr_replace($value,$A1CellReference,$cellReference[0][1],strlen($cellReference[0][0]));
  296. }
  297. }
  298. }
  299. unset($value);
  300. // Then rebuild the formula string
  301. $cellDataFormula = implode('"',$temp);
  302. $hasCalculatedValue = true;
  303. break;
  304. }
  305. }
  306. $columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
  307. $cellData = PHPExcel_Calculation::_unwrapResult($cellData);
  308. // Set cell value
  309. $objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setValue(($hasCalculatedValue) ? $cellDataFormula : $cellData);
  310. if ($hasCalculatedValue) {
  311. $cellData = PHPExcel_Calculation::_unwrapResult($cellData);
  312. $objPHPExcel->getActiveSheet()->getCell($columnLetter.$row)->setCalculatedValue($cellData);
  313. }
  314. // Read cell formatting
  315. } elseif ($dataType == 'F') {
  316. $formatStyle = $columnWidth = $styleSettings = '';
  317. $styleData = array();
  318. foreach($rowData as $rowDatum) {
  319. switch($rowDatum{0}) {
  320. case 'C' :
  321. case 'X' : $column = substr($rowDatum,1);
  322. break;
  323. case 'R' :
  324. case 'Y' : $row = substr($rowDatum,1);
  325. break;
  326. case 'P' : $formatStyle = $rowDatum;
  327. break;
  328. case 'W' : list($startCol,$endCol,$columnWidth) = explode(' ',substr($rowDatum,1));
  329. break;
  330. case 'S' : $styleSettings = substr($rowDatum,1);
  331. for ($i=0;$i<strlen($styleSettings);++$i) {
  332. switch ($styleSettings{$i}) {
  333. case 'I' : $styleData['font']['italic'] = true;
  334. break;
  335. case 'D' : $styleData['font']['bold'] = true;
  336. break;
  337. case 'T' : $styleData['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  338. break;
  339. case 'B' : $styleData['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  340. break;
  341. case 'L' : $styleData['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  342. break;
  343. case 'R' : $styleData['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
  344. break;
  345. }
  346. }
  347. break;
  348. }
  349. }
  350. if (($formatStyle > '') && ($column > '') && ($row > '')) {
  351. $columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
  352. if (isset($this->_formats[$formatStyle])) {
  353. $objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($this->_formats[$formatStyle]);
  354. }
  355. }
  356. if ((!empty($styleData)) && ($column > '') && ($row > '')) {
  357. $columnLetter = PHPExcel_Cell::stringFromColumnIndex($column-1);
  358. $objPHPExcel->getActiveSheet()->getStyle($columnLetter.$row)->applyFromArray($styleData);
  359. }
  360. if ($columnWidth > '') {
  361. if ($startCol == $endCol) {
  362. $startCol = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
  363. $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
  364. } else {
  365. $startCol = PHPExcel_Cell::stringFromColumnIndex($startCol-1);
  366. $endCol = PHPExcel_Cell::stringFromColumnIndex($endCol-1);
  367. $objPHPExcel->getActiveSheet()->getColumnDimension($startCol)->setWidth($columnWidth);
  368. do {
  369. $objPHPExcel->getActiveSheet()->getColumnDimension(++$startCol)->setWidth($columnWidth);
  370. } while ($startCol != $endCol);
  371. }
  372. }
  373. } else {
  374. foreach($rowData as $rowDatum) {
  375. switch($rowDatum{0}) {
  376. case 'C' :
  377. case 'X' : $column = substr($rowDatum,1);
  378. break;
  379. case 'R' :
  380. case 'Y' : $row = substr($rowDatum,1);
  381. break;
  382. }
  383. }
  384. }
  385. }
  386. // Close file
  387. fclose($fileHandle);
  388. // Return
  389. return $objPHPExcel;
  390. }
  391. /**
  392. * Get sheet index
  393. *
  394. * @return int
  395. */
  396. public function getSheetIndex() {
  397. return $this->_sheetIndex;
  398. }
  399. /**
  400. * Set sheet index
  401. *
  402. * @param int $pValue Sheet index
  403. * @return PHPExcel_Reader_SYLK
  404. */
  405. public function setSheetIndex($pValue = 0) {
  406. $this->_sheetIndex = $pValue;
  407. return $this;
  408. }
  409. }