Back to contents PHP Python Ruby Choose a language:

Microsoft Excel spreadsheet files can contain lots of data in cells, formulae, notes and so on.

For more details, read the php-excel-reader documentation.

Opening the file

Download the Excel file, and load it into the Excel Reader class. This example is taken from an FOI request for tram accidents. require_once('scraperwiki/excel_reader2.php'); $url = "http://www.whatdotheyknow.com/request/82804/response/208592/attach/2/ACCIDENTS%20TRAMS%20Laurderdale.xls"; file_put_contents("/tmp/spreadsheet.xls", scraperWiki::scrape($url)); $book = new Spreadsheet_Excel_Reader("/tmp/spreadsheet.xls");

Find out how big the worksheet is.

print $book->rowcount() . "\n"; print $book->colcount() . "\n";

By default all commands work on the first sheet, number 0. You can pass in a $sheet_index parameter to most functions to select a different worksheet.

Reading values

Read individual cells, starting with (1, 1) at the top left. You can also use the Excel alphabetical column headings.

print $book->val(3, 1) . "\n"; print $book->val(3, 'A') . "\n";

Loop through rows like this.

for ($col = 1; $col <= $book->colcount(); $col++) { print $book->val(3, $col) . ","; }

Saving to the datastore

Work out sensible heading names. In this case we remove the "." from "FLEET NO.".

$keys = array('dummy'); for ($col = 1; $col <= $book->colcount(); $col++) { $keys[] = str_replace(".", "", $book->val(3, $col)); } print_r($keys);

Make an associative array from the rows and save them.

for ($row = 1; $row <= $book->rowcount(); $row++) { for ($col = 1; $col <= $book->colcount(); $col++) { if ($keys[$col]) { $data[$keys[$col]] = $book->val($row, $col); } } $data['rownumber'] = $row; print_r($data); if ($data['DATE'] != 'DATE' && $data['DATE'] && $data['FLEET NO']) { $data['DATE'] = DateTime::createFromFormat('d/m/y', $data['DATE'])->format('Y-m-d'); scraperwiki::save(array('rownumber'), $data); } }