后端开发|php教程
导入EXCEL,Mysql
后端开发-php教程
这里介绍一个直接将excel文件导入mysql的例子。我花了一晚上的时间测试，无论导入简繁体都不会出现乱码，非常好用。
PHP-ExcelReader,下载地址: /projects/phpexcelreader
说明：
测试环境：MYSQL数据库采用utf8编码.导入EXCEL文档是xls格式,经过测试，xlsx 格式[excel ]也OK.
文中红色标注为需要注意的地方，请替换成你配置好的数据，如数据库配置等。运行http://localost/test.php实现导入。
以下是我贴出的详细代码，其中test.php为我写的测试文件，reader.php和oleread.inc文件是从上面提供的网址中下载的。
1. test.php
代码如下:
php成绩管理系统源码下载,ubuntu20 wifi,tomcat8.0重复启动,python爬虫面试指南,韩忠康php培训视频教程学习,seo在哪买lzw
setOutputEncoding(gbk); //”data.xls”是指要导入到mysql中的excel文件 $data->read(date.xls); @ $db = mysql_connect(localhost, oot, 1234) or die("Could not connect to database.");//连接数据库 mysql_query("set names gbk\");//输出中文 mysql_select_db(wenhuaedu); //选择数据库 error_reporting(E_ALL ^ E_NOTICE); for ($i = 1; $i sheets[0][ umRows]; $i++) { //以下注释的for循环打印excel表数据 /*for ($j = 1; $j sheets[0][ umCols]; $j++) { echo """.$data->sheets[0][cells][$i][$j]."","; } echo "n"; *//* 何问起 *///以下代码是将excel表数据【3个字段】插入到mysql中，根据你的excel表字段的多少，改写以下代码吧！ $sql = "INSERT INTO test VALUES(\". $data->sheets[0][cells][$i][1].",\". $data->sheets[0][cells][$i][2].",\". $data->sheets[0][cells][$i][3].")"; echo$sql.
; $res = mysql_query($sql); ?>
视频网站源码支持vip,ubuntu定制专用应用,Python网络爬虫 框架,php isip,湖北网络seolzw
包含的文件
OLERead.php
html分类源码,ubuntu定时清理内存,铝塑板做爬虫箱,php literal,域名做seolzw
<?php define(NUM_BIG_BLOCK_DEPOT_BLOCKS_POS, 0x2c); define(SMALL_BLOCK_DEPOT_BLOCK_POS, 0x3c); define(ROOT_START_BLOCK_POS, 0x30); define(BIG_BLOCK_SIZE, 0x200); define(SMALL_BLOCK_SIZE, 0x40); define(EXTENSION_BLOCK_POS, 0x44); define(NUM_EXTENSION_BLOCK_POS, 0x48); define(PROPERTY_STORAGE_BLOCK_SIZE, 0x80); define(BIG_BLOCK_DEPOT_BLOCKS_POS, 0x4c); define(SMALL_BLOCK_THRESHOLD, 0x1000); // property storage offsets define(SIZE_OF_NAME_POS, 0x40); define(TYPE_POS, 0x42); define(START_BLOCK_POS, 0x74); define(SIZE_POS, 0x78); define(IDENTIFIER_OLE, pack("CCCCCCCC",0xd0,0xcf,0x11,0xe0,0xa1,0xb1,0x1a,0xe1)); //echo ROOT_START_BLOCK_POS = .ROOT_START_BLOCK_POS."\n"; //echo bin2hex($data[ROOT_START_BLOCK_POS])."\n"; //echo "a="; //echo $data[ROOT_START_BLOCK_POS]; //function log function GetInt4d($data, $pos) { $value = ord($data[$pos]) | (ord($data[$pos+1]) << 8) | (ord($data[$pos+2]) << 16) | (ord($data[$pos+3]) <=4294967294) { $value=-2; } return$value; } class OLERead { var$data = \; function OLERead(){ } function read($sFileName){ // check if file exist and is readable (Darko Miljanovic) if(!is_readable($sFileName)) { $this->error = 1; returnfalse; } $this->data = @file_get_contents($sFileName); if (!$this->data) { $this->error = 1; returnfalse; } //echo IDENTIFIER_OLE; //echo start; if (substr($this->data, 0, 8) != IDENTIFIER_OLE) { $this->error = 1; returnfalse; } $this->numBigBlockDepotBlocks = GetInt4d($this->data, NUM_BIG_BLOCK_DEPOT_BLOCKS_POS); $this->sbdStartBlock = GetInt4d($this->data, SMALL_BLOCK_DEPOT_BLOCK_POS); $this->rootStartBlock = GetInt4d($this->data, ROOT_START_BLOCK_POS); $this->extensionBlock = GetInt4d($this->data, EXTENSION_BLOCK_POS); $this->numExtensionBlocks = GetInt4d($this->data, NUM_EXTENSION_BLOCK_POS); /*echo $this->numBigBlockDepotBlocks." "; echo $this->sbdStartBlock." "; echo $this->rootStartBlock." "; echo $this->extensionBlock." "; echo $this->numExtensionBlocks." "; *///echo "sbdStartBlock = $this->sbdStartBlock\n"; $bigBlockDepotBlocks = array(); $pos = BIG_BLOCK_DEPOT_BLOCKS_POS; // echo "pos = $pos"; $bbdBlocks = $this->numBigBlockDepotBlocks; if ($this->numExtensionBlocks != 0) { $bbdBlocks = (BIG_BLOCK_SIZE - BIG_BLOCK_DEPOT_BLOCKS_POS)/4; } for ($i = 0; $i data, $pos); $pos += 4; } for ($j = 0; $j numExtensionBlocks; $j++) { $pos = ($this->extensionBlock + 1) * BIG_BLOCK_SIZE; $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, BIG_BLOCK_SIZE / 4 - 1); for ($i = $bbdBlocks; $i data, $pos); $pos += 4; } $bbdBlocks += $blocksToRead; if ($bbdBlocks numBigBlockDepotBlocks) { $this->extensionBlock = GetInt4d($this->data, $pos); } } /* 何问起 */// var_dump($bigBlockDepotBlocks); // readBigBlockDepot $pos = 0; $index = 0; $this->bigBlockChain = array(); for ($i = 0; $i numBigBlockDepotBlocks; $i++) { $pos = ($bigBlockDepotBlocks[$i] + 1) * BIG_BLOCK_SIZE; //echo "pos = $pos"; for ($j = 0 ; $jbigBlockChain[$index] = GetInt4d($this->data, $pos); $pos += 4 ; $index++; } } //var_dump($this->bigBlockChain); //echo =====2; // readSmallBlockDepot(); $pos = 0; $index = 0; $sbdBlock = $this->sbdStartBlock; $this->smallBlockChain = array(); while ($sbdBlock != -2) { $pos = ($sbdBlock + 1) * BIG_BLOCK_SIZE; for ($j = 0; $jsmallBlockChain[$index] = GetInt4d($this->data, $pos); $pos += 4; $index++; } $sbdBlock = $this->bigBlockChain[$sbdBlock]; } // readData(rootStartBlock) $block = $this->rootStartBlock; $pos = 0; $this->entry = $this->__readData($block); /*while ($block != -2) { $pos = ($block + 1) * BIG_BLOCK_SIZE; $this->entry = $this->entry.substr($this->data, $pos, BIG_BLOCK_SIZE); $block = $this->bigBlockChain[$block]; } *///echo ===.$this->entry."==="; $this->__readPropertySets(); } function __readData($bl) { $block = $bl; $pos = 0; $data = \; while ($block != -2) { $pos = ($block + 1) * BIG_BLOCK_SIZE; $data = $data.substr($this->data, $pos, BIG_BLOCK_SIZE); //echo "pos = $pos data=$data\n"; $block = $this->bigBlockChain[$block]; } return$data; } function __readPropertySets(){ $offset = 0; //var_dump($this->entry); while ($offset entry)) { $d = substr($this->entry, $offset, PROPERTY_STORAGE_BLOCK_SIZE); $nameSize = ord($d[SIZE_OF_NAME_POS]) | (ord($d[SIZE_OF_NAME_POS+1]) << 8); $type = ord($d[TYPE_POS]); //$maxBlock = strlen($d) / BIG_BLOCK_SIZE - 1; $startBlock = GetInt4d($d, START_BLOCK_POS); $size = GetInt4d($d, SIZE_POS); $name = \; for ($i = 0; $i props[] = array ( ame => $name, ype => $type, startBlock => $startBlock, size => $size); if (($name == "Workbook") || ($name == "Book")) { $this->wrkbook = count($this->props) - 1; } if ($name == "Root Entry") { $this->rootentry = count($this->props) - 1; } //echo "name ==$name=\n"; $offset += PROPERTY_STORAGE_BLOCK_SIZE; } } function getWorkBook(){ if ($this->props[$this->wrkbook][size]__readData($this->props[$this->rootentry][startBlock]); $streamData = \; $block = $this->props[$this->wrkbook][startBlock]; //$count = 0; $pos = 0; while ($block != -2) { $pos = $block * SMALL_BLOCK_SIZE; $streamData .= substr($rootdata, $pos, SMALL_BLOCK_SIZE); $block = $this->smallBlockChain[$block]; } return$streamData; }else{ $numBlocks = $this->props[$this->wrkbook][size] / BIG_BLOCK_SIZE; if ($this->props[$this->wrkbook][size] % BIG_BLOCK_SIZE != 0) { $numBlocks++; } if ($numBlocks == 0) return \; //echo "numBlocks = $numBlocks\n"; //byte[] streamData = new byte[numBlocks * BIG_BLOCK_SIZE]; //print_r($this->wrkbook); $streamData = \; $block = $this->props[$this->wrkbook][startBlock]; //$count = 0; $pos = 0; //echo "block = $block"; while ($block != -2) { $pos = ($block + 1) * BIG_BLOCK_SIZE; $streamData .= substr($this->data, $pos, BIG_BLOCK_SIZE); $block = $this->bigBlockChain[$block]; } //echo stream.$streamData; return$streamData; } } } ?>
参考：/h/bjaf/to3l3tjm.htm
/roucheng/p/phpmysql.html
以上就介绍了PHP导入excel数据到MYSQL，包括了导入EXCEL,Mysql方面的内容，希望对PHP教学有兴趣的朋友有所帮助。
如果觉得《PHP导入excel数据到MYSQL mysql导入excel文件 java excel导入mysql mysql 导入excel》对你有帮助，请点赞、收藏，并留下你的观点哦！
