3

I am trying to read a cell with possible trailing zeros as a string instead of numeric (which strips off leading zeros). The cell is read by integer column/row as below instead of column string as this answer has.

initial code

    $instReader = $reader->load($this->file);
    $sheet = $instReader->getSheet(0);

I tried modifying this from:

$keyCell = $sheet->getCellByColumnAndRow(1,5);

to:

$sheet->setCellValueExplicitByColumnAndRow(1,5, PHPExcel_Cell_DataType::TYPE_STRING);
$keyCell = $sheet->getCellByColumnAndRow(1,5);

the former gives 1407 for $keyCell instead of 01407

the latter gives "s" or ""

how do I treat the cell as string before calling getCellByColumnAndRow and using only integer values for column and row.

(BTW, if this can be done once for an entire column instead of each time for each individual cell that would be better)

Community
  • 1
  • 1
user813801
  • 521
  • 2
  • 6
  • 23

2 Answers2

11
$keyCell = $sheet->getCellByColumnAndRow(1,5)->getValue();

Will read the cell data in the format that it's actually stored by Excel, you can't arbitrarily change that or tell PHPExcel to read it as a different datatype.

However, if the cell has formatting applied, then you can use

$keyCell = $sheet->getCellByColumnAndRow(1,5)->getFormattedValue();

instead, and this will return the data as a string, with whatever format mask was defined in the Excel spreadsheet

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • thanks mark. but does not work. it still returns 1407 instead of 01407 as displayed in microsoft excel. – user813801 Dec 31 '14 at 14:36
  • 1
    Have you set readDataOnly when you're loading the file? If so, then style information (like format masks) aren't loaded, only raw data – Mark Baker Dec 31 '14 at 14:37
  • found the problem. $xlsReader->setReadDataOnly(true); was removing the formating. – user813801 Dec 31 '14 at 14:40
  • did that but then got a memory error for another 300k excel file. consumed over 500mb of ram. is there an alternative solution? – user813801 Jan 01 '15 at 12:45
  • If you're hitting memory limits with PHPExcel, the try using "cell caching" as described in the documentation – Mark Baker Jan 01 '15 at 12:53
  • I did. thank you. did not help. caching worked very well for other files. but as soon as I disabled setReadDataOnly memory use skyrocketed over 100x and crashed the script. – user813801 Jan 01 '15 at 13:09
  • You have to pick the most appropriate caching for your situation, and it only reduces the memory requirements for each cell (dependent on PHP versions)... SQLite/3 is normally the best option for reducing memory, but you may also need to increase the memory that you allow for PHP when working with larger files – Mark Baker Jan 01 '15 at 13:12
  • yes, i used sqlite cache. memory used was only about 10mb but as soon as i disabled setReadDataOnly memory went to over 500mb and crashed the script – user813801 Jan 01 '15 at 13:29
  • PHPExcel (by default) stores all the data that it loads in memory.... how much memory that will take isn't really predictable, it depends on all manner of factors: though largely dictated by the number and content of the cells, the number of styles, also plays a part – Mark Baker Jan 01 '15 at 13:29
  • Cell caching stores some proportion of cell data in a compressed form, or partially outside of PHP memory, reducing the total memory required.... but there may still be a lot of style information that is still stored in PHP memory – Mark Baker Jan 01 '15 at 13:31
  • do you think it's normal that memory goes from 10mb to >500mb just by disabling setReadDataOnly? – user813801 Jan 01 '15 at 13:31
  • No, that seems very abnormal, though not impossible.... I don't know what your spreadsheet contains – Mark Baker Jan 01 '15 at 13:32
  • If you need to look at alternatives, then I maintain a list [here](http://stackoverflow.com/questions/3930975/alternative-for-php-excel/3931142#3931142) but they all have their limitations and issues – Mark Baker Jan 01 '15 at 13:57
  • seems to be loading tens of thousands of empty cells when readonly is disabled. any way to load only those cells which are not empty? did a print_r of the sheet and was about 100mb array. though when i open in excel only about 150 rows and 10 columns – user813801 Jan 01 '15 at 14:08
  • listWorksheetInfo seems to be giving a range thosuands of times more than actual – user813801 Jan 01 '15 at 14:11
  • A cell may contain invisible data (such as nulls or spaces).... open the file in MS Excel itself, and use Shift-Ctrl-End to jump to the last cell in the file. PHPExcel will __always__ load all cells that contain data, whether invisible or not – Mark Baker Jan 01 '15 at 14:17
  • did that. goes to b2:136 while phpexcel detects 9000 rows. could be file is corrupt. any way to load minimal formatting? all I need is to capture possible leading zeros which are being ignored since cell is treated as a number instead of a string. thanks alot for your kind patience. – user813801 Jan 01 '15 at 14:35
  • I can't guess why PHPExcel detects 9000 rows, because I don't have your file, I don't even know what format it is; but if the file was corrupted you'd probably have problems opening it in MS Excel as well as in PHPExcel. No there is no way to load "minimal formatting", because there is nothing that could help define what "minimal formatting" is, there's either formatting or there isn't. Note that the cell content __is__ a number, it's just the formatting that tells Excel to display it with leading zeroes – Mark Baker Jan 01 '15 at 14:48
  • to overcome the memory limits creating a command line php script that parse the excel might do the trick. You have to call it from another php script. – Svetoslav Marinov Mar 10 '16 at 18:20
  • I am adding conditional formula on string and have used $keyCell = $sheet->getCellByColumnAndRow(1,5)->getFormattedValue(); – Umar Adil Apr 28 '18 at 10:53
0

Same issue for me. I become crazy. Tried to set

 $objReader->setReadDataOnly(true);

wasn't working

tried

 $sheet->getCellByColumnAndRow(4,$row)->getValue()

because normaly display text as raw => doesn't working.

So last I change code in library. Edit file named DefaultValueBinder.php Search for dataTypeForValue function and set this :

    } elseif (is_float($pValue) || is_int($pValue)) {
        return PHPExcel_Cell_DataType::TYPE_STRING;//TYPE_NUMERIC patch here;

    } elseif (preg_match('/^\-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)$/', $pValue)) {
        return PHPExcel_Cell_DataType::TYPE_STRING;//TYPE_NUMERIC patch here;

So now return numbers with 0

Fred
  • 399
  • 3
  • 12