22

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.

Psyche
  • 8,513
  • 20
  • 70
  • 85

6 Answers6

34

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);
Lucio Mollinedo
  • 2,295
  • 1
  • 33
  • 28
FelipeR
  • 463
  • 4
  • 3
25

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.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 1
    Mark, is it possible to set a default string type for the entire worksheet (not just particular cell) to avoid scientific notation? – Yang Oct 24 '16 at 11:12
  • [setting the default style for a workbook](https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/08-Recipes.md#setting-the-default-style-of-a-workbook) – Mark Baker Oct 24 '16 at 11:54
4

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,912
  • 8
  • 58
  • 65
Bladimir Ramirez
  • 71
  • 5
4

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.

Lucio Mollinedo
  • 2,295
  • 1
  • 33
  • 28
1

I wanted the Number same as I get from database for example.

  1. 00100.220000

  2. 00123

  3. 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
  • 15,128
  • 2
  • 19
  • 18
Pavan Pyati
  • 950
  • 2
  • 13
  • 18
0

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,757
  • 8
  • 32
  • 57
user3490018
  • 11