PHP 的 COM 擴展（php_com_dotnet.dll）允許在 Windows 平台上調用 COM 組件，實現對 Access 數據庫、Office 應用、系統組件（WMI、ADSI、Scripting.FileSystemObject 等）的原生訪問。雖然跨平台性為零，但在 Windows 內網、遷移舊 VB6/ASP 系統、辦公自動化、報表生成等場景極具價值。本文系統講解 ADO 訪問 Access、Word 自動化、Excel 報表、錯誤處理、性能優化及安全邊界。
extension=com_dotnet (php.ini)dcomcnfg → 組件服務 → 計算機 → 我的電腦 → 屬性 → COM 安全 → 啟動和激活權限/訪問權限 → 添加 IIS_IUSRS / php-fpm 用戶connStr = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source={$dbPath};Persist Security Info=False;";
}
public function connect(): void {
$this->conn = new COM('ADODB.Connection');
$this->conn->ConnectionString = $this->connStr;
$this->conn->Open();
}
public function query(string $sql, array $params = []): array {
$rs = $this->conn->Execute($sql, $affectedRows, 1); // 1 = adCmdText
if (!$rs) throw new RuntimeException("查詢執行失敗: {$sql}");
$results = [];
while (!$rs->EOF) {
$row = [];
for ($i = 0; $i < $rs->Fields->Count; $i++) {
$field = $rs->Fields($i);
$row[$field->Name] = $field->Value;
}
$results[] = $row;
$rs->MoveNext();
}
$rs->Close();
return $results;
}
public function execute(string $sql, array $params = []): int {
$this->conn->Execute($sql, $affectedRows, 1);
return $affectedRows;
}
public function beginTransaction(): void {
$this->conn->BeginTrans();
}
public function commit(): void {
$this->conn->CommitTrans();
}
public function rollback(): void {
$this->conn->RollbackTrans();
}
public function __destruct() {
if ($this->conn && $this->conn->State) {
$this->conn->Close();
}
}
}
// 使用示例
$db = new AccessDatabase('D:\Data\business.accdb');
$db->connect();
$users = $db->query("SELECT id, username, email FROM users WHERE active = ?", [true]);
foreach ($users as $u) {
echo "{$u['id']}: {$u['username']} <{$u['email']}>\n";
}
Class not registered。CursorLocation = 3 (adUseClient)、CursorType = 3 (adOpenStatic)、LockType = 3 (adLockOptimistic)。Close()，否則導致 .ldb 鎖文件殘留、連接泄漏。word = new COM('Word.Application') or die('無法啟動 Word');
$this->word->Visible = $visible;
$this->word->DisplayAlerts = 0; // 靜默模式
}
public function createFromTemplate(string $templatePath, array $bookmarks, string $outputPath, string $format = 'pdf'): bool {
try {
$doc = $this->word->Documents->Open($templatePath, false, true); // 唯讀打開模板
foreach ($bookmarks as $name => $text) {
if ($doc->Bookmarks->Exists($name)) {
$doc->Bookmarks($name)->Range->Text = $text;
}
}
$formatMap = ['pdf' => 17, 'docx' => 16, 'doc' => 0, 'rtf' => 6, 'html' => 8];
$doc->SaveAs($outputPath, $formatMap[$format] ?? 17);
$doc->Close(false);
return true;
} catch (com_exception $e) {
error_log("Word 自動化失敗: {$e->getMessage()}");
return false;
}
}
public function convertToPdf(string $inputPath, string $outputPath): bool {
try {
$doc = $this->word->Documents->Open($inputPath, false, true);
$doc->SaveAs($outputPath, 17); // wdFormatPDF = 17
$doc->Close(false);
return true;
} catch (com_exception $e) {
return false;
}
}
public function __destruct() {
if ($this->word) {
$this->word->Quit(false);
unset($this->word);
}
}
}
// 使用示例
$word = new WordAutomation();
$bookmarks = ['ClientName' => '張三', 'Amount' => '¥10,000.00', 'Date' => date('Y-m-d')];
$word->createFromTemplate('D:\Templates\contract.dotx', $bookmarks, 'D:\Output\contract_123.pdf');
excel = new COM('Excel.Application') or die('無法啟動 Excel');
$this->excel->Visible = $visible;
$this->excel->DisplayAlerts = false;
$this->excel->ScreenUpdating = false; // 關閉屏幕刷新，大幅提速
}
public function exportReport(string $templatePath, array $data, string $outputPath): bool {
try {
$wb = $this->excel->Workbooks->Open($templatePath);
$ws = $wb->Worksheets(1); // 第一個工作表
// 寫入標題行
$headers = array_keys($data[0] ?? []);
foreach ($headers as $col => $header) {
$ws->Cells(1, $col + 1)->Value = $header;
}
// 批量寫入數據 (陣列一次性賦值，比逐單元格快 100x+)
$rowCount = count($data);
$colCount = count($headers);
if ($rowCount > 0) {
$values = [];
foreach ($data as $row) {
$values[] = array_values($row);
}
$range = $ws->Range($ws->Cells(2, 1), $ws->Cells($rowCount + 1, $colCount));
$range->Value = $values;
}
// 自動調整列寬
$ws->Columns->AutoFit();
$wb->SaveAs($outputPath, 51); // xlOpenXMLWorkbook = 51 (.xlsx)
$wb->Close(false);
return true;
} catch (com_exception $e) {
error_log("Excel 導出失敗: {$e->getMessage()}");
return false;
}
}
public function __destruct() {
if ($this->excel) {
$this->excel->Quit();
unset($this->excel);
}
}
}
| 問題 | 原因 | 解決 |
|---|---|---|
| 極慢 (秒級/操作) | COM 跨進程調用開銷大、逐屬性/方法調用封送 | 批量操作 (Range.Value = 陣列)、關閉 ScreenUpdating/DisplayAlerts、最小化跨邊界調用 |
| 內存洩漏/進程殘留 | COM 物件未正確 Release、PHP 異常中斷未清理 | __destruct 中 Quit()、try/finally 確保關閉、定期重啟 PHP-FPM/IIS 應用池 |
| 並發失敗 | Office 應用單實例、非線程安全、文件鎖衝突 | 串行隊列處理 (Redis/RabbitMQ + 單消費者)、文件唯一命名、超時監控 |
| 權限拒絕 | DCOM 啟動身份、文件系統 ACL、受保護視圖 | dcomcnfg 設置啟動用戶、輸出目錄授予寫入權、關閉受保護視圖 |
| 需求 | 推薦庫 (Composer) | 特點 |
|---|---|---|
| 讀寫 .xlsx/.xls | PhpSpreadsheet | 純 PHP、功能全、內存佔用大、大文件需分塊 |
| 高性能 .xlsx 寫入 | Box/Spout | 流式寫入、常數內存、速度快、無圖表/樞紐表 |
| 讀寫 .docx | PhpOffice/PhpWord | 純 PHP、模板引擎、轉 PDF 需 LibreOffice |
| Access 數據庫 | PDO_ODBC + ACE Driver | 標準 PDO 接口、跨平台 (Linux 需 mdbtools)、無 COM 依賴 |
| 文檔轉 PDF | LibreOffice Headless | libreoffice --headless --convert-to pdf input.docx、完美還原、無 Office 授權 |
PHP COM 橋接了 Web 應用與 Windows 生態，在辦公自動化、遷移舊系統、報表生成中不可替代。但COM 本質是跨進程 RPC，開銷大、脆弱、難併發。核心原則：能用純 PHP 庫解決的絕不用 COM；必須用 COM 時，隔離在獨立 Worker 進程，通過隊列串行處理，嚴格資源清理與超時監控。新架構設計階段，優先選擇 PhpSpreadsheet、LibreOffice Headless、PDO_ODBC 等無平台鎖定方案。
