PHP PHPExcel_Reader_Excel5::canRead方法代码示例

 /**
  * 获取可以读取类的phpexcel对象
  * @return PHPExcel_Reader_Excel2007
  */
 private function _getReadFileObj()
 {
     self::getInstance2007();
     if (!self::$_excelObj2007->canRead($this->_filePath)) {
         self::getInstance5();
         if (!self::$_execelObj5->canRead($this->_filePath)) {
             return false;
         } else {
             $this->_instance = self::$_execelObj5;
         }
     } else {
         $this->_instance = self::$_excelObj2007;
     }
 }

 public function reader($filePath = null, $index = 2)
 {
     if ($filePath == null) {
         $filePath = $_FILES['file']['tmp_name'];
     }
     $PHPReader = new PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             return 'no Excel';
         }
     }
     $PHPExcel = $PHPReader->load($filePath);
     $rows = array();
     $sheet = $PHPExcel->getSheet(0);
     $allColumn = $sheet->getHighestColumn();
     $allRow = $sheet->getHighestRow();
     $allCell = $this->AT[$allColumn];
     for ($row = $index; $row <= $allRow; $row++) {
         $arr = array();
         for ($cell = 0; $cell <= $allCell; $cell++) {
             $val = $sheet->getCellByColumnAndRow($cell, $row)->getValue();
             $arr[$this->A[$cell]] = $val;
         }
         $rows[] = $arr;
     }
     return $rows;
 }

function updateData($filename, $encode = 'utf-8')
{
    $PHPReader = new PHPExcel_Reader_Excel2007();
    if (!$PHPReader->canRead($filename)) {
        $PHPReader = new PHPExcel_Reader_Excel5();
        if (!$PHPReader->canRead($filename)) {
            echo 'no Excel';
            return;
        }
    }
    $PHPExcel = $PHPReader->load($filename);
    $currentSheet = $PHPExcel->getSheet(0);
    $allColumn = $currentSheet->getHighestColumn();
    $allRow = $currentSheet->getHighestRow();
    $excelData = ['name' => $currentSheet->getCellByColumnAndRow(A, 1)->getValue(), 'version' => $currentSheet->getCellByColumnAndRow(A, 2)->getValue()];
    $keys = [];
    for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
        $keys[$currentColumn] = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, 5)->getValue();
    }
    for ($currentRow = 6; $currentRow <= $allRow; $currentRow++) {
        $tmp = [];
        for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
            $tmpv = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
            $tmp[$keys[$currentColumn]] = in_array($keys[$currentColumn], ['Id', 'Exp', 'Hp', 'Attack', 'Defense']) ? intval($tmpv) : floatval($tmpv);
        }
        if (!empty($tmp)) {
            $excelData['list'][] = $tmp;
        }
    }
    return $excelData;
}

 /**
  * 导入代理商信息
  * @method import
  */
 public function import()
 {
     $file_info = R('Public/UploadExcel');
     //上传excel并返回上传路径
     if (!file_exists($file_info)) {
         //上传失败则输出
         $error_info['status'] = 0;
         $error_info['info'] = $file_info;
         die(json_encode($error_info));
     }
     import('Common.Tools.PHPExcel.PHPExcel');
     $PHPExcel = new \PHPExcel();
     $PHPReader = new \PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($file_info)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
     }
     if (!$PHPReader->canRead($file_info)) {
         $error_info['status'] = 0;
         $error_info['info'] = '无法读取文件';
         die(json_encode($error_info));
     }
     $PHPExcel = $PHPReader->load($file_info);
     $currentSheet = $PHPExcel->getSheet(0);
     $rowTotal = $currentSheet->getHighestRow();
     //总行数
     $columnTotal = $currentSheet->getHighestColumn();
     //总列数
     $columnTotal = \PHPExcel_Cell::columnIndexFromString($columnTotal);
     //字母列转换为数字列
     $_list = array();
     for ($i = 1; $i <= $rowTotal; $i++) {
         for ($j = 0; $j <= $columnTotal; $j++) {
             $val = $currentSheet->getCellByColumnAndRow($j, $i)->getValue();
             if (!empty($val)) {
                 $_list[$i - 1][] = $val;
             }
         }
     }
     foreach ($_list as $_k => $_v) {
         $list[$_k]['name'] = $_v[0];
         $list[$_k]['mobile'] = $_v[1];
         $list[$_k]['id_card'] = $_v[2];
         $list[$_k]['wechat_number'] = $_v[3];
         $list[$_k]['class'] = $_v[4];
         $list[$_k]['authorize_code'] = $_v[5];
         $list[$_k]['ctime'] = now();
         $list[$_k]['mtime'] = now();
     }
     $result = D('Agent')->addAll($list);
     if ($result) {
         $error_info['status'] = 1;
         $error_info['info'] = '导入成功';
     } else {
         $error_info['status'] = 0;
         $error_info['info'] = '导入数据失败';
     }
     die(json_encode($error_info));
 }

 public static function readExcel($filePath)
 {
     self::init();
     $PHPReader = new PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             return null;
         }
     }
     return $PHPReader->load($filePath);
 }

 /**
  * 
  *函数名：		exportToExcelWithHeader
  *输入：		模板excel的路径 $filePath，例如 'C:\\123.xls'
  *输入：		二维数组  要导出的内容 $dataList
  * 			array(
  *				array(key1=>$value1,key2=>$value2,...),
  *				array(key1=>$value1,key2=>$value2,...),...
  *			)
  *输入：		一维数组  生成excel的头部信息 $headerList 
  *			array(数据库前台字段key1，数据库前台字段key2,...)
  *输入：		string $OutputFileName 输出文件名(建议输出文件名带时间戳)
  *输入：		$outputType 默认0输出到浏览器，输入其他保存到文件服务器并成功返回相对地址，失败返回NULL
  *创建人：          石昌民
  *创建时间：       2015-9-30
  */
 public function exportToExcelWithHeader($filePath, $dataList, $headerList, $OutputFileName, $outputType = 0)
 {
     require './ThinkPHP/Library/Org/PHPExcel/PHPExcel.php';
     //实例化Excel读取类
     $PHPReader = new \PHPExcel_Reader_Excel2007();
     $fileWebService = new FileWebController($this->userID);
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new \PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             return false;
         }
     }
     //读取Excel
     $PHPExcel = $PHPReader->load($filePath);
     /**读取excel文件中的第一个工作表*/
     $currentSheet = $PHPExcel->getSheet(0);
     /**取得一共有多少行*/
     $allRow = $currentSheet->getHighestRow();
     $allRow++;
     //实例化Excel写入类
     $PHPWriter = new \PHPExcel_Writer_Excel5($PHPExcel);
     //遍历数据列表中的内容,从第allRow行开始
     $i = $allRow;
     foreach ($dataList as $key => $value) {
         $position = 'A';
         foreach ($headerList as $headerKey => $headerValue) {
             $currentSheet->setCellValueExplicit("{$position}" . $i, $dataList[$key][$headerValue], \PHPExcel_Cell_DataType::TYPE_STRING);
             //headerKey为数据库前台字段
             $position++;
         }
         $i++;
     }
     ob_end_clean();
     //清除缓冲区,避免乱码
     //决定是导出到浏览器还是文件服务器
     if ($outputType !== 0) {
         $path = Constant::WEBSERVER_EXCEL_SAVEPATH . "{$OutputFileName}.xls";
         $PHPWriter->save($path);
         // 					dump($path);
         // 					dump(file_exists($path));
         $fileURL = $fileWebService->uploadFile($path, "{$OutputFileName}.xls");
         if ($fileURL == false) {
             $fileURL = null;
         }
         return $fileURL;
         return false;
     } else {
         header('Content-Type: application/vnd.ms-excel');
         header("Content-Disposition: attachment;filename={$OutputFileName}.xls");
         header('Cache-Control: max-age=0');
         $PHPWriter->save('php://output');
     }
 }

 public function loadExcel($file)
 {
     //         $PHPExcelReader = new PHPExcel_Reader_Excel2007();
     //         //为了可以读取所有版本Excel文件(读2007的报错)
     //         if(!$PHPExcelReader->canRead($file))
     //         {
     $PHPExcelReader = new PHPExcel_Reader_Excel5();
     if (!$PHPExcelReader->canRead($file)) {
         return;
     }
     //         }
     $this->excelReaderObj = $PHPExcelReader->load($file);
 }

 function excel($postdata)
 {
     require_once 'Excel_reader.php';
     require_once 'Classes/PHPExcel.php';
     $tmp = $postdata;
     $save_path = "./";
     //暂时上传的文件路径
     $file_name = $save_path . date('Ymdhis') . ".xls";
     //上传后的文件保存路径和名称
     if (copy($tmp, $file_name)) {
     }
     $PHPExcel = new PHPExcel();
     $PHPReader = new PHPExcel_Reader_Excel2007();
     //为了可以读取所有版本Excel文件
     if (!$PHPReader->canRead($file_name)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($file_name)) {
             echo '未发现Excel文件！';
             return;
         }
     }
     //不需要读取整个Excel文件而获取所有工作表数组的函数，感觉这个函数很有用，找了半天才找到
     $sheetNames = $PHPReader->listWorksheetNames($file_name);
     //读取Excel文件
     $PHPExcel = $PHPReader->load($file_name);
     //获取工作表的数目
     $sheetCount = $PHPExcel->getSheetCount();
     for ($i = 0; $i < $sheetCount; $i++) {
         //选择第一个工作表
         $currentSheet = $PHPExcel->getSheet($i);
         //取得一共有多少列
         $allColumn = $currentSheet->getHighestColumn();
         //取得一共有多少行
         $allRow = $currentSheet->getHighestRow();
         for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
             for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                 $address = $currentColumn . $currentRow;
                 $data[$i][$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
                 //echo ."\t";
             }
         }
         //$data[$i]=$currentSheet;
     }
     unlink($file_name);
     //删除上传的文件
     return $data;
 }

 public function get_rows_from_excel($filepath, &$infos)
 {
     if (empty($filepath)) {
         return $infos;
     }
     $php_reader = new PHPExcel_Reader_Excel2007();
     if (!$php_reader->canRead($filepath)) {
         $php_reader = new PHPExcel_Reader_Excel5();
         if (!$php_reader->canRead($filepath)) {
             return $infos;
         }
     }
     $cur_excel = $php_reader->load($filepath);
     $cur_sheet = $cur_excel->getSheet(0);
     $all_column = $cur_sheet->getHighestColumn();
     //取得一共有多少列
     $all_row = $cur_sheet->getHighestRow();
     //取得一共有多少行
     $infos_have = array();
     $infos_error = array();
     $i = 0;
     $j = 0;
     for ($cur_row = 1; $cur_row <= $all_row; $cur_row++) {
         $flagadd = FALSE;
         $tmprow = array();
         $tmpchar = '';
         for ($cur_column = 'A'; $cur_column <= $all_column; $cur_column++) {
             $address = $cur_column . $cur_row;
             $curinfo = trim($cur_excel->getActiveSheet()->getCell($address)->getvalue());
             //if($cur_column == 'A')
             //{
             if (!empty($curinfo)) {
                 $flagadd = TRUE;
             }
             //}
             $tmprow[] = $curinfo;
             $tmpchar .= $curinfo;
         }
         if ($flagadd) {
             $infos['infos_have'][$i] = $tmprow;
             $i++;
         } elseif (!empty($tmpchar)) {
             $infos['infos_error'][$j] = $tmprow;
             $j++;
         }
     }
 }

 public function index()
 {
     // 		$xq_list = $this->manage_model->get_all_xiaoqu_list();
     // 		foreach ($xq_list as $xq) {
     // 			$name =  str_replace("•","",$xq['name']);
     // 			//echo $xq['id'] . ": " . $this->encode($name) . '<br>';
     // 			$xq['jianpin'] = $this->encode($name);
     // 			$this->manage_model->update_xiaoqu_jianpin($xq);
     // 		}
     // 		die('DONE');
     require_once APPPATH . 'libraries/PHPExcel/PHPExcel.php';
     $PHPExcel = new \PHPExcel();
     $PHPReader = new \PHPExcel_Reader_Excel2007();
     $file = 'E:/broker.xlsx';
     //为了可以读取所有版本Excel文件
     if (!$PHPReader->canRead($file)) {
         $PHPReader = new \PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($file)) {
             echo '未发现Excel文件！';
             return;
         }
     }
     //不需要读取整个Excel文件而获取所有工作表数组的函数，感觉这个函数很有用，找了半天才找到
     $sheetNames = $PHPReader->listWorksheetNames($file);
     //读取Excel文件
     $PHPExcel = $PHPReader->load($file);
     //获取工作表的数目
     $sheetCount = $PHPExcel->getSheetCount();
     //选择第一个工作表
     $currentSheet = $PHPExcel->getSheet(0);
     //取得一共有多少列
     $allColumn = $currentSheet->getHighestColumn();
     //取得一共有多少行
     $allRow = $currentSheet->getHighestRow();
     //循环读取数据，默认编码是utf8，这里转换成gbk输出
     $data = array();
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         $name = trim($currentSheet->getCell("A" . $currentRow)->getValue());
         $tel = trim($currentSheet->getCell("B" . $currentRow)->getValue());
         $data[] = array('username' => $tel, 'passwd' => sha1('888888'), 'tel' => $tel, 'company_name' => "顺达地产", 'rel_name' => $name, 'region_id' => 6, 'admin_group' => 2);
     }
     $this->manage_model->add_broker_batch($data);
 }

function format_excel2array($filePath = '', $sheet = 0)
{
    if (empty($filePath) or !file_exists($filePath)) {
        die('file not exists');
    }
    $PHPReader = new PHPExcel_Reader_Excel2007();
    //建立reader对象
    if (!$PHPReader->canRead($filePath)) {
        $PHPReader = new PHPExcel_Reader_Excel5();
        if (!$PHPReader->canRead($filePath)) {
            echo 'no Excel';
            return;
        }
    }
    $PHPExcel = $PHPReader->load($filePath);
    //建立excel对象
    $currentSheet = $PHPExcel->getSheet($sheet);
    //**读取excel文件中的指定工作表*/
    $allColumn = $currentSheet->getHighestColumn();
    //**取得最大的列号*/
    $allRow = $currentSheet->getHighestRow();
    //**取得一共有多少行*/
    $data = array();
    for ($rowIndex = 1; $rowIndex <= $allRow; $rowIndex++) {
        //循环读取每个单元格的内容。注意行从1开始，列从A开始
        for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) {
            $addr = $colIndex . $rowIndex;
            $cell = $currentSheet->getCell($addr)->getValue();
            if ($cell instanceof PHPExcel_RichText) {
                //富文本转换字符串
                $cell = $cell->__toString();
            }
            $data[$rowIndex][$colIndex] = $cell;
        }
    }
    return $data;
}

 public function import($filePath)
 {
     $this->__construct();
     $PHPExcel = new PHPExcel();
     /**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
     $PHPReader = new PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             echo 'no Excel';
             return;
         }
     }
     $PHPExcel = $PHPReader->load($filePath);
     $currentSheet = $PHPExcel->getSheet(0);
     //读取excel文件中的第一个工作表
     $allColumn = $currentSheet->getHighestColumn();
     //取得最大的列号
     $allRow = $currentSheet->getHighestRow();
     //取得一共有多少行
     $erp_orders_id = array();
     //声明数组
     /**从第二行开始输出，因为excel表中第一行为列名*/
     for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
         /**从第A列开始输出*/
         for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
             $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
             /**ord()将字符转为十进制数*/
             //if($val!=''){
             $erp_orders_id[] = $val;
             //}
             /**如果输出汉字有乱码，则需将输出内容用iconv函数进行编码转换，如下将gb2312编码转为utf-8编码输出*/
             //echo iconv('utf-8','gb2312', $val)."\t";
         }
     }
     return $erp_orders_id;
 }

 /**
  * ShipfeeQueryAct::actBatchShipfeeQueryImport()
  * 批量运费验证信息导入导出
  * @return array 
  */
 public function actBatchShipfeeQueryImport()
 {
     $data = array();
     $uid = intval($_SESSION[C('USER_AUTH_SYS_ID')]);
     if (isset($_FILES['upfile']) && !empty($_FILES['upfile'])) {
         $fielName = $uid . "_batch_shipfee_" . date('YmdHis') . '_' . rand(1, 3009) . ".xls";
         $fileName = WEB_PATH . 'html/temp/' . $fielName;
         if (move_uploaded_file($_FILES['upfile']['tmp_name'], $fileName)) {
             $filePath = $fileName;
         }
     }
     if (substr($filePath, -3) != 'xls') {
         show_message($this->smarty, "导入的文件名格式错误！", "index.php?mod=shipfeeQuery&act=shipfeeQueryImport");
         @unlink($filePath);
         exit;
     }
     //读取导入文件
     require_once WEB_PATH . "lib/PHPExcel.php";
     //如果读取的表较大，需要调整内存和时间限制
     ini_set('memory_limit', '20M');
     // ini_set('max_execution_time', '2');
     $PHPExcel = new PHPExcel();
     $PHPReader = new PHPExcel_Reader_Excel2007();
     if (!$PHPReader->canRead($filePath)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($filePath)) {
             show_message($this->smarty, "文件内容无法读取！", "index.php?mod=shipfeeQuery&act=shipfeeQueryImport");
             @unlink($filePath);
             exit;
         }
     }
     $PHPExcel = $PHPReader->load($filePath);
     $currentSheet = $PHPExcel->getSheet(0);
     //取得共有多少列,若不使用此静态方法，获得的$col是文件列的最大的英文大写字母
     $cols = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn());
     $rows = $currentSheet->getHighestRow();
     if ($rows > C('EXCELL_ROWS_MAX')) {
         show_message($this->smarty, "单个EXCELL文件行数超过系统处理最大值: <b>" . C('EXCELL_ROWS_MAX') . "</b> 行!", "index.php?mod=shipfeeQuery&act=shipfeeQueryImport");
         @unlink($filePath);
         exit;
     }
     $row = 1;
     $res = array();
     while (1) {
         $flag = true;
         $rowFlag = true;
         $country = '';
         $weight = 0;
         $tracknum = '';
         $carrier = '';
         $carrierId = 0;
         $fee = 0;
         $totalFee = 0;
         $carrierNew = '';
         $aa = 'A' . $row;
         $bb = 'B' . $row;
         $cc = 'C' . $row;
         $dd = 'D' . $row;
         $ee = 'E' . $row;
         $ff = 'F' . $row;
         $gg = 'G' . $row;
         $country = post_check(trim($currentSheet->getCell($aa)->getValue()));
         $tracknum = post_check(trim($currentSheet->getCell($bb)->getValue()));
         $weight = post_check(trim($currentSheet->getCell($cc)->getValue()));
         $carrier = post_check(trim($currentSheet->getCell($dd)->getValue()));
         $carrierNew = post_check(trim($currentSheet->getCell($ee)->getValue()));
         $totalFee = post_check(trim($currentSheet->getCell($ff)->getValue()));
         $fee = post_check(trim($currentSheet->getCell($gg)->getValue()));
         if (empty($country)) {
             break;
         }
         if ($row == 1) {
             if ($country != '国家' || $tracknum != '挂号条码' || $weight != '重量' || $carrier != '运输方式' || $carrierNew != '最优运输方式' || $totalFee != '总运费' || $fee != '折扣运费') {
                 show_message($this->smarty, '<font color="red">文件导入失败，导入模版内容有误,请勿修改表头</font>', "index.php?mod=shipfeeQuery&act=shipfeeQueryImport");
                 @unlink($filePath);
                 exit;
             }
             $res[] = array("国家", "挂号条码", "重量", "运输方式", "最优运输方式", "总运费", "折扣运费");
         } else {
             if (!empty($tracknum)) {
                 $carrierId = self::getErpCarrierId($carrier);
             }
             if (empty($carrierId)) {
                 $fees = TransOpenApiAct::act_getBestCarrierNew(1, $country, $weight);
             } else {
                 $fees = TransOpenApiAct::act_fixCarrierQueryNew($carrierId, $country, $weight);
             }
             if (empty($fees)) {
                 self::$errMsg .= "运费校验失败：{$carrier}==={$country}==={$weight}！<br/>";
                 $res[] = array($country, $tracknum, $weight, $carrier, '', '', '');
             } else {
                 $fee = $fees['fee'];
                 $totalFee = $fees['totalFee'];
                 $carrierNew = self::getErpCarrierId($fees['carrierId'], true);
                 $res[] = array($country, $tracknum, $weight, $carrier, $carrierNew, $totalFee, $fee);
//.........这里部分代码省略.........

    public function index()
    {
        //提交的配置
        $config = $_POST['config'];
        $filePath = AMANGO_FILE_ROOT . $config['excel_path'];
        $currentid = $config['excel_readxls'] >= 1 ? $config['excel_readxls'] - 1 : 0;
        $startid = $config['excel_currentrow'] >= 2 ? $config['excel_currentrow'] : 2;
        if ($filePath == AMANGO_FILE_ROOT) {
            $this->error('请选择文件');
        }
        $xlsparam = parse_config($_POST['config']['excel_param']);
        //导入第三方插件
        Amango_Addons_Import('PHPExcel.php', 'Excelimport');
        Amango_Addons_Import('PHPExcel/Reader/Excel2007.php', 'Excelimport');
        //自动生成唯一数据表名
        $table_name = 'addonsexcel' . str_replace('_', '', strtolower($config['excel_tablename']));
        $tablename = C('DB_PREFIX') . $table_name;
        $sql = <<<sql
\t\t\t\tSHOW TABLES LIKE '{$tablename}';
sql;
        $res = M()->query($sql);
        //判断表唯一  以便生成新表
        if (count($res) > 0) {
            $this->error('该表名已存在,请换个数据表名');
        } else {
            if (strpos($_POST['config']['excel_param'], ':') === false) {
                $this->error('生成数据表时,请务必填写字段读取配置,格式：字段名:读取列标识');
            } else {
                $fields = '';
                $field = array();
                foreach ($xlsparam as $key => $value) {
                    if (!empty($key)) {
                        $fields .= "`{$key}` text,";
                        $field[] = $key;
                    }
                }
                //生成数据表
                $sql = <<<sql
\t\t\t\tCREATE TABLE IF NOT EXISTS `{$tablename}` (
\t\t\t\t`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' ,
\t\t\t\t{$fields}
\t\t\t\tPRIMARY KEY (`id`)
\t\t\t\t)
\t\t\t\tENGINE=MyISAM
\t\t\t\tDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
\t\t\t\tCHECKSUM=0
\t\t\t\tROW_FORMAT=DYNAMIC
\t\t\t\tDELAY_KEY_WRITE=0
\t\t\t\t;
sql;
                $res = M()->execute($sql);
                if ($res === false) {
                    $this->error('建立数据表出错,请重新建表');
                }
            }
        }
        //导入数据表名 存入总表
        D('addonsexcel')->add(array('fileds' => implode(',', $field), 'tablename' => $tablename));
        $PHPExcel = new \PHPExcel();
        /**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($filePath)) {
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($filePath)) {
                $this->error('无法在' . $filePath . '路径下找到该文件');
            }
        }
        $PHPExcel = $PHPReader->load($filePath);
        /**读取excel文件中的第一个工作表*/
        $currentSheet = $PHPExcel->getSheet($currentid);
        /**取得最大的列号*/
        $allColumn = $currentSheet->getHighestColumn();
        /**取得一共有多少行*/
        $allRow = $currentSheet->getHighestRow();
        //初始化
        $rows = array();
        foreach ($xlsparam as $key => $value) {
            for ($currentRow = $startid; $currentRow <= $allRow; $currentRow++) {
                $rowinfo = $currentSheet->getCell(strtoupper($value) . $currentRow)->getValue();
                $rows[$currentRow][$key] = $config['excel_parxhtml'] == 1 ? strip_tags($rowinfo) : $rowinfo;
            }
        }
        //读取出总记录
        foreach ($rows as $key => $value) {
            M($table_name)->add($value);
        }
        M('addonsexcel')->where(array('tablename' => $tablename))->save(array('rows' => count($rows)));
        $this->success('新增成功', U('Addons/adminList', array('name' => 'Excelimport')));
    }

 /**
  * 生成订单附件
  * @param $order_data 要写入订单模板的数据数组
  * @param $attachment_path = '' 附件生成路径
  * @return string|bool
  */
 public static function create_order_attachment($order_data, $attachment_path = '')
 {
     if (!empty($order_data) && is_array($order_data)) {
         // 引入PHPExcel扩展
         Yii::createComponent('application.extensions.excel.PHPExcel');
         // 租车订单Excel附件 - 模板目录
         $templet_path = ASSETS_FILE . 'templet/tichedan.xls';
         // 租车订单Excel附件 - 正式目录
         $attachment_path = $attachment_path == '' ? SECRET_FILE_PATH . 'Carrental/' . date('Y-m-d', time()) . '/' : $attachment_path;
         try {
             // 正式目录不存在则创建
             if (!file_exists($attachment_path)) {
                 if (!DirectoryFile::dirCreate($attachment_path)) {
                     throw new Exception('创建目录失败！');
                 }
             }
             // 如模板文件存在则复制到正式目录下
             if (file_exists($templet_path)) {
                 // 生成附件名称
                 $attachment_name = 'order_' . time() . '_' . rand(100, 10000) . '.xls';
                 // 复制租车订单模板至正式目录下
                 $status = @copy($templet_path, $attachment_path . $attachment_name);
                 if (!$status) {
                     throw new Exception('模板文件移动失败！');
                 }
             } else {
                 throw new Exception('订单模板文件不存在！');
             }
             // 实例化Excel读取类
             $PHPReader = new PHPExcel_Reader_Excel5();
             if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
                 $PHPReader = new PHPExcel_Reader_Excel2007();
                 if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
                     throw new Exception('不是一个Excel文件！');
                 }
             }
             // 读取Excel工作表
             $PHPExcel = $PHPReader->load($attachment_path . $attachment_name);
             $currentSheet = $PHPExcel->getSheet(0);
             // 重写Excel工作表 将单项信息写入Excel单元格
             $currentSheet->setCellValue('B3', !empty($order_data[0]) ? $order_data[0] : '');
             // 预定号[reservation number]
             $currentSheet->setCellValue('D3', $order_data[1]);
             // 确认号码[confirmation number]
             $currentSheet->setCellValue('B5', $order_data[2]);
             // 租车人姓名[Name]
             $currentSheet->setCellValue('D5', $order_data[3]);
             // 航班号码[Flight number]
             $currentSheet->setCellValue('B19', $order_data[4]);
             // 车辆代码[Car Code]
             $currentSheet->setCellValue('D19', $order_data[5]);
             // 车门数[Doors]
             $currentSheet->setCellValue('D22', $order_data[6]);
             // 空调[Air Con]
             $currentSheet->setCellValue('D20', $order_data[7]);
             // 座位数[Car Seats]
             $currentSheet->setCellValue('B9', $order_data[8]);
             // 取车日期
             $currentSheet->setCellValue('B10', $order_data[9]);
             // 取车时间
             $currentSheet->setCellValue('B11', $order_data[10]);
             // 取车门店[Location]
             $currentSheet->setCellValue('B12', $order_data[11]);
             // 取车地址[Address]
             $currentSheet->setCellValue('B13', $order_data[12]);
             // 取车地电话[Tel]
             $currentSheet->setCellValue('D9', $order_data[13]);
             // 还车日期
             $currentSheet->setCellValue('D10', $order_data[14]);
             // 还车时间
             $currentSheet->setCellValue('D11', $order_data[15]);
             // 还车门店[Location]
             $currentSheet->setCellValue('D12', $order_data[16]);
             // 还车地址[Address]
             $currentSheet->setCellValue('D13', $order_data[17]);
             // 还车地电话[Tel]
             $currentSheet->setCellValue('B23', $order_data[18]);
             // 确认号码[confirmation number]
             $currentSheet->setCellValue('B18', $order_data[19]);
             // 车行名称Supplier
             $currentSheet->setCellValue('B20', $order_data[20]);
             // 车辆类型 Car Type
             $currentSheet->setCellValue('D21', $order_data[21]);
             //变速器类型transmission
             $currentSheet->setCellValueExplicit('B22', $order_data[22], PHPExcel_Cell_DataType::TYPE_STRING);
             //车行的账户号transmission
             $currentSheet->setCellValue('A26', $order_data[23]);
             //里程限制
             $currentSheet->setCellValue('A27', $order_data[24]);
             //车辆保险
             $currentSheet->setCellValue('A28', $order_data[25]);
             //车辆包含的税费
             $currentSheet->setCellValue('A30', $order_data[26]);
             //预定的额外服务
//.........这里部分代码省略.........