8. Excel匯出匯入及點擊編輯

一、 匯出Excel

1. PHPExcel官網（舊）：https://github.com/PHPOffice/PHPExcel
2. PHPExcel API手冊：http://oweb1.osakac.ac.jp/labs/koeda/tmp/phpexcel/Documentation/API/elementindex.html
3. PhpSpreadsheet官網（新）：https://github.com/PHPOffice/PhpSpreadsheet
4. 舊專案將停止維護，但新專案PHP需5.6以上，為取得較好相容性，目前仍以舊專案為主
5. 可讀取、產生Excel 97～2007的檔案，甚至可輸出PDF、CSV、HTML檔。
6. 安裝需求：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);
   $objActSheet->setCellValueByColumnAndRow(3, 1, '測試'); //直欄從0開始，橫列從1開始

3. 調整儲存格欄寬

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

4. 設定橫列高度

   $objActSheet->getRowDimension($i)->setRowHeight(60);

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

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

6. 合併儲存格

   $objActSheet->mergeCells("A1:J1")->setCellValue("A1", '巷談集所有文章備份');

7. 插入公式

   $objActSheet->setCellValue("A{$i}", '文章數');
   $n = $i - 1;
   $objActSheet->setCellValue("B{$i}", "=COUNT(A3:A{$n})");

8. 保護儲存格

   $objActSheet->getProtection()->setSheet(true);
   $objActSheet->getProtection()->setSort(true);
   $objActSheet->getProtection()->setInsertRows(true);
   $objActSheet->getProtection()->setFormatCells(true);
   $objActSheet->getProtection()->setPassword('1234');
   

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

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

10. 邊框設定，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:J{$i}")->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->getColor()->setRGB('000000');
    

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

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

三、 匯入Excel

1. 先準備好匯入界面

   <h1 style="margin-top:60px;">匯入Excel</h1>
   <form action="index.php" method="post" class="form-inline"  enctype="multipart/form-data" style="margin: 60px;">
     <div class="form-group">
       <label for="userfile">請選擇 xls 檔</label>
       <input type="file"  name="userfile" class="form-control" id="userfile" placeholder="請選擇 xls 檔">
       <input type="hidden" name="op" value="import_excel">
     </div>
     <button type="submit" class="btn btn-default">匯入</button>
   </form>

2. 先根據上傳的檔名$_FILES['userfile']['name']來判斷匯入的檔案格式，以便選用正確的閱讀器

   include_once TADTOOLS_PATH . '/PHPExcel/IOFactory.php';
   if (preg_match('/\.(xlsx)$/i', $_FILES['userfile']['name'])) {
       $reader = PHPExcel_IOFactory::createReader('Excel2007');
   } else {
       $reader = PHPExcel_IOFactory::createReader('Excel5');
   }

3. 載入上傳檔案（$_FILES['userfile']['tmp_name']）並讀取內容，$highestRow讓我們知道檔案裡面有幾列

   $PHPExcel   = $reader->load($_FILES['userfile']['tmp_name']); // 檔案名稱
   $sheet      = $PHPExcel->getSheet(0); // 讀取第一個工作表(編號從 0 開始)
   $highestRow = $sheet->getHighestRow(); // 取得總列數

4. 利用兩個for迴圈，讀出每一格資料：從第3列開始讀（前兩列都是標題），讀到最後一列，接著再讀取欄，通常都會知道有幾欄要讀

   for ($row = 3; $row <= $highestRow; $row++) {
       $v = array();
       //讀取一列中的每一格
       for ($col = 0; $col <= 10; $col++) {
           //讀取資料
       }
       //寫入資料庫
       $sql = "insert into $tbl (`sn`, `focus`, `topic_sn`, `sort`, `title`, `content`, `username`, `create_time`, `update_time`, `uid`) values('{$v[0]}' , '{$v[1]}', '{$v[2]}', '{$v[3]}', '{$v[4]}', '{$v[5]}', '{$v[6]}', '{$v[7]}', '{$v[8]}', '{$v[9]}')";
       $xoopsDB->queryF($sql) or web_error($sql);
   }

5. 自動判斷格式函數

   //針對excel各種數據類型
   function get_value_of_cell($cell = "") {
   	if (is_null($cell)) {
   		$value = $cell->setIterateOnlyExistingCells(TRUE);
   	} else {
   		if (strstr($cell->getValue(), '=')) {
   			$value = $cell->getCalculatedValue();
   		} else if ($cell->getValue() instanceof PHPExcel_RichText) {
   			$value = $cell->getValue()->getPlainText();
   		} else if (PHPExcel_Shared_Date::isDateTime($cell)) {
   			//$value = $cell->getFormattedValue();
   			$value = PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue())->format('Y-m-d');
   		} else {
   			$value = $cell->getValue();
   		}
   	}
   	return $value;
   }

6. 讀取資料的方法：先判斷有無日期格式，若有，格式化儲存格為日期。若無，則讀出儲存格資料。由於最後要寫入資料庫，故寫入前還是得做資料的整理。

   if (PHPExcel_Shared_Date::isDateTime($sheet->getCellByColumnAndRow($col, $row))) {
       $val = PHPExcel_Shared_Date::ExcelToPHPObject($sheet->getCellByColumnAndRow($col, $row)->getValue())->format('Y-m-d H:i:s');
   } else {
       $val = $sheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
   }
   $v[$col] = $myts->addSlashes($val);

四、 直覺點擊編輯

1. 先在欲編輯的頁面（顯示函數中）套用以下語法

   include_once XOOPS_ROOT_PATH."/modules/tadtools/jeditable.php";
   $file="save.php";
   $jeditable = new jeditable();
   //此處加入欲直接點擊編輯的欄位設定
   $jeditable->render();

2. 「加入欲直接點擊編輯的欄位設定」共有三種欄位可以加入：

   //一般文字框
   $jeditable->setTextCol("#id名稱", $file, '140px', '12px', "{'sn':$sn,'op' : 'save'}", "點擊編輯");
   //大量文字框
   $jeditable->setTextAreaCol("#id名稱", $file, '500px', '150px', "{'sn':$sn,'op' : 'save'}", "點擊編輯");
   //下拉選單
   $jeditable->setSelectCol("#id名稱", $file, "{'值1':'選項文字1' , '值2':'選項文字2' , 'selected':'預設值'}", "{'sn' : $sn , 'op' : 'save'}", "點擊編輯");

3. 第一個參數是欲編輯的元件id，此id同時會送出給php當作變數名稱（類似name），所以，在樣板記得也要加上id設定：

   <p class="text-center" id="username"><{$snews.username}></p>

4. 第二個參數$file用來指定檔案，如save.php以用來儲存使用者輸入後的值，而save.php會接收到什麼呢？以上例來說，會接收到四個變數如下：
   • (1) $_POST['id'] => 'username';  //指定的id值
   • (2) $_POST['value'] => 'XXX';  //使用者輸入的值
   • (3) $_POST['sn'] => '2';   //額外傳送的變數
   • (4) $_POST['op'] => 'save';    //額外傳送的變數
5. 利用接收到的變數，可以撰寫save.php內容如下：

   <?php
   include "header.php";
   if (power_chk('', 1)) {
       $sql = "update " . $xoopsDB->prefix("snews") . " set `{$_POST['id']}`='{$_POST['value']}' where sn='{$_POST['sn']}'";
       $xoopsDB->queryF($sql);
       echo $_POST['value'];
   }

   • (1) save.php執行後所echo的值會出現在該元件中。
   • (2) 一樣記得要檢查權限才能改，否則會變成大漏洞。
6. 第三個參數則是欲帶到save.php的額外變數值，以{}包起來，每組變數的格式為「變數名稱:變數值」，每組變數用 , 隔開。
7. 下拉選單若是從資料庫撈出陣列，可利用json_encode($陣列,256) 函數將之變成所需選項格式。