I have data in hebrew in my database and want to export that data as excell file on button click using php in wordpress. On downloading and opening the file the hebrew text display like מפרץ שלמה.
Here is my code:
ob_start();
date_default_timezone_set("Asia/Bangkok");
$admin_added_customers = $wpdb->get_results
(
$wpdb->prepare
(
"SELECT * FROM ".$wpdb->prefix . "record WHERE not_shipped = %d",
0
)
);
function filterData(&$str)
{
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/?
/", "\
", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}
// file name for download
$fileName = "unshipped_cards_export_data" . date('Y-m-d') . ".xls";
// headers for download
header("Content-Type: text/html; charset=UTF-16LE");
header("Content-Disposition: attachment; filename=\"$fileName\"");
//header("Content-Type: application/vnd.ms-excel");
$keys = array("Name","Number","First Name", "Email","Address1","Address2","City","State","Zipcode","Country","Purchased On", "Expire On");
echo implode("\t", $keys) . "
";
foreach($admin_added_customers as $row) {
$customer = new WP_User($row->customer_id);
echo mb_convert_encoding($customer->billing_first_name." ".$customer->billing_last_name,'utf-16','utf-8')."\t";
echo $row->id."\t";
echo $row->name."\t";
echo $customer->user_email."\t";
echo $customer->billing_address_1."\t";
echo mb_convert_encoding($customer->billing_address_2,'utf-16','utf-8')."\t";
echo mb_convert_encoding($customer->billing_city,'utf-16','utf-8')."\t";
echo mb_convert_encoding($customer->billing_state,'utf-16','utf-8')."\t";
echo $customer->billing_postcode."\t";
echo WC()->countries->countries[$customer->billing_country]."\t";
echo !empty($row->purchase_date) ? date("F j, Y", strtotime($row->purchase_date))."\t" : "\t";
echo !empty($row->expiry_date) ? date("F j, Y", strtotime($row->expiry_date))."\t" : "\t";
echo "
";
}
Edit 1:
Here is the new code :
header("Content-Disposition: attachment; filename=\"$fileName\"");
header("Content-Type: application/vnd.ms-excel");
echo "<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head><body>";
$keys = array("Name","Number","First Name", "Email","Address1","Address2","City","State","Zipcode","Country","Purchased On", "Expire On");
echo implode("\t", $keys) . "
";
foreach($admin_added_customers as $row) {
$customer = new WP_User($row->customer_id);
echo $customer->billing_first_name." ".$customer->billing_last_name."\t";
echo $row->id."\t";
echo $row->name."\t";
echo $customer->user_email."\t";
echo $customer->billing_address_1."\t";
echo $customer->billing_address_2."\t";
echo $customer->billing_city."\t";
echo $customer->billing_state)."\t";
echo $customer->billing_postcode."\t";
echo WC()->countries->countries[$customer->billing_country]."\t";
echo !empty($row->purchase_date) ? date("F j, Y", strtotime($row->purchase_date))."\t" : "\t";
echo !empty($row->expiry_date) ? date("F j, Y", strtotime($row->expiry_date))."\t" : "\t";
echo "
";
}
echo "</body></html>";
This will solve the issue of hebrew text but now rather show data in tabular form, its showing whole data in first cell of excel.