Export an Excel memo using Phpexcel

Importing and exporting Excel using the Phpexcel class is quick and easy, documenting the operations commonly used in this class for a rainy time.

<?PHPrequire_onceRoot_path. ' includes/phpexcel/classes/phpexcel.php ';//introducing the Phpexcel class library    $objPHPExcel=NewPhpexcel ();//instantiate Phpexcel//Set properties to set exported document information    $objPHPExcel->getproperties ()->setcreator ("Dabai")//Set Creator->setlastmodifiedby ("Dabai")//Last modified person->settitle (Office "XLSX Document")//title->setsubject (Office "XLSX Document")//Topics->setdescription ("Document for Office" XLSX, generated using PHP classes. ")//Description->setkeywords ("Office openxml PHP")//Key Words->setcategory ("Tongji");//type//Set the current sheet, that is, the first page of Excel table    $objPHPExcel->setactivesheetindex (0); $sheet=$objPHPExcel->getactivesheet ();//can be said when the page is assigned to $sheet variable conveniently called later    $sheet->settitle (' simple ');//set the paging name for the page//set Excel to the default centering mode    $objPHPExcel->getdefaultstyle ()->getalignment ()->sethorizontal (Phpexcel_style_alignment::horizontal_center);//Center Horizontally    $objPHPExcel->getdefaultstyle ()->getalignment ()->setvertical (Phpexcel_style_alignment::vertical_center);//Center middle//Set Default cell width and row height in vertical direction    $sheet->getdefaultrowdimension ()->setrowheight (30); $sheet->getdefaultcolumndimension ()->setwidth (10); //set the width of the corresponding cell    $sheet->getcolumndimension (' A ')->setwidth (10); $sheet->getcolumndimension (' B ')->setautosize (true); //set the height of the corresponding cell    $sheet->getrowdimension (' A ')->setrowheight (10); //set the alignment of a column    $sheet->getstyle (' E ')->getalignment ()->sethorizontal (phpexcel_style_alignment::horizontal_left);//Align Left; Fill in the Data    $sheet->setcellvalue (' A1 ', ' serial number ')->setcellvalue (' B1 ', ' name ');//Can be consistent operation, you can also add a single bar
$sheet->setcellvalueexplicit (' G '. $start, $val [' Bankcard '],phpexcel_cell_datatype::type_string); Scientific counting method adjusts to normal digital display//Freeze cell    $sheet->freezepane (' A2 ');//freezes the left column of the A2 and above. Merge detached cells    $sheet->mergecells (' a1:b4 ');//adds a value to the starting cell.    /*There is a merge in the data of the loop, when there are branches. Tip: Note the start and end points when looping.            For example: if ($key = = 0) {$start = $key + 2;        $end = $start + $goods _len-1;            } else {$start = $end + 1;         $end = $start + $goods _len-1; }    */    //Separating cells    $sheet->->unmergecells (' A18:e22 '); //Formatting cells    $sheet->getcell (' C2 ')->setvalueexplicit (' 861391327543258 ', phpexcel_cell_datatype::type_numeric);//you can format cells//add Comment Add comments    $sheet->getcomment (' E11 ')->setauthor (' Phpexcel ')); $objCommentRichText=$sheet->getcomment (' E11 ')->gettext ()->createtextrun (' Phpexcel: ')); $objCommentRichText->getfont ()->setbold (true); $sheet->getcomment (' E11 ')->gettext ()->createtextrun ("\ r \ n"); $sheet->getcomment (' E11 ')->gettext ()->createtextrun (' Total amount on the current invoice, excluding VAT. ')); //set font, bold    $sheet->getstyle (' A1 ')->getfont ()->setname (' Candara ');//Font    $sheet->getstyle (' A ')->getfont ()->setbold (true);//bold//Set font color    $sheet->getstyle (' B1 ')->getfont ()->getcolor ()->setargb (Phpexcel_style_color::color_white); //Set Border    $sheet->getstyle (' A1 ')->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border::Border_thin); $sheet->getstyle (' A1 ')->getborders ()->getleft ()->setborderstyle (Phpexcel_style_border::Border_thin); $sheet->getstyle (' A1 ')->getborders ()->getright ()->setborderstyle (Phpexcel_style_border::Border_thin); $sheet->getstyle (' A1 ')->getborders ()->getbottom ()->setborderstyle (Phpexcel_style_border::Border_thin); //Set Border color    $sheet->getstyle (' A1 ')->getborders ()->getleft ()->getcolor ()->setargb (' FF993300 '); $sheet->getstyle (' A1 ')->getborders ()->gettop ()->getcolor ()->setargb (' FF993300 '); //or using array parameters    $styleArray=Array(        ' Borders ' =Array(            ' Allborders ' =Array(                //' style ' = phpexcel_style_border::border_thick,//border is thick' Style ' = Phpexcel_style_border::border_thin,//fine border//' color ' = = Array (' ARGB ' = ' FFFF0000 '),),        )    ); $sheet->getstyle (' A1:n ').$n)->applyfromarray ($styleArray);//border from A1 to n$n//Set Fill color    $sheet->getstyle (' A1 ')->getfill ()->setfilltype (Phpexcel_style_fill::fill_solid); $sheet->getstyle (' A1 ')->getfill ()->getstartcolor ()->setargb (' FF808080 ')); //Add link    $sheet->setcellvalue (' A1 ', ' www.aaa.net '); $sheet->getcell (' A1 ')->gethyperlink ()->seturl (' http://www.aaa.com ')); $sheet->getcell (' A1 ')->gethyperlink ()->settooltip (' Navigate to website '); $sheet->getstyle (' A1 ')->getalignment ()->sethorizontal (phpexcel_style_alignment::Horizontal_right);
Set Document security
$objPHPExcel->getsecurity ()->setlockwindows (true);
$objPHPExcel->getsecurity ()->setlockstructure (true);
$objPHPExcel->getsecurity ()->setworkbookpassword ("Phpexcel");

Set Sheet Security Setup worksheet safety
    $sheet->getprotection ()->setpassword (' Phpexcel ');
    $sheet->getprotection ()->setsheet (TRUE);//
    $sheet->getprotection ()->setsort (true);
    $sheet->getprotection ()->setinsertrows (true);
    $sheet->getprotection ()->setformatcells (true);
Create a new work label
$objPHPExcel->createsheet ();
$objPHPExcel->setactivesheetindex (1);

// Output    Header (' Content-type:application/vnd.ms-excel ');     Header $filename . '. xls ');     Header (' cache-control:max-age=0 ');         $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');     $objWriter->save (' php://output ');

Export an Excel memo using Phpexcel