PHP Operation Excel–phpexcel Basic usage detailed _php tutorial

Source: Internet
Author: User
Tags border color echo date getcolor
1.header
[PHP]
Header ("Content-type:application/vnd.ms-excel");
Header ("Content-disposition:attachment;filename=sample.xls");
Header ("Pragma:no-cache");
Header ("expires:0");
2.PHPExcel
Http://www.codeplex.com/PHPExcel
Http://www.phpexcel.net
The development Package tests directory has the detailed use instance support Chinese, note the file encoding file saves as Utf-8

Write Excel
[PHP]
Include class
Require_once (' classes/phpexcel.php ');
Require_once (' classes/phpexcel/writer/excel2007.php ');
$objPHPExcel = new Phpexcel ();

Set properties settings file attributes
$objPHPExcel->getproperties ()->setcreator ("Maarten Balliauw");
$objPHPExcel->getproperties ()->setlastmodifiedby ("Maarten Balliauw");
$objPHPExcel->getproperties ()->settitle ("Office" XLSX Test Document ");
$objPHPExcel->getproperties ()->setsubject ("Office" XLSX Test Document ");
$objPHPExcel->getproperties ()->setdescription ("Test document for Office-XLSX, generated using PHP classes.");
$objPHPExcel->getproperties ()->setkeywords ("Office openxml PHP");
$objPHPExcel->getproperties ()->setcategory ("Test result file");

Add some data
$objPHPExcel->setactivesheetindex (0);
$objPHPExcel->getactivesheet ()->setcellvalue (' A1 ', ' Hello ');//can specify location
$objPHPExcel->getactivesheet ()->setcellvalue (' A2 ', true);
$objPHPExcel->getactivesheet ()->setcellvalue (' A3 ', false);
$objPHPExcel->getactivesheet ()->setcellvalue (' B2 ', ' world! ');
$objPHPExcel->getactivesheet ()->setcellvalue (' B3 ', 2);
$objPHPExcel->getactivesheet ()->setcellvalue (' C1 ', ' Hello ');
$objPHPExcel->getactivesheet ()->setcellvalue (' D2 ', ' world! ');

Cycle
for ($i = 1; $i <200; $i + +) {
$objPHPExcel->getactivesheet ()->setcellvalue (' A '. $i, $i);
$objPHPExcel->getactivesheet ()->setcellvalue (' B '. $i, ' Test value ');
}

Date formatting
$objPHPExcel->getactivesheet ()->setcellvalue (' D1 ', Time ());
$objPHPExcel->getactivesheet ()->getstyle (' D1 ')->getnumberformat ()->setformatcode (phpexcel_style_ Numberformat::format_date_yyyymmddslash);

Add Comment Comment
$objPHPExcel->getactivesheet ()->getcomment (' E11 ')->setauthor (' Phpexcel ');
$objCommentRichText = $objPHPExcel->getactivesheet ()->getcomment (' E11 ')->gettext ()->createtextrun (' Phpexcel: ');
$objCommentRichText->getfont ()->setbold (true);
$objPHPExcel->getactivesheet ()->getcomment (' E11 ')->gettext ()->createtextrun ("\ r \ n");
$objPHPExcel->getactivesheet ()->getcomment (' E11 ')->gettext ()->createtextrun (' Total amount on the Current invoice, excluding VAT. ');

Add Rich-text string adds text to set style
$objRichText = new Phpexcel_richtext ($objPHPExcel->getactivesheet ()->getcell (' A18 '));
$objRichText->createtext (' This invoice is ');
$objPayable = $objRichText->createtextrun (' payable within thirty days after the end of the month ');
$objPayable->getfont ()->setbold (true);
$objPayable->getfont ()->setitalic (true);
$objPayable->getfont ()->setcolor (new Phpexcel_style_color (Phpexcel_style_color::color_darkgreen));
$objRichText->createtext (', unless specified otherwise on the invoice. ');

Merge cells merges detached cells
$objPHPExcel->getactivesheet ()->mergecells (' A18:e22 ');
$objPHPExcel->getactivesheet ()->unmergecells (' A18:e22 ');

Protect Cells Cell protection
$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 cell number formats numeric formatting
$objPHPExcel->getactivesheet ()->getstyle (' E4 ')->getnumberformat ()->setformatcode (phpexcel_style_ Numberformat::format_currency_eur_simple);
$objPHPExcel->getactivesheet ()->duplicatestyle ($objPHPExcel->getactivesheet ()->getstyle (' E4 '), ' E5: E13 ');

Set column widths setting columns width
$objPHPExcel->getactivesheet ()->getcolumndimension (' B ')->setautosize (true);
$objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setwidth (12);

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

Set alignments setting alignment
$objPHPExcel->getactivesheet ()->getstyle (' D11 ')->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_right);
$objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_justify);
$objPHPExcel->getactivesheet ()->getstyle (' A18 ')->getalignment ()->setvertical (phpexcel_style_ Alignment::vertical_center);
$objPHPExcel->getactivesheet ()->getstyle (' A3 ')->getalignment ()->setwraptext (true);

Set column borders setting columns border
$objPHPExcel->getactivesheet ()->getstyle (' A4 ')->getborders ()->gettop ()->setborderstyle (PHPExcel _style_border::border_thin);
$objPHPExcel->getactivesheet ()->getstyle (' A10 ')->getborders ()->getleft ()->setborderstyle ( Phpexcel_style_border::border_thin);
$objPHPExcel->getactivesheet ()->getstyle (' E10 ')->getborders ()->getright ()->setborderstyle ( Phpexcel_style_border::border_thin);
$objPHPExcel->getactivesheet ()->getstyle (' D13 ')->getborders ()->getleft ()->setborderstyle ( Phpexcel_style_border::border_thick);
$objPHPExcel->getactivesheet ()->getstyle (' E13 ')->getborders ()->getbottom ()->setborderstyle ( Phpexcel_style_border::border_thick);

Set border colors 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 ()->getright ()->getcolor () Setargb (' FF993300 ');

Set fills setting padding
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->setfilltype (Phpexcel_style_fill::fill_ SOLID);
$objPHPExcel->getactivesheet ()->getstyle (' A1 ')->getfill ()->getstartcolor ()->setargb (' FF808080 ') ;

Add a hyperlink to the sheet link
$objPHPExcel->getactivesheet ()->setcellvalue (' E26 ', ' www.phpexcel.net ');
$objPHPExcel->getactivesheet ()->getcell (' E26 ')->gethyperlink ()->seturl (' http://www.phpexcel.net ');
$objPHPExcel->getactivesheet ()->getcell (' E26 ')->gethyperlink ()->settooltip (' Navigate to website ');
$objPHPExcel->getactivesheet ()->getstyle (' E26 ')->getalignment ()->sethorizontal (phpexcel_style_ Alignment::horizontal_right);

Add a drawing to the worksheet adding a picture
$objDrawing = new phpexcel_worksheet_drawing ();
$objDrawing->setname (' Logo ');
$objDrawing->setdescription (' Logo ');
$objDrawing->setpath ('./images/officelogo.jpg ');
$objDrawing->setheight (36);
$objDrawing->setcoordinates (' B15 ');
$objDrawing->setoffsetx (110);
$objDrawing->setrotation (25);
$objDrawing->getshadow ()->setvisible (true);
$objDrawing->getshadow ()->setdirection (45);
$objDrawing->setworksheet ($objPHPExcel->getactivesheet ());

Play around with inserting and removing rows and columns
$objPHPExcel->getactivesheet ()->insertnewrowbefore (6, 10);
$objPHPExcel->getactivesheet ()->removerow (6, 10);
$objPHPExcel->getactivesheet ()->insertnewcolumnbefore (' E ', 5);
$objPHPExcel->getactivesheet ()->removecolumn (' E ', 5);

ADD Conditional formatting
$objConditional 1 = new phpexcel_style_conditional ();
$objConditional 1->setconditiontype (Phpexcel_style_conditional::condition_cellis);
$objConditional 1->setoperatortype (Phpexcel_style_conditional::operator_lessthan);
$objConditional 1->setcondition (' 0 ');
$objConditional 1->getstyle ()->getfont ()->getcolor ()->setargb (phpexcel_style_color::color_red);
$objConditional 1->getstyle ()->getfont ()->setbold (true);

Set AutoFilter Automatic Filtering
$objPHPExcel->getactivesheet ()->setautofilter (' a1:c9 ');

Hide "Phone" and "Fax" column hidden columns
$objPHPExcel->getactivesheet ()->getcolumndimension (' C ')->setvisible (false);
$objPHPExcel->getactivesheet ()->getcolumndimension (' D ')->setvisible (false);

Set Document security
$objPHPExcel->getsecurity ()->setlockwindows (true);
$objPHPExcel->getsecurity ()->setlockstructure (true);
$objPHPExcel->getsecurity ()->setworkbookpassword ("Phpexcel");

Set Sheet Security Setup worksheet safety
$objPHPExcel->getactivesheet ()->getprotection ()->setpassword (' Phpexcel ');
$objPHPExcel->getactivesheet ()->getprotection ()->setsheet (TRUE);//This should is enabled in order to enable Any of the following!
$objPHPExcel->getactivesheet ()->getprotection ()->setsort (true);
$objPHPExcel->getactivesheet ()->getprotection ()->setinsertrows (true);
$objPHPExcel->getactivesheet ()->getprotection ()->setformatcells (true);

Calculated data calculation
Echo ' Value of B14 [=count (B2:B12)]: '. $objPHPExcel->getactivesheet ()->getcell (' B14 ')->getcalculatedvalue (). "\ r \ n";

Set outline Levels
$objPHPExcel->getactivesheet ()->getcolumndimension (' E ')->setoutlinelevel (1);
$objPHPExcel->getactivesheet ()->getcolumndimension (' E ')->setvisible (false);
$objPHPExcel->getactivesheet ()->getcolumndimension (' E ')->setcollapsed (true);

Freeze panes
$objPHPExcel->getactivesheet ()->freezepane (' A2 ');

Rows to repeat at top
$objPHPExcel->getactivesheet ()->getpagesetup ()->setrowstorepeatattopbystartandend (1, 1);

Set data validation Validate input values
$objValidation = $objPHPExcel->getactivesheet ()->getcell (' B3 ')->getdatavalidation ();
$objValidation->settype (Phpexcel_cell_datavalidation::type_whole);
$objValidation->seterrorstyle (phpexcel_cell_datavalidation::style_stop);
$objValidation->setallowblank (TRUE);
$objValidation->setshowinputmessage (TRUE);
$objValidation->setshowerrormessage (TRUE);
$objValidation->seterrortitle (' Input error ');
$objValidation->seterror (' number is not allowed! ');
$objValidation->setprompttitle (' allowed input ');
$objValidation->setprompt (' only numbers between and allowed. ');
$objValidation->setformula1 (10);
$objValidation-&GT;SETFORMULA2 (20);
$objPHPExcel->getactivesheet ()->getcell (' B3 ')->setdatavalidation ($objValidation);

Create a new worksheet, after the default sheet, creates a work label
$objPHPExcel->createsheet ();
$objPHPExcel->setactivesheetindex (1);

Set Header and footer. When no different headers to Odd/even is used, odd header is assumed. Header Footer
$objPHPExcel->getactivesheet ()->getheaderfooter ()->setoddheader (' &c&hplease treat this document As confidential! ');
$objPHPExcel->getactivesheet ()->getheaderfooter ()->setoddfooter (' &l&b '. $objPHPExcel GetProperties ()->gettitle (). ' &rpage &p of &n ');

Set page orientation and size direction sizes
$objPHPExcel->getactivesheet ()->getpagesetup ()->setorientation (phpexcel_worksheet_pagesetup::o Rientation_landscape);
$objPHPExcel->getactivesheet ()->getpagesetup ()->setpapersize (phpexcel_worksheet_pagesetup::P apersize_ A4);

Rename Sheet Renaming sheet tabs
$objPHPExcel->getactivesheet ()->settitle (' simple ');

Set Active sheet Index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setactivesheetindex (0);

Save Excel file Saved
$objWriter = new phpexcel_writer_excel2007 ($objPHPExcel);
$objWriter->save (Str_replace ('. php ', '. xlsx ', __file__));

Save Excel 5 File
Require_once (' classes/phpexcel/writer/excel5.php ');
$objWriter = new Phpexcel_writer_excel5 ($objPHPExcel);
$objWriter->save (Str_replace ('. php ', '. xls ', __file__));

1.6.2 New Save
Require_once (' classes/phpexcel/iofactory.php ');
$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel2007 ');
$objWriter->save (Str_replace ('. php ', '. xls ', __file__));

Read Excel
[PHP]
Include class
Require_once (' classes/phpexcel/reader/excel2007.php ');
$objReader = new phpexcel_reader_excel2007;
$objPHPExcel = $objReader->load ("05featuredemo.xlsx");

Read and write CSV
[PHP]
require_once ("05featuredemo.inc.php");
Require_once (' classes/phpexcel/writer/csv.php ');
require_once (' classes/phpexcel/reader/csv.php ');
require_once (' classes/phpexcel/writer/excel2007.php ');
//write to CSV format write
$objWriter = new Phpexcel_writer_csv ($objPHPExcel);
$objWriter->setdelimiter (';');
$objWriter->setenclosure (");
$objWriter->setlineending ("\ r \ n");
$objWriter->setsheetindex (0);
$objWriter->save (Str_replace ('. php ', '. csv ', __file__));
//read from CSV format read
$objReader = new Phpexcel_reader_csv ();
$objReader->setdelimiter (';');
$objReader->setenclosure (");
$objReader->setlineending ("\ r \ n");
$objReader->setsheetindex (0);
$objPHPExcelFromCSV = $objReader->load (str_replace ('. php ', '. csv ', __file__));
//write to Excel2007 format
$objWriter = new phpexcel_writer_excel2007 ($objPHPExcelFromCSV);
$objWriter 2007->save (Str_replace ('. php ', '. xlsx ', __file__));

Write HTML
[PHP]
Require_once ("05featuredemo.inc.php");
Require_once (' classes/phpexcel/writer/html.php ');
Write to HTML format
$objWriter = new phpexcel_writer_html ($objPHPExcel);
$objWriter->setsheetindex (0);
$objWriter->save (Str_replace ('. php ', '. htm ', __file__));

Write a PDF
[PHP]
Require_once ("05featuredemo.inc.php");
Require_once (' classes/phpexcel/iofactory.php ');
Write to PDF format www.2cto.com
$objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' PDF ');
$objWriter->setsheetindex (0);
$objWriter->save (Str_replace ('. php ', '. pdf ', __file__));
Echo Memory Peak usage
echo Date (' H:i:s '). "Peak Memory Usage:". (Memory_get_peak_usage (TRUE)/1024/1024)

http://www.bkjia.com/PHPjc/477393.html www.bkjia.com true http://www.bkjia.com/PHPjc/477393.html techarticle 1.header [PHP] Header (content-type:application/vnd.ms-excel); Header (content-disposition:attachment;filename= Sample.xls); Header (Pragma:no-cache); Header (expires:0); 2.PHPExcel htt ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.