code_junkie

Microsoft Excel mangles Diacritics in .csv files?

Nov 11th, 2011
134
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.33 KB | None | 0 0
  1. /**
  2. * Export an array as downladable Excel CSV
  3. * @param array $header
  4. * @param array $data
  5. * @param string $filename
  6. */
  7. function toCSV($header, $data, $filename) {
  8. $sep = "t";
  9. $eol = "n";
  10. $csv = count($header) ? '"'. implode('"'.$sep.'"', $header).'"'.$eol : '';
  11. foreach($data as $line) {
  12. $csv .= '"'. implode('"'.$sep.'"', $line).'"'.$eol;
  13. }
  14.  
  15. header('Content-Description: File Transfer');
  16. header('Content-Type: application/vnd.ms-excel');
  17. header('Content-Disposition: attachment; filename='.$filename.'.csv');
  18. header('Content-Transfer-Encoding: binary');
  19. header('Expires: 0');
  20. header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  21. header('Pragma: public');
  22. header('Content-Length: '. strlen($csv));
  23. echo chr(255) . chr(254) . mb_convert_encoding($csv, 'UTF-16LE', 'UTF-8');
  24. exit;
  25. }
  26.  
  27. >>> open('oldmac.csv', 'rb').read()
  28. 'xefxbbxbfOld MacDonald had a farm,xc3x88xc3x8cxc3x89xc3x8dxc3x98rn'
  29. >>> ^Z
  30.  
  31. <%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%>
  32. <%
  33. Response.Clear();
  34. Response.ContentType = "text/csv";
  35. Response.Charset = "utf-8";
  36. Response.AddHeader("Content-Disposition", "attachment; filename=excelTest.csv");
  37. Response.Write("uFEFF");
  38. // csv text here
  39. %>
  40.  
  41. <html>
  42. <meta http-equiv="Content-Type" content="text/html" charset="utf-8" />
  43. <table>
  44. <tr>
  45. <th>id</th>
  46. <th>name</th>
  47. </tr>
  48. <tr>
  49. <td>4</td>
  50. <td>Hélène</td>
  51. </tr>
  52. </table>
  53. </html>
  54.  
  55. def handlePersoonListExport(request):
  56. # Retrieve a query_set
  57. ...
  58.  
  59. template = loader.get_template("export.csv")
  60. context = Context({
  61. 'data': query_set,
  62. })
  63.  
  64. response = HttpResponse()
  65. response['Content-Disposition'] = 'attachment; filename=export.csv'
  66. response['Content-Type'] = 'text/csv; charset=utf-8'
  67. response.write("xEFxBBxBF")
  68. response.write(template.render(context))
  69.  
  70. return response
  71.  
  72. echo "xEFxBBxBF";
  73.  
  74. <%
  75. require 'fastercsv'
  76. fcsv_options = {
  77. :row_sep => "n",
  78. :col_sep => params[:delimiter],
  79. :force_quotes => @export_config.force_quotes,
  80. :headers => @export_columns.collect { |column| format_export_column_header_name(column) }
  81. }
  82.  
  83. data = FasterCSV.generate(fcsv_options) do |csv|
  84. csv << fcsv_options[:headers] unless params[:skip_header] == 'true'
  85. @records.each do |record|
  86. csv << @export_columns.collect { |column|
  87. # Converte ogni campo in UTF-16 scartando il BOM, richiesto per Excel (> 2003 ?)
  88. Iconv.conv('UTF-16', 'UTF-8', get_export_column_value(record, column))[2..-1]
  89. }
  90. end
  91. end
  92. -%><%= data -%>
  93.  
  94. Iconv.conv('UTF-16', 'UTF-8', get_export_column_value(record, column))[2..-1]
Add Comment
Please, Sign In to add comment