CODEWITHSUNDEEP
phpphpexcel
Maggi
Maggi

Reputation: 173

converting text in to number format in phpexcel

I have made this sheet using PHPExcel i filled it using an array .

it has values in percent form and it treated as text form by default . how can i convert my all the columns in to numeric form for so i can apply some math formulas on it .

   $excel_out_Questions = $this->outputQuestions($Sets);

   $objPHPExcel->getActiveSheet()->fromArray($excel_out_Questions, "" , 'A5');

excel sheet after filling

i have tried this but it doesn't work ... :/

  $objPHPExcel->getActiveSheet()->getStyle('B434:B444')->getNumberFormat()->setFormatCode (PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

$objPHPExcel->getActiveSheet()->setCellValue( 'B445','=(SUM(B443:B444)-SUM(B434:B440))*100)');

Please suggest.

Thanks in advance

Upvotes: 4

Views: 10864

Answers (2)

Saudi
Saudi

Reputation: 1

Try this

$excel_date = 43010; //here is that value 41621 or 41631
$unix_date = ($excel_date - 25569) * 86400;
$excel_date = 25569 + ($unix_date / 86400);
$unix_date = ($excel_date - 25569) * 86400;
echo gmdate("Y-m-d", $unix_date);

// output: 2017-10-02

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212522

Unless you use setCellValueExplicit(), then PHPExcel uses a value binder to identify the datatype that you are setting in the cell.

By default, this is PHPExcel_Cell_DefaultValueBinder, which performs a very simplistic check on whether the PHP datatype is Null or Boolean, or a numeric value or a string. A value such as 37.7% is a PHP string, so will be treated as a string.

There is also an advanced value binder (PHPExcel_Cell_AdvancedValueBinder) that can perform much more sophisticated checks, and is capable of identifying strings containing basic date formats and converting them to an Excel serialized datetime, and setting a number format mask accordingly. It can also detect currency values, fractions, and (most importantly) percentages.

If you pass a string value like 37.7% through the advanced value binder, it will divide the numeric part by 100, and set the number format mask to a percentage mask, in exactly the same way as the MS Excel GUI.

To enable the advanced value binder, call

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

before instantiating your PHPExcel object.

You can find an example of advanced binder usage in /Examples/29advancedvaluebinder.php

Upvotes: 6

Related Questions