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;
}
}
}
/**
* 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) {
for ($c = 0; $c < $num_cols; ++$c) {
protected function setPHPExcelReader($file)
{
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
return $objReader->load($file);
}
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');
}
function read_xls($xls_file)
{
include "static/PHPExcel.php";
$objPHPExcel = new PHPExcel();
//$objReader = new PHPExcel_Reader_Excel2007(); /*.xlsx*/
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($xls_file);
$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
$array_data = array();
foreach ($rowIterator as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
// Loop all cells, even if it is not set
if (1 == $row->getRowIndex()) {
continue;
}
//skip first row
$rowIndex = $row->getRowIndex();
$array_data[$rowIndex] = array('no' => '', 'product_category' => '', 'type_code' => '', 'product_name' => '', 'type_name' => '', 'color_group' => '', 'color_image' => '', 'type_price' => '', 'type_description' => '', 'type_weight' => '', 'size_type' => '', 'quantity' => '', 'image1' => '', 'image2' => '', 'image3' => '', 'image4' => '', 'image5' => '');
foreach ($cellIterator as $cell) {
if ('A' == $cell->getColumn()) {
$array_data[$rowIndex]["no"] = $cell->getCalculatedValue();
} else {
if ('B' == $cell->getColumn()) {
$array_data[$rowIndex]["product_category"] = $cell->getCalculatedValue();
} else {
if ('C' == $cell->getColumn()) {
$array_data[$rowIndex]["type_code"] = $cell->getCalculatedValue();
} else {
if ('D' == $cell->getColumn()) {
$array_data[$rowIndex]["product_name"] = $cell->getCalculatedValue();
} else {
if ('E' == $cell->getColumn()) {
$array_data[$rowIndex]["type_name"] = $cell->getCalculatedValue();
} else {
if ('F' == $cell->getColumn()) {
$array_data[$rowIndex]["color_group"] = $cell->getCalculatedValue();
} else {
if ('G' == $cell->getColumn()) {
$array_data[$rowIndex]["color_image"] = $cell->getCalculatedValue();
} else {
if ('H' == $cell->getColumn()) {
$array_data[$rowIndex]["type_price"] = $cell->getCalculatedValue();
} else {
if ('I' == $cell->getColumn()) {
$array_data[$rowIndex]["type_description"] = $cell->getCalculatedValue();
} else {
if ('J' == $cell->getColumn()) {
$array_data[$rowIndex]["type_weight"] = $cell->getCalculatedValue();
} else {
if ('K' == $cell->getColumn()) {
$array_data[$rowIndex]["size_type"] = $cell->getCalculatedValue();
} else {
if ('L' == $cell->getColumn()) {
$array_data[$rowIndex]["quantity"] = $cell->getCalculatedValue();
} else {
if ('M' == $cell->getColumn()) {
$array_data[$rowIndex]["image1"] = $cell->getCalculatedValue();
} else {
if ('N' == $cell->getColumn()) {
$array_data[$rowIndex]["image2"] = $cell->getCalculatedValue();
} else {
if ('O' == $cell->getColumn()) {
$array_data[$rowIndex]["image3"] = $cell->getCalculatedValue();
} else {
if ('P' == $cell->getColumn()) {
$array_data[$rowIndex]["image4"] = $cell->getCalculatedValue();
} else {
if ('Q' == $cell->getColumn()) {
$array_data[$rowIndex]["image5"] = $cell->getCalculatedValue();
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
//foreach cell
}
//foreach row
$result = translate($array_data);
insert_database_multiple($result);
}
public function excel_read($file)
{
$reader = new PHPExcel_Reader_Excel5();
$reader->setReadDataOnly(true);
// Not read styles
$excel = $reader->load($file);
//excel的路径
$data = $excel->getActiveSheet()->toArray(0, true, true, true);
return $data;
}
/**
* Reads the data from file in the DB and generates a wpDataTable
*/
public function readFileData($table_data)
{
$columnTypes = array();
if (!empty($table_data['file'])) {
$xls_url = urldecode($table_data['file']);
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$xls_url = str_replace(site_url(), str_replace('\\', '/', ABSPATH), $xls_url);
} else {
$xls_url = str_replace(site_url(), ABSPATH, $xls_url);
}
} else {
return _('Empty file', 'wpdatatables');
}
for ($i = 0; $i < count($table_data['columns']); $i++) {
if ($table_data['columns'][$i]['orig_header'] == '%%NEW_COLUMN%%') {
$table_data['columns'][$i]['orig_header'] = 'column' . $i;
}
$columnTypes[$table_data['columns'][$i]['orig_header']] = $table_data['columns'][$i]['type'];
}
$this->_id = $this->generateManualTable($table_data);
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
return _('File format not supported!', 'wpdatatables');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$insertArray = array();
// Insert statement default beginning
$insert_statement_beginning = "INSERT INTO " . $this->_name . " (" . implode(', ', array_values($this->_column_headers)) . ") ";
$insert_blocks = array();
for ($row = 2; $row <= $highestRow; ++$row) {
// Set all cells in the row to their defaults
foreach ($table_data['columns'] as $column) {
$insertArray[$this->_column_headers[$column['orig_header']]] = "'" . esc_sql($column['default_value']) . "'";
}
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
if (!in_array($dataColumnHeading, array_keys($this->_column_headers))) {
continue;
}
if ($columnTypes[$dataColumnHeading] != 'date') {
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . esc_sql($dataRow[$row][$dataColumnIndex]) . "'";
} else {
if ($objReader instanceof PHPExcel_Reader_CSV) {
$date = strtotime(str_replace('/', '-', $dataRow[$row][$dataColumnIndex]));
} else {
$date = esc_sql(PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]));
}
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . date('Y-m-d', $date) . "'";
}
}
}
$insert_blocks[] = '(' . implode(', ', $insertArray) . ')';
if ($row % 100 == 0) {
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
$insert_blocks = array();
}
}
$this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
}
public function excelBasedConstruct($xls_url, $wdtParameters = array())
{
ini_set("memory_limit", "2048M");
if (!$xls_url) {
throw new WDTException('Excel file not found!');
}
if (!file_exists($xls_url)) {
throw new WDTException('Provided file ' . stripcslashes($xls_url) . ' does not exist!');
}
require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
throw new WDTException('File format not supported!');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
$namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
if (WDT_DETECT_DATES_IN_EXCEL) {
$cellID = $dataColumnIndex . $row;
if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) {
$namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
}
}
}
}
}
$namedDataArray = apply_filters('wpdatatables_filter_excel_array', $namedDataArray, $this->getWpId(), $xls_url);
return $this->arrayBasedConstruct($namedDataArray, $wdtParameters);
}
private function getExcelData($file)
{
$reader = new \PHPExcel_Reader_Excel2007();
if (!$reader->canRead($file)) {
$reader = new \PHPExcel_Reader_Excel5();
if (!$reader->canRead($file)) {
throw new ErrorException("can not read file as Excel");
}
}
$reader->setReadDataOnly(true);
$excel = $reader->load($file);
$currentSheet = $excel->getActiveSheet();
$allRow = $currentSheet->getHighestRow();
$arr = [];
for ($j = 1; $j <= $allRow; $j++) {
/**从第A列开始输出*/
$strs = [];
for ($k = 'A'; $k <= 'F'; $k++) {
$strs[] = $excel->getActiveSheet()->getCell("{$k}{$j}")->getValue();
}
if (empty($strs[0]) && empty($strs[1])) {
continue;
}
$arr[] = $strs;
}
$data = [];
foreach ($arr as $i => $item) {
if ($i == 0) {
continue;
}
$data[$i]["ip"] = $item[0];
$data[$i]["port"] = $item[1];
$data[$i]["wire_frame"] = $item[2];
$data[$i]["wire_position"] = $item[3];
$data[$i]["point"] = $item[4];
$data[$i]["tag"] = $item[5];
/*
$data[$i]["insert_no"] = $item[5];
$data[$i]["tag"] = $this->getTagByIP($item[0],$item[5]);
*/
}
return $data;
}
