phpexcel 導入導出

//導出
function export_Excel($data,$title){
    $path = dirname(__FILE__); //找到目前腳本所在路徑
    $PHPExcel = new PHPExcel(); //執行個體化PHPExcel類，類似于在桌面上建立一個Excel表格
    $PHPSheet = $PHPExcel->getActiveSheet(); //獲得目前活動sheet的操作對象
    $PHPSheet->setTitle($title); //給目前活動sheet設定名稱
    //循環給表格指派(包括标題)
    foreach ($data as $key=>$val){
        foreach ($val as $k=>$v){
            $PHPSheet->setCellValue($k,$v);
        }
    }
    $PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');//按照指定格式生成Excel檔案，‘Excel2007’表示生成2007版本的xlsx，‘Excel5’表示生成2003版本Excel檔案
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告訴浏覽器輸出07Excel檔案
    //header('Content-Type:application/vnd.ms-excel');//告訴浏覽器将要輸出Excel03版本檔案
    header('Content-Disposition: attachment;filename='.$title.'.xlsx');//告訴浏覽器輸出浏覽器名稱
    header('Cache-Control: max-age=0');//禁止緩存
    $PHPWriter->save("php://output");
}
           

/**

 *  資料導入

 * @param string $file excel檔案

 * @param string $sheet

 * @return string   傳回解析資料

 * @throws PHPExcel_Exception

 * @throws PHPExcel_Reader_Exception

 */

function importExecl($filename='', $filedList = [], $isCutting = false, $size = 2000){
    $ext = pathinfo($filename, PATHINFO_EXTENSION);
    switch ($ext) {
        case 'xlsx':
            $objReader = PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($filename);//加載檔案
            break;
        case 'xls':
            $objReader = PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel = $objReader->load($filename);//加載檔案
            break;
        case 'csv':
            $objReader = PHPExcel_IOFactory::createReader('CSV');
            $objPHPExcel = $objReader->setInputEncoding('GBK')->load($filename);//加載檔案
            break;
        default:
            return [];
            break;
    }
    $highestRow = $objPHPExcel->getSheet()->getHighestRow();//取得總行數
    //$highestColumn = $objPHPExcel->getSheet()->getHighestColumn();//取得總列數
    $cellList = [];
    foreach ($filedList as $k => $v){
        $cellList[] = IntToChr($k);
    }

//    if ($cellList[count($filedList) - 1] != $highestColumn){
//        return [];
//    }
    $data = [];
    for ($i = 0; $i < $highestRow - 1; $i++) {
        for ($j = 0; $j < count($cellList); $j++) {
            $data[$i][$filedList[$j]] = $objPHPExcel->getActiveSheet()->getCell($cellList[$j] . ($i + 2))->getFormattedValue();
            if (is_object($data[$i][$filedList[$j]])) $data[$i][$filedList[$j]] = $data[$i][$filedList[$j]]->__toString();
            $data[$i][$filedList[$j]] = preg_replace("/(\s|\ \;|　|\xc2\xa0)/", "", $data[$i][$filedList[$j]]);
        }
    }
    if ($isCutting) return array_chunk($data, $size);
    return $data;
}
           

/**
 * 生成Excel列标
 * @param int $index 索引值
 * @param int $start 字母起始值
 * @return string 傳回字母
 */
function IntToChr($index, $start = 65)
{
    $str = '';
    if (floor($index / 26) > 0) {
        $str .= IntToChr(floor($index / 26) - 1);
    }
    return $str . chr($index % 26 + $start);
}