Home  >  Article  >  Backend Development  >  Use ExcelFileParser in PHP to process excel to obtain data (can be imported into the database in batches)_PHP tutorial

Use ExcelFileParser in PHP to process excel to obtain data (can be imported into the database in batches)_PHP tutorial

WBOY
WBOYOriginal
2016-07-21 15:35:161008browse

Copy code The code is as follows:





Excel data acquisition demonstration< ;/title> <br><meta name="Keywords" content="TODO" /> <br><meta name="Description" content="TODO"/> <br></head> <br><body> <br><div> <br><div>Excel data acquisition demonstration</div> <br><div> <br><form method="POST" action= "/Index/parse" enctype="multipart/form-data"> <br><input type="file" name="excel" value="" /> <br><input type="submit " name="submit" value="Submit" /> <br></form> <br></div> <br></div> <br></body> <br>< ;/html> <br> </div> <br><div class="codetitle"> <span style="CURSOR: pointer" onclick="doCopy('code257')"><u>Copy code </u></span> The code is as follows: </div> <div class="codebody" id="code257"> <br><?php <BR>/ **<BR>* CopyRight (c) 2009, <BR>* All rights reserved. <BR>* File name: <BR>* Summary: <BR>* <BR>* @author Saturday [url=mailto:ixqbar@ hotmail.com]ixqbar@hotmail.com[/url] <BR>* @version <BR>*/ <br><br>public function parse() <BR>{ <BR>/**<BR>* $_FILES array description<BR>* array(n) { <BR>* ["form file box name"] => array(5) { <BR>* ["name"] => Submit File name <BR>* ["type"] => Submit file type Excel as "application/vnd.ms-excel" <BR>* ["tmp_name"] => Temporary file name <BR>* ["error "] => Error (0 Success 1 File too large exceeds upload_max_filesize 2 File too large exceeds MAX_FILE 3 Incomplete upload 4 No file uploaded) <BR>* ["size"] => File size (unit: KB) <BR> * } <BR>* } <BR>*/ <BR>$return=array(0,''); <BR> /**<BR>* Determine whether to submit <BR>* is_uploaded_file (file name) is used to determine whether the specified file is uploaded using the POST method to prevent illegal submission. It is usually used together with move_upload_file to save the uploaded file to the specified path <BR>*/ <BR>if(!isset($_FILES) || !is_uploaded_file($_FILES['excel']['tmp_name'])) <BR>{ <BR>$return=array( 1,'Submission is illegal'); <BR>} <BR>//Processing<BR>if(0 == $return[0]) <BR>{ <BR>import('@.Util.ExcelParser') ; <BR>$excel=new ExcelParser($_FILES['excel']['tmp_name']); <BR>$return=$excel->main(); <br>} <br>//Output processing <br>print_r($return); <br>?> <br> </div> <br><div class="codetitle"> <span style="CURSOR: pointer" onclick="doCopy('code78230')"><u>Copy code</u></span> The code is as follows:</div> <div class="codebody" id="code78230"> <br><?php <BR>/**<BR>* CopyRight (c) 2009, <BR>* All rights reserved. <BR>* File name: excel data acquisition<BR>* Summary: <BR>* <BR>* @author Monday [url=mailto :ixqbar@hotmail.com]ixqbar@hotmail.com[/url] <BR>* @version 0.1 <BR>*/ <BR>class ExcelParser <BR>{ <BR>private $_data=array(0,''); <BR>private $ _excel_handle; <BR>private $_excel=array(); <BR>/**<BR>* Constructor <BR>* @param <string> $filename Temporary file name of uploaded file <br>*/ <br>public function __construct($filename) <br>{ <br>/**<br>* Introduce excelparser class <br>* The common method is <br>* requires path.'excelparser.php'; <br>*/ <br>import('@.Util.PHPExcelParser.excelparser','','.php'); <br>$this->_excel_handle=new ExcelFileParser(); <br>//Error getting<br>$this ->checkErrors($filename); <br>} <br>/**<br>* Error checking <br>*/ <br>private function checkErrors($filename) <br>{ <br>/**<br>* Method 1 <br>*/ <br>$error_code=$this->_excel_handle->ParseFromFile($filename); <br>/**<br>* 方法二 <br>* $file_handle = fopen($this->_filename,'rb'); <br>* $content = fread($file_handle,filesize($this->_filename)); <br>* fclose($file_handle); <br>* $error_code = $this->_excel->ParseFromString($content); <br>* unset($content,$file_handle); <br>*/ <br>switch($error_code) <br>{ <br>case 0: <br>//No error, no processing<br>break; <br>case 1: <br>$this->_data=array(1,'File reading error (Linux pay attention to read and write permissions)'); <br>break; <br>case 2: <br>$this->_data=array(1,'File too small'); <br>break; <br>case 3: <br>$this->_data =array(1,'Failed to read Excel header'); <br>break; <br>case 4: <br>$this->_data=array(1,'File reading error'); <br>break; <br>case 5: <br>$this->_data=array(1,'The file may be empty'); <br>break; <br>case 6: <br>$this-> _data=array(1,'Incomplete file'); <br>break; <br>case 7: <br>$this->_data=array(1,'Error reading data'); <br>break ; <br>case 8: <br>$this->_data=array(1,'version error'); <br>break; <br>} <br>unset($error_code); <br>} <br>/**<br>* Excel information acquisition <br>*/ <br>private function getExcelInfo() <br>{ <br>if(1==$this->_data[0])return; <br>/**<br>* Get the number of sheets <br>* Get the rows and columns corresponding to the sheet unit <br>*/ <br>$this->_excel['sheet_number']=count($this->_excel_handle->worksheet['name']); <br>for($i=0;$i< $this->_excel['sheet_number'];$i++) <br>{ <br>/**<br>* rows in columns <br>* Note: counting starts from 0 <br>*/ <br>$row=$this->_excel_handle->worksheet['data' ][$i]['max_row']; <br>$col=$this->_excel_handle->worksheet['data'][$i]['max_col']; <br>$this-> _excel['row_number'][$i]=($row==NULL)?0:++$row; <br>$this->_excel['col_number'][$i]=($col== NULL)?0:++$col; <br>unset($row,$col); <br>} <br>} <br>/**<br>* Chinese processing function <br>* @return <string> <br>*/ <br>private function uc2html($str ) <br>{ <br>$ret = ''; <br>for( $i=0; $i<strlen($str)/2; $i++ ) <BR>{ <BR>$charcode = ord( $str[$i*2])+256*ord($str[$i*2+1]); <BR>$ret .= '&#'.$charcode.';'; <BR>} <BR>return mb_convert_encoding($ret,'UTF-8','HTML-ENTITIES'); <BR>} <BR>/**<BR>* Excel data acquisition <BR>*/ <BR>private function getExcelData() <BR>{ <BR>if(1==$this->_data[0])return; <br>//Modify mark<br>$this->_data[0]=1; <br>//Get data<br> for($i=0;$i<$this->_excel['sheet_number'];$i++) <br>{ <br>/**<br>* Loop over rows <br>*/ <br>for($j=0; $j<$this->_excel['row_number'][$i];$j++) <br>{ <br>/**<br>* Alignment loop <br>*/ <br>for($k=0;$k< $this->_excel['col_number'][$i];$k++) <br>{ <br>/**<br>* array(4) { <br>* ["type"] => Type [0 character type 1 integer 2 floating point number 3 date] <br>* ["font"] => Font <br> * ["data"] => data<br>* ... <br>* } <br>*/ <br>$data=$this->_excel_handle->worksheet ['data'][$i]['cell'][$j][$k]; <br>switch($data['type']) <br>{ <br>case 0: <br>/ /Character type<br>if($this->_excel_handle->sst['unicode'][$data['data']]) <br>{ <br>//Chinese processing<br>$data[ 'data'] = $this->uc2html($this->_excel_handle->sst['data'][$data['data']]); <br>} <br>else <br>{ <br>$data['data'] = $this->_excel_handle->sst['data'][$data['data']]; <br>} <br>break; <br>case 1 : <br>//Integer<br>//TODO <br>break; <br>case 2: <br>//Floating point<br>//TODO <br>break; <br>case 3: <br>//Date<br>//TODO <br>break; <br>} <br>$this->_data[1][$i][$j][$k]=$data['data' ]; <br>unset($data); <br>} <br>} <br>} <br>} <br>/**<br>* Main function <br>* @return <array> array(identifier, content s) <br>*/ <br>public function main() <br>{ <br>//Get Excel information<br>$this->getExcelInfo(); <br>//Get Excel data<br>$this->getExcelData(); <br>return $this->_data ; <br>} <br>} <br>?> <br> </div> <p align="left"></p> <div style="display:none;"> <span id="url" itemprop="url">http://www.bkjia.com/PHPjc/322271.html</span><span id="indexUrl" itemprop="indexUrl">www.bkjia.com</span><span id="isOriginal" itemprop="isOriginal">true</span><span id="isBasedOnUrl" itemprop="isBasedOnUrl">http: //www.bkjia.com/PHPjc/322271.html</span><span id="genre" itemprop="genre">TechArticle</span><span id="description" itemprop="description">Copy the code as follows: !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" html xmlns="http://www.w3.org/199...</span> </div> <div class="art_confoot"></div></div><div class="nphpQianMsg"><div class="clear"></div></div><div class="nphpQianSheng"><span>Statement:</span><div>The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn</div></div></div><div class="nphpSytBox"><span>Previous article:<a class="dBlack" title="How to set up graphics and text in php using Visual Studio_PHP tutorial" href="http://m.php.cn/faq/309728.html">How to set up graphics and text in php using Visual Studio_PHP tutorial</a></span><span>Next article:<a class="dBlack" title="How to set up graphics and text in php using Visual Studio_PHP tutorial" href="http://m.php.cn/faq/309730.html">How to set up graphics and text in php using Visual Studio_PHP tutorial</a></span></div><div class="nphpSytBox2"><div class="nphpZbktTitle"><h2>Related articles</h2><em><a href="http://m.php.cn/article.html" class="bBlack"><i>See more</i><b></b></a></em><div class="clear"></div></div><ul class="nphpXgwzList"><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/2.html" title="All expression symbols in regular expressions (summary)" class="aBlack">All expression symbols in regular expressions (summary)</a><div class="clear"></div></li></ul></div></div><footer><div class="footer"><div class="footertop"><img src="/static/imghwm/logo.png" alt=""><p>Public welfare online PHP training,Help PHP learners grow quickly!</p></div><div class="footermid"><a href="http://m.php.cn/about/us.html">About us</a><a href="http://m.php.cn/about/disclaimer.html">Disclaimer</a><a href="http://m.php.cn/update/article_0_1.html">Sitemap</a></div><div class="footerbottom"><p> © php.cn All rights reserved </p></div></div></footer><script>isLogin = 0;</script><script type="text/javascript" src="/static/layui/layui.js"></script><script type="text/javascript" src="/static/js/global.js?4.9.47"></script></div><script src="https://vdse.bdstatic.com//search-video.v1.min.js"></script><link rel='stylesheet' id='_main-css' href='/static/css/viewer.min.css' type='text/css' media='all'/><script type='text/javascript' src='/static/js/viewer.min.js?1'></script><script type='text/javascript' src='/static/js/jquery-viewer.min.js'></script><script>jQuery.fn.wait = function (func, times, interval) { var _times = times || -1, //100次 _interval = interval || 20, //20毫秒每次 _self = this, _selector = this.selector, //选择器 _iIntervalID; //定时器id if( this.length ){ //如果已经获取到了,就直接执行函数 func && func.call(this); } else { _iIntervalID = setInterval(function() { if(!_times) { //是0就退出 clearInterval(_iIntervalID); } _times <= 0 || _times--; //如果是正数就 -- _self = $(_selector); //再次选择 if( _self.length ) { //判断是否取到 func && func.call(_self); clearInterval(_iIntervalID); } }, _interval); } return this; } $("table.syntaxhighlighter").wait(function() { $('table.syntaxhighlighter').append("<p class='cnblogs_code_footer'><span class='cnblogs_code_footer_icon'></span></p>"); }); $(document).on("click", ".cnblogs_code_footer",function(){ $(this).parents('table.syntaxhighlighter').css('display','inline-table');$(this).hide(); }); $('.nphpQianCont').viewer({navbar:true,title:false,toolbar:false,movable:false,viewed:function(){$('img').click(function(){$('.viewer-close').trigger('click');});}}); </script></body></html>