PHP Phpexcel-based Export Excel class, phpexcelexcel_php tutorial

PHP based on phpexcel production of the export Excel class, Phpexcelexcel

Recently, to write a project, because there are too many exported excel in the project, writing a class is easy to use.

 fileName = ' file name ';//Set file name, default timestamp * $excel->format = ' 2007 ';//file type, default = 2007, other excel5* $record = Array (' Delkey ' = Array (' id ', ' addtime ', ' status '),//If there are columns in the data $data that do not need to be displayed, you can describe them here. Delete the key values that do not need to be exported ' sort ' =>array (' KeyName ' =>array (' subjectname ', ' flag '),//Sort by keyName column, if not present. ' Reorder ' = ' desc ',//Sort by, Desc is reversed, ASC is the positive sequence. If KeyName exists, sort keyname if it does not exist, sort by the key name of the array, if reorder does not exist, or sort if sort exists, otherwise not sorted, if keyname exists then sort by setting, if not present, sort by character, If the reorder does not exist or is empty or is DES, the positive order is equal to DESC in reverse. ' Excelstyle ' =>array (' setName ' = ' Arial ',//font style ' setSize ' = ' 12 ',//font size),//table global style ' title ' =>array (' TableName ' + ' disciplines list ', ' Center ' =>true, ' direction ' = ' right ', ' merge ' = ' 2 ', ' setSize ' + ' 30 '),//title, Center vertical, direction is the merge direction. Right,left,up,down. Merge for merging several cells, setsize for font size ' data ' =>array (' tableName ' = ' account name ', ' width ' = ' + ', ' setName ' = ' Arial ', ' SetSize ' = ' + ', ' background ' = ' red ', ' textcolor ' = ' white ', ' bold ' =>true, ' underline ' =>true, ' BorderColor ' + ' cyan ', ' center ' =>true,), Array (' tableName ' = ' disciplines ', ' width ')= ' + ', ' center ' =>true),///color table: Black,white,red,green,blue,yellow,magenta,cyan),//table name TableName name, Width is the table widths, SetName is the font style, background is the background color, textcolor is the font color, bold is bold, underline is underlined, and bordercolor is the border color. ' Merge ' = Array (//' flag ' =>array (' keyword ' = ' at the beginning ', ' direction ' = ' right ', ' merge ' = ' 2 '), the key value of the merge is the key value that needs to process the data array, Keyword performs a different style operation if this keyword exists, and if keyword does not exist then all cells with a key value of flag are executed. ' All ' =>array (' width ' = ' + ', ' setName ' = ' song ', ' 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 ';/***************************** generates an Excel document. */class EXCELC {Public $format = ' 2007 ';//conversion format, default to 2007 version, other version, please enterThe number public $fileName is not 2007;//the file name defaults to a timestamp. Private $objExcel;p rivate $letters;p ublic 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 Limited");//Creator $ This->objexcel->getproperties ()->setlastmodifiedby ("Lida Line Limited");//Last modified by $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");//Keyword $this->objexcel->getproperties ()->setcategory ("Excel");//Type}//Settings 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 ');//Set table name. }/**************************** Export excel* @attr $rEcord for header and style * @attr $data for data */public function export ($record =array (), $data =array ()) {if (! $data) return false;if (!is_array ($record)) return false;//table style and other settings//processing obtained data $data = $this->manidata ($record, $data);//Get the overall style. $this->exceldata ($record, $data);//$this->objexcel->getactivesheet ()->setcellvalue (' A1 ', ' quarter '); $this->down ();//Export Download}/** processing table */private function Exceldata (& $record,& $data) {$this->attribute ();// Set the property $this->sheet ();//Set Table $this->whole ($record);//Set the overall style $this->tableheader ($record);//Set the table header. $this->tablecontent ($record, $data);//Set Table $this->exceltitle ($record, 2);//Set Caption}/** set table overall style */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 caption */private function Exceltitle ($record, $num) {$titleL = $this->letters[0]; if (!array_key_exists (' title ', $record)) return false; $this->appointstyle ($titleL, 1, $record [' title ']);} /** sets 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);//padding 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 the 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);// Sets the background color style, no style will not display 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 (' Outlin E ' = = Array (' style ' = = Phpexcel_style_border::border_thin,//Set Border style ' color ' = = Array (' ARGB ' = $dat a),//Set Border color),), $this->objexcel->getactivesheet ()->getstyle ($letter)->applyfromarray ($styleTh Inblackborderoutline);} 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 = $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;} /** 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 ' = ' FFFF0000 ',//system colour #3-red ' green ' = ' ff00ff00 ',//system C Olour #4-green ' blue ' = ' ff0000ff ',//system colour #5-blue ' yellow ' = ' FFFFFF00 ',//system colour #6-yellow ' Magen Ta ' = ' 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 + +;}}} /** Settings table specifies the 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 the alphabetical 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 '); /***************************** 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 ']: ';//Do you want to delete the keyword $sort = (array_key_exists (' sort ', $record))? $record [' Sort ']: ';//whether 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 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 data $deldata = $this->deldata ($v, $delKey);//Sort by Set key value $ SortData = $this->sortdata ($delData, $sort); $array [$k] = $sortData;} return $array;} /***************************** Delete key value */public function Deldata ($data,& $delKey) {if ($delKey) {foreach ($delKey as $ DelVal) {if (Array_key_exists ($delVal, $data))//Determines whether the key value exists unset ($data [$delVal]);//clears the key name. }}return $data;} /***************************** Key value sort */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 download Private function down () {if ($this->format = = '): 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 ');}}

Want to use PHP to export Excel table, but PHP environment is 51*, there is no way to use Phpexcel open source class, what method?

Hello, actually very simple question, is the page that will be printed to the head set to!

Header ("Content-type:application/vnd.ms-excel");
Header ("Content-disposition:filename=php100.xls");

This allows the browser to download the file directly. Put the file inside the

Phpexcel generate Excel can not read

Summarize PHP export Excel PHP Import Excel phpexcel instructions for use Phpexcel 2009/03/06 02:37 method One: Features, simple, worry,

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

$tx = ' table header ';
echo $tx. " \ n ";
The output reads as follows:
echo "Name". " \ t ";
echo "Age". " \ t ";
echo "Education". " \ t ";
echo "\ n";
echo "Zhang San". " \ t ";
echo "25". " \ t ";
echo "Undergraduate". " \ t ";
?>

Method Two: Referring to the small class library recommended in Google code (roughly the same as method one, more complex points)

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

Method Three: Phpexcel class library, powerful, support win Excel2003, win Excel2007.

Www.codeplex.com/PHPExcel

To set the include path for the Phpexcel class library
Set_include_path ('. '). Path_separator.
' D:\Zeal\PHP_LIBS '. Path_separator.
Get_include_path ());

/**
* The following are examples of use, for lines beginning with////are optional, depending on your needs
* Open the comment for the corresponding line.
* If you use Excel5, the content of the output should be GBK encoded.
*/
Require_once ' phpexcel.php ';

Uncomment
Require_once ' phpexcel/writer/excel5.ph ... Remaining full text >>

http://www.bkjia.com/PHPjc/884175.html www.bkjia.com true http://www.bkjia.com/PHPjc/884175.html techarticle PHP based on phpexcel production of the export of Excel class, phpexcelexcel recently to write a project, because the project to export too many Excel, so write a class easy to use. php/**@ method of use. * Introduction of Class library ...



</table> is placed directly in the Excel table! For more information, please refer to the php100 tutorial. PHP100 has a video tutorial on this issue. Download Address: www.php100.com/...0.html

Content </td>