mysql - How do I export a table that contains Hindi text into Excel using php? -
this question has answer here:
i trying export hindi data excel using php. after having exported hindi data, shown in different format "????????? ????". previous code
<?php include 'config.php'; setlocale(lc_all, "en_us.utf-8"); $db_tblname = "feedback"; //mysql table name $filename = "feedback"; //file name /*******you not need edit below line*******/ //create mysql connection $sql = "select * $db_tblname"; $connect = @mysql_connect($db_server, $db_username, $db_password) or die("couldn't connect 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()); $file_ending = "xls"; //setlocale(lc_all, "en_us.utf-8"); //$result=mb_convert_encoding($result, 'iso-8859-13','utf-8'); /*******start of formatting excel*******/ //define separator (defines columns in excel & tabs in word) $sep = "\t"; //tabbed character //start of printing column names names of mysql fields ($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 data while($row = mysql_fetch_row($result)) { $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); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } //header info browser header("content-type: application/vnd-ms-excel"); header("content-disposition: attachment; filename=$filename.xls"); header("pragma: no-cache"); header("expires: 0"); ?> after doing research suggested add charset before fetching data table. , added line mysql_set_charser('utf-8'). exporting in different format णाचबूछठछतम, not getting in hindi.
thanks.
exporting utf-8 encoded data mysql excel using php
manual demonstrates how export utf-8 encoded data mysql excel. in case tried export hindi data excel works fine. can export in 2 ways
1) exporting manually ,
2) exporting programmatically using php.
1) exporting manually:
following steps exporting manually database.
1) save exported file csv or xls while exporting database
2) open excel
3) import data using data
-->import external data
--> import data
4) select file type of "csv" or "xls" , browse file
5) in import wizard change file_origin "65001 utf" (or choose correct language character identifier)
6) change delimiter comma
7) select import , finish
way special characters should show correctly.
2) exporting programmatically using php:
in php need follow 2 steps.
step 1: add mysql_set_charset(“utf8”) when before fetching data db.
ex:
mysql_set_charset("utf8"); $result = @mysql_query("select * $db_tblname",$connect) or die("couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); step 2: , add bom(byte order mark) code headers.
ex:
header("content-type: application/xls"); header("content-disposition: attachment; filename=$filename.xls"); header("pragma: no-cache"); header("expires: 0"); echo "\xef\xbb\xbf"; final code in php:
<?php include 'config.php'; $filename = "student_details"; //file name //create mysql connection $sql = "select * $db_tblname"; $connect = @mysql_connect($db_server, $db_username, $db_password) or die("couldn't connect 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_set_charset("utf8"); $result = @mysql_query($sql,$connect) or die("couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); $file_ending = "xls"; //header info browser header("content-type: application/xls"); header("content-disposition: attachment; filename=$filename.xls"); header("pragma: no-cache"); header("expires: 0"); echo "\xef\xbb\xbf"; /*******start of formatting excel*******/ //define separator (defines columns in excel & tabs in word) $sep = "\t"; //tabbed character //start of printing column names names of mysql fields ($i = 0; $i < mysql_num_fields($result); $i++) { echo mysql_field_name($result,$i) . ","; } print("\n"); //end of printing column names //start while loop data while($row = mysql_fetch_row($result)) { $schema_insert = ""; for($j=0; $j<mysql_num_fields($result);$j++) { if(!isset($row[$j])) $schema_insert .= "null".","; elseif ($row[$j] != "") $schema_insert .= "$row[$j]".","; else $schema_insert .= "".","; } $schema_insert = str_replace(","."$", "", $schema_insert); $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } ?>
Comments
Post a Comment