export arabic data to csv format

[farukcse]

Dear Sir,

I have a PHP script that export CSV with arabic text, for example I download the CSV on the website then save to desktop of my computer and Open it on NOTEPAD... that works fine I could still see all the arabic text OK with no problem, but if I open the CSV file in Microsoft EXCEL, all the arabic text are busted and no longer readable.
arabic text is inserted and stored in database as UTF-8 format.

Do you know a solution for this, how could I make it work OK on MS Excel, because the user want it to view the CSV file in Excel and this is so much urgent for me . please reply.

Regards,
Fauk Chowdhury.

 
<?php
// Connect to database
mysql_connect("localhost", "root", "root123");
mysql_select_db("medgulf");
 
 $fname = date("d-m-Y");
// mention the file name to display in download prompt
$file_name = "$fname.csv";
 
header("Content-Disposition: attachment; filename=\"$file_name\"");
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
header('Content-Type: text/xml,  charset=UTF-8; encoding=UTF-8');
 
$fd1 = $_POST['fd'];
$fm1 = $_POST['fm'];
$fy1 = $_POST['fy']; 
// Output header
//echo "Name, Code"."\n";
$frmday = $fy1."-".$fm1."-".$fd1;
$ld1 = $_POST['ld'];
$lm1 = $_POST['lm'];
$ly1 = $_POST['ly']; 
 
$today = $ly1."-".$lm1."-".$ld1;
//echo $frmday;
$usrname = $_POST['usname'];
$export_date = date("Y-m-d");
 
$result = mysql_query("SELECT product, agent_code, policy_no, DATE_FORMAT(entry_date, '%d/%m/%Y'), DATE_FORMAT(inception_date_eng, '%d/%m/%Y'), DATE_FORMAT(inception_date_hijri, '%d/%m/%Y'), DATE_FORMAT(expiry_date_eng, '%d/%m/%Y'), DATE_FORMAT(expiry_date_hijri, '%d/%m/%Y'), client_info.first_name, client_info.family_name, client_info.father_name, client_info.grand_father_name, client_info.mobile_no, client_info.dob, client_info.p_o_box, client_info.zip_code, client_info.city, client_info.id_type, client_info.id_no, vehicle_type, license_type, net_premium, brandname, model, reg_type, plate_no, model_year, chassis_no, color, user_name, age_limit  FROM vehicle_info, client_info where vehicle_info.policy_no = client_info.sr AND entry_date BETWEEN '$frmday' AND '$today' AND  user_name='$usrname' AND exported='yes' ");
$str = mb_convert_encoding($result, "UTF-8", "ASCII");
echo "encode $str";
 
if($result) {
while($rs = mysql_fetch_array($result))
{    echo $rs[0].", ";
    echo $rs[1].", ";
    echo $rs[2].", ";
    echo $rs[3].", ";
    echo $rs[4].", ";
    echo $rs[5].", ";
    echo $rs[6].", ";
    echo $rs[7].", ";
    echo $rs[8].", ";
    echo $rs[9].", ";
    echo $rs[10].", ";
    echo $rs[11].", ";
    echo $rs[12].", ";
    echo $rs[13].", ";
    echo $rs[14].", ";
    echo $rs[15].", ";
    echo $rs[16].", ";
    echo $rs[17].", ";
    echo $rs[18].", ";
    echo $rs[19].", ";
    echo $rs[20].", ";
    echo $rs[21].", ";
    echo $rs[22].", ";
    echo $rs[23].", ";
    echo $rs[24].", ";
    echo " ".", ";
    echo $rs[25].", ";
    echo $rs[26].", ";
    echo $rs[27].", ";
    echo $rs[28].", ";
    echo " ".", ";
    echo " ".", ";
    echo $rs[29].", ";
    echo $rs[30].", ";
}
 
$result4 = mysql_query("UPDATE vehicle_info SET exported ='yes', exported_date='$export_date'   WHERE user_name ='$usrname'");
 
if (!$result4) {
           die('Query failed: To Vehicle Info Table ');
            }
 
 
}
else {
 
echo "There Is No Data To Export ";
 
}
 
 
?>

[pbmods]

Heya, Fauk.

Make sure you're setting your MySQL connection encoding:

mysql_query("SET NAMES 'utf-8'", $conn);
mysql_query("SET CHARACTER SET 'utf-8'", $conn);
 

Where $conn is your MySQL connection resource.

[unus29]

Dear Faruk,

You can use phpexcel library for this purpose. Its works great. You can download the library from here:
http://code.google.com/p/php-excel/

The sample code is as follows (I used that in joomla):
function export_contact_messages() {
$model = $this->getModel('usercontacts');

$rows = $model->getContactMessages();
global $mosConfig_absolute_path;

if( !file_exists( $mosConfig_absolute_path . '/libraries/phpexcel/PHPExcel.php') ) {
die('Phpexcel library not found');
}
$phpexcel_path = $mosConfig_absolute_path . '/libraries/phpexcel/PHPExcel.php';
include_once($phpexcel_path);

//die($phpexcel_path);

$objPHPExcel = new PHPExcel();

$objPHPExcel->getProperties()->setCreator("Title")
->setLastModifiedBy("Title")
->setTitle("Contact Message")
->setSubject("Contact Message");



$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow(0, 1, "Name")
->setCellValueByColumnAndRow(1, 1, "Email")
->setCellValueByColumnAndRow(2, 1, "Message")
->setCellValueByColumnAndRow(3, 1, "Posted");

$row_count = 2;
foreach ($rows as $row) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValueByColumnAndRow(0, $row_count, $row->name)
->setCellValueByColumnAndRow(1, $row_count, $row->email)
->setCellValueByColumnAndRow(2, $row_count, $row->message)
->setCellValueByColumnAndRow(3, $row_count, $row->created_at);
$row_count++;
}

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('contact_message');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

ob_clean();
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="contract_messages.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}