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; }