PHP 匯出 Excel

由於業務需要，需要在後臺新增匯出Excel的功能。所以很自然的想到了PHPExcel，於是就開始動手了。
由於之前沒有做過，所以基本都是借鑑網上大佬的。

檢查是否有PHPExcel包，如果沒有，請在網上下好。
2.開始寫程式碼了。需要注意得是，一般匯出資料，資料量都會較大。所以需要設定記憶體使用量和，超時時間。並且需要注意匯出的資料會被Excel改寫，如身份證號，手機號，時間戳等長數字會被顯示為科學計數法，時間格式也顯示為Excel的預設顯示格式。脾氣是大得很吶....惹不起惹不起。所以經過查資料尋得PHPExcel中有個方法（setCellValueExplicit）是可以設定匯出單元格格式為文字。還有種方法是獲取資料時做處理，後面會講到。

/**\
* 匯出Excel檔案 速度慢
* @param $fileName 匯出的檔名
* @param $headArr 資料頭
* @param $data 匯出資料
*/
function getExcel($fileName,$headArr,$data){
  //設定PHP最大單執行緒的獨立記憶體使用量
  ini_set('memory_limit','1024M');
  //程式超時設定設為不限時
  ini_set('max_execution_time ','0');

  //匯入PHPExcel類庫，因為PHPExcel沒有用名稱空間，所以使用vendor匯入
  vendor("PHPExcel.PHPExcel.IOFactory");
  vendor("Excel.PHPExcel");
  vendor("Excel.PHPExcel.Writer.Excel5");
  vendor("Excel.PHPExcel.IOFactory.php");

  //對資料進行檢驗
  if(empty($data) || !is_array($data)){
      die("data must be a array");
  }
  //檢查檔名
  if(empty($fileName)){
      exit;
  }
  $date = date("Y_m_d",time());
  $fileName .= "_{$date}.xls";
  //建立PHPExcel物件
  $objPHPExcel = new \PHPExcel();

  //設定表頭
  $key = ord("A");
  foreach($headArr as $hkey => $v){
      $colum = chr($key);
      $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
      $key += 1;
      unset($headArr[$hkey]);
  }
  $column = 2;
  $objActSheet = $objPHPExcel->getActiveSheet();
  foreach($data as $key => $rows){ //行寫入
      $span = ord("A");
      foreach($rows as $keyName=>$value){// 列寫入
          $j = chr($span);
          //設定匯出單元格格式為文字，避免身份證號的資料被Excel改寫
          $objActSheet->setCellValueExplicit($j.$column, $value);
          $span++;
          unset($rows[$keyName]);
      }
      $column++;
      unset($data[$key]);
  }
  $fileName = iconv("utf-8", "gb2312", $fileName);
  //重新命名錶
  // $objPHPExcel->getActiveSheet()->setTitle('test');
  //設定活動單指數到第一個表,所以Excel開啟這是第一個表
  $objPHPExcel->setActiveSheetIndex(0);
  ob_end_clean();
  ob_start();
  header('Content-Type: application/vnd.ms-excel');//定義輸出的檔案型別為excel檔案
  header("Content-Disposition: attachment;filename=\"$fileName\"");//定義輸出的檔名
  header('Cache-Control: max-age=0');//強制每次請求直接傳送給源伺服器，而不經過本地快取版本的校驗。
  $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter->save('php://output'); //檔案通過瀏覽器下載
  exit;

}

3.呼叫實現,此處用的是thinkPHP。如框架不同，請自行修改語法。

public function exportexcel(){
    $sqlWhere = session('H5OPERATIONLOG_TIME_WHERE');
    $data = M(self::T_TABLE)->where('create_time'.$sqlWhere)->select();//匯出資料
    $filename="XXXXX";//匯出檔名
    $headArr=array("XXX","XXX1","XXX2");//資料頭
    getExcel($filename,$headArr,$data);
}

<td><a href="你的exportexcel訪問路徑"><input type="button" value="匯出Excel"></input></a></td>

以上借鑑於：https://www.cnblogs.com/HoverM/p/6210178.h...

4.原本以為這樣完事了。（事實上這樣也算完事了）但是後來覺得，貌似匯出速度有點慢。就找了時間再深入研究了一下。於是找到了另一種比較好的方法。-------匯出CSV--------

5.CSV概念

CSV是一種通用的、相對簡單的檔案格式，被使用者、商業和科學廣泛應用。最廣泛的應用是在程式之間轉移表格資料，而這些程式本身是在不相容的格式上進行操作的（往往是私有的和/或無規範的格式）。因為大量程式都支援某種CSV變體，至少是作為一種可選擇的輸入/輸出格式。

6.因為CSV通常也是使用Excel檢視。所以不可避免的，還是需要注意一些Excel的限制。

表資料限制：

Excel 2003及以下的版本。一張表最大支援65536行資料，256列。

Excel 2007-2010版本。一張表最大支援1048576行，16384列。

7.PHPExcel於CSV需要注意的地方

PHPexcel記憶體溢位：
既然資料限制在104W，那麼資料分割就資料分割唄，於是你嘗試50W一次匯入表，然而PHPexcel內部有函式報記憶體溢位錯誤，然後你就不斷的調小資料量，直到5W一次匯入你都會發現有記憶體溢位錯誤。這是為什麼呢，雖然你分割資料來匯入多個資料表，但是最後PHPexcel內部還是一次性把所有表資料放進一個變數中來建立檔案……額，這幾百萬資料一個變數儲存，你想記憶體不溢位，還真有點困難。
（後來看了一些文章發現PHPExcel也有解決方案，PHPExcel_Settings::setCacheStorageMethod方法更改緩衝方式來減小記憶體的使用）

CSV輸出buffer過多：
當你用PHP原生函式putcsv()其實就使用到了輸出快取buffer，如果你把幾百萬的資料一直用這個函式輸出，會導致輸出快取太大而報錯的，因此我們每隔一定量的時候，必須進行將輸出快取中的內容取出來，設定為等待輸出狀態。具體操作是：
ob_flush();
flush();

然後需要解決的就是，當資料量達到百萬以上是。不可避免的就需要面對Excel的顯示資料問題以及大量資料匯出的問題。這個問題也是我在目前為止並沒有解決的。參考文章中有說道一個方案就是使用yield生成器以及分段生成CSV檔案然後再統一打包下載。如果有大神有這方面經驗，還請不吝賜教。

文章出處：https://blog.csdn.net/qq_16142851/article/...

8.具體實現程式碼如下，需要注意的是。匯出CSV的方法並不像PHPExcel一樣提供了轉文字格式的方法。所以這裡需要在傳入資料是做資料處理。具體方法為：在需要處理的資料後面拼接製表符："/t"。一定要雙引號。

    /**
     * 匯出CSV資料處理
     * 待優化項：如果匯出資料達到百萬以上，需要做分批匯出CSV檔案再新增到壓縮檔案統一打包下載
     * @desc 資料匯出到csv(csv檔案)
     * @param string $filename 檔名稱
     * @param array $tileArray 所有列名稱
     * @param array $dataArray 所有列資料
     */
    function exportToCsv($filename, $tileArray=array(), $dataArray=array()){
        //設定PHP最大單執行緒的獨立記憶體使用量
        ini_set('memory_limit','1024M');
        //程式超時設定設為不限時
        ini_set('max_execution_time ','0');
        ob_end_clean();
        ob_start();
        header("Content-Type: text/csv");
        $filename .= date("Y-m-d").".csv";
        header("Content-Disposition:filename=".$filename);
        $fp=fopen('php://output','w');
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));//轉碼 防止亂碼
        fputcsv($fp,$tileArray);
        $index = 0;
        foreach ($dataArray as $item) {
            if($index==1000){
                $index=0;
                ob_flush();
                flush();
            }
            $index++;
            fputcsv($fp,$item);
        }

        ob_flush();
        flush();
        ob_end_clean();
    }

    public function exportexcel(){
          $sqlWhere = session('H5OPERATIONLOG_TIME_WHERE');
          $data = M(self::T_TABLE)->where('create_time'.$sqlWhere)->select();
          foreach ($data as $key => $row) {
              $data[$key]['create_time'] = $row['create_time']."\t";//加"\t"形成文字格式，原樣輸出
          }
         $filename="XXXXX";//匯出檔名
         $headArr=array("XXX","XXX1","XXX2");//資料頭
         exportToCsv($filename,$headArr,$data);
    }

    <td><a href="你的exportexcel訪問路徑"><input type="button" value="匯出Excel"></input></a></td>

    //此地方的出處找不到了，希望原作看到不要揍我。狗頭保命ing......

9.CSV在Excel中展示會有點小問題

PHP 匯出 Excel

解決方案：SYLK 檔案時一個文字檔案，開頭的為“ID”或“ID_XXXX”（其中XXXX是文字字串）。
SYLK 檔案的第一個的記錄是在 ID_Number 記錄的。
Excel 將識別該文字在文字檔案開頭時, 它會將該檔案解釋為 SYLK 檔案。
Excel 將嘗試從該 SYLK 格式轉換該檔案，但不能這樣做，因為"ID"字元後不有任何有效的 SYLK 程式碼。
因為 Excel 不能轉換該檔案，您收到錯誤訊息。

當您開啟一個文字檔案、 CSV 檔案和檔案的前兩個字元是大寫字母"I"，"D"時，會發生此問題。
例如文字檔案可能包含以下文字：
ID, STATUS 123, open 456, closed
如果前兩個字母小寫"i"和"d"不會發生此問題的。

參考：https://www.cnblogs.com/A2008A/archive/201...

至此也就完成了。原本匯出1W+條資料，PHPExcel需要8秒。現在換成CSV方法只需要兩秒，也算能滿足日常的運營需求了。當然，可以優化的地方還有很多。歡迎指正修改。

PHP 匯出 Excel

相關文章