Phpexcel Chinese help Manual (Knowledge point)

$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) '); Copy Code Merge cells $objPHPExcel->getactivesheet ()->mergecells (' A18:e22 '); Copy Code Separating cells $objPHPExcel->getactivesheet ()->unmergecells (' a28:b28 '); Copy Code 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 '); Copy Code Set format Set Cell number formats echo 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 '); Copy Code Setting width width Set column Widths $objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true); $objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (12); Copy Code 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); Copy Code 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); Copy Code Center vertically $objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->setvertical (phpexcel_style_ Alignment::vertical_center); Copy Code 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); Copy Code Set 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 '); Copy Code 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 ') ; Copy Code Add picture $objDrawing = new phpexcel_worksheet_drawing (); $objDrawing->setname (' Logo '); $objDrawing->setdescription (' Logo '); $objDrawing->setpath ('./images/officelogo.jpg '); $objDrawing->setheight (36); $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 (110); $objDrawing->setrotation (25); $objDrawing->getshadow ()->setvisible (true); $objDrawing->getshadow ()->setdirection (45); $objDrawing->setworksheet ($objPHPExcel->getactivesheet ()); Copy Code Processing the Chinese output problem requires the conversion of the string to UTF-8 encoding to normal output, otherwise the Chinese characters will be output blank, the following processing: $STR = Iconv (' gb2312 ', ' utf-8 ', $str); or you can write a function that specializes in Chinese strings: function ConvertUTF8 ($STR) { if (empty ($STR)) return '; Return Iconv (' gb2312 ', ' utf-8 ', $str); } Copy Code Output data processing from the database Read data from a database such as: $db = new Mysql ($dbconfig); $sql = "SELECT * from table name"; $row = $db->getall ($sql); $row as a two-dimensional array $count = count ($row); for ($i = 2; $i <= $count +1; $i + +) { $objPHPExcel->getactivesheet ()->setcellvalue (' 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]); } Copy Code After the default sheet, create a worksheet here to insert a paragraph, before the scripting School also introduced a few articles about Phpexcel, Interested friends can look at: use Phpexcel to discriminate and format the date format in Excel example Phpexcel export Excel colors inconsistent with colors in the Web page resolution CI uses PHPEXCEL to export data to Excel echo Date (' H:i:s '). "Create new Worksheet object\n"; $objPHPExcel->createsheet (); $objWriter = Phpexcel_iofactory::createwriter ($objExcel, ' Excel5 '); $objWriter-save (' php://output '); Copy Code Attached, several common methods of use. Include ' phpexcel.php '; Include ' phpexcel/writer/excel2007.php '; Or include ' phpexcel/writer/excel5.php '; For the output. xls Create an 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 '); ?> Copy Code

