Personal use of the process to save some experience using Phpexcel, in order to later flip through:
Combined with the PHP yii framework, it's easy to use. And do not have to say on the Internet to modify Yii automatic loading files and other methods.
Specific Use method:
Download Phpoffice http://phpexcel.codeplex.com/releases/view/119187
After download the head directory structure is Classes, documentation, Examples, Changelog.txt, Install.txt, License.txt.
What to do after downloading is to let Yii load Phpexcel. Lele imitate Yii2-swiftmailer loading mode, modify Composer.json under the project root path, add "Phpoffice/phpexcel" in "Require": "Dev-develop", then use the command line to enter the root directory , perform composer update, (as if without manual download Phpexcel,composer will automatically ... After completion, you can use $phpexcel = new \phpexcel () in PHP, and come to an instance. Remember, new must add \, or will report the class can not find the error (Lele do not know why add \, who knows please say in the comments).
OK, after you can use it, write some code to use:
$filePath = ". /file/test.xlsx "; The path of the file to read
$PHPExcel = new \phpexcel (); Get an example, use it later.
$PHPReader = new \phpexcel_reader_excel2007 (); Reader is critical for reading Excel files
if (! $PHPReader->canread ($filePath)) {//Here is the reader to try to read the file, 07 not with 05,05 do not error. Note that return here is the way of the YII framework.
$PHPReader = new \phpexcel_reader_excel5 ();
if (! $PHPReader->canread ($filePath)) {
$errorMessage = "Can not read file.";
return $this->render (' Error ', [' errormessage ' = ' $errorMessage]);
}
}
$PHPExcel = $PHPReader->load ($filePath); When reader is read out, it is loaded to an Excel instance
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
The above can be read Excel, the following traversal of the output of this two-dimensional table. The method name of Phpexcel is more semantic.
$allSheet = $PHPExcel->getsheetcount (); Number of sheet
$currentSheet = $PHPExcel->getsheet (0); Get the first sheet (workbook?) ）
$allColumn = $currentSheet->gethighestcolumn (); The highest column, such as AU. Column starts from a
$allRow = $currentSheet->gethighestrow (); The largest row, such as 12980. Line starting from 0
$result = new Readfileresult (); Result was written by myself. An entity class that stores results
for ($currentRow = 1; $currentRow <= $allRow; $currentRow + +) {
Echo $currentRow;
$lineVal = [];
for ($currentColumn = "A"; $currentColumn <= $allColumn; $currentColumn + +) {
$val = $currentSheet->getcellbycolumnandrow (ord ($currentColumn)-All, $currentRow)->getvalue (); Ord turns the letters into ASCII, A->65, b->66 .... The pit here is au->65, and the back of you is not counted, so indexing is a flawed way to traverse.
Array_push ($lineVal, $val);
}
Array_push ($result->content, $lineVal);
}
Look at the development document discovery, there are two very useful traversal methods.
First, use ToArray to convert this sheet to a two-dimensional array.
$currentSheet->getstyle (' A2:a6 ')->getnumberformat ()->setformatcode (' Yyyy-mm-dd '); Convert to A2 to A6 time format
$result->content = $currentSheet->toarray (", true, true); Convert the current sheet to a two-dimensional array
Second, use the Phpexcel to bring the column iterator. That would be a lot more secure.
foreach ($currentSheet->getrowiterator () as $row) {//row iterators
$cellIterator = $row->getcelliterator (); Get the cell iterator in the row
$cellIterator->setiterateonlyexistingcells (FALSE); Set the cell iterator to traverse all cells, even if the cell has no value
$lineVal = [];
foreach ($cellIterator as $cell) {
if ($cell->getdatatype () = = \phpexcel_cell_datatype::type_numeric) {//Here is a comparison of the data type in cell is number
$cellStyleFormat = $cell->getstyle ($cell->getcoordinate ())->getnumberformat (); The next two sentences are the format of this number.
$formatCode = $cellStyleFormat->getformatcode (); If it is a normal number, Formatcode will be general, if it is a time format such as 6/12/91 12:00, Formatcode will be/d/yy h:mm (anyway is the time format)
echo $cell->getcoordinate (). " " . $formatCode; echo "
";
if (Preg_match ("/m\/d\/yy h:mm/i", $formatCode)) {
$value = Gmdate ("y-m-d h:i:s", \phpexcel_shared_date::exceltophp ($cell->getvalue ())); This is the time to convert Excel to PHP by format
} else {
$value = $cell->getvalue ();
}
} else {
$value = $cell->getvalue ();
}
if ($cell->getcolumn () = = ' I ') {//Get column coordinates
$value = "0". $value;
}
Array_push ($lineVal, $value. " " . $cell->getcolumn ());
}
Array_push ($result->content, $lineVal);
}
Lele from Yanhui_wei classmate an article saw Ta on Phpexcel class and method summary, write good, special excerpt down:
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
(4) 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
(5) 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
(6) 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
(7) 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
(8) 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
(9) 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
(Ten) 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 ');
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
The above describes the YII2 use Phpexcel read Excel, including the aspects of the content, I hope that the PHP tutorial interested in a friend helpful.
