[PHP] 匯出處理 – CSV、EXCEL匯出實例教學

Intro

針對使用PHP處理CSV、EXCEL輸出的基礎程式,以及實作上使用之套件。

現行建議使用套件做匯出匯入,可使用Phpspreadsheet-helper


輸出Header

以CSV為例,PHP的Header與輸出如下:

header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename");
echo $content;
exit;

檔案格式介紹:

格式 Type 相隔符號
CSV text/x-csv ,
SXW application/octet-stream
Word application/msword
Excel .xls application/vnd.ms-excel \t
Excel .xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Incomplete list of MIME types


編碼轉換:

中文的話得考慮編碼轉換:

(1) iconv ( “原編碼” , “新編碼” , “內容” );

echo iconv( "UTF-8", "Big5" , $content);

(2) mb_convert_encoding( “內容” , “新編碼” , “原編碼”);

echo mb_convert_encoding($content , "Big5" , "UTF-8");

實作範例:

CSV (using ,)

<?php 
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=output.csv");
$content = "Column1,tColumn2\nnRow1-1,nRow1-2";
$content = mb_convert_encoding($content , "Big5" , "UTF-8");
echo $content;
exit;
?>

CSV不想硬刻的話可以使用fputcsv()

EXCEL (using \t)

<?php 
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:filename=exportFileName.xls");
$content = "Column1\tColumn2\tColumn3\nRow1-1\tRow1-2\tRow1-3";
$content = mb_convert_encoding($content , "Big5" , "UTF-8");
echo $content;
exit;
?>

\n: 共用換行; ,: CSV分隔號; \t: XLS分隔號

到此應該會發現,EXCEL基礎不適合硬幹,開發請參考以下套件。


現成套件

CSV

完整CSV Helper可以參考:https://github.com/yidas/php-helpers/blob/master/src/CSV.php (包含過濾)

EXCEL

PHPSpreadSheet Helper快速開發Excel:https://github.com/yidas/phpspreadsheet-helper (PHPExcel & PHPSpreadSheet基底)

Leave a Reply

Your email address will not be published. Required fields are marked *