PHP Export Excel Quick Development Guide
Phpexcel has proprietary development documentation, please refer to its development documentation for detailed operations, this document is only optimized for its use and is easy to develop quickly in new projects.
There are also two ways to generate files for Phpexcel, one way for direct output, and one for generating static files.
Direct output:
The primary file is (the same directory file as the class directory):
Copy Code code as follows:
<?php
Include ("./class/class.php"); Basic header file that contains class
Include ("./class/phpexcel/phpexcel.php"); Generate basic class definitions for Excel (note the case of file names)
If you output an Excel file directly, include this file
Include ("./class/phpexcel/phpexcel/iofactory.php");
Creates a Phpexcel object that contains the contents and formatting of the output
$m _objphpexcel = new Phpexcel ();
Template file, in order to achieve the separation of format and content, the output file specific content implementation in the template file
Template files Manipulate object $m_objphpexcel
Include ("./include/excel.php");
Type of output file, Excel or PDF
$m _exporttype = "Excel";
$m _stroutputexcelfilename = Date (' y-m-j_h_i_s '). XLS "; Output Excel file name
$m _stroutputpdffilename = Date (' y-m-j_h_i_s '). PDF "; Output PDF file name
Phpexcel_iofactory, Output Excel
Require_once dirname (__file__). ' /classes/phpexcel/iofactory.php ';
If you need to output Excel formatting
if ($m _exporttype== "Excel") {
$objWriter = Phpexcel_iofactory::createwriter ($m _objphpexcel, ' Excel5 ');
Direct output from browser $m_stroutputexcelfilename
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-excel;");
Header ("Content-type:application/octet-stream");
Header ("Content-type:application/download");
Header ("Content-disposition:attachment;filename=". $m _stroutputexcelfilename);
Header ("Content-transfer-encoding:binary");
$objWriter->save ("Php://output");
}
If you need to output PDF format
if ($m _exporttype== "pdf") {
$objWriter = Phpexcel_iofactory::createwriter ($m _objphpexcel, ' PDF ');
$objWriter->setsheetindex (0);
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/pdf");
Header ("Content-type:application/octet-stream");
Header ("Content-type:application/download");
Header ("Content-disposition:attachment;filename=". $m _stroutputpdffilename);
Header ("Content-transfer-encoding:binary");
$objWriter->save ("Php://output");
}
?>
Template file content (additional frequently used actions)
Copy Code code as follows:
<?php
Global $m _objphpexcel; Defined by an external file
Set basic properties
$m _objphpexcel->getproperties ()->setcreator ("Sun Star Data Center")
->setlastmodifiedby ("Sun Star Data Center")
->settitle ("Microsoft Office Excel Document")
->setsubject ("Test data"--from Sunstar data Center)
->setdescription ("LD Test data, Generate by Sunstar data Center")
->setkeywords ("Sunstar ld")
->setcategory ("Test result file");
Create multiple workbooks
$sheet 1 = $m _objphpexcel->createsheet ();
$sheet 2 = $m _objphpexcel->createsheet ();
Manipulating the index allows you to manipulate the corresponding work sheet
Just set the workbook index that you want to manipulate as the current active workbook, such as
$m _objphpexcel->setactivesheetindex (0);
Set first workbook as active workbook
$m _objphpexcel->setactivesheetindex (0);
Set the active workbook name
If you are Chinese, you must use the Iconv function to convert the code
$m _objphpexcel->getactivesheet ()->settitle (Iconv (' GBK ', ' utf-8 ', ' Test Workbook '));
Set default font and size
$m _objphpexcel->getdefaultstyle ()->getfont ()->setname (Iconv (' GBK ', ' utf-8 ', ' Arial '));
$m _objphpexcel->getdefaultstyle ()->getfont ()->setsize (10);
Set the width of a column
$m _objphpexcel->getactivesheet ()->getcolumndimension (' A ')->setwidth (15);
Set the height of a row
$m _objphpexcel->getactivesheet ()->getrowdimension (' 6 ')->setrowheight (30);
Merging cells
$m _objphpexcel->getactivesheet ()->mergecells (' A1:p1 ');
Define a style, bold, centered
$styleArray 1 = Array (
' Font ' => array (
' Bold ' => true,
' Color ' =>array (
' Argb ' => ' 00000000 ',
),
),
' Alignment ' => array (
' Horizontal ' => phpexcel_style_alignment::horizontal_center,
),
);
Apply a style to cell A1
$m _objphpexcel->getactivesheet ()->getstyle (' A1 ')->applyfromarray ($styleArray 1);
Set cell style (black font)
$m _objphpexcel->getactivesheet ()->getstyle (' H5 ')->getfont ()->getcolor () ()->setargb (PHPExcel_Style _color::color_black); Black
formatting cells (background)
$m _objphpexcel->getactivesheet ()->getstyle (' H5 ')->getfill ()->getstartcolor () (" 00ff99cc '); Set the background to light pink
Format cell (number format)
$m _objphpexcel->getactivesheet ()->getstyle (' F1 ')->getnumberformat ()->setformatcode (' 0.000 ');
Write content to a specific cell
$m _objphpexcel->getactivesheet ()->setcellvalue (' A1 ', ' Hello Baby ');
Set cell style (centered)
$m _objphpexcel->getactivesheet ()->getstyle (' H5 ')->getalignment ()->sethorizontal (PHPExcel_Style_ Alignment::horizontal_center);
Put a picture in the cell and place the data picture in the J1 cell then cell
$objDrawing = new phpexcel_worksheet_drawing ();
$objDrawing->setname (' Logo ');
$objDrawing->setdescription (' Logo ');
$objDrawing->setpath (".. /logo.jpg "); Picture path, only relative path
$objDrawing->setwidth (400); Picture width
$objDrawing->setheight (123); Picture height
$objDrawing->setcoordinates (' J1 ');//Cell
$objDrawing->setworksheet ($m _objphpexcel->getactivesheet ());
Set cell contents of A5 and add hyperlinks
$m _objphpexcel->getactivesheet ()->setcellvalue (' A5 ', iconv (' GBK ', ' utf-8 ', ' Hyperlink keiyi.com '));
$m _objphpexcel->getactivesheet ()->getcell (' A5 ')->gethyperlink ()->seturl (' http://www.keiyi.com/');
?>
Generating static files on the server side
Compared to direct generation, the main difference between the two methods is that the format is different, the template file is exactly the same, below is a change on the basis of the above example, notice the difference with the above example.
Copy Code code as follows:
<?php
Basic header file that contains class
Include ("./class/class.php");
Generate basic class definitions for Excel (note the case of file names)
Include ("./class/phpexcel/phpexcel.php");
Contains files written in Excel5 format, if you need to generate excel2007 files, including the corresponding writer can
Include ("./class/phpexcel/phpexcel/writer/excel5.php");
Include write PDF format file
Include ("./class/phpexcel/phpexcel/writer/pdf.php");
Creates a Phpexcel object that contains the contents and formatting of the output
$m _objphpexcel = new Phpexcel ();
Template file, in order to achieve the separation of format and content, the output file specific content implementation in the template file
Template files Manipulate object $m_objphpexcel
Include ("./include/excel.php");
Type of output file, Excel or PDF
$m _exporttype = "PDF";
$m _stroutputexcelfilename = Date (' y-m-j_h_i_s '). XLS "; Output Excel file name
$m _stroutputpdffilename = Date (' y-m-j_h_i_s '). PDF "; Output PDF file name
Output file save path, this path must be writable
$m _stroutputpath = "./output/";
If you need to output Excel formatting
if ($m _exporttype== "Excel") {
$objWriter = new Phpexcel_writer_excel5 ($m _objphpexcel);
$objWriter->save ($m _stroutputpath. $m _stroutputexcelfilename);
}
If you need to output PDF format
if ($m _exporttype== "pdf") {
$objWriter = new Phpexcel_writer_pdf ($m _objphpexcel);
$objWriter->save ($m _stroutputpath. $m _stroutputpdffilename);
}
?>
