using PHP to read data from Excel 2k7: PHPExcel-Library

Notre Dame de Paris (France), by Christoph Burmeister (own photo)

Notre Dame de Paris (France), by Christoph Burmeister (own photo)

Just started a little project in which I have to read a bunch of data from the most commonly-used „database“: Excel-Sheets. Sure, Excel is not the database we use for professional purposes, but in private sector and for little projects it is the tool-of-choice to provide and receive data, that is easily editable.

With the great PHPExcel-lib, PHP becomes able to read and manipulate Excel-worksheets.

The project:
phpexcel-example-projectoverview

The datasource:
excel-sheet

The index.php where the PHPExcel-specific vars are instantiated for further work (it’s not nice MVC for the moment, but just for now it’s enough)

<?php

require_once './PHPExcel_1.7.8/Classes/PHPExcel.php';

$inputFileName = './books.xlsx';
$colForTitle = 'A';
$colForAuthor = 'B';
$colForState = 'C';
$books = array();

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();

for ($row = 1; $row <= $highestRow; $row++) {
    $title = $objPHPExcel->getActiveSheet()->getCell($colForTitle.$row)->getValue();
    $author = $objPHPExcel->getActiveSheet()->getCell($colForAuthor.$row)->getValue();    
    $state = $objPHPExcel->getActiveSheet()->getCell($colForState.$row)->getValue();    
    $books[] = new Book($title, $author, $state);       
}

include "./page.php";

class Book{
    private $title;
    private $author;
    private $state;
            
    public function __construct($title, $author, $state) {
        $this->title = $title;
        $this->author = $author;
        $this->state = $state;
    }
    
    public function getTitle(){
        return $this->title;
    }
    
    public function getAuthor(){
        return $this->author;
    }   
    
    public function getState(){
        return $this->state;
    }    
}
?>

page.php is the template (or the view) where the books-array will be displayed:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
    <head>
        <title>Star Wars :: B&uuml;cherliste</title>
        <link href="./styles/main.css" rel="stylesheet" type="text/css" />
    </head>
    <body>        
        <table id="booktable">
            <tr>
                <th>Title</th>
                <th>Author</th>
                <th>Status</th>
            </tr>
            <?php
            foreach ($books as &$book) {
                $title = utf8_decode($book->getTitle());
                $author = utf8_decode($book->getAuthor());
                $state = utf8_decode($book->getState());

                echo "<tr>\r\n";
                echo "  <td class=\"" . $state . "\">" . $title . "</td>\r\n";
                echo "  <td>" . $author . "</td>\r\n";
                echo "  <td>" . $state . "</td>\r\n";
                echo "</tr>\r\n";
            }
            ?>
            </table>;

    </body>
</html>

For some decoration and styling, a main.css is provided that simply matches the „Status“-coloumn of the xlsx:

table, tr, td { 
  border-width:1px;
  border-style:solid;
  border-color:black;
  border-collapse: collapse
}

.gelesen {
    background-color: #00cc00;
}

.vorhanden{
    background-color: #ffff33;
}

.vorgemerkt {
    background-color: #ff9999;
}

.bestellt {
    background-color: #ff0000;
}

And the resulting html-table:
phpexcel-htmlview

Resources: