It's been a little while since I've written; but everything has been smooth sailing for a while. Tonight, I had a small issue with Excel XML file creation that I thought I'd write about.
The problem stems from word processing programs using left and right oriented apostrophe's instead of single quotes. Notice that the character ' is different from ’ and even different from ‘.
They all have different html codes associated with them. To display the single quotation, we escape the character with a backslash; however, with left apostrophe and right apostrophe, we have to use &lsquo and &rsquo respectively.
Now, php's htmlspecialchars() function does not look after these special html translations, so we have to add in a function like
function myhtmlspecialchars($string) {
$transTable = get_html_translation_table(HTML_ENTITIES);
$transTable[chr(145)] = '\''; // looks after &lsquo
$transTable[chr(146)] = '\''; // looks after &rsquo
$transTable[chr(147)] = '"'; // looks after &ldquo
$transTable[chr(148)] = '"'; // looks after &rdquo
return strtr($string, $transTable);
}
The result of not fixing this on a website is that you get diamonds with question marks in them. The result of not fixing this on a xml file is that it breaks.
The function that I'm using the generate the excel data is as follows
function excelXML($bigArray) {
$colCount = sizeof($bigArray[0]);
$colCount2 = $colCount -1;
$rowCount = sizeof($bigArray)+1;
$beginOutput = "
dougler
dougler
2010-05-04T04:19:22Z
12.00
14880
28755
0
135
False
False
\n"; foreach ($bigArray[0] as $key => $value) { $beginOutput .= "| $key | \n"; } $beginOutput .= "
\n"; $middleOutput = ""; for ($i = 0; $i < sizeof($bigArray); $i++) {
$middleOutput .= "\n"; foreach ($bigArray[$i] as $key => $value) { $middleOutput .= "| " . myhtmlspecialchars($value) . " | \n"; } $middleOutput .= "
\n"; } $endOutput = "
300
300
3
55
False
False
";
return $beginOutput . $middleOutput . $endOutput;
}
Where $bigArray looks something like
Array
(
[0] => Array
(
[id] => 1
[company] => test
[address] => test
[city] => test
[province] => test
[postal] => test
[website] => test
[contact] => test
[telephone] => test
[email] => test@test.ca
[password] => test
[Number of Projects] => test
)
[1] => Array
(
[id] => 2
[company] => test
[address] => test
[city] => test
[province] => test
[postal] => test
[website] => test
[contact] => test
[telephone] => test
[email] => test@test.ca
[password] => test
[Number of Projects] =>
)
[2] => Array
(
[id] => 3
[company] => test
[address] => test
[city] => test
[province] => test
[postal] => test
[website] => test
[contact] => test
[telephone] => test
[email] => test@test.ca
[password] => test
[Number of Projects] => test
)
)
