Reprint: Processing of PHP Excel

Here is a summary of several ways to useInclude ' phpexcel.php ';Include ' phpexcel/writer/excel2007.php ';//Or include ' phpexcel/writer/excel5.php '; For the output. xlsCreate an Excel$objPHPExcel =NewPhpexcel (); 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"); The last person modified$objPHPExcel->getproperties ()->setlastmodifiedby ("Maarten Balliauw"); title$objPHPExcel->getproperties ()->settitle (Office "XLSX Test Document"); topic$objPHPExcel->getproperties ()->setsubject (Office "XLSX Test Document"); describe$objPHPExcel->getproperties ()->setdescription ("Test document for Office" XLSX, generated using PHP classes.)); keywords$objPHPExcel->getproperties ()->setkeywords ("Office openxml PHP"); kind$objPHPExcel->getproperties ()->setcategory ("Test result file"); —————————————————————————————————————— – Set the current sheet$objPHPExcel->setactivesheetindex (0); Set the name of the sheet$objPHPExcel->getactivesheet ()->settitle (' Simple '); Set the value of the cell$objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' String ');$objPHPExcel->getactivesheet ()->setcellvalue (' A2 ', 12);$objPHPExcel->getactivesheet ()->setcellvalue (' A3 ',True);$objPHPExcel->getactivesheet ()->setcellvalue (' C5 ', ' =sum (C2:C4) ');$objPHPExcel->getactivesheet ()->setcellvalue (' B8 ', ' =min (B2:C5) '); Merge cells$objPHPExcel->getactivesheet ()->mergecells (' A18:e22 ')); separating cells$objPHPExcel->getactivesheet ()->unmergecells (' a28:b28 ')); Protect 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 '); formatting//Set Cell number formatsEchoDate (' 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 (20);$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);//Center vertically$objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->setvertical (phpexcel_style_ Alignment::Vertical_center); Set the border of column$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); Sets the color of the BORDER$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 =NewPhpexcel_worksheet_drawing ();$objDrawing->setname (' Logo '));$objDrawing->setdescription (' Logo '));$objDrawing->setpath ('./images/officelogo.jpg '));$objDrawing->setheight (36);$objDrawing->setworksheet ($objPHPExcelGetactivesheet ());$objDrawing =NewPhpexcel_worksheet_drawing ();$objDrawing->setname (' Paid ');$objDrawing->setdescription (' Paid ');$objDrawing->setpath ('./images/paid.png '));$objDrawing->setcoordinates (' B15 ');$objDrawing->setoffsetx (110);$objDrawing->setrotation (25);$objDrawing->getshadow ()->setvisible (True);$objDrawing->getshadow ()->setdirection (45);$objDrawing->setworksheet ($objPHPExcelGetactivesheet ());//Processing Chinese output problem requires converting a string to utf-8 encoding, in order to normal output, otherwise Chinese characters will be output as blank, the following processing: $STR = Iconv (' gb2312 ', ' utf-8 ', $str); or you can write a function that handles Chinese strings specifically: 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 ');

Reprint: Processing of PHP Excel