static function importCartshop($tmp_file, $file, &$out) { global $CONFIG, $ST; $dir = 'shared/php/'; include "{$dir}PHPExcel.php"; include "{$dir}PHPExcel/IOFactory.php"; PHPExcel_Settings::setLocale('ru'); if (file_ext($file) == 'xlsx') { $objReader = new PHPExcel_Reader_Excel2007(); } elseif (file_ext($file) == 'xls') { $objReader = new PHPExcel_Reader_Excel5(); } $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($tmp_file); /*$objPHPExcel->setActiveSheetIndex(0); $objWorksheet = $objPHPExcel->getActiveSheet(); $keys=array(); $keys_flg=array('category_id'=>'id', 'parent_id'=>'parentid', 'name'=>'name', 'description'=>'description', 'image_name'=>'img', 'sort_order'=>'main_sort', ); $out=array(); foreach ($objWorksheet->getRowIterator() as $row) { $data=array(); $field=array(); $field_ext=array(); $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $fld_flg=false;//Строка поля $no_user_itm=''; foreach ($cellIterator as $cell) { $val=trim(u2w($cell->getValue())); if(!$fld_flg && $val=='category_id'){ $fld_flg=true; } if($fld_flg){//Грузим поля if(isset($keys_flg[$val])){ $keys[$cell->getColumn()]=$keys_flg[$val]; } // print_r($keys); continue; } if($keys && isset($keys[$cell->getColumn()])){ if(is_integer($keys[$cell->getColumn()])){ $field["{$keys[$cell->getColumn()]}"]=$val; }else{ $field_ext["{$keys[$cell->getColumn()]}"]=$val; } } } if(!$keys){ continue; } if(!empty($field_ext['id'])){ if($field_ext['img']){ if($img=@file_get_contents('http://www.farmcosmetica.ru/image/'.$field_ext['img'])){ $img_name=preg_replace('|^data/|','',$field_ext['img']); $img_name=str_replace('/','_',$img_name); $img_name="storage/catalog/".$img_name; if(!file_exists($img_name)){ file_put_contents($img_name,$img); } $field_ext['img']="/".$img_name; }else{ $field_ext['img']=""; } } $rs=$ST->select("SELECT * FROM sc_shop_catalog WHERE id={$field_ext['id']}"); if($rs->next()){ $ST->update('sc_shop_catalog',$field_ext,"id={$field_ext['id']}"); }else{ $ST->insert('sc_shop_catalog',$field_ext); } $out[$field_ext['id']]=$field_ext; } } */ $objPHPExcel->setActiveSheetIndex(1); $objWorksheet = $objPHPExcel->getActiveSheet(); $keys = array(); $keys_flg = array('product_id' => 'id', 'categories' => 'categories', 'name' => 'name', 'quantity' => 'in_stock', 'image_name' => 'img', 'additional image names' => 'img_add', 'price' => 'price', 'date_added' => 'insert_time', 'date_modified' => 'update_time', 'viewed' => 'views', 'description' => 'html'); $out = array(); foreach ($objWorksheet->getRowIterator() as $row) { $data = array(); $field = array(); $field_ext = array(); $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $fld_flg = false; //Строка поля $no_user_itm = ''; // echo "\n"; foreach ($cellIterator as $cell) { $val = trim(u2w($cell->getValue())); if (!$fld_flg && $val == 'product_id') { //$cell->getColumn()=='B' && $fld_flg = true; } if ($fld_flg) { //Грузим поля if (isset($keys_flg[$val])) { $keys[$cell->getColumn()] = $keys_flg[$val]; } // print_r($keys); continue; } if ($keys && isset($keys[$cell->getColumn()])) { if (is_integer($keys[$cell->getColumn()])) { $field["{$keys[$cell->getColumn()]}"] = $val; } else { $field_ext["{$keys[$cell->getColumn()]}"] = $val; } } } if (!$keys) { continue; } if (!empty($field_ext['id'])) { if ($field_ext['img']) { if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $field_ext['img']))) { $img_name = preg_replace('|^data/|', '', $field_ext['img']); $img_name = str_replace('/', '_', $img_name); $img_name = "storage/catalog/goods/" . $img_name; if (!file_exists($img_name)) { file_put_contents($img_name, $img); } $field_ext['img'] = "/" . $img_name; } else { $field_ext['img'] = ""; } } if ($field_ext['img_add']) { $images = explode(',', $field_ext['img_add']); $field_ext['img_add'] = array(); foreach ($images as $i) { if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $i))) { $img_name = preg_replace('|^data/|', '', $i); $img_name = str_replace('/', '_', $img_name); $img_name = "storage/catalog/goods/" . $img_name; if (!file_exists($img_name)) { file_put_contents($img_name, $img); } $field_ext['img_add'][] = "/" . $img_name; } } $field_ext['img_add'] = implode(',', $field_ext['img_add']); } $rs = $ST->select("SELECT * FROM sc_shop_item WHERE id={$field_ext['id']}"); $categories = $field_ext['categories']; unset($field_ext['categories']); if ($rs->next()) { $ST->update('sc_shop_item', $field_ext, "id={$field_ext['id']}"); } else { $ST->insert('sc_shop_item', $field_ext); } // if($categories){ // $categories=explode(',',$categories); // $ST->delete('sc_shop_item2cat',"itemid={$field_ext['id']}"); // foreach ($categories as $c) { // $ST->insert('sc_shop_item2cat',array('itemid'=>$field_ext['id'],'catid'=>$c)); // } // // } // if($field_ext['manufacturer']){ // $rs=$ST->select("SELECT * FROM sc_manufacturer WHERE name='".SQL::slashes($field_ext['manufacturer'])."'"); // if($rs->next()){ // $man_id=$rs->getInt('id'); // }else{ // $man_id=$ST->insert("sc_manufacturer",array('name'=>$field_ext['manufacturer'])); // } // $ST->update('sc_shop_item',array('manufacturer_id'=>$man_id),"id={$field_ext['id']}"); // // // } // if($field_ext['Объем']){ // $rs=$ST->select("select * FROM sc_shop_prop_val WHERE prop_id=1 AND item_id={$field_ext['id']}"); // if($rs->next()){ // $ST->update('sc_shop_prop_val',array('value'=>$field_ext['Объем']),"id={$rs->get('id')}"); // }else{ // $ST->insert('sc_shop_prop_val',array('value'=>$field_ext['Объем'],'prop_id'=>1,"item_id"=>$field_ext['id'])); // } // } $out[$field_ext['id']] = $field_ext; } } }
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; }
public function index(){ //导入PHPExcel类库,因为PHPExcel没有用命名空间,只能inport导入 import("Org.Util.PHPExcel"); //要导入的xls文件,位于根目录下的Public文件夹 $filename="./Public/1.xls"; //创建PHPExcel对象,注意,不能少了\ $PHPExcel=new \PHPExcel(); //如果excel文件后缀名为.xls,导入这个类 import("Org.Util.PHPExcel.Reader.Excel5"); //如果excel文件后缀名为.xlsx,导入这下类 //import("Org.Util.PHPExcel.Reader.Excel2007"); //$PHPReader=new \PHPExcel_Reader_Excel2007(); $PHPReader=new \PHPExcel_Reader_Excel5(); //载入文件 $PHPExcel=$PHPReader->load($filename); //获取表中的第一个工作表,如果要获取第二个,把0改为1,依次类推 $currentSheet=$PHPExcel->getSheet(0); //获取总列数 $allColumn=$currentSheet->getHighestColumn(); //获取总行数 $allRow=$currentSheet->getHighestRow(); //循环获取表中的数据,$currentRow表示当前行,从哪行开始读取数据,索引值从0开始 for($currentRow=1;$currentRow<=$allRow;$currentRow++){ //从哪列开始,A表示第一列 for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){ //数据坐标 $address=$currentColumn.$currentRow; //读取到的数据,保存到数组$arr中 $arr[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue(); } } dump($arr); }
/** * 导入代理商信息 * @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 function createReport() { ini_set('memory_limit', '-1'); $objReader = new PHPExcel_Reader_Excel5(); $filename = 'first_data.xls'; $objPHPExcel = $objReader->load(dirname(__FILE__) . '/../../files/' . $filename); $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); echo "<pre>"; print_r($sheetData); }
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 xlsToCsv($file_xls_input, $file_name_csv_output) { $objReader = new PHPExcel_Reader_Excel5(); try { /** Load $inputFileName to a PHPExcel Object **/ $objPHPExcel = $objReader->load($file_xls_input); return true; } catch (PHPExcel_Reader_Exception $e) { die('Error loading file: ' . $e->getMessage()); } $writer = new PHPExcel_Writer_CSV($objPHPExcel); $writer->save($file_name_csv_output); }
/** * 获取可以读取类的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 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++; } } }
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 writeCells($celdas, $srcOrigen, $sheet) { // escribe las celdas especificas de un archivo de excel existente if (is_string($srcOrigen)) { $objReader = new PHPExcel_Reader_Excel5(); $objPHPExcel = $objReader->load($srcOrigen); } else { $objPHPExcel = $srcOrigen; } $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); //objeto de PHPExcel, para escribir en el excel $objPHPExcel->setActiveSheetIndex($sheet); foreach ($celdas as $campo => $celda) { $objPHPExcel->getActiveSheet()->setCellValue($campo, $celda); } return $objPHPExcel; }
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); }
public function index() { set_time_limit(0); date_default_timezone_set('Europe/Paris'); include_once ROOT_PATH . 'core/PHPExcel/PHPExcel/IOFactory.php'; $inputFileName = UPLOADS_PATH . 'example1.xls'; echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using PHPExcel_Reader_Excel5<br />'; $objReader = new PHPExcel_Reader_Excel5(); // $objReader = new PHPExcel_Reader_Excel2007(); // $objReader = new PHPExcel_Reader_Excel2003XML(); // $objReader = new PHPExcel_Reader_OOCalc(); // $objReader = new PHPExcel_Reader_SYLK(); // $objReader = new PHPExcel_Reader_Gnumeric(); // $objReader = new PHPExcel_Reader_CSV(); $objPHPExcel = $objReader->load($inputFileName); echo '<hr />'; $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); $vars = array('sheet_data' => $sheetData); $this->render('excel', $vars); }
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; }
/** * 导入供应商信息的函数 * 基于 PHPExcel.php * @return $result $result > 0 成功,否则失败 */ public function act_importSave() { $uploadfile = date("Y") . date("m") . date("d") . rand(1, 3009) . ".xls"; if (!move_uploaded_file($_FILES['upfile']['tmp_name'], WEB_PATH . 'upload_datas/' . $uploadfile)) { return false; } $fileName = WEB_PATH . 'upload_datas/' . $uploadfile; $filePath = $fileName; $PHPExcel = new PHPExcel(); $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); /**取得一共有多少列*/ $c = 2; while (true) { $aa = 'A' . $c; $bb = 'B' . $c; $cc = 'C' . $c; $dd = 'D' . $c; $ee = 'E' . $c; $ff = 'F' . $c; $gg = 'G' . $c; $hh = 'H' . $c; $ii = 'I' . $c; $jj = 'J' . $c; $kk = 'K' . $c; $ll = 'L' . $c; $mm = 'M' . $c; $nn = 'N' . $c; $oo = 'O' . $c; $pp = 'P' . $c; $qq = 'Q' . $c; $rr = 'R' . $c; $ss = 'S' . $c; $tt = 'T' . $c; $uu = 'U' . $c; $vv = 'V' . $c; $ww = 'W' . $c; $zz = 'Z' . $c; $xx = 'X' . $c; $c++; $company_name = str_rep(trim($currentSheet->getCell($aa)->getValue())); $username = str_rep(trim($currentSheet->getCell($bb)->getValue())); $category_name = str_rep(trim($currentSheet->getCell($cc)->getValue())); $tel = str_rep(trim($currentSheet->getCell($dd)->getValue())); $phone = str_rep(trim($currentSheet->getCell($ee)->getValue())); $fax = str_rep(trim($currentSheet->getCell($ff)->getValue())); $QQ = str_rep(trim($currentSheet->getCell($gg)->getValue())); $e_mail = str_rep(trim($currentSheet->getCell($hh)->getValue())); $AliIM = str_rep(trim($currentSheet->getCell($ii)->getValue())); $shoplink = str_rep(trim($currentSheet->getCell($jj)->getValue())); $city = str_rep(trim($currentSheet->getCell($kk)->getValue())); $address = str_rep(trim($currentSheet->getCell($ll)->getValue())); $status = str_rep(trim($currentSheet->getCell($mm)->getValue())); $email_status = str_rep(trim($currentSheet->getCell($nn)->getValue())); $sms_status = str_rep(trim($currentSheet->getCell($oo)->getValue())); $purchaser = str_rep(trim($currentSheet->getCell($pp)->getValue())); $company = str_rep(trim($currentSheet->getCell($qq)->getValue())); $note = str_rep(trim($currentSheet->getCell($rr)->getValue())); $partnerStr = "单位名称:{$company_name}, 姓名:{$username}, 电话:{$tel} ... "; //var_dump(self::check_input($company_name)); //exit; if ($company_name != '' && !self::check_input($company_name)) { $errInfo = " -[<font color='#FF0000'>导入失败, '单位名称':'{$company_name}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($username != '' && !self::check_input($username)) { $errInfo = " -[<font color='#FF0000'>导入失败, '姓名':'{$username}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($category_name != '' && !self::check_input($category_name)) { $errInfo = " -[<font color='#FF0000'>导入失败, '单位类型':'{$category_name}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($tel != '' && !self::isTel($tel)) { $errInfo = " -[<font color='#FF0000'>导入失败, '电话号码':'{$tel}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($phone != '' && !self::isMobile($phone)) { $errInfo = " -[<font color='#FF0000'>导入失败, '手机号码':'{$phone}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($fax != '' && !self::isPhone($fax)) { $errInfo = " -[<font color='#FF0000'>导入失败, '传真号码':'{$fax}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($QQ != '' && !self::isQQ($QQ)) { $errInfo = " -[<font color='#FF0000'>导入失败, 'QQ号码':'{$QQ}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($e_mail != '' && !self::isEmail($e_mail)) { $errInfo = " -[<font color='#FF0000'>导入失败, '邮件':'{$e_mail}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($AliIM != '' && !self::isNormalCharacter($AliIM)) { $errInfo = " -[<font color='#FF0000'>导入失败, '阿里旺旺':'{$AliIM}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($city != '' && !self::check_input($city)) { $errInfo = " -[<font color='#FF0000'>导入失败, '所属城市':'{$city}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($address != '' && !self::check_input($address)) { $errInfo = " -[<font color='#FF0000'>导入失败, '地址':'{$address}' 填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } $status = $status == '黑名单' ? 0 : $status == '正常' ? 1 : 2; if ($status < 0 || $status > 2) { $errInfo = " -[<font color='#FF0000'>导入失败, '状态'填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } $email_status = $email_status == '是' ? 1 : 0; if ($email_status != 0 && $email_status != 1) { $errInfo = " -[<font color='#FF0000'>导入失败, '支持邮件'填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } $sms_status = $sms_status == '是' ? 1 : 0; if ($sms_status != 0 && $sms_status != 1) { $errInfo = " -[<font color='#FF0000'>导入失败, '支持短信'填写有误!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } if ($company_name == '') { break; } //$c++; //To get type_id $where = " AND `category_name` = '{$category_name}' "; $field = " `id` "; $resultType = PartnerTypeAct::act_getPartnerTypeList($where, $field); $type_id = $resultType[0]['id']; if (!isset($type_id)) { $errInfo = " -[<font color='#FF0000'>导入失败, 单位类型 '{$category_name}' 不存在!" . $partnerStr . "</font>]"; continue; } //To get purchaser_id $where = " AND `global_user_name` = '{$purchaser}' "; $field = " `global_user_id` "; $resultPurchase = self::act_getPurchaserList($where, $field); $purchaser_id = $resultPurchase[0]['global_user_id']; if (!isset($purchaser_id)) { $errInfo = " -[<font color='#FF0000'>导入失败, 采购员 '{$purchaser}' 不存在!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } //To get company_id $where = " AND `company` = '{$company}' "; $field = " `id` "; $resultCompany = self::act_getPartnerCompany($where, $field); //print_r($resultCompany); $company_id = $resultCompany[0]['id']; if (!isset($company_id)) { $errInfo = " -[<font color='#FF0000'>导入失败, 关联公司 '{$company}' 不存在!" . $partnerStr . "</font>]"; echo $errInfo . '<br>'; continue; } $data = array('company_name' => $company_name, 'username' => $username, 'type_id' => $type_id, 'tel' => $tel, 'phone' => $phone, 'fax' => $fax, 'QQ' => $QQ, 'AliIM' => $AliIM, 'e_mail' => $e_mail, 'shoplink' => $shoplink, 'city' => $city, 'address' => $address, 'note' => $note, 'status' => $status, 'sms_status' => $sms_status, 'email_status' => $email_status, 'purchaseuser_id' => $purchaser_id, 'company_id' => $company_id); //print_r($data); $result = PartnerModel::insertRow($data); if ($result) { $errInfo = " -[<font color='#33CC33'>导入成功, " . $partnerStr . "</font>]"; } else { $errMsg = PartnerModel::$errMsg; $errInfo = " -[<font color='#FF0000'>导入失败, " . $partnerStr . $errMsg . "</font>]"; } echo $errInfo . '<br>'; } }
public function import() { if (!isset($_FILES['customExcelData'])) { uploadFileErrorRespond('请选择数据!'); } if (file_exists($_FILES['customExcelData']['tmp_name'])) { $Custom = M('Custom'); $CustomGoods = D('CustomGoods'); $SendGoodsSort = D('SendGoodsSort'); $Employee = D('Employee'); $Province = D('Province'); $CustomUnit = D('CustomUnit'); import("ORG.Excel.PHPExcel"); import("ORG.Excel.PHPExcel.IOFactory", THINK_PATH . '/Lib/', '.php'); $fileName = $_FILES['customExcelData']['tmp_name']; $PHPExcel = new PHPExcel(); /** 默认用excel2007读取excel,若格式不对,则用之前的版本进行读取 */ $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($fileName)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($fileName)) { uploadFileErrorRespond('Excel格式不支持,请转换后重试!'); //uploadFileRespond('Excel format not support ,please try again.'); return; } } $EmployeeInsertId = get_employeeid($_SESSION[C('USER_AUTH_KEY')]); ob_clean(); $PHPExcel = $PHPReader->load($fileName); /* 读取excel文件中的第一个工作表 */ $currentSheet = $PHPExcel->getSheet(0); /* 取得最大的列号 */ $allColumn = $currentSheet->getHighestColumn(); /* 取得一共有多少行 */ $allRow = $currentSheet->getHighestRow(); if (empty($allRow) || $allRow < 2) { uploadFileErrorRespond('请检查数据是否在excel的第一个sheet中。'); } $customRowCount = 0; $customUnitRowCount = 0; $customGoodsRowCount = 0; /* 从第二行开始输出,因为excel表中第一行为列名 */ for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { /* 从第A列开始输出 */ $managerName = trim($currentSheet->getCell('A' . $currentRow)->getValue()); $address = trim($currentSheet->getCell('B' . $currentRow)->getValue()); $customName = trim($currentSheet->getCell('C' . $currentRow)->getValue()); $customUnitName = trim($currentSheet->getCell('D' . $currentRow)->getValue()); $className = trim($currentSheet->getCell('E' . $currentRow)->getValue()); $tel = trim($currentSheet->getCell('F' . $currentRow)->getValue()); $sendSortName = trim($currentSheet->getCell('G' . $currentRow)->getValue()); $provinceName = trim($currentSheet->getCell('H' . $currentRow)->getValue()); $payName = trim($currentSheet->getCell('I' . $currentRow)->getValue()); $zipCode = trim($currentSheet->getCell('J' . $currentRow)->getValue()); if (empty($managerName) && empty($customName)) { continue; } $map['name'] = $provinceName; $provinceId = $Province->where($map)->getField('id'); unset($map); $map['name'] = $sendSortName; $sendSortId = $SendGoodsSort->where($map)->getField('id'); unset($map); $map['employeeName'] = $managerName; $employeeId = $Employee->where($map)->getField('id'); if (empty($employeeId)) { $employeeId = $EmployeeInsertId; } unset($map); $map['name'] = $customUnitName; $customUnitId = $CustomUnit->where($map)->getField('id'); if (empty($customUnitId)) { $data['name'] = $customUnitName; $data['telphone1'] = $tel; $data['address'] = $address; $data['postCode'] = $zipCode; $data['employeeID'] = $employeeId; $customUnitId = $CustomUnit->data($data)->add(); $customUnitRowCount++; /* 客户单位信息添加一条 */ } unset($map); unset($data); /* 唯一定位一个客户 */ $map['employeeID'] = $employeeId; $map['name'] = $customName; $map['address'] = $address; $map['provinceID'] = $provinceId; $customId = $Custom->where($map)->getField('id'); //echo $Custom->getLastSql(); //exit(); if (empty($customId)) { $data['insertPerson'] = $EmployeeInsertId; $data['employeeID'] = $employeeId; $data['name'] = $customName; $data['provinceID'] = $provinceId; $data['address'] = $address; $data['telphone1'] = $tel; $data['UnitID'] = $customUnitId; $data['insertTime'] = time(); $data['postCode'] = $zipCode; $customId = $Custom->data($data)->add(); $customRowCount++; /* 客户信息插入了一条 */ } unset($map); unset($data); /* 唯一定位一个发货人 */ $map['customID'] = $customId; $map['schoolID'] = $customUnitId; $map['className'] = $className; $map['tel'] = $tel; $customGoodsId = $CustomGoods->where($map)->getField('id'); if (empty($customGoodsId)) { $data['customID'] = $customId; $data['recName'] = $customName; $data['phone'] = $tel; $data['address'] = $address; $data['cityName'] = $address; $data['payName'] = $payName; $data['sendSortID'] = $sendSortId; $data['provinceID'] = $provinceId; $data['className'] = $className; $data['schoolID'] = $customUnitId; $data['zipCode'] = $zipCode; $customGoodsId = $CustomGoods->data($data)->add(); $customGoodsRowCount++; } unset($map); unset($data); } $customRowLast = $allRow - $customRowCount; $customUnitRowLast = $allRow - $customUnitRowCount; $customGoodsRowLast = $allRow - $customGoodsRowCount; $msg = '导入成功!<br/>共有' . $allRow . '条数据.<br/>客户信息添加' . $customRowCount . '条;跳过' . $customRowLast . '条.<br/>单户单位信息添加' . $customUnitRowCount . '条;跳过' . $customUnitRowLast . '条.<br/>客户收货信息添加' . $customGoodsRowCount . '条;跳过' . $customGoodsRowLast . '条.'; uploadFileSuccessRespond($msg, 'Custom'); return; } uploadFileErrorRespond('出现错误,请重试!'); }
/** * Read OfficeArtRGFOPTE table of property-value pairs * * @param string $data Binary data * @param int $n Number of properties */ private function _readOfficeArtRGFOPTE($data, $n) { $splicedComplexData = substr($data, 6 * $n); // loop through property-value pairs for ($i = 0; $i < $n; ++$i) { // read 6 bytes at a time $fopte = substr($data, 6 * $i, 6); // offset: 0; size: 2; opid $opid = PHPExcel_Reader_Excel5::_GetInt2d($fopte, 0); // bit: 0-13; mask: 0x3FFF; opid.opid $opidOpid = (0x3fff & $opid) >> 0; // bit: 14; mask 0x4000; 1 = value in op field is BLIP identifier $opidFBid = (0x4000 & $opid) >> 14; // bit: 15; mask 0x8000; 1 = this is a complex property, op field specifies size of complex data $opidFComplex = (0x8000 & $opid) >> 15; // offset: 2; size: 4; the value for this property $op = PHPExcel_Reader_Excel5::_GetInt4d($fopte, 2); if ($opidFComplex) { $complexData = substr($splicedComplexData, 0, $op); $splicedComplexData = substr($splicedComplexData, $op); // we store string value with complex data $value = $complexData; } else { // we store integer value $value = $op; } $this->_object->setOPT($opidOpid, $value); } }
} /** * The possible scopes for $plugin_param are: 'table', 'database', and 'server' */ if (isset($plugin_list)) { $plugin_list['xls'] = array('text' => 'strImportXLS', 'extension' => 'xls', 'options' => array(array('type' => 'bool', 'name' => 'col_names', 'text' => 'strImportColNames')), 'options_text' => 'strOptions'); /* We do not define function when plugin is just queried for information above */ return; } ini_set('memory_limit', '256M'); set_time_limit(120); /* Append the PHPExcel directory to the include path variable */ set_include_path(get_include_path() . PATH_SEPARATOR . getcwd() . '/libraries/PHPExcel/'); require_once './libraries/PHPExcel/PHPExcel.php'; require_once './libraries/PHPExcel/PHPExcel/Reader/Excel5.php'; $objReader = new PHPExcel_Reader_Excel5(); $objReader->setReadDataOnly(true); $objReader->setLoadAllSheets(); $objPHPExcel = $objReader->load($import_file); $sheet_names = $objPHPExcel->getSheetNames(); $num_sheets = count($sheet_names); $tables = array(); $tempRow = array(); $rows = array(); $col_names = array(); for ($s = 0; $s < $num_sheets; ++$s) { $current_sheet = $objPHPExcel->getSheet($s); $num_rows = $current_sheet->getHighestRow(); $num_cols = PMA_getColumnNumberFromName($current_sheet->getHighestColumn()); if ($num_rows != 1 && $num_cols != 1) { for ($r = 1; $r <= $num_rows; ++$r) {
/** * 生成订单附件 * @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]); //预定的额外服务 // 执行Excel写入操作 $PHPWriter = new PHPExcel_Writer_Excel5($PHPExcel); // 生成订单附件 // $PHPWriter->save(iconv('utf - 8', 'gbk', $attachment_path . $attachment_name)); $PHPWriter->save($attachment_path . $attachment_name); } catch (Exception $e) { echo $e->getMessage(); } echo '<script language="javascript" type="text/javascript">window.top.window.stopUpload(1);</script >'; return $attachment_path . $attachment_name; } else { exit('参数不正确!'); } }
function importMhs($filename = '') { require_once 'includes/xls_report/PHPExcel.php'; $xls_file = 'uploads/form/' . $filename; $objReader = new PHPExcel_Reader_Excel5(); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($xls_file); #return $filename; $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, false); $totalrow = count($sheetData); #return $sheetData; # get active record $start_record = 5; $start_col = 0; $data = array(); $index = 0; $kd_prodi = $sheetData[0][0]; $kd_fak = $this->model->getKodeFakultasByKodeProdi($kd_prodi); while ($start_record < $totalrow) { $a = $this->security->xss_clean($sheetData[$start_record][$start_col]); $start_col++; $b = $this->security->xss_clean($sheetData[$start_record][$start_col]); $start_col++; $c = $this->security->xss_clean($sheetData[$start_record][$start_col]); $start_col++; $d = $this->security->xss_clean($sheetData[$start_record][$start_col]); $start_col++; $e = $this->security->xss_clean($sheetData[$start_record][$start_col]); $start_col++; $data[$index++] = array($a, $b, $c, $d, $e); $start_record++; $start_col = 0; # insert to table mahasiswa $data = array('npm' => $a, 'nm_mhs' => $b, 'kd_fak' => $kd_fak, 'kd_prodi' => $kd_prodi, 'alamat_mhs' => $c, 'email_mhs' => $d, 'phone_mhs' => $e, 'date_created' => date('Y-m-d H:i:s'), 'createdby' => $this->session->userdata('user_id')); $sv_mhs = $this->model->insertData('md_mhs', $data); if ($sv_mhs) { $dataUser = array('id_user' => $a, 'user_password' => sha1($a), 'user_name' => $b, 'user_email' => $d, 'user_phone' => $e, 'user_level' => 1, 'date_created' => date('Y-m-d H:i:s'), 'createdby' => $this->session->userdata('user_id')); $this->model->insertData('users', $dataUser); $this->outh->user_log($this->session->userdata('user_id'), 'Import Mahasiswa -> ' . $data['nm_mhs'] . ' (' . $data['npm'] . ')'); } } #print_r($data); echo $kd_prodi.'-'. $kd_fak; $this->session->set_flashdata('success', ' Import Data Mahasiswa berhasil. <b>' . $index . '</b> data mahasiswa baru berhasil disimpan!'); redirect('master/mahasiswa'); }
protected function setPHPExcelReader($file) { $objReader = new PHPExcel_Reader_Excel5(); $objReader->setReadDataOnly(true); return $objReader->load($file); }
public function excelImport() { $m_contacts = M('contacts'); if ($_POST['submit']) { if (isset($_FILES['excel']['size']) && $_FILES['excel']['size'] != null) { import('@.ORG.UploadFile'); $upload = new UploadFile(); $upload->maxSize = 20000000; $upload->allowExts = array('xls'); $dirname = UPLOAD_PATH . date('Ym', time()) . '/' . date('d', time()) . '/'; if (!is_dir($dirname) && !mkdir($dirname, 0777, true)) { alert('error', L('ATTACHMENTS TO UPLOAD DIRECTORY CANNOT WRITE'), U('contacts/index')); } $upload->savePath = $dirname; if (!$upload->upload()) { alert('error', $upload->getErrorMsg(), U('contacts/index')); } else { $info = $upload->getUploadFileInfo(); } } if (is_array($info[0]) && !empty($info[0])) { $savePath = $dirname . $info[0]['savename']; } else { alert('error', L('UPLOAD FAILED'), U('contacts/index')); } import("ORG.PHPExcel.PHPExcel"); $PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($savePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); } $PHPExcel = $PHPReader->load($savePath); $currentSheet = $PHPExcel->getSheet(0); $allRow = $currentSheet->getHighestRow(); for ($currentRow = 3; $currentRow <= $allRow; $currentRow++) { $data = array(); $data['creator_role_id'] = session('role_id'); $data['create_time'] = time(); $data['update_time'] = time(); $data['owner_role_id'] = trim($_POST['owner_role_id']); $name = (string) $currentSheet->getCell('A' . $currentRow)->getValue(); $name != '' && $name != null ? $data['name'] = $name : ''; /* $customer_name = (String)$currentSheet->getCell('C'.$currentRow)->getValue(); $customer_id = M('Customer')->where('name = "%s"' ,trim($customer_name))->getField('customer_id'); if($customer_name){ if($customer_id > 0){ $r_c_c['customer_id'] = $customer_id; $data['customer_id'] = $customer_id; } else { alert('error', '导入至第' . $currentRow . '行出错, 原因:"'.$customer_name.'"客户不存在', U('contacts/index')); break; } } */ $saltname = (string) $currentSheet->getCell('B' . $currentRow)->getValue(); $saltname != '' && $saltname != null ? $data['saltname'] = $saltname : ''; $department = (string) $currentSheet->getCell('C' . $currentRow)->getValue(); $department != '' && $department != null ? $data['department'] = $department : ''; $post = (string) $currentSheet->getCell('D' . $currentRow)->getValue(); $post != '' && $post != null ? $data['post'] = $post : ''; $qq = (string) $currentSheet->getCell('E' . $currentRow)->getValue(); $qq != '' && $qq != null ? $data['qq'] = $qq : ''; $telephone = (string) $currentSheet->getCell('F' . $currentRow)->getValue(); $telephone != '' && $telephone != null ? $data['telephone'] = $telephone : ''; $email = (string) $currentSheet->getCell('G' . $currentRow)->getValue(); $email != '' && $email != null ? $data['email'] = $email : ''; $address = (string) $currentSheet->getCell('H' . $currentRow)->getValue(); $address != '' && $address != null ? $data['address'] = $address : ''; $zip_code = (string) $currentSheet->getCell('I' . $currentRow)->getValue(); $zip_code != '' && $zip_code != null ? $data['zip_code'] = $zip_code : ''; $description = (string) $currentSheet->getCell('J' . $currentRow)->getValue(); $description != '' && $description != null ? $data['description'] = $description : ''; if (!($contacts_id = $m_contacts->add($data))) { if ($this->_post('error_handing', 'intval', 0) == 0) { alert('error', L('ERROR INTRODUCED INTO THE LINE', array($currentRow, $m_contacts->getError())), U('contacts/index')); } else { $error_message .= L('LINE ERROR', array($currentRow, $m_contacts->getError())); $m_contacts->clearError(); } break; } } alert('success', L('IMPORT SUCCESS', array($error_message)), U('contacts/index')); } else { $this->display(); } }
public function doWebBatchAddInfo() { global $_W, $_GPC; load()->func('tpl'); if (checksubmit()) { $excelfile = $_FILES['exceldata']; if (empty($excelfile)) { message('请先上传文件'); } $str = explode(".", $excelfile['name']); $filetype = strtolower($str[1]); if (!($filetype == 'xlsx' || $filetype == 'xls')) { message('上传文件类型不正确!'); } $path = '../attachment/excel/water/'; if (!file_exists($path)) { mkdir($path); } $time = time(); $filePath = $path . $time . '.' . $filetype; move_uploaded_file($excelfile['tmp_name'], $filePath); //首先导入PHPExcel require_once 'PHPExcel.php'; //$filePath = "../addons/water_query2/test.xlsx"; //建立reader对象 $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { echo 'no Excel'; return; } } //建立excel对象,此时你即可以通过excel对象读取文件,也可以通过它写入文件 $PHPExcel = $PHPReader->load($filePath); /**读取excel文件中的第一个工作表*/ $currentSheet = $PHPExcel->getSheet(0); /**取得最大的列号*/ $allColumn = $currentSheet->getHighestColumn(); echo $allColumn . '---'; /**取得一共有多少行*/ $allRow = $currentSheet->getHighestRow(); echo $allRow . '----'; //循环读取每个单元格的内容。注意行从1开始,列从A开始 $data = array('uniacid' => $_W['uniacid']); for ($rowIndex = 1; $rowIndex <= $allRow; $rowIndex++) { for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) { $addr = $colIndex . $rowIndex; $cell = $currentSheet->getCell($addr)->getValue(); if ($cell instanceof PHPExcel_RichText) { //富文本转换字符串 $cell = $cell->__toString(); } //echo '第'.$rowIndex.'行,第'.$colIndex.'列:'.$cell.'</br>'; if ($colIndex == 'A') { $data['keyword'] = $cell; } else { $data['ordercode'] = $cell; } } pdo_insert('water_query2_info', $data); } message('导入成功!', $this->createWebUrl('Info'), 'success'); } else { include $this->template('batchaddinfo'); } }
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'))); }
/** * @desc 读取excel数据 * @param string $excelFilePath excel的保存文件路径 */ public function readExcel($excelFilePath) { $return = array('status' => 0); $PHPReader = new PHPExcel_Reader_Excel2007(); $PHPReader->setReadDataOnly(true); //设为只读 //如果没有选择excel文件 if (!$PHPReader->canRead($excelFilePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($excelFilePath)) { $return['msg'] = 'no Excel'; return $return; } } $PHPExcel = $PHPReader->load($excelFilePath); //传入excel路径,载入该excel $currentSheet = $PHPExcel->getSheet(0); //sheet 0 $allColumn = $currentSheet->getHighestColumn(); //总列 //column Count $allRow = $currentSheet->getHighestRow(); //总行//Row Count $blockStartEndRow = $this->getBloclStartEndRow($allColumn, $allRow, $currentSheet); $excelData = $this->getExcelData($blockStartEndRow, $allColumn, $allRow, $currentSheet); if (ISPRINTEXCELDATAARR) { echo '<pre>'; echo '开始-结束行<br/>'; print_r($blockStartEndRow); echo '<hr/>'; echo 'excel数据数组<br/>'; print_r($excelData); exit; } unlink($excelFilePath); //删除excel文件 $return['data'] = $excelData; return $return; }
ini_set('display_errors', TRUE); ini_set('display_startup_errors', TRUE); date_default_timezone_set('Asia/Jakarta'); define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br \\>'); include "blob.php"; require_once "PHPExcel.php"; $data = new koneksi(); $ds = DIRECTORY_SEPARATOR; if (!empty($_FILES)) { $tempFile = $_FILES['file']['tmp_name']; $targetPath = "../template-report/"; $temp = explode(".", $_FILES["file"]["name"]); $newFileName = rand(1, 99999999) . '.' . end($temp); $targetFile = $targetPath . $newFileName; move_uploaded_file($tempFile, $targetFile); $objReader = new PHPExcel_Reader_Excel5(); $objReader->setReadDataOnly(true); $objExcel = $objReader->load("../template-report/" . $newFileName); $objExcel->setActiveSheetIndex(0); $highestRow = $objExcel->setActiveSheetIndex(0)->getHighestRow(); for ($row = 7; $row <= $highestRow; $row++) { $noreg = $objExcel->getSheet(0)->getCell('A' . $row)->getValue(); $nama = $objExcel->getSheet(0)->getCell('B' . $row)->getValue(); $alamat = $objExcel->getSheet(0)->getCell('C' . $row)->getValue(); $rayon = $objExcel->getSheet(0)->getCell('D' . $row)->getValue(); $pasar = $objExcel->getSheet(0)->getCell('E' . $row)->getValue(); $subpasar = $objExcel->getSheet(0)->getCell('F' . $row)->getValue(); $type = $objExcel->getSheet(0)->getCell('G' . $row)->getValue(); $area = $objExcel->getSheet(0)->getCell('H' . $row)->getValue(); $kodepos = $objExcel->getSheet(0)->getCell('I' . $row)->getValue(); $barcode = $objExcel->getSheet(0)->getCell('J' . $row)->getValue();