对于如此大量的数据，我不会推荐 PHPExcel 或 ApachePOI（用于 Java）之类的工具，因为它们需要内存。我最近一直在努力完成类似的任务，并且找到了将数据注入电子表格的方便（但可能有点繁琐）的方法。服务器端生成或更新 Excel 电子表格可以实现简单的 XML 编辑。我的服务器上有 XLSX 电子表格，每次从 dB 收集数据时，我都会使用 php 对其进行解压缩。然后，我访问包含需要注入和手动插入数据的工作表内容的特定 XML 文件。然后，我压缩电子表格文件夹，以便将其作为常规 XLSX 文件分发。整个过程非常快速且可靠。显然，与 XLSX/Open XML 文件的内部组织相关的问题和故障很少（例如，Excel 倾向于将所有字符串存储在单独的表中，并在工作表中使用对此表的引用）。但当仅注入数字和字符串等数据时，这并不难。如果有人感兴趣，我可以提供一些代码。
好的，这是示例代码。我试图评论它的作用，但请随时要求进一步解释。
<?php
/**
* Class for serverside spreadsheet data injecting
* Reqs: unzip.php, zip.php (containing any utility functions able to unzip files & zip folders)
*
* Author: Poborak
*/
class DataInjector
{
//spreadsheet file, we inject data into this one
const SPREADSHEET_FILE="datafile.xlsx";
// specific worksheet into which data are being injected
const SPREADSHEET_WORKSHEET_FILE="/xl/worksheets/sheet7.xml";
//working directory, spreadsheet is extracted here
const WSPACE_DIR="Wspace";
// query for obtaining data from DB
const STORE_QUERY = "SELECT * FROM stores ORDER BY store_number ASC";
private $dbConn;
private $storesData;
/**
* @param mysqli $dbConn
*/
function __construct(mysqli $dbConn) {
$this->dbConn = $dbConn;
}
/**
* Main method for whole injection process
* First data are gathered from DB and spreadsheet is decompressed to workspace.
* Then injection takes place and spreadsheet is ready to be rebuilt again by zipping.
*
* @return boolean Informace o úspěchu
*/
public function injectData() {
if (!$this->getStoresInfoFromDB()) return false;
if (!$this->explodeSpreadsheet(self::SPREADSHEET_FILE,self::WSPACE_DIR)) return false;
if (!$this->injectDataToSpreadsheet(self::WSPACE_SUBDIR.self::SPREADSHEET_WORKSHEET_FILE)) return false;
if (!$this->implodeSpreadsheet(self::SPREADSHEET_FILE,self::WSPACE_DIR)) return false;
return true;
}
/**
* Decompress spreadsheet file to folder
*
* @param string $spreadsheet
* @param string $targetFolder
*
* @return boolean success/fail
*/
private function explodeSpreadsheet($spreadsheet, $targetFolder) {
return unzip($spreadsheet,$targetFolder);
}
/**
* Compress source folder to spreadsheet file
*
* @param string $spreadsheet
* @param string $sourceFolder
*
* @return boolean success/fail
*/
private function implodeSpreadsheet($spreadsheet, $sourceFolder) {
return zip($sourceFolder,$spreadsheet);
}
/**
* Loads data from DB to member variable $storesDetails (as array)
*
* @return boolean success/fail
*/
private function getStoresInfoFromDb() {
unset($this->storesData);
if ($stmt = $this->dbConn->prepare(self::STORE_QUERY)) {
$stmt->execute();
$stmt->bind_result($store_number, $store_regional_manager, $store_manager, $store_city, $store_address);
while ($stmt->fetch()) {
$this->storesData[trim($store_number)] = array(trim($store_regional_manager),trim($store_manager),trim($store_address),trim($store_city));
}
$stmt->close();
}
return true;
}
/**
* Injects data from member variable $storesDetails to spreadsheet $ws
*
* @param string $ws target worksheet
*
* @return boolean success/fail
*/
private function injectDataToSpreadsheet($ws) {
$worksheet = file_get_contents($ws);
if ($worksheet === false or empty($this->storesData) return false;
$xml = simplexml_load_string($worksheet);
if (!$xml) return false;
// Loop through $storesDetails array containing rows of data
foreach ($this->storesData as $std){
// For each row of data create new row in excel worksheet
$newRow = $xml->sheetData->addChild('row');
// Loop through columns values in rowdata
foreach ($std as $cbd){
// Save each column value into next column in worksheets row
foreach ($this->storesData as $cbd){
$newCell = $newRow->addChild('c');
$newCell->addAttribute('t', "inlineStr");
$newIs = $newCell->addChild('is');
// text has to be saved as utf-8 (otherwise the spreadsheet file become corrupted)
if (!mb_check_encoding($cbd, 'utf-8')) $cbd = iconv("cp1250","utf-8",$cbd);
$newT = $newIs->addChild('t',$cbd);
}
}
}
// Save xml data back to worksheet file
if (file_put_contents($ws, $xml->asXML()) !== false) return true;
}
}
?>
