0

I have used the solution found within this Answer.

The code I used looks like this: 

  $objWorksheet->getColumnDimension("AM")->setWidth(20);
  $objWorksheet->SetCellValue("AM1", "Date1");
  $objWorksheet->getStyle("AM")
                 ->getNumberFormat()
                   ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

The one problem I had is when I select the entire column as such:

enter image description here

It still says "general".

But when I select a range of columns from 0 to 4500 lets say:

enter image description here

It shows up as "text".

If set to "General" the dates are auto-formmated by Excel and read wrong into PHPExcel.

Will these cells be read as "General" or as "Text"?

Can I rely that they will always be read as "Text" using this method?

Community
  • 1
  • 1
Kellen Stuart
  • 7,775
  • 7
  • 59
  • 82
  • Why aren't you storing dates properly, as an MS EXcel serialized timestamp, and using a valid date/time format mask? – Mark Baker Mar 05 '16 at 16:35
  • @MarkBaker which one do you mean? Under custom - `yyyy-mm-dd hh-mm-ss`? I tried this and the dates came in completely wrong. – Kellen Stuart Mar 05 '16 at 16:41
  • How are you actually setting the date values in those cells? Are they a formatted string, or are they an MS Excel serialized date/time value? If you're using TEXT or GENERAL, that suggests you're storing string values – Mark Baker Mar 05 '16 at 16:43
  • @MarkBaker This sheet is generated with Php Excel. The data comes from a MySqli query which returns a string array. So the incoming values are string values put into the excel sheet. – Kellen Stuart Mar 05 '16 at 16:48
  • Then you should probably convert them to valid MS Excel timestamps using PHPExcel_Shared_Date::stringToExcel(), and apply an appropriate format mask to the cells to display them as dates (e.g. `'yyyy-mm-dd hh:mm:ss'` – Mark Baker Mar 05 '16 at 16:54
  • @MarkBaker Interesting. Is the `yyyy-mm-dd hh:mm:ss` under custom in the cell format? – Kellen Stuart Mar 05 '16 at 16:55
  • You can define format masks yourself, you're not restricted by those standard values defined in MS Excel (or in PHPExcel) - https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4 - and generally they will appear under 'Custom' – Mark Baker Mar 05 '16 at 16:58
  • @MarkBaker Gotcha. Thanks! Learned something new. – Kellen Stuart Mar 05 '16 at 16:59

0 Answers0