8-8 import.php

<?php
/*-----------引入檔案區--------------*/
include_once "header.php";
$xoopsOption['template_main'] = "snews_index.tpl";
include_once XOOPS_ROOT_PATH . "/header.php";

/*-----------function區--------------*/
function import_excel(){
    global $xoopsDB;

    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');
    }

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

    $myts = MyTextSanitizer::getInstance();
    $tbl    = $xoopsDB->prefix('snews');

    for ($row = 3; $row <= $highestRow; $row++) {
        $v = array();
        //讀取一列中的每一格
        for ($col = 0; $col < 10; $col++) {
            $cell=$sheet->getCellByColumnAndRow($col, $row);
            $val=get_value_of_cell($cell);
            if($col == 0 and !is_numeric($val)){
                break 2;
            }
            $v[$col] = $myts->addSlashes($val);
        }
        //寫入資料庫
        $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);
    }

}


//針對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;
}

/*-----------執行動作判斷區----------*/
include_once $GLOBALS['xoops']->path('/modules/system/include/functions.php');
$op = system_CleanVars($_REQUEST, 'op', '', 'string');

switch ($op) {
    case "import_excel":
        import_excel();
        header("location: index.php");
        exit;

    default:
        $op = "import_form";        
        break;
}

/*-----------秀出結果區--------------*/
$xoopsTpl->assign('op', $op);
$xoopsTpl->assign("toolbar", toolbar_bootstrap($interface_menu));
include_once XOOPS_ROOT_PATH . '/footer.php';