phputf-8drupalexport-to-csvexport-to-excel

Drupal export data from table to excel


I am using the following 2 functions to export data from a table called eco_customers to excel file

but I have a problem with Arabic characters, they show corrupt, how can I modify the encoding to correct the Arabic characters problem in the excel file

function cleanData(&$str) { 
    $str = preg_replace("/\t/", "\\t", $str); 
    $str = preg_replace("/\r?\n/", "\\n", $str); 
    if(strstr($str, '"')) 
    $str = '"' . str_replace('"', '""', $str) . '"';
     }

function customer_export(){

 $filename = "cutomers_data_" . date('Ymd') . ".xls"; 
 header("Content-Disposition: attachment; filename=\"$filename\""); 
 header("Content-Type: application/vnd.ms-excel, charset=UTF-8; encoding=UTF-8"); 

 $flag = false;

 $result = db_query("SELECT name, arabic_name, phones, fax, address, country, city, email, website, registration_no FROM eco_customers ") or die('Query failed!'); 

  while(false !== ($row = db_fetch_array($result))) { 

         if(!$flag) { 
          // display field/column names as first row 
          echo implode("\t", array_keys($row)) . "\r\n"; 
          $flag = true; 
          }
         array_walk($row, 'cleanData'); 
         echo implode("\t", array_values($row)) . "\r\n";
  }

 }

Solution

  • I used phpExcel library and everything worked fine for me now

     function customer_export(){
    
    
    require_once('Classes/PHPExcel.php');
    
    
    
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getActiveSheet()->setTitle('List of Customers');
    
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', 'Name')
                ->setCellValue('B1', 'Arabic Name')
                ->setCellValue('C1', 'Phone')
                ->setCellValue('D1', 'Fax')
                ->setCellValue('E1', 'Address')
                ->setCellValue('F1', 'Country')
                ->setCellValue('G1', 'City')
                ->setCellValue('H1', 'Email')
                ->setCellValue('I1', 'Website')
                ->setCellValue('J1', 'Registration Number');
    
     $result = db_query("SELECT name, arabic_name, phones, fax, address, country, city, email, website, registration_no FROM eco_customers ") or die('Query failed!'); 
    
      // Loop through the result set
        $rowNumber = 2;
        while ($row = db_fetch_array($result)) {
           $col = 'A';
           foreach($row as $cell) {
              $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
              $col++;
           }
           $rowNumber++;
        }
    
    
        // Freeze pane so that the heading line won't scroll
        $objPHPExcel->getActiveSheet()->freezePane('A2');
    
        // Save as an Excel BIFF (xls) file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    
       header('Content-Type: application/vnd.ms-excel');
       header('Content-Disposition: attachment;filename="customers_data_' . date('Ymd') . '.xls"');
       header('Cache-Control: max-age=0');
    
       $objWriter->save('php://output');
       exit();
    
     }