static function importCartshop($tmp_file, $file, &$out)
{
global $CONFIG, $ST;
$dir = 'shared/php/';
include "{$dir}PHPExcel.php";
include "{$dir}PHPExcel/IOFactory.php";
PHPExcel_Settings::setLocale('ru');
if (file_ext($file) == 'xlsx') {
$objReader = new PHPExcel_Reader_Excel2007();
} elseif (file_ext($file) == 'xls') {
$objReader = new PHPExcel_Reader_Excel5();
}
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($tmp_file);
/*$objPHPExcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPExcel->getActiveSheet();
$keys=array();
$keys_flg=array('category_id'=>'id',
'parent_id'=>'parentid',
'name'=>'name',
'description'=>'description',
'image_name'=>'img',
'sort_order'=>'main_sort',
);
$out=array();
foreach ($objWorksheet->getRowIterator() as $row) {
$data=array();
$field=array();
$field_ext=array();
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$fld_flg=false;//Строка поля
$no_user_itm='';
foreach ($cellIterator as $cell) {
$val=trim(u2w($cell->getValue()));
if(!$fld_flg && $val=='category_id'){
$fld_flg=true;
}
if($fld_flg){//Грузим поля
if(isset($keys_flg[$val])){
$keys[$cell->getColumn()]=$keys_flg[$val];
}
// print_r($keys);
continue;
}
if($keys && isset($keys[$cell->getColumn()])){
if(is_integer($keys[$cell->getColumn()])){
$field["{$keys[$cell->getColumn()]}"]=$val;
}else{
$field_ext["{$keys[$cell->getColumn()]}"]=$val;
}
}
}
if(!$keys){
continue;
}
if(!empty($field_ext['id'])){
if($field_ext['img']){
if($img=@file_get_contents('http://www.farmcosmetica.ru/image/'.$field_ext['img'])){
$img_name=preg_replace('|^data/|','',$field_ext['img']);
$img_name=str_replace('/','_',$img_name);
$img_name="storage/catalog/".$img_name;
if(!file_exists($img_name)){
file_put_contents($img_name,$img);
}
$field_ext['img']="/".$img_name;
}else{
$field_ext['img']="";
}
}
$rs=$ST->select("SELECT * FROM sc_shop_catalog WHERE id={$field_ext['id']}");
if($rs->next()){
$ST->update('sc_shop_catalog',$field_ext,"id={$field_ext['id']}");
}else{
$ST->insert('sc_shop_catalog',$field_ext);
}
$out[$field_ext['id']]=$field_ext;
}
}
*/
$objPHPExcel->setActiveSheetIndex(1);
$objWorksheet = $objPHPExcel->getActiveSheet();
$keys = array();
$keys_flg = array('product_id' => 'id', 'categories' => 'categories', 'name' => 'name', 'quantity' => 'in_stock', 'image_name' => 'img', 'additional image names' => 'img_add', 'price' => 'price', 'date_added' => 'insert_time', 'date_modified' => 'update_time', 'viewed' => 'views', 'description' => 'html');
$out = array();
foreach ($objWorksheet->getRowIterator() as $row) {
$data = array();
$field = array();
$field_ext = array();
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
$fld_flg = false;
//Строка поля
$no_user_itm = '';
// echo "\n";
foreach ($cellIterator as $cell) {
$val = trim(u2w($cell->getValue()));
if (!$fld_flg && $val == 'product_id') {
//$cell->getColumn()=='B' &&
$fld_flg = true;
}
if ($fld_flg) {
//Грузим поля
if (isset($keys_flg[$val])) {
$keys[$cell->getColumn()] = $keys_flg[$val];
}
// print_r($keys);
continue;
}
if ($keys && isset($keys[$cell->getColumn()])) {
if (is_integer($keys[$cell->getColumn()])) {
$field["{$keys[$cell->getColumn()]}"] = $val;
} else {
$field_ext["{$keys[$cell->getColumn()]}"] = $val;
}
}
}
if (!$keys) {
continue;
}
if (!empty($field_ext['id'])) {
if ($field_ext['img']) {
if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $field_ext['img']))) {
$img_name = preg_replace('|^data/|', '', $field_ext['img']);
$img_name = str_replace('/', '_', $img_name);
$img_name = "storage/catalog/goods/" . $img_name;
if (!file_exists($img_name)) {
file_put_contents($img_name, $img);
}
$field_ext['img'] = "/" . $img_name;
} else {
$field_ext['img'] = "";
}
}
if ($field_ext['img_add']) {
$images = explode(',', $field_ext['img_add']);
$field_ext['img_add'] = array();
foreach ($images as $i) {
if ($img = @file_get_contents('http://www.farmcosmetica.ru/image/' . iconv('cp1251', 'utf-8', $i))) {
$img_name = preg_replace('|^data/|', '', $i);
$img_name = str_replace('/', '_', $img_name);
$img_name = "storage/catalog/goods/" . $img_name;
if (!file_exists($img_name)) {
file_put_contents($img_name, $img);
}
$field_ext['img_add'][] = "/" . $img_name;
}
}
$field_ext['img_add'] = implode(',', $field_ext['img_add']);
}
$rs = $ST->select("SELECT * FROM sc_shop_item WHERE id={$field_ext['id']}");
$categories = $field_ext['categories'];
unset($field_ext['categories']);
if ($rs->next()) {
$ST->update('sc_shop_item', $field_ext, "id={$field_ext['id']}");
} else {
$ST->insert('sc_shop_item', $field_ext);
}
// if($categories){
// $categories=explode(',',$categories);
// $ST->delete('sc_shop_item2cat',"itemid={$field_ext['id']}");
// foreach ($categories as $c) {
// $ST->insert('sc_shop_item2cat',array('itemid'=>$field_ext['id'],'catid'=>$c));
// }
//
// }
// if($field_ext['manufacturer']){
// $rs=$ST->select("SELECT * FROM sc_manufacturer WHERE name='".SQL::slashes($field_ext['manufacturer'])."'");
// if($rs->next()){
// $man_id=$rs->getInt('id');
// }else{
// $man_id=$ST->insert("sc_manufacturer",array('name'=>$field_ext['manufacturer']));
// }
// $ST->update('sc_shop_item',array('manufacturer_id'=>$man_id),"id={$field_ext['id']}");
//
//
// }
// if($field_ext['Объем']){
// $rs=$ST->select("select * FROM sc_shop_prop_val WHERE prop_id=1 AND item_id={$field_ext['id']}");
// if($rs->next()){
// $ST->update('sc_shop_prop_val',array('value'=>$field_ext['Объем']),"id={$rs->get('id')}");
// }else{
// $ST->insert('sc_shop_prop_val',array('value'=>$field_ext['Объем'],'prop_id'=>1,"item_id"=>$field_ext['id']));
// }
// }
$out[$field_ext['id']] = $field_ext;
}
}
}
public function reader($filePath = null, $index = 2)
{
if ($filePath == null) {
$filePath = $_FILES['file']['tmp_name'];
}
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
return 'no Excel';
}
}
$PHPExcel = $PHPReader->load($filePath);
$rows = array();
$sheet = $PHPExcel->getSheet(0);
$allColumn = $sheet->getHighestColumn();
$allRow = $sheet->getHighestRow();
$allCell = $this->AT[$allColumn];
for ($row = $index; $row <= $allRow; $row++) {
$arr = array();
for ($cell = 0; $cell <= $allCell; $cell++) {
$val = $sheet->getCellByColumnAndRow($cell, $row)->getValue();
$arr[$this->A[$cell]] = $val;
}
$rows[] = $arr;
}
return $rows;
}
function updateData($filename, $encode = 'utf-8')
{
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filename)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filename)) {
echo 'no Excel';
return;
}
}
$PHPExcel = $PHPReader->load($filename);
$currentSheet = $PHPExcel->getSheet(0);
$allColumn = $currentSheet->getHighestColumn();
$allRow = $currentSheet->getHighestRow();
$excelData = ['name' => $currentSheet->getCellByColumnAndRow(A, 1)->getValue(), 'version' => $currentSheet->getCellByColumnAndRow(A, 2)->getValue()];
$keys = [];
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$keys[$currentColumn] = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, 5)->getValue();
}
for ($currentRow = 6; $currentRow <= $allRow; $currentRow++) {
$tmp = [];
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$tmpv = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
$tmp[$keys[$currentColumn]] = in_array($keys[$currentColumn], ['Id', 'Exp', 'Hp', 'Attack', 'Defense']) ? intval($tmpv) : floatval($tmpv);
}
if (!empty($tmp)) {
$excelData['list'][] = $tmp;
}
}
return $excelData;
}
public function index(){
//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
import("Org.Util.PHPExcel");
//要导入的xls文件，位于根目录下的Public文件夹
$filename="./Public/1.xls";
//创建PHPExcel对象，注意，不能少了\
$PHPExcel=new \PHPExcel();
//如果excel文件后缀名为.xls，导入这个类
import("Org.Util.PHPExcel.Reader.Excel5");
//如果excel文件后缀名为.xlsx，导入这下类
//import("Org.Util.PHPExcel.Reader.Excel2007");
//$PHPReader=new \PHPExcel_Reader_Excel2007();
$PHPReader=new \PHPExcel_Reader_Excel5();
//载入文件
$PHPExcel=$PHPReader->load($filename);
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
$currentSheet=$PHPExcel->getSheet(0);
//获取总列数
$allColumn=$currentSheet->getHighestColumn();
//获取总行数
$allRow=$currentSheet->getHighestRow();
//循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
for($currentRow=1;$currentRow<=$allRow;$currentRow++){
//从哪列开始，A表示第一列
for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
//数据坐标
$address=$currentColumn.$currentRow;
//读取到的数据，保存到数组$arr中
$arr[$currentRow][$currentColumn]=$currentSheet->getCell($address)->getValue();
}
}
dump($arr);
}
/**
* 导入代理商信息
* @method import
*/
public function import()
{
$file_info = R('Public/UploadExcel');
//上传excel并返回上传路径
if (!file_exists($file_info)) {
//上传失败则输出
$error_info['status'] = 0;
$error_info['info'] = $file_info;
die(json_encode($error_info));
}
import('Common.Tools.PHPExcel.PHPExcel');
$PHPExcel = new \PHPExcel();
$PHPReader = new \PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($file_info)) {
$PHPReader = new PHPExcel_Reader_Excel5();
}
if (!$PHPReader->canRead($file_info)) {
$error_info['status'] = 0;
$error_info['info'] = '无法读取文件';
die(json_encode($error_info));
}
$PHPExcel = $PHPReader->load($file_info);
$currentSheet = $PHPExcel->getSheet(0);
$rowTotal = $currentSheet->getHighestRow();
//总行数
$columnTotal = $currentSheet->getHighestColumn();
//总列数
$columnTotal = \PHPExcel_Cell::columnIndexFromString($columnTotal);
//字母列转换为数字列
$_list = array();
for ($i = 1; $i <= $rowTotal; $i++) {
for ($j = 0; $j <= $columnTotal; $j++) {
$val = $currentSheet->getCellByColumnAndRow($j, $i)->getValue();
if (!empty($val)) {
$_list[$i - 1][] = $val;
}
}
}
foreach ($_list as $_k => $_v) {
$list[$_k]['name'] = $_v[0];
$list[$_k]['mobile'] = $_v[1];
$list[$_k]['id_card'] = $_v[2];
$list[$_k]['wechat_number'] = $_v[3];
$list[$_k]['class'] = $_v[4];
$list[$_k]['authorize_code'] = $_v[5];
$list[$_k]['ctime'] = now();
$list[$_k]['mtime'] = now();
}
$result = D('Agent')->addAll($list);
if ($result) {
$error_info['status'] = 1;
$error_info['info'] = '导入成功';
} else {
$error_info['status'] = 0;
$error_info['info'] = '导入数据失败';
}
die(json_encode($error_info));
}
public function createReport()
{
ini_set('memory_limit', '-1');
$objReader = new PHPExcel_Reader_Excel5();
$filename = 'first_data.xls';
$objPHPExcel = $objReader->load(dirname(__FILE__) . '/../../files/' . $filename);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
echo "<pre>";
print_r($sheetData);
}
public static function readExcel($filePath)
{
self::init();
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
return null;
}
}
return $PHPReader->load($filePath);
}
/**
*
*函数名： exportToExcelWithHeader
*输入： 模板excel的路径 $filePath，例如 'C:\\123.xls'
*输入： 二维数组 要导出的内容 $dataList
* array(
* array(key1=>$value1,key2=>$value2,...),
* array(key1=>$value1,key2=>$value2,...),...
* )
*输入： 一维数组 生成excel的头部信息 $headerList
* array(数据库前台字段key1，数据库前台字段key2,...)
*输入： string $OutputFileName 输出文件名(建议输出文件名带时间戳)
*输入： $outputType 默认0输出到浏览器，输入其他保存到文件服务器并成功返回相对地址，失败返回NULL
*创建人： 石昌民
*创建时间： 2015-9-30
*/
public function exportToExcelWithHeader($filePath, $dataList, $headerList, $OutputFileName, $outputType = 0)
{
require './ThinkPHP/Library/Org/PHPExcel/PHPExcel.php';
//实例化Excel读取类
$PHPReader = new \PHPExcel_Reader_Excel2007();
$fileWebService = new FileWebController($this->userID);
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
return false;
}
}
//读取Excel
$PHPExcel = $PHPReader->load($filePath);
/**读取excel文件中的第一个工作表*/
$currentSheet = $PHPExcel->getSheet(0);
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
$allRow++;
//实例化Excel写入类
$PHPWriter = new \PHPExcel_Writer_Excel5($PHPExcel);
//遍历数据列表中的内容,从第allRow行开始
$i = $allRow;
foreach ($dataList as $key => $value) {
$position = 'A';
foreach ($headerList as $headerKey => $headerValue) {
$currentSheet->setCellValueExplicit("{$position}" . $i, $dataList[$key][$headerValue], \PHPExcel_Cell_DataType::TYPE_STRING);
//headerKey为数据库前台字段
$position++;
}
$i++;
}
ob_end_clean();
//清除缓冲区,避免乱码
//决定是导出到浏览器还是文件服务器
if ($outputType !== 0) {
$path = Constant::WEBSERVER_EXCEL_SAVEPATH . "{$OutputFileName}.xls";
$PHPWriter->save($path);
// dump($path);
// dump(file_exists($path));
$fileURL = $fileWebService->uploadFile($path, "{$OutputFileName}.xls");
if ($fileURL == false) {
$fileURL = null;
}
return $fileURL;
return false;
} else {
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename={$OutputFileName}.xls");
header('Cache-Control: max-age=0');
$PHPWriter->save('php://output');
}
}
public function loadExcel($file)
{
// $PHPExcelReader = new PHPExcel_Reader_Excel2007();
// //为了可以读取所有版本Excel文件(读2007的报错)
// if(!$PHPExcelReader->canRead($file))
// {
$PHPExcelReader = new PHPExcel_Reader_Excel5();
if (!$PHPExcelReader->canRead($file)) {
return;
}
// }
$this->excelReaderObj = $PHPExcelReader->load($file);
}
function xlsToCsv($file_xls_input, $file_name_csv_output)
{
$objReader = new PHPExcel_Reader_Excel5();
try {
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($file_xls_input);
return true;
} catch (PHPExcel_Reader_Exception $e) {
die('Error loading file: ' . $e->getMessage());
}
$writer = new PHPExcel_Writer_CSV($objPHPExcel);
$writer->save($file_name_csv_output);
}
/**
* 获取可以读取类的phpexcel对象
* @return PHPExcel_Reader_Excel2007
*/
private function _getReadFileObj()
{
self::getInstance2007();
if (!self::$_excelObj2007->canRead($this->_filePath)) {
self::getInstance5();
if (!self::$_execelObj5->canRead($this->_filePath)) {
return false;
} else {
$this->_instance = self::$_execelObj5;
}
} else {
$this->_instance = self::$_excelObj2007;
}
}
public function get_rows_from_excel($filepath, &$infos)
{
if (empty($filepath)) {
return $infos;
}
$php_reader = new PHPExcel_Reader_Excel2007();
if (!$php_reader->canRead($filepath)) {
$php_reader = new PHPExcel_Reader_Excel5();
if (!$php_reader->canRead($filepath)) {
return $infos;
}
}
$cur_excel = $php_reader->load($filepath);
$cur_sheet = $cur_excel->getSheet(0);
$all_column = $cur_sheet->getHighestColumn();
//取得一共有多少列
$all_row = $cur_sheet->getHighestRow();
//取得一共有多少行
$infos_have = array();
$infos_error = array();
$i = 0;
$j = 0;
for ($cur_row = 1; $cur_row <= $all_row; $cur_row++) {
$flagadd = FALSE;
$tmprow = array();
$tmpchar = '';
for ($cur_column = 'A'; $cur_column <= $all_column; $cur_column++) {
$address = $cur_column . $cur_row;
$curinfo = trim($cur_excel->getActiveSheet()->getCell($address)->getvalue());
//if($cur_column == 'A')
//{
if (!empty($curinfo)) {
$flagadd = TRUE;
}
//}
$tmprow[] = $curinfo;
$tmpchar .= $curinfo;
}
if ($flagadd) {
$infos['infos_have'][$i] = $tmprow;
$i++;
} elseif (!empty($tmpchar)) {
$infos['infos_error'][$j] = $tmprow;
$j++;
}
}
}
function excel($postdata)
{
require_once 'Excel_reader.php';
require_once 'Classes/PHPExcel.php';
$tmp = $postdata;
$save_path = "./";
//暂时上传的文件路径
$file_name = $save_path . date('Ymdhis') . ".xls";
//上传后的文件保存路径和名称
if (copy($tmp, $file_name)) {
}
$PHPExcel = new PHPExcel();
$PHPReader = new PHPExcel_Reader_Excel2007();
//为了可以读取所有版本Excel文件
if (!$PHPReader->canRead($file_name)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($file_name)) {
echo '未发现Excel文件！';
return;
}
}
//不需要读取整个Excel文件而获取所有工作表数组的函数，感觉这个函数很有用，找了半天才找到
$sheetNames = $PHPReader->listWorksheetNames($file_name);
//读取Excel文件
$PHPExcel = $PHPReader->load($file_name);
//获取工作表的数目
$sheetCount = $PHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; $i++) {
//选择第一个工作表
$currentSheet = $PHPExcel->getSheet($i);
//取得一共有多少列
$allColumn = $currentSheet->getHighestColumn();
//取得一共有多少行
$allRow = $currentSheet->getHighestRow();
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$address = $currentColumn . $currentRow;
$data[$i][$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
//echo ."\t";
}
}
//$data[$i]=$currentSheet;
}
unlink($file_name);
//删除上传的文件
return $data;
}
public function writeCells($celdas, $srcOrigen, $sheet)
{
// escribe las celdas especificas de un archivo de excel existente
if (is_string($srcOrigen)) {
$objReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($srcOrigen);
} else {
$objPHPExcel = $srcOrigen;
}
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
//objeto de PHPExcel, para escribir en el excel
$objPHPExcel->setActiveSheetIndex($sheet);
foreach ($celdas as $campo => $celda) {
$objPHPExcel->getActiveSheet()->setCellValue($campo, $celda);
}
return $objPHPExcel;
}
public function index()
{
// $xq_list = $this->manage_model->get_all_xiaoqu_list();
// foreach ($xq_list as $xq) {
// $name = str_replace("•","",$xq['name']);
// //echo $xq['id'] . ": " . $this->encode($name) . '<br>';
// $xq['jianpin'] = $this->encode($name);
// $this->manage_model->update_xiaoqu_jianpin($xq);
// }
// die('DONE');
require_once APPPATH . 'libraries/PHPExcel/PHPExcel.php';
$PHPExcel = new \PHPExcel();
$PHPReader = new \PHPExcel_Reader_Excel2007();
$file = 'E:/broker.xlsx';
//为了可以读取所有版本Excel文件
if (!$PHPReader->canRead($file)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($file)) {
echo '未发现Excel文件！';
return;
}
}
//不需要读取整个Excel文件而获取所有工作表数组的函数，感觉这个函数很有用，找了半天才找到
$sheetNames = $PHPReader->listWorksheetNames($file);
//读取Excel文件
$PHPExcel = $PHPReader->load($file);
//获取工作表的数目
$sheetCount = $PHPExcel->getSheetCount();
//选择第一个工作表
$currentSheet = $PHPExcel->getSheet(0);
//取得一共有多少列
$allColumn = $currentSheet->getHighestColumn();
//取得一共有多少行
$allRow = $currentSheet->getHighestRow();
//循环读取数据，默认编码是utf8，这里转换成gbk输出
$data = array();
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$name = trim($currentSheet->getCell("A" . $currentRow)->getValue());
$tel = trim($currentSheet->getCell("B" . $currentRow)->getValue());
$data[] = array('username' => $tel, 'passwd' => sha1('888888'), 'tel' => $tel, 'company_name' => "顺达地产", 'rel_name' => $name, 'region_id' => 6, 'admin_group' => 2);
}
$this->manage_model->add_broker_batch($data);
}
public function index()
{
set_time_limit(0);
date_default_timezone_set('Europe/Paris');
include_once ROOT_PATH . 'core/PHPExcel/PHPExcel/IOFactory.php';
$inputFileName = UPLOADS_PATH . 'example1.xls';
echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using PHPExcel_Reader_Excel5<br />';
$objReader = new PHPExcel_Reader_Excel5();
// $objReader = new PHPExcel_Reader_Excel2007();
// $objReader = new PHPExcel_Reader_Excel2003XML();
// $objReader = new PHPExcel_Reader_OOCalc();
// $objReader = new PHPExcel_Reader_SYLK();
// $objReader = new PHPExcel_Reader_Gnumeric();
// $objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load($inputFileName);
echo '<hr />';
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$vars = array('sheet_data' => $sheetData);
$this->render('excel', $vars);
}
function format_excel2array($filePath = '', $sheet = 0)
{
if (empty($filePath) or !file_exists($filePath)) {
die('file not exists');
}
$PHPReader = new PHPExcel_Reader_Excel2007();
//建立reader对象
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
echo 'no Excel';
return;
}
}
$PHPExcel = $PHPReader->load($filePath);
//建立excel对象
$currentSheet = $PHPExcel->getSheet($sheet);
//**读取excel文件中的指定工作表*/
$allColumn = $currentSheet->getHighestColumn();
//**取得最大的列号*/
$allRow = $currentSheet->getHighestRow();
//**取得一共有多少行*/
$data = array();
for ($rowIndex = 1; $rowIndex <= $allRow; $rowIndex++) {
//循环读取每个单元格的内容。注意行从1开始，列从A开始
for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) {
$addr = $colIndex . $rowIndex;
$cell = $currentSheet->getCell($addr)->getValue();
if ($cell instanceof PHPExcel_RichText) {
//富文本转换字符串
$cell = $cell->__toString();
}
$data[$rowIndex][$colIndex] = $cell;
}
}
return $data;
}
public function import($filePath)
{
$this->__construct();
$PHPExcel = new PHPExcel();
/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
echo 'no Excel';
return;
}
}
$PHPExcel = $PHPReader->load($filePath);
$currentSheet = $PHPExcel->getSheet(0);
//读取excel文件中的第一个工作表
$allColumn = $currentSheet->getHighestColumn();
//取得最大的列号
$allRow = $currentSheet->getHighestRow();
//取得一共有多少行
$erp_orders_id = array();
//声明数组
/**从第二行开始输出，因为excel表中第一行为列名*/
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
/**从第A列开始输出*/
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$val = $currentSheet->getCellByColumnAndRow(ord($currentColumn) - 65, $currentRow)->getValue();
/**ord()将字符转为十进制数*/
//if($val!=''){
$erp_orders_id[] = $val;
//}
/**如果输出汉字有乱码，则需将输出内容用iconv函数进行编码转换，如下将gb2312编码转为utf-8编码输出*/
//echo iconv('utf-8','gb2312', $val)."\t";
}
}
return $erp_orders_id;
}
/**
* 导入供应商信息的函数
* 基于 PHPExcel.php
* @return $result $result > 0 成功，否则失败
*/
public function act_importSave()
{
$uploadfile = date("Y") . date("m") . date("d") . rand(1, 3009) . ".xls";
if (!move_uploaded_file($_FILES['upfile']['tmp_name'], WEB_PATH . 'upload_datas/' . $uploadfile)) {
return false;
}
$fileName = WEB_PATH . 'upload_datas/' . $uploadfile;
$filePath = $fileName;
$PHPExcel = new PHPExcel();
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
echo 'no Excel';
return;
}
}
$PHPExcel = $PHPReader->load($filePath);
$currentSheet = $PHPExcel->getSheet(0);
/**取得一共有多少列*/
$c = 2;
while (true) {
$aa = 'A' . $c;
$bb = 'B' . $c;
$cc = 'C' . $c;
$dd = 'D' . $c;
$ee = 'E' . $c;
$ff = 'F' . $c;
$gg = 'G' . $c;
$hh = 'H' . $c;
$ii = 'I' . $c;
$jj = 'J' . $c;
$kk = 'K' . $c;
$ll = 'L' . $c;
$mm = 'M' . $c;
$nn = 'N' . $c;
$oo = 'O' . $c;
$pp = 'P' . $c;
$qq = 'Q' . $c;
$rr = 'R' . $c;
$ss = 'S' . $c;
$tt = 'T' . $c;
$uu = 'U' . $c;
$vv = 'V' . $c;
$ww = 'W' . $c;
$zz = 'Z' . $c;
$xx = 'X' . $c;
$c++;
$company_name = str_rep(trim($currentSheet->getCell($aa)->getValue()));
$username = str_rep(trim($currentSheet->getCell($bb)->getValue()));
$category_name = str_rep(trim($currentSheet->getCell($cc)->getValue()));
$tel = str_rep(trim($currentSheet->getCell($dd)->getValue()));
$phone = str_rep(trim($currentSheet->getCell($ee)->getValue()));
$fax = str_rep(trim($currentSheet->getCell($ff)->getValue()));
$QQ = str_rep(trim($currentSheet->getCell($gg)->getValue()));
$e_mail = str_rep(trim($currentSheet->getCell($hh)->getValue()));
$AliIM = str_rep(trim($currentSheet->getCell($ii)->getValue()));
$shoplink = str_rep(trim($currentSheet->getCell($jj)->getValue()));
$city = str_rep(trim($currentSheet->getCell($kk)->getValue()));
$address = str_rep(trim($currentSheet->getCell($ll)->getValue()));
$status = str_rep(trim($currentSheet->getCell($mm)->getValue()));
$email_status = str_rep(trim($currentSheet->getCell($nn)->getValue()));
$sms_status = str_rep(trim($currentSheet->getCell($oo)->getValue()));
$purchaser = str_rep(trim($currentSheet->getCell($pp)->getValue()));
$company = str_rep(trim($currentSheet->getCell($qq)->getValue()));
$note = str_rep(trim($currentSheet->getCell($rr)->getValue()));
$partnerStr = "单位名称:{$company_name}, 姓名:{$username}, 电话:{$tel} ... ";
//var_dump(self::check_input($company_name));
//exit;
if ($company_name != '' && !self::check_input($company_name)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '单位名称':'{$company_name}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($username != '' && !self::check_input($username)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '姓名':'{$username}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($category_name != '' && !self::check_input($category_name)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '单位类型':'{$category_name}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($tel != '' && !self::isTel($tel)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '电话号码':'{$tel}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($phone != '' && !self::isMobile($phone)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '手机号码':'{$phone}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($fax != '' && !self::isPhone($fax)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '传真号码':'{$fax}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($QQ != '' && !self::isQQ($QQ)) {
$errInfo = " -[<font color='#FF0000'>导入失败, 'QQ号码':'{$QQ}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($e_mail != '' && !self::isEmail($e_mail)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '邮件':'{$e_mail}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($AliIM != '' && !self::isNormalCharacter($AliIM)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '阿里旺旺':'{$AliIM}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($city != '' && !self::check_input($city)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '所属城市':'{$city}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($address != '' && !self::check_input($address)) {
$errInfo = " -[<font color='#FF0000'>导入失败, '地址':'{$address}' 填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
$status = $status == '黑名单' ? 0 : $status == '正常' ? 1 : 2;
if ($status < 0 || $status > 2) {
$errInfo = " -[<font color='#FF0000'>导入失败, '状态'填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
$email_status = $email_status == '是' ? 1 : 0;
if ($email_status != 0 && $email_status != 1) {
$errInfo = " -[<font color='#FF0000'>导入失败, '支持邮件'填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
$sms_status = $sms_status == '是' ? 1 : 0;
if ($sms_status != 0 && $sms_status != 1) {
$errInfo = " -[<font color='#FF0000'>导入失败, '支持短信'填写有误！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
if ($company_name == '') {
break;
}
//$c++;
//To get type_id
$where = " AND `category_name` = '{$category_name}' ";
$field = " `id` ";
$resultType = PartnerTypeAct::act_getPartnerTypeList($where, $field);
$type_id = $resultType[0]['id'];
if (!isset($type_id)) {
$errInfo = " -[<font color='#FF0000'>导入失败, 单位类型 '{$category_name}' 不存在！" . $partnerStr . "</font>]";
continue;
}
//To get purchaser_id
$where = " AND `global_user_name` = '{$purchaser}' ";
$field = " `global_user_id` ";
$resultPurchase = self::act_getPurchaserList($where, $field);
$purchaser_id = $resultPurchase[0]['global_user_id'];
if (!isset($purchaser_id)) {
$errInfo = " -[<font color='#FF0000'>导入失败, 采购员 '{$purchaser}' 不存在！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
//To get company_id
$where = " AND `company` = '{$company}' ";
$field = " `id` ";
$resultCompany = self::act_getPartnerCompany($where, $field);
//print_r($resultCompany);
$company_id = $resultCompany[0]['id'];
if (!isset($company_id)) {
$errInfo = " -[<font color='#FF0000'>导入失败, 关联公司 '{$company}' 不存在！" . $partnerStr . "</font>]";
echo $errInfo . '<br>';
continue;
}
$data = array('company_name' => $company_name, 'username' => $username, 'type_id' => $type_id, 'tel' => $tel, 'phone' => $phone, 'fax' => $fax, 'QQ' => $QQ, 'AliIM' => $AliIM, 'e_mail' => $e_mail, 'shoplink' => $shoplink, 'city' => $city, 'address' => $address, 'note' => $note, 'status' => $status, 'sms_status' => $sms_status, 'email_status' => $email_status, 'purchaseuser_id' => $purchaser_id, 'company_id' => $company_id);
//print_r($data);
$result = PartnerModel::insertRow($data);
if ($result) {
$errInfo = " -[<font color='#33CC33'>导入成功, " . $partnerStr . "</font>]";
} else {
$errMsg = PartnerModel::$errMsg;
$errInfo = " -[<font color='#FF0000'>导入失败, " . $partnerStr . $errMsg . "</font>]";
}
echo $errInfo . '<br>';
}
}
public function import()
{
if (!isset($_FILES['customExcelData'])) {
uploadFileErrorRespond('请选择数据！');
}
if (file_exists($_FILES['customExcelData']['tmp_name'])) {
$Custom = M('Custom');
$CustomGoods = D('CustomGoods');
$SendGoodsSort = D('SendGoodsSort');
$Employee = D('Employee');
$Province = D('Province');
$CustomUnit = D('CustomUnit');
import("ORG.Excel.PHPExcel");
import("ORG.Excel.PHPExcel.IOFactory", THINK_PATH . '/Lib/', '.php');
$fileName = $_FILES['customExcelData']['tmp_name'];
$PHPExcel = new PHPExcel();
/** 默认用excel2007读取excel，若格式不对，则用之前的版本进行读取 */
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($fileName)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($fileName)) {
uploadFileErrorRespond('Excel格式不支持，请转换后重试！');
//uploadFileRespond('Excel format not support ,please try again.');
return;
}
}
$EmployeeInsertId = get_employeeid($_SESSION[C('USER_AUTH_KEY')]);
ob_clean();
$PHPExcel = $PHPReader->load($fileName);
/* 读取excel文件中的第一个工作表 */
$currentSheet = $PHPExcel->getSheet(0);
/* 取得最大的列号 */
$allColumn = $currentSheet->getHighestColumn();
/* 取得一共有多少行 */
$allRow = $currentSheet->getHighestRow();
if (empty($allRow) || $allRow < 2) {
uploadFileErrorRespond('请检查数据是否在excel的第一个sheet中。');
}
$customRowCount = 0;
$customUnitRowCount = 0;
$customGoodsRowCount = 0;
/* 从第二行开始输出，因为excel表中第一行为列名 */
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
/* 从第A列开始输出 */
$managerName = trim($currentSheet->getCell('A' . $currentRow)->getValue());
$address = trim($currentSheet->getCell('B' . $currentRow)->getValue());
$customName = trim($currentSheet->getCell('C' . $currentRow)->getValue());
$customUnitName = trim($currentSheet->getCell('D' . $currentRow)->getValue());
$className = trim($currentSheet->getCell('E' . $currentRow)->getValue());
$tel = trim($currentSheet->getCell('F' . $currentRow)->getValue());
$sendSortName = trim($currentSheet->getCell('G' . $currentRow)->getValue());
$provinceName = trim($currentSheet->getCell('H' . $currentRow)->getValue());
$payName = trim($currentSheet->getCell('I' . $currentRow)->getValue());
$zipCode = trim($currentSheet->getCell('J' . $currentRow)->getValue());
if (empty($managerName) && empty($customName)) {
continue;
}
$map['name'] = $provinceName;
$provinceId = $Province->where($map)->getField('id');
unset($map);
$map['name'] = $sendSortName;
$sendSortId = $SendGoodsSort->where($map)->getField('id');
unset($map);
$map['employeeName'] = $managerName;
$employeeId = $Employee->where($map)->getField('id');
if (empty($employeeId)) {
$employeeId = $EmployeeInsertId;
}
unset($map);
$map['name'] = $customUnitName;
$customUnitId = $CustomUnit->where($map)->getField('id');
if (empty($customUnitId)) {
$data['name'] = $customUnitName;
$data['telphone1'] = $tel;
$data['address'] = $address;
$data['postCode'] = $zipCode;
$data['employeeID'] = $employeeId;
$customUnitId = $CustomUnit->data($data)->add();
$customUnitRowCount++;
/* 客户单位信息添加一条 */
}
unset($map);
unset($data);
/* 唯一定位一个客户 */
$map['employeeID'] = $employeeId;
$map['name'] = $customName;
$map['address'] = $address;
$map['provinceID'] = $provinceId;
$customId = $Custom->where($map)->getField('id');
//echo $Custom->getLastSql();
//exit();
if (empty($customId)) {
$data['insertPerson'] = $EmployeeInsertId;
$data['employeeID'] = $employeeId;
$data['name'] = $customName;
$data['provinceID'] = $provinceId;
$data['address'] = $address;
$data['telphone1'] = $tel;
$data['UnitID'] = $customUnitId;
$data['insertTime'] = time();
$data['postCode'] = $zipCode;
$customId = $Custom->data($data)->add();
$customRowCount++;
/* 客户信息插入了一条 */
}
unset($map);
unset($data);
/* 唯一定位一个发货人 */
$map['customID'] = $customId;
$map['schoolID'] = $customUnitId;
$map['className'] = $className;
$map['tel'] = $tel;
$customGoodsId = $CustomGoods->where($map)->getField('id');
if (empty($customGoodsId)) {
$data['customID'] = $customId;
$data['recName'] = $customName;
$data['phone'] = $tel;
$data['address'] = $address;
$data['cityName'] = $address;
$data['payName'] = $payName;
$data['sendSortID'] = $sendSortId;
$data['provinceID'] = $provinceId;
$data['className'] = $className;
$data['schoolID'] = $customUnitId;
$data['zipCode'] = $zipCode;
$customGoodsId = $CustomGoods->data($data)->add();
$customGoodsRowCount++;
}
unset($map);
unset($data);
}
$customRowLast = $allRow - $customRowCount;
$customUnitRowLast = $allRow - $customUnitRowCount;
$customGoodsRowLast = $allRow - $customGoodsRowCount;
$msg = '导入成功!<br/>共有' . $allRow . '条数据.<br/>客户信息添加' . $customRowCount . '条;跳过' . $customRowLast . '条.<br/>单户单位信息添加' . $customUnitRowCount . '条;跳过' . $customUnitRowLast . '条.<br/>客户收货信息添加' . $customGoodsRowCount . '条;跳过' . $customGoodsRowLast . '条.';
uploadFileSuccessRespond($msg, 'Custom');
return;
}
uploadFileErrorRespond('出现错误，请重试!');
}
/**
* Read OfficeArtRGFOPTE table of property-value pairs
*
* @param string $data Binary data
* @param int $n Number of properties
*/
private function _readOfficeArtRGFOPTE($data, $n)
{
$splicedComplexData = substr($data, 6 * $n);
// loop through property-value pairs
for ($i = 0; $i < $n; ++$i) {
// read 6 bytes at a time
$fopte = substr($data, 6 * $i, 6);
// offset: 0; size: 2; opid
$opid = PHPExcel_Reader_Excel5::_GetInt2d($fopte, 0);
// bit: 0-13; mask: 0x3FFF; opid.opid
$opidOpid = (0x3fff & $opid) >> 0;
// bit: 14; mask 0x4000; 1 = value in op field is BLIP identifier
$opidFBid = (0x4000 & $opid) >> 14;
// bit: 15; mask 0x8000; 1 = this is a complex property, op field specifies size of complex data
$opidFComplex = (0x8000 & $opid) >> 15;
// offset: 2; size: 4; the value for this property
$op = PHPExcel_Reader_Excel5::_GetInt4d($fopte, 2);
if ($opidFComplex) {
$complexData = substr($splicedComplexData, 0, $op);
$splicedComplexData = substr($splicedComplexData, $op);
// we store string value with complex data
$value = $complexData;
} else {
// we store integer value
$value = $op;
}
$this->_object->setOPT($opidOpid, $value);
}
}
}
/**
* The possible scopes for $plugin_param are: 'table', 'database', and 'server'
*/
if (isset($plugin_list)) {
$plugin_list['xls'] = array('text' => 'strImportXLS', 'extension' => 'xls', 'options' => array(array('type' => 'bool', 'name' => 'col_names', 'text' => 'strImportColNames')), 'options_text' => 'strOptions');
/* We do not define function when plugin is just queried for information above */
return;
}
ini_set('memory_limit', '256M');
set_time_limit(120);
/* Append the PHPExcel directory to the include path variable */
set_include_path(get_include_path() . PATH_SEPARATOR . getcwd() . '/libraries/PHPExcel/');
require_once './libraries/PHPExcel/PHPExcel.php';
require_once './libraries/PHPExcel/PHPExcel/Reader/Excel5.php';
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objReader->setLoadAllSheets();
$objPHPExcel = $objReader->load($import_file);
$sheet_names = $objPHPExcel->getSheetNames();
$num_sheets = count($sheet_names);
$tables = array();
$tempRow = array();
$rows = array();
$col_names = array();
for ($s = 0; $s < $num_sheets; ++$s) {
$current_sheet = $objPHPExcel->getSheet($s);
$num_rows = $current_sheet->getHighestRow();
$num_cols = PMA_getColumnNumberFromName($current_sheet->getHighestColumn());
if ($num_rows != 1 && $num_cols != 1) {
for ($r = 1; $r <= $num_rows; ++$r) {
/**
* 生成订单附件
* @param $order_data 要写入订单模板的数据数组
* @param $attachment_path = '' 附件生成路径
* @return string|bool
*/
public static function create_order_attachment($order_data, $attachment_path = '')
{
if (!empty($order_data) && is_array($order_data)) {
// 引入PHPExcel扩展
Yii::createComponent('application.extensions.excel.PHPExcel');
// 租车订单Excel附件 - 模板目录
$templet_path = ASSETS_FILE . 'templet/tichedan.xls';
// 租车订单Excel附件 - 正式目录
$attachment_path = $attachment_path == '' ? SECRET_FILE_PATH . 'Carrental/' . date('Y-m-d', time()) . '/' : $attachment_path;
try {
// 正式目录不存在则创建
if (!file_exists($attachment_path)) {
if (!DirectoryFile::dirCreate($attachment_path)) {
throw new Exception('创建目录失败！');
}
}
// 如模板文件存在则复制到正式目录下
if (file_exists($templet_path)) {
// 生成附件名称
$attachment_name = 'order_' . time() . '_' . rand(100, 10000) . '.xls';
// 复制租车订单模板至正式目录下
$status = @copy($templet_path, $attachment_path . $attachment_name);
if (!$status) {
throw new Exception('模板文件移动失败！');
}
} else {
throw new Exception('订单模板文件不存在！');
}
// 实例化Excel读取类
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($attachment_path . $attachment_name)) {
throw new Exception('不是一个Excel文件！');
}
}
// 读取Excel工作表
$PHPExcel = $PHPReader->load($attachment_path . $attachment_name);
$currentSheet = $PHPExcel->getSheet(0);
// 重写Excel工作表 将单项信息写入Excel单元格
$currentSheet->setCellValue('B3', !empty($order_data[0]) ? $order_data[0] : '');
// 预定号[reservation number]
$currentSheet->setCellValue('D3', $order_data[1]);
// 确认号码[confirmation number]
$currentSheet->setCellValue('B5', $order_data[2]);
// 租车人姓名[Name]
$currentSheet->setCellValue('D5', $order_data[3]);
// 航班号码[Flight number]
$currentSheet->setCellValue('B19', $order_data[4]);
// 车辆代码[Car Code]
$currentSheet->setCellValue('D19', $order_data[5]);
// 车门数[Doors]
$currentSheet->setCellValue('D22', $order_data[6]);
// 空调[Air Con]
$currentSheet->setCellValue('D20', $order_data[7]);
// 座位数[Car Seats]
$currentSheet->setCellValue('B9', $order_data[8]);
// 取车日期
$currentSheet->setCellValue('B10', $order_data[9]);
// 取车时间
$currentSheet->setCellValue('B11', $order_data[10]);
// 取车门店[Location]
$currentSheet->setCellValue('B12', $order_data[11]);
// 取车地址[Address]
$currentSheet->setCellValue('B13', $order_data[12]);
// 取车地电话[Tel]
$currentSheet->setCellValue('D9', $order_data[13]);
// 还车日期
$currentSheet->setCellValue('D10', $order_data[14]);
// 还车时间
$currentSheet->setCellValue('D11', $order_data[15]);
// 还车门店[Location]
$currentSheet->setCellValue('D12', $order_data[16]);
// 还车地址[Address]
$currentSheet->setCellValue('D13', $order_data[17]);
// 还车地电话[Tel]
$currentSheet->setCellValue('B23', $order_data[18]);
// 确认号码[confirmation number]
$currentSheet->setCellValue('B18', $order_data[19]);
// 车行名称Supplier
$currentSheet->setCellValue('B20', $order_data[20]);
// 车辆类型 Car Type
$currentSheet->setCellValue('D21', $order_data[21]);
//变速器类型transmission
$currentSheet->setCellValueExplicit('B22', $order_data[22], PHPExcel_Cell_DataType::TYPE_STRING);
//车行的账户号transmission
$currentSheet->setCellValue('A26', $order_data[23]);
//里程限制
$currentSheet->setCellValue('A27', $order_data[24]);
//车辆保险
$currentSheet->setCellValue('A28', $order_data[25]);
//车辆包含的税费
$currentSheet->setCellValue('A30', $order_data[26]);
//预定的额外服务
// 执行Excel写入操作
$PHPWriter = new PHPExcel_Writer_Excel5($PHPExcel);
// 生成订单附件
// $PHPWriter->save(iconv('utf - 8', 'gbk', $attachment_path . $attachment_name));
$PHPWriter->save($attachment_path . $attachment_name);
} catch (Exception $e) {
echo $e->getMessage();
}
echo '<script language="javascript" type="text/javascript">window.top.window.stopUpload(1);</script >';
return $attachment_path . $attachment_name;
} else {
exit('参数不正确！');
}
}
function importMhs($filename = '')
{
require_once 'includes/xls_report/PHPExcel.php';
$xls_file = 'uploads/form/' . $filename;
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($xls_file);
#return $filename;
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, false);
$totalrow = count($sheetData);
#return $sheetData;
# get active record
$start_record = 5;
$start_col = 0;
$data = array();
$index = 0;
$kd_prodi = $sheetData[0][0];
$kd_fak = $this->model->getKodeFakultasByKodeProdi($kd_prodi);
while ($start_record < $totalrow) {
$a = $this->security->xss_clean($sheetData[$start_record][$start_col]);
$start_col++;
$b = $this->security->xss_clean($sheetData[$start_record][$start_col]);
$start_col++;
$c = $this->security->xss_clean($sheetData[$start_record][$start_col]);
$start_col++;
$d = $this->security->xss_clean($sheetData[$start_record][$start_col]);
$start_col++;
$e = $this->security->xss_clean($sheetData[$start_record][$start_col]);
$start_col++;
$data[$index++] = array($a, $b, $c, $d, $e);
$start_record++;
$start_col = 0;
# insert to table mahasiswa
$data = array('npm' => $a, 'nm_mhs' => $b, 'kd_fak' => $kd_fak, 'kd_prodi' => $kd_prodi, 'alamat_mhs' => $c, 'email_mhs' => $d, 'phone_mhs' => $e, 'date_created' => date('Y-m-d H:i:s'), 'createdby' => $this->session->userdata('user_id'));
$sv_mhs = $this->model->insertData('md_mhs', $data);
if ($sv_mhs) {
$dataUser = array('id_user' => $a, 'user_password' => sha1($a), 'user_name' => $b, 'user_email' => $d, 'user_phone' => $e, 'user_level' => 1, 'date_created' => date('Y-m-d H:i:s'), 'createdby' => $this->session->userdata('user_id'));
$this->model->insertData('users', $dataUser);
$this->outh->user_log($this->session->userdata('user_id'), 'Import Mahasiswa -> ' . $data['nm_mhs'] . ' (' . $data['npm'] . ')');
}
}
#print_r($data); echo $kd_prodi.'-'. $kd_fak;
$this->session->set_flashdata('success', ' Import Data Mahasiswa berhasil. <b>' . $index . '</b> data mahasiswa baru berhasil disimpan!');
redirect('master/mahasiswa');
}
protected function setPHPExcelReader($file)
{
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
return $objReader->load($file);
}
public function excelImport()
{
$m_contacts = M('contacts');
if ($_POST['submit']) {
if (isset($_FILES['excel']['size']) && $_FILES['excel']['size'] != null) {
import('@.ORG.UploadFile');
$upload = new UploadFile();
$upload->maxSize = 20000000;
$upload->allowExts = array('xls');
$dirname = UPLOAD_PATH . date('Ym', time()) . '/' . date('d', time()) . '/';
if (!is_dir($dirname) && !mkdir($dirname, 0777, true)) {
alert('error', L('ATTACHMENTS TO UPLOAD DIRECTORY CANNOT WRITE'), U('contacts/index'));
}
$upload->savePath = $dirname;
if (!$upload->upload()) {
alert('error', $upload->getErrorMsg(), U('contacts/index'));
} else {
$info = $upload->getUploadFileInfo();
}
}
if (is_array($info[0]) && !empty($info[0])) {
$savePath = $dirname . $info[0]['savename'];
} else {
alert('error', L('UPLOAD FAILED'), U('contacts/index'));
}
import("ORG.PHPExcel.PHPExcel");
$PHPExcel = new PHPExcel();
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($savePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
}
$PHPExcel = $PHPReader->load($savePath);
$currentSheet = $PHPExcel->getSheet(0);
$allRow = $currentSheet->getHighestRow();
for ($currentRow = 3; $currentRow <= $allRow; $currentRow++) {
$data = array();
$data['creator_role_id'] = session('role_id');
$data['create_time'] = time();
$data['update_time'] = time();
$data['owner_role_id'] = trim($_POST['owner_role_id']);
$name = (string) $currentSheet->getCell('A' . $currentRow)->getValue();
$name != '' && $name != null ? $data['name'] = $name : '';
/* $customer_name = (String)$currentSheet->getCell('C'.$currentRow)->getValue();
$customer_id = M('Customer')->where('name = "%s"' ,trim($customer_name))->getField('customer_id');
if($customer_name){
if($customer_id > 0){
$r_c_c['customer_id'] = $customer_id;
$data['customer_id'] = $customer_id;
} else {
alert('error', '导入至第' . $currentRow . '行出错, 原因："'.$customer_name.'"客户不存在', U('contacts/index'));
break;
}
} */
$saltname = (string) $currentSheet->getCell('B' . $currentRow)->getValue();
$saltname != '' && $saltname != null ? $data['saltname'] = $saltname : '';
$department = (string) $currentSheet->getCell('C' . $currentRow)->getValue();
$department != '' && $department != null ? $data['department'] = $department : '';
$post = (string) $currentSheet->getCell('D' . $currentRow)->getValue();
$post != '' && $post != null ? $data['post'] = $post : '';
$qq = (string) $currentSheet->getCell('E' . $currentRow)->getValue();
$qq != '' && $qq != null ? $data['qq'] = $qq : '';
$telephone = (string) $currentSheet->getCell('F' . $currentRow)->getValue();
$telephone != '' && $telephone != null ? $data['telephone'] = $telephone : '';
$email = (string) $currentSheet->getCell('G' . $currentRow)->getValue();
$email != '' && $email != null ? $data['email'] = $email : '';
$address = (string) $currentSheet->getCell('H' . $currentRow)->getValue();
$address != '' && $address != null ? $data['address'] = $address : '';
$zip_code = (string) $currentSheet->getCell('I' . $currentRow)->getValue();
$zip_code != '' && $zip_code != null ? $data['zip_code'] = $zip_code : '';
$description = (string) $currentSheet->getCell('J' . $currentRow)->getValue();
$description != '' && $description != null ? $data['description'] = $description : '';
if (!($contacts_id = $m_contacts->add($data))) {
if ($this->_post('error_handing', 'intval', 0) == 0) {
alert('error', L('ERROR INTRODUCED INTO THE LINE', array($currentRow, $m_contacts->getError())), U('contacts/index'));
} else {
$error_message .= L('LINE ERROR', array($currentRow, $m_contacts->getError()));
$m_contacts->clearError();
}
break;
}
}
alert('success', L('IMPORT SUCCESS', array($error_message)), U('contacts/index'));
} else {
$this->display();
}
}
public function doWebBatchAddInfo()
{
global $_W, $_GPC;
load()->func('tpl');
if (checksubmit()) {
$excelfile = $_FILES['exceldata'];
if (empty($excelfile)) {
message('请先上传文件');
}
$str = explode(".", $excelfile['name']);
$filetype = strtolower($str[1]);
if (!($filetype == 'xlsx' || $filetype == 'xls')) {
message('上传文件类型不正确！');
}
$path = '../attachment/excel/water/';
if (!file_exists($path)) {
mkdir($path);
}
$time = time();
$filePath = $path . $time . '.' . $filetype;
move_uploaded_file($excelfile['tmp_name'], $filePath);
//首先导入PHPExcel
require_once 'PHPExcel.php';
//$filePath = "../addons/water_query2/test.xlsx";
//建立reader对象
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
echo 'no Excel';
return;
}
}
//建立excel对象，此时你即可以通过excel对象读取文件，也可以通过它写入文件
$PHPExcel = $PHPReader->load($filePath);
/**读取excel文件中的第一个工作表*/
$currentSheet = $PHPExcel->getSheet(0);
/**取得最大的列号*/
$allColumn = $currentSheet->getHighestColumn();
echo $allColumn . '---';
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
echo $allRow . '----';
//循环读取每个单元格的内容。注意行从1开始，列从A开始
$data = array('uniacid' => $_W['uniacid']);
for ($rowIndex = 1; $rowIndex <= $allRow; $rowIndex++) {
for ($colIndex = 'A'; $colIndex <= $allColumn; $colIndex++) {
$addr = $colIndex . $rowIndex;
$cell = $currentSheet->getCell($addr)->getValue();
if ($cell instanceof PHPExcel_RichText) {
//富文本转换字符串
$cell = $cell->__toString();
}
//echo '第'.$rowIndex.'行，第'.$colIndex.'列：'.$cell.'</br>';
if ($colIndex == 'A') {
$data['keyword'] = $cell;
} else {
$data['ordercode'] = $cell;
}
}
pdo_insert('water_query2_info', $data);
}
message('导入成功！', $this->createWebUrl('Info'), 'success');
} else {
include $this->template('batchaddinfo');
}
}
public function index()
{
//提交的配置
$config = $_POST['config'];
$filePath = AMANGO_FILE_ROOT . $config['excel_path'];
$currentid = $config['excel_readxls'] >= 1 ? $config['excel_readxls'] - 1 : 0;
$startid = $config['excel_currentrow'] >= 2 ? $config['excel_currentrow'] : 2;
if ($filePath == AMANGO_FILE_ROOT) {
$this->error('请选择文件');
}
$xlsparam = parse_config($_POST['config']['excel_param']);
//导入第三方插件
Amango_Addons_Import('PHPExcel.php', 'Excelimport');
Amango_Addons_Import('PHPExcel/Reader/Excel2007.php', 'Excelimport');
//自动生成唯一数据表名
$table_name = 'addonsexcel' . str_replace('_', '', strtolower($config['excel_tablename']));
$tablename = C('DB_PREFIX') . $table_name;
$sql = <<<sql
\t\t\t\tSHOW TABLES LIKE '{$tablename}';
sql;
$res = M()->query($sql);
//判断表唯一 以便生成新表
if (count($res) > 0) {
$this->error('该表名已存在,请换个数据表名');
} else {
if (strpos($_POST['config']['excel_param'], ':') === false) {
$this->error('生成数据表时,请务必填写字段读取配置,格式：字段名:读取列标识');
} else {
$fields = '';
$field = array();
foreach ($xlsparam as $key => $value) {
if (!empty($key)) {
$fields .= "`{$key}` text,";
$field[] = $key;
}
}
//生成数据表
$sql = <<<sql
\t\t\t\tCREATE TABLE IF NOT EXISTS `{$tablename}` (
\t\t\t\t`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' ,
\t\t\t\t{$fields}
\t\t\t\tPRIMARY KEY (`id`)
\t\t\t\t)
\t\t\t\tENGINE=MyISAM
\t\t\t\tDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
\t\t\t\tCHECKSUM=0
\t\t\t\tROW_FORMAT=DYNAMIC
\t\t\t\tDELAY_KEY_WRITE=0
\t\t\t\t;
sql;
$res = M()->execute($sql);
if ($res === false) {
$this->error('建立数据表出错,请重新建表');
}
}
}
//导入数据表名 存入总表
D('addonsexcel')->add(array('fileds' => implode(',', $field), 'tablename' => $tablename));
$PHPExcel = new \PHPExcel();
/**默认用excel2007读取excel，若格式不对，则用之前的版本进行读取*/
$PHPReader = new \PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
$this->error('无法在' . $filePath . '路径下找到该文件');
}
}
$PHPExcel = $PHPReader->load($filePath);
/**读取excel文件中的第一个工作表*/
$currentSheet = $PHPExcel->getSheet($currentid);
/**取得最大的列号*/
$allColumn = $currentSheet->getHighestColumn();
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
//初始化
$rows = array();
foreach ($xlsparam as $key => $value) {
for ($currentRow = $startid; $currentRow <= $allRow; $currentRow++) {
$rowinfo = $currentSheet->getCell(strtoupper($value) . $currentRow)->getValue();
$rows[$currentRow][$key] = $config['excel_parxhtml'] == 1 ? strip_tags($rowinfo) : $rowinfo;
}
}
//读取出总记录
foreach ($rows as $key => $value) {
M($table_name)->add($value);
}
M('addonsexcel')->where(array('tablename' => $tablename))->save(array('rows' => count($rows)));
$this->success('新增成功', U('Addons/adminList', array('name' => 'Excelimport')));
}
/**
* @desc 读取excel数据
* @param string $excelFilePath excel的保存文件路径
*/
public function readExcel($excelFilePath)
{
$return = array('status' => 0);
$PHPReader = new PHPExcel_Reader_Excel2007();
$PHPReader->setReadDataOnly(true);
//设为只读
//如果没有选择excel文件
if (!$PHPReader->canRead($excelFilePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($excelFilePath)) {
$return['msg'] = 'no Excel';
return $return;
}
}
$PHPExcel = $PHPReader->load($excelFilePath);
//传入excel路径，载入该excel
$currentSheet = $PHPExcel->getSheet(0);
//sheet 0
$allColumn = $currentSheet->getHighestColumn();
//总列 //column Count
$allRow = $currentSheet->getHighestRow();
//总行//Row Count
$blockStartEndRow = $this->getBloclStartEndRow($allColumn, $allRow, $currentSheet);
$excelData = $this->getExcelData($blockStartEndRow, $allColumn, $allRow, $currentSheet);
if (ISPRINTEXCELDATAARR) {
echo '<pre>';
echo '开始-结束行<br/>';
print_r($blockStartEndRow);
echo '<hr/>';
echo 'excel数据数组<br/>';
print_r($excelData);
exit;
}
unlink($excelFilePath);
//删除excel文件
$return['data'] = $excelData;
return $return;
}
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Jakarta');
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br \\>');
include "blob.php";
require_once "PHPExcel.php";
$data = new koneksi();
$ds = DIRECTORY_SEPARATOR;
if (!empty($_FILES)) {
$tempFile = $_FILES['file']['tmp_name'];
$targetPath = "../template-report/";
$temp = explode(".", $_FILES["file"]["name"]);
$newFileName = rand(1, 99999999) . '.' . end($temp);
$targetFile = $targetPath . $newFileName;
move_uploaded_file($tempFile, $targetFile);
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objExcel = $objReader->load("../template-report/" . $newFileName);
$objExcel->setActiveSheetIndex(0);
$highestRow = $objExcel->setActiveSheetIndex(0)->getHighestRow();
for ($row = 7; $row <= $highestRow; $row++) {
$noreg = $objExcel->getSheet(0)->getCell('A' . $row)->getValue();
$nama = $objExcel->getSheet(0)->getCell('B' . $row)->getValue();
$alamat = $objExcel->getSheet(0)->getCell('C' . $row)->getValue();
$rayon = $objExcel->getSheet(0)->getCell('D' . $row)->getValue();
$pasar = $objExcel->getSheet(0)->getCell('E' . $row)->getValue();
$subpasar = $objExcel->getSheet(0)->getCell('F' . $row)->getValue();
$type = $objExcel->getSheet(0)->getCell('G' . $row)->getValue();
$area = $objExcel->getSheet(0)->getCell('H' . $row)->getValue();
$kodepos = $objExcel->getSheet(0)->getCell('I' . $row)->getValue();
$barcode = $objExcel->getSheet(0)->getCell('J' . $row)->getValue();
