Sometimes when you are developing a project, it might be necessary to do a parsing of xls documents. To give an example: you do a synchronization between xls worksheets and a website database, and you need to convert xls data to the Mysql and want to do it completely automatically.

If you work with Windows it is simple enough – you just need to use COM objects. However, it is another thing if you work with PHP and need to make it work under the UNIX systems. Fortunately there are many classes and libraries for this purpose. One of them is the class PHPExcel. This library is completely cross-platform, so you will not have problems with portability. 

PHPxcel allows importing and exporting data with Excel and applying formatting styles to the reports. Scraping tasks often require reading, so this is what we are going to learn here. With PHPExcel we can read the following formats:

  • Excel 2007
  • Excel 5.0/Excel 95
  • Excel 97 nd latest
  • PHPExcel Serialized Spreadshet
  • Symbolic Link
  • CSV

The PHPxcel  library is available for downloading here.

The library contains the following classes and files:

/classes                    // The main catalog of library

/clasess/PHPExcel/          // Catalog of classes of PHPExcel

/classess/PHPExcel.php      // The file for including

/documentation             // The full documentation

/license.txt               // The license

/install.txt                // Brief instruction

/examples                  // Catalog with usage and examples

/changelog.txt             // List of changes with the current release

Additionally, it would be a good thing if functionality included storing and getting records from the database. For this purpose we’ll use the PDO class – a convenient instrument to work with MySQL database. Firstly, let’s create a table called “excel_data”:

Reading from Excel file

For the data reading we need the objects of three classes:

PHPExcel_Worksheet_RowIterator – we will use it for row iteration

PHPExcel_Worksheet_CellIterator – we will use it for cell iteration.

PDO – to create a connection to our DB and use it to get data from it.

Below is the example of how we read the data from a worksheet and the database. Let’s say we want to display html table scraping data from excel sheet.

The method getHighestColumn returns the name of the highest column in a string presentation (A1, B2, etc). The static method PHPExcel_Cell::columnIndexFromString returns the number of this column.

Now the data are displayed. Of course, we can use it for any purpose: for example, for the updating of prices and stocks in our website product catalog.

Writing into Excel file

Besides reading, we can use PHPExcel for writing –  importing data to Excel. Below is the example:

 

Instead of the method setCellValueByColumnAndRow(), we could use the method setCellValue(), but in this case we would have to reference the cell by row and column name (A1, B2, etc).

We have reviewed the main methods of the PHPExcel class, which will help us to get data from Excel and manage the workbook content. There are a lot of other methods which you can find in documentation.