Php supports Reading and Writing excel files, and supports multiple workbooks and custom styles.

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.