solution for numbers stored as text

Topics: Developer Forum, User Forum
Jan 27, 2014 at 6:45 AM
I am generating one report but it is showing error message with values like 23,222 or values with % symbol.It is showing error as numbers stored as text.Manually i am converting there values to Number.I need a solution for this.

My code is :-
    include_once("system/application/libraries/PHP_Excel/PHPExcel.php");
    $excel = new PHPExcel(); // Create the object
    $rowval = explode("\n",$data); // Splitting the data to get the rows
    $rowcount = count($rowval); // Getting the count of rows
    $colcount = count($fields); // Getting the field/column count

    // Write the heading value to the xl sheet start
    for($col = 0; $col < $colcount; $col++){
        $excel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $fields[$col]);
    }

    // style array for the heading
    $styleArray = array(
                        'font' => array(
                        'bold' => true,
                        )
                ); // style array for the heading

    // applying style for the heading to the entire first row
    $excel->getActiveSheet()->getStyle('A1:XFD1')->applyFromArray($styleArray);

    // Write the values to the xl sheet
    for($i = 0; $i < $rowcount; $i++){
        $cellval = explode("\t", $rowval[$i]);
        $colcount = count($cellval);
        for($j = 0; $j < $colcount; $j++){
            if(!empty($cellval[$j])){

                $excel->getActiveSheet()->setCellValueByColumnAndRow($j, ($i+2), $cellval[$j]);
            }
        }
    }

    $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007'); 
Thanks
Harsh
Jan 27, 2014 at 10:03 AM
It is the $cellVal variable that contains values with the separator of thousands or percentages?
In this case, it must detect these features to 'clean' the value and apply the appropriate format.
Jan 28, 2014 at 7:31 AM
Yes $cellVal variable that contains values with separator or percentages.

Which appropriate format you are talking about?
Jan 28, 2014 at 12:14 PM
I'm talking about number formats that Excel must apply and therefore define with PHPExcel.
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode (PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1).
At the same time, your value (defined with setCellValue or equivalent) no longer have to contain commas.
I used a predefined format but you can also pass a string containing a custom format to the method.
Jan 29, 2014 at 4:49 AM
I have included AdvancedValueBinder.php
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); in my code and it is working fine for all :)
except number format like 23,222.

I have included below code in AdvancedValueBinder.php .But still it is showing error ico with salary.

// Style logic - Numbers
    if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
        // Leading zeroes?
        if (preg_match('/^\d+,\d+$/', $value)) {
            // Convert value to string
            $cell->setValueExplicit( $value, PHPExcel_Cell_DataType::TYPE_STRING);

            // Set style
            $cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );

            return true;
        }
    } 
Please look into it
Jan 29, 2014 at 8:59 AM
'23,222' is not numeric but string, so it does not cross the first test.
Then why force it in text (in addition, the style will not serve much)? If it is a number with thousand separators, removes separators to a real number and applies the style. You can see a very close implementation of your need in the 'check for currency' of the advancedBinder.
Jan 30, 2014 at 5:21 AM
Working fine:)..thanks

But if i used PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 then for large values like 1234567899.66 it is showning ######### in excel sheet.Need to drage the Cell.is there any solution for this?
Jan 30, 2014 at 11:31 AM
To correct the problem of width, two solutions:
-Manually set the size of the column:
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
-Ask PHPExcel to determine the size:
$objPHPExcel->getActiveSheet()->getColumnDimension ('B')->setAutoSize(true);
By setWidth, unity is peculiar. See Chapter 4.6.28 documentation that explains how.
setAutoSize is of course easier to use, but the method make sure to take into consideration a certain number of cases, it is quite heavy, the save method execution time can significantly increase.
Jan 31, 2014 at 10:22 AM
Thanks..It is working fine :)
Apr 29, 2014 at 7:53 AM
harshshahi wrote:
I have included AdvancedValueBinder.php
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); in my code and it is working fine for all :)
except number format like 23,222.

I have included below code in AdvancedValueBinder.php .But still it is showing error ico with salary.

// Style logic - Numbers
    if ($dataType === PHPExcel_Cell_DataType::TYPE_NUMERIC) {
        // Leading zeroes?
        if (preg_match('/^\d+,\d+$/', $value)) {
            // Convert value to string
            $cell->setValueExplicit( $value, PHPExcel_Cell_DataType::TYPE_STRING);

            // Set style
            $cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 );

            return true;
        }
    } 
Please look into it
Thank you! I was running out of ideas, I have an array of usernames, many of which include a leading zero, and I could find no way to make it work, your tip of using setValueExplicit with the TYPE_STRING works!

This however does not, I think it is a bug, since the style is showing correctly in Excel (Text), but the leading zero is gone:
$objPHPExcel->setActiveSheetIndex(0)->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->setActiveSheetIndex(0)->fromArray($Users);

I also tried using like: $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '0123Hello'); in combination with the FORMAT_TEXT above - the 0 is gone...