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";
}
}
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();
}