10

I am facing issues with dates and time. I want them to be read just like they appear in the excel workbook.

So, I believe if I get all cells of the sheet using toArray(), then I should simply do something (some format conversion) that will map all cells as strings, dates, times etc.

OR may be there is function like load, which load all data in the workbook as string without any formating or other complex stuff.

matino
  • 17,199
  • 8
  • 49
  • 58
WebSoft
  • 103
  • 1
  • 1
  • 5

1 Answers1

14

toArray() supports the following arguments:

/**
 * @param  mixed    $nullValue          Value returned in the array entry if a cell doesn't 
 *                                      exist
 * @param  boolean  $calculateFormulas  Should formulas be calculated?
 * @param  boolean  $formatData         Should formatting be applied to cell values?
 * @param  boolean  $returnCellRef      False - Return a simple array of rows and 
 *                                      columns indexed by number counting from zero
 *                                      True - Return rows and columns indexed by their 
 *                                      actual row and column IDs
 */

so 

toArray(NULL,TRUE,TRUE);

will return all the cell values in the worksheet (calculated and formatted) exactly as they appear in Excel itself.

So dates will be returned as (for example) 21-Dec-2011 07:30 rather than 47239.318 (depending on the format mask for the cell). Other numeric cells could be returned as 21,357.00 (depending on the format masking for that cell) so it is a two-edged sword.

Gama11
  • 31,714
  • 9
  • 78
  • 100
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 4
    Thanks for answering this question. It worked fine. But I think documentation of toArray(), rangeToArray() (and to all others where applicable) should include a caution to not use `$objReader->setReadDataOnly(true)'. It took me a while to figure out, why your suggestion is not working. I have to dive into the code PHPExel to figure this out. – WebSoft Dec 21 '11 at 23:53
  • That proviso about setReadDataOnly(true) applies throughout PHPExcel, not just to methods like toArray()... adding a caution everywhere it applies would be adding several hundreds of lines to the documentation. However, the documentation updating process is ongoing. – Mark Baker Dec 21 '11 at 23:57
  • Thanks for your reply. Another question: Is it possible to import tab delimated text files into PHPEXCEL object? – WebSoft Dec 22 '11 at 02:05
  • TSV files are just CSV files with a tab rather than a comma as the delimiter... in the same way fgetcsv can read tsv as well as csv files, so can PHPExcel. Just instantiate a CSV Reader $objReader = new PHPExcel_Reader_CSV(), set delimiter to a tab using $objReader->setDelimiter("\t"), and $objPHPExcel = $objReader->load('filename.tsv') – Mark Baker Dec 22 '11 at 07:33