Home > Ajax, Back End, Front End Development, Javascript, PHP > Reading Excel Documents with PHP and Ajax

Reading Excel Documents with PHP and Ajax

August 16th, 2010


Every wanted to display information from an excel document on a website? Ever wanted to do it asynchronously? Today is your lucky day. This article will show you how to load in an excel document using php, jquery and ajax.

What are we building?

We are going to build a page that pulls in each row from you excel document and styles it to our liking. We’ll be doing this by building a php page that spits out json. Then with the magic of jQuery we’ll pull that information in and parse it to our liking.

Download Example

Excel Ajax

Required Tools

We’ll be using a pre-made php class to handle reading of our excel file. The one I used is call php-excel-reader. You’ll also need an excel document to read from. I’ll be using a list of hair salons in Canada….don’t ask, it was for a project I was working on. It has all the contact info for each business. Besides that you should be good to go.

Extending Class to Meet Our Requirements

The php excel reader class isn’t prefect for our needs. We need some sort of method to take each cell and dump it into an array. Also, the class doesn’t handle phone number formatting very well. So what are we going to do? Create our own class and extend the excel reader class with our new methods…fun! Why not just edit the original class? It’s generally bad practice to hack the core. Any mistakes we’ll be making will be contained within our new file.

First thing is first. We need to create our class.

Our Class -> ExcelReader.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
    <?php
        //Load in original excel reader class
        require_once 'excel_reader2.php';
       
        /**
        * Adds Function to excel reader class
        */
        class ExcelReader extends Spreadsheet_Excel_Reader
        {
            /*
                This method will spit out an array
                of all our excel document rows.
            */
            function dumptoarray($sheet=0) {
                # code...
            }
           
            /*
                This method will overwrite a method in
                the Spreadsheet_Excel_Reader.  We will
                add the functionality to handle phone
                number formatting.
            */
            function _format_value($format,$num,$f) {
                # code...
            }
           
        }
    ?>

 

Now on to the method to convert each of the rows into an array.

The dumptoarray method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
    <?php
    function dumptoarray($sheet=0) {
        $array = array();
       
        //Step through each row.
        for($row=1;$row<=$this->rowcount($sheet);$row++) {
           
            //Step through each column
            for($col=1;$col<=$this->colcount($sheet);$col++){
               
                //Add each cell value into the array.
                $array[$row][$col] = htmlentities($this->val($row,$col,$sheet));
               
            }
           
        }

        return $array;
    }
    ?>

 

If you don’t have a phone number formatting in your excel document feel free to skip this. All we’ll be doing is copying the method from the original class, and place in some new code that handles formatting for phone numbers.

Here is the extra code we’ll be placing in the code from the original class.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
  //Handle Phone Numbers
 
  //Check to see if formatting fits (###) ###-####
  $phone_regex = "/\((\d{3})\) (\d{3})-(\d{4})/";
  if (preg_match($phone_regex,$pattern,$matches)) {

    //Puts the number in the cell into the formatting we require.
    $formatted = "(".substr($num,0,3).") ".substr($num,3,3)."-".substr($num,6,4);
   
    //sets pattern for use in class.
    $pattern = preg_replace($phone_regex, $formatted, $pattern);
  }
 
  //set values and exits method.
  return array(
    'string'=>$pattern,
    'formatColor'=>$color
  );
?>

 

So now our method looks something like…

_format_value method

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
<?php
  function _format_value($format,$num,$f) {
      // 49==TEXT format
      // http://code.google.com/p/php-excel-reader/issues/detail?id=7
      if ( (!$f && $format=="%s") || ($f==49) || ($format=="GENERAL") ) {
        return array('string'=>$num, 'formatColor'=>null);
      }

      // Custom pattern can be POSITIVE;NEGATIVE;ZERO
      // The "text" option as 4th parameter is not handled
      $parts = split(";",$format);
      $pattern = $parts[0];
      // Negative pattern
      if (count($parts)>2 && $num==0) {
        $pattern = $parts[2];
      }
      // Zero pattern
      if (count($parts)>1 && $num<0) {
        $pattern = $parts[1];
        $num = abs($num);
      }

      $color = "";
      $matches = array();
      $color_regex = "/^\[(BLACK|BLUE|CYAN|GREEN|MAGENTA|RED|WHITE|YELLOW)\]/i";
      if (preg_match($color_regex,$pattern,$matches)) {
        $color = strtolower($matches[1]);
        $pattern = preg_replace($color_regex,"",$pattern);
      }

      // In Excel formats, "_" is used to add spacing, which we can't do in HTML
      $pattern = preg_replace("/_./","",$pattern);

      // Some non-number characters are escaped with \, which we don't need
      $pattern = preg_replace("/\\\/","",$pattern);

      // Some non-number strings are quoted, so we'll get rid of the quotes
      $pattern = preg_replace("/\"/","",$pattern);

      // TEMPORARY - Convert # to 0
      $pattern = preg_replace("/\#/","0",$pattern);

      // Find out if we need comma formatting
      $has_commas = preg_match("/,/",$pattern);
      if ($has_commas) {
        $pattern = preg_replace("/,/","",$pattern);
      }

      // Handle Percentages
      if (preg_match("/\d(\%)([^\%]|$)/",$pattern,$matches)) {
        $num = $num * 100;
        $pattern = preg_replace("/(\d)(\%)([^\%]|$)/","$1%$3",$pattern);
      }


      //Handle Phone Numbers
      $phone_regex = "/\((\d{3})\) (\d{3})-(\d{4})/";
      if (preg_match($phone_regex,$pattern,$matches)) {

        $formatted = "(".substr($num,0,3).") ".substr($num,3,3)."-".substr($num,6,4);

        $pattern = preg_replace($phone_regex, $formatted, $pattern);
      }

      return array(
        'string'=>$pattern,
        'formatColor'=>$color
      );

      // Handle the number itself
      $number_regex = "/(\d+)(\.?)(\d*)/";

      if (preg_match($number_regex,$pattern,$matches)) {
        $left = $matches[1];
        $dec = $matches[2];
        $right = $matches[3];
        if ($has_commas) {
          $formatted = number_format($num,strlen($right));
        }
        else {
          $sprintf_pattern = "%1.".strlen($right)."f";
          $formatted = sprintf($sprintf_pattern, $num);
        }
        $pattern = preg_replace($number_regex, $formatted, $pattern);

      }

      return array(
        'string'=>$pattern,
        'formatColor'=>$color
      );
  }
?>

JSON Our Entries

Now it’s as simple as pulling in our excel file, calling our dumptoarray method and convert it to JSON. The JSON will be read by jQuery and parsed accordingly.

To do all of this, we’ll create the file list.php and place everything in there.

list.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
    <?php
    //load in our class we created.
    require_once 'ExcelReader.php';
   
    //Reads our excel document.
    $data = new ExcelReader("entries.xls",false);
   
    //Put our rows into an array.
    $ary = $data->dumptoarray();
   
    //Convert the array in json.
    $json = json_encode($ary);
   
    //display the json.
    print $json;
    ?>

Load in Our JSON via Ajax

Our HTML is going to be pretty simple. We’re just going to have one div with an id=”content” and a title.

Here is the HTML

1
2
3
4
5
6
7
8
9
10
11
12
13
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="ru">
<head>
    <title>Excel Reader</title>
    <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>
   
</head>
<body>
    <div id="content">
        <h1 id="excelEntries">Hair Salons in Canada</h1>
    </div> 
</body>
</html>

Our jQuery isn’t overly complicated. We load in the JSON via Ajax, and we just step through each entry and format it accordingly.

The jQuery

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
    $.getJSON("list.php",
      function(data){
                var item = data;
                var content = "";
               
                //Loop through each entry.
                for (var i in data) {

                        // data[row][column] references cell from excel document.
                        var name = data[i][1];
                        var address = data[i][2];
                        var city = data[i][3];
                        var province = data[i][4];
                        var postal = data[i][5];
                        var phone = data[i][6];
                   
                    //First row is column headers.
                    if (i > 1){
                       
                        //Format Entry
                        content += "<div class='entry'>"+
                        "<h4> "+name+ "</h4>"+
                        "<strong>Address:</strong> "+address+"<br />"+
                        "<strong>City:</strong> "+city+"<br />"+
                        "<strong>Province:</strong> "+province+"<br />"+
                        "<strong>Postal Code:</strong> "+postal+"<br />"+
                        "<strong>Phone Number:</strong> "+phone+
                        "</div>";
                    }
       
                  };
               
                //Add entry to page.
                $('#content').append(content);
    });

After adding some css, your HTML page should look something like…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="ru">
<head>
    <title>Excel Reader</title>
    <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>
    <style type="text/css" media="screen">

        body{
            font-family: Arial, "MS Trebuchet", sans-serif;
            font-size:.8em;
            color:#333;

        }
        /* Styling Entries */
        .entry{

            background:#efefef;
            padding:10px;
            border:1px solid #ddd;
            line-height:1.5em;
            margin:0 5px 5px 0px;
            width:400px;
            float:left;

            /* Rounded Corners */
            -moz-border-radius:10px;
            -webkit-border-radius:10px;
            -khtml-border-radius:10px;
            border-radius:10px;

            /*Inner shadow */
            -moz-box-shadow: 2px 2px 3px #ddd; /* FF3.5 */
            -webkit-box-shadow: 2px 2px 3px #ddd; /* Saf3.0 , Chrome */
            box-shadow: 2px 2px 3px #ddd; /* Opera 10.5, IE 9.0 */

            /* Gradient */
            filter: progid:DXImageTransform.Microsoft.gradient(startColorstr='#efefef', endColorstr='#dedede'); /* for IE */
            background: -webkit-gradient(linear, left top, left bottom, from(#efefef), to(#dedede)); /* for webkit browsers */
            background: -moz-linear-gradient(top,  #efefef,  #dedede); /* for firefox 3.6+ */


        }

        h4{
            margin:3px 0px;
            font-size:1.5em;
            color:#014A8D;
            text-shadow:0 1px 1px #fff;
        }

    </style>
</head>
<body>

<div id="content">
    <h1 id="excelEntries">Hair Salons in Canada</h1>
</div>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript" charset="utf-8">
    $.getJSON("list.php",
      function(data){
                var item = data;
                var content = "";

                for (var i in data) {

                        // data[row][column] references cell from excel document.
                        var name = data[i][1];
                        var address = data[i][2];
                        var city = data[i][3];
                        var province = data[i][4];
                        var postal = data[i][5];
                        var phone = data[i][6];

                    if (i > 1){

                        content += "<div class='entry'>"+
                        "<h4> "+name+ "</h4>"+
                        "<strong>Address:</strong> "+address+"<br />"+
                        "<strong>City:</strong> "+city+"<br />"+
                        "<strong>Province:</strong> "+province+"<br />"+
                        "<strong>Postal Code:</strong> "+postal+"<br />"+
                        "<strong>Phone Number:</strong> "+phone+
                        "</div>";
                    }

                  };
                $('#content').append(content);
       });
</script>

</body>
</html>

That should be it. Be sure to download all the files and fiddle around with it yourself. Also be sure to read up on the documentation that comes with the excel reader class.

Download Example

 

 

Author:
Categories: Ajax, Back End, Front End Development, Javascript, PHP
Tags: , , , ,



Top