PHP创建Excel Sheet在打开时创建错误 [英] PHP created Excel Sheet creates errors upon opening
问题描述
所以，我目前的代码工作100％，这个文件是在excel中打开的。但是，在打开时，为了打开文件，需要点击几个错误。它们如下：
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:
address-book.xls的文件格式和扩展名不匹配。该文件可能已损坏或不安全。除非你信任它的来源，否则不要打开它。你想打开它吗？
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?
然后：
Excel检测到'address-book.xls是一个SYLK文件，但无法加载它。文件有错误或不是SYLK文件格式。单击确定以尝试以不同的格式打开文件。
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.
然后终于：
address-book.xls的文件格式和扩展名不匹配。该文件可能已损坏或不安全。除非你信任它的来源，否则不要打开它。你想打开它吗？
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;
？
PHPExcel错误： / p>
PHPExcel Error:
$colnames = array("Title","First name","Last Name","Address","City","State","Zipcode","Home Phone","Cell Phone","Email #1","Email #2");
$col = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
// Add Headers
for($i=0;$i<count($colnames);$i++){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($col[$i]."1", $colnames[$i]);
}
$sql = "SELECT * FROM ".$sDB_table." ORDER BY Last_Name";
$query = $sDBConn -> prepare($sql);
$query -> execute();
$rownum = 1;
//Add Content
while(false !== ($row = $query->fetch(PDO::FETCH_ASSOC))) {
$rownum++;
for($i=0;$i<count($row);$i++){
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($col[$i]."".$rownum, $row[$i]);
}
}
当它发生在while语句时出现错误，if
The error occurs when it hits the while statement, if I take that part out, it works.
推荐答案
问题是 ID 作为文件的前两个字节。这是SYLK文件的签名，是Multiplan（Symbolic Link Interchange - SYLK - 文件格式）使用的旧电子表格格式，是Microsoft Excel的前身。
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.
最简单解决这个问题的方法是将 ID 作为标题，并使用 Id （IIRC，SYLK签名是case-敏感）或键或其他一些文本值
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
你使用 xls 扩展名保存文件，但实际上并不是使用Excel格式，只是一种csv格式;所以MS Excel会抱怨格式与扩展名不匹配....或者将扩展名更改为 .csv 以匹配您正在创建的文件的实际格式;或创建一个真正的BIFF格式的Excel文件
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
任何帮助格式化电子表格，如拥有足够大的内容可以让内容变得非常棒。
您使用的格式，csv（带有制表符分隔符）不支持任何类型的格式，包括列宽。如果你想要的话，那么你需要切换到一个真正的电子表格格式（通过巧合，SYLK是一个真正的电子表格格式，虽然它很漂亮，我不会特别推荐），如BIFF为真正的xls文件，OfficeOpenXML为xlsx文件，OASIS for ods文件。
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.
我用于OfficeOpenXML xlsx文件的标题：
Headers that I use for OfficeOpenXML xlsx files:
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="01simple.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
这篇关于PHP创建Excel Sheet在打开时创建错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
