hackatool: March 2012

This article is based on a great project two guys did on google code: http://code.google.com/p/php-excel-reader/ It is opening Excel files without any additional driver like an ODBC connector. I am extending the only - very small - example with another one importing the data of the attached spread sheet to a mysql database. This is the simple structure of the table:

CREATE TABLE IF NOT EXISTS `table1` (
  `value1` int(10) NOT NULL,
  `value2` varchar(255) NOT NULL,
  `value3` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And the code importing the data. You first need to choose the sheet and afterwards the data is directly imported.

<?php

error_reporting(E_ALL ^ E_NOTICE);
require_once 'excel_reader2.php';
$data = new Spreadsheet_Excel_Reader("testdata.xls");

?>
<html>
<head>
</head>
<body>
<form method="POST">
<?php
if(isset($_REQUEST["sheet"]))
{
    $sheet = $_REQUEST["sheet"];
    $db = mysql_connect("localhost", "root", "password") 
        or die("error in mysql_connect");
    mysql_select_db('excel', $db) or die("error in mysql_select_db");

    $rowcount = $data->rowcount($sheet);
    
    for($i = 2; $i < $rowcount; ++$i) //rows are 1 based, first row is header
    {
        $query = "INSERT INTO table1 (`value1`, `value2`, `value3`) 
            VALUES (".$data->val($i, 1, $sheet).", 
                '".$data->val($i, 2, $sheet)."', 
                '".$data->val($i, 3, $sheet)."')";
        mysql_query($query, $db) or die("error in mysql_query");
    }
    mysql_close($db);
}
else
{
    ?>
        <select name="sheet">
    <?PHP
    
        foreach($data->boundsheets as $key => $row)
        {
            ?>
                <option value="<?PHP echo $key; ?>">
                    <?PHP echo $row["name"]; ?></option>
            <?PHP
        }
    ?>
        </select>
        <input type="submit" value="import" />
    <?PHP
}
?>
</form>
</body>
</html>

This is the content of the file:

I have an additional note to this API: formated numerical values (currency, ...) are maybe not displayed correctly. This is maybe due to wrong interpretion of the format string. You can change to use the function "raw" instead of "val" but the string will not be formated.

This article and code is licensed under MIT license

hackatool

Wednesday, March 14, 2012

Import Excel xls in PHP