Php supports Reading and Writing excel files, and supports multiple workbooks and custom styles.
1.
[Code][PHP] code
2.
[Code][PHP] code
IsHeader) $ this-> _ line [] = $ this-> getLine ($ array); $ this-> isHeader = true ;} /*** @ desc add custom character conversion or filter function * @ param unknown_type $ functionName callback function name */public function addConvert ($ functionName = "convert ") {$ this-> convert = $ functionName;} protected function getLine (array $ array, $ sheet = 'sheet1') {$ _ temp = ""; $ _ count = sizeof ($ array); $ index = 0; foreach ($ array as $ value): $ _ temp. = $ this-> convert ($ value); if ($ index = $ _ count-1): $ _ temp. = "\ t \ n"; else: $ _ temp. = "\ t"; endif; $ index ++; endforeach; return $ _ temp;}/*** @ desc add an excel row, if no header is set, the first element of the array is used as the header * @ param array $ array * @ param unknown_type $ sheet */public function addRow (array $ array, $ sheet = "sheet1") {$ this-> _ line [] = $ this-> getLine ($ array, $ sheet); return $ this-> _ line ;} /*** @ desc nested add an excel row, if no header is set, the first element of the array is used as the header * @ param array $ array * @ param unknown_type $ sheet */public function addRows (array $ array, $ sheet = "sheet1") {foreach ($ array as $ value): if (is_array ($ value): $ this-> addRow ($ value); else: $ this-> addRow ($ array); endif; endforeach;}/*** @ desc add a workbook, currently, V1.0 * @ deprecated * @ param array $ array */public function addSheet ($ sheet) {return;} is not supported ;} /*** @ desc returns the number of rows in the excel table */public function getRows () {return sizeof ($ this-> _ line );} /*** @ desc returns the excel content */public function getBody () {return $ this-> _ line ;} /*** @ desc export excel file * @ param unknown_type $ fileName export file name */public function export ($ fileName = "excel") {header ("Content-Type: application/vnd. ms-excel; "); header (" Content-Disposition: filename={filename=.xls "); for ($ index = 0; $ index
_ Line); $ index ++): echo $ this-> _ line [$ index]; endfor ;} /*** @ desc user-defined encoding conversion and data filtering function * @ param unknown_type $ str */protected function convert ($ str) {if (function_exists ($ this-> convert): return call_user_func ($ this-> convert, $ str); else: return $ str; endif ;} /*** @ desc sets table content * @ param unknown_type $ array */public function setBody ($ array) {$ this-> _ line = $ array ;} /*** @ desc release resource */public function release () {unset ($ This-> _ line);} public function import ($ fileName, $ convert_callback_function = null) {}}?>
3.
[Code][PHP] code
\ N
"; Private $ documentInfo ="
{Author}
{Time}
{Company}
{Version}
"; Private $ footer ="
"; Private $ align_left =""; Private $ align_center =""; Private $ align_right =""; Private $ align_bold =""; Private $ align_default =""; Private $ charset = CHARSET; private $ convert =" convert "; private static $ pre_workBook = NULL; private $ _ line = NULL; private $ _ column = NULL; private $ _ columnType = NULL; private $ _ styles = NULL; private $ _ style = NULL; private $ _ title = NULL; private $ _ align = "Left "; private $ defaultHeight = 13.5; private $ defaultWidth = 54; private $ _ sheets = NULL; private $ _ heads = NULL; /*** @ desc constructor PHP5.X * @ param string $ charset character encoding */public function _ construct ($ charset = 'utf-8 ') {$ this-> charset = $ charset ;} /*** @ desc constructor PHP4.X * @ param string $ charset character encoding */public function XmlExcel ($ charset = 'utf-8 ') {$ this-> charset = $ charset;}/*** @ desc destructor */public function _ destruct () {}/*** @ desc release available resources * @ return null */public function release () {unset ($ this-> _ line, $ this-> _ column, $ this-> _ heads, $ this-> _ sheets, $ this-> _ styles, $ this-> _ style, $ this-> _ title, self :: $ pre_workBook);}/*** @ desc array row conversion function * @ param array $ array */protected function getLine (array $ array) {$ _ temp ="
"; Foreach ($ array as $ key => $ val): # read the specified data type. the default value is String $ _ type =! Empty ($ this-> _ columnType) & isset ($ this-> _ columnType )?! Empty ($ this-> _ columnType [$ key]) & isset ($ this-> _ columnType )? $ This-> _ columnType [$ key]: "String": "String"; $ _ temp. ="
{$ This-> convert ($ val )}
"; Endforeach; $ _ temp. ="
"; Return $ _ temp;}/*** @ desc add table header, the default first array will be used as the header * @ param array $ array * @ param string $ sheet worksheet name * @ exception $ array cannot be blank */public function addHead (array $ array, $ sheet = "sheet1") {$ this-> _ line [$ sheet] [0] = $ this-> getLine ($ array ); $ this-> _ title [$ sheet] ['width'] = count ($ array)-1; $ this-> _ sheets [] = $ sheet; $ this-> _ heads [$ sheet] [0] = $ array ;} /*** @ desc add row * @ param array $ array * @ param string $ sheet */publ Ic function addRow (array $ array, $ sheet = "sheet1", $ isErrorReport = true) {if ($ isErrorReport): if (empty ($ array) |! Isset ($ array) | count ($ array) = 0): exit ("data can't Null'"); else: $ this-> _ line [$ sheet] [] = $ this-> getLine ($ array); endif; else: $ this-> _ line [$ sheet] [] = $ this-> getLine ($ array); endif ;} /*** @ desc sets the workbook header object * @ param $ head header data * @ param $ sheet workbook name */public function setSheetHead (array $ head, $ sheet = "Sheet1") {$ this-> _ line [$ sheet] [] = $ this-> getLine ($ head );} /*** @ desc add multi-row support nested array * @ param array $ array * @ param unknown_type $ sheet */public function addRows (array $ array, $ sheet = "Sheet1") {foreach ($ array as $ value): if (is_array ($ value): $ this-> addRow ($ value, $ sheet ); else: $ this-> addRow ($ array, $ sheet); endif; endforeach ;} /*** @ desc get the column width of the created workbook * @ param @ sheet worksheet name */public function getColumnLength ($ sheet = "Sheet1 ") {return $ this-> _ title [$ sheet] ['width'];} /*** @ desc add a working thin ** @ param unknown_type $ sheet */public function addSheet ($ sheet, $ array = array ()) {$ this-> _ line [$ sheet] [] = $ array ;} /*** @ desc add the title * @ param string $ str Title * @ param string $ sheet name */public function addTitle ($ str, $ sheet = "Sheet1 ") {$ str = $ this-> convert ($ str); $ this-> _ title [$ sheet] ['title'] ="
{$ Str}
";}/*** @ Desc excel export * @ param string $ fileName exported file name */public function export ($ fileName =" excel ", $ isConvert = false) {if ($ isConvert): $ fileName = $ this-> getConvertString ($ fileName); endif; header ("Content-Type: application/vnd. ms-excel; charset = ". $ this-> charset); header ("Content-Disposition: attachment; filename = \" Invalid parameter filename=.xls \ ""); echo stripslashes (sprintf ($ this-> header, $ this-> charset); echo str_replace ("{company}", "xiao meng online", str_replace ("{time}", date ("Y-m-dH: i: s ", time (), str_replace (" {author} "," mengdejun ", str_replace (" {version} ", VERSION, $ this-> documentInfo); echo"
"; Echo stripslashes (sprintf ($ this-> align_default, $ this-> _ align); echo $ this-> align_left; echo $ this-> align_right; echo $ this-> align_center; echo $ this-> align_bold; echo"
"; $ _ HasData = count ($ this-> _ line) = 0? False: true; if ($ _ hasData): # if there is data, parse the array object to the excel table foreach ($ this-> _ line as $ key => $ value): echo"
Convert ($ key)} \ ">
"; Endforeach; endif; # table title if (! Empty ($ this-> _ title [$ key] ['title']): echo str_replace ("{num }", $ this-> _ title [$ key] ['width'], $ this-> _ title [$ key] ['title']); endif; # cell foreach ($ value as $ _ v): echo $ _ v; endforeach; echo"
DefaultWidth} \ "ss: DefaultRowHeight = \" {$ this-> defaultHeight} \ "> "; # if (isset ($ this-> _ column [$ key] ['style _ width']): foreach ($ this-> _ column [$ key] ['style _ width'] as $ s_key => $ s_value): echo"
"; Endforeach; # load the standard workbook (three workbooks by default) $ length = count ($ this-> _ line); while ($ length <3): $ length ++; echo"
"; Endwhile; else: # no data, add the default workbook and data support (error handling: file reading failed) for ($ index = 1; $ index <= 3; $ index ++): echo"
"; Endfor; endif; echo $ this-> footer;}/*** @ desc excel import function, note that the name of the function must be non-Chinese * @ param unknown_type $ File imported * @ param unknown_type $ convert_callback_function the callback function supports encoding conversion, return the converted string * @ return three-dimensional array, corresponding to the working thin/row/cell */public function import ($ fileName, $ convert_callback_function = null) {$ xls = simplexml_load_file ($ fileName); $ is_convert =! Empty ($ convert_callback_function) & function_exists ($ convert_callback_function); $ index = 0; $ _ ra = array (); foreach ($ xls-> Worksheet as $ worksheet ): # cyclic working thin $ index_ I = 1; foreach ($ worksheet-> Table-> Row as $ cells): # loop Row if ($ index_ I! = 1): foreach ($ cells as $ cell): # loop cell $ _ ra [$ index] [$ index_ I] [] = $ is_convert? Call_user_func ($ convert_callback_function, $ cell-> Data): $ cell-> Data; endforeach; endif; $ index_ I ++; endforeach; $ index ++; endforeach; return $ _ ra;}/*** @ desc sets the character encoding * @ param string $ charset sets the encoding of the exported file */public function setCharset ($ charset = "GBK ") {$ this-> charset = $ charset;}/*** sets the array (1 => asd) repeated setting of this value will overwrite the result of the previous operation * @ param string $ sheet working thin name * @ param array $ array column array */publi C function setColumnWidth ($ sheet = "sheet1", $ array) {if (! Empty ($ this-> _ column [$ sheet] ['style _ width']) & isset ($ this-> _ column [$ sheet] ['style _ width']): unset ($ this-> _ column [$ sheet] ['style _ width']); endif; $ this-> _ column [$ sheet] ['style _ width'] = $ array ;} /*** @ desc set the width of all columns in the working thin State * @ param array $ array column width */public function setAllColumnWidth (array $ array) {$ _ temp = $ this-> getAllSheetNames (); foreach ($ _ temp as $ value): $ this-> setColumnWidth ($ value, $ array); endforeach ;} /** * @ Desc sets the default row height * @ param integer $ height */public function setDefaultRowHeight ($ height = "54") {$ this-> defaultHeight = $ height ;} /*** set the character encoding conversion function (callback function) * @ param string $ convert set the default name of the conversion function to convert */public function addConvert ($ convert = "convert ") {$ this-> convert = $ convert;}/*** @ desc internal callback function, converting character encoding * @ param unknown_type $ str */protected function convert ($ str) {if (function_exists ($ this-> convert): return Call_user_func ($ this-> convert, $ str); else: return $ str; endif ;} /*** get the number of workbooks * @ param int $ sheet get the number of workbooks * @ return integer */public function getSheets () {return sizeof ($ this-> _ line );} /*** obtain the number of rows in a workbook * @ param String $ sheet worksheet name * @ return integer */public function getRows ($ sheet) {return sizeof ($ this-> _ line [$ sheet]);} /*** @ desc get the header information of the specified workbook * @ param string $ sheet worksheet name */public function getHead ($ sheet) {Return $ this-> _ heads [$ sheet] [0];} /*** @ desc set the default row height * @ param integer $ defaultHeight The Row's default height does not have the default value */public function setDefaultHeight ($ defaultHeight) {$ this-> defaultHeight = $ defaultHeight ;} /*** @ desc set the default column width * @ param integer $ defaultWidth the default width of the column does not have the default value */public function setDefaultWidth ($ defaultWidth) {$ this-> defaultWidth = $ defaultWidth;}/*** @ desc current working thin number of available lines */public function currentSheetsLength () {Return sizeof ($ this-> _ line) + 1 ;} /*** @ desc sets the default Center mode. * @ param string $ _ align optional values: Left, center, Right) */public function setDefaultAlign ($ _ align) {$ this-> _ align = ucfirst ($ _ align);}/*** @ desc automatically creates a workbook, supports automatic volume splitting. this method conflicts with addHead. do not call addHead when using this method, otherwise, a blank workbook * @ param array $ head header * @ param array $ data * @ param int $ pageSize indicates that the number of page rows is 60000 by default, excel supports a maximum of 65536 * @ param string $ defanam Nam E. work thin name. The Workbook cannot be renamed again */public function addPageRow (array $ head, array $ data, $ pageSize = 60000, $ defaultName = "Sheet") {if (! Isset ($ defaultName) | $ defaultName = "Sheet") $ defaultName = "Sheet ". ($ this-> getSheets () + 1); if (empty (self: $ pre_workBook): self: $ pre_workBook = $ defaultName; if (! Isset ($ this-> _ heads [self: $ pre_workBook] [0]) $ this-> addHead ($ head, self: $ pre_workBook ); $ this-> addRow ($ data, self: $ pre_workBook); else: if ($ this-> getRows (self ::$ pre_workBook)> = ($ pageSize + 1): $ this-> addHead ($ head, $ defaultName); $ this-> addRow ($ data, $ defaultName); self :: $ pre_workBook = $ defaultName; else: $ this-> addRow ($ data, self: $ pre_workBook); endif ;} /*** @ desc returns all the thin instance names * @ param null */public function GetAllSheetNames () {return $ this-> _ sheets;}/*** @ desc sets all table titles (volume shards) to merge all columns in the current Workbook by default, center (bold) this method must be called when the workbook exists. * @ param string $ title */public function setAllTitle ($ title) {$ _ temp = $ this-> getAllSheetNames (); foreach ($ _ temp as $ value ): $ this-> addTitle ($ title, $ value); endforeach ;} /*** @ desc encoding conversion function * @ param string $ str conversion string * @ param string $ source_code original encoding default UTF-8 * @ param string $ target_code target encoding Default GBK */protected function getConvertString ($ str, $ source_code = 'utf-8', $ target_code = 'gbk') {return! Empty ($ str) & is_string ($ str )? Iconv ($ source_code, $ target_code, $ str): $ str;}/*** @ desc print debugging information * @ param null */public function debug ($ out = true) {if ($ out): var_dump ($ this-> _ line); else: return $ this-> _ line; endif ;} /*** @ desc working thin name suffix call this method to generate a globally unique working thin name * @ param $ name custom working thin name */public function uniqueName ($ name) {$ size = $ this-> getSheets (); if ($ size = 0) return $ name; else return $ name. $ size;}/** set the data type of the unit lattice. to complete the data type before filling in the data, refer to the excel * @ param $ _ of the specified version _ ColumnType the $ _ columnType to set the key-value pair array */public function set_columnType ($ _ columnType) {$ this-> _ columnType =$ _ columnType ;}}?>
4.
[Code][PHP] code
setDefaultWidth(80);$xls->setDefaultAlign("center");$xls->setDefaultHeight(18);$xls->addHead(array("title1","title2","title3","title4","title5","title6"),"demo1");for($ind=0;$ind<10;$ind++):$xls->addRow(array($ind,$ind,$ind,$ind,$ind,$ind),"demo1");endfor;$xls->export("demo1");?>
5.
[Code][PHP] code
setDefaultWidth(80);$xls->setDefaultAlign("center");$xls->setDefaultHeight(18);for($ind=0;$ind<50;$ind++):$xls->addPageRow(array("title1","title2","title3","title4","title5","title6"),array($ind,$ind,$ind,$ind,$ind,$ind),10,$xls->uniqueName("demo"));endfor;$xls->export("demo2");?>
6.
[Image]E1.jpg
7.
[Image]E2.jpg