Php excel export class based on phpexcel, phpexcelexcel_PHP tutorial

Php: phpexcelexcel, an excel export class based on phpexcel. Php is an excel export class based on phpexcel. phpexcelexcel recently has to write a project. because there are too many excel export projects, it is easy to write a class .? Php ** @ usage. * Introduce the class library php excel export class based on phpexcel, phpexcelexcel

Recently, I want to write a project. because there are too many excel files exported from the project, it is easy to write a class.

 FileName = 'File name'; // sets the file name. The default value is the timestamp * $ excel-> format = '000000'. // file type. the default value is 2007, others are excel5 * $ record = array ('delkey' => array ('id', 'addtime', 'status '), // if $ data contains columns that do not need to be displayed, you can describe them here. Delete key values that do not need to be exported 'sort '=> array ('keyname' => array ('subobjectname', 'flag'), // sort by keyName column, if it does not exist, it is not sorted. // 'Reorder' => 'desc', // sorting method. DESC is in reverse order, and ASC is in positive order. If the keyName exists, sort the keyName. if the keyName does not exist, sort it by the key name of the array. if the reorder does not exist, sort it. // if the sort exists, sort it. Otherwise, the keyName is not sorted, if the keyName exists, sort by Settings. if the keyName does not exist, sort by characters. if the reorder does not exist or is empty or DES, sort in the forward order. if the reorder is DESC, the reverse order is set. 'Excelstyle' => array ('setname' => 'arial', // font style 'setsize' => '12', // font size ), // Global table style 'title' => array ('tablename' => 'Subject list', 'center' => true, 'direction' => 'right ', 'merge' => '2', 'setsize' => '30'), // title, center vertical, and direction. Right, left, up, down. Merge is used to merge several cells, and setSize is the font size 'data' => array ('tablename' => 'Subject name', 'width' => '30 ', 'setname' => 'body', 'setsize' => '20', 'background' => 'red', 'textcolor' => 'white ', 'bold '=> true, 'underline' => true, 'bordercolor' => 'cyany', 'center' => true ,), array ('tablename' => 'Subject ', 'width' => '50', 'center' => true), // the color table is: black, white, red, green, blue, yellow, magenta, cyan), // The table name tableName is the name, width is the table width, setName is the font style, background is the background color, textColor It is the font color, bold is bold, underline is the underline, and borderColor is the border color. 'merge' => array (// 'flag' => array ('keyword' => 'chu', 'direction' => 'right ', 'merge' => '2'), the key value of merge is the key value of the data array to be processed, and keyword is used to perform other style operations if this keyword exists, if the keyword does not exist, all cells with the flag key value are executed. 'All' => array ('width' => '30', 'setname' => 'body', 'setsize' => '20 ', 'Background' => 'red', 'textcolor' => 'white', 'bold '=> true, 'underline' => true, 'bordercolor' => 'cyn', 'center' => true,),), //); // export configuration * $ excel-> export ($ record, $ data); // $ record is the export configuration, $ data is the data of the database, and $ data can be an array or an object. * ***/$ Address = dirname (_ FILE __)). '/PHPExcel'; include $ address. '/PHPExcel. class. php'; include $ address. '/PHPExcel/Writer/excel2007.php'; include $ address. '/PHPExcel/Writer/excel5.php'; include $ address. '/PHPExcel/IOFactory. php ';/****************************** to generate an excel document. */Class excelC {public $ format = '000000'; // conversion format. the default value is 2007. For other versions, enter a public $ fileName number other than 2007; // the default file name is a timestamp. Private $ objExcel; private $ letters; public function _ construct () {$ this-> fileName = time (); $ this-> fileTitle = 'export data '; $ this-> objExcel = new PHPExcel (); $ this-> letters = $ this-> letter () ;}// export the excel attribute private function attribute () {$ this-> objExcel-> getProperties ()-> setCreator ("Lixing Limited"); // created by $ this-> objExcel-> getProperties () -> setLastModifiedBy ("Lixing Limited"); // last modifier $ this-> objExcel-> getProperties ()-> setTitle (" "); Output data "); // Title $ this-> objExcel-> getProperties ()-> setSubject (" export data "); // question $ this-> objExcel-> getProperties ()-> setDescription ("data export"); // description $ this-> objExcel-> getProperties () -> setKeywords ("office export"); // keyword $ this-> objExcel-> getProperties ()-> setCategory ("excel "); // type} // set the table (if only one sheet can be ignored, this function is created by default .) Private function sheet () {$ this-> objExcel-> setActiveSheetIndex (0); // set the current table $ this-> objExcel-> getActiveSheet () -> setTitle ('Excel '); // set the table name .} /*************************** Export excel * @ attr $ record as the header and style set * @ attr $ data to the data to be exported */public function export ($ record = array (), $ data = array () {if (! $ Data) return false; if (! Is_array ($ record) return false; // table style and other settings // process the obtained data $ data = $ this-> maniData ($ record, $ data ); // Obtain the overall style. $ This-> excelData ($ record, $ data); // $ this-> objExcel-> getActiveSheet ()-> setCellValue ('A1', 'quarterly '); $ this-> down (); // export and download}/** process table */private function excelData (& $ record, & $ data) {$ this-> attribute (); // sets the attribute $ this-> sheet (); // sets the table $ this-> whole ($ record ); // set the overall style $ this-> tableHeader ($ record); // set the table header. $ This-> tableContent ($ record, $ data); // set the table $ this-> excelTitle ($ record, 2 ); // set the title}/** set the overall style of the table */private function whole (& $ record) {if (! Array_key_exists ('excelstyle', $ record) return false; $ excelStyle = $ record ['excelstyle']; $ default = $ this-> objExcel-> getDefaultStyle (); if (array_key_exists ('setname', $ excelStyle) $ default-> getFont ()-> setName ($ excelStyle ['setname']); // set the font style if (array_key_exists ('setsize', $ excelStyle) $ default-> getFont ()-> setSize ($ excelStyle ['setsize']); // set the font size}/** set the title */private function excelTitle ($ record, $ num) {$ titleL = $ This-> letters [0]; if (! Array_key_exists ('title', $ record) return false; $ this-> appOintStyle ($ titleL, 1, $ record ['title']);} /** set the table header. */Private function tableHeader ($ record) {if (! Array_key_exists ('data', $ record) return false; $ objExcel = $ this-> objExcel; $ letters = $ this-> letters; if (! Is_array ($ record ['data']) return false; $ I = 0; $ hang = 2; foreach ($ record ['data'] as $ k => $ v) {$ this-> appOintStyle ($ letters [$ I], $ hang, $ v ); $ I ++ ;}} private function setCellValue ($ letter, $ data) {if (@ $ data) $ this-> objExcel-> getActiveSheet () -> setCellValue ($ letter, $ data); // fill value return $ this;} private function getColumnDimension ($ letter, $ data) {if (@ $ data) $ this-> objExcel-> getActiveSheet ()-> getColumnDimension ($ letter )-> SetWidth ($ data); // set the return $ this;} private function setName ($ letter, $ data) {if (@ $ data) $ this-> objExcel-> getActiveSheet ()-> getStyle ($ letter)-> getFont ()-> setName ($ data); // set the font return $ this ;} private function setSize ($ letter, $ data) {if (@ $ data) $ this-> objExcel-> getActiveSheet ()-> getStyle ($ letter)-> getFont () -> setSize ($ data); // set the font size return $ this;} private function background ($ letter, $ data) {if (@ $ data) {$ th Is-> objExcel-> getActiveSheet ()-> getStyle ($ letter)-> getFill ()-> getStartColor () -> setARGB ($ this-> backColor ($ data); $ this-> objExcel-> getActiveSheet ()-> getStyle ($ letter)-> getFill () -> setFillType (PHPExcel_Style_Fill: FILL_SOLID); // sets the background color style. if there is no style, the background color is not displayed .} Return $ this;} private function textColor ($ letter, $ data) {if (@ $ data) {$ this-> objExcel-> getActiveSheet ()-> getStyle ($ letter) -> getFont ()-> getColor ()-> setARGB ($ data); // font color} return $ this;} private function setBold ($ letter, $ data) {if (@ $ data) {$ this-> objExcel-> getActiveSheet ()-> getStyle ($ letter)-> getFont ()-> setBold (true ); // bold} return $ this;} private function setUnderline ($ letter, $ data) {if (@ $ data) {$ this-> objExcel-> GetActiveSheet ()-> getStyle ($ letter)-> getFont ()-> setUnderline (PHPExcel_Style_Font: UNDERLINE_SINGLE); // underline} return $ this ;} private function border ($ letter, $ data) {if (@ $ data) {$ styleThinBlackBorderOutline = array ('borders '=> array ('outline' => array ('style' => PHPExcel_Style_Border: BORDER_THIN, // set the border style 'color' => array ('Arg' => $ data), // set the border color ),),); $ this-> objExcel-> getActiveSheet ()-> GetStyle ($ letter)-> applyFromArray ($ styleThinBlackBorderOutline);} return $ this;}/** merge */private function mergeCells ($ letters, $ hang, $ direction, $ merge) {$ merge = $ merge-1; if ($ merge> 0 & $ direction) {// print_r ($ this-> letters ); $ l = array_flip ($ this-> letters); $ ln = $ l [$ letters]; switch ($ direction) {case 'left ': $ signal = $ this-> letters [($ ln-$ merge)]. $ hang. ':'. $ letters. $ hang; break; case 'right': $ signal = $ l Etters. $ hang. ':'. $ this-> letters [($ ln + $ merge)]. $ hang; break; case 'Up': $ signal = $ letters. ($ hang-$ merge ). ':'. $ letters. $ hang; break; case 'low': $ signal = $ letters. $ hang. ':'. $ letters. ($ hang + $ merge); break; default: $ signal = '';} if ($ signal) {$ this-> objExcel-> getActiveSheet () -> mergeCells ($ signal) ;}return $ this;}/** vertical center */private function setVertical ($ letter, $ data) {if ($ data) {$ this-> objExcel-> getActiveSheet ()-> GetStyle ($ letter)-> getAlignment ()-> setVertical (PHPExcel_Style_Alignment: VERTICAL_CENTER); $ this-> objExcel-> getActiveSheet ()-> getStyle ($ letter) -> getAlignment ()-> setHorizontal (PHPExcel_Style_Alignment: HORIZONTAL_CENTER);} return $ this;}/** set color */private function backColor ($ color) {$ array = array ('black' => 'ff000000', // System Colour #1-black 'white' => 'ffffffff ', // System Colour #2-White 'red' => 'fff F000000', // System Colour #3-Red 'green' => 'ff00ff00', // System Colour #4-green' blue '=> 'ff0000ff ', // System Colour #5-Blue 'yellow' => 'ffffff00', // System Colour #6-yellow 'Magenta '=> 'ffffff00ff ', // System Colour #7-Magenta 'cya' => 'ff00ffff', // System Colour #8-cyan); if (array_key_exists ($ color, $ array )) {return $ array [$ color];} else {return false;}/** set table */private function tableContent (& $ record, & $ da Ta) {$ objExcel = $ this-> objExcel; $ letters = $ this-> letters; if (array_key_exists ('merge', $ record )) $ merge = $ record ['merge']; else $ merge = ''; $ hang = 2; foreach ($ data as $ k => $ v) {$ I = 0; $ hang ++; foreach ($ v as $ kk => $ vv) {$ this-> setCellValue ($ letters [$ I]. $ hang, $ vv); // set the content $ this-> Appoint ($ kk, $ vv, $ letters [$ I], $ hang, $ merge ); $ I ++ ;}}/ ** specify the style of the table */private function Appoint ($ kk, $ vv, $ letters, $ hang, $ merge) {if (! $ Merge) return false; if (array_key_exists ($ kk, $ merge) {$ v = $ merge [$ kk]; if (array_key_exists ('keyword', $ v )) {if (strpos ($ vv, $ v ['keyword'])>-1) {$ this-> appOintStyle ($ letters, $ hang, $ v );}} else {$ this-> appOintStyle ($ letters, $ hang, $ v) ;}} else if (array_key_exists ('all', $ merge )) {$ v = $ merge ['all']; if (array_key_exists ('keyword', $ v) {if (strpos ($ vv, $ v ['keyword'])>-1) {$ this-> appOintStyle ($ letters, $ hang, $ v );}} Else {$ this-> appOintStyle ($ letters, $ hang, $ v) ;}}/ ** ultimate style */private function appOintStyle ($ letters, $ hang, $ v) {$ this-> setCellValue ($ letters. $ hang, @ $ v ['tablename'])-> getColumnDimension ($ letters, @ $ v ['width'])-> setName ($ letters. $ hang, @ $ v ['setname'])-> setSize ($ letters. $ hang, @ $ v ['setsize'])-> background ($ letters. $ hang, @ $ v ['background'])-> textColor ($ letters. $ hang, $ this-> backColor (@ $ v ['textcolor'])-> setBold ($ Letters. $ hang, @ $ v ['bold '])-> setUnderline ($ letters. $ hang, @ $ v ['underline'])-> border ($ letters. $ hang, $ this-> backColor (@ $ v ['bordercolor'])-> mergeCells ($ letters, $ hang, @ $ v ['direction'], @ $ v ['merge'])-> setVertical ($ letters. $ hang, @ $ v ['center']);}/** should be A letter list */public function letter () {return array ('A', 'B ', 'C', 'D', 'F', 'G', 'H', 'I', 'G', 'K', 'L', 'M ', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'u', 'V', 'W ', 'H', 'y', 'z ');}/*********** * ***************** Process data, sort and delete fields */private function maniData ($ record, $ data) {if (! $ Data) return false; if (! Is_array ($ record) return false; // table style and other settings $ data = $ this-> objectToArray ($ data ); // Convert objects to arrays $ delKey = (array_key_exists ('delkey', $ record ))? $ Record ['delkey']: ''; // do you want to delete the keyword $ sort = (array_key_exists ('sort ', $ record ))? $ Record ['sort ']: ''; // whether to sort $ data = $ this-> delSort ($ data, $ delKey, $ sort); return $ data ;} /**************************** object conversion array */private function objectToArray ($ data) {if (! $ Data) return false; $ data = (array) $ data; foreach ($ data as $ k => $ v) {if (is_object ($ v) | is_array ($ v) {$ data [$ k] = (array) $ this-> objectToArray ($ v) ;}} return $ data ;} /***************************** delete the key value, and sort */private function delSort ($ data, $ delKey = '', $ sort ='') {if (! $ Data) return false; $ array = array (); foreach ($ data as $ k => $ v) {// delete a key value in the data $ delData = $ this-> delData ($ v, $ delKey ); // sort by the set key value $ sortData = $ this-> sortData ($ delData, $ sort); $ array [$ k] = $ sortData;} return $ array ;} /**************************** delete the key value */public function delData ($ data, & $ delKey) {if ($ delKey) {foreach ($ delKey as $ delVal) {if (array_key_exists ($ delVal, $ data )) // Determine whether the key value has an unset ($ data [$ delVal]); // clear the key name .}} Return $ data ;} /***************************** sort key values */public function sortData ($ data, & $ sort) {$ array = array (); if ($ sort) {if (array_key_exists ('keyname', $ sort )) {$ keyName = $ sort ['keyname']; if (array_key_exists ('reorder', $ sort) {if ($ sort ['reorder'] = 'desc ') {krsort ($ keyName);} else if ($ sort ['reorder'] = 'asc ') {ksort ($ keyName );}} foreach ($ keyName as $ vn) {$ array [$ vn] = (array_key_exists ($ vn, $ data ))? $ Data [$ vn]: '';} else {if (array_key_exists ('reorder', $ sort )) {if ($ sort ['reorder'] = 'desc') {krsort ($ data);} else if ($ sort ['reorder'] = 'asc ') {ksort ($ data) ;}$ array = $ data ;}}return $ array ;}// export and download the private function down () {if ($ this-> format = '000000'): header ('content-Type: application/vnd. openxmlformats-officedocument.spreadsheetml.sheet '); $ excel = 'excel2007'; else: header ('content-Type: application/vnd. ms-excel '); $ excel = 'excel5'; endif; header ("Content-Disposition: attachment; filename = \" $ this-> fileName \""); header ('cache-Control: max-age = 0'); $ objWriter = PHPExcel_IOFactory: createWriter ($ this-> objExcel, $ excel ); $ objWriter-> save ('php: // output ');}}

I want to use PHP to export an excel table, but the php environment is 51 *. I cannot use the PHPExcel open source class. what can I do?

Hello, in fact, the very simple problem is that the header of the page to be printed is set!

Header ("Content-type: application/vnd. ms-excel ");
Header ("Content-Disposition: filenameappsphp100.xls ");

In this way, the browser will directly download the file. Copy

The Excel file generated by PHPExcel cannot be read.

Summary php export Excel php import Excel PhpExcel instructions for use PhpExcel user manual 2009/03/06 am Method 1: features, simplicity, worry-free,

Header ("Content-type: application/vnd. ms-excel ");
Header ("Content-Disposition: attachment?filename=test_data.xls ");

$ Tx = 'header ';
Echo $ tx. "\ n ";
// The output content is as follows:
Echo "name". "\ t ";
Echo "age". "\ t ";
Echo "education". "\ t ";
Echo "\ n ";
Echo "James". "\ t ";
Echo "25". "\ t ";
Echo "undergraduate". "\ t ";
?>

Method 2: reference the small library recommended by google code (similar to Method 1, more complex)

Code.google.com/p/php-excel/downloads/list

Method 3: PHPEXCEL class library. it has powerful functions and supports win Excel2003 and Win Excel2007.

Www.codeplex.com/PHPExcel

// Set the include path of the PHPExcel class library
Set_include_path ('.'. PATH_SEPARATOR.
'D: \ Zeal \ PHP_LIBS '. PATH_SEPARATOR.
Get_include_path ());

/**
* The following is an example. the optional methods are different for rows starting //.
* Open the comment of the corresponding row.
* If Excel5 is used, the output content should be GBK encoded.
*/
Require_once 'phpexcel. php ';

// Uncomment
/// Require_once 'phpexcel/Writer/Excel5.ph ...... the remaining full text>

Http://www.bkjia.com/PHPjc/884175.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/884175.htmlTechArticlephp based on phpexcel export excel class, phpexcelexcel recently to write a project, because the project to export too many excel, so write a class is easy to use. ? Php/** @ usage. * Introduce the class library...

</Table>Put it directly in the Excel table!For more information, see the php100 tutorial. Php100 has a video tutorial on this issue.: Www.php100.com/..0.html

Content </td>