PHP export Excel class based on Phpexcel, annotated and invoked method

We used to write a PHP that uses a chained operation to output data in Excel (CSV) format, http://www.111cn.net/phper/php-cy/72796.htm, Now we're going to learn to write a PHP export Excel class based on Phpexcel

The code is as follows

Copy Code

<?php /* *@ use method. * Introduce class library. * $excel = News EXCELC (); * $excel->filename = ' file name ';//Set file name, default to timestamp * $excel->format = ' 2007 '///file type, default to 2007, others to Excel5 * $record = Array ( ' Delkey ' =>array (' id ', ' addtime ', ' status '),//If there are columns in the data $data that do not need to be displayed, this can be explained here. Delete key values that do not need to be exported ' Sort ' =>array ( ' KeyName ' =>array (' subjectname ', ' flag '), sorted by KeyName column, not sorted if not present. ' Reorder ' => ' DESC ',//Sort way, DESC to reverse, ASC to positive sequence. Sort KeyName If the keyname exists, sorted by the key of the array if it does not exist, not sorted if reorder does not exist ),//Sort if sort exists sorted, otherwise not sorted, if keyname exists, sorted by set, sorted by character if not present, if reorder does not exist or is null or des then the positive sequence equals desc to reverse. ' Excelstyle ' =>array ( ' SetName ' => ' Arial ',//font style ' SetSize ' => ' 12 ',//font size ),//table global style ' title ' =>array (' tablename ' => ' discipline list ', ' Center ' =>true, ' direction ' => ' right ', ' merge ' => ' 2 ', ' SetSize ' =& gt; ' 30 '),//title, center vertical, direction for merging direction. Right,left,up,down. Merge to combine several cells, setsize for font size ' Data ' =>array ( Array (' tablename ' => ' account name ', ' width ' => ', ' SetName ' => ' song Body ', ' setSize ' => ', ' ' background ', ' the ', ' ", '", ' ", '" " TextColor ' => ' white ', ' bold ' =>true, ' underline ' =>true, ' bordercolor ' => ' cyan ', ' Center ' =>true, Array (' tablename ' => ' discipline ', ' width ' => ', ' center ' =>true),//Color table is: Black,white,red,green,blue,yellow, Magenta,cyan ),//table name TableName is the name, width is table widths, SetName is the font style, background is the background color, textcolor is the font color, bold is bold, underline is underlined, BorderColor is the border color. ' Merge ' =>array ( ' Flag ' =>array (' keyword ' => ' early ', ' direction ' => ' right ', ' merge ' => ' 2 '), the key value of the merge is the key value of the data array to be processed, Keyword performs a different style operation if the keyword exists, and executes all cells that have the key value flag if keyword does not exist. ' All ' =>array (' width ' => ', ' setname ' => ' Arial ', ' setSize ' => ', ' Background ' => ' red ', ' TextColor ' => ' White ', ' bold ' =>true, ' underline ' =>true, ' bordercolor ' => ' cyan ', ' Center ' =>true, ),// )//Export configuration * $excel->export ($record, $data);//$record for export configuration, $data data for the database, $data can be an array or an object. * * * */ $address = DirName (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 '; /**************************** * Generate an Excel document. */ Class Excelc { Public $format = ' 2007 '///conversion format, default to version 2007, other version, please enter a number that is not 2007 public $fileName;//file name defaults to timestamp. Private $objExcel; Private $letters; Public Function __construct () { $this->filename = time (); $this->filetitle = ' export data '; $this->objexcel = new Phpexcel (); $this->letters = $this->letter (); } Export Properties for Excel Private Function attribute () { $this->objexcel->getproperties ()->setcreator ("Lida Line Co., Ltd.");/create person $this->objexcel->getproperties ()->setlastmodifiedby ("Lida Line Co., Ltd.");/Last modified person $this->objexcel->getproperties ()->settitle ("Export Data");//title $this->objexcel->getproperties ()->setsubject ("Export Data")//Topic $this->objexcel->getproperties ()->setdescription ("Data Export");//Description $this->objexcel->getproperties ()->setkeywords ("Office Export");//Keywords $this->objexcel->getproperties ()->setcategory ("Excel");//Kind } Set the table (if only one sheet can ignore the function, it will be created by default.) ) Private Function sheet () { $this->objexcel->setactivesheetindex (0);//Set the current table $this->objexcel->getactivesheet ()->settitle (' Excel '),//sets the table name. } /*************************** * Export Excel * @attr $record for table headers and style settings * @attr $data for data that needs 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 Processing the data that was obtained $data = $this->manidata ($record, $data); Gets the overall style. $this->exceldata ($record, $data); $this->objexcel->getactivesheet ()->setcellvalue (' A1 ', ' quarter '); $this->down ()//Export Download } /* * Processing Forms */ Private Function Exceldata (& $record,& $data) { $this->attribute ()//Set properties $this->sheet ()//Set table $this->whole ($record);//Set the overall style $this->tableheader ($record);//Set table header. $this->tablecontent ($record, $data);//Set table $this->exceltitle ($record, 2);//Set 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 font style if (array_key_exists (' setSize ', $excelStyle)) $default->getfont ()->setsize ($excelStyle [' setSize ']);//Set Font size } /* * Set 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 width return $this; } Private Function SetName ($letter, $data) { if (@ $data) $this->objexcel->getactivesheet ()->getstyle ($letter)->getfont ()->setname ($data);//Set Font return $this; } Private Function SetSize ($letter, $data) { if (@ $data) $this->objexcel->getactivesheet ()->getstyle ($letter)->getfont ()->setsize ($data);//Set Font size return $this; } Private function background ($letter, $data) { if (@ $data) { $this->objexcel->getactivesheet ()->getstyle ($letter)->getfill ()->getstartcolor ()->setARGB ($ This->backcolor ($data)); $this->objexcel->getactivesheet ()->getstyle ($letter)->getfill ()->setfilltype (phpexcel_style_ Fill::fill_solid);/set background color style, no style will not show background color. } 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 Border style ' Color ' => array (' ARGB ' => $data),//Set border color ), ), ); $this->objexcel->getactivesheet ()->getstyle ($letter)->applyfromarray ($styleThinBlackBorderOutline) ; } return $this; } /* * Merging */ 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 = $letters. $hang. ': $this->letters[($LN + $merge)]. $hang; Break Case ' up ': $signal = $letters. ($hang-$merge). ': ' $letters $hang; Break Case ' down ': $signal = $letters. $hang. ': $letters. ($hang + $merge); Break Default $signal = '; } if ($signal) { $this->objexcel->getactivesheet ()->mergecells ($signal); } } return $this; } /* * Center Vertically */ 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 ' => ' FFFF0000 ',//System colour #3-red ' Green ' => ' ff00ff00 ',//System colour #4-green ' Blue ' => ' ff0000ff ',//System colour #5-blue ' Yellow ' => ' FFFFFF00 ',//System colour #6-yellow ' Magenta ' => ' ffff00ff ',//System colour #7-magenta ' Cyan ' => ' ff00ffff ',//System colour #8-cyan ); if (Array_key_exists ($color, $array)) { return $array [$color]; } else { return false; } } /* * Set Table */ Private Function Tablecontent (& $record,& $data) { $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 content $this->appoint ($kk, $VV, $letters [$i], $hang, $merge); $i + +; } } } /* * Set table specified style */ 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 list of letters */ 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 '); } /**************************** * Processing data, sorting and deleting 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);//object to array $delKey = (array_key_exists (' Delkey ', $record))? $record [' Delkey ']: ';//delete keyword $sort = (array_key_exists (' sort ', $record))? $record [' Sort ']: ';//whether to sort $data = $this->delsort ($data, $delKey, $sort); return $data; } /**************************** * Object to 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 key values, and sort */ Private Function Delsort ($data, $delKey = ', $sort = ') { if (! $data) return false; $array = Array (); foreach ($data as $k => $v) { Delete a key value from the data $delData = $this->deldata ($v, $delKey); Sort by set key values $sortData = $this->sortdata ($delData, $sort); $array [$k] = $sortData; } return $array; } /**************************** * Delete key values */ Public Function Deldata ($data,& $delKey) { if ($delKey) { foreach ($delKey as $delVal) { if (Array_key_exists ($delVal, $data))//Determine whether the key value exists Unset ($data [$delVal]);//clear the key name. } } return $data; } /**************************** * Key value Ordering */ 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 Downloads Private function down () { if ($this->format = = ' 2007 '): 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 '); } }