Empty rows

Topics: User Forum
Sep 21, 2008 at 5:30 PM
Hi to all!
I created a script to convert excel data in MYSQL data using this marvellous class. But every time I upload an excel file, I found 2 empty rows at the end of the array with data. Is it normal? If this is a usual behaviour of the class, I will use a "for" insted a "foreach" to publish a report of the inserted file so as to remove the last 2 rows...

Thanks you
Gianluca
Developer
Sep 22, 2008 at 12:11 AM
I think this isn't normal behavior. But it might be the case that some ”empty” row aren't as empty as it looks. For example if you change the row height or specify a specific style in an empty cell, then the row counts as non-empty.

You may try to delete some of the ”empty” rows and see if it makes a difference. Alternatively, you may use setReadDataOnly(true) for the reader to ignore styling. Then styles will not be loaded and getHighestRow() for the worksheet will return the highest row containing data.
Nov 16, 2008 at 10:12 AM
Edited Nov 16, 2008 at 10:38 AM
Unfortunately, your suggest doesn't work and I don't know why! :-|

This is the method that I use to instance the class after the upload

[PHP]
private function caricaFile(){
        /** Include path **/
        set_include_path('include/class/');
        
        /** PHPExcel */
        include 'PHPExcel.php';
        
        /** PHPExcel_IOFactory */
        include 'PHPExcel/IOFactory.php';
        
        //estraggo l'estensione del file
        $explode_nome_file = explode(".", $this->nome_file);
        $estensione = $explode_nome_file[1];
        
        //istanzio l'oggetto che dovrà accedere al file excel
        switch ($estensione){
            case "xls":
                $objReader = PHPExcel_IOFactory::createReader("Excel5");
            break;
            
            case "xlsx":
                $objReader = PHPExcel_IOFactory::createReader("Excel2007");
            break;
            
            default:
                //inserire l'errore del file sbagliato
            break;
        }
                
        //carico il file
        $this->objPHPExcel = $objReader->load($this->nome_file);
        
        //attivo il primo foglio
        $this->selezionaFoglio(0);
    }

    private function selezionaFoglio($numero){
        $this->foglio = $this->objPHPExcel->getSheet($numero);
    }
[/PHP]
and this is the method where the getHighestRow() function makes the mistake:

[PHP] 
private function estraiDati(){
        
        //verifico l'ultima riga compilata del file excel
        $ultimaRiga = $this->objPHPExcel->getActiveSheet()->getHighestRow();
              
        for ($i=0; $i<=$ultimaRiga; $i++){
       //do something
       }
}
[/PHP]

Thank you!
Gianluca



Developer
Nov 16, 2008 at 3:01 PM
Did you try this:

switch ($estensione){
            case "xls":
                $objReader = PHPExcel_IOFactory::createReader("Excel5");
                $objReader->setReadDataOnly(true);
            break;
            
            case "xlsx":
                $objReader = PHPExcel_IOFactory::createReader("Excel2007");
                $objReader->setReadDataOnly(true);
            break;
            
            default:
                //inserire l'errore del file sbagliato
            break;
        }
Nov 17, 2008 at 9:01 AM
Ok, I found my mistakes (yes I tried your code before write here)!

Firstly, the "for" cicle starts from 0 while it should start from 1.

Secondly, I made a method that check if the excel file contains labels and eventually shifts the pointer to the following row. In this way I can't use getHighestRow() but getHighestRow()-1!

Thank you for your patience
Gianluca