PHP 从数据库Mysql中读取数据生成excel(解决乱码问题,解决中文变问号问题)
来源:互联网 发布:淘宝发布宝贝多种口味 编辑:程序博客网 时间:2024/06/10 00:00
有时候直接用php导出excel会有乱码或者中文变成问号出现,这时候肯定就是编码的问题了。
前 后
前 后
环境
MySQL 5.0 数据库 utf8_general_ci 编码
PHP UTF8 编码
需要注意的问题
1. 从数据库读取出来的数据是否乱码?
需要注意的问题
1. 从数据库读取出来的数据是否乱码?
mysql_query( "SET NAMES 'utf8'"); //定义好如何和数据库连接的编码2. 输出为 xls 文件时候,xls 编码格式大多为 gbk or gb2312,因而要多读取的数据库中的数据有中文的要转码
$filename = mb_convert_encoding($filename, "gb2312" , "utf-8" ); 由utf8转为gb2312
$DB_Server = "localhost"; //your MySQL Server $DB_Username = "lukeyan"; //your MySQL User Name $DB_Password = "123"; //your MySQL Password $DB_DBName = ""; //your MySQL Database Name $DB_TBLName = ""; //your MySQL Table Name $sql = "Select * from $DB_TBLName limit 0,10" ; //create MySQL connection $Connect = @mysql_connect( $DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); //select database $Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); //execute query mysql_query( "SET NAMES 'utf8'"); //定义好如何和数据库连接的编码 $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); $Use_Title = 1; $now_date = date( 'm-d-Y H:i'); $title = "Dump For Table on $now_date" ; $file_type = "vnd.ms-excel"; $file_ending = "xls"; header( "Content-Type: application/$file_type" ); header( "Content-Disposition: attachment; filename=database_dump.$file_ending"); header( "Pragma: no-cache"); header( "Expires: 0"); if ($Use_Title == 1) { echo(" $title\n"); } $sep = "\t"; $tablename = "编码abc"; $tablename = mb_convert_encoding($tablename, "gb2312", "utf-8" ); for ($i = 0; $i < mysql_num_fields($result); $i++) { echo $tablename. "\t" ; } print("\n" ); //end of printing column names //start while loop to get data while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset ($row[$j])) { $filename = "编码abcd" ; $filename = mb_convert_encoding($filename, "gb2312" , "utf-8" ); 由utf8转为gb2312 $schema_insert .= $filename.$sep; } else{ $row[$j] = mb_convert_encoding($row[$j], "gb2312" , "utf-8" ); $schema_insert .= $row[$j].$sep; } } $schema_insert = str_replace($sep. "$", "" , $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/" , " " , $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; }
PHP导出excel or word 源码:http://fundisom.com/phparadise/php/databases/mySQL_to_excel
<?php /*________________________________________________________________lixlpixel PHParadise_______________________________________________________________________category : databasessnippet : mySQL to exceldownloaded : 04.25.2012 - 19:37file URL : http://www.fundisom.com/phparadise/php/databases/mySQL_to_exceldescription : export a mySQL database table to an EXCEL file.database table dump to WORD document possible also.___________________________START___COPYING___HERE__________________*/ ?><?php//EDIT YOUR MySQL Connection Info:$DB_Server = "localhost"; //your MySQL Server$DB_Username = ""; //your MySQL User Name$DB_Password = ""; //your MySQL Password$DB_DBName = ""; //your MySQL Database Name$DB_TBLName = ""; //your MySQL Table Name//$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser//as parameters in a query string, so that this code may be easily reused for//any MySQL table or any MySQL database on your server//DEFINE SQL QUERY://you can use just about ANY kind of select statement you want -//edit this to suit your needs!$sql = "Select * from $DB_TBLName";//Optional: print out title to top of Excel or Word file with Timestamp//for when file was generated://set $Use_Titel = 1 to generate title, 0 not to use title$Use_Title = 1;//define date for title: EDIT this to create the time-format you need$now_date = date('m-d-Y H:i');//define title for .doc or .xls file: EDIT this if you want$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";/*Leave the connection info below as it is:just edit the above.(Editing of code past this point recommended only for advanced users.)*///create MySQL connection$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());//select database$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());//execute query$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());//if this parameter is included ($w=1), file returned will be in word format ('.doc')//if parameter is not included, file returned will be in excel format ('.xls')if (isset($w) && ($w==1)){ $file_type = "msword"; $file_ending = "doc";}else { $file_type = "vnd.ms-excel"; $file_ending = "xls";}//header info for browser: determines file type ('.doc' or '.xls')header("Content-Type: application/$file_type");header("Content-Disposition: attachment; filename=database_dump.$file_ending");header("Pragma: no-cache");header("Expires: 0");/* Start of Formatting for Word or Excel */if (isset($w) && ($w==1)) //check for $w again{ /* FORMATTING FOR WORD DOCUMENTS ('.doc') */ //create title with timestamp: if ($Use_Title == 1) { echo("$title\n\n"); } //define separator (defines columns in excel & tabs in word) $sep = "\n"; //new line character while($row = mysql_fetch_row($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { //define field names $field_name = mysql_field_name($result,$j); //will show name of fields $schema_insert .= "$field_name:\t"; if(!isset($row[$j])) { $schema_insert .= "NULL".$sep; } elseif ($row[$j] != "") { $schema_insert .= "$row[$j]".$sep; } else { $schema_insert .= "".$sep; } } $schema_insert = str_replace($sep."$", "", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); //end of each mysql row //creates line to separate data from each MySQL table row print "\n----------------------------------------------------\n"; }}else{ /* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */ //create title with timestamp: if ($Use_Title == 1) { echo("$title\n"); } //define separator (defines columns in excel & tabs in word) $sep = "\t"; //tabbed character //start of printing column names as names of MySQL fields for ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . "\t"; } print("\n"); //end of printing column names //start while loop to get data while($row = mysql_fetch_row($result)) { //set_time_limit(60); // HaRa $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "NULL".$sep; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".$sep; else $schema_insert .= "".$sep; } $schema_insert = str_replace($sep."$", "", $schema_insert); //following fix suggested by Josue (thanks, Josue!) //this corrects output in excel when table fields contain \n or \r //these two characters are now replaced with a space $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; }}?><?php /*_____________________END___OF___THE___CODE______________________get more code from http://www.fundisom.com/phparadise/___________________________________________________________________*/ ?>
- PHP 从数据库Mysql中读取数据生成excel(解决乱码问题,解决中文变问号问题)
- PHP 从数据库Mysql中读取数据生成excel(解决乱码问题,解决中文变问号问题)
- 解决PHP写入、读取MYSQL数据库数据中文乱码问题
- Android笔记--解决使用php文件插入数据到mysql数据库的表中中文变问号的问题
- PHP从数据库导出数据到excel,无插件(解决中文以及法语乱码问题)
- 如何解决php页面读取mysql数据库中文后打印出来是问号问题
- 解决Mysql下中文乱码全是问号的问题
- java 生成EXCEL 解决中文乱码问题
- 解决MySQL中文乱码问题|PHP+MySQL
- php+mysql中文数据乱码问题的解决
- PHP读取Mysql数据库中文乱码问题
- 解决存储数据到mysql数据库中文乱码的问题
- 解决Java Web开发中Jsp存储读取MySQL数据中文乱码的问题
- 解决Java Web开发中Jsp存储读取MySQL数据中文乱码的问题
- 解决Java Web开发中Jsp存储读取MySQL数据中文乱码的问题
- 解决PHP和MYSQL中文乱码问题
- 解决php向mysql数据库插入数据乱码问题
- 完美解决MySQL数据库中文乱码问题
- 日時の書式を変換するクラス
- stl 的find_if算法用来查找容器内的符合条件的元素
- suse10 下给64位oracle打补丁
- 4字节的时间戳,3 字节的机器号,2 字节的进程 id 以及 3 字节的自增计数组成
- vb.net 或 Sqlserver 加减月份的方法
- PHP 从数据库Mysql中读取数据生成excel(解决乱码问题,解决中文变问号问题)
- 每天记录点学会的知识~1234全排列模版
- mongodb的记录
- 文字列を判断するクラス
- Android: R cannot be resolved to a variable
- Intent中putExtra()方法用法
- 菜鸟从零开始的第一个应用上线记(一)
- dfsdf
- 数字の書式を変換するクラス