Mysql数据库导出替Excel(ODBC驱动)
程序员文章站
2022-03-08 08:47:14
...
Mysql数据库导出为Excel(ODBC驱动)
- PHP code
currDir = dirname(__FILE__);
}
/* 获取Mysql的列名称 */
public function getSrcColumn(){
$handle = @mysql_connect($this->host,$this->username,$this->pwd);
$this->srcColumn = array();
if(is_bool($handle)){
echo "====**====
";
echo "Mysql连接出错.
文件：".(__FILE__)."
行号：".(__LINE__)."
错误：".mysql_error();
echo "
====**====
";
exit(0);
}
$ret = @mysql_select_db($this->dbName,$handle);
if($handle == FALSE){
echo "====**====
";
echo "Mysql选择数据库出错.
文件：".(__FILE__)."
行号：".(__LINE__)."
错误：".mysql_error();
echo "
====**====
";
exit(0);
}
$ret = @mysql_query('describe '.$this->tbName,$handle);
if(is_bool($ret)){
echo "====**====
";
echo "Mysql语句执行出错.
文件：".(__FILE__)."
行号：".(__LINE__)."
错误：".mysql_error();
echo "
====**====
";
exit(0);
}
while(($data = mysql_fetch_array($ret)) != FALSE){
array_push($this->srcColumn,$data['Field']);
}
mysql_free_result($ret);
mysql_close($handle);
return $this->srcColumn;
}
/* 获取指定的Mysql数据库表中的内容 */
public function getSrcData(){
$handle = @mysql_connect($this->host,$this->username,$this->pwd);
$this->srcData = array();
if(is_bool($handle)){
echo "====**====
";
echo "Mysql连接出错.
文件：".(__FILE__)."
行号：".(__LINE__)."
错误：".mysql_error();
echo "
====**====
";
exit(0);
}
$ret = @mysql_select_db($this->dbName,$handle);
if($handle == FALSE){
echo "====**====
";
echo "Mysql选择数据库出错.
文件：".(__FILE__)."
行号：".(__LINE__)."
错误：".mysql_error();
echo "
====**====
";
exit(0);
}
$ret = @mysql_query('select * from '.$this->tbName,$handle);
if(is_bool($ret)){
echo "====**====
";
echo "Mysql语句执行出错.
文件：".(__FILE__)."
行号：".(__LINE__)."
错误：".mysql_error();
echo "
====**====
";
exit(0);
}
while(($data = mysql_fetch_array($ret)) != FALSE){
array_push($this->srcData,$data);
}
mysql_free_result($ret);
mysql_close($handle);
return $this->srcData;
}
/* 主力部分 将Mysql数据库中的内容导出为Excel文件 */
public function m2Xls(){
$handle = odbc_connect("Driver={MicroSoft Excel Driver (*.xls)};READONLY=false;CREATE_DB=\"".$this->xlsName."\";Dbq=".$this->xlsName,"","");
$sql = 'create table '.$this->tbName.'(';
foreach($this->srcColumn as $value){
$sql .= ($value." text,");
}
$sql = substr($sql,0,strlen($sql)-1);
$sql .= ")";
$ret = odbc_exec($handle,$sql);
//以上内容是创建一个Excel文件 并将Mysql的列转移到Excel文件中去。
foreach($this->srcData as $value){
$sql = 'insert into ['.$this->tbName.'$] values(';
for($i=0;$ihost = $host;
$this->username = $username;
$this->pwd = $pwd;
$this->dbName = $dbName;
$this->tbName = $tbName;
}
/* 设置Excel数据库的名称 */
public function setXlsName($xlsName){
$this->xlsName = $this->currDir."\\".$xlsName.".xls";
return $this->xlsName;
}
}
/*以下部分是测试功能的，可删除*/
header("Content-Type:text/html;charset=UTF-8");
$mysql2xls = new mysql2Xls();
$mysql2xls->setMysqlArgs('localhost','root','******','others','zhuifeng');
$mysql2xls->setXlsName('test');
$mysql2xls->getSrcColumn();
$mysql2xls->getSrcData();
$mysql2xls->m2Xls();
?>
声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn核实处理。
相关文章
相关视频