7. Excel的匯出與匯入

一、 匯出Excel

1. PHPExcel官網：http://phpexcel.codeplex.com
2. 線上API手冊：http://www.osakac.ac.jp/labs/koeda/tmp/phpexcel/Documentation/API/
3. 詳細使用範例（日文）：http://mizyupon-rpg.com/posts/719.html
4. 可讀取、產生Excel 97～2007的檔案，甚至可輸出PDF、CSV、HTML檔。
5. 安裝需求：PHP 5.2.0 以上、需開啟php_zip、php_xml、php_gd2函式庫。

   <?php
   include_once "header.php";
   require_once TADTOOLS_PATH . '/PHPExcel.php'; //引入 PHPExcel 物件庫
   require_once TADTOOLS_PATH . '/PHPExcel/IOFactory.php'; //引入 PHPExcel_IOFactory 物件庫
   $objPHPExcel = new PHPExcel(); //實體化Excel
   //----------內容-----------//
   header('Content-Type: application/vnd.ms-excel');
   header('Content-Disposition: attachment;filename=通訊錄.xls');
   header('Cache-Control: max-age=0');
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
   $objWriter->setPreCalculateFormulas(false);
   $objWriter->save('php://output');
   exit;

    

二、 常用方法：

1. 建立工作表並指定名稱

   $objPHPExcel->setActiveSheetIndex(0);  //設定預設顯示的工作表
   $objActSheet = $objPHPExcel->getActiveSheet(); //指定預設工作表為 $objActSheet
   $objActSheet->setTitle("通訊錄");  //設定標題
   $objPHPExcel->createSheet(); //建立新的工作表，上面那三行再來一次，編號要改

    

2. 指定儲存格內容，有以下類型可設定：TYPE_BOOL、TYPE_ERROR、TYPE_FORMULA、TYPE_INLINE、TYPE_NULL、TYPE_NUMERIC、TYPE_STRING

   $objActSheet->setCellValue("A1", '姓名')->setCellValue("B1", '電話');
   $objActSheet->setCellValueExplicit("C2", '0987654321',PHPExcel_Cell_DataType:: TYPE_STRING);

    

3. 調整儲存格欄寬

   $objActSheet->getColumnDimension('A')->setWidth(8);  //固定寬度8
   $objActSheet->getColumnDimension('B')->setAutoSize(true);  //自動寬度

    

4. 設定文字字型、粗細、顏色、儲存格背景顏色

   $objPHPExcel->getDefaultStyle()->getFont()->setName('微軟正黑體')->setSize(14);
   $objActSheet->getStyle('A1:K1')->getFont()->setBold(true)->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
   $objActSheet->getStyle('A1:K1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('00D7E2F2');

    

5. 合併儲存格

   $objActSheet->mergeCells("A10:K10")->setCellValue("A10", '資料數共計');

    

6. 插入公式

   $n=$i-1;
   $objActSheet->setCellValue("K{$i}", "=COUNTA(E2:E{$n})");

    

7. 保護儲存格

   $objActSheet->getProtection()->setSheet(true);
   $objActSheet->protectCells("C{$j}", 'password');

    

8. 對齊方向及自動換行，對齊的值有以下這些: HORIZONTAL_CENTER、 HORIZONTAL_CENTER_CONTINUOUS、 HORIZONTAL_GENERAL、 HORIZONTAL_JUSTIFY、 HORIZONTAL_LEFT、 HORIZONTAL_RIGHT、 VERTICAL_BOTTOM、 VERTICAL_CENTER、 VERTICAL_JUSTIFY、 VERTICAL_TOP

   $objActSheet->getStyle('C')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直置中對齊
   $objActSheet->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //水平置中對齊
   $objActSheet->getStyle('H')->getAlignment()->setWrapText(true); //自動換行

    

9. 邊框設定，getAllborders()、getTop()、getLeft()、getRight()、getBottom() 等位置，邊框的種類有：BORDER_DASHDOT、 BORDER_DASHDOTDOT、 BORDER_DASHED、 BORDER_DOTTED、 BORDER_DOUBLE、 BORDER_HAIR、 BORDER_MEDIUM、 BORDER_MEDIUMDASHDOT、 BORDER_MEDIUMDASHDOTDOT、 BORDER_MEDIUMDASHED、 BORDER_NONE、 BORDER_SLANTDASHDOT、 BORDER_THICK、 BORDER_THIN

   $objActSheet->getStyle("A1:K{$j}")->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setRGB('000000');

    

10. 把數字轉換為A、B、C欄位的方法：

    function num2alpha($n){
        for($r = ""; $n >= 0; $n = intval($n / 26) - 1)
            $r = chr($n%26 + 0x41) . $r;
        return $r;
    }

     

11. 利用欄、列位置順序寫入內容：

    $objActSheet->setCellValueByColumnAndRow($第N欄, $第N列 ,$內容);

     

三、 匯入Excel

1. 匯入的基本架構

   include_once TADTOOLS_PATH . '/PHPExcel/IOFactory.php';
   if (preg_match('/\.(xlsx)$/i', $file_name)) {
       $reader = PHPExcel_IOFactory::createReader('Excel2007');
   } else {
       $reader = PHPExcel_IOFactory::createReader('Excel5');
   }
   $PHPExcel   = $reader->load($file); // 檔案名稱
   $sheet      = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)
   $highestRow = $sheet->getHighestRow(); // 取得總列數

    

2. 讀取出每一列

   for ($row = 1; $row <= $highestRow; $row++) {
     for ($col = 0; $col <= 10; $col++) {
       $v=$sheet->getCellByColumnAndRow($col , $row) ;
       //格式檢查
       if( PHPExcel_Shared_Date::isDateTime( $v )){
         $val = PHPExcel_Shared_Date::ExcelToPHPObject( $v->getValue())->format('Y-m-d');
       }else{
         $val =  $v->getCalculatedValue();
       }
   
       if(!get_magic_quotes_runtime()) {
         $data[$col]=addSlashes($val);
       }
     }
     $sql = "insert into … 略 … values('{$v[0]}' , '{$v[1]}' …)";
     $xoopsDB->queryF($sql);
   }