Phpexcel Section Chinese Reference Manual (export Import)

/** * The following is an example of use, for a line beginning with////is a different alternative, please open the corresponding line comment according to the actual need. * If you use Excel5, the content of the output should be GBK encoded.*///Require_once ' phpexcel.php '; Uncomment//Require_once ' phpexcel/writer/excel5.php '; For other low versions of XLS//or//Require_once ' phpexcel/writer/excel2007.php '; For excel-2007 format$objExcel =New Phpexcel ();//Create a Processing object instance//create file format to write to the object instance, uncomment$objWriter =New Phpexcel_writer_excel5 ($objExcel);//For other version formats//or//$objWriter = new phpexcel_writer_excel2007 ($objExcel); For 2007 format//$objWriter->setoffice2003compatibility (TRUE); Set document basic Properties$objProps =$objExcelGetProperties ();//$objProps->setcreator ("Zeal Li"); $objProps->setlastmodifiedby ("Zeal Li"); $objProps->settitle ("Office XLS Test Document"); $objProps->setsubject ("Office XLS Test Document, Demo"); $objProps->setdescription ("Test document, generated by Phpexcel."); $objProps->setkeywords ("Office Excel Phpexcel"); $objProps->setcategory ("Test"); Sets the current sheet index for subsequent content operations. It is generally only necessary to display a call when multiple sheet are used. By default, Phpexcel automatically creates the first sheet to be set sheetindex=0$objExcel->setactivesheetindex (0);$objActSheet =$objExcelGetactivesheet ();//Sets the name of the currently active sheet$objActSheet->settitle (' Test sheet ');//Set cell contents////by Phpexcel automatically determine cell content type based on 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 specify content type$objActSheet->setcellvalueexplicit (' A5 ', ' 847475847857487584 ',Phpexcel_cell_datatype::type_string);//Merge cells$objActSheet->mergecells (' B1:c22 ');//Separating cells$objActSheet->unmergecells (' B1:c22 ');//Set cell style///Set width$objActSheet->getcolumndimension (' B ')->setautosize (True);$objActSheet->getcolumndimension (' A ')->setwidth (30);$objStyleA 5 =$objActSheet->getstyle (' A5 ');//Sets the number format for the contents of the cell. If you use Phpexcel_writer_excel5 to generate content,//You need to note that in the Phpexcel_style_numberformat class, the const variable definition//Various custom formatting methods, Other types can be used normally, but when setformatcode//is format_number, the actual effect is not formatted as "0". Require//Modify the GETXF ($style) method in the source code of the Phpexcel_writer_excel5_format class, and//add one in front of the IF ($this->_biff_version = = 0x0500) {(Near line No. 363) Line code://if ($ifmt = = = ' 0 ') $ifmt = 1; Format to Phpexcel_style_numberformat::format_number, avoid some large numbers//are used scientific notation display, with the following Setautosize method can let each line of content// Are all displayed according to the original content.$objStyleA 5Getnumberformat ()->setformatcode (Phpexcel_style_numberformat::Format_number);//Set font$objFontA 5 =$objStyleA 5->GetFont ();$objFontA 5->setname (' Courier New ');$objFontA 5->setsize (10);$objFontA 5->setbold (True);$objFontA 5->setunderline (Phpexcel_style_font::Underline_single);$objFontA 5->getcolor ()->setargb (' FF999999 '));//Set alignment$objAlignA 5 =$objStyleA 5->Getalignment ();$objAlignA 5->sethorizontal (phpexcel_style_alignment::Horizontal_right);$objAlignA 5->setvertical (phpexcel_style_alignment::Vertical_center);//Set border$objBorderA 5 =$objStyleA 5->GetBorders ();$objBorderA 5->gettop ()->setborderstyle (Phpexcel_style_border::Border_thin);$objBorderA 5->gettop ()->getcolor ()->setargb (' FFFF0000 ');//Color$objBorderA 5->getbottom ()->setborderstyle (Phpexcel_style_border::Border_thin);$objBorderA 5->getleft ()->setborderstyle (Phpexcel_style_border::Border_thin);$objBorderA 5->getright ()->setborderstyle (Phpexcel_style_border::Border_thin);//Set Fill Color$objFillA 5 =$objStyleA 5->Getfill ();$objFillA 5->setfilltype (Phpexcel_style_fill::Fill_solid);$objFillA 5->getstartcolor ()->setargb (' ffeeeeee '));//Copies the style information from the specified cell.$objActSheet->duplicatestyle ($objStyleA 5, ' B1:c22 ');//Add a picture$objDrawing =NewPhpexcel_worksheet_drawing ();$objDrawing->setname (' zealimg ');$objDrawing->setdescription (' Image inserted by Zeal ');$objDrawing->setpath ('./zeali.net.logo.gif '));$objDrawing->setheight (36);$objDrawing->setcoordinates (' C23 ');$objDrawing->setoffsetx (10);$objDrawing->setrotation (15);$objDrawing->getshadow ()->setvisible (True);$objDrawing->getshadow ()->setdirection (36);$objDrawing->setworksheet ($objActSheet);//Add a new worksheet$objExcelCreatesheet ();$objExcel->getsheet (1)->settitle (' Test 2 ');//Protect cells$objExcel->getsheet (1)->getprotection ()->setsheet (True);$objExcel->getsheet (1)->protectcells (' A1:c22 ', ' phpexcel ');//Output content//$outputFileName =$title. ". xls";Header ("Content-type:application/force-download");Header ("Content-type:application/octet-stream");Header ("Content-type:application/download");Header (' Content-disposition:attachment;filename= '.$outputFileName. ‘"‘);// to file ////header (' content-disposition:inline;filename= '. $outputFileName. '); To the browser header ("content-transfer-encoding:binary" header ("Expires:mon, Jul 1997 05:00:00 GMT" 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 '); 
                

Import Excel

$PHPExcel =NewPhpexcel ();/** Read Excel By default with excel2007, if the format is not correct, then use the previous version to read*/$PHPReader =NewPhpexcel_reader_excel2007 ();if (!$PHPReader->canread ($filePath)) {$PHPReader =NewPhpexcel_reader_excel5 ();if (!$PHPReader->canread ($filePath)) {Echo ' No Excel ';Return; }}$PHPExcel =$PHPReader->load ($filePath);$currentSheet =$PHPExcel->getsheet (0);/** Read the first worksheet in an Excel file*/$allColumn =$currentSheet->gethighestcolumn ();/** Get the maximum number of columns*/$allRow =$currentSheet->gethighestrow ();/** Get a total number of lines*/Phpexcel_cell::columnindexfromstring ();//The letter column is converted to a number column such as: AA to 27for ($currentRow = 2;$currentRow <= $allRow;  $currentRow =  $currentRow + 2 ) { $pair _name = $ Currentsheet->getcellbycolumnandrow (0,  $currentRow) getvalue ();  $trailtime =  $currentSheet->getcellbycolumnandrow (3,  $currentRow)->getvalue ();  $trailtime = date ("y-m-d h:i:s", phpexcel_shared_date::exceltophp ( $trailtime)-28800); // time conversion    

Phpexcel Section Chinese Reference Manual (export Import)