Reading/Parsing Excel Spreadsheet using PHP

Posted on

This is second article in the series of common PHP problems that a beginner faces. You can check the first article Warning: header data already sent.

In this tutorial we will see, how we can easily parse or read an Excel sheet and display the parsed data to user. Even though we are going to display the output, the other extension of this technique is that we can parse an excel sheet filled with data, in set format, and convert it into SQL statement and insert the data into database. Now this is an easy task on an windows based server, to do the same thing in Linux we need to use some other libraries.

This tutorial assumes that you know PHP.

So let’s get into the details, PHP-ExcelReader is used for parsing the excel sheet.

Example Spreadsheet that we are going to parse

First of all download the PHP-ExcelReader from SourceForge, if you PEAR configured properly or you can download the files [download#10#nohits] from which I have removed the PEAR dependency (note: this is a bad practice, I only use this in my DEV environment for simple parsing and testing, please do not use this file in production environment).

require_once 'reader.php';

I just included the class file.

Now we will use following function for parsing the file, This function basically returns the data parsed from the excel sheet in an associative array. Off-course you have to remember that it is just a quick function to get the job done in DEV environment and you should not use it in production environment, without suitable modifications.

function parseExcel($excel_file_name_with_path)
{

    $data = new Spreadsheet_Excel_Reader();
    $data->setOutputEncoding('CP1251');
    $data->read($excel_file_name_with_path);

    $colname=array('NAME','SKU','PROD GROUP','WAREHOUSE CODE','DATE','LENGTH','WIDTH','COLOR','WEIGHT LB','PACKAGING','COST','RETAIL','WHOLESALE','SHIPPING COST','QUANTITY','MIN INV','MAX INV','DESC');
    $startloging=false;
    $k=0;

    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {

        for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {

            if($data->sheets[0]['cells'][$i][$j]=='NAME')
            {
                $startloging=true;
                break;
           }
           if($startloging)
            {
                $product[$k-1][$j-1]=$data->sheets[0]['cells'][$i][$j];
                $product[$k-1][$colname[$j-1]]=$data->sheets[0]['cells'][$i][$j];

            }

        }

        if($startloging)
        {

            $k=$k+1;

        }

    }

    return $product;

}

In first line we initialize the Spreadsheet_Excel_Reader, then we set the output encoding.

Finally we read the Excel file using read method of Spreadsheet_Excel_Reader class. This basically completes the process of reading the excel spreadsheet. Simple isn’t it?

Rest of the logic is basically parsing the output array of spreadsheet class, to more user friendly and easy to understand array.

The first FOR loop is counting the rows in spreadsheet, while second FOR loop is counting the number of columns in each row.

if($data->sheets[0]['cells'][$i][$j]=='NAME')

This condition is used to find the right row and column while parsing the data array. This ensures that we only return the relevant data from the excel sheet and not everything. In your case replace ‘NAME’ with the name of the first column in your spreadsheet.

Are you satisfied with your knowledge? No, then spent 15 minutes every day on PHPCamp.net a knowledge sharing website for our own PHP community

Once we figure out the correct row( from where we need to pick the data) we save it in both the numeric array and also as associative array.

Here is a variation of this function, where our excel sheet does not have any column name mentioned.

Second Example of Spread Sheet

function parseExcel($excel_file_name_with_path)
{

    $data = new Spreadsheet_Excel_Reader();

    // Set output Encoding.

    $data->setOutputEncoding('CP1251');

    $data->read($excel_file_name_with_path);
    $colname=array('id','name');

    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
          for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {

                $product[$i-1][$j-1]=$data->sheets[0]['cells'][$i][$j];
                $product[$i-1][$colname[$j-1]]=$data->sheets[0]['cells'][$i][$j];

        }

    }

    return $product;

}

If you know some other alternatives please share.

74 thoughts on “Reading/Parsing Excel Spreadsheet using PHP”

  2. Pingback: roScripts - Webmaster resources and websites

  3. Pingback: PHP : Dead Easy Excel Export | Edmonds Commerce Blog

  4. Anyhow i tried use the reader.php but i got this error

    Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 512 bytes)
    any idea how to direct read it without puting the data into arrays? cos my excel file size is 70MB consist of 114 columns and 29,000 rows of datas. Pleaseeeee Help!!

  5. Pingback: | 8 Jam 27 Menit | Catatan

  6. i want to read excel esv file i am able to read it but

    i have some formula’s in my excel csv file i want to read the data with

    output of given formula but i can,t read it..

    the output come with formula

  7. guys anyone here have a fix on the date problem? coz the date output is like this 00/0606/08080808 instead of 08/21/2008… i tried different encodings though TIA

  8. Was there a solution for the memory error…i have a small file 3.5 mb it will not work, but when i cut in half…runs fine.

    I made the date updates and GetInt4 update but that did not help.

    but my file will keep growing…i plan to have a 15 mb file when its all compile.

    If you find a solution for the 70mb file please share the info.

  9. Thank you very much for your advice, Binny VA. I have applied your advice, say no to excel, and it was working properly in 5 minutes with csv.

    Thank you to everybody!

  10. how to install php excel reader. i have xampp folder
    so where i put this new packages inside of this xampp
    anyone pls help me

  11. I use this code for upload a excel file but in my code fisrt i give upload a master xls file on local machine then user add the data and then upload this save file so at that time it’ll give error this file is not readable.and i make another file on local machine it’ll upload so it’s not give an error readable.it’ll easly uploaded so what to do for that?
    pls give reply as soon as possible

  14. When i download master file and then open that file and save as on my local machine so at that time also able to upload.Otherwise it’ll give error for the file is Tmpphp741.tmp is not readable

  16. MY problem with the phpexcelreader is that it can’t read the temporary file PHP uploads via a web form. I don’t want to save every dang file being uploaded. I just want the parsed content and then to move on. Fails. 🙁

  18. I have downloaded the excel reader and putted it on my local web server
    I is working properly but when I was uploaded this on webserer It is showing two type of err The filename Sql2Excel.xls is not readable
    But reading and fetching the records on localhost server. How can Improve and solve the problem with the script.

    • @rakesh please check that actual file name on the erver and file name you specified in the script are same(it is case-sensitive)

  20. function str_database_name($str)
    {
    $str=strtolower(strtr($str,”*()!$’?: ,&+-/.ŠŒŽšœžŸ¥µÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýÿ”,
    “—————SOZsozYYuAAAAAAACEEEEIIIIDNOOOOOOUUUUYsaaaaaaaceeeeiiiionoooooouuuuyy”));
    return $str;
    }
    what is this function

  23. Hi i am creating a spreadsheet when client clicks on a button but when i try to upload the file for reading the contents using “reader.php” i get the following error:

    “The file name template.xls is not readable” i think its going to line # 171 in reader.php page.

    please help me out in this issue Thanks in advance.

  24. @ Srinivas : I sure your directory of the xls file is not true.you need to set the directory again? Examples : in my applycation,the xls file in helpers folder in a com_sim component of Joomla.But the command is $data->read(‘components/com_sim/helpers/hello.xls’);

  25. Any solutions to the Date Error.

    My date also shows in the following way.

    00/1818/0909 when it should be 3/18/09

    i’ve tryed the updates, but no luck.

    thanks

  27. While I am trying to upload a xls file after downloading and editing it, I am getting an error “The filename is not readable”. But if I copy the same content in a new xls and upload it,it’s getting uploaded successfully. I am using Mac and Safari.

  28. I am getting a error “The filename is not readable” while uploading a exel file after downloading and editing it. But if a paste the content in a new excel the error doesn’t come. Please help.

  30. Hi,
    I am working on php 5 I tried the phpExcelReader,i also tried the example files with the downloaded directory and the above tutorial code but it simply didnt work..i dont see any error or any sort of info on the browser that why it didnt work..
    any help is appreciated

    Regards,

  31. well, without error message it is hard to help, but may be you can try to see if path to xls file is correct.

  33. Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 33030145 bytes) in D:xampphtdocstestexcelExceloleread.inc on line 172

    Why do i get this when i run the example script

  35. Thanks for the article, is very usefuly. I have the “is not readable” error and i fix it in this way:

    i have uploaded file in webform and when call to the Spreadsheet_Excel_Reader read method send this argument $_FILES[“myFile”][“tmp_name”] and doesn’t work, i move the file into own directory in this way :

    $path = “data/” .$_FILE[“file”][“name”];
    $data = new Spreadsheet_Excel_Reader();
    $data->setOutputEncoding(‘CP1251’);
    $data->read($path);
    }

    and works fine. I hope thats was we helpfully for you

    Regards

  36. hello people,

    please use “tmp_name” as the second array parameter.

    for example :-
    $data->read($_FILES[‘excelfile’][‘tmp_name’]);

    so it will get the location of where the file is temporarily stored on server.

    thanks.

  37. Hi,
    i have the same error. like this
    “While I am trying to upload a xls file after downloading and editing it, I am getting an error “The filename is not readable”. But if I copy the same content in a new xls and upload it,it’s getting uploaded successfully.”
    i use many method,but i can’t solution this question,i also use ” ‘tmp_name’ as the second array parameter”,but can’t solution.

    please help !!

  38. Thanks for sharing.

    firstly I can’t switch to csv. My problem is in my excel file i have data in different languages (German, Italian, Spanish, Chinese, Japanese, Russian etc…) I am not able to read it all at once.

    I tried using different encoding mechanism but didnt work.

    Anyone????

  42. Pingback: Parse (Read) Excel Spreadsheet from PHP « Expertester

  43. Pingback: Parse Excel document from PHP « My CS231

  44. What about more than 1 sheet? i have an XLS and only want to parse the content of the second excel-sheet. how to do this?

  45. I have more than one table im my excel sheet
    (foreign key relation) and I want UPDATE or
    INSERT my data. In this case a lot of coding
    is required. I have read in other thread about
    dbtube.org. It is a grahpical editor for imports.
    Worth a look.

    marco

  47. Hello,

    I am getting problem while Importing an XLS file which is exported.
    Procedure
    ===========
    1) I exported an xls.
    2) Save that file locally.
    3) Import the same XLS generating error that its unreadable.
    4) I copy the content and make a new xls manually and import and it works.

    Can u please explain why the same exported file is not unreadable.

    Thanks.

  48. I get this: 40087.00 for 10.01.09, any ideas?

    Does anyone know how to put the “correct” date format?

  49. It works great with .XLS files, but doesn’t like the new Excel 2007 .XLSX format. Is that correct? Are there any plans to update phpExcelReader to work with 2007 files?

  50. Hi,

    I need to extract an image from excel sheet and want to save it to local / application folder. How I can achieve this functionality?

    Regards,
    Pritam

  51. Hi,
    You write very good script,but why we add two if condition for var ‘$startloging’.we can add the ‘$k=$k+1’ in one if loop.
    Can it possible to make the one array for each row? Not like the each column & row.

    Have dream day

  52. Hi,
    I made new code for each row wish array.

    for ($i = 1; $i sheets[0][‘numRows’]; $i++)
    {

    for ($j = 1; $j sheets[0][‘numCols’]; $j++) {

    if($data->sheets[0][‘cells’][$i][$j]==’Category Name’)
    {
    $startloging=true;
    break;
    }
    if($startloging)
    {
    $product[$i][$j-1]=$data->sheets[0][‘cells’][$i][$j];
    $product[$i][$colname[$j-1]]=$data->sheets[0][‘cells’][$i][$j];
    $k=$k+1;
    }

    }

    }
    Have dream day

  53. Pingback: Blogalfin: Export file excel ke database mysql |

  55. The unreadable file problem. That error message is generated because of the read function in oleread.inc file. on about line 55 we see that all errors are given the value of “1”. This means that all errors are reported as “unreadable file” even when the file is readable.

  57. It shows me following error
    Fatal error: Allowed memory size of 94371840 bytes exhausted (tried to allocate 71 bytes)in excel_reader/first_test/oleread.inc on line 133

  58. I need to extract excal sheet data and save it to database

    plese help me to extract data form an excel file containing more than one sheets

  59. I want to read an excel file from a web directory to a localhost please help me with how to specify the path. I already tried this …. $data = new Spreadsheet_Excel_Reader(“www.domain.com/excel/$address”.”.xls”);

  60. I have a few columns which have optional values and hence can be empty, how can i check that while inserting, it is giving the error
    “Notice: Undefined offset: 2..” 2 would be the column number where that particular cell is empty. plz its a little urgent

  62. Assigning the return value of new by reference is deprecated in D:xampphtdocscountrylifeexcelsheet_readerreader.php on line 124

    getting this error when i downloaded reader.php, am using php5

  63. is there a way to convert xls files to csv via php?
    i’d like to read an excel file and i’ve heard that reading csv files via php is far more simpler

  64. could you tell me any way to convert xls files into the csv format…
    i want to read an xls file and have heard that reading a csv file is simpler….

  65. i am also having the same issue: Fatal error: Allowed memory size of 94371840 bytes exhausted.

    someone please suggest me if we need to install some special package or something.

    Please help or email me at => [email protected]

  68. Pingback: Programatically Import Downloadable Product from Excel Spreadsheet « jMP Maniac

  69. hello sir,
    i used the coding to read from excel sheet but for date i am getting the formatted value not the date can u give some idea how to get the actual date

    true, // return web page
    CURLOPT_HEADER => false, // don’t return headers
    CURLOPT_FOLLOWLOCATION => true, // follow redirects
    CURLOPT_ENCODING => “”, // handle all encodings
    CURLOPT_USERAGENT => “spider”, // who am i
    CURLOPT_AUTOREFERER => true, // set referer on redirect
    CURLOPT_CONNECTTIMEOUT => 120, // timeout on connect
    CURLOPT_TIMEOUT => 120, // timeout on response
    CURLOPT_MAXREDIRS => 10, // stop after 10 redirects
    CURLOPT_PROXY => ‘192.168.2.66:3128’, // proxy info
    CURLOPT_PROXYPORT => 3128, // proxy port
    CURLOPT_PROXYUSERPWD => “santhanam:SN90*k&yu?”, // proxy user name and password
    CURLOPT_USERAGENT => $_SERVER[‘HTTP_USER_AGENT’]
    );
    $rootUri = ‘http://www.springsgov.com/’;

    $appendUri = isset($_GET[‘url’]) ? ( (isset($_GET[‘download’])) ? $_GET[‘url’] : base64_decode($_GET[‘url’])) : ‘Page.aspx?NavID=453’;

    $target_url = $rootUri . $appendUri;

    if (isset($_GET[‘download’])) {
    _importXls($target_url, $options);
    exit();
    }

    $ch = curl_init($target_url);
    curl_setopt_array($ch, $options);

    $content = curl_exec($ch);
    curl_close($ch);

    //echo $content;
    $html = new simple_html_dom();
    $html->load($content);

    foreach ($html->find(“table td[bgcolor=#DDDDDD] a.LEVEL2_On”) as $values) {
    $title = $values->innertext;
    print ‘href) . ‘”>’ . $values->innertext . ‘‘;
    print ”;
    }
    foreach ($html->find(“table td[bgcolor=#DDDDDD] a.LEVEL2”) as $values) {
    print ‘href) . ‘”>’ . $values->innertext . ‘‘;
    print ”;
    }

    if (!empty($_GET[‘url’])) {
    ?>

    <?php
    echo '’.$title.”;
    foreach ($html->find(“a#content”) as $values) {
    foreach ($values->parent()->find(‘p a’) as $link) {
    print ‘href,$rootUri) . ‘&download=1″>’ . $link->innertext . ‘‘;

    }
    }
    ?> ‘192.168.2.66:3128’, // proxy info
    CURLOPT_PROXYPORT => 3128, // proxy port
    CURLOPT_PROXYUSERPWD => “santhanam:SN90*k&yu?”, // proxy user name and password
    );

    $ch = curl_init($url);
    curl_setopt_array($ch, $downloadOptions);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    $data = curl_exec($ch);

    curl_close($ch);

    file_put_contents($path, $data);

    chmod($path, 0755);
    // ExcelFile($filename, $encoding);
    $xlsData = new Spreadsheet_Excel_Reader();
    // $xlsData = new Spreadsheet_Excel_Reader(“”,false);

    // Set output Encoding.
    $xlsData->setOutputEncoding(‘CP1251’);

    $xlsData->read($path);
    $first = true;
    $baddrCount = 1;
    $maddrCount = 1;
    echo ”;
    for ($i = 1; $i sheets[0][‘numRows’]; $i++) {
    if ($i <= 3) {
    continue;
    }

    echo '’;
    for ($j = 1; $j sheets[0][‘numCols’]; $j++) {
    if ($first) {
    if (preg_match(“/^lic/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j]))) {
    $fieldColumns[$j] = ‘License’;
    } elseif (preg_match(“/^b/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j])) && preg_match(“/addr/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j]))) {
    $fieldColumns[$j] = ‘BAddr’.$baddrCount;
    $baddrCount++;
    } elseif (preg_match(“/^m/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j])) && preg_match(“/addr/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j]))) {
    $fieldColumns[$j] = ‘MAddr’.$maddrCount;
    $maddrCount++;
    } elseif (preg_match(“/^dba/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j]))) {
    $fieldColumns[$j] = ‘DBA’;
    } elseif (preg_match(“/nature/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j]))) {
    $fieldColumns[$j] = ‘B_Nature’;
    } elseif (preg_match(“/phone/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j]))) {
    $fieldColumns[$j] = ‘Phone’;
    } elseif (preg_match(“/begin/”, strtolower($xlsData->sheets[0][‘cells’][$i][$j]))) {

    $fieldColumns[$j] = ‘Begin_Date’;
    }
    $first = ($j == $xlsData->sheets[0][‘numCols’]) ? FALSE : TRUE;
    } else {
    $fieldDatas[$j] = $xlsData->sheets[0][‘cells’][$i][$j];
    if ($j == $xlsData->sheets[0][‘numCols’]) {
    // Insert into DB
    echo”;
    echo $query = “INSERT INTO scrap SET
    `File_Num` = ‘”.$fieldDatas[array_search(‘License’, $fieldColumns)].”‘,
    `Business_Address` = ‘”.$fieldDatas[array_search(‘BAddr1’, $fieldColumns)].”‘,
    `Business_Address_2` = ‘”.$fieldDatas[array_search(‘BAddr2’, $fieldColumns)].”‘,
    `Contact_Address` = ‘”.$fieldDatas[array_search(‘MAddr1’, $fieldColumns)].”‘,
    `Contact_Address_2` = ‘”.$fieldDatas[array_search(‘MAddr2’, $fieldColumns)].”‘,
    `Business_Phone` = ‘”.$fieldDatas[array_search(‘Phone’, $fieldColumns)].”‘,
    `File_Date` = ‘”.cellval($fieldDatas[array_search(‘Begin_Date’, $fieldColumns)],’m/d/Y’).”‘”;
    echo”;
    // mysql_query($query);
    // $fieldDatas[array_search(‘DBA’, $fieldColumns)];
    //$fieldDatas[array_search(‘B_Nature’, $fieldColumns)];
    }
    }
    //echo “”.$xlsData->sheets[0][‘cells’][$i][$j].””;
    }
    echo “”;
    }
    echo ”;

    }
    function cellval($cell, $datemode){

    if ($cell.ctype == $xlrd.XL_CELL_DATE)
    $datetuple = $xlrd.xldate_as_tuple($cell.value, $datemode);
    if ($datetuple[3] ==’0,0,0′)
    {
    return datetime.date($datetuple[0], $datetuple[1], $datetuple[2]);
    return datetime.date($datetuple[0], $datetuple[1], $datetuple[2], $datetuple[3], $datetuple[4],$datetuple[5]) ;
    }
    if ($cell.ctype == $xlrd.XL_CELL_EMPTY)
    return None;
    if ($cell.ctype == $xlrd.XL_CELL_BOOLEAN)
    return $cell.value == 1;
    return $cell;
    }
    ?>

  70. I want to export Images in excel sheet by PHP.I tried to put it showing me as text. I need to show image.Please help me.

    Thank you

  71. What about encoding issues?
    I’ve a pt-BR worksheet and I can’t read cell values that contains latin characters (ô, ã, ó, etc).

    I’ve tried to change the output enconding via setOutputEncoding(), but with no success.

    Any ideas?

  72. Still regarding latin characters issue above, when a cell contains these chars, I got a “null” string instead of the actual cell value.

  73. I tried to read and write excel(.xls) file to database.But I want to know how to read and write excel(.xlsx)file

Comments are closed.