How could I merge the ties below?

Asked

Viewed 103 times

2

This is the spreadsheet:

inserir a descrição da imagem aqui

This is the code:

public function loadExcel($file, $dir)
{
    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $filterValidation = new Application_Model_Filter_FilterValidation();
    if (file_exists($dir . DS . $file)) {
        $objPHPExcel = $objReader->load($dir . DS . $file);
        $arrayData = array();
        //laço 1
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $arrayData[$worksheet->getTitle()] = $worksheet->toArray(null, true, true, true);
        }

        $sheetData = array_values($arrayData)[0];
        $header = $sheetData[1];
        unset($sheetData[1]);
        $data = array_values($sheetData);
        //return $data;
        if(count($data)) {
            $result = array();
            //laço 2
            foreach ($data as $key => $value) {
                if (count($value)) {
                    //laço 3
                    foreach ($value as $letter => $val) {
                        if ($header[$letter] != '' && !is_null($header[$letter]) && !is_numeric($header[$letter])) {
                            if (!is_null($val)) {
                                $result[$header[$letter]][] = $val;
                            }
                        }
                    }
                }
            }
            $new = array();
            //laço 4
            foreach ($result as $key => $val) {
                if (count($val)) {
                    //laço 5
                    foreach ($val as $idx => $dado) {
                        if (count($dado)) {
                            $new[$idx][$key] = $dado;
                        }
                    }
                }
            }
            return $new;
        }
    }
}

Upshot:

Array
(
    [0] => Array
        (
            [NOME] => Luiz Felipe Machado
            [USUARIO] => alunoguten001
            [SENHA] => aluno001
        )

    [1] => Array
        (
            [NOME] => Maria Rita de Cássia
            [USUARIO] => alunoguten002
            [SENHA] => aluno002
        )

)

PS: part of this solution has been resolved in this question, but I don’t think it’s necessary to do three foreach to treat the data, I believe that can improve this in a single loop, I just do not know how could improve?

php phpexcel

  • You could trade these foreach loops for loops for, so the memory consumption would be better, unfortunately a lot can’t be done, since php has few features and is terrible on clean code and optimization.

  • I’m not familiar with the Phpexcel library. I could post the output of the var_dump($data) command before the if(Count($data) command)) ?

  • what exactly the code does to generate this result?

1 answer

0

I’ll give you a suggestion, save this spreadsheet in CSV and use the following code.

function loadFile ( $file ) 
{
    $data = [];
    if ( ( $handle = fopen( $file, "r" ) ) !== FALSE) {
        $i = 0;
        while ( ( $data = fgetcsv( $handle, 1000, ";" ) ) !== FALSE ) {

            if ( $i == 0 ) {
                $i++;
                continue;
            }

            $data[] = [
                'nome'    => utf8_encode( $data[0] ),
                'usuario' => utf8_encode( $data[1] ),
                'senha'   => utf8_encode( $data[2] )
            ];

            $i++;
        }
    }

    return $data;
}

P.S.: This will only be useful for you only with CSV type files.

  • Yeah, it’s no use to me.

Browser other questions tagged php phpexcel

You are not signed in. Login or sign up in order to post.