Got strange result on php array excel upload

Question

guys! I need some help of my code,why are my array result on excel upload is get a null value? I'm trace the code and debug it but still got nothing.

Here's my excel data:

Title   
=======================================================================================================
|Region |Branch Id  | Agent Code    | Cabang |  Policy No   | Policy Holder     | Remark |Date        |
=======================================================================================================
|NYC    |           |   12345       | BG 3   |  3003659123  | YU KERY           | Remark | 15-05-2017 |
|JKT    |           |   54321       | BG 3   |  3003822124  | PRAWINDRA IRAWAN  | Remark | 30-12-2016 |
=======================================================================================================

I just want get the data start from agent code,but after debug the there null array data and also got strange date too.

                [0] => Array
                    (
                        [0] => 12345
                        [1] => NYC
                        [2] => 3003659123 
                        [3] => YU KERY
                        [4] => Remark
                        [5] => 42870 ?
                        [6] => ???
                    )

Here's my code to get the excel data:

        for ($h = 0; $h <= PHPExcel_Cell::columnIndexFromString($objPHPExcel->getActiveSheet()->getHighestColumn()); $h++)
            if(trim($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($h,2)->getValue()))
                $headerText['sheet0'][] = strtoupper($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($h,2)->getValue()); // get header or field name

        $startRow = 3;  
        for ($i = $startRow; $i <= $objPHPExcel->getActiveSheet()->getHighestRow(); $i++) 
        {
            $dataRow = array();
            for ($j = 2; $j <= count($headerText['sheet0']); $j++) 
            {
                $region = strtolower($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(0,$i)->getValue());
                $branch = strtolower($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1,$i)->getValue());

                if ($region != null) {
                    $dataRow[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($j,$i)->getValue();
                    if(!in_array($region,$arrNPK) && !empty($region))
                    $arrNPK[] = $region;

                } elseif ($region == null && $branch != null) {

                    $dataRow[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($j,$i)->getValue();
                    if(!in_array($branch,$arrNPK) && !empty($branch))
                    $arrNPK[] = $branch;

                }
            }
            $arrResult['sheet0'][$region][] = $dataRow;
            $arrResult['sheet0'][$branch][] = $dataRow;
            print_r($arrResult);exit();
        }

why i made $region and $branch is because sometimes users,only fill one of them in excel.

problem solve. add this code $j <= PHPExcel_Cell::columnIndexFromString($objPHPExcel->getActiveSheet()->getHighestColumn())-1; and if(!in_array($region,$arrNPK) && !empty($region)) $arrNPK[] = $region; if($j==7 ) $dataRow[] = PHPExcel_Style_NumberFormat::toFormattedString($objPHPExcel->getActiveSheet()->getCellByColumnAndRow($j,$i)->getCalculatedValue(),'DD-MMM-YYYY'); else $dataRow[] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($j,$i)->getValue(); — Bobby Z
– Bobby Z, Commented Oct 12, 2017 at 3:56

Evinn · Accepted Answer · 2017-10-11 10:29:51Z

1

There is nothing really wrong with your code, But you can check the current format in the excel. I guess the date in your excel data is in text format , not the date format you should change the excel format.

answered Oct 11, 2017 at 10:29

Evinn

1531 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bobby Z Over a year ago

still got the same result. I format it to "dd-mm-yyyy". What about the null in array data,do you the cause about it ? @evinn

Evinn Over a year ago

can you tell me where did you get excel data from ? is that result from database ? if yes you should check about default value and format that you define in database.

Bobby Z Over a year ago

no. the excel file were from user created manually. that was the sample i created by myself,so in the future the user will follow the excel template i made. @evinn

Evinn Over a year ago

you can try using this code for date issue's $dateyouwant = strtolower($objPHPExcel->getActiveSheet()->getCellByColumnAndRow(7,$i)->getValue()); if(PHPExcel_Shared_Date::isDateTime($dateyouwant)) { $dateyouwant = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dateyouwant)); }

Evinn Over a year ago

i just copy your code to select the region, so my code is to validate when read every row in the interation , if the row in the column 7 (date) is not datetime then convert it, you should copy first my code in the notepad and you will realise where to put it. And for null value, you can check about total row of your excel and $objPHPExcel->getActiveSheet()->getHighestRow() value, is it same or not ?

|

Collectives™ on Stack Overflow

Got strange result on php array excel upload

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related