因此，我当前的代码 100% 有效，文件已创建并在 excel 中打开。但是在打开时，必须单击一些错误才能打开文件。它们如下:

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 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.

然后最后:

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?

然后它不情愿地打开了。这是创建它的代码。 任何有关格式化电子表格的帮助，例如让单元格足够大以容纳内容都会很棒。但在打开时消除错误是我的首要任务。

<?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 错误:

$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 语句时，如果我把那部分去掉，它就可以工作。

最佳答案

问题是 ID 作为文件的前两个字节。这是 SYLK 文件的签名，Multiplan(符号链接(symbolic link)交换 - SYLK - 文件格式)使用的一种旧电子表格格式，是 Microsoft Excel 的前身。

解决此问题的最简单方法是避免将 ID 作为您的标题，并使用 Id(IIRC，SYLK 签名区分大小写)或 Key 或其他一些文本值代替

您正在使用 xls 扩展名保存您的文件，但您实际上并没有使用 Excel 格式，只是 csv 格式；所以 MS Excel 会提示格式与扩展名不匹配....要么将扩展名更改为 .csv 以匹配您正在创建的文件的实际格式；或者创建一个真正的 BIFF 格式的 Excel 文件

任何有关格式化电子表格的帮助，例如让单元格足够大以容纳内容都会很棒。

您使用的格式 csv(带有制表符分隔符)不支持任何类型的格式设置，包括列宽。如果你想要那样，那么你需要切换到真正的电子表格格式(巧合的是，SYLK 是一种真正的电子表格格式，虽然它已经过时而且我不会特别推荐它)例如真正的 xls 文件的 BIFF，xlsx 的 OfficeOpenXML文件，OASIS 用于 ods 文件。

我用于 OfficeOpenXML xlsx 文件的 header :

// 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 工作表在打开时会出错，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/37510349/