Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: polskie znaki przy eksporcie do excela z bazy MySQL przez php
Forum PHP.pl > Forum > Bazy danych
falent
12.07.2012, 08:44:53
Przy eksporcie danych mam krzaki zamiast 걶ć, reszta polskich liter działa bez zarzutu. Kodowanie w bd ustawione na utf8_polish_ci


Co można zrobić żeby ±ę¶ć działało?

[PHP] pobierz, plaintext 
  1. <?php
  2.  
  3. /*_________________________________________________ _______________
  4.  
  5.  
  6. lixlpixel PHParadise
  7.  
  8. __________________________________________________ _____________________
  9.  
  10. category : databases
  11.  
  12. snippet : mySQL to excel
  13.  
  14. downloaded : 05.04.2012 - 03:49
  15.  
  16. file URL : <a href="http://www.fundisom.com/phparadise/p...mySQL_to_excel" target="_blank">http://www.fundisom.com/phparadise/p...mySQL_to_excel</a>
  17.  
  18. description : export a mySQL database table to an EXCEL file.
  19. database table dump to WORD document possible also.
  20.  
  21. ___________________________START___COPYING___HERE_ _________________*/ ?>
  22.  
  23.  
  24. <?php
  25.  
  26. //EDIT YOUR MySQL Connection Info:
  27. $DB_Server = "localhost"; //your MySQL Server
  28. $DB_Username = "************"; //your MySQL User Name
  29. $DB_Password = "***************"; //your MySQL Password
  30. $DB_DBName = "***************"; //your MySQL Database Name
  31. $DB_TBLName = "aplikacja1"; //your MySQL Table Name
  32. //$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser
  33. //as parameters in a query string, so that this code may be easily reused for
  34. //any MySQL table or any MySQL database on your server
  35.  
  36. //DEFINE SQL QUERY:
  37. //you can use just about ANY kind of select statement you want -
  38. //edit this to suit your needs!
  39. $sql = "Select * from $DB_TBLName";
  40.  
  41. //Optional: print out title to top of Excel or Word file with Timestamp
  42. //for when file was generated:
  43. //set $Use_Titel = 1 to generate title, 0 not to use title
  44. $Use_Title = 1;
  45. //define date for title: EDIT this to create the time-format you need
  46. $now_date = date('m-d-Y H:i');
  47. //define title for .doc or .xls file: EDIT this if you want
  48. $title = "Szanowna Pani kierownik na pani życzenie lista aplikacji o miejsce w akademiku dnia $now_date";
  49. /*
  50.  
  51. Leave the connection info below as it is:
  52. just edit the above.
  53.  
  54. (Editing of code past this point recommended only for advanced users.)
  55. */
  56. //create MySQL connection
  57. $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
  58. or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
  59. mysql_query("SET NAMES 'utf8'");
  60. //select database
  61. $Db = @mysql_select_db($DB_DBName, $Connect)
  62. or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
  63. //execute query
  64. $result = @mysql_query($sql,$Connect)
  65. or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
  66.  
  67. //if this parameter is included ($w=1), file returned will be in word format ('.doc')
  68. //if parameter is not included, file returned will be in excel format ('.xls')
  69. if (isset($w) && ($w==1))
  70. {
  71. $file_type = "msword";
  72. $file_ending = "doc";
  73. }else {
  74. $file_type = "vnd.ms-excel";
  75. $file_ending = "xls";
  76. }
  77. //header info for browser: determines file type ('.doc' or '.xls')
  78. header("Content-Type: application/$file_type");
  79. header("Content-Disposition: attachment; filename=baza_aplikacji.$file_ending");
  80. header("Pragma: no-cache");
  81. header("Expires: 0");
  82.  
  83. /* Start of Formatting for Word or Excel */
  84.  
  85. if (isset($w) && ($w==1)) //check for $w again
  86. {
  87. /* FORMATTING FOR WORD DOCUMENTS ('.doc') */
  88. //create title with timestamp:
  89. if ($Use_Title == 1)
  90. {
  91. echo("$title\n\n");
  92. }
  93. //define separator (defines columns in excel & tabs in word)
  94. $sep = "\n"; //new line character
  95.  
  96. while($row = mysql_fetch_row($result))
  97. {
  98. //set_time_limit(60); // HaRa
  99. $schema_insert = "";
  100. for($j=0; $j<mysql_num_fields($result);$j++)
  101. {
  102. //define field names
  103. $field_name = mysql_field_name($result,$j);
  104. //will show name of fields
  105. $schema_insert .= "$field_name:\t";
  106. if(!isset($row[$j])) {
  107. $schema_insert .= "NULL".$sep;
  108. }
  109. elseif ($row[$j] != "") {
  110. $schema_insert .= "$row[$j]".$sep;
  111. }
  112. else {
  113. $schema_insert .= "".$sep;
  114. }
  115. }
  116. $schema_insert = str_replace($sep."$", "", $schema_insert);
  117. $schema_insert .= "\t";
  118. print(trim($schema_insert));
  119. //end of each mysql row
  120. //creates line to separate data from each MySQL table row
  121. print "\n----------------------------------------------------\n";
  122. }
  123. }else{
  124. /* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */
  125. //create title with timestamp:
  126. if ($Use_Title == 1)
  127. {
  128. echo("$title\n");
  129. }
  130. //define separator (defines columns in excel & tabs in word)
  131. $sep = "\t"; //tabbed character
  132.  
  133. //start of printing column names as names of MySQL fields
  134. for ($i = 0; $i < mysql_num_fields($result); $i++)
  135. {
  136. echo mysql_field_name($result,$i) . "\t";
  137. }
  138. print("\n");
  139. //end of printing column names
  140.  
  141. //start while loop to get data
  142. while($row = mysql_fetch_row($result))
  143. {
  144. //set_time_limit(60); // HaRa
  145. $schema_insert = "";
  146. for($j=0; $j<mysql_num_fields($result);$j++)
  147. {
  148. if(!isset($row[$j]))
  149. $schema_insert .= "NULL".$sep;
  150. elseif ($row[$j] != "")
  151. $schema_insert .= "$row[$j]".$sep;
  152. else
  153. $schema_insert .= "".$sep;
  154. }
  155. $schema_insert = str_replace($sep."$", "", $schema_insert);
  156. //following fix suggested by Josue (thanks, Josue!)
  157. //this corrects output in excel when table fields contain \n or \r
  158. //these two characters are now replaced with a space
  159. $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
  160. $schema_insert .= "\t";
  161. print(trim($schema_insert));
  162. print "\n";
  163. }
  164. }
  165.  
  166. ?>
  167.  
  168.  
  169. <?php /*_____________________END___OF___THE___CODE_______ _______________
  170.  
  171.  
  172. get more code from <a href="http://www.fundisom.com/phparadise/" target="_blank">http://www.fundisom.com/phparadise/</a>
  173.  
  174.  
  175. __________________________________________________ _________________*/ ?> 
[PHP] pobierz, plaintext
franki01
15.07.2012, 23:19:33
Radziłbym przenie¶ć na dział PHP.

Na górze zainicjuj ładowanie do bufora przez ob_start. W ostatniej linijce kodu pobierz wszystko z bufora i przekonwertuj kodowanie z utf-8 na windows-1250:
[PHP] pobierz, plaintext 
  1. $text = ob_get_clean();
  2. $text = iconv('UTF-8', 'CP1250//TRANSLIT', $text);
  3. echo $text;
[PHP] pobierz, plaintext
falent
16.07.2013, 22:36:18
niestety nie działa sad.gif Ma kto¶ jeszcze jaki¶ pomysł?

[PHP] pobierz, plaintext 
  1. <?php
  2.  
  3. ob_start();
  4.  
  5. $DB_Server = "localhost"; //your MySQL Server
  6. $DB_Username = "************"; //your MySQL User Name
  7. $DB_Password = "***************"; //your MySQL Password
  8. $DB_DBName = "***************"; //your MySQL Database Name
  9. $DB_TBLName = "aplikacja1"; //your MySQL Table Name
  10. //$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser
  11. //as parameters in a query string, so that this code may be easily reused for
  12. //any MySQL table or any MySQL database on your server
  13.  
  14. //DEFINE SQL QUERY:
  15. //you can use just about ANY kind of select statement you want -
  16. //edit this to suit your needs!
  17. $sql = "Select * from $DB_TBLName";
  18.  
  19. //Optional: print out title to top of Excel or Word file with Timestamp
  20. //for when file was generated:
  21. //set $Use_Titel = 1 to generate title, 0 not to use title
  22. $Use_Title = 1;
  23. //define date for title: EDIT this to create the time-format you need
  24. $now_date = date('m-d-Y H:i');
  25. //define title for .doc or .xls file: EDIT this if you want
  26. $title = "Szanowna Pani kierownik na pani życzenie lista aplikacji o miejsce w akademiku dnia $now_date";
  27. /*
  28.  
  29. Leave the connection info below as it is:
  30. just edit the above.
  31.  
  32. (Editing of code past this point recommended only for advanced users.)
  33. */
  34. //create MySQL connection
  35. $Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
  36. or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
  37. mysql_query("SET NAMES 'utf8'");
  38. //select database
  39. $Db = @mysql_select_db($DB_DBName, $Connect)
  40. or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
  41. //execute query
  42. $result = @mysql_query($sql,$Connect)
  43. or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
  44.  
  45. //if this parameter is included ($w=1), file returned will be in word format ('.doc')
  46. //if parameter is not included, file returned will be in excel format ('.xls')
  47. if (isset($w) && ($w==1))
  48. {
  49. $file_type = "msword";
  50. $file_ending = "doc";
  51. }else {
  52. $file_type = "vnd.ms-excel";
  53. $file_ending = "xls";
  54. }
  55. //header info for browser: determines file type ('.doc' or '.xls')
  56. header("Content-Type: application/$file_type");
  57. header("Content-Disposition: attachment; filename=baza_aplikacji.$file_ending");
  58. header("Pragma: no-cache");
  59. header("Expires: 0");
  60.  
  61. /* Start of Formatting for Word or Excel */
  62.  
  63. if (isset($w) && ($w==1)) //check for $w again
  64. {
  65. /* FORMATTING FOR WORD DOCUMENTS ('.doc') */
  66. //create title with timestamp:
  67. if ($Use_Title == 1)
  68. {
  69. echo("$title\n\n");
  70. }
  71. //define separator (defines columns in excel & tabs in word)
  72. $sep = "\n"; //new line character
  73.  
  74. while($row = mysql_fetch_row($result))
  75. {
  76. //set_time_limit(60); // HaRa
  77. $schema_insert = "";
  78. for($j=0; $j<mysql_num_fields($result);$j++)
  79. {
  80. //define field names
  81. $field_name = mysql_field_name($result,$j);
  82. //will show name of fields
  83. $schema_insert .= "$field_name:\t";
  84. if(!isset($row[$j])) {
  85. $schema_insert .= "NULL".$sep;
  86. }
  87. elseif ($row[$j] != "") {
  88. $schema_insert .= "$row[$j]".$sep;
  89. }
  90. else {
  91. $schema_insert .= "".$sep;
  92. }
  93. }
  94. $schema_insert = str_replace($sep."$", "", $schema_insert);
  95. $schema_insert .= "\t";
  96. print(trim($schema_insert));
  97. //end of each mysql row
  98. //creates line to separate data from each MySQL table row
  99. print "\n----------------------------------------------------\n";
  100. }
  101. }else{
  102. /* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */
  103. //create title with timestamp:
  104. if ($Use_Title == 1)
  105. {
  106. echo("$title\n");
  107. }
  108. //define separator (defines columns in excel & tabs in word)
  109. $sep = "\t"; //tabbed character
  110.  
  111. //start of printing column names as names of MySQL fields
  112. for ($i = 0; $i < mysql_num_fields($result); $i++)
  113. {
  114. echo mysql_field_name($result,$i) . "\t";
  115. }
  116. print("\n");
  117. //end of printing column names
  118.  
  119. //start while loop to get data
  120. while($row = mysql_fetch_row($result))
  121. {
  122. //set_time_limit(60); // HaRa
  123. $schema_insert = "";
  124. for($j=0; $j<mysql_num_fields($result);$j++)
  125. {
  126. if(!isset($row[$j]))
  127. $schema_insert .= "NULL".$sep;
  128. elseif ($row[$j] != "")
  129. $schema_insert .= "$row[$j]".$sep;
  130. else
  131. $schema_insert .= "".$sep;
  132. }
  133. $schema_insert = str_replace($sep."$", "", $schema_insert);
  134. //following fix suggested by Josue (thanks, Josue!)
  135. //this corrects output in excel when table fields contain \n or \r
  136. //these two characters are now replaced with a space
  137. $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
  138. $schema_insert .= "\t";
  139. print(trim($schema_insert));
  140. print "\n";
  141. }
  142. }
  143.  
  144. $text = ob_get_clean();
  145. $text = iconv('UTF-8', 'CP1250//TRANSLIT', $text);
  146. echo $text;
  147.  
  148. ?>
  149.  
  150.  
[PHP] pobierz, plaintext
To jest wersja lo-fi głównej zawarto¶ci. Aby zobaczyć pełn± wersję z większ± zawarto¶ci±, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.