strange formatting results with exporting to excel – $objPHPExcel – Grid 4 PHP

Questions › strange formatting results with exporting to excel – $objPHPExcel

Hello Abu asked 8 years ago

Hi Folks,

we're offering an exporti on one of our databases.
Most fields contain text or numbers. There is one mixed field of serial numbers,
which may contain letters and digits. As long as the field contains at minimum one letter everything is fine. Excel recognizes it as text, so far so good.
As soon as the field contains only numbers and its length is less than 8 also everything is fine. BUT as soon the length is 8 or more Excel will crash the number into some crap.
I was looking for some formatting stuff during the export e.g
$objPHPExcel->setActiveSheetIndex(0)->getStyle("G$r")->getNumberFormat()->setFormatCode('0.00');

but I could not find the right stuff on declaring the default data type in the excel sheet.

Any idea?

Best regards
Michael

6 Answers

0 Vote Up Vote Down

Abu Ghufran answered 8 years ago

Hi Michael,

I am sending you demo code that allow you to set this statement using phpgrid export callback function.
https://gist.github.com/gridphp/8fed7ed3758d336e61f29632ac945551

0 Vote Up Vote Down

Michael Bawden answered 8 years ago

Hi Abu,

thx for your answer.
I saw your example already in the demo code, but it does not help me at all. I want to have one cell in the exported excel sheet as a predefined
text field, independent from the source field, which can be a text string or a number format.

regards
Michael

0 Vote Up Vote Down

Abu Ghufran answered 8 years ago

Extending above code, you can have following function. It will set C5 as TEST in exported excel.

function custom_export($param)
{
$objPHPExcel = $param["phpexcel"];
$arr = $param["data"];

$objPHPExcel->setActiveSheetIndex(0)->setCellValue("C5", "TEST");
}

0 Vote Up Vote Down

Michael Bawden answered 8 years ago

Hi Abu,

yeah, I saw that also, but it does not work for me.
I need to define the cell format, not the content.
The content(for this cell) can content numbers and/or letters.
If only numbers, I still want to be the cell a text cell.

ragards

Michael

0 Vote Up Vote Down

Abu Ghufran answered 8 years ago

Hello,

Please refer this:
https://stackoverflow.com/a/17144762/385377

Also:
https://stackoverflow.com/questions/12457610/reading-numbers-as-text-format-with-phpexcel
https://stackoverflow.com/questions/22090978/phpexcel-how-to-change-data-type-for-whole-column-of-an-excel

This phpexcel formatting help can be found on stackoverflow.

0 Vote Up Vote Down

Michael Bawden answered 8 years ago

Hi Abu,

thx for the links.
The second one was exactly what I was looking for.