Skip to Content

Reading data from Excel seems to be one of those things that is always being asked be it from ABAP, Java, BSP, or what not.   Awhile back while working on a application to help monitor statistics in the SDN community I had a similiar need but since I was building the application with PHP I was at a bit of a lose, I mean sure I can export the Excel file to a CVS file and do it that way but I wanted a way to easily just give the Excel file to a web page and read all the data.

Since I am not going to be the only uploading the statistics into the application to work with them I needed something that did not require a lot of open this file, save as this after modifiying that or removing those lines. I began my hunt which ended very quickly with PHP Excel Reader .

Quite simple and sweet. I downloaded the files (2 of them) needed to work with. Then I opened the example file that came with it:

setOutputEncoding(‘CP1251’);

/***

  • if you want you can change ‘iconv’ to mb_convert_encoding:

  • $data->setUTFEncoder(‘mb’);

*

**/

/***

  • By default rows & cols indeces start with 1

  • For change initial index use:

  • $data->setRowColOffset(0);

*

**/

/***

  • Some function for formatting output.

  • $data->setDefaultFormat(‘%.2f’);

  • setDefaultFormat – set format for columns with unknown formatting

*

  • $data->setColumnFormat(4, ‘%.3f’);

  • setColumnFormat – set format for column (apply only to number fields)

*

**/

$data->read(‘jxlrwtest.xls’);

/*

$data->sheets[0][‘numRows’] – count rows

$data->sheets[0][‘numCols’] – count columns

$data->sheets[0][‘cells’][$i][$j] – data from $i-row $j-column

$data->sheets[0][‘cellsInfo’][$i][$j] – extended info about cell

   

    $data->sheets[0][‘cellsInfo’][$i][$j][‘type’] = “date” | “number” | “unknown”

        if ‘type’ == “unknown” – use ‘raw’ value, because  cell contain value with format ‘0.00’;

    $data->sheets[0][‘cellsInfo’][$i][$j][‘raw’] = value if cell without format

    $data->sheets[0][‘cellsInfo’][$i][$j][‘colspan’]

    $data->sheets[0][‘cellsInfo’][$i][$j][‘rowspan’]

*/

error_reporting(E_ALL ^ E_NOTICE);

for ($i = 1; $i

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Stoyan Stefanov
    Great, thanks for sharing, Graig, this library looks cool!

    BTW, in PEAR (PHP Extension and Application Repository, http://pear.php.net) there is a package for writing Excel spreadsheets. It’s quite powerful, you can put formulas, images and whatnot in the spreadsheet and it writes “real” Excel files. Because, otherwise, you know, you can fake the generation of Excel sheets, by simply sending a normal HTML table with an application/ms-excel HTTP header.

    Interestingly, the PEAR library is called (according to the PEAR conventions) Spreadsheet_Excel_Writer and the one you showed us is Spreadsheet_Excel_Reader. I hope the Reader finds it’s way into PEAR.

    For those interested in the spreadsheet Writer, here’s an intro – http://www.sitepoint.com/article/pear-spreadsheet_excel_writer

    (0) 

Leave a Reply