PHP Excel Quick Development Guide-phpexcel

PHP Quick Development Guide for exporting Excel


Phpexcel has proprietary development documentation. For detailed operations, see its development documentation. This document only optimizes and integrates its usage to facilitate rapid development in new projects.


Phpexcel can also generate files in two ways: Direct output and static file generation.


Direct output:


The main file is (the same directory file in the class directory ):

<? PHP


Include ("./class. php"); // contains the basic Class header file

Include ("./class/phpexcel. php"); // generate basic class definitions for Excel (note the case sensitivity of file names)


// If an Excel file is output directly, the file must be included.

Include ("./class/phpexcel/iofactory. php ");


// Create a phpexcel object that contains the output content and format

$ M_objphpexcel = new phpexcel ();


// Template file. to separate the format and content, the specific content of the output file is implemented in the template file.

// Operate the object $ m_objphpexcel in the template file

Include ("./include/Excel. php ");


// Type of the 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 the Excel format

If ($ m_exporttype = "Excel "){

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


// Output $ m_stroutputexcelfilename directly from the browser

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


}


// 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 common operations)

<? PHP


Global $ m_objphpexcel; // defined by an external file


// Set basic attributes

$ M_objphpexcel-> getproperties ()-> setcreator ("sun star data center ")

-> Setlastmodifiedby ("sun star data center ")

-> Settitle ("Microsoft Office Excel document ")

-> Setsubject ("test data report -- from Sunstar Data Center ")

-> Setdescription ("LD test data report, generate by Sunstar Data Center ")

-> Setkeywords ("Sunstar LD Report ")

-> Setcategory ("Test Result file ");


// Create multiple workbooks

$ Sheet1 = $ m_objphpexcel-> createsheet ();

$ Sheet2 = $ m_objphpexcel-> createsheet ();


// You can use the index to operate the corresponding workbook.

// You only need to set the index of the workbook to be operated as the current active workbook, as shown in

// $ M_objphpexcel-> setactivesheetindex (0 );


// Set the first workbook as an active workbook

$ M_objphpexcel-> setactivesheetindex (0 );


// Set the name of the activity workbook

// Use the iconv function for conversion encoding if it is a Chinese character

$ M_objphpexcel-> getactivesheet ()-> settitle (iconv ('gbk', 'utf-8', 'test workbooks '));


// Set the default font and size

$ M_objphpexcel-> getdefaultstyle ()-> getfont ()-> setname (iconv ('gbk', 'utf-8', ' '));

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




// Merge Cells

$ M_objphpexcel-> getactivesheet ()-> mergecells ('a1: p1 ');


// Define a style, bold, and centered

$ Stylearray1 = array (

'Font' => array (

'Bold '=> true,

'Color' => array (

'Arg' => '123 ',

),

),


'Alignment '=> array (

'Horizontal '=> phpexcel_style_alignment: horizontal_center,

),

);


// Apply the style to cell A1

$ M_objphpexcel-> getactivesheet ()-> getstyle ('a1')-> applyfromarray ($ stylearray1 );


// Set the cell style (black font)

$ M_objphpexcel-> getactivesheet ()-> getstyle ('h5')-> getfont ()-> getcolor ()-> setargb (phpexcel_style_color: color_black); // black


// Set the cell format (background)

$ M_objphpexcel-> getactivesheet ()-> getstyle ('h5')-> getfill ()-> getstartcolor ()-> setargb ('00ff99cc '); // set the background to pale pink


// Set the cell format (Number Format)

$ M_objphpexcel-> getactivesheet ()-> getstyle ('f1')-> getnumberformat ()-> setformatcode ('0. 000 ');


// Write content to a specific cell

$ M_objphpexcel-> getactivesheet ()-> setcellvalue ('a1', 'Hello baba ');


// Set the cell style (center)

$ M_objphpexcel-> getactivesheet ()-> getstyle ('h5')-> getalignment ()-> sethorizontal (phpexcel_style_alignment: horizontal_center );


// Put an image in the cell and the data image in the J1 Cell

$ Objdrawing = new phpexcel_worksheet_drawing ();

$ Objdrawing-> setname ('logo ');

$ Objdrawing-> setdescription ('logo ');

$ Objdrawing-> setpath ("../logo.jpg"); // The image path, which can only be a relative path
$ Objdrawing-> setwidth (400); // The image width.

$ Objdrawing-> setheight (123); // The Image Height.

$ Objdrawing-> setcoordinates ('j1'); // Cell


$ Objdrawing-> setworksheet ($ m_objphpexcel-> getactivesheet ());


// Set the content of cell A5 and add a hyperlink

$ M_objphpexcel-> getactivesheet ()-> setcellvalue ('a5 ', iconv ('gbk', 'utf-8', 'hyperlink keiyi.com '));

$ M_objphpexcel-> getactivesheet ()-> getcell ('a5 ')-> gethyperlink ()-> seturl ('HTTP: // www.keiyi.com /');


?>




Generate static files on the server


Compared with direct generation, the main difference between the two methods is that the generation format is different, and the template file is exactly the same. The following figure shows the changed format based on the previous example. Note the difference with the previous example.



<? PHP


// Contains the basic Class header file

Include ("./class. php ");


// Generate basic class definitions for Excel (note the case sensitivity of file names)

Include ("./class/phpexcel. php ");

// Contains files in excel5 format. If you need to generate an excel2007 file, you can include the corresponding writer.

Include ("./class/phpexcel/Writer/excel5.php ");

// Contains PDF files.

Include ("./class/phpexcel/Writer/pdf. php ");


// Create a phpexcel object that contains the output content and format

$ M_objphpexcel = new phpexcel ();


// Template file. to separate the format and content, the specific content of the output file is implemented in the template file.

// Operate the object $ m_objphpexcel in the template file

Include ("./include/Excel. php ");


// Type of the 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 storage path, which must be writable

$ M_stroutputpath = "./output /";



// If You Need to output the Excel format

If ($ m_exporttype = "Excel "){

$ Objwriter = new phpexcel_writer_excel5 ($ m_objphpexcel );

$ Objwriter-> Save ($ m_stroutputpath. $ m_stroutputexcelfilename );


}


// Output PDF Format

If ($ m_exporttype = "pdf "){

$ Objwriter = new phpexcel_writer_pdf ($ m_objphpexcel );

$ Objwriter-> Save ($ m_stroutputpath. $ m_stroutputpdffilename );


}

?>