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); }
/** * *函数名: 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 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); }
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); }
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); }
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); }
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; }
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; }
function act_goodsAddXlsSave() { $uploadfile = date("Y") . date("m") . date("d") . rand(1, 3009) . ".xls"; if ($_FILES['upfile']['tmp_name'] == '') { echo "请选择上传的excel文件"; exit; } if (move_uploaded_file($_FILES['upfile']['tmp_name'], './upload/' . $uploadfile)) { echo "<font color=BLUE>文件上传成功!</font><br>"; } else { echo "<font color=red> 文件上传失败!</font><br>"; } echo $uploadfile; $fileName = 'upload/' . $uploadfile; $filePath = $fileName; $username = $_SESSION['userName']; $userId = $_SESSION['userId']; $stockdetailpower = UserModel::getUserPower($userId); $stockdetailpower = explode(',', $stockdetailpower[0]['powerName']); $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); $allRow = $currentSheet->getHighestRow(); for ($c = 2; $c <= $allRow; $c++) { $data = array(); $nowtime = time(); $storeid = ''; $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; $sku = post_check(trim($currentSheet->getCell($bb)->getValue())); if (!preg_match("/^[A-Z0-9]+(_[A-Z0-9]+)*\$/", $sku)) { if ($sku != '') { echo "数据料号:<font color='#FF0000'>" . $sku . "</font>书写不规范,请认真检查再提交..."; exit; } } //检测是否导入完成 if (empty($sku)) { $mask++; if ($mask > 9) { break; } continue; } else { $mask = 1; } // 过滤下数据 $goodsCost = trim($currentSheet->getCell($dd)->getValue()); $goodsWeight = trim($currentSheet->getCell($ff)->getValue()); if (!preg_match("/[\\d]/", $goodsCost) && $goodsCost != '') { echo "货品成本数据有误"; exit; } else { if (!preg_match("/[\\d]/", $goodsWeight) && $goodsWeight != '') { echo "货品重量数据有误"; exit; } } $spu = post_check(trim($currentSheet->getCell($aa)->getValue())); $goodsName = post_check(trim($currentSheet->getCell($cc)->getValue())); $goodsCost = empty($goodsCost) ? 0 : round_num($goodsCost, 2); //仓位号 $goods_location = post_check(trim($currentSheet->getCell($ee)->getValue())); $goodsWeight = empty($goodsWeight) ? 0 : number_format($goodsWeight, 3, '.', ''); $goodsNote = post_check(trim($currentSheet->getCell($gg)->getValue())); $goodsDecNameByEN = post_check(trim($currentSheet->getCell($hh)->getValue())); $goodsCustomsCode = post_check(trim($currentSheet->getCell($ii)->getValue())); $goodsDecNameByCN = post_check(trim($currentSheet->getCell($jj)->getValue())); $goodsDecWorth = post_check(trim($currentSheet->getCell($kk)->getValue())); $goodsLength = post_check(trim($currentSheet->getCell($ll)->getValue())); $goodsWidth = post_check(trim($currentSheet->getCell($mm)->getValue())); $goodsHeight = post_check(trim($currentSheet->getCell($nn)->getValue())); //类别 $goods_categoryname = post_check(trim($currentSheet->getCell($oo)->getValue())); $pid_name = post_check(trim($currentSheet->getCell($pp)->getValue())); //仓库 $warehouseName = post_check(trim($currentSheet->getCell($qq)->getValue())); //采购员 $purchaseName = post_check(trim($currentSheet->getCell($rr)->getValue())); //包装材料 $packingmaterial = post_check(trim($currentSheet->getCell($ss)->getValue())); //供应商 $capacity = post_check(trim($currentSheet->getCell($tt)->getValue())); if ($goodsLength == '') { $goodsLength = 0; } if ($goodsWidth == '') { $goodsWidth = 0; } if ($goodsHeight == '') { $goodsHeight = 0; } $spu = strpos($spu, '_') === false ? str_pad($spu, 3, '0', STR_PAD_LEFT) : $spu; $sku = strpos($sku, '_') === false ? str_pad($sku, 3, '0', STR_PAD_LEFT) : $sku; // if($department != '采购部' && $spu !=''){ // echo "<br/>非采购部人员不能导入或修改spu的值"; // exit(); // } /* 仓库对应的IDfactory */ if (!empty($warehouseName)) { /* $wh = RelationModel::getWarehouse("and warehouseName='{$warehouseName}'"); var_dump($wh);die; $storeid = $wh[0]['id']; */ $storeid = 1; } /* 货品类别 */ if (!empty($goods_categoryname) && !empty($pid_name)) { $pcate = CategoryModel::getCategoryList("id", "where name='{$pid_name}'"); if ($pcate) { $cate = CategoryModel::getCategoryList("path", "where pid='{$pcate[0]['id']}' and name='{$goods_categoryname}'"); if ($cate) { $goodsCategory = $cate[0]['path']; } else { echo "<br/>第<font color='red'>{$c}</font>条记录中<font color='orange'>货品类别或父类有误,</font>请检查后再提交...<br/>"; exit; } } else { echo "<br/>第<font color='red'>{$c}</font>条记录中<font color='orange'>货品类别或父类有误,</font>请检查后再提交...<br/>"; exit; } //$goodsCategory = "1-3-5"; //调用类别接口获取类别id } if ($purchaseName == "") { echo "有部分数据没填采购名字,请认真检查再提交..."; exit; } else { $purchaseId = 1; //需调用接口获取采购员id } /* 需要调接口获取用户部门 if($department == '采购部' && trim($goodsCategory) == ''){//采购部人员操作时,导入货品类别不能为空 echo "有部分数据没填货品类别,请认真检查再提交..."; exit(); } */ //统计供应商id if (!empty($capacity)) { $capacity_a = array(); $capacity_a = array_filter(explode(',', $capacity)); $capacityid = 1; //调用供应商接口获取id } $data = array('goodsName' => $goodsName, 'goodsDecNameByEN' => $goodsDecNameByEN, 'goodsDecNameByCN' => $goodsDecNameByCN, 'spu' => $spu, 'sku' => $sku, 'goodsCost' => $goodsCost, 'goodsWeight' => $goodsWeight, 'goodsNote' => $goodsNote, 'goodsLength' => $goodsLength, 'goodsWidth' => $goodsWidth, 'goodsHeight' => $goodsHeight, 'goodsCategory' => $goodsCategory, 'goodsCustomsCode' => $goodsCustomsCode, 'goodsDecWorth' => $goodsDecWorth, 'purchaseId' => $purchaseId); $result = GoodsModel::getGoodsList("id", "where sku='{$sku}'"); if (empty($result)) { $data['goodsCreatedTime'] = $nowtime; $data['isNew'] = 1; //默认是新品 /* if(empty($goodsCategory) && $department == '采购部'){//拦截错误的货品类别和父类记录 $cn = $c - 2; echo "<br/>第<font color='red'>$cn</font>条记录中<font color='orange'>货品类别或父类有误,</font>请检查后再提交...<br/>"; exit(); } */ //if(empty($spu) && $department == '采购部'){//首次插入数据时spu必须非空且要符合规范 if (empty($spu)) { //首次插入数据时spu必须非空且要符合规范 echo "spu存在为空的列,请认真检查再提交..."; exit; } //if(!preg_match("/^[A-Z0-9]+(_[A-Z0-9]+)*$/",$spu) && $department == '采购部'){ if (!preg_match("/^[A-Z0-9]+(_[A-Z0-9]+)*\$/", $spu)) { echo "SPU号:<font color='#FF0000'>" . $spu . "</font>书写不规范,请认真检查再提交..."; exit; } /* foreach($sqlnew as $arrnew){//如果在料号转换中存在和导入的sku相同的新料号,则认为该sku仍然是老品 if($arrnew['new_sku'] == $goods_sn){ $isNew = 0; break; } } */ $goodsid = GoodsModel::insertRow($data); if ($goodsid) { $goodsCode = $goodsid + 1000000; $u_data = array('goodsCode' => $goodsCode); $where = "AND id='{$goodsid}'"; if (GoodsModel::update($u_data, $where)) { //插入物品与仓位关联表 if (!empty($storeid)) { $good_wh_data = array('goodsId' => $goodsid, 'locationId' => $storeid); } //跟新sku自动表进入系统 $u_sku_data = array('status' => 2); SpuModel::update($u_sku_data, "AND sku='{$spu}'"); $status = " -[<font color='#33CC33'>物品编号:{$sku} 导入成功 </font>]"; echo $status . '<br>'; } else { $status = " -[<font color='#FF0000'>物品编号:{$sku} 导入失败<br/></font>]"; echo $status . '<br>'; } } /* if($goods_cost){ //增加价格变化到价格表 add by guanyongjun 2013-06-08 $sqlcost = "INSERT INTO ebay_goods_cost (goods_sn,goods_cost,add_time,cguser,storeid,mainsku) VALUES('{$goods_sn}','{$goods_cost}','{$nowtime}','{$cguser}','{$storeid}','".get_mainsku($goods_sn)."')"; echo $sqlcost."<br/>"; $dbcon->execute($sqlcost); } */ } else { if ($spu == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['spu']); } if ($goodsName == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsName']); } if ($goodsDecNameByEN == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsDecNameByEN']); } if ($goodsDecNameByCN == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsDecNameByCN']); } if ($sku == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['sku']); } if ($goodsCost == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsCost']); } if ($goodsWeight == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsWeight']); } if ($goodsNote == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsNote']); } if ($goodsLength == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsLength']); } if ($goodsWidth == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsWidth']); } if ($goodsHeight == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsHeight']); } if ($goodsCategory == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsCategory']); } if ($goodsCustomsCode == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsCustomsCode']); } if ($goodsDecWorth == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['goodsDecWorth']); } if ($purchaseName == '' || !in_array('stock_name', $stockdetailpower)) { unset($data['purchaseId']); } $where = "AND sku='{$sku}'"; if (!empty($data)) { if (GoodsModel::update($data, $where)) { //插入物品与仓位关联表 if (!empty($storeid)) { $good_wh_data = array('goodsId' => $goodsid, 'locationId' => $storeid); RelationModel::insertGoodsWhRow($good_wh_data); } $status = " -[<font color='#FF0000'>物品编号:{$sku} 更新失败</font>]"; echo $status . '<br>'; } else { $status = " -[<font color='#FF0000'>物品编号:{$sku} 更新失败</font>]"; echo $status . '<br>'; } /* if($goods_cost != '' && $goods_cost && in_array('stock_cost', $stockdetailpower) ) { //增加价格变化到价格表 add by guanyongjun 2013-06-08 $sqlcost = "SELECT goods_cost FROM ebay_goods WHERE goods_sn = '{$goods_sn}' AND goods_cost = '{$goods_cost}'"; //echo $sqlcost; //exit; $sqlcost = $dbcon->execute($sqlcost); $sqlcost = $dbcon->getResultArray($sqlcost); if(!$sqlcost){ $costtime = time(); $sqlcost = "INSERT INTO ebay_goods_cost (goods_sn,goods_cost,add_time,cguser,storeid,mainsku) VALUES('{$goods_sn}','{$goods_cost}','{$costtime}','{$cguser}','{$storeid}','".get_mainsku($goods_sn)."')"; echo $sqlcost."<br/>"; $dbcon->execute($sqlcost); } } if($isuse!=='' && in_array('stock_status', $stockdetailpower) ){ //更新价格表状态 add by guanyongjun 2013-06-08 $sqlcost = "UPDATE ebay_goods_cost SET isuse = '".intval($isuse)."' WHERE goods_sn = '{$goods_sn}'"; $dbcon->execute($sqlcost); } */ } else { $status = " -[<font color='#FF0000'>物品编号:{$sku} 已经是最新</font>]"; echo $status . '<br>'; } } echo '<br>'; } }
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(); $query = $data->runQuery("INSERT INTO `pelanggan`(`noreg`, `nama`, `alamat`, `rayon`, `pasar`, `sub_pasar`, \n\t\t\t\t\t\t\t\t`type`, `area`, `kode_pos`, `barcode`, `hapus`) VALUES('{$noreg}', '{$nama}', '{$alamat}', '{$rayon}', '{$pasar}', \n\t\t\t\t\t\t\t\t'{$subpasar}', '{$type}', '{$area}', '{$kodepos}', '{$barcode}', '0');"); }
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('出现错误,请重试!'); }
/** * 导入供应商信息的函数 * 基于 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 view_importBoxData() { require_once WEB_PATH . 'lib/PHPExcel.php'; $uploadfile = date("Y") . date("m") . date("d") . rand(1, 100) . ".xlsx"; $path = WEB_PATH . 'html/template/v1/updload/boxinfo/'; if (move_uploaded_file($_FILES['upfile']['tmp_name'], $path . $uploadfile)) { $importResult = "<font color=green>上传成功</font><br>"; $ismark = 'yes'; } else { $importResult = "<font color=red>上传失败</font><br>"; $ismark = 'no'; } $fileName = $path . $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); $sheet = $PHPExcel->getActiveSheet(); /**取得一共有多少列*/ $c = 2; $importShow = ''; $boxObj = new BoxManageModel(); while (true) { $aa = 'A' . $c; $bb = 'B' . $c; $cc = 'C' . $c; $dd = 'D' . $c; $ee = 'E' . $c; $ff = 'F' . $c; $boxId = trim($sheet->getCell($aa)->getValue()); $length = trim($sheet->getCell($bb)->getValue()); $width = trim($sheet->getCell($cc)->getValue()); $high = trim($sheet->getCell($dd)->getValue()); $weight = trim($sheet->getCell($ee)->getValue()); $netWeight = trim($sheet->getCell($ff)->getValue()); if (empty($boxId)) { break; } $rtnCode = $boxObj->batchUpdBoxInfo($boxId, $length, $width, $high, $weight, $netWeight); if ($rtnCode == 'success') { $importShow .= "<font color=green>箱号[" . $boxId . "]更新成功</font><br/>"; } else { if ($rtnCode == 'failure') { $importShow .= "<font color=red>箱号[" . $boxId . "]更新失败</font><br/>"; } else { if ($rtnCode == 'statusError') { $importShow .= "<font color=red>箱号[" . $boxId . "]未复核,不能更新</font><br/>"; } else { if ($rtnCode == 'null') { $importShow .= "<font color=red>箱号[" . $boxId . "]不存在</font><br/>"; } else { if ($rtnCode == 'moreWeight') { $importShow .= "<font color=red>箱号[" . $boxId . "]净重大于毛重,请复查</font><br/>"; } } } } } $c++; } $navlist = array(array('url' => '', 'title' => '海外仓补货'), array('url' => '', 'title' => '箱号管理'), array('url' => '', 'title' => '箱号信息导入结果')); $toplevel = 2; $this->smarty->assign('navlist', $navlist); //顶层菜单 $this->smarty->assign('toplevel', $toplevel); $this->smarty->assign('importShow', $importShow); $this->smarty->assign('importResult', $importResult); $this->smarty->display('importBoxInfoResult.htm'); }
public function add_questionall() { if (IS_POST) { //提交的配置 $groupid = $_POST['group']; $filePath = $_POST['excelpath']; //提取有效path $filePathparam = explode('Uploads', $filePath); $filePath = './Uploads' . $filePathparam[1]; if (!file_exists($filePath)) { $this->error('请选择文件'); } //导入第三方插件 Amango_Addons_Import('PHPExcel.php', 'Exam'); Amango_Addons_Import('PHPExcel/Reader/Excel2007.php', 'Exam'); $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 . '路径下找到该文件'); } } $startid = 1; $PHPExcel = $PHPReader->load($filePath); /**读取excel文件中的第一个 0 工作表*/ $currentSheet = $PHPExcel->getSheet(0); /**取得最大的列号*/ $allColumn = $currentSheet->getHighestColumn(); /**取得一共有多少行*/ $allRow = $currentSheet->getHighestRow(); $xlsparam = array('A', 'B', 'C'); foreach ($xlsparam as $key => $value) { for ($currentRow = $startid; $currentRow <= $allRow; $currentRow++) { if ($currentRow % 3 != 0) { $rowinfo = $currentSheet->getCell(strtoupper($value) . $currentRow)->getValue(); $rows[$currentRow][$key] = $rowinfo; } } } require_once ONETHINK_ADDON_PATH . 'Exam/functions.php'; $new_group = array_chunk($rows, 2); $total = count($new_group); $newrows = array(); $i = 0; //答案类型 $a_typearr = array_flip($this->a_typearr); //题目类型 $q_typearr = array('文字' => 'text', '图片' => 'image', '音频' => 'audio', '视频' => 'video'); foreach ($new_group as $key => $value) { $num = $i++; $newrows[] = array('paixu' => $total - $num, 'q_title' => set_kb($value[0][1]), 'q_titletype' => set_kb($q_typearr[$value[0][0]]), 'a_typedata' => set_kb($value[0][2]), 'a_type' => set_kb($a_typearr[$value[1][0]]), 'a_choices' => set_kb($value[1][2]), 'q_right' => set_kb($value[1][1])); } $status = D('Addons://Exam/Addonsques')->check_questions($newrows); $Addonsques = M('Addonsques'); if ($status[0] == '0') { $this->error('考题格式错误!错误位置:' . implode(',', $status[1][1])); } foreach ($status[1] as $key => $value) { $value['group'] = $groupid; $Addonsques->add($value); } $this->success('Excel批量导入考题成功!', addons_url('Exam://Exam/list_question', array('id' => $groupid))); } else { $group = I('get.group'); $model = M('Addonsexam'); $testlist = $model->field('title,id')->select(); $this->assign('list', $testlist); $this->assign('info', $this->test_info($group)); $this->assign('group', $group); $this->display(); } }
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'); } }
*/ 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) { for ($c = 0; $c < $num_cols; ++$c) { $cell = $current_sheet->getCellByColumnAndRow($c, $r)->getCalculatedValue(); if (!strcmp($cell, '')) {
/** * @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; }
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'))); }