Here is a summary of several ways to 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 ');
——————————————————————————————————————–
To 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 ");
Describe
$objPHPExcel->getproperties ()->setdescription ("Test document for Office-XLSX, generated using PHP classes.");
Key words
$objPHPExcel->getproperties ()->setkeywords ("Office openxml PHP");
Kinds
$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 a 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 ');
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 ');
Setting 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);
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 ');
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 (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 ());
Handling Chinese output issues
You need to convert the string to UTF-8 encoding for normal output, otherwise the Chinese characters will be output blank, as follows:
$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);
}
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]);
}
After the default sheet, create a worksheet
echo Date (' H:i:s '). "Create new Worksheet object\n";
$objPHPExcel->createsheet ();
$objWriter = Phpexcel_iofactory::createwriter ($objExcel, ' Excel5 ');
$objWriter-save (' php://output ');
The above describes the description of the order Phpexcel description, including the description of the order of the contents of the text, I hope the PHP tutorial interested in a friend helpful.
