22
New! Save questions or answers and organize your favorite content.
Learn more.
Do you know how can I set the cell type before writing a value in it? I would like to be able to set types like "General", "Text" and "Number".
Thank you.
asked Jun 17, 2013 at 8:24
When the text is a number with leading zeros, then do: [Cuando el texto es un número que empieza por ceros, hacer]
$objPHPExcel->getActiveSheet[]->setCellValueExplicit['A1', $val,PHPExcel_Cell_DataType::TYPE_STRING];
answered Sep 24, 2015 at 15:12
FelipeRFelipeR
4654 silver badges3 bronze badges
The same way as you'd set the type [number format mask] after writing a value to it:
$objPHPExcel->getActiveSheet[]
->getStyle['A1']
->getNumberFormat[]
->setFormatCode[
PHPExcel_Style_NumberFormat::FORMAT_GENERAL
];
or
$objPHPExcel->getActiveSheet[]
->getStyle['A1']
->getNumberFormat[]
->setFormatCode[
PHPExcel_Style_NumberFormat::FORMAT_TEXT
];
Though "Number" isn't a valid format mask.
You can find a list of pre-defined format masks in Classes/PHPExcel/Style/NumberFormat.php or set the value to any valid Excel number format masking string.
answered Jun 17, 2013 at 9:55
Mark BakerMark Baker
207k31 gold badges339 silver badges380 bronze badges
2
try this
$currencyFormat = '_[$* #,##0.00_];_[$* [#,##0.00];_[$* "-"??_];_[@_]';
$textFormat='@';//'General','0.00','@'
$excel->getActiveSheet[]->getStyle['B1']->getNumberFormat[]->setFormatCode[$currencyFormat];
$excel->getActiveSheet[]->getStyle['C1']->getNumberFormat[]->setFormatCode[$textFormat];`
richsage
26.7k8 gold badges57 silver badges65 bronze badges
answered May 7, 2015 at 23:32
Followed Mark's advise and did this to set the default number formatting to text in the whole workbook:
$objPHPExcel = new PHPExcel[];
$objPHPExcel->getDefaultStyle[]
->getNumberFormat[]
->setFormatCode[
PHPExcel_Style_NumberFormat::FORMAT_TEXT
];
And it works flawlessly. Thank you, Mark Baker.
answered Dec 1, 2017 at 15:51
Lucio MollinedoLucio Mollinedo
2,0651 gold badge28 silver badges27 bronze badges
I wanted the Number same as I get from database for example.
00100.220000
00123
0000.0000100
So I modified the code as below
$objPHPExcel->getActiveSheet[]
->setCellValue['A3', '00100.220000'];
$objPHPExcel->getActiveSheet[]
->getStyle['A3']
->getNumberFormat[]
->setFormatCode['00000.000000'];
$objPHPExcel->getActiveSheet[]
->setCellValue['A4', '00123'];
$objPHPExcel->getActiveSheet[]
->getStyle['A4']
->getNumberFormat[]
->setFormatCode['00000'];
$objPHPExcel->getActiveSheet[]
->setCellValue['A5', '0000.0000100'];
$objPHPExcel->getActiveSheet[]
->getStyle['A5']
->getNumberFormat[]
->setFormatCode['0000.0000000'];
gam6itko
14.2k2 gold badges19 silver badges18 bronze badges
answered Dec 11, 2017 at 11:13
Pavan PyatiPavan Pyati
8982 gold badges13 silver badges18 bronze badges
For Numbers with leading zeroes and comma separated:
You can put 'A' to affect the entire column'.
$objPHPExcel->getActiveSheet[]->getStyle['A1']->getNumberFormat[]->setFormatCode[PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1];
Then you can write to the cell as you normally would.
PowerStat
3,7117 gold badges30 silver badges55 bronze badges
answered Jun 26, 2019 at 6:58