Reading EXCEL in PHP, the main methods of the PHPEXCEL class

PHPExcel is a library designed for processing data in MS Excel format. With its help, you can read and write information to files, format their contents, perform operations with formulas, styles, etc.
PHPExcel requires PHP 5.2 or higher to work, as well as the ZIP, XML and GD2 extensions enabled.
Note: The php_zip extension is only needed when using the PHPExcel_Reader_Excel2007, PHPExcel_Writer_Excel2007 and PHPExcel_Reader_OOCalc classes. That is, if you want to use PHPExcel to fully work with files in .xlsx or .ods formats, then you will need a zip extension.

PHPExcel library features:

1. PHPExcel can read various spreadsheet file formats:

• Excel5
• Excel2003XML
• Excel2007
• OOCalc
• SYLK
• Gnumeric
• CSV

2. Creation and processing of documents using PHP

Installing PHPExcel:
1) Download the library from the official website https://github.com/PHPOffice/PHPExcel
2) In the downloaded archive, copy the contents from the classes folder to the root of the site

Archive composition:

/classes                    // Directory of the PHPExcel library  
/classess/PHPExcel/         // Directory of PHPExcel library classes  
/classess/PHPExcel.php      // File for include, contains key methods for data processing  
/documentation              // Complete documentation for library usage  
/license.txt                // License agreement  
/install.txt                // Short installation guide  
/examples                   // Directory with examples for using PHPExcel  
/changelog.txt              // List of changes in the current version  

An example of reading an Excel file using the PHPExcel class

To read data from an Excel file, you need to connect the PHPExcel library file and use the load() method of the PHPExcel_IOFactory class. This method collects data from everyone from all sheets of the current document:

require_once '/path/to/file/PHPExcel.php';  
$excel = PHPExcel_IOFactory::load('/path/to/file/simple.xlsx');  

Next, we form an array of all sheets in the Excel file using a loop:

foreach($excel ->getWorksheetIterator() as $worksheet) {
 $lists[] = $worksheet->toArray();
}

Output of the generated array in the form of HTML tables(s):

foreach($lists as $list) {  
    echo '<table border="1">';  
    // Iterate through rows  
    foreach($list as $row) {  
        echo '<tr>';  
        // Iterate through columns  
        foreach($row as $col) {  
            echo '<td>' . $col . '</td>';  
        }  
        echo '</tr>';  
    }  
    echo '</table>';  
}  

Basic methods and properties of the PHPExcel class

The PHPExcel class is the core of PHPExcel. It contains links to the contained sheets, document security settings, and document metadata. In addition to the above, this class includes many other useful methods for processing the received data. Some of them will be described below.

getSheetCount() - will return the number of sheets in the book;

getSheetNames() - Returns a list of all sheets in a workbook, indexing them in the order in which their "tabs" appear when opened in MS Excel (or other spreadsheet programs);

getSheet(N) - used to access a sheet by index. The position index (N) represents the order in which the worksheet tabs are displayed when the workbook is open in MS Excel (or other spreadsheet programs);

getSheetByName(list_name) - method is used to access a sheet by its name (list_name);

getActiveSheet() - This method allows you to access the active sheet of the document directly. The active sheet is the one that will be active when the workbook is opened in MS Excel (or other suitable spreadsheet program);

setActiveSheetIndex() and setActiveSheetIndexByName() - Using these methods, you can designate a sheet to be active by its index or name.

Getting cell value by coordinates

To get the value of a cell, the cell must first be found by using the getCell method. The value of a cell can be obtained using the following command:

$objPHPExcel->getActiveSheet()->getCell('B8')->getValue();

In case you need calculated cell values, instead of getValue(), use the getCalculatedValue() method:

$objPHPExcel->getActiveSheet()->getCell('B8')->getCalculatedValue();

Getting cell value by rows and columns

To get the value of a cell, the cell in the worksheet must first be found using the getCellByColumnAndRow method. The value of a cell can be obtained using the following command:

$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(1, 8)->getValue();

Conclusion

This article discussed the main capabilities of the PHPExcel library in the area of ​​reading Excel files. In addition to all of the above, it is possible to use this library more flexibly to work with various table parameters. For these questions, it is recommended to consult the documentation. The PHPExcel library is also adapted to work with other file types, such as Excel (BIFF). XLS, Excel 2007 (OfficeOpenXML). XLSX, CSV, Libre / OpenOffice Calc. ODS, Gnumeric, PDF and HTML.