"PHP" to phpexcel some simple understanding

Here are a few concepts about Excel files that you need to explain, and these concepts are useful for our follow-up programming:
1. Workbooks: Files used in an Excel environment to store data and manipulate data, also known as Excel files or Excel documents,
In a workbook, you can have multiple worksheets of different types, no matter how many worksheets you have, and you save them in a workbook file when you save them, instead of saving them by the number of worksheets.
By default, each workbook file contains three sheet files: Sheet1,sheet2,sheet3

2. Worksheet: is a two-dimensional table composed of multirow and multiple columns, the number of the line number is from top to bottom starting from 1, the column number from left to right with the letter number, starting from a

3. Cell: Row coordinates and column coordinates do the specified position, called cell, column name and row number are made up of names that are cell names

Hello everyone, use this time to lead you to learn Excel report technology in enterprise project use.

In the management of the site is often used to build and read the report, where Excel is one of the most common report formats, today we will learn how to operate Excel report in PHP.

Before we go into formal learning, let's demonstrate how Excel reporting is used in real-world projects.
Application of Case 1:excel report technology in electronic tax system
① the tax status of each taxpayer: urban maintenance and construction tax, enterprise income tax, etc.

Generate Excel report files in PHP, mainly by headers () to the browser to send header information to complete

There are several ways that PHP generates Excel report files:

OK, now we're going to build a simple Excel file with a PHP program

Case:

<?php
First, at the beginning of the PHP file, define the output file type as Excel file by header ()
Defines the output file type, which represents the output of an Excel file
Header ("Content-type:application/vnd.ms-excel");
Defines the file name of the output, indicating that the output file name is Test.xls, and if the file name is Chinese, transcode with the Iconv function
Header ("Content-disposition:filename=test.xls");

Why do you want to add \ t and \ n? Mainly by means of the program to simulate the table key and enter
echo "Test1\t"; \t:table key, by means of the program to simulate the table key and enter the press
echo "test2\t\n"; \t\n:table Key and Wrap

echo "Test1\t";
echo "test2\t\n";

echo "Test1\t";
echo "test2\t\n";

echo "Test1\t";
echo "test2\t\n";

echo "Test1\t";
echo "test2\t\n";

echo "Test1\t";
echo "test2\t\n";

In fact, in most cases, you can take the data out of the database, and then follow the end of each column to add \ t, after the end of each row of data add \ n method echo out

Add a title to each column in an Excel table
echo "Id\t";
echo "customer abbreviation \ t";
echo "Customer's full name \ T";
echo "contact \ T";
echo "Contact phone \ t";
echo "fax \ t";
echo "Address \t\n";

mysql_connect ("localhost", "root", "");
mysql_select_db ("jxc2008");
mysql_query ("Set names ' UTF8 '");
$sql = "SELECT * from Kehu ORDER by id DESC";
$rec =mysql_query ($sql);//Returns a result set
$row _num=mysql_num_rows ($REC);//number of rows in the result set
$col _num=mysql_num_fields ($REC);//number of columns in the result set

while ($row =mysql_fetch_array ($rec)) {//row-wise fetch of records from the result set
for ($col =0; $col < $col _num; $col + +) {
if ($col = = ($col _num-1)) {The number of columns in the//excel file starts with 0, and the total number of columns-1 corresponds to the number of the largest column in the Excel file
echo $row [$col]. " \t\n ";//If it is the last column, add \ n to simulate the ENTER key
}else{
echo $row [$col]. " \ t ";//each row of data is output column-wise to an Excel file cell, not the last column, plus \ t to simulate the TAB key
}
}
}
?>
A simple Excel file is generated by running the following program. In this way to generate Excel files, there are his advantages, there are his shortcomings:
Pros: The above format is relatively simple, because the comparison is single
Cons: It's inconvenient to control the resulting Excel table style, reading Excel files and inconvenient

Case: Generate an Excel file by <table> table: omit

Cons: more complex points of the report will be troublesome, to the template in one cell to put the StyleID value

Problem: Although using PHP header () can directly generate Excel, but for the control of the generated Excel style (such as: center alignment, background color, etc.) is very inconvenient, so here is recommended a most popular Excel class library-phpexcel

Phpexcel class Library is a very powerful PHP class library, basically can solve all the Excel problem, the enterprise in the Excel report processing mostly use this kind of library to complete

This kind of library can go to its official website http://www.codeplex.com/PHPExcel to download, the latest version is 1.7.7, I have downloaded it here,

Unzip it into my web app and find out there are three folders in it:
Classess: This folder is the Phpexcel core class library file, our processing of Excel report is done through the class file in this folder, note: All class names are the beginning of the phpexcel_*
Features: All files are a class
All class names start with phpexcel_*.
Documentation: This folder contains the English documentation of the Phpexcel class library.
Tests: Some small test cases, these small cases are very helpful for us to get started.

First, we open a test file to get a quick experience. After the test file is executed. Will prompt us to download

Here are two concepts about Excel documentation that you need to explain:
Workbooks: Excel documents that contain multiple worksheets, that is, workbooks ==excel documents, the name of the Excel document that we create is the name of the workbook
Worksheets: two-dimensional tables with rows and columns, and multiple worksheets form a single workbook


Well, knowing all this, let's read this PHP test file.

Phpexcel implements the Autoloader automatic loading mechanism, which means that we do not need to introduce the Phpexcel folder of each class library file, only need to introduce the Phpexcel initialization class library file in the file to be able to automatically load the class, will automatically introduce other required class library files

To simplify the concept of the Phpexcel class, the Phpexcel class represents your workbook

For common Excel Report operations, we need to master the following classes of libraries:
1. Workbook Object class: Phpexcel
2. Sheet object class: Phpexcel_worksheet
3. Style object class: Phpexcel_style
4. Alignment Object class: Phpexcel_style_alignment
5. Font object class: Phpexcel_style_font
6. Color Object class: Phpexcel_style_color
7. Cell class: Phpexcel_cell
8. Class for reading and writing files: phpexcel_iofactory
9. Read the file class: Phpexcel_reader_excel5
10. Write the file class: Phpexcel_writer_excel5

Everyone can see, I wrote a certain level of relationship, why write it? Because in many cases low-level classes depend on their classes at the previous level

For common Excel Report operations, we need to master the following classes of libraries:

(1) Phpexcel: Workbook object

Excel document processing objects are primarily used to manage our Excel documents, how to manage them (managed by properties and methods)? As you know, the class is mainly composed of properties and methods, through the means of PHP program to manage Excel documents, in fact, through the object's properties and methods to manage, let's look at the Phpexcel class has those properties and methods, These properties and methods are primarily used to manage those aspects of an Excel document

GetProperties (): Gets the Property object for the currently active worksheet, returns the Property object
Getactivesheet (): Gets the current active sheet, returning the sheet object
Getactivesheetindex (): Gets the index value of the currently active worksheet and returns an int
Setactivesheetindex (): Sets the index of the current active state sheet, returning the sheet object
Getsheetbyname (): Gets the current sheet object from the sheet name and returns the sheet object
Getdefaultstyle (): Get the Excel document default style (all worksheet styles), return style object
Createsheet (): Create a new worksheet after the currently active worksheet
Getsheetcount (): Gets the number of worksheets in the Excel document, returns an int
Getsheetnames (): Gets an array of all the sheet names in the Excel document

(2) Phpexcel_worksheet: Sheet Object

Sheet objects, which are used primarily to manage our worksheets, how to manage them? is also managed through properties and methods, but in most cases the sheet object can be obtained by using an Excel Document object


ToArray (): Convert data in worksheets to an array
FromArray (): Gets the data from the array to populate the worksheet, returning the sheet object
Getcell (): Get Cell object
Getcellbycolumnandrow (): Returns a Cell object using the column index and row index to get the specified cell
Getdefaultstyle (): Get worksheet default style, return style object
Gethighestcolumn (): Gets the largest column of the worksheet, returning the name of the column
Getcolumndimension (): Get the current column
GetStyle (): Gets the style of the specified cell, returning the Style object
GetParent (): Gets the parent class object, returning the Excel document object
GetTitle (): Gets the title or name of the worksheet, returns the string type
Setcellvalue (): Sets the value of the cell, returning the sheet object or Cell object, depending entirely on the value of the parameter
Setcellvaluebycolumnandrow (): Sets the cell Value by column index and row index, return type ibid.
Setcellvalueexplicit (): Sets the value of the cell and displays the specified data type, returning the sheet object
Setcellvalueexplicitbycolumnandrow (): Setting cell values by column and row index
Settitle (): Set sheet title

(3) Phpexcel_cell: Cell Object

(3) Phpexcel_style: Style object, mainly used to set the style of the cell: alignment, font, border, fill, and so on, as we have learned before the CSS style, where you want to set the alignment, font size, border color, etc. are through the style object to do

Getactivecell (): Gets the name of the currently active cell and returns a string, for example, A1
Getactivesheet (): Gets the currently active sheet, returning the sheet object
Getalignment (): Gets the Alignment object, returns the Alignment object
GetBorders (): Get Border object, return border object
Getfill (): Get Fill Object
GetFont (): Get Font Object
SetFont (): Set font, return style object

(3) Phpexcel_style_alignment: Alignment Object


Gethorizontal (): Get the horizontal center mode
Getvertical (): Get Vertical Center Mode
Sethorizontal (): Sets the horizontal centering method, returning the Alignment object
Setvertical (): Sets the vertical centering method, returning the Alignment object

Centering mode:
Horizontal_center
Horizontal_center_continuous
Horizontal_general
Horizontal_justify
Horizontal_left
Horizontal_right
Vertical_bottom
Vertical_center
Vertical_justify
Vertical_top

(3) Phpexcel_style_font: Font Object

Setbold (): Set font Bold
SetColor (): Set Font Color
Setitalic (): Set font skew
SetName (): Set font name
SetSize (): Set Font size
Setunderline (): Set font underline

(4) Phpexcel_writer_excel5: Write Operation object, mainly used to output XLS file

Save (workbook file name): Saves the data in a Workbook object to a workbook file

(4) Phpexcel_writer_excel2007: Write Operation object, mainly used to output xlsx file

Save (workbook file name): Saves the data in a Workbook object to a workbook file

(5) Phpexcel_reader_excel5: Read Operation object, mainly used for input XLS file

CanRead (): Whether the current reader object can read the workbook file
Load (): Loads the Workbook object from a workbook file, that is, the data in the workbook file is loaded into the workbook object to manage

(6) Phpexcel_iofactory: Read and write Operation object

Createreader (): Creates different read objects depending on the parameters: the primary role is to read the data in the workbook file

Createwriter (): Different write objects are returned depending on the parameter: the primary role is to write data from the Phpexcel Workbook object to a workbook file

Load (): Loads the Phpexcel Workbook object from the workbook file, which is: Load data from the workbook file into the Phpexcel workbook object to manage


Phpexcel object: is a Workbook object

Include_once "phpexcel/writer/excel5.php";//used primarily for other low versions of files with file name suffix xls, if we want to generate an Excel file with a suffix called xls format, we recommend introducing such libraries

Include_once "phpexcel/writer/excel2007.php";//is used primarily for excel2007 format, file name suffix for xlsx Excel file, if we want to generate an Excel file with the suffix named xlsx format, It is recommended to introduce this kind of library

$objWriter = new Phpexcel_writer_excel5 ($objExcel);//Create a file format to write to the object instance, which is used primarily to write content to a file in the specified format, such as writing to an Excel file with the suffix named xls format. , used in other panel formats

$objWriter = new phpexcel_writer_excel2007 ($objExcel);//Create a file format to write to the object instance, which is used primarily to write content to a file in the specified format, such as Write content to an Excel file with the suffix named xls format for excel2007 format
$objWriter->setoffice2003compatibility (TRUE);//Compatible office2003


Set document basic Properties
$objProps = $objExcel->getproperties (); Get Phpexcel_document Document Object
$objProps->setcreator ("Zeal Li"); Set author
$objProps->setlastmodifiedby ("Zeal Li"); Set Last Modified Time
$objProps->settitle ("Office XLS Test Document"); Set Title
$objProps->setsubject ("Office XLS Test Document, Demo");//Set theme
$objProps->setdescription ("Test document, generated by Phpexcel."); /description
$objProps->setkeywords ("Office Excel Phpexcel"); Key words
$objProps->setcategory ("Test"); Classification


$objExcel->setactivesheetindex (0);//sets the first sheet that the user sees when opening an Excel file, and defaults to the last action if not set sheet

$objActSheet->settitle (' Test sheet ');//Set the currently active workbook name


Sets the cell contents according to the cell name, and automatically determines the cell's content type by phpexcel based on the incoming content
$objActSheet->setcellvalue (' A1 ', ' string content '); String content
$objActSheet->setcellvalue (' A2 ', 26); Numerical
$objActSheet->setcellvalue (' A3 ', true); Boolean value
$objActSheet->setcellvalue (' A4 ', ' =sum (A2:A2) '); Formula

/explicitly specifies that the cell's content type is a string type
$objActSheet->setcellvalueexplicit (' A5 ', ' 847475847857487584 ', phpexcel_cell_datatype::type_string);

Merge cells
$objActSheet->mergecells (' b1:c22 ');


Set the width of a column
$objActSheet->getcolumndimension (' B ')->setautosize (true);
$objActSheet->getcolumndimension (' A ')->setwidth (30);

Set the height of a row
$objPHPExcel->getactivesheet ()->getrowdimension (' 2 ')->setrowheight (11.5);


Format: used primarily to manipulate cells, such as setting fonts, setting alignment, setting borders, etc.
$objStyleA 5 = $objActSheet->getstyle (' A5 ');//Gets the style of the A5 cell

Set the font for a cell
$objFontA 5 = $objStyleA 5->getfont (); Get fonts
$objFontA 5->setname (' Arial ');//Set font name
$objFontA 5->setsize (10); Set Font size
$objFontA 5->setbold (TRUE);//Set Font bold
$objFontA 5->getcolor ()->setargb (' FF999999 ');//Set Font color

Set the alignment of a cell
$objAlignA 5 = $objStyleA 5->getalignment ();//Get Alignment
$objAlignA 5->sethorizontal (phpexcel_style_alignment::horizontal_right);//Horizontal Right
$objAlignA 5->setvertical (Phpexcel_style_alignment::vertical_center);//Vertical Center

Set the border of a cell
$objBorderA 5 = $objStyleA 5->getborders ();//Get border
$objBorderA 5->gettop ()->setborderstyle (Phpexcel_style_border::border_thin);//Border style
$objBorderA 5->gettop ()->getcolor ()->setargb (' FFFF0000 ');//color of top border
$objBorderA 5->getbottom ()->setborderstyle (Phpexcel_style_border::border_thin);
$objBorderA 5->getleft ()->setborderstyle (Phpexcel_style_border::border_thin);//Left style
$objBorderA 5->getright ()->setborderstyle (Phpexcel_style_border::border_thin);//Right style

Set the fill color of a cell
$objFillA 5 = $objStyleA 5->getfill ();//Fill
$objFillA 5->setfilltype (phpexcel_style_fill::fill_solid);//Fill type
$objFillA 5->getstartcolor ()->setargb (' ffeeeeee ');

Calculate the value of the cell
$objPHPExcel->getactivesheet ()->setcellvalue (' B7 ', ' =sum (B5:C5) ');
$objPHPExcel->getactivesheet ()->getcell (' B7 ')->getcalculatedvalue ();
If you want to get a value for a cell, you first need to get a Cell object through the Getcell method of the worksheet, and then get the cell value from the Cell object's GetValue method, if the cell value is calculated, You need to use the Getcalculatedvalue method to get the value of the cell, set the value of the cell, and we only need to set it through the Setcellvalue method of the worksheet.


$dateTimeNow =time ();
$objPHPExcel->getactivesheet ()->setcellvalue (' C10 ', phpexcel_shared_date::P hptoexcel ($dateTimeNow));// 41105.75

$objPHPExcel->getactivesheet ()->getstyle (' C10 ')->getnumberformat ()->setformatcode (phpexcel_style_ NUMBERFORMAT::FORMAT_DATE_TIME4);//18:00:54, just a change of display mode, does not change the original value of the type

Echo GetType ($objPHPExcel->getactivesheet ()->getcell (' C10 ')->getvalue ());//double
echo $objPHPExcel->getactivesheet ()->getcell (' C10 ')->getvalue ();//41105.75

' 2010-10-21 ' must be placed in quotation marks, otherwise the displayed value is 1979 text (recommended)
$objPHPExcel->setactivesheetindex (0)->setcellvalueexplicit ("D1", ' 2010-10-21 ', Phpexcel_cell_datatype:: type_string); Feature: String types are left-hand-displayed

Add a new worksheet
$objExcel->createsheet ();//Create a new worksheet
$objExcel->getsheet (1)->settitle (' Test 2 ');//Set the title of the current sheet

Protect cells
$objExcel->getsheet (1)->getprotection ()->setsheet (true);
$objExcel->getsheet (1)->protectcells (' A1:c22 ', ' phpexcel ');

Output content to an Excel file, and save the file on the server
$objWriter->save ("Test.xls");

Force output content to browser download
Header ("Content-type:application/force-download");
Header ("Content-type:application/octet-stream");
Header ("Content-type:application/download");
Header (' Content-disposition:inline;filename= '. $outputFileName. ' ');
Header ("Content-transfer-encoding:binary");
Header ("Expires:mon, Jul 1997 05:00:00 GMT");
Header ("last-modified:".) Gmdate ("D, D M Y h:i:s"). "GMT");
Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0");
Header ("Pragma:no-cache");
$objWriter->save (' php://output ');//parameter-Indicates the direct output to the browser for the client to download

Excel reads an object
$PHPReader = new Phpexcel_reader_excel5 ();//Create a read object for an Excel file
$PHPExcel = $PHPReader->load ($filePath);//Read an Excel table that returns an Excel file object
$currentSheet = $PHPExcel->getsheet (0);//Read the first worksheet in an Excel file
$allColumn = $currentSheet->gethighestcolumn ();//Gets the largest column number of the current worksheet, such as E
$allRow = $currentSheet->gethighestrow ();//Gets the total number of rows in the current worksheet

Set the default style for a workbook
$objPHPExcel->getdefaultstyle ()->getfont ()->setname (' Arial ');
$objPHPExcel->getdefaultstyle ()->getfont ()->setsize (8);

Merge cells
$objPHPExcel->getactivesheet ()->mergecells (' A18:e22 ');

Case 1: Iterating through all the cells using iterators, reading the values of all the cells in the worksheet, displaying them in a table table

<?php
$objReader = Phpexcel_iofactory::createreader (' Excel2007 ');
$objReader->setreaddataonly (TRUE);
$objPHPExcel = $objReader->load ("test.xlsx");
$objWorksheet = $objPHPExcel->getactivesheet ();//Gets the current active sheet
Echo ' <table> '. "\ n";
foreach ($objWorksheet->getrowiterator () as $row) {
Echo ' <tr> '. "\ n";
$cellIterator = $row->getcelliterator ();
$cellIterator->setiterateonlyexistingcells (FALSE);
foreach ($cellIterator as $cell) {
Echo ' <td> '. $cell->getvalue (). ' </td> '. "\ n";
}
Echo ' </tr> '. "\ n";
}
Echo ' </table> '. "\ n";
?>

Case 2: Looping all cells using an index

<?php
$objReader = Phpexcel_iofactory::createreader (' Excel2007 ');
$objReader->setreaddataonly (TRUE);
$objPHPExcel = $objReader->load ("test.xlsx");
$objWorksheet = $objPHPExcel->getactivesheet ();
$highestRow = $objWorksheet->gethighestrow (); e.g. 10
$highestColumn = $objWorksheet->gethighestcolumn (); e.g ' F '
$highestColumnIndex = phpexcel_cell::columnindexfromstring ($highestColumn); 5
Echo ' <table> '. \ n ";
for ($row = 1; $row <= $highestRow; + + $row) {
Echo ' <tr> '. "\ n";
for ($col = 0; $col <= $highestColumnIndex; + + $col) {
Echo ' <td> '. $objWorksheet->getcellbycolumnandrow ($col, $row)->getvalue (). ' </td> '. ' \ n ";
}
Echo ' </tr> '. \ n ";
}
Echo ' </table> '. \ n ";
?>

"PHP" to phpexcel some simple understanding