Create Excel Data from PHP

Hey All,

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)] = '&quot'; // looks after &ldquo
    $transTable[chr(148)] = '&quot'; // 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
        )
)