Sunday, 4 March 2012

phpExcelReader uses and problems?


If you will search over internet about excel reader in PHP at the end you would be redirected to the following this link only. It’s well written code but there are also some problems in that. Some documentation is missing. In this post we are going to discuss how to use phpExcelReader and which type of problems you could face.

Download : 

1) You can download the original library from here.

2) Mine one(with bug fixes) is available here. Go there, Click on File menu then download it.

How to use :

1)  In the package itself example2.php is a very good example of using excel reader.

2)  Beginners can have a look at following example


<?php
            ini_set("display_errors",1);
            error_reporting(E_ALL ^ E_NOTICE);
            require_once 'phpExcelReader/Excel/reader.php';
                       
            $data = new Spreadsheet_Excel_Reader();
            $data->read("files/1331184203.xls");
           
            //If there are n sheets in a excel file then it would run for n times
            for($sheet=0;$sheet<count($data->sheets);$sheet++)
            {
                        for($row=1;$row<=$data->sheets[$sheet]['numRows'];$row++)
                        {
                                    for($col=1;$col<=$data->sheets[$sheet]['numCols'];$col++)
                                    {
                                                $xls[$sheet][$row][$col] = htmlentities($data->sheets[$sheet]['cells'][$row][$col]);
                                    }
                        }
                       
            }
            print "<pre>";
            print_r($xls);
                       
            include "html/beginners.html";
?>


It would return an array. You can use this to save contents in the database or do whatever you want with that.

Problems : 

When you will use the original library, following problem could come.

1)  Warning: require_once(Spreadsheet/Excel/Reader/OLERead.php) [function.require-once]: failed to open stream: No such file or directory in C:\......\phpExcelReader\Excel\reader.php on line 32

Fix :  

Find a line in phpExcelReader\Excel\reader.php with following code:

require_once 'Spreadsheet/Excel/Reader/OLERead.php';

replace it with following line :

require_once 'Excel/oleread.inc';

2) Assigning the return value of new by reference is deprecated in  C:\.....\phpExcelReader\Excel\reader.php on line 264 

Fix : 

Find a line in phpExcelReader\Excel\reader.php with following code : 

      $this->_ole = & new OLERead();  
    
      Replace it with:

      $this->_ole = new OLERead();

Or

$le = new OLERead();
$this-<_ole = & $le;

3)  The filename abc.xls is not readable.

Fix : 

      It means your file is not in a proper format. For proper format check the
      file : Correct.xls


 4)  Date issue : If you are getting wrong dates like 

a)      You have entered the date with format like dd-mm-yyyy ex. 17-03-2008 and it’s returning you with 18/03/2008 (with one day ahead)

b)      You have entered date with format mm-dd-yyyy ex. 03-17-2008 and it’s returning just like 00/1818/08080808

Fix : 


Find function createDate($numValue){.....} in reader.php file and replace it with following code :


function createDate($numValue)
{
        if ($numValue > 1) {
            $utcDays = $numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS);
            $utcValue = round(($utcDays) * SPREADSHEET_EXCEL_READER_MSINADAY);
                 
                  $this->curformat = strtolower($this->curformat);
                  //echo $this->curformat; echo "<br>";
                  if ($this->curformat == 'mm/dd/yyyy' || $this->curformat == 'i/dd/yyyy') {
                        $this->curformat = 'Y-m-d';
                  }
                 
            $string = date ($this->curformat, $utcValue);
            $raw = $utcValue;
        } else {
            $raw = $numValue;
            $hours = floor($numValue * 24);
            $mins = floor($numValue * 24 * 60) - $hours * 60;
            $secs = floor($numValue * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60;
            $string = date ($this->curformat, mktime($hours, $mins, $secs));
        }

        return array($string, $raw);
 }


If you are facing more issues or the package is not working for you, please contact me here or put your comments against the post.

Thanks!!!!!!!!!!!! Enjoy Programming :)




17 comments:

  1. thx ur fixes help me a lot !!!! cheers

    ReplyDelete
  2. What if I have the date format as "12/16/2013 12:00:00 AM"? Every time I have 12:00:00 AM, it subtracts a day.

    ReplyDelete
  3. It's working fine for me. Have you done changes as suggested in step 4 of Problems section?

    ReplyDelete
  4. Thanks a lot....I was struggling more than one week...But now it works fine....

    ReplyDelete
    Replies
    1. But now i'm struggling with fetching datetime from excelsheet..Please help me immediately....

      Delete
    2. Arvind, Can you please tell me exactly what problem you are facing. Please describe a little..............

      Delete
  5. Yes, it's work.
    I need to modify like this :
    if ($this->curformat == 'mm/dd/yyyy' || $this->curformat == 'i/dd/yyyy') {
    $this->curformat = 'Y-m-d';
    }
    if ($this->curformat == 'dd/mm/yyyy' || $this->curformat == 'i/dd/yyyy') {
    $this->curformat = 'd/m/Y';
    }

    for a french date

    ReplyDelete
  6. Hi,
    I am using this excel reader for storing some numbers (% and $ amounts) and strings from .xls file. The issue is when I fetch the data from excel, the '%' fields values always return numbers "Divided by 100" and end up returning "0" if the number is less than 100% (example: 25% returns "0" and not 25). How do I get around this issue? Please help.

    Thanks in advance.
    Abhi

    ReplyDelete
    Replies
    1. Sorry Abhi, due to busy schedule in those days, couldn't reply you back. You fixed the issue?

      Delete
  7. The filename jxlrwtest.xls is not readable
    can you help me using teamviewer?

    ReplyDelete
    Replies
    1. It means your file is not in a proper format. Please check 3rd point in Problems section of this post.

      Delete
  8. hi, i am trying this code to import xls data but it is showing error....

    Catchable fatal error: Object of class Spreadsheet_Excel_Reader could not be converted to string in C:\xampp\htdocs\crs\web\phpExcelReader\Excel\reader.php on line 364


    please fix it.

    ReplyDelete
    Replies
    1. Either you are using library with bugs or problem with your code. In first case please use following library: https://docs.google.com/open?id=0B-hkQE1DccnoZnVkUUVIOWFRT1dRTDRhRUdsWHZkdw

      Delete
  9. 1. $data->sheets[0]['cellsInfo'][$i][$j]['type'] is not giving the type of cell.
    2. This does not work for xlsx. IS there any workaround to make this work for xlxs?

    ReplyDelete
    Replies
    1. send me your code on dirtyhandsphp@gmail.com. I will look into that.

      Delete

Thanks for your valuable comments.

Odoo 10: Close wizard and open standard form

Hi, Today we are going to learn how to open standard form after saving data in wizard. Let's say I have created an wizard to fill bas...