PHP导入excel数据到MYSQL

这里介绍一个直接将excel文件导入mysql的例子。我花了一晚上的时间测试,无论导入简繁体都不会出现乱码,非常好用。PHP-ExcelReader,下载地址:http://sourceforge.

大家好,又见面了,我是全栈君,今天给大家准备了Idea注册码。

这里介绍一个直接将excel文件导入mysql的例子。我花了一晚上的时间测试,无论导入简繁体都不会出现乱码,非常好用。
PHP-ExcelReader,下载地址: http://sourceforge.net/projects/phpexcelreader
说明: 
测试环境:MYSQL数据库采用utf8编码.导入EXCEL文档是xls格式,经过测试,xlsx 格式[excel 2007]也OK. 
文中红色标注为需要注意的地方,请替换成你配置好的数据,如数据库配置等。运行http://localost/test.php实现导入。 
以下是我贴出的详细代码,其中test.php为我写的测试文件,reader.php和oleread.inc文件是从上面提供的网址中下载的。 
1. test.php

代码如下:

<?php
require_once './includes/reader.php'; 
// ExcelFile($filename, $encoding); 
$data = new Spreadsheet_Excel_Reader(); 
// Set output Encoding. 
$data->setOutputEncoding('gbk'); 
//”data.xls”是指要导入到mysql中的excel文件 
$data->read('date.xls'); 
@ $db = mysql_connect('localhost', 'root', '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 <= $data->sheets[0]['numRows']; $i++) { 
//以下注释的for循环打印excel表数据 
/* 
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { 
echo """.$data->sheets[0]['cells'][$i][$j]."","; 
} 
echo "n"; 
*/ /* 何问起 hovertree.com */
//以下代码是将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.'<br />'; 
$res = mysql_query($sql); 
?>

包含的文件 
OLERead.php 

<?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]) << 24); if ($value>=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; return false; } $this->data = @file_get_contents($sFileName); if (!$this->data) { $this->error = 1; return false; } //echo IDENTIFIER_OLE; //echo 'start';  if (substr($this->data, 0, 8) != IDENTIFIER_OLE) { $this->error = 1; return false; } $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 < $bbdBlocks; $i++) { $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos); $pos += 4; } for ($j = 0; $j < $this->numExtensionBlocks; $j++) { $pos = ($this->extensionBlock + 1) * BIG_BLOCK_SIZE; $blocksToRead = min($this->numBigBlockDepotBlocks - $bbdBlocks, BIG_BLOCK_SIZE / 4 - 1); for ($i = $bbdBlocks; $i < $bbdBlocks + $blocksToRead; $i++) { $bigBlockDepotBlocks[$i] = GetInt4d($this->data, $pos); $pos += 4; } $bbdBlocks += $blocksToRead; if ($bbdBlocks < $this->numBigBlockDepotBlocks) { $this->extensionBlock = GetInt4d($this->data, $pos); } } /* 何问起 hovertree.com */ // var_dump($bigBlockDepotBlocks); // readBigBlockDepot  $pos = 0; $index = 0; $this->bigBlockChain = array(); for ($i = 0; $i < $this->numBigBlockDepotBlocks; $i++) { $pos = ($bigBlockDepotBlocks[$i] + 1) * BIG_BLOCK_SIZE; //echo "pos = $pos";  for ($j = 0 ; $j < BIG_BLOCK_SIZE / 4; $j++) { $this->bigBlockChain[$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; $j < BIG_BLOCK_SIZE / 4; $j++) { $this->smallBlockChain[$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 < strlen($this->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 < $nameSize ; $i++) { $name .= $d[$i]; } $name = str_replace("\x00", "", $name); $this->props[] = array ( 'name' => $name, 'type' => $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'] < SMALL_BLOCK_THRESHOLD){ // getSmallBlockStream(PropertyStorage ps)  $rootdata = $this->__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; } } } ?>

参考:http://hovertree.com/h/bjaf/to3l3tjm.htm

http://www.cnblogs.com/roucheng/p/phpmysql.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/120535.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • java输入Scanner基本用法[通俗易懂]

    java输入Scanner基本用法[通俗易懂]1.基本介绍java.util.Scanner是Java5的新特征,我们可以通过Scanner类来获取用户的输入,每个next获取输入对应的字符。Scannersc=newScanner(System.in);当我们通过Scanner类的next()与nextLine()方法获取输入的字符串,在读取前我们一般需要使用hasNext与hasNextLine判断是否还有输入的数据:next()–>hasNext() nextLine()–…

  • 浅谈 MyBatis 缓存

    浅谈 MyBatis 缓存EhCache是一个快速的、轻量级的、易于使用的、进程内的缓存。它支持read-only和read/write缓存,内存和磁盘缓存。是一个非常轻量级的缓存实现,而且从1.2之后就支持了集群,目前的最新版本是2.8。

  • 解决Windows update medic service服务禁用不了拒绝访问

    解决Windows update medic service服务禁用不了拒绝访问1、首先使用Win+R组合快捷键,打开运行对话框,然后输入命令regedit,点击下方的“确定”打开服务,如下图所示。2、在注册表设置中,找到并定位到“HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WaaSMedicSvc”右侧找到“Start”键,右键点击“修改”,将数值改为“4”3、再找到“FailureAct…

  • 霍夫曼树为何物

    霍夫曼树为何物

  • Java ListNode 链表

    JavaListNode链表基本结构基本初始化添加构造方法初始化范型写法创建与遍历链表插入节点替换节点删除节点补充说明基本结构链表是一种数据结构,由数据和指针构成,JavaListNode链表是一种由Java自定义实现的链表结构。基本初始化classListNode{//类名:Java类就是一种自定义的数据结构intval;//数据:节点数据ListNodenext;//对象:引用下一个节点对象。在Jav

  • 预测算法用java实现吗_java 数据结构与算法

    预测算法用java实现吗_java 数据结构与算法常见的预测算法有1.简易平均法,包括几何平均法、算术平均法及加权平均法;2.移动平均法,包括简单移动平均法和加权移动平均法;3,指数平滑法,包括一次指数平滑法和二次指数平滑法,三次指数平滑法;4,线性回归法,包括一元线性回归和二元线性回归,下面我一一的简单介绍一下各种方法。 4P5?.C(B4j”^5_2h  一,简易平均法,是一种简便的时间序列法。是以一定观察期的数据求得

    2022年10月31日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号