paisal

Hi, I'm new for PHP MySQL and I got problem about export from MySQL to spressheet ods,xls with UTF-8 here is code i use .. when export to ods I have to choose from dialog UTF-8 encoding for readable , badly if export to xls and openwith MS-Excel can't read at all, but openoffice I can choose encoding. Thank you all for advance. if ($_GET['date'] == "today") { $date = today(); }else { $date = trim(mysql_prep(htmlentities($_GET['date']))); } $filename="DailyInformation({$date})"; // header("Content-Type: application/vnd.ms-excel; charset=utf-8"); header("Content-Type: application/vnd.oasis.opendocument.spreadsheet ods; charset=utf-8"); header("Content-Disposition: attachment; filename=".$filename.".ods"); // header("Content-Disposition: attachment; filename=".$filename.".xls"); header("Pragma: no-cache"); header("Expires: 0"); $sep = "\t"; //tabbed character $th = array( "สาขา","","ประเภทสินค้า","ราคา","","ประเภทสินค้า","จำนวน","","ประเภทสินค้า","จำนวน","","สินค้า", "ราคาทุน","ราคาขาย","ราคาคู่แข่ง","ชื่อร้านคู่แข่ง","","ปัญหา","จากเรา","จากที่อื่น","ไม่ระบุ"); foreach ($th as $value) { echo $value . "\t"; } print("\n"); // $date = "2008-10-31"; $query = "SELECT DISTINCT `branch` "; $query .= " FROM serve.proreport"; $query .= " WHERE `date` LIKE '{$date}%'"; $query .= " AND `reptype` = '1'"; $query .= " LIMIT 100"; mysql_query("SET NAMES 'utf8'",$connection); $result = mysql_query($query, $connection); confirm_query($result); while ($row = mysql_fetch_array($result)) { $br[] = $row['branch']; } // avoid foreach error if not exist value from query data. if (!mysql_affected_rows()) { $br=array(); } foreach ($br as $key => $value) { for ($k=1; $k < 5; $k++) { // we have for type of report top,disc,long,fight // $date = "2008-10-31"; $query0 = "SELECT `proname`,`costprice`,`price`,`diffprice`,`quantity`,`shopname` "; $query0 .= " FROM serve.proreport"; $query0 .= " WHERE `date` LIKE '{$date}%'"; $query0 .= " AND `reptype` = '{$k}'"; $query0 .= " AND `branch` = '{$value}'"; $query0 .= " ORDER BY `date` ASC"; mysql_query("SET NAMES 'utf8'",$connection); $result0 = mysql_query($query0, $connection); confirm_query($result0); $i=0; while ( $row0 = mysql_fetch_array($result0)) { switch ($k) { case '1': $j=0; $td[$value][$k][$i][$j] = $row0['proname']; $j++; $td[$value][$k][$i][$j] = $row0['price']; $i++; break; case '2': $j=0; $td[$value][$k][$i][$j] = $row0['proname']; $j++; $td[$value][$k][$i][$j] = $row0['quantity']; $i++; break; case '3': $j=0; $td[$value][$k][$i][$j] = $row0['proname']; $j++; $td[$value][$k][$i][$j] = $row0['quantity']; $i++; break; case '4': $j=0; $td[$value][$k][$i][$j] = $row0['proname']; $j++; $td[$value][$k][$i][$j] = $row0['costprice']; $j++; $td[$value][$k][$i][$j] = $row0['price']; $j++; $td[$value][$k][$i][$j] = $row0['diffprice']; $j++; $td[$value][$k][$i][$j] = $row0['shopname']; $i++; break; default: # code... break; } // $t = array(); $t[] = $i; // problem happen here , now fix it. } } $u[] = max($t); $t=array(); // $date = "2008-10-31"; $query1 = "SELECT `issue`,`issuestart` "; $query1 .= " FROM serve.probissue"; $query1 .= " WHERE `postdate` LIKE '{$date}%'"; $query1 .= " AND `branch` = '{$value}'"; $query1 .= " ORDER BY `postdate` ASC"; mysql_query("SET NAMES 'utf8'",$connection); $result1 = mysql_query($query1, $connection); confirm_query($result1); $a=0;$b=1;$d = 0; while ( $row1 = mysql_fetch_array($result1)) { $td[$value][5][$d][$a] = $row1['issue']; $td[$value][5][$d][$b] = $row1['issuestart']; $d++; } $count = count($u) - 1; // take last position of array $x = $u[$count]; $total_rows = array($x, $d); // take max value between proreport and issue report $branch = branchbyid($value); for ($i=0; $i < max($total_rows); $i++) { switch ($td[$value][5][$i][1]) { case '0': $issue_from = $sep."Other".$sep; break; case '1': $issue_from = "ME".$sep.$sep; break; case '2': $issue_from = $sep.$sep."Not Specity"; break; default: $issue_from = ""; break; } if ($value != $previous_value) { $schema_insert = $branch.$sep.$sep.$td[$value][1][$i][0].$sep.$td[$value][1][$i][1].$sep.$sep.$td[$value][2][$i][0].$sep.$td[$value][2][$i][1].$sep.$sep.$td[$value][3][$i][0].$sep.$td[$value][3][$i][1].$sep.$sep.$td[$value][4][$i][0].$sep.$td[$value][4][$i][1].$sep.$td[$value][4][$i][2].$sep.$td[$value][4][$i][3].$sep.$td[$value][4][$i][4].$sep.$sep.$td[$value][5][$i][0].$sep.$issue_from; }else { $schema_insert = "\"\"".$sep.$sep.$td[$value][1][$i][0].$sep.$td[$value][1][$i][1].$sep.$sep.$td[$value][2][$i][0].$sep.$td[$value][2][$i][1].$sep.$sep.$td[$value][3][$i][0].$sep.$td[$value][3][$i][1].$sep.$sep.$td[$value][4][$i][0].$sep.$td[$value][4][$i][1].$sep.$td[$value][4][$i][2].$sep.$td[$value][4][$i][3].$sep.$td[$value][4][$i][4].$sep.$sep.$td[$value][5][$i][0].$sep.$issue_from; } $previous_value = $value; $schema_insert = str_replace($sep."$", "", $schema_insert); //following fix suggested by Josue (thanks, Josue!) //this corrects output in excel when table fields contain \n or \r //these two characters are now replaced with a space $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } // empty 1 row for seperate purpose between branches $schema_insert = $sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep.$sep; $schema_insert = str_replace($sep."$", "", $schema_insert); //following fix suggested by Josue (thanks, Josue!) //this corrects output in excel when table fields contain \n or \r //these two characters are now replaced with a space $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert); $schema_insert .= "\t"; print(trim($schema_insert)); print "\n"; } // end foreach