Phpexcel: PhP open source class library generated by documents such as Excel

Documentation. It also supports the generation of PDF, HTML, and CSV documents. Phpexcel also supports filling data with existing Excel documents as templates to generate the above documents. In addition, its API documentation and demo are quite complete and very helpful for developers.

 

The official phpexcel website is as follows:

Http://phpexcel.codeplex.com/

 

The current version is 1.7.6. The downloaded and decompressed phpexcel directory structure is as follows:

Where:

Classes directoryPhpexcelSource codeFile:Phpexcel. php fileIs the class library interface, external PHPCodeUse it to call phpexcel;Phpexcel directoryThe specific operation code for reading and generating different documents (such as Excel, excel2007, and PDF) is called by the phpexcel. php file under the classes directory in the factory mode.

Documentation directoryThe official phpexcel help documentation, especiallyAPI directoryThe following web files are all phpexcel API help documents, while others are about this open-source project.

Tests directoryNearly 40 Examples of phpexcel are small.Program, And can be tested and learned in the Apache + PhP environment together with the classes directory.

 

Below are some common PHP Excel Code segments:

<? PHP // import the phpexcel class library require_once ("classes/phpexcel. PHP "); // usually, PHP Excel objects can be instantiated in two ways. // 1. use the New Keyword to create a blank document $ phpexcel = new phpexcel (); // 2. create $ phpexcel = phpexcel_iofactory: createreader ("excel5") by reading an existing template-> load ("template.xls "); /*** the instantiated phpexcel object is similar to a file stored in memory, * You can perform operations on it to modify the document data * // set the document attributes $ phpexcel-> getproperties ()-> setcreator ("Liu Jian ") // Document Author-> setlastmodifiedby ("Liu Jian") // The Last modifier-> sett Itle ("Office 2003 XLS test document") // Title-> setsubject ("Office 2003 XLS test document") // topic-> setdescription ("test document for Office 2003 xls, generated using phpexcel. ") // remarks-> setkeywords (" Office 2003 openxml PHP ") // keyword-> setcategory (" Test Result file "); // category // by default, the newly created blank document (via new) has only one Worksheet (sheet) and its number (INDEX) 0 // you can add a new worksheet $ phpexcel-> createsheet (1) as follows; // obtain an existing worksheet $ sheet = $ phpexce L-> getsheet (1); // set the number of the currently activated worksheet $ phpexcel-> setactivesheetindex (1 ); // obtain the currently activated worksheet $ sheet = $ phpexcel-> getactivesheet (); // after obtaining the worksheet, you can operate on its cell to modify the data. // modify the worksheet name $ sheet-> settitle ("test "); // set the value of cell A5 $ sheet-> setcellvalue ("A5", date ('Y-m-d h: I: s ')); // set the value of 3rd columns (E3) in 5th rows $ sheet-> setcellvaluebycolumnandrow (4, 3, date ('Y-m-d h: I: s ')); // get the value of cell A5 $ sheet-> getcell ("A5")-> getvalue (); // merge cells $ sheet-> mergecells ("C3: G6 "); // Split the merged cells $ sheet-> unmergecells ("C3: G6"); // set the attribute of row 3rd $ sheet-> getrowdimension (3)-> setrowheight (100) // Row Height-> setvisible (true) // whether it is visible. The default value is true-> setrowindex (6) // change the row number to 6-> setoutlinelevel (5 ); // priority level. The default value is 0. The parameter must be 0 to 7. // you can use getcolumndimension ("F") to set the attribute of column F) replace $ sheet-> getcolumndimension ("F")-> setwidth (200) // column width-> setcolumnindex ("I ") // change the column number to I-> setvisible (false) // whether visible-> setautos Ize (true); // automatically adapts to the column width. // insert one row before the first row to change the row to the new 3rd row, the other rows move down 1 row in sequence $ sheet-> insertnewrowbefore (3, 1); // insert 1 column before Row C, and the column will become the new column C, the other columns are shifted to $ sheet-> insertnewcolumnbefore ("C", 1); // method 1 $ sheet-> insertnewcolumnbeforebyindex (2, 1); // method 2, column C is the 2nd Column // get the style object $ style = $ sheet-> getstyle ("D3") of cell D3; // It is equivalent to getstylebycolumnandrow (3, 3) // set the font attribute of the cell $ style-> getfont ()-> setbold (true) // whether to bold-> setsize (16) // font size-> setname ("gungsuh ") // Body name, only applicable to foreign fonts-> setitalic (true) // whether italic is italic-> setstrikethrough (true) // whether strikethrough exists-> setunderline (phpexcel_style_font: underline_doubleaccounting) // underline type-> getcolor ()-> setargb (phpexcel_style_color: color_blue); // font color // set the background filling attribute of the cell $ style-> getfill () -> setfilltype (phpexcel_style_fill: fill_solid) // fill mode-> getstartcolor ()-> setargb (phpexcel_style_color: color_yellow ); // background color // set the format of the number in the cell $ style-> Getnumberformat ()-> setformatcode ("0.00"); // set the text alignment in the cell $ style-> getalignment ()-> sethorizontal (phpexcel_style_alignment: horizontal_center) // horizontal direction-> setvertical (phpexcel_style_alignment: vertical_center); // vertical direction $ sheet-> setcellvalue ("D3", "12.3456 "); // Save the phpexcel_iofactory: createwriter ($ phpexcel, 'excel5')-> Save ("output.xls"); // output the document to the page header ('content-type: application/vnd. MS-Excel '); Header ('Content-Disposition: attachment; filename = "test.xls" '); header ('cache-control: Max-age = 0'); phpexcel_iofactory: createwriter ($ phpexcel, 'excel5')-> Save ('php: // output');?>

Note that:

1.Phpexcel itself has functions related to inserting images into cells and setting cell borders. However, I personally feel that it is easier to dynamically add images and set borders without using static Excel templates to generate documents. For more information, see the final references.

2.When a new row or column is added, the style of the new row or column is automatically consistent with the previous row/column of the new row/column.

3.When setting the cell fill color, you must first set the fill mode, otherwise it will become invalid.

4.Through the static method createreader or createwriter of phpexcel_iofactory, you must input the parameter string corresponding to the document format, where:

• Excel 2003(.xls)
• Excel 2007(.xlsx)
• Response (response)
• Csv(csv(.csv)

When outputting a document, the file extension must match the parameter string.