/**
* 获取可以读取类的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;
}
}
}
