PHP excel, phpexcel usage

Phpexcel is very convenient to operate on Excel, especially when images can be conveniently added. jpg and PNG formats are supported.

: Http://www.codeplex.com/PHPExcel

The following are the usage methods

Include 'phpexcel. php ';

Include 'phpexcel/Writer/excel2007.php ';

// Or include 'phpexcel/Writer/excel5.php'; for output. xls

Create an Excel file

$ Objphpexcel = new phpexcel ();

Save the Excel-2007 format

$ Objwriter = new phpexcel_writer_excel2007 ($ objphpexcel );

// Or $ objwriter = new phpexcel_writer_excel5 ($ objphpexcel); Non-2007 format

$ Objwriter-> save(”xxx.xlsx ");

Output directly to the 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 Excel attributes:

Created

$ Objphpexcel-> getproperties ()-> setcreator ("Maarten balliauw ");

Last modified

$ Objphpexcel-> getproperties ()-> setlastmodifiedby ("Maarten balliauw ");

Title

$ Objphpexcel-> getproperties ()-> settitle ("Office 2007 XLSX test document ");

Question

$ Objphpexcel-> getproperties ()-> setsubject ("Office 2007 XLSX test document ");

Description

$ Objphpexcel-> getproperties ()-> setdescription ("test document for Office 2007 XLSX, generated using PHP classes .");

Keywords

$ Objphpexcel-> getproperties ()-> setkeywords ("Office 2007 openxml PHP ");

Type

$ Objphpexcel-> getproperties ()-> setcategory ("Test Result file ");

---------------------------------------

Set the current sheet

$ Objphpexcel-> setactivesheetindex (0 );

Set Sheet Name

$ Objphpexcel-> getactivesheet ()-> settitle ('simple ');

Set the cell value

$ 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 ′);

Separate Cells

$ Objphpexcel-> getactivesheet ()-> unmergecells ('a28: b28 ′);

Cell protection

$ Objphpexcel-> getactivesheet ()-> getprotection ()-> setsheet (true); // needs to be 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 formatsn ";

$ Objphpexcel-> getactivesheet ()-> getstyle ('e4 ')-> getnumberformat ()-> setformatcode (phpexcel_style_numberformat: format_currency_eur_simple );

$ Objphpexcel-> getactivesheet ()-> duplicatestyle ($ objphpexcel-> getactivesheet ()-> getstyle ('e4 '), 'e5: e13 ′);

Set 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 );

// Vertical center

$ 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 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 Images

$ 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 ());

After the default sheet, create a worksheet

Echo date ('H: I: s'). "create new worksheet objectn ";

$ Objphpexcel-> createsheet ();

$ Objwriter = phpexcel_iofactory: createwriter ($ objexcel, 'excel5 ');

$ Objwriter-save ('php: // output ');