/** * 获取可以读取类的phpexcel对象 * @return PHPExcel_Reader_Excel2007 */ private function _getReadFileObj() { self::getInstance2007(); if (!self::$_excelObj2007->canRead($this->_filePath)) { self::getInstance5(); if (!self::$_execelObj5->canRead($this->_filePath)) { return false; } else { $this->_instance = self::$_execelObj5; } } else { $this->_instance = self::$_excelObj2007; } }
public function reader($filePath = null, $index = 2) { if ($filePath == null) { $filePath = $_FILES['file']['tmp_name']; } $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { return 'no Excel'; } } $PHPExcel = $PHPReader->load($filePath); $rows = array(); $sheet = $PHPExcel->getSheet(0); $allColumn = $sheet->getHighestColumn(); $allRow = $sheet->getHighestRow(); $allCell = $this->AT[$allColumn]; for ($row = $index; $row <= $allRow; $row++) { $arr = array(); for ($cell = 0; $cell <= $allCell; $cell++) { $val = $sheet->getCellByColumnAndRow($cell, $row)->getValue(); $arr[$this->A[$cell]] = $val; } $rows[] = $arr; } return $rows; }
function updateData($filename, $encode = 'utf-8') { $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filename)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filename)) { echo 'no Excel'; return; } } $PHPExcel = $PHPReader->load($filename); $currentSheet = $PHPExcel->getSheet(0); $allColumn = $currentSheet->getHighestColumn(); $allRow = $currentSheet->getHighestRow(); $excelData = ['name' => $currentSheet->getCellByColumnAndRow(A, 1)->getValue(), 'version' => $currentSheet->getCellByColumnAndRow(A, 2)->getValue()]; $keys = []; for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) { $keys[$currentColumn] = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, 5)->getValue(); } for ($currentRow = 6; $currentRow <= $allRow; $currentRow++) { $tmp = []; for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) { $tmpv = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue(); $tmp[$keys[$currentColumn]] = in_array($keys[$currentColumn], ['Id', 'Exp', 'Hp', 'Attack', 'Defense']) ? intval($tmpv) : floatval($tmpv); } if (!empty($tmp)) { $excelData['list'][] = $tmp; } } return $excelData; }
/** * 导入代理商信息 * @method import */ public function import() { $file_info = R('Public/UploadExcel'); //上传excel并返回上传路径 if (!file_exists($file_info)) { //上传失败则输出 $error_info['status'] = 0; $error_info['info'] = $file_info; die(json_encode($error_info)); } import('Common.Tools.PHPExcel.PHPExcel'); $PHPExcel = new \PHPExcel(); $PHPReader = new \PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($file_info)) { $PHPReader = new PHPExcel_Reader_Excel5(); } if (!$PHPReader->canRead($file_info)) { $error_info['status'] = 0; $error_info['info'] = '无法读取文件'; die(json_encode($error_info)); } $PHPExcel = $PHPReader->load($file_info); $currentSheet = $PHPExcel->getSheet(0); $rowTotal = $currentSheet->getHighestRow(); //总行数 $columnTotal = $currentSheet->getHighestColumn(); //总列数 $columnTotal = \PHPExcel_Cell::columnIndexFromString($columnTotal); //字母列转换为数字列 $_list = array(); for ($i = 1; $i <= $rowTotal; $i++) { for ($j = 0; $j <= $columnTotal; $j++) { $val = $currentSheet->getCellByColumnAndRow($j, $i)->getValue(); if (!empty($val)) { $_list[$i - 1][] = $val; } } } foreach ($_list as $_k => $_v) { $list[$_k]['name'] = $_v[0]; $list[$_k]['mobile'] = $_v[1]; $list[$_k]['id_card'] = $_v[2]; $list[$_k]['wechat_number'] = $_v[3]; $list[$_k]['class'] = $_v[4]; $list[$_k]['authorize_code'] = $_v[5]; $list[$_k]['ctime'] = now(); $list[$_k]['mtime'] = now(); } $result = D('Agent')->addAll($list); if ($result) { $error_info['status'] = 1; $error_info['info'] = '导入成功'; } else { $error_info['status'] = 0; $error_info['info'] = '导入数据失败'; } die(json_encode($error_info)); }
public static function readExcel($filePath) { self::init(); $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { return null; } } return $PHPReader->load($filePath); }
/** * *函数名: exportToExcelWithHeader *输入: 模板excel的路径 $filePath,例如 'C:\\123.xls' *输入: 二维数组 要导出的内容 $dataList * array( * array(key1=>$value1,key2=>$value2,...), * array(key1=>$value1,key2=>$value2,...),... * ) *输入: 一维数组 生成excel的头部信息 $headerList * array(数据库前台字段key1,数据库前台字段key2,...) *输入: string $OutputFileName 输出文件名(建议输出文件名带时间戳) *输入: $outputType 默认0输出到浏览器,输入其他保存到文件服务器并成功返回相对地址,失败返回NULL *创建人: 石昌民 *创建时间: 2015-9-30 */ public function exportToExcelWithHeader($filePath, $dataList, $headerList, $OutputFileName, $outputType = 0) { require './ThinkPHP/Library/Org/PHPExcel/PHPExcel.php'; //实例化Excel读取类 $PHPReader = new \PHPExcel_Reader_Excel2007(); $fileWebService = new FileWebController($this->userID); if (!$PHPReader->canRead($filePath)) { $PHPReader = new \PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { return false; } } //读取Excel $PHPExcel = $PHPReader->load($filePath); /**读取excel文件中的第一个工作表*/ $currentSheet = $PHPExcel->getSheet(0); /**取得一共有多少行*/ $allRow = $currentSheet->getHighestRow(); $allRow++; //实例化Excel写入类 $PHPWriter = new \PHPExcel_Writer_Excel5($PHPExcel); //遍历数据列表中的内容,从第allRow行开始 $i = $allRow; foreach ($dataList as $key => $value) { $position = 'A'; foreach ($headerList as $headerKey => $headerValue) { $currentSheet->setCellValueExplicit("{$position}" . $i, $dataList[$key][$headerValue], \PHPExcel_Cell_DataType::TYPE_STRING); //headerKey为数据库前台字段 $position++; } $i++; } ob_end_clean(); //清除缓冲区,避免乱码 //决定是导出到浏览器还是文件服务器 if ($outputType !== 0) { $path = Constant::WEBSERVER_EXCEL_SAVEPATH . "{$OutputFileName}.xls"; $PHPWriter->save($path); // dump($path); // dump(file_exists($path)); $fileURL = $fileWebService->uploadFile($path, "{$OutputFileName}.xls"); if ($fileURL == false) { $fileURL = null; } return $fileURL; return false; } else { header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment;filename={$OutputFileName}.xls"); header('Cache-Control: max-age=0'); $PHPWriter->save('php://output'); } }
public function loadExcel($file) { // $PHPExcelReader = new PHPExcel_Reader_Excel2007(); // //为了可以读取所有版本Excel文件(读2007的报错) // if(!$PHPExcelReader->canRead($file)) // { $PHPExcelReader = new PHPExcel_Reader_Excel5(); if (!$PHPExcelReader->canRead($file)) { return; } // } $this->excelReaderObj = $PHPExcelReader->load($file); }
function excel($postdata) { require_once 'Excel_reader.php'; require_once 'Classes/PHPExcel.php'; $tmp = $postdata; $save_path = "./"; //暂时上传的文件路径 $file_name = $save_path . date('Ymdhis') . ".xls"; //上传后的文件保存路径和名称 if (copy($tmp, $file_name)) { } $PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); //为了可以读取所有版本Excel文件 if (!$PHPReader->canRead($file_name)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($file_name)) { echo '未发现Excel文件!'; return; } } //不需要读取整个Excel文件而获取所有工作表数组的函数,感觉这个函数很有用,找了半天才找到 $sheetNames = $PHPReader->listWorksheetNames($file_name); //读取Excel文件 $PHPExcel = $PHPReader->load($file_name); //获取工作表的数目 $sheetCount = $PHPExcel->getSheetCount(); for ($i = 0; $i < $sheetCount; $i++) { //选择第一个工作表 $currentSheet = $PHPExcel->getSheet($i); //取得一共有多少列 $allColumn = $currentSheet->getHighestColumn(); //取得一共有多少行 $allRow = $currentSheet->getHighestRow(); for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) { for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) { $address = $currentColumn . $currentRow; $data[$i][$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue(); //echo ."\t"; } } //$data[$i]=$currentSheet; } unlink($file_name); //删除上传的文件 return $data; }
public function get_rows_from_excel($filepath, &$infos) { if (empty($filepath)) { return $infos; } $php_reader = new PHPExcel_Reader_Excel2007(); if (!$php_reader->canRead($filepath)) { $php_reader = new PHPExcel_Reader_Excel5(); if (!$php_reader->canRead($filepath)) { return $infos; } } $cur_excel = $php_reader->load($filepath); $cur_sheet = $cur_excel->getSheet(0); $all_column = $cur_sheet->getHighestColumn(); //取得一共有多少列 $all_row = $cur_sheet->getHighestRow(); //取得一共有多少行 $infos_have = array(); $infos_error = array(); $i = 0; $j = 0; for ($cur_row = 1; $cur_row <= $all_row; $cur_row++) { $flagadd = FALSE; $tmprow = array(); $tmpchar = ''; for ($cur_column = 'A'; $cur_column <= $all_column; $cur_column++) { $address = $cur_column . $cur_row; $curinfo = trim($cur_excel->getActiveSheet()->getCell($address)->getvalue()); //if($cur_column == 'A') //{ if (!empty($curinfo)) { $flagadd = TRUE; } //} $tmprow[] = $curinfo; $tmpchar .= $curinfo; } if ($flagadd) { $infos['infos_have'][$i] = $tmprow; $i++; } elseif (!empty($tmpchar)) { $infos['infos_error'][$j] = $tmprow; $j++; } } }
public function index() { // $xq_list = $this->manage_model->get_all_xiaoqu_list(); // foreach ($xq_list as $xq) { // $name = str_replace("•","",$xq['name']); // //echo $xq['id'] . ": " . $this->encode($name) . '<br>'; // $xq['jianpin'] = $this->encode($name); // $this->manage_model->update_xiaoqu_jianpin($xq); // } // die('DONE'); require_once APPPATH . 'libraries/PHPExcel/PHPExcel.php'; $PHPExcel = new \PHPExcel(); $PHPReader = new \PHPExcel_Reader_Excel2007(); $file = 'E:/broker.xlsx'; //为了可以读取所有版本Excel文件 if (!$PHPReader->canRead($file)) { $PHPReader = new \PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($file)) { echo '未发现Excel文件!'; return; } } //不需要读取整个Excel文件而获取所有工作表数组的函数,感觉这个函数很有用,找了半天才找到 $sheetNames = $PHPReader->listWorksheetNames($file); //读取Excel文件 $PHPExcel = $PHPReader->load($file); //获取工作表的数目 $sheetCount = $PHPExcel->getSheetCount(); //选择第一个工作表 $currentSheet = $PHPExcel->getSheet(0); //取得一共有多少列 $allColumn = $currentSheet->getHighestColumn(); //取得一共有多少行 $allRow = $currentSheet->getHighestRow(); //循环读取数据,默认编码是utf8,这里转换成gbk输出 $data = array(); for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $name = trim($currentSheet->getCell("A" . $currentRow)->getValue()); $tel = trim($currentSheet->getCell("B" . $currentRow)->getValue()); $data[] = array('username' => $tel, 'passwd' => sha1('888888'), 'tel' => $tel, 'company_name' => "顺达地产", 'rel_name' => $name, 'region_id' => 6, 'admin_group' => 2); } $this->manage_model->add_broker_batch($data); }
function format_excel2array($filePath = '', $sheet = 0) { if (empty($filePath) or !file_exists($filePath)) { die('file not exists'); } $PHPReader = new PHPExcel_Reader_Excel2007(); //建立reader对象 if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { echo 'no Excel'; return; } } $PHPExcel = $PHPReader->load($filePath); //建立excel对象 $currentSheet = $PHPExcel->getSheet($sheet); //**读取excel文件中的指定工作表*/ $allColumn = $currentSheet->getHighestColumn(); //**取得最大的列号*/ $allRow = $currentSheet->getHighestRow(); //**取得一共有多少行*/ $data = array(); for ($rowIndex = 1; $rowIndex <= $allRow; $rowIndex++) { //循环读取每个单元格的内容。注意行从1开始,列从A开始 for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) { $addr = $colIndex . $rowIndex; $cell = $currentSheet->getCell($addr)->getValue(); if ($cell instanceof PHPExcel_RichText) { //富文本转换字符串 $cell = $cell->__toString(); } $data[$rowIndex][$colIndex] = $cell; } } return $data; }
public function import($filePath) { $this->__construct(); $PHPExcel = new PHPExcel(); /**默认用excel2007读取excel,若格式不对,则用之前的版本进行读取*/ $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { echo 'no Excel'; return; } } $PHPExcel = $PHPReader->load($filePath); $currentSheet = $PHPExcel->getSheet(0); //读取excel文件中的第一个工作表 $allColumn = $currentSheet->getHighestColumn(); //取得最大的列号 $allRow = $currentSheet->getHighestRow(); //取得一共有多少行 $erp_orders_id = array(); //声明数组 /**从第二行开始输出,因为excel表中第一行为列名*/ for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { /**从第A列开始输出*/ for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue(); /**ord()将字符转为十进制数*/ //if($val!=''){ $erp_orders_id[] = $val; //} /**如果输出汉字有乱码,则需将输出内容用iconv函数进行编码转换,如下将gb2312编码转为utf-8编码输出*/ //echo iconv('utf-8','gb2312', $val)."\t"; } } return $erp_orders_id; }
/** * ShipfeeQueryAct::actBatchShipfeeQueryImport() * 批量运费验证信息导入导出 * @return array */ public function actBatchShipfeeQueryImport() { $data = array(); $uid = intval($_SESSION[C('USER_AUTH_SYS_ID')]); if (isset($_FILES['upfile']) && !empty($_FILES['upfile'])) { $fielName = $uid . "_batch_shipfee_" . date('YmdHis') . '_' . rand(1, 3009) . ".xls"; $fileName = WEB_PATH . 'html/temp/' . $fielName; if (move_uploaded_file($_FILES['upfile']['tmp_name'], $fileName)) { $filePath = $fileName; } } if (substr($filePath, -3) != 'xls') { show_message($this->smarty, "导入的文件名格式错误!", "index.php?mod=shipfeeQuery&act=shipfeeQueryImport"); @unlink($filePath); exit; } //读取导入文件 require_once WEB_PATH . "lib/PHPExcel.php"; //如果读取的表较大,需要调整内存和时间限制 ini_set('memory_limit', '20M'); // ini_set('max_execution_time', '2'); $PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { show_message($this->smarty, "文件内容无法读取!", "index.php?mod=shipfeeQuery&act=shipfeeQueryImport"); @unlink($filePath); exit; } } $PHPExcel = $PHPReader->load($filePath); $currentSheet = $PHPExcel->getSheet(0); //取得共有多少列,若不使用此静态方法,获得的$col是文件列的最大的英文大写字母 $cols = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn()); $rows = $currentSheet->getHighestRow(); if ($rows > C('EXCELL_ROWS_MAX')) { show_message($this->smarty, "单个EXCELL文件行数超过系统处理最大值: <b>" . C('EXCELL_ROWS_MAX') . "</b> 行!", "index.php?mod=shipfeeQuery&act=shipfeeQueryImport"); @unlink($filePath); exit; } $row = 1; $res = array(); while (1) { $flag = true; $rowFlag = true; $country = ''; $weight = 0; $tracknum = ''; $carrier = ''; $carrierId = 0; $fee = 0; $totalFee = 0; $carrierNew = ''; $aa = 'A' . $row; $bb = 'B' . $row; $cc = 'C' . $row; $dd = 'D' . $row; $ee = 'E' . $row; $ff = 'F' . $row; $gg = 'G' . $row; $country = post_check(trim($currentSheet->getCell($aa)->getValue())); $tracknum = post_check(trim($currentSheet->getCell($bb)->getValue())); $weight = post_check(trim($currentSheet->getCell($cc)->getValue())); $carrier = post_check(trim($currentSheet->getCell($dd)->getValue())); $carrierNew = post_check(trim($currentSheet->getCell($ee)->getValue())); $totalFee = post_check(trim($currentSheet->getCell($ff)->getValue())); $fee = post_check(trim($currentSheet->getCell($gg)->getValue())); if (empty($country)) { break; } if ($row == 1) { if ($country != '国家' || $tracknum != '挂号条码' || $weight != '重量' || $carrier != '运输方式' || $carrierNew != '最优运输方式' || $totalFee != '总运费' || $fee != '折扣运费') { show_message($this->smarty, '<font color="red">文件导入失败,导入模版内容有误,请勿修改表头</font>', "index.php?mod=shipfeeQuery&act=shipfeeQueryImport"); @unlink($filePath); exit; } $res[] = array("国家", "挂号条码", "重量", "运输方式", "最优运输方式", "总运费", "折扣运费"); } else { if (!empty($tracknum)) { $carrierId = self::getErpCarrierId($carrier); } if (empty($carrierId)) { $fees = TransOpenApiAct::act_getBestCarrierNew(1, $country, $weight); } else { $fees = TransOpenApiAct::act_fixCarrierQueryNew($carrierId, $country, $weight); } if (empty($fees)) { self::$errMsg .= "运费校验失败:{$carrier}==={$country}==={$weight}!<br/>"; $res[] = array($country, $tracknum, $weight, $carrier, '', '', ''); } else { $fee = $fees['fee']; $totalFee = $fees['totalFee']; $carrierNew = self::getErpCarrierId($fees['carrierId'], true); $res[] = array($country, $tracknum, $weight, $carrier, $carrierNew, $totalFee, $fee); } } $row++; } $fileName = 'batch_shipfee_infos_' . $uid; $fileUrl = WEB_URL . "temp/" . date('Ymd') . "/" . $fileName . ".xls"; $filePath = WEB_PATH . "html/temp/" . date('Ymd') . "/" . $fileName . ".xls"; require_once WEB_PATH . "lib/php-export-data.class.php"; $excel = new ExportDataExcel('file'); $excel->filename = $filePath; $excel->initialize(); foreach ($res as $row) { $excel->addRow($row); } $excel->finalize(); unset($res); $data['url'] = $fileUrl; $data['res'] = self::$errMsg; return $data; }
public function index() { //提交的配置 $config = $_POST['config']; $filePath = AMANGO_FILE_ROOT . $config['excel_path']; $currentid = $config['excel_readxls'] >= 1 ? $config['excel_readxls'] - 1 : 0; $startid = $config['excel_currentrow'] >= 2 ? $config['excel_currentrow'] : 2; if ($filePath == AMANGO_FILE_ROOT) { $this->error('请选择文件'); } $xlsparam = parse_config($_POST['config']['excel_param']); //导入第三方插件 Amango_Addons_Import('PHPExcel.php', 'Excelimport'); Amango_Addons_Import('PHPExcel/Reader/Excel2007.php', 'Excelimport'); //自动生成唯一数据表名 $table_name = 'addonsexcel' . str_replace('_', '', strtolower($config['excel_tablename'])); $tablename = C('DB_PREFIX') . $table_name; $sql = <<<sql \t\t\t\tSHOW TABLES LIKE '{$tablename}'; sql; $res = M()->query($sql); //判断表唯一 以便生成新表 if (count($res) > 0) { $this->error('该表名已存在,请换个数据表名'); } else { if (strpos($_POST['config']['excel_param'], ':') === false) { $this->error('生成数据表时,请务必填写字段读取配置,格式:字段名:读取列标识'); } else { $fields = ''; $field = array(); foreach ($xlsparam as $key => $value) { if (!empty($key)) { $fields .= "`{$key}` text,"; $field[] = $key; } } //生成数据表 $sql = <<<sql \t\t\t\tCREATE TABLE IF NOT EXISTS `{$tablename}` ( \t\t\t\t`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' , \t\t\t\t{$fields} \t\t\t\tPRIMARY KEY (`id`) \t\t\t\t) \t\t\t\tENGINE=MyISAM \t\t\t\tDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci \t\t\t\tCHECKSUM=0 \t\t\t\tROW_FORMAT=DYNAMIC \t\t\t\tDELAY_KEY_WRITE=0 \t\t\t\t; sql; $res = M()->execute($sql); if ($res === false) { $this->error('建立数据表出错,请重新建表'); } } } //导入数据表名 存入总表 D('addonsexcel')->add(array('fileds' => implode(',', $field), 'tablename' => $tablename)); $PHPExcel = new \PHPExcel(); /**默认用excel2007读取excel,若格式不对,则用之前的版本进行读取*/ $PHPReader = new \PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new \PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { $this->error('无法在' . $filePath . '路径下找到该文件'); } } $PHPExcel = $PHPReader->load($filePath); /**读取excel文件中的第一个工作表*/ $currentSheet = $PHPExcel->getSheet($currentid); /**取得最大的列号*/ $allColumn = $currentSheet->getHighestColumn(); /**取得一共有多少行*/ $allRow = $currentSheet->getHighestRow(); //初始化 $rows = array(); foreach ($xlsparam as $key => $value) { for ($currentRow = $startid; $currentRow <= $allRow; $currentRow++) { $rowinfo = $currentSheet->getCell(strtoupper($value) . $currentRow)->getValue(); $rows[$currentRow][$key] = $config['excel_parxhtml'] == 1 ? strip_tags($rowinfo) : $rowinfo; } } //读取出总记录 foreach ($rows as $key => $value) { M($table_name)->add($value); } M('addonsexcel')->where(array('tablename' => $tablename))->save(array('rows' => count($rows))); $this->success('新增成功', U('Addons/adminList', array('name' => 'Excelimport'))); }
/** * 生成订单附件 * @param $order_data 要写入订单模板的数据数组 * @param $attachment_path = '' 附件生成路径 * @return string|bool */ public static function create_order_attachment($order_data, $attachment_path = '') { if (!empty($order_data) && is_array($order_data)) { // 引入PHPExcel扩展 Yii::createComponent('application.extensions.excel.PHPExcel'); // 租车订单Excel附件 - 模板目录 $templet_path = ASSETS_FILE . 'templet/tichedan.xls'; // 租车订单Excel附件 - 正式目录 $attachment_path = $attachment_path == '' ? SECRET_FILE_PATH . 'Carrental/' . date('Y-m-d', time()) . '/' : $attachment_path; try { // 正式目录不存在则创建 if (!file_exists($attachment_path)) { if (!DirectoryFile::dirCreate($attachment_path)) { throw new Exception('创建目录失败!'); } } // 如模板文件存在则复制到正式目录下 if (file_exists($templet_path)) { // 生成附件名称 $attachment_name = 'order_' . time() . '_' . rand(100, 10000) . '.xls'; // 复制租车订单模板至正式目录下 $status = @copy($templet_path, $attachment_path . $attachment_name); if (!$status) { throw new Exception('模板文件移动失败!'); } } else { throw new Exception('订单模板文件不存在!'); } // 实例化Excel读取类 $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($attachment_path . $attachment_name)) { $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($attachment_path . $attachment_name)) { throw new Exception('不是一个Excel文件!'); } } // 读取Excel工作表 $PHPExcel = $PHPReader->load($attachment_path . $attachment_name); $currentSheet = $PHPExcel->getSheet(0); // 重写Excel工作表 将单项信息写入Excel单元格 $currentSheet->setCellValue('B3', !empty($order_data[0]) ? $order_data[0] : ''); // 预定号[reservation number] $currentSheet->setCellValue('D3', $order_data[1]); // 确认号码[confirmation number] $currentSheet->setCellValue('B5', $order_data[2]); // 租车人姓名[Name] $currentSheet->setCellValue('D5', $order_data[3]); // 航班号码[Flight number] $currentSheet->setCellValue('B19', $order_data[4]); // 车辆代码[Car Code] $currentSheet->setCellValue('D19', $order_data[5]); // 车门数[Doors] $currentSheet->setCellValue('D22', $order_data[6]); // 空调[Air Con] $currentSheet->setCellValue('D20', $order_data[7]); // 座位数[Car Seats] $currentSheet->setCellValue('B9', $order_data[8]); // 取车日期 $currentSheet->setCellValue('B10', $order_data[9]); // 取车时间 $currentSheet->setCellValue('B11', $order_data[10]); // 取车门店[Location] $currentSheet->setCellValue('B12', $order_data[11]); // 取车地址[Address] $currentSheet->setCellValue('B13', $order_data[12]); // 取车地电话[Tel] $currentSheet->setCellValue('D9', $order_data[13]); // 还车日期 $currentSheet->setCellValue('D10', $order_data[14]); // 还车时间 $currentSheet->setCellValue('D11', $order_data[15]); // 还车门店[Location] $currentSheet->setCellValue('D12', $order_data[16]); // 还车地址[Address] $currentSheet->setCellValue('D13', $order_data[17]); // 还车地电话[Tel] $currentSheet->setCellValue('B23', $order_data[18]); // 确认号码[confirmation number] $currentSheet->setCellValue('B18', $order_data[19]); // 车行名称Supplier $currentSheet->setCellValue('B20', $order_data[20]); // 车辆类型 Car Type $currentSheet->setCellValue('D21', $order_data[21]); //变速器类型transmission $currentSheet->setCellValueExplicit('B22', $order_data[22], PHPExcel_Cell_DataType::TYPE_STRING); //车行的账户号transmission $currentSheet->setCellValue('A26', $order_data[23]); //里程限制 $currentSheet->setCellValue('A27', $order_data[24]); //车辆保险 $currentSheet->setCellValue('A28', $order_data[25]); //车辆包含的税费 $currentSheet->setCellValue('A30', $order_data[26]); //预定的额外服务 // 执行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('参数不正确!'); } }
/** * 批量导入箱子信息方法 */ 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 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 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(); } }
public function actionReadNumberExcel() { sleep(2); Yii::$app->response->format = \yii\web\Response::FORMAT_JSON; $request = Yii::$app->request; $fileB64 = $request->post('fileB64'); $content = $request->post('content'); $modelGroupId = $request->post('modelGroupId'); //delete all sms model by groupId //根据groupid删除各自短信模板 但是导入之后前台刷新会丢失groupid 会造成数据库数据累积 //表中含有accountid字段可以根据该字段删除所有模板 if ($modelGroupId != -1) { SmsModel::deleteAll(['groupId' => $modelGroupId]); } $file = base64_decode(substr($fileB64, strpos($fileB64, ";base64,") + 8)); $filePath = Yii::getAlias('@runtime') . '/numberExcel' . date('his') . '.csv'; file_put_contents($filePath, $file); //csv 由xlsx转换 第一列为号码 第二列替换param1 第三列param2 $phpReader = new \PHPExcel_Reader_Excel2007(); if (!$phpReader->canRead($filePath)) { $phpReader = new \PHPExcel_Reader_Excel5(); if (!$phpReader->canRead($filePath)) { $phpReader = new \PHPExcel_Reader_CSV(); if (!$phpReader->canRead($filePath)) { unlink($filePath); return ['fileError' => true]; } } } $phpExcel = $phpReader->load($filePath); $sheets = $phpExcel->getAllSheets(); $recordCount = -1; for ($si = 0; $si < sizeof($sheets); $si++) { $sheet = $sheets[$si]; $ingredientFinished = false; $rowCount = $sheet->getHighestRow(); $recordCount = $rowCount; $highestCol = $sheet->getHighestColumn(); // LogUtil::error(date('Y-m-d h:i:s') . ' $rowCount: ' . $rowCount . ' $highestCol: ' . $highestCol); $colCount = ord($highestCol) - 65; $currentGroupId = $this->guid(); // LogUtil::error(date('Y-m-d h:i:s') . ' $getAccountId: ' . $this->getAccountId()); for ($row = 1; $row <= $rowCount; $row++) { $realModel = $content; $number = ''; for ($col = 0; $col <= $colCount; $col++) { $val = $sheet->getCellByColumnAndRow($col, $row)->getValue(); $val = trim((string) $val); if ($val === '') { continue; } if ($col == 0) { $number = $val; } if ($col > 0) { $realModel = str_replace('%param' . $col . '%', $val, $realModel); } } //如果存在空白行则会把模板存入 $SmsModel = new SmsModel(); $SmsModel->groupId = $currentGroupId; $SmsModel->mobile = $number; $SmsModel->content = $realModel; $SmsModel->accountId = $this->getAccountId(); $SmsModel->save(); } } LogUtil::error(date('Y-m-d h:i:s') . ' $modelGroupId: ' . $currentGroupId); return ['recordCount' => $recordCount, 'modelGroupId' => $currentGroupId]; }
echo time() . '</br>'; ini_set('memory_limit', '1024M'); ini_set('max_execution_time', '600'); error_reporting(E_ERROR); require_once '/PHPExcel/PHPExcel.php'; require_once 'define.php'; //定义数据源文件 $file_name = 'text2.xlsx'; //工作表数 $sheet_num = 1; //========================= $php_excel_obj = new PHPExcel(); $php_reader = new PHPExcel_Reader_Excel2007(); if (!$php_reader->canRead($file_name)) { $php_reader = new PHPExcel_Reader_Excel5(); if (!$php_reader->canRead($file_name)) { echo 'NO Excel!'; } } $php_excel_obj = $php_reader->load($file_name); $excel_obj = new PHPExcel(); $objWriter = new PHPExcel_Writer_Excel5($excel_obj); for ($i = 0; $i <= $sheet_num - 1; $i++) { $current_sheet = $php_excel_obj->getSheet($i); $all_column = $current_sheet->getHighestColumn(); $all_row = $current_sheet->getHighestRow(); $all_arr = array(); $c_arr = array(); for ($r_i = 1; $r_i <= $all_row; $r_i++) { $c_arr = array(); for ($c_i = 'A'; $c_i <= $all_column; $c_i++) {
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>'; } }
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('出现错误,请重试!'); }
function up_excel($name) { //读取文件 require "../include/config.inc.php"; require "../excel/PHPExcel.php"; //$db = new DB_test(); $filePath = "../upload/" . $name; //$filePath = "./upload/kk.xlsx"; $PHPExcel = new PHPExcel(); $PHPReader = new PHPExcel_Reader_Excel2007(); if (!$PHPReader->canRead($filePath)) { $PHPReader = new PHPExcel_Reader_Excel5(); if (!$PHPReader->canRead($filePath)) { echo "<script>alert('读取文件错误!');<script>"; return; //echo 'no Excel';return ; } } $PHPExcel = $PHPReader->load($filePath); $currentSheet = $PHPExcel->getSheet(0); $allColumn = $currentSheet->getHighestColumn(); $allRow = $currentSheet->getHighestRow(); $i = 0; for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) { //$val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65,$currentRow)->getValue(); $val2 = $currentSheet->getCellByColumnAndRow($x - 65, $currentRow); $val = $val2->getValue(); if ($currentColumn == 'A') { $a = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'B') { $b = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'C') { $c = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'D') { $d = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'E') { $e = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'F') { $f = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'G') { $g = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'H') { $h = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'I') { $i = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'J') { $j = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'K') { $k = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'L') { $l = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'M') { $m = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'N') { $n = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'O') { $o = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'P') { $p = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'Q') { $q = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'R') { $r = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'S') { $s = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'T') { $t = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'U') { $u = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'V') { $v = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'W') { $w = iconv('utf-8', 'gb2312', $val); } elseif ($currentColumn == 'X') { $x = iconv('utf-8', 'gb2312', $val); } } $value = array(); //$value[$i] = " '$a', '$b', '$c', '$d', '$e', '$f', '$g', '$h', '$i', '$j', '$k', '$l', '$m', '$n', '$o', '$p', '$q', '$r', '$s', '$t', '$u', '$v', '$w', '$x'"; //$i++; return "'{$a}', \t'{$b}', \t'{$c}', \t'{$d}', \t'{$e}', \t'{$f}', \t'{$g}', \t'{$h}', \t'{$i}', \t'{$j}', \t'{$k}', \t'{$l}', \t'{$m}', \t'{$n}', \t'{$o}', \t'{$p}', '{$q}', \t'{$r}', \t'{$s}', \t'{$t}', \t'{$u}', \t'{$v}', \t'{$w}', \t'{$x}'"; //插入数据 // return $sql = "INSERT INTO tb_uploadfile ( td_uploadfile_khid, td_uploadfile_bianhao, td_uploadfile_quchen, td_uploadfile_money, td_uploadfile_sale_dunshu, td_uploadfile_khxz, td_uploadfile_sfzh_zch, td_uploadfile_frman, td_uploadfile_zczb, td_uploadfile_clrq, td_uploadfile_zylxr, td_uploadfile_zhiwei, td_uploadfile_phone, td_uploadfile_tel1, td_uploadfile_tel2, td_uploadfile_fax, td_uploadfile_address, td_uploadfile_jyzk, td_uploadfile_bankname, td_uploadfile_bankno, td_uploadfile_sh, td_uploadfile_email, td_uploadfile_time, td_uploadfile_jg ) VALUES ( '$a', '$b', '$c', '$d', '$e', '$f', '$g', '$h', '$i', '$j', '$k', '$l', '$m', '$n', '$o', '$p', '$q', '$r', '$s', '$t', '$u', '$v', '$w', '$x')"; //$db->query($sql); } return $value; }
/** * 读取Excel * @param string $filePath Excel文件 * @return array */ public function readExcel($filePath) { $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); /**读取excel文件中的第一个工作表*/ $currentSheet = $PHPExcel->getSheet(0); /**取得最大的列号*/ $allColumn = $currentSheet->getHighestColumn(); /**取得一共有多少行*/ $allRow = $currentSheet->getHighestRow(); $data = array(); /**从第二行开始输出,因为excel表中第一行为列名*/ for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { /**从第A列开始输出*/ $tmp = array(); for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) { $val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue(); /**ord()将字符转为十进制数*/ $tmp[] = $val; } $data[] = $tmp; } return $data; }
/** * 读Excel * @param string $path * @return \PHPExcel|void * @throws \PHPExcel_Reader_Exception */ public static function readExcelFile($path) { $reader = new \PHPExcel_Reader_Excel2007(); if (!$reader->canRead($path)) { $reader = new \PHPExcel_Reader_Excel5(); if (!$reader->canRead($path)) { echo "no Excel!"; return; } } return $reader->load($path); }
/** * Import contacts from excel. * * @access public * @return void */ public function import() { if ($_SERVER['REQUEST_METHOD'] == 'POST') { $file = $this->loadModel('file')->getUpload('files'); if (empty($file)) { $this->send(array('result' => 'fail', 'message' => $this->lang->contact->noFile)); } $file = $file[0]; move_uploaded_file($file['tmpname'], $this->file->savePath . $file['pathname']); $file = $this->file->savePath . $file['pathname']; $phpExcel = $this->app->loadClass('phpexcel'); $phpReader = new PHPExcel_Reader_Excel2007(); if (!$phpReader->canRead($file)) { $phpReader = new PHPExcel_Reader_Excel5(); if (!$phpReader->canRead($file)) { unlink($file); die(js::alert($this->lang->excel->canNotRead)); } } $this->session->set('importFile', $file); $this->send(array('result' => 'success', 'locate' => inlink('showImport'))); } $this->view->title = $this->lang->import; $this->display('file', 'import'); }
/** * 导入供应商信息的函数 * 基于 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 actionToCookbook() { sleep(2); Yii::$app->response->format = \yii\web\Response::FORMAT_JSON; $request = Yii::$app->request; $fileB64 = $request->post('fileB64'); $file = base64_decode(substr($fileB64, strpos($fileB64, ";base64,") + 8)); $filePath = Yii::getAlias('@runtime') . '/cookbook' . date('his'); file_put_contents($filePath, $file); $phpReader = new \PHPExcel_Reader_Excel2007(); if (!$phpReader->canRead($filePath)) { $phpReader = new \PHPExcel_Reader_Excel5(); if (!$phpReader->canRead($filePath)) { $phpReader = new \PHPExcel_Reader_CSV(); if (!$phpReader->canRead($filePath)) { unlink($filePath); return ['fileError' => true]; } } } $phpExcel = $phpReader->load($filePath); $sheets = $phpExcel->getAllSheets(); $cookbookTitles = []; for ($si = 0; $si < sizeof($sheets); $si++) { $sheet = $sheets[$si]; $rowTemp = []; $cowTemp = []; $ingredientFinished = false; $rowCount = $sheet->getHighestRow(); $highestCol = $sheet->getHighestColumn(); $colCount = ord($highestCol) - 65; $cookbook = []; //There has a bug //When the 'cuisineType' row does not exist, the $rowCount will be infinity //The code blow can avoid this bug $rowCount = $rowCount > 100 ? 100 : $rowCount; for ($row = 1; $row <= $rowCount; $row++) { for ($col = 0; $col <= $colCount; $col++) { $val = $sheet->getCellByColumnAndRow($col, $row)->getValue(); $val = trim((string) $val); if ($val === '') { continue; } // Fill title and image if (!isset($cookbook['title'])) { $arr = explode('-', $val, 2); if (empty($arr) || sizeof($arr) < 2) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: title is required'); return ['contentError' => true]; } if (mb_strlen(trim(trim($arr[1])), 'utf-8') > 30) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: title should less than 30 words'); return ['titleLengthError' => true]; } $cookbook['image'] = Yii::$app->qiniu->domain . '/' . trim($arr[0]) . '.jpg'; $cookbook['title'] = trim(trim($arr[1])); unset($arr); continue; } // Find category row if (!isset($rowTemp['category'])) { if (!preg_match('/^category$/i', $val)) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: category is required'); return ['contentError' => true]; } $rowTemp['category'] = $row; continue; } // Fill category if ($rowTemp['category'] === $row) { //The first sheet's category row will leads to a bug if ($si == 0) { $firstCate = $val; } $arr = $this->_spiltByComma($val); // $arr = preg_split('/[,,]/', $val); $cookbook['category'] = []; foreach ($arr as $v) { $v = trim($v); if ($v != '') { $cookbook['category'][] = trim($v); } } $row++; $col = -1; unset($arr); continue; } // Find subCategory row if (!isset($rowTemp['subCategory'])) { if (!preg_match('/^sub[\\s\\n]*category$/i', $val)) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: subCategory is required'); return ['contentError' => true]; } $rowTemp['subCategory'] = $row; continue; } // Fill subCategory if ($rowTemp['subCategory'] === $row) { // $arr = preg_split('/[,,]/', $val); $arr = $this->_spiltByComma($val); $cookbook['subCategory'] = []; foreach ($arr as $v) { $v = trim($v); if ($v != '') { $cookbook['subCategory'][] = trim($v); } } $row++; $col = -1; unset($arr); continue; } // Find cuisineType row if (!isset($rowTemp['cuisineType'])) { if (preg_match('/^cuisine[\\s\\n]*type$/i', $val)) { $rowTemp['cuisineType'] = $row; continue; } else { $rowTemp['cuisineType'] = ''; } } // Fill cuisineType if ($rowTemp['cuisineType'] === $row) { // $arr = preg_split('/[,,]/', $val); $arr = $this->_spiltByComma($val); $cookbook['cuisineType'] = []; foreach ($arr as $v) { $v = trim($v); if ($v != '') { $cookbook['cuisineType'][] = trim($v); } } $row++; $col = -1; unset($arr); continue; } // Find yield row if (!isset($rowTemp['yield'])) { if (!preg_match('/^yield$/i', $val)) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: yield is required'); return ['contentError' => true]; } $rowTemp['yield'] = $row; continue; } // Fill yield if ($rowTemp['yield'] === $row) { if (!isset($cookbook['yield'])) { $cookbook['yield'] = []; $cookbook['yield']['Quantity'] = $val; } else { $cookbook['yield']['unit'] = $val; } continue; } // Find portionSize row if (!isset($rowTemp['portionSize'])) { if (!preg_match('/^portion[\\s\\n]*size$/i', $val)) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: portionSize is required'); return ['contentError' => true]; } $rowTemp['portionSize'] = $row; continue; } // Fill portionSize if ($rowTemp['portionSize'] === $row) { $cookbook['portionSize'] = $val; $row++; $col = -1; continue; } //Find ingredient quantity colume if (!isset($colTemp['idtQuantity'])) { if (!preg_match('/^quantity$/i', $val)) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient quantity is required'); return ['contentError' => true]; } $colTemp['idtQuantity'] = $col; continue; } //Find ingredient unit colume if (!isset($colTemp['idtUnit'])) { if (!preg_match('/^unit$/i', $val)) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient unit is required'); return ['contentError' => true]; } $colTemp['idtUnit'] = $col; continue; } //Find ingredient name colume if (!isset($colTemp['idtName'])) { if (!preg_match('/^ingredient[\\s\\n]*name$/i', $val)) { unlink($filePath); LogUtil::error(date('Y-m-d h:i:s') . ' ukkklp parse excel to cookbook error: ingredient name is required'); return ['contentError' => true]; } $colTemp['idtName'] = $col; continue; } //Fill ingredient if (!isset($cookbook['ingredient'])) { $cookbook['ingredient'] = []; } if (!$ingredientFinished) { // Fill ingredient quantity if ($col === $colTemp['idtQuantity']) { $cookbook['ingredient'][$row]['quantity'] = $val; } // Fill ingredient unit if ($col === $colTemp['idtUnit']) { $cookbook['ingredient'][$row]['unit'] = $val; } // Fill ingredient name if ($col === $colTemp['idtName']) { $cookbook['ingredient'][$row]['name'] = $val; } $ingredientFinished = preg_match('/^preparation[\\s\\n]*method$/i', $val); if ($ingredientFinished) { array_pop($cookbook['ingredient']); } continue; } // Find preparation method description colume if (!isset($colTemp['ptnDescription'])) { if (preg_match('/^description$/i', $val)) { $colTemp['ptnDescription'] = $col; } continue; } //Fill preparation method if (!isset($cookbook['preparationMethod'])) { $cookbook['preparationMethod'] = []; } // Fill preparation method description if ($col === $colTemp['ptnDescription']) { $cookbook['preparationMethod'][$row]['description'] = []; $arr = preg_split('/靈感來源\\s*or\\s*貼心小提示/i', $val); if (empty($arr) || sizeof($arr) !== 2) { $cookbook['preparationMethod'][$row]['description']['step'] = $val; $cookbook['preparationMethod'][$row]['description']['creativeExperience'] = ''; } else { $cookbook['preparationMethod'][$row]['description']['step'] = trim($arr[0]); $cookbook['preparationMethod'][$row]['description']['creativeExperience'] = trim($arr[1]); } unset($arr); } } } if (!isset($cookbook['ingredient']) || !isset($cookbook['preparationMethod'])) { unlink($filePath); return ['contentError' => true]; } $cookbook['ingredient'] = array_values($cookbook['ingredient']); $tmpInfo = $this->_findProductUrlAndSave($cookbook['ingredient']); $cookbook['ingredient'] = $tmpInfo['ingredients']; unset($tmpInfo); $cookbook['preparationMethod'] = array_values($cookbook['preparationMethod']); for ($i = 0; $i < sizeof($cookbook['ingredient']); $i++) { $cookbook['ingredient'][$i]['id'] = $this->_getRandomId(); } $cookbook['content'] = $cookbook['preparationMethod'][0]['description']['step']; $cookbook['creativeExperience'] = $cookbook['preparationMethod'][0]['description']['creativeExperience']; $cookbooks[] = $cookbook; unset($rowTemp); unset($colTemp); unset($cookbook); } unlink($filePath); if (empty($cookbooks)) { return []; } $results = Cookbook::saveImportedCookbooks($cookbooks, $this->getAccountId()); $cookbookBatch = new CookbookBatch(); $accessToken = Token::getToken(); $user = User::findOne(['_id' => $accessToken->userId]); $cookbookBatch->operator = $user->name; $cookbookBatch->cookbooks = $results; $cookbookBatch->hasImages = false; $cookbookBatch->accountId = $this->getAccountId(); $cookbookBatch->createdTime = new \MongoDate(); $cookbookBatch->insert(); return sizeof($results); }
/** * @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 view_addOrUpdateSpeicailSpuOn() { $useId = $_SESSION['userId']; $personName = getPersonNameById($useId); if (empty($personName)) { $status = "<font color=red>系统找不到登录人信息!</font><br/>"; echo $status; exit; } if (isset($_POST['submit']) && $_POST['submit'] != '') { $uploadfile = 'addOrUpdateSpeicailSpuExcelImport_' . date("Y") . date("m") . date("d") . date("H") . date('i') . date('s') . '_' . $personName . ".xls"; if (move_uploaded_file($_FILES['upfile']['tmp_name'], 'upload/' . $uploadfile)) { echo "<font color=BLUE>文件上传成功!</font><br>"; } else { echo "<font color=red> 文件上传失败!</font>"; exit; } $fileName = 'upload/' . $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); $highestRow = $currentSheet->getHighestRow(); //表格中的最大行数 echo "表格总行数为 {$highestRow} <br />"; if ($highestRow > 30000) { echo "表格不能超过30000行,请重新整理表格导入,谢谢!"; exit; } $status = ''; $flag = true; //标识检测结果 $tName = 'pc_special_property'; $select = 'id,propertyName'; $where = "WHERE isOn=1"; $pspList = OmAvailableModel::getTNameList($tName, $select, $where); $IPArr = array(); foreach ($pspList as $value) { $IPArr[$value['id']] = $value['propertyName']; } if (empty($IPArr)) { echo "启用的特殊属性记录为空,退出"; exit; } for ($i = 2; $i <= $highestRow; $i++) { //先检查导入表格的正确性 $spu = trim($currentSheet->getCell('A' . $i)->getValue()); //spu $spu = preg_replace("/(\\s|\\ \\;| | )/", "", $spu); $spu = strpos($spu, '_') === false ? str_pad($spu, 3, '0', STR_PAD_LEFT) : $spu; $specailPropertyName = trim($currentSheet->getCell('B' . $i)->getValue()); //$specailPropertyName = preg_replace("/(\s|\ \;| |\xc2\xa0)/","",$specailPropertyName); if (empty($spu)) { $status .= "第 {$i} 行,SPU为空 <br/>"; $flag = false; continue; } $tName = 'pc_auto_create_spu'; $where = "WHERE is_delete=0 AND isSingSpu=1 AND spu='{$spu}'"; if (!OmAvailableModel::getTNameCount($tName, $where)) { $status .= "第 {$i} 行,SPU不存在 <br/>"; $flag = false; continue; } $propertyId = intval(array_search($specailPropertyName, $IPArr)); if ($propertyId <= 0) { $status .= "第 {$i} 行,找不到 {$specailPropertyName} 这个特殊属性记录 <br/>"; $flag = false; continue; } } if (!$flag) { //验证不通过 echo "<font color=red>{$status}</font>"; exit; } else { $status = ''; for ($i = 2; $i <= $highestRow; $i++) { //先检查导入表格的正确性 $spu = trim($currentSheet->getCell('A' . $i)->getValue()); //spu $spu = preg_replace("/(\\s|\\ \\;| | )/", "", $spu); $spu = strpos($spu, '_') === false ? str_pad($spu, 3, '0', STR_PAD_LEFT) : $spu; $specailPropertyName = trim($currentSheet->getCell('B' . $i)->getValue()); //spu $propertyId = intval(array_search($specailPropertyName, $IPArr)); $tName = 'pc_special_property_spu'; $where = "WHERE spu='{$spu}' AND propertyId={$propertyId}"; if (OmAvailableModel::getTNameCount($tName, $where)) { $status .= "第 {$i} 行,{$spu} 已经存在 {$specailPropertyName} 这个特殊属性<br/>"; } else { $dataArr = array(); $dataArr['spu'] = $spu; $dataArr['propertyId'] = $propertyId; OmAvailableModel::addTNameRow2arr($tName, $dataArr); $status .= "第 {$i} 行,{$spu} 添加至 {$specailPropertyName} 成功 <br/>"; } } echo "<font color=green>{$status}</font>"; exit; } } }