Processing and processing reports in Excel in PHP
Not rarely, when developing a project, it becomes necessary to form reporting statistics. If the project is developed in Delphi , C # or for example, in C ++ and under Windows , then there are no problems. Just need to use the COM object. But things are different if you need to generate a report in excel format for PHP . And for this creation to function on UNIX-like systems. But, fortunately, not so bad. And there are enough libraries for this. I stopped my choice on PHPExcel . I have been working with this library for a couple of years now and am satisfied. Since it is cross-platform, there are no problems with portability.
PHPExcel allows you to import and export data to excel. Apply different styles to reports. In general, everything is on top. There is even the possibility of working with formulas (I have not tried it myself). Just remember that all work (read and write) must be in the utf-8 encoding.
Library installation
A version of PHP 5.2.0 or higher is required. The following extensions are also needed: php_zip, php_xml and php_gd2. You can download the library from here .
Using the PHPExcel library, you can write data in the following formats:
')
Consider an example of the formation of the multiplication table.
Next we need to get our * .xls file. Here you can go two ways. If we assume you have an online store, and the customer wants to download the price list, then it would be better to resort to this conclusion:
Here, the generated data immediately “spit out” in the browser. However, if you need to save the file and not “discard” it immediately, then you do not need to output HTTP headers and instead of “php: // output” you should specify the path to your file. Remember that the directory in which the file is supposed to be created must have write access. This applies to UNIX-like systems.
Consider another example of three useful instructions:
Also notice these methods here: setCellValue and setCellValueByColumnAndRow .
That is, you can access cells in two different ways. Which is very convenient.
It is often necessary to highlight some data in a report. Make a font selection or apply a frame with a background fill for some cells, etc. What allows you to concentrate on the most important information (although it may conversely distract). For these purposes, the PHPExcel library has a whole set of styles that can be applied to cells in excel. Of course, there is a small “minus” in this library - it is impossible to apply the style to several cells at the same time, but only to each individually. But this does not create discomfort when developing web applications.
You can assign a style to a cell in two ways:
The value of the fill parameter is an array with the following optional parameters:
Fill styles
An example of specifying settings for the fill:
Or you can use the following methods:
$ PHPExcel_Style-> getFill () -> setFillType (PHPExcel_Style_Fill :: FILL_GRADIENT_LINEAR);
$ PHPExcel_Style-> getFill () -> setRotation (0);
$ PHPExcel_Style-> getFill () -> getStartColor () -> applyFromArray (array ('rgb' => 'C2FABD'));
$ PHPExcel_Style-> getFill () -> getEndColor () -> applyFromArray (array ('argb' => 'FFFFFFFF')).
Quite rarely, but it is useful to insert an image into a report. This could be a logo, a scheme, etc. To work we need the following methods:
Code showing the image insertion algorithm is shown below:
Here is the report with the image inserted:
The value of the font parameter is an array that contains the following optional parameters:
Underline styles
Example of specifying settings for the font:
Or use the following methods:
$ PHPExcel_Style-> getFont () -> setName ('Arial');
$ PHPExcel_Style-> getFont () -> setBold (true);
$ PHPExcel_Style-> getFont () -> setItalic (false);
$ PHPExcel_Style-> getFont () -> setSuperScript (false);
$ PHPExcel_Style-> getFont () -> setSubScript (false);
$ PHPExcel_Style-> getFont () -> setUnderline (PHPExcel_Style_Font :: UNDERLINE_DOUBLE);
$ PHPExcel_Style-> getFont () -> setStrikethrough (false);
$ PHPExcel_Style-> getFont () -> getColor () -> applyFromArray (array ('rgb' => '808080'));
$ PHPExcel_Style-> getFont () -> setSize (12).
The value of the borders parameter is an array that contains the following optional parameters:
Line styles
Example of specifying settings for the frame:
You can also resort to using the following methods:
$ PHPExcel_Style-> getBorders () -> getLeft () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getRight () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getTop () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getBottom () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getDiagonal () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> setDiagonalDirection (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))).
The value of the alignment parameter is an array that accepts four optional parameters as input:
Horizontal alignment
Vertical Alignment
An example of alignment style settings:
And you can use the following methods:
$ PHPExcel_Style-> getAlignment () -> setHorizontal (PHPExcel_Style_Alignment :: HORIZONTAL_CENTER);
$ PHPExcel_Style-> getAlignment () -> setVertical (PHPExcel_Style_Alignment :: VERTICAL_JUSTIFY);
$ PHPExcel_Style-> getAlignment () -> setTextRotation (10);
$ PHPExcel_Style-> getAlignment () -> setWrapText (true);
$ PHPExcel_Style-> getAlignment () -> setShrinkToFit (false);
$ PHPExcel_Style-> getAlignment () -> setIndent (5).
The numberformat parameter is an array that includes only one parameter: code is the data format of the cell.
List of possible formats
Example setup for cell data format:
And you can use the method:
$ PHPExcel_Style-> getNumberFormat () -> setFormatCode (PHPExcel_Style_NumberFormat :: FORMAT_CURRENCY_EUR_SIMPLE);
The value of the protection parameter is an array, which contains two optional parameters:
Parameter setting example for cell protection:
Or use the following methods:
$ PHPExcel_Style-> getProtection () -> setLocked (true);
$ PHPExcel_Style-> getProtection () -> setHidden (false);
Now we know what style settings there are and what parameters each style has. Now we apply a layout style to the cells of the table, but we will do this using two methods. The first method is to create an array of settings, which we pass as the parameter to the applyFromArray method, PHPExcel_Style class.
Next we apply the style we created to the excel cells.
Now apply the same style, but using a different technique.
Here's what we got:
If you want to apply the style many times, then the first method is better, in the other case, it is better to stay on the second. To obtain an object (an instance of the class PHPExcel_Style ) of the cell responsible for the style, you must use one of the following methods:
Generating reports and applying styles to them is of course excellent. But the possibilities of the PHP Excel library do not end there. Well, let's look at what she is still capable of. And it is also capable of reading data from * .xls / * .xlsx files.
Using the PHPExcel library, you can read the following formats:
To work we need objects of two classes:
For demonstration we will display data from the table with information about cars.
An example of reading the file is presented below:
First option
Second option
In the first variant, we read data from cells using iterators. And in the second, we use index addressing to access and retrieve data from the cells of the paper. You can get data on the number of rows and columns using the following methods of the PHPExcel_Worksheet class:
And also, we cannot do without the help of the columnIndexFromString method, which is part of the PHPExcel_Cell class. This method allows you to determine the column index by its symbolic representation.
With the help of the demonstrated capabilities, you can generate and read any reports in the form of files, excel format. And also almost all possible methods for working with styles were demonstrated.
PHPExcel allows you to import and export data to excel. Apply different styles to reports. In general, everything is on top. There is even the possibility of working with formulas (I have not tried it myself). Just remember that all work (read and write) must be in the utf-8 encoding.
Library installation
A version of PHP 5.2.0 or higher is required. The following extensions are also needed: php_zip, php_xml and php_gd2. You can download the library from here .
Using the PHPExcel library, you can write data in the following formats:
- Excel 2007;
- Excel 97 and later versions;
- PHPExcel Serialized Spreadshet;
- HTML;
- PDF;
- CSV.
')
Import data from PHP to Excel
Consider an example of the formation of the multiplication table.
// excel require_once('PHPExcel.php'); // excel require_once('PHPExcel/Writer/Excel5.php'); // PHPExcel $xls = new PHPExcel(); // $xls->setActiveSheetIndex(0); // $sheet = $xls->getActiveSheet(); // $sheet->setTitle(' '); // A1 $sheet->setCellValue("A1", ' '); $sheet->getStyle('A1')->getFill()->setFillType( PHPExcel_Style_Fill::FILL_SOLID); $sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('EEEEEE'); // $sheet->mergeCells('A1:H1'); // $sheet->getStyle('A1')->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER); for ($i = 2; $i < 10; $i++) { for ($j = 2; $j < 10; $j++) { // $sheet->setCellValueByColumnAndRow( $i - 2, $j, $i . "x" .$j . "=" . ($i*$j)); // $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()-> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } } Next we need to get our * .xls file. Here you can go two ways. If we assume you have an online store, and the customer wants to download the price list, then it would be better to resort to this conclusion:
// HTTP- header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" ); header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" ); header ( "Cache-Control: no-cache, must-revalidate" ); header ( "Pragma: no-cache" ); header ( "Content-type: application/vnd.ms-excel" ); header ( "Content-Disposition: attachment; filename=matrix.xls" ); // $objWriter = new PHPExcel_Writer_Excel5($xls); $objWriter->save('php://output'); Here, the generated data immediately “spit out” in the browser. However, if you need to save the file and not “discard” it immediately, then you do not need to output HTTP headers and instead of “php: // output” you should specify the path to your file. Remember that the directory in which the file is supposed to be created must have write access. This applies to UNIX-like systems.
Consider another example of three useful instructions:
- $ sheet-> getColumnDimension ('A') -> setWidth (40) - sets the column “A” 40 units wide;
- $ sheet-> getColumnDimension ('B') -> setAutoSize (true) - here the automatic width will be set for the column “B”;
- $ sheet-> getRowDimension (4) -> setRowHeight (20) - sets the fourth row height to 20 units.
Also notice these methods here: setCellValue and setCellValueByColumnAndRow .
- setCellValue (pCoordinate, pValue, returnCell = false) takes three parameters: the cell coordinate, the data to output to the cell, and the third parameter is optional (if you set it to true, the method will return the cell object, otherwise the worksheet object);
- setCellValueByColumnAndRow (pColumn, pRow, pValue = null, returnCell = false) takes four parameters: the column number of the cell, the row number of the cell, the data to output to the cell, and the fourth parameter works similarly to the third parameter of the setCellValue method.
That is, you can access cells in two different ways. Which is very convenient.
Making a report using PHP in Excel
It is often necessary to highlight some data in a report. Make a font selection or apply a frame with a background fill for some cells, etc. What allows you to concentrate on the most important information (although it may conversely distract). For these purposes, the PHPExcel library has a whole set of styles that can be applied to cells in excel. Of course, there is a small “minus” in this library - it is impossible to apply the style to several cells at the same time, but only to each individually. But this does not create discomfort when developing web applications.
You can assign a style to a cell in two ways:
- Apply the applyFromArray method, class PHPExcel_Style . An array with the following parameters is passed to the applyFromArray method:
- fill - an array with fill parameters;
- font - an array with font parameters;
- borders - an array with the parameters of the frame;
- alignment - an array with alignment parameters;
- numberformat - an array with the format parameters of the data cell;
- protection - array with cell protection parameters.
- Use the class method PHPExcel_Style for each of the styles separately. For example, you can assign a font to a cell like this: $ sheet-> getStyle ('A1') -> getFont () -> setName ('Arial').
Fill
The value of the fill parameter is an array with the following optional parameters:
- type - fill type;
- rotation - the angle of the gradient;
- startcolor - value in the form of an array with the initial color parameter in the RGB format;
- endcolor - value as an array with the final color parameter in ARGB format;
- color - value in the form of an array with the initial color parameter in the RGB format.
Fill styles
| FILL_NONE | none |
| FILL_SOLID | solid |
| FILL_GRADIENT_LINEAR | linear |
| FILL_GRADIENT_PATH | path |
| FILL_PATTERN_DARKDOWN | darkDown |
| FILL_PATTERN_DARKGRAY | darkGray |
| FILL_PATTERN_DARKGRID | darkGrid |
| FILL_PATTERN_DARKHORIZONTAL | darkHorizontal |
| FILL_PATTERN_DARKTRELLIS | darkTrellis |
| FILL_PATTERN_DARKUP | darkUp |
| FILL_PATTERN_DARKVERTICAL | darkVertical |
| FILL_PATTERN_GRAY0625 | gray0625 |
| FILL_PATTERN_GRAY125 | gray125 |
| FILL_PATTERN_LIGHTDOWN | lightDown |
| FILL_PATTERN_LIGHTGRAY | lightGray |
| FILL_PATTERN_LIGHTGRID | lightGrid |
| FILL_PATTERN_LIGHTHORIZONTAL | lightHorizontal |
| FILL_PATTERN_LIGHTTRELLIS | lightTrellis |
| FILL_PATTERN_LIGHTUP | lightUp |
| FILL_PATTERN_LIGHTVERTICAL | lightVertical |
| FILL_PATTERN_MEDIUMGRAY | mediumGray |
An example of specifying settings for the fill:
array( 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 0, 'startcolor' => array( 'rgb' => '000000' ), 'endcolor' => array( 'argb' => 'FFFFFFFF' ), 'color' => array( 'rgb' => '000000' ) ); Or you can use the following methods:
$ PHPExcel_Style-> getFill () -> setFillType (PHPExcel_Style_Fill :: FILL_GRADIENT_LINEAR);
$ PHPExcel_Style-> getFill () -> setRotation (0);
$ PHPExcel_Style-> getFill () -> getStartColor () -> applyFromArray (array ('rgb' => 'C2FABD'));
$ PHPExcel_Style-> getFill () -> getEndColor () -> applyFromArray (array ('argb' => 'FFFFFFFF')).
Insert images
Quite rarely, but it is useful to insert an image into a report. This could be a logo, a scheme, etc. To work we need the following methods:
- setPath ($ pValue = '', $ pVerifyFile = true) takes one required and the second optional parameters: the first parameter is the path to the image file. The second parameter makes sense to specify if it is necessary to check for the existence of a file (it can be one of true or false values).
- setCoordinates ($ pValue = 'A1') takes one parameter as a string with the cell coordinate as input.
- setOffsetX ($ pValue = 0) takes one parameter with an X offset value from the left edge of the cell.
- setOffsetY () takes one parameter with an Y offset value from the top edge of the cell.
- setWorksheet (PHPExcel_Worksheet $ pValue = null, $ pOverrideOld = false) This method takes two parameters as input. The first is mandatory, and the second is not. The first parameter is an instance of the active sheet object. If the value of the second parameter is true, then if the sheet has already been assigned before, it will be overwritten and, accordingly, the image will be deleted.
Code showing the image insertion algorithm is shown below:
... $sheet->getColumnDimension('B')->setWidth(40); $imagePath = dirname ( __FILE__ ) . '/excel.png'; if (file_exists($imagePath)) { $logo = new PHPExcel_Worksheet_Drawing(); $logo->setPath($imagePath); $logo->setCoordinates("B2"); $logo->setOffsetX(0); $logo->setOffsetY(0); $sheet->getRowDimension(2)->setRowHeight(190); $logo->setWorksheet($sheet); } ... Here is the report with the image inserted:
Font
The value of the font parameter is an array that contains the following optional parameters:
- name - the name of the font;
- size - font size;
- bold - bold;
- italic - italicize;
- underline - underline style;
- strike - cross out;
- superScript - superscript;
- subScript - subscript;
- color - value in the form of an array with a color parameter in the RGB format.
Underline styles
| UNDERLINE_NONE | not |
| UNDERLINE_DOUBLE | double underscore |
| UNDERLINE_SINGLE | single underscore |
Example of specifying settings for the font:
array( 'name' => 'Arial', 'size' => 12, 'bold' => true, 'italic' => false, 'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE, 'strike' => false, 'superScript' => false, 'subScript' => false, 'color' => array( 'rgb' => '808080' ) ); Or use the following methods:
$ PHPExcel_Style-> getFont () -> setName ('Arial');
$ PHPExcel_Style-> getFont () -> setBold (true);
$ PHPExcel_Style-> getFont () -> setItalic (false);
$ PHPExcel_Style-> getFont () -> setSuperScript (false);
$ PHPExcel_Style-> getFont () -> setSubScript (false);
$ PHPExcel_Style-> getFont () -> setUnderline (PHPExcel_Style_Font :: UNDERLINE_DOUBLE);
$ PHPExcel_Style-> getFont () -> setStrikethrough (false);
$ PHPExcel_Style-> getFont () -> getColor () -> applyFromArray (array ('rgb' => '808080'));
$ PHPExcel_Style-> getFont () -> setSize (12).
Frame
The value of the borders parameter is an array that contains the following optional parameters:
- frame view (top | bootom | left | right | diagonal | diagonaldirection) - an array of parameters:
- style - frame style;
- color - value in the form of an array with a color parameter in the RGB format.
Line styles
| BORDER_NONE | not |
| BORDER_DASHDOT | dotted with dot |
| BORDER_DASHDOTDOT | dotted with two dots |
| BORDER_DASHED | dotted |
| BORDER_DOTTED | point |
| BORDER_DOUBLE | double |
| BORDER_HAIR | hairline |
| BORDER_MEDIUM | average |
| BORDER_MEDIUMDASHDOT | dotted with dot |
| BORDER_MEDIUMDASHDOTDOT | thick dotted line with two points |
| BORDER_MEDIUMDASHED | thick dotted |
| BORDER_SLANTDASHDOT | slant dotted with dot |
| BORDER_THICK | thickened |
| BORDER_THIN | thin |
Example of specifying settings for the frame:
array( 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'color' => array( ' rgb' => '808080' ) ), 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'color' => array( 'rgb' => '808080' ) ) ); You can also resort to using the following methods:
$ PHPExcel_Style-> getBorders () -> getLeft () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getRight () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getTop () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getBottom () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> getDiagonal () -> applyFromArray (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))));
$ PHPExcel_Style-> getBorders () -> setDiagonalDirection (array ('style' => PHPExcel_Style_Border :: BORDER_DASHDOT, 'color' => array ('rgb' => '808080'))).
Alignment
The value of the alignment parameter is an array that accepts four optional parameters as input:
- horizontal - horizontal alignment constant;
- vertical - vertical alignment constant;
- rotation - the angle of rotation of the text;
- wrap - allow text wrapping;
- shrinkToFit - whether to change the font size when the text leaves the cell area;
- indent - indent from the left edge.
Horizontal alignment
| HORIZONTAL_GENERAL | the main |
| HORIZONTAL_LEFT | on the left |
| HORIZONTAL_RIGHT | right hand |
| HORIZONTAL_CENTER | centered |
| HORIZONTAL_CENTER_CONTINUOUS | centered selection |
| HORIZONTAL_JUSTIFY | across the width |
Vertical Alignment
| VERTICAL_BOTTOM | bottom edge |
| VERTICAL_TOP | along the top edge |
| VERTICAL_CENTER | centered |
| VERTICAL_JUSTIFY | by height |
An example of alignment style settings:
array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => true, 'shrinkToFit' => false, 'indent' => 5 ) And you can use the following methods:
$ PHPExcel_Style-> getAlignment () -> setHorizontal (PHPExcel_Style_Alignment :: HORIZONTAL_CENTER);
$ PHPExcel_Style-> getAlignment () -> setVertical (PHPExcel_Style_Alignment :: VERTICAL_JUSTIFY);
$ PHPExcel_Style-> getAlignment () -> setTextRotation (10);
$ PHPExcel_Style-> getAlignment () -> setWrapText (true);
$ PHPExcel_Style-> getAlignment () -> setShrinkToFit (false);
$ PHPExcel_Style-> getAlignment () -> setIndent (5).
Data presentation format
The numberformat parameter is an array that includes only one parameter: code is the data format of the cell.
List of possible formats
| FORMAT_GENERAL | General |
| FORMAT_TEXT | @ |
| FORMAT_NUMBER | 0 |
| FORMAT_NUMBER_00 | 0.00 |
| FORMAT_NUMBER_COMMA_SEPARATED1 | #, ## 0.00 |
| FORMAT_NUMBER_COMMA_SEPARATED2 | #, ## 0.00_- |
| FORMAT_PERCENTAGE | 0% |
| FORMAT_PERCENTAGE_00 | 0.00% |
| FORMAT_DATE_YYYYMMDD2 | yyyy-mm-dd |
| FORMAT_DATE_YYYYMMDD | yy-mm-dd |
| FORMAT_DATE_DDMMYYYY | dd / mm / yy |
| FORMAT_DATE_DMYSLASH | d / m / y |
| FORMAT_DATE_DMYMINUS | dmy |
| FORMAT_DATE_DMMINUS | dm |
| FORMAT_DATE_MYMINUS | my |
| FORMAT_DATE_XLSX14 | mm-dd-yy |
| FORMAT_DATE_XLSX15 | d-mmm-yy |
| FORMAT_DATE_XLSX16 | d-mmm |
| FORMAT_DATE_XLSX17 | mmm-yy |
| FORMAT_DATE_XLSX22 | m / d / yy h: mm |
| FORMAT_DATE_DATETIME | d / m / yh: mm |
| FORMAT_DATE_TIME1 | h: mm AM / PM |
| FORMAT_DATE_TIME2 | h: mm: ss AM / PM |
| FORMAT_DATE_TIME3 | h: mm |
| FORMAT_DATE_TIME4 | h: mm: ss |
| FORMAT_DATE_TIME5 | mm: ss |
| FORMAT_DATE_TIME6 | h: mm: ss |
| FORMAT_DATE_TIME7 | i: sS |
| FORMAT_DATE_TIME8 | h: mm: ss |
| FORMAT_DATE_YYYYMMDDSLASH | yy / mm / dd; @ |
| FORMAT_CURRENCY_USD_SIMPLE | "$" #, ## 0.00 _-; @ |
| FORMAT_CURRENCY_USD | $ #, ## 0_- |
| FORMAT_CURRENCY_EUR_SIMPLE | [$ EUR] #, ## 0.00_- |
Example setup for cell data format:
array( 'code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE ); And you can use the method:
$ PHPExcel_Style-> getNumberFormat () -> setFormatCode (PHPExcel_Style_NumberFormat :: FORMAT_CURRENCY_EUR_SIMPLE);
Cell protection
The value of the protection parameter is an array, which contains two optional parameters:
- locked - protect the cell;
- hidden - hide formulas.
Parameter setting example for cell protection:
array( 'locked' => true, 'hidden' => false ); Or use the following methods:
$ PHPExcel_Style-> getProtection () -> setLocked (true);
$ PHPExcel_Style-> getProtection () -> setHidden (false);
Now we know what style settings there are and what parameters each style has. Now we apply a layout style to the cells of the table, but we will do this using two methods. The first method is to create an array of settings, which we pass as the parameter to the applyFromArray method, PHPExcel_Style class.
$style = array( 'font' => array( 'name' => 'Arial', ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array ( 'rgb' => 'C2FABD' ) ), 'alignment' => array ( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER ) ); Next we apply the style we created to the excel cells.
$sheet->getStyleByColumnAndRow($i - 2, $j)->applyFromArray($style); Now apply the same style, but using a different technique.
// $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // $sheet->getStyleByColumnAndRow($i - 2, $j)->getFont()->setName('Arial'); // $sheet->getStyleByColumnAndRow($i - 2, $j)->getFill()-> setFillType(PHPExcel_Style_Fill::FILL_SOLID); $sheet->getStyleByColumnAndRow($i - 2, $j)->getFill()-> getStartColor()->applyFromArray(array('rgb' => 'C2FABD')); Here's what we got:
If you want to apply the style many times, then the first method is better, in the other case, it is better to stay on the second. To obtain an object (an instance of the class PHPExcel_Style ) of the cell responsible for the style, you must use one of the following methods:
- getStyleByColumnAndRow (pColumn, pRow) - to access a cell by index;
- getStyle (pCellCoordinate) - to refer to the coordinate of the cell.
Reading data from Excel
Generating reports and applying styles to them is of course excellent. But the possibilities of the PHP Excel library do not end there. Well, let's look at what she is still capable of. And it is also capable of reading data from * .xls / * .xlsx files.
Using the PHPExcel library, you can read the following formats:
- Excel 2007;
- Excel 5.0 / Excel 95;
- Excel 97 and later versions;
- PHPExcel Serialized Spreadshet;
- Symbolic Link;
- CSV.
To work we need objects of two classes:
- PHPExcel_Worksheet_RowIterator - used for sorting rows;
- PHPExcel_Worksheet_CellIterator - used to iterate over cells.
For demonstration we will display data from the table with information about cars.
An example of reading the file is presented below:
require_once ('PHPExcel/IOFactory.php'); // $xls = PHPExcel_IOFactory::load('xls.xls'); // $xls->setActiveSheetIndex(0); // $sheet = $xls->getActiveSheet(); First option
... echo "<table>"; // $rowIterator = $sheet->getRowIterator(); foreach ($rowIterator as $row) { // $cellIterator = $row->getCellIterator(); echo "<tr>"; foreach ($cellIterator as $cell) { echo "<td>" . $cell->getCalculatedValue() . "</td>"; } echo "</tr>"; } echo "</table>"; Second option
... echo "<table>"; for ($i = 1; $i <= $sheet->getHighestRow(); $i++) { echo "<tr>"; $nColumn = PHPExcel_Cell::columnIndexFromString( $sheet->getHighestColumn()); for ($j = 0; $j < $nColumn; $j++) { $value = $sheet->getCellByColumnAndRow($j, $i)->getValue(); echo "<td>$value</td>"; } echo "</tr>"; } echo "</table>"; In the first variant, we read data from cells using iterators. And in the second, we use index addressing to access and retrieve data from the cells of the paper. You can get data on the number of rows and columns using the following methods of the PHPExcel_Worksheet class:
- getHighestColumn () - returns the character representation of the last occupied column in the active sheet. Note: not the column index, but its symbolic representation (A, F, etc.);
- getHighestRow () - returns the number of rows occupied in the active sheet.
And also, we cannot do without the help of the columnIndexFromString method, which is part of the PHPExcel_Cell class. This method allows you to determine the column index by its symbolic representation.
With the help of the demonstrated capabilities, you can generate and read any reports in the form of files, excel format. And also almost all possible methods for working with styles were demonstrated.
Source: https://habr.com/ru/post/245233/
All Articles