Skip to main content

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 :)




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
  10. hi. i have a problem..some computer this work well... but other computer this not work "The filename abc.xls is not readable." surely same server.. why this situation occur?

    ReplyDelete
  11. I have exported one xls file and then importing it using library of Spreadsheet_Excel_Reader ,it doesn't work for my expoerted xls file while it works fine for newly created xls file with same data.It gives the error of file is not readable due to error but while importing at beginning i had checked the $_FILE uploaded, it have zero error, then how it gives error = 1 after passing file path in Spreadsheet_Excel_Reader object.Please help me.

    ReplyDelete
  12. iam running on ubuntu server show not readable, but on localhost(windows) it is working, pls suggest for me.
    thanks

    ReplyDelete

Post a Comment

Thanks for your valuable comments.

Popular posts from this blog

Odoo/OpenERP: one2one relational field example

one2one relational field is deprecated in OpenERP version>5 but you can achieve the same using many2one relational field. You can achieve it in following two ways : 1) using many2one field in both the objects ( http://tutorialopenerp.wordpress.com/2014/04/23/one2one/ ) 2)  using inheritance by deligation You can easily find the first solution with little search over internet so let's start with 2nd solution. Scenario :  I want to create a one2one relation between two objects of openerp hr.employee and hr.employee.medical.details What I should do  i. Add _inherits section in hr_employee class ii. Add field medical_detail_id in hr_employee class class hr_employee(osv.osv):     _name = 'hr.employee'     _inherits = {' hr.employee.medical.details ': "medical_detail_id"}     _inherit = 'hr.employee'         _columns = {              'emp_code':fields.char('Employee Code', si

How to draw Dynamic Line or Timeseries Chart in Java using jfreechart library?

Today we are going to write a code to draw a dynamic timeseries-cum-line chart in java.   The only difference between simple and dynamic chart is that a dynamic event is used to create a new series and update the graph. In out example we are using timer which automatically calls a funtion after every 1/4 th second and graph is updated with random data. Let's try with the code : Note : I had tried my best to provide complete documentation along with code. If at any time anyone have any doubt or question please post in comments section. DynamicLineAndTimeSeriesChart.java import java.awt.BorderLayout; import java.awt.Color; import java.awt.event.ActionEvent; import java.awt.event.ActionListener; import javax.swing.Timer; import javax.swing.JPanel; import org.jfree.chart.ChartFactory; import org.jfree.chart.ChartPanel; import org.jfree.chart.JFreeChart; import org.jfree.chart.axis.ValueAxis; import org.jfree.chart.plot.XYPlot; import

Odoo: Download Binary File in v10

To download any binary file in Odoo10 following is the link: http://127.0.0.1:8069/web/content?model=<module_name>&field=<field_name>&filename_field=<field_filename>&id=<object_id> module_name    - the name of the model with the Binary field field_name         - the name of the Binary field object_id            - id of the record containing particular file. field_filename    - name of a Char field containing file's name (optional). So if you want to call a function on button click and download the file, code is as follow: file_url = "http://127.0.0.1:8069/web/content?model=<module_name>&field=<field_name>&filename_field=<field_filename>&id=<object_id>" return {     'type': 'ir.actions.act_url',     'url': file_url,     'target': 'new' } In Reports or web page, you can use it as: <t t-foreach="files&qu