PHP Export Excel Document

The following is a summary of several ways to use include ' phpexcel.php '; include ' phpexcel/writer/excel2007.php ';//or include ' phpexcel/writer/ Excel5.php '; For output. xls Create a excel$objphpexcel = new Phpexcel (); save excel-2007 format $objwriter = new Phpexcel_writer_excel2007 ($ Objphpexcel);//or $objwriter = new Phpexcel_writer_excel5 ($objPHPExcel); Non-2007 format $objwriter->save ("xxx.xlsx"); direct output to browser $objwriter = new Phpexcel_writer_excel5 ($objPHPExcel); Header (" Pragma:public "), Header (" Expires:0″ "), Header (" Cache-control:must-revalidate, post-check=0, Pre-check=0″); Header (" Content-type:application/force-download "); Header (" Content-type:application/vnd.ms-execl "); Header (" Content-Type : Application/octet-stream "); Header (" content-type:application/download ");; Header (' content-disposition:attachment;filename= ' Resume.xls '); Header ("Content-transfer-encoding:binary"); $ Objwriter->save (' Php://output '); —————————————————————————————————————— – Set properties for Excel: Create person $objphpexcel-> GetProperties ()->setcreator ("Maarten Balliauw"); Last modified person $objphpexcel->getproperties ()->setlastmodifiedby ("Maarten Balliauw"); title $objphpexcel->getproperties ()->settitle ("Office" XLSX Test Document "), topic $objphpexcel->getproperties ()->setsubject (" Office XLSX Test Document "); Description $objphpexcel-> GetProperties ()->setdescription ("Test document for Office" XLSX, generated using PHP classes. "); Keyword $objphpexcel->getproperties ()->setkeywords ("Office openxml PHP"); kind $objphpexcel->getproperties () ->setcategory ("Test result file"); —————————————————————————————————————— – Sets the current sheet$objphpexcel-> Setactivesheetindex (0); set sheet Name$objphpexcel->getactivesheet ()->settitle (' simple '); Set cell Value $ Objphpexcel->getactivesheet ()->setcellvalue (' A1 ', ' String '); $objPHPExcel->getactivesheet () Setcellvalue (' A2 ', ' n '), $objPHPExcel->getactivesheet ()->setcellvalue (' A3 ', true); $objPHPExcel Getactivesheet ()->setcellvalue (' C5 ', ' =sum (C2:C4) '); $objPHPExcel->getactivesheet ()->setcellvalue (' B8 ', ' =min (B2:C5) '); Merge cells $objphpexcel-&Gt;getactivesheet ()->mergecells (' A18:e22 '); separating cell $objphpexcel->getactivesheet ()->unmergecells (' A28:B28 Protection Cell$objphpexcel->getactivesheet ()->getprotection ()->setsheet (true); Needs to BES set to true in order to enable any worksheet protection! $objPHPExcel->getactivesheet ()->protectcells ( ' A3:e13 ', ' phpexcel '); Set format//Set cell number Formatsecho date (' H:i:s '). "Set cell number formats\n"; $objPHPExcel->getactivesheet ()->getstyle (' E4 ')->getnumberformat () Setformatcode (phpexcel_style_numberformat::format_currency_eur_simple), $objPHPExcel->getactivesheet () Duplicatestyle ($objPHPExcel->getactivesheet ()->getstyle (' E4 '), ' e5:e13 '); set width width//set column widths$ Objphpexcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true); $objPHPExcel Getactivesheet ()->getcolumndimension (' D ')->setwidth (12); set Font$objphpexcel->getactivesheet () GetStyle (' B1 ')->getfont ()->setname (' Candara '); $objPHPExcel->getactivesHeet ()->getstyle (' B1 ')->getfont ()->setsize (), $objPHPExcel->getactivesheet ()->getstyle (' B1 ')- >getfont ()->setbold (true), $objPHPExcel->getactivesheet ()->getstyle (' B1 ')->getfont () Setunderline (Phpexcel_style_font::underline_single), $objPHPExcel->getactivesheet ()->getstyle (' B1 ') GetFont ()->getcolor ()->setargb (phpexcel_style_color::color_white), $objPHPExcel->getactivesheet () GetStyle (' E1 ')->getfont ()->getcolor ()->setargb (phpexcel_style_color::color_white); $objPHPExcel Getactivesheet ()->getstyle (' D13 ')->getfont ()->setbold (true); $objPHPExcel->getactivesheet () GetStyle (' E13 ')->getfont ()->setbold (true); set Align$objphpexcel->getactivesheet ()->getstyle (' D11 ')- >getalignment ()->sethorizontal (phpexcel_style_alignment::horizontal_right), $objPHPExcel Getactivesheet ()->getstyle (' D12 ')->getalignment ()->sethorizontal (phpexcel_style_alignment::horizontal _right); $objPHPExcel->getactivesheET ()->getstyle (' D13 ')->getalignment ()->sethorizontal (phpexcel_style_alignment::horizontal_right); $ Objphpexcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->sethorizontal (PHPExcel_Style_ alignment::horizontal_justify)///Vertical Center $objphpexcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()- >setvertical (phpexcel_style_alignment::vertical_center); Set column's Border$objphpexcel->getactivesheet ()- >getstyle (' A4 ')->getborders ()->gettop ()->setborderstyle (Phpexcel_style_border::border_thin); $ Objphpexcel->getactivesheet ()->getstyle (' B4 ')->getborders ()->gettop ()->setborderstyle (PHPExcel_ Style_border::border_thin); $objPHPExcel->getactivesheet ()->getstyle (' C4 ')->getborders ()->gettop ()- >setborderstyle (Phpexcel_style_border::border_thin), $objPHPExcel->getactivesheet ()->getstyle (' D4 ')- >getborders ()->gettop ()->setborderstyle (Phpexcel_style_border::border_thin), $objPHPExcel Getactivesheet ()->getstyle (' E4 ')->getborders ()->gettop ()->setborderstyle (phpexcel_style_border::border_thin); setting Border color$ Objphpexcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->getleft ()->getcolor ()->setARGB (' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->gettop ()->getcolor ( )->setargb (' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->getbottom ()->getcolor ()->setargb (' FF993300 '); $objPHPExcel->getactivesheet ()->getstyle (' E13 ')->getborders ()->gettop ()->getcolor ()->setargb (' FF993300 '), $objPHPExcel->getactivesheet ()->getstyle (' E13 ')- >getborders ()->getbottom ()->getcolor ()->setargb (' FF993300 '); $objPHPExcel->getactivesheet () GetStyle (' E13 ')->getborders ()->getright ()->getcolor ()->setargb (' FF993300 '); Set fill Color $objphpexcel-> Getactivesheet ()->getstyle (' A1 ')->getfill ()->setfilltype (phpexcel_style_fill::fill_solid); $ Objphpexcel->getactivesheet()->getstyle (' A1 ')->getfill ()->getstartcolor ()->setargb (' FF808080 '); $objPHPExcel Getactivesheet ()->getstyle (' B1 ')->getfill ()->setfilltype (phpexcel_style_fill::fill_solid); $ Objphpexcel->getactivesheet ()->getstyle (' B1 ')->getfill ()->getstartcolor ()->setargb (' FF808080 '); Add Picture $objdrawing = new phpexcel_worksheet_drawing (); $objDrawing->setname (' Logo '); $objDrawing->setdescription ( ' Logo '); $objDrawing->setpath ('./images/officelogo.jpg '); $objDrawing->setheight ($objDrawing) Setworksheet ($objPHPExcel->getactivesheet ()); $objDrawing = new phpexcel_worksheet_drawing (); $objDrawing SetName (' Paid '); $objDrawing->setdescription (' Paid '); $objDrawing->setpath ('./images/paid.png '); $ Objdrawing->setcoordinates (' B15 '); $objDrawing->setoffsetx; $objDrawing->setrotation (25); $ Objdrawing->getshadow ()->setvisible (true), $objDrawing->getshadow ()->setdirection (), $objDrawing- >setworksheet ($objPHPExcel->getactivesheet ());Processing the Chinese output problem needs to convert the string to UTF-8 encoding to normal output, otherwise the Chinese characters will be output as blank, the following processing: $STR = Iconv (' gb2312 ', ' utf-8 ', $str);   Or you can write a function that specifically handles Chinese strings: function convertUTF8 ($STR) {if (empty ($STR)) return '; Return Iconv (' gb2312 ', ' utf-8 ', $str);}  Reads data from the database from the database output, such as: $db = new Mysql ($dbconfig), $sql = "SELECT * from table name"; $row = $db->getall ($sql); $row is a two-dimensional array $count = count ($row); for ($i = 2; $i <= $count +1; $i + +) {$objPHPExcel->getactivesheet ()->setcellval UE (' A '. $i, ConvertUTF8 ($row [$i -2][1]); $objPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, ConvertUTF8 ($row [$i -2][2]); $objPHPExcel->getactivesheet ()->setcellvalue (' C '. $i, ConvertUTF8 ($row [$i -2][3]); $objPHPExcel->getactivesheet ()->setcellvalue (' D '. $i, ConvertUTF8 ($row [$i -2][4]); $objPHPExcel->getactivesheet ()->setcellvalue (' E '. $i, convertUTF8 (Date ("y-m-d", $row [$i -2][5])); $objPHPExcel->getactivesheet ()->setcellvalue (' F '. $i, ConvertUTF8 ($row [$i -2][6]); $objPHPExcel->getactivesheet ()->setcellvalue ('G '. $i, ConvertUTF8 ($row [$i -2][7]); $objPHPExcel->getactivesheet ()->setcellvalue (' H '. $i, ConvertUTF8 ($row [$i -2][8]);} After the default sheet, create a worksheetecho date (' H:i:s '). "Create new Worksheet object\n"; $objPHPExcel->createsheet (); $objWriter = Phpexcel_iofactory::createwriter ($ Objexcel, ' Excel5 '); $objWriter-save (' php://output ');

　　

PHP Export Excel Document