export excel utf-8

[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

[tivrfoa]

I tested this:

 

<?php

    require (dirname (FILE) . "/class-excel-xml.inc.php"); 

    $myarray = array(); 
for($i = 0; $i < 10; $i++) {
        $myarray[] = array("name", "email", "other thing"); 
    } 

    $xls = new Excel_XML;
    $xls->addArray ( $myarray ); 
    $xls->generateXML ( "testfile" ); 

?> 

 

the class "class-excel-xml.inc.php"

http://code.google.com/p/php-excel/downloads/list

 

and it worked fine, but my text editor (notepad ++) don't recognize arabic characters, and changed the characters to this "?".

 

does someone can give some clue about how to work with arabic characters?

I visited an arabic site and the codification is Thai (TIS-620)

[tivrfoa]

I think this thread should be on PHP help ???

[Mark Baker]

Excel (Biff5/8 xls) and (2007 xlsx) should both work with straight utf-8, so if you're retrieving utf-8 data from the database, then it should simply be a matter of setting the cell values to the value retrieved from the database.

 

If your database isn't utf-8, you should be able to see what charset it is using and do an iconv to convert that to utf-8.

Normally arabic sites would use charset=iso-8859-6

 

What I can't comment on is whether your document writer actually does use utf-8 for excel files, but the version you've indicated is rather dated

[fenway]

I think this thread should be on PHP help ???

Agreed... pls confirm this isn't DB-related, and I'll move it.