: This article describes how to use PHPExcel to read excel from Yii2. For more information about PHP tutorials, see. My personal experience in using PHPExcel is saved for later reading:
It can be easily used in combination with the Yii Framework of PHP. In addition, you do not need to modify Yii's automatic file loading methods.
Usage:
Download phpoffice http://phpexcel.codeplex.com/releases/view/119187
After the download, the first-level directory structure is Classes, Documentation, Examples, changelog.txt, install.txt, and license.txt.
After the download, you need to load PHPExcel for Yii. lele imitates the yii2-swiftmailer loading method and modifies the composer under the Project root path. json, add "phpoffice/phpexcel": "dev-develop" to "require", run the command line to enter the root directory, and execute composer update. (it seems that you do not need to manually download phpexcel, composer will automatically get better ...) You can use $ PHPExcel = new \ PHPExcel (); in php to get an instance. Remember, you must add \ when you are new, or an error cannot be found in the class will be reported (lele does not know why to add \. anyone who knows, please comment on it ).
OK. after it can be used, write the code used:
$ FilePath = "../file/test.xlsx"; // path of the file to be read
$ PHPExcel = new \ PHPExcel (); // Get the instance and use it later
$ PHPReader = new \ PHPExcel_Reader_Excel2007 (); // Reader is critical for reading excel files
If (! $ PHPReader-> canRead ($ filePath) {// Here we use Reader to try to read the file. if 07 fails to use, an error is returned. Note that the return here is the Yii Framework method.
$ PHPReader = new \ PHPExcel_Reader_Excel5 ();
If (! $ PHPReader-> canRead ($ filePath )){
$ ErrorMessage = "Can not read file .";
Return $ this-> render ('error', ['errormessage' => $ errorMessage]);
}
}
$ PHPExcel = $ PHPReader-> load ($ filePath); // after the Reader reads the file, it is loaded to the Excel instance.
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* -*-*-*-*-*-*-*-*-
Now you can read the excel file, and traverse and output the two-dimensional table below. The PHPExcel method name is more semantic.
$ AllSheet = $ PHPExcel-> getSheetCount (); // Number of sheet
$ CurrentSheet = $ PHPExcel-> getSheet (0); // get the first sheet (workbook ?)
$ AllColumn = $ currentSheet-> getHighestColumn (); // The highest column. for example, the AU. column starts from.
$ AllRow = $ currentSheet-> getHighestRow (); // The largest row, for example, 12980. the row starts from 0.
$ Result = new ReadFileResult (); // result is an object class for storing results written by myself.
For ($ currentRow = 1; $ currentRow <= $ allRow; $ currentRow ++ ){
Echo $ currentRow;
$ LineVal = [];
For ($ currentColumn = "A"; $ currentColumn <= $ allColumn; $ currentColumn ++ ){
$ Val = $ currentSheet-> getCellByColumnAndRow (ord ($ currentColumn)-65, $ currentRow)-> getValue (); // ord converts letters into ascii codes, A-> 65, b-> 66 .... the pitfall here lies in AU-> 65, and the subsequent U is not computed, so the index traversal is flawed.
Array_push ($ lineVal, $ val );
}
Array_push ($ result-> content, $ lineVal );
}
Looking at the development documentation, we found that there are two very useful traversal methods.
1. use toArray to convert the sheet into a two-dimensional array.
$ CurrentSheet-> getStyle ('A2: A6 ')-> getNumberFormat ()-> setFormatCode ('yyyy-mm-DD'); // Convert the format to the time format from A2 to A6
$ Result-> content = $ currentSheet-> toArray ('', true, true); // convert the current sheet to a two-dimensional array
2. use the row and column iterator that comes with PHPExcel. This will be much more secure.
Foreach ($ currentSheet-> getRowIterator () as $ row) {// row iterator
$ 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) {// check whether the data type in cell is number.
$ CellStyleFormat = $ cell-> getStyle ($ cell-> getCoordinate ()-> getNumberFormat (); // the following two sentences obtain the number format
$ FormatCode = $ cellStyleFormat-> getFormatCode (); // if it is a common number, formatCode will be General. if it is a time format such as 6/12/91, formatCode will be/d/yy h: mm (it is time format anyway)
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 ())); // Here is the time for converting the Excel time to the PHP time format
} Else {
$ Value = $ cell-> getValue ();
}
} Else {
$ Value = $ cell-> getValue ();
}
If ($ cell-> getColumn () = 'I') {// get the column coordinate
$ Value = "0". $ value;
}
Array_push ($ lineVal, $ value. "". $ cell-> getColumn ());
}
Array_push ($ result-> content, $ lineVal );
}
Lele saw TA's summary of the PHPExcel class and method from an article by yanhui_wei, which was well written and excerpted:
For common excel report operations, we need to master the following class libraries:
(1) PHPExcel: workbook object
Excel document processing objects are mainly used to manage our excel documents. how can we manage them (through attributes and methods )? As you know, classes are mainly composed of attributes and methods. using php programs to manage excel documents is actually managed by attributes and methods of the object, next, let's take a look at the attributes and methods in the PHPExcel class. these attributes and methods are mainly used to manage those aspects of the excel document.
GetProperties (): get the property object of the current active worksheet, and return the property object
GetActiveSheet (): Get the worksheet of the current activity status and return the worksheet object.
GetActiveSheetIndex (): Get the index value of the worksheet in the current active state. int is returned.
SetActiveSheetIndex (): sets the index of the current active worksheet and returns the worksheet object.
GetSheetByName (): obtains the current worksheet object by using the worksheet name, and returns the worksheet object.
GetDefaultStyle (): get the default style of the excel document (the style of all worksheets), and return the style object.
CreateSheet (): create a new worksheet after the current active worksheet
GetSheetCount (): get the number of worksheets in the excel document. int is returned.
GetSheetNames (): Get an array composed of all worksheet names in the excel document
(2) PHPExcel_Worksheet: worksheet object
Worksheet objects are mainly used to manage our worksheets. how can we manage them? It is also managed by attributes and methods, but most worksheet objects can be obtained through excel document objects.
ToArray (): converts the data in the worksheet to an array.
FromArray (): obtains data from the array and fills in the worksheet. the worksheet object is returned.
GetCell (): get the cell object
GetCellByColumnAndRow (): obtains the specified cell through the column index and row index, and returns the cell object.
GetDefaultStyle (): get the default style of the worksheet and return the style object.
GetHighestColumn (): obtains the maximum column of the worksheet and returns the column name.
GetColumnDimension (): get the current column
GetStyle (): Get the style of the specified cell and return the style object.
GetParent (): Get the parent class object and return the excel document object.
GetTitle (): get the title or name of the worksheet. The return value belongs to the string type.
SetCellValue (): sets the value of a cell and returns the worksheet object or cell object, depending entirely on the value of the parameter.
SetCellValueByColumnAndRow (): sets the cell value through the column index and row index. the return type is the same as above.
SetCellValueExplicit (): sets the cell value and displays the specified data type. the worksheet object is returned.
SetCellValueExplicitByColumnAndRow (): sets the cell value through column and row indexes.
SetTitle (): Set the worksheet title
(3) PHPExcel_Cell: cell object
(4) PHPExcel_Style: style object. it is mainly used to set the cell style: alignment, font, border, and fill. it is similar to the css style we have learned before, here, if you want to set the alignment mode, font size, border color, and so on, it is done through the style object.
GetActiveCell (): Get the name of the current active cell, and return string; for example, A1
GetActiveSheet (): obtains the worksheet of the current activity and returns the worksheet object.
GetAlignment (): Gets the alignment object and returns the alignment object.
GetBorders (): get the border object and return the border object.
GetFill (): Get the filling object
GetFont (): Get the font object
SetFont (): set the font and return the style object.
(5) PHPExcel_Style_Alignment: alignment object
GetHorizontal (): obtains the horizontal center mode.
GetVertical (): obtains the vertical center mode.
SetHorizontal (): sets the horizontal center mode and returns the alignment type object.
SetVertical (): sets the vertical center mode and returns the alignment type object.
Center 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 the font to bold
SetColor (): set the font color
SetItalic (): Set font skew
SetName (): set the font name
SetSize (): set the font size
SetUnderline (): set the font underline
(7) PHPExcel_Writer_Excel5: write operation object, mainly used to output xls files
Save (Workbook file name): save the data in the workbook object to a workbook file.
(8) PHPExcel_Writer_Excel2007: write operation object, mainly used to output xlsx files
Save (Workbook file name): save the data in the workbook object to a workbook file.
(9) PHPExcel_Reader_Excel5: read operation object, mainly used to input xls files
CanRead (): whether the current reader object can read a workbook file
Load (): load the workbook object from a workbook file, that is, load the data in the workbook file to the workbook object for management.
(10) PHPExcel_IOFactory: read/write operation object
CreateReader (): creates different read objects based on different parameters. The main function is to read data in a workbook file.
CreateWriter (): return different write objects based on different parameters. The main function is to write data in the PHPExcel workbook object to a workbook file.
Load (): load the PHPExcel workbook object from the workbook file, that is, load the data in the workbook file to the PHPExcel workbook object for management.
PHPExcel object: A Workbook object
Include_once "PHPExcel/Writer/Excel5.php"; // It is mainly used for files with the file name suffix xls in other lower versions. if you want to generate an excel file with the extension name xls, we recommend that you introduce such libraries.
Include_once "PHPExcel/Writer/Excel2007.php"; // It is mainly used for excel files with the file name suffix xlsx in the excel2007 format. if you want to generate an excel file with the extension name xlsx, we recommend that you introduce this library
$ ObjWriter = new PHPExcel_Writer_Excel5 ($ objExcel); // create a file format to write the object instance. this object is mainly used to write the content to a file in the specified format, for example, the content is written to an excel file with the suffix "xls" for other plate formats.
$ ObjWriter = new PHPExcel_Writer_Excel2007 ($ objExcel); // creates an object instance for writing data in the file format. this object is mainly used to write content to a file in the specified format, for example, write content to excel files with the suffix "xls" for excel2007 format
$ ObjWriter-> setOffice2003Compatibility (true); // Compatible with office2003
// Set the basic attributes of the document
$ ObjProps = $ objExcel-> getProperties (); // Obtain the PHPExcel_document document object.
$ ObjProps-> setCreator ("Zeal Li"); // Set the Author
$ ObjProps-> setLastModifiedBy ("Zeal Li"); // you can specify the last modification time.
$ ObjProps-> setTitle ("Office XLS Test Document"); // set the title
$ ObjProps-> setSubject ("Office XLS Test Document, Demo"); // you can specify a topic.
$ ObjProps-> setDescription ("Test document, generated by PHPExcel."); // description
$ ObjProps-> setKeywords ("office excel PHPExcel"); // keyword
$ ObjProps-> setCategory ("Test"); // category
$ ObjExcel-> setActiveSheetIndex (0); // sets the first sheet you see when opening an excel file. if this is not set, the last sheet is used by default.
$ ObjActSheet-> setTitle ('test Sheet '); // you can specify the name of the workbook for the current activity.
// Set the cell content based on the cell name. PHPExcel automatically determines the cell content type based on the input content.
$ ObjActSheet-> setCellValue ('A1', 'string content'); // String content
$ ObjActSheet-> setCellValue ('A2 ', 26); // value
$ ObjActSheet-> setCellValue ('A3 ', true); // boolean value
$ ObjActSheet-> setCellValue ('A4 ',' = SUM (A2: A2) '); // formula
/Explicitly specify the content type of a cell as the string type
$ ObjActSheet-> setCellValueExplicit ('a5 ', '000000', PHPExcel_Cell_DataType: TYPE_STRING );
// Merge cells
$ ObjActSheet-> mergeCells ('b1: c22 ');
// Set the column width
$ ObjActSheet-> getColumnDimension ('B')-> setAutoSize (true );
$ ObjActSheet-> getColumnDimension ('A')-> setWidth (30 );
// Set the row height
$ ObjPHPExcel-> getActiveSheet ()-> getRowDimension ('2')-> setRowHeight (11.5 );
// Format: mainly used to operate cells, such as setting fonts, setting alignment, and setting borders.
$ ObjStyleA5 = $ objActSheet-> getStyle ('a5 '); // Get the style of the A5 cell
// Set the cell font
$ ObjFontA5 = $ objStyleA5-> getFont (); // Obtain the font
$ ObjFontA5-> setName (' '); // set the font name
$ ObjFontA5-> setSize (10); // you can specify the font size.
$ ObjFontA5-> setBold (true); // set the font to bold
$ ObjFontA5-> getColor ()-> setARGB ('ff9999999999 '); // set the font color
// Set cell alignment
$ ObjAlignA5 = $ objStyleA5-> getAlignment (); // get alignment
$ ObjAlignA5-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_RIGHT); // The right of the horizontal ranking
$ ObjAlignA5-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER); // vertically centered
// Set the cell border
$ ObjBorderA5 = $ objStyleA5-> getBorders (); // get the border
$ ObjBorderA5-> getTop ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); // border style
$ ObjBorderA5-> getTop ()-> getColor ()-> setARGB ('ffffff000000'); // The color of the top border
$ ObjBorderA5-> getBottom ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN );
$ ObjBorderA5-> getLeft ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); // left Style
$ ObjBorderA5-> getRight ()-> setBorderStyle (PHPExcel_Style_Border: BORDER_THIN); // right style
// Set the cell fill color
$ ObjFillA5 = $ objStyleA5-> getFill (); // fill
$ ObjFillA5-> setFillType (PHPExcel_Style_Fill: FILL_SOLID); // fill type
$ ObjFillA5-> getStartColor ()-> setARGB ('ffeeeeeee ');
// Calculate the cell value
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('b7 ',' = SUM (B5: C5 )');
$ ObjPHPExcel-> getActiveSheet ()-> getCell ('b7 ')-> getCalculatedValue ();
// If you want to obtain a cell value, you first need to get a cell object through the getCell method of the worksheet, and then get the cell value through the getValue method of the cell object, if the cell value is calculated, you need to use the getCalculatedValue method to obtain the cell value and set the cell value. you only need to use the setCellValue method of the worksheet to set the cell value.
// $ DateTimeNow = time ();
$ ObjPHPExcel-> getActiveSheet ()-> setCellValue ('c10', PHPExcel_Shared_Date: PHPToExcel ($ dateTimeNow); // 41105.75
$ ObjPHPExcel-> getActiveSheet ()-> getStyle ('c10')-> getNumberFormat ()-> setFormatCode (PHPExcel_Style_NumberFormat: FORMAT_DATE_TIME4); // 18:00:54, the display mode is changed, and the type of the original value is not changed.
Echo gettype ($ objPHPExcel-> getActiveSheet ()-> getCell ('c10')-> getValue (); // double
Echo $ objPHPExcel-> getActiveSheet ()-> getCell ('c10')-> getValue (); // 41105.75
// '2017-10-21 'must be placed in quotation marks. Otherwise, the displayed value is 2010 text (recommended)
$ ObjPHPExcel-> setActiveSheetIndex (0)-> setCellValueExplicit ("D1", '2017-10-21 ', PHPExcel_Cell_DataType: TYPE_STRING); // feature: the string type is displayed on the left.
// Add a new worksheet
$ ObjExcel-> createSheet (); // create a new worksheet
$ ObjExcel-> getSheet (1)-> setTitle ('test 2'); // you can specify the title of the current worksheet.
// Protect cells
$ ObjExcel-> getSheet (1)-> getProtection ()-> setSheet (true );
$ ObjExcel-> getSheet (1)-> protectCells ('A1: c22', 'phpexcel ');
// Output the content to an excel file and save the file on the server
$ ObjWriter-> save ("test.xls ");
// Force output content to the browser for 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, 26 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'); // The parameter-indicates that the file is directly output to the browser for download by the client.
// Read objects in excel
$ PHPReader = new PHPExcel_Reader_Excel5 (); // Create an excel file read object
$ PHPExcel = $ PHPReader-> load ($ filePath); // read an excel table and return the excel file object
$ CurrentSheet = $ PHPExcel-> getSheet (0); // read the first worksheet in the excel file
$ AllColumn = $ currentSheet-> getHighestColumn (); // obtain the maximum column number of the current worksheet, for example, E
$ AllRow = $ currentSheet-> getHighestRow (); // obtain the total number of rows in the current worksheet
// Set the default workbook style
$ ObjPHPExcel-> getDefaultStyle ()-> getFont ()-> setName ('arial ');
$ ObjPHPExcel-> getDefaultStyle ()-> getFont ()-> setSize (8 );
// Merge cells
$ ObjPHPExcel-> getActiveSheet ()-> mergeCells ('a18: e22 ');
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
The above introduces Yii2 using PHPExcel to read excel, including some content, hope to be helpful to friends who are interested in PHP tutorials.
