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)


相关推荐

  • ANSYS ICEM CFD——网格划分基础知识

    ANSYS ICEM CFD——网格划分基础知识1、网格划分技术在使用商用CFD软件的工作中,大约有80%的时间是花费在网格划分上的,可以说网格划分能力的高低是决定工作效率的主要因素之一。结构化网格和非结构化网格的比较FLUENT软件采用非结构网络与适应性网络相结合的方式进行网络划分。与结构化网络和分块结构网络相比,非结构网络划分便于处理复杂外形的网络划分,而适应性网格则便于计算流场参数变化剧烈、梯度很大的流动,同时这种划分方式也便于网络的细化或粗化,使得网络划分更灵活、简便。FLUENT划分网格的途径1、一种是用FLUENT提供的专用网络软件

  • 数列所有公式大全_finish验证失败是什么意思

    数列所有公式大全_finish验证失败是什么意思请写一个程序,要求维护一个数列,支持以下 6 种操作:(请注意,格式栏 中的下划线 _ 表示实际输入文件中的空格)输入格式第 1 行包含两个数 N 和 M,N 表示初始时数列中数的个数,M 表示要进行的操作数目。第 2 行包含 N 个数字,描述初始时的数列。以下 M 行,每行一条命令,格式参见问题描述中的表格。输出格式对于输入数据中的 GET-SUM 和 MAX-SUM 操作,向输出文件依次打印结果,每个答案(数字)占一行。数据范围与约定你可以认为在任何时刻,数列中至少有 1 个数。输入

  • 中国大推力矢量发动机WS15 跨入 世界先进水平!

    中国大推力矢量发动机WS15 跨入 世界先进水平!

  • (深度学习)Pytorch之dropout训练

    (深度学习)Pytorch之dropout训练(深度学习)Pytorch学习笔记之dropout训练Dropout训练实现快速通道:点我直接看代码实现Dropout训练简介在深度学习中,dropout训练时我们常常会用到的一个方法——通过使用它,我们可以可以避免过拟合,并增强模型的泛化能力。通过下图可以看出,dropout训练训练阶段所有模型共享参数,测试阶段直接组装成一个整体的大网络:那么,我们在深度学习的有力工具——Pytor…

  • 使用cloudsim进行云计算仿真,至少分为哪几个步骤_模拟语音信号处理步骤

    使用cloudsim进行云计算仿真,至少分为哪几个步骤_模拟语音信号处理步骤cloud3.0当中的8个examples,给出的模拟步骤大体相似,只是在初始化当中,数量上有所变化,步骤一般如下:Stp1:   用时间和用户数,traceflag初始化cloudSim Stp2: 创建数据中心DatacenterStp3:  创建数据中心的BrokerStp4:创建 虚拟机VM,虚拟机列表被提交到brokerStp5:   创建微云clo

    2022年10月13日
  • cxfreeze打包python程序的方法说明(生成安装包,实现桌面快捷方式、删除快捷方式)

    cxfreeze打包python程序的方法说明(生成安装包,实现桌面快捷方式、删除快捷方式)一、cxfreeze基础1、cxfreeze功能python代码文件转exe方法有三种,分别是cx_freeze,py2exe,PyInstaller,这三种方式各有千秋,本人只用过py2exe和

发表回复

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

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