Michael Adair Michael Adair - 6 months ago 71
PHP Question

PHP created Excel Sheet creates errors upon opening

So, my current code works 100%, the file is made and it is opened in excel. But upon opening, there are a few errors one must click through in order to open the file. They are as follows:


The file format and extension of 'address-book.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?


Then:


Excel has detected that 'address-book.xls is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format. Click OK to try to open the file in a different format.


Then finally:


The file format and extension of 'address-book.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?


After that it reluctantly opens. Here is the code that creates it.
Any help with formatting the spreadsheet, such as having the cells be big enough for the content would be great. But getting rid of the errors upon opening is my priority.

<?PHP
require_once('conn.php');

$colnames = array(
'id' => "ID",
'Title' => "Title",
'First_Name' => "First name",
'Last_Name' => "Last Name",
'Address' => "Address",
'City' => "City",
'State' => "State",
'Zipcode' => "Zipcode",
'Home_Phone' => "Home Phone",
'Cell_Phone' => "Cell Phone",
'Email1' => "Email #1",
'Email2' => "Email #2"
);

function map_colnames($input){
global $colnames;
return isset($colnames[$input]) ? $colnames[$input] : $input;
}
function cleanData(&$str){
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
}

$filename = "address-book";

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$filename.".xls");
header("Pragma: no-cache");
header("Expires: 0");
$flag = false;
$sql = "SELECT * FROM ".$sDB_table." ORDER BY Last_Name";
$query = $sDBConn -> prepare($sql);
$query -> execute();
while(false !== ($row = $query->fetch(PDO::FETCH_ASSOC))) {
if(!$flag){
$firstline = array_map(__NAMESPACE__ . '\map_colnames', array_keys($row));
echo implode("\t", $firstline) . "\r\n";
$flag = true;
}
array_walk($row, __NAMESPACE__ . '\cleanData');
echo implode("\t", array_values($row)) . "\r\n";
}
exit;


?>

Answer

The problem is ID as the first two bytes of your file. This is the signature for a SYLK file, an old spreadsheet format used by Multiplan (Symbolic Link Interchange - SYLK - file format), a precursor to Microsoft Excel.

The easiest way to resolve this is to avoid ID as your heading, and use Id (IIRC, the SYLK signature is case-sensitive) or Key or some other text value instead


You're saving your file with an xls extension, but you're not actually using Excel format, simply a csv format; so MS Excel will complain that the format doesn't match the extension.... either change the extension to .csv to match the actual format of the file you're creating; or create a real BIFF-format Excel file


Any help with formatting the spreadsheet, such as having the cells be big enough for the content would be great.

The format that you're using, csv (with a tab separator) doesn't support formatting of any kind, including column widths. If you want that, then you need to switch to a true spreadsheet format (by coincidence, SYLK is a true spreadsheet format, though it's pretty dated and I wouldn't particularly recommend it) such as BIFF for real xls files, OfficeOpenXML for xlsx files, OASIS for ods files.

Comments