Yii2 uses PHPExcel to read excel

During personal use, I save some experiences using PHPExcel for later reference:
Combined with PHP’s Yii framework, it can be used easily. And there is no need to modify Yii's automatic loading files and other methods mentioned on the Internet.
Specific usage:
Download phpoffice http://phpexcel.codeplex.com/releases/view/119187
The first-level directory structure after downloading is Classes, Documentation, Examples, changelog.txt, install.txt, license.txt.
Download The last thing to do is to let Yii load PHPExcel. lele imitates the loading method of yii2-swiftmailer, modifies composer.json under the project root path, adds "phpoffice/phpexcel": "dev-develop" to "require", and then uses the command Enter the root directory and execute composer update (it seems that you don’t need to download phpexcel manually, composer will download it automatically...) After completion, you can use $PHPExcel = new PHPExcel(); in PHP to get an instance. Remember, you must add it when you add new, otherwise you will get an error that the class cannot be found (lele doesn’t know why you need to add it. If anyone knows, please tell me in the comments).
OK, after it works, write some usage code:
$filePath = "../file/test.xlsx"; // The path of the file to be read
$PHPExcel = new PHPExcel(); // Get the example and use it later
$PHPReader = new PHPExcel_Reader_Excel2007(); // Reader is very important, used to read excel files
if (!$PHPReader->canRead($filePath)) { // Reader is used here Try to read the file. If 07 does not work, use 05. If 05 does not work, an error will be reported. Note that return here is the way of 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); // After Reader reads it, load it to the Excel instance
-*-*-*- *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*- *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
The above is enough to read excel, let’s traverse it below Output this two-dimensional table. PHPExcel's method names are relatively semantic.
$allSheet = $PHPExcel->getSheetCount(); // Number of sheets
$currentSheet = $PHPExcel->getSheet(0); // Get the first sheet (workbook?)
$allColumn = $currentSheet ->getHighestColumn(); // The highest column, such as AU. The column starts from A
$allRow = $currentSheet->getHighestRow(); // The largest row, such as 12980. The row starts from 0
$result = new ReadFileResult(); // result is an entity class written by myself to store results
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 is that AU->65, the following U is not calculated, so traversing using index method is Defective.
array_push($lineVal, $val);
}
array_push($result->content, $lineVal);
}
Looking at the development documentation, I found that there are two very useful traversal methods.
First, use toArray to convert this sheet into a two-dimensional array.
$currentSheet->getStyle('A2:A6')->getNumberFormat()->setFormatCode('yyyy-mm-dd'); // Convert to the time format from A2 to A6
$result-> content = $currentSheet->toArray('', true, true); //Convert the current sheet into a two-dimensional array
Second, use the row and column iterator that comes with PHPExcel. This will be much safer.
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) { // This is to compare whether the data type in the cell is number
            $cellStyleFormat = $cell->getStyle( $cell->getCoordinate() )->getNumberFormat(); // Next this The two sentences are to get the format of this number. $formatCode = $cellStyleFormat->getFormatCode(); // If it is an ordinary number, the 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, it is the time format)
echo $ cell-& gt; getCoordInate (). "". $ formatcode; echo "& lt;";
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 It is the time to convert Excel time to PHP according to the format
          } else {
                                                                         gt;getValue();
}
                                                                                                                                                                                    Get the column coordinates
                                                                                                       ​ cell->getColumn());
}
array_push($result->content, $lineVal);
}
lele saw his summary of PHPExcel classes and methods from an article by yanhui_wei, and it was well written. Special excerpt:

For common excel report operations, we need to master the following class libraries:
(1) PHPExcel: workbook object
excel document processing object is mainly used to manage our excel documents, how to do it Management (managed through properties and methods)? As we all know, classes are mainly composed of attributes and methods. Managing excel documents through the PHP program is actually managed through the attributes and methods of this object. Let's take a look at the attributes and methods in the PHPExcel class. , these properties and methods are mainly used to manage those aspects of excel documents
getProperties(): Get the property object of the currently active worksheet, return the property object
getActiveSheet(): Get the currently active worksheet, return the worksheet object
getActiveSheetIndex(): Get the index value of the currently active worksheet, return int
setActiveSheetIndex(): Set the index of the currently active worksheet, return the worksheet object
getSheetByName(): Get the current worksheet object through the worksheet name, return Worksheet object
getDefaultStyle(): Get the default style of the excel document (the style of all worksheets), return the style object
createSheet(): Create a new worksheet after the current active worksheet
getSheetCount(): Get the excel document The number of worksheets in it, returns int
getSheetNames(): Gets an array of all worksheet names in the excel document
(2) PHPExcel_Worksheet: Worksheet object
Worksheet object, mainly used to manage our worksheets, how to manage it?It is also managed through properties and methods, but the worksheet object can be obtained through the excel document object in most cases
toArray(): Convert the data in the worksheet into an array
fromArray(): Get the data from the array and fill it into the work Table, returns the worksheet object
getCell(): Gets the cell object
getCellByColumnAndRow(): Gets the specified cell through the column index and row index, returns the cell object
getDefaultStyle(): Gets the default style of the worksheet, returns the style object
getHighestColumn(): Get the largest column of the worksheet, return the name of the column
getColumnDimension(): Get the current column
getStyle(): Get the style of the specified cell, return the style object
getParent(): Get the parent class object, return Excel document object
getTitle(): Get the title or name of the worksheet, return the string type
setCellValue(): Set the value of the cell, return the worksheet object or cell object, completely depends on the value of the parameter
setCellValueByColumnAndRow() : Set the value of the cell through column index and row index, the return type is the same as above
setCellValueExplicit(): Set the value of the cell, and display the specified data type, return the worksheet object
setCellValueExplicitByColumnAndRow(): Set the cell through column and row index Value
setTitle(): Set the worksheet title
(3) PHPExcel_Cell: Cell object
     
(4) PHPExcel_Style: Style object, mainly used to set the style of the cell: alignment, font, border, padding, etc., follow us The css styles I have learned before are similar. If you want to set the alignment, font size, border color, etc. here, it is all done through the style object. getActiveCell(): Get the name of the currently active cell and return string; For example, A1
getActiveSheet(): Get the currently active worksheet and return the worksheet object
getAlignment(): Get the alignment object and return the alignment object
getBorders(): Get the border object and return the border object
getFill(): Get the fill object
getFont(): Get the font object
setFont(): Set the font and return the style object
(5) PHPExcel_Style_Alignment: Alignment object
getHorizontal(): Get the horizontal centering method
getVertical(): Get the vertical centering method
setHorizontal(): Set the horizontal centering method and return the alignment object
setVertical(): Set the vertical centering method and return the alignment object
Centering method:
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 tilt
setName(): Set font name
setSize(): Set 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 for outputting 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 for input xls file
canRead(): Whether the current reader object can read the workbook file
load(): Load the workbook object from a workbook file, that is, load the data in the workbook file into the workbook object for management
(10) PHPExcel_IOFactory: Read and write operation objects
createReader(): Create different read objects according to different parameters: The main function is to read the data in the workbook file
createWriter(): Return different writing objects according to different parameters Object: The main function is to write the data in the PHPExcel workbook object into a workbook file
load(): Load the PHPExcel workbook object from the workbook file, that is: load the data in the workbook file into the PHPExcel workbook Object to manage
PHPExcel object: It is a workbook object
include_once "PHPExcel/Writer/Excel5.php";//Mainly used for other lower versions, and the file name suffix is ​​xls, if we want to generate the suffix For excel files in xls format, it is recommended to introduce such a library
include_once "PHPExcel/Writer/Excel2007.php";//Mainly used for excel2007 format, file name suffix is ​​xlsx excel file, if we want to generate suffix xlsx format excel file excel file, it is recommended to introduce such a library
$objWriter = new PHPExcel_Writer_Excel5($objExcel);//Create a file format writing object instance. This object is mainly used to write content to a file in a specified format, such as writing content to Excel files with the suffix name xls format, etc., used for other board formats
$objWriter = new PHPExcel_Writer_Excel2007($objExcel);//Create a file format writing object instance. This object is mainly used to write content to a file in a specified format, such as writing content to an excel file with the suffix name xls format, etc. , used for excel2007 format
$objWriter->setOffice2003Compatibility(true);//Compatible with office2003
//Set the basic properties of the document
$objProps = $objExcel->getProperties(); //Get the PHPExcel_document document object
$objProps- >setCreator("Zeal Li"); //Set the author
$objProps->setLastModifiedBy("Zeal Li"); //Set the last modification 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"); //Keywords
$objProps->setCategory("Test"); //Category
$objExcel->setActiveSheetIndex(0);//Set users to open excel When you file, the first sheet you see, if not set, defaults to the last sheet operated
$objActSheet->setTitle('Test Sheet');//Set the name of the currently active workbook
//According to the cell The name sets the cell content, and PHPExcel automatically determines the content type of the cell based on the incoming 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 the cell as string type
$objActSheet->setCellValueExplicit('A5','847475847857487584',PHPExcel_Cell_DataType::TYPE_STRING);
//Merge cells
$objActSheet->mergeCells('B1:C22');
//Set the width of the column
$objActSheet->getColumnDimension('B')->setAutoSize(true);
$objActSheet->getColumnDimension(' A')->setWidth(30);
//Set the height of the row
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(11.5);
//Format: Main Used to operate cells, such as setting fonts, setting alignment, setting borders, etc.
$objStyleA5 = $objActSheet->getStyle('A5');//Get the style of cell A5
//Set the cell Font
$objFontA5 = $objStyleA5->getFont(); //Get the font
$objFontA5->setName('宋体'); //Set the font name
$objFontA5->setSize(10); // Set the font size
$objFontA5->setBold(true);//Set the font to be bold
$objFontA5->getColor()->setARGB('FF999999');//Set the font color
//Set the cell Alignment
$objAlignA5 = $objStyleA5->getAlignment();//Get alignment
$objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);//Horizontal right
$objAlignA5->setVertical(PHPExcel_Style_Alignment : :VERTICAL_CENTER);//Vertically centered
//Set the border of the cell
$objBorderA5 = $objStyleA5->getBorders();//Get the border
$objBorderA5->getTop()->setBorderStyle(PHPExcel_Style_Border:: BORDER_THIN);//Border style
$objBorderA5->getTop()->getColor()->setARGB('FFFF0000');//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 fill color of the cell
$objFillA5 = $objStyleA5->getFill();//Fill
$objFillA5->setFillType(PHPExcel_Style_Fill::FILL_SOLID);//Fill type
$objFillA5->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 of a cell, you first need to use the getCell method of the worksheet Obtain a cell object, and then obtain the value of the cell through the getValue method of the cell object. If the value of the cell is obtained by calculation, you need to use the getCalculatedValue method to obtain the value of the cell and set the value of the cell. We only need to set it through the setCellValue method of the worksheet
//$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, just changing the display method, it will not change the type of the original value
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); //Features: String types are displayed on the left
//Add a new worksheet
$objExcel->createSheet();//Create a new worksheet
$ objExcel->getSheet(1)->setTitle('Test 2');//Set 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 the 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');//Parameter-indicates direct output to the browser for client download
//excel reading object
$PHPReader = new PHPExcel_Reader_Excel5();//Create an excel file reader Get the object
$PHPExcel = $PHPReader->load($filePath);//Read an excel table and return the excel file object
$currentSheet = $PHPExcel->getSheet(0);//Read the excel file The first worksheet in
$allColumn = $currentSheet->getHighestColumn();//Get the largest column number of the current worksheet, for example, E
$allRow = $currentSheet->getHighestRow();//Get How many rows are there in the current worksheet? //Set the default style of the workbook. $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle() ->getFont()->setSize(8);
//Merge cells
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

Copyright Statement: This article is an original article by the blogger and may not be reproduced without the blogger's permission.

The above introduces how Yii2 uses PHPExcel to read excel, including the relevant content. I hope it will be helpful to friends who are interested in PHP tutorials.