• onlima

    (@onlima)


    Hi everyone,

    i have one problem with Export DB table to Excel with UTF8 encoding.

    when i add

    echo pack(“CCC”,0xef,0xbb,0xbf);

    all my columns are in one. Without this command my excel is not in UTF8

    function ns_contact_form_csv_pull() {
    
        
        global $wpdb;
    
        $table = $wpdb->prefix.'registred_players';// table name
        $file = 'wcgt_prihlaseni_hraci_csv'; // xls file name
        $command = "SELECT * FROM $table ORDER BY kod DESC";
        $nieco = $wpdb->get_results($command);
    
        $data = array();
        $i = 0;
    
        foreach($nieco as $zaznam) {
          $i++;
          $data[$i]=array("Meno" => $zaznam->meno, "Priezvisko" => $zaznam->priezvisko, "Domovsky klub" => $zaznam->domovsky_klub);
        }
    
      function cleanData(&$str)
      {
        $str = preg_replace("/\t/", "\\t", $str);
        $str = preg_replace("/\r?\n/", "\\n", $str);
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
      }
    
      // file name for download
      $filename = "wcgt_registrovany" . date('Ymd') . ".xls";
    
      header("Content-Disposition: attachment; filename=\"$filename\"");
      header( "Content-type: application/vnd.ms-excel; charset=UTF-8" );
      echo pack("CCC",0xef,0xbb,0xbf); // WHEN I REMOVED IT UTF-8 WASNT WORK
    
      $flag = false;
      foreach($data as $row) {
        if(!$flag) {
          // display field/column names as first row
          echo implode("\t", array_keys($row)) . "\n";
          $flag = true;
        }
        array_walk($row, __NAMESPACE__ . '\cleanData');
        echo implode("\t", array_values($row)) . "\n";
      }
    
      exit;
    
        }
       add_action('wp_ajax_csv_pull','ns_contact_form_csv_pull');
    ?>
Viewing 1 replies (of 1 total)
  • Moderator bcworkz

    (@bcworkz)

    The pack line sets the file’s byte order mark. It’s supposed to be optional in UTF-8, but Microsoft did not always follow proper conventions in the .xls format days. You can leave this line in, but you need to figure out how to separate columns. Normally the “\t” (tab) in implode() would do this. You can try other control characters by replacing "\t" with something like chr(9) (also tab). The 9, being the ASCII code for tab, will not actually help, but you can try other ASCII control codes like 3,5,10 etc.

    Looking at an old .xls Excel file with a hex editor, the cell contents were actually separated by a 3 byte 005 value. If this holds true to your file format, then try replacing the "\t" with pack('CCC',0x05,0x00,0x00). However, my .xls file is UTF-8, yet it has no byte order mark. In any case, if you play around with cell separators long enough you should hit on something that works. Good luck!

Viewing 1 replies (of 1 total)
  • The topic ‘Export DB table to Excel UTF8 problem’ is closed to new replies.