function getAll() { $reader = new Spreadsheet_Excel_Reader(); $reader->setUTFEncoder('iconv'); $reader->setOutputEncoding('UTF-8'); $reader->read($this->filename); $data = array(); $index_row = 1; if ($this->have_header) { $index_row = 2; } $iterator = -1; for ($i = $index_row; $i <= $reader->sheets[$this->sheet_index]['numRows']; $i++) { $iterator++; $data[$iterator] = array(); for ($j = 1; $j <= $reader->sheets[$this->sheet_index]['numCols']; $j++) { if ($this->have_header) { $data[$iterator][$this->headers[$j - 1]] = $reader->sheets[0]['cells'][$i][$j]; } else { $data[$iterator][$j - 1] = $reader->sheets[0]['cells'][$i][$j]; } } } return $data; }
function get_student_schedule($filename, $teacherSchedule) { $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('UTF8'); $data->setUTFEncoder('mb'); $data->read($filename); return get_result_student($data, $teacherSchedule); }
function TestBackupExcel() { $filename = 'products_test.xls'; $this->p_ex->DownloadIdMot = 1; // We will create file with articles, that are connected with id_mot=1 $this->p_ex->DownloadRubriques = array(1); // We will create file with articles, that lie in rubrique 1 $this->p_ex->BackupExcel($filename); $full_path = $this->p_ex->GetTmpDir() . $filename; $this->assertTrue(file_exists($full_path), 'Excel file hasn\'t been created!'); $reader = new Spreadsheet_Excel_Reader(); $reader->setUTFEncoder('iconv'); $reader->setOutputEncoding('UTF-8'); $reader->read($full_path); $this->assertEqual(1, count($reader->sheets)); // Document must contain 1 sheet $rows = $reader->sheets[0]["cells"]; $this->assertEqual(3, count($rows)); // Document must contain 1 heading + 1 data = 2 rows if (file_exists($full_path)) { unlink($full_path); } }
public function importAction() { $result = array('success' => true, 'info' => '导入成功'); $request = $this->getRequest()->getParams(); $now = date('Y-m-d H:i:s'); if (isset($_FILES['csv'])) { $now = date('Y-m-d H:i:s'); $user_session = new Zend_Session_Namespace('user'); $user_id = $user_session->user_info['user_id']; $file = $_FILES['csv']; $file_extension = strrchr($file['name'], "."); $h = new Application_Model_Helpers(); $tmp_file_name = $h->getMicrotimeStr() . $file_extension; $savepath = "../temp/"; $tmp_file_path = $savepath . $tmp_file_name; move_uploaded_file($file["tmp_name"], $tmp_file_path); $plist = new Product_Model_Productlist(); // 读取文件 require_once LIBRARY_PATH . "/Excel/reader.php"; $reader = new Spreadsheet_Excel_Reader(); $reader->setOutputEncoding('UTF-8'); $reader->setUTFEncoder('mb'); $reader->setRowColOffset(1); $reader->read($tmp_file_path); error_reporting(E_ALL ^ E_NOTICE); $data = array(); for ($i = 1; $i <= $reader->sheets[0]['numRows']; $i++) { $row = array(); for ($j = 1; $j <= $reader->sheets[0]['numCols']; $j++) { $row[] = $reader->sheets[0]['cells'][$i][$j]; } $data[] = $row; } // 数据校验 if (count($data) <= 1) { $result['success'] = false; $result['info'] = "文件中无数据!"; echo Zend_Json::encode($result); exit; } $cols = array("sn", "code", "step", "description", "is_bom_exists", "bom_apply_time", "bom_archive_time", "product_code", "bosa", "bosa_supply", "tosa", "tosa_supply", "rosa", "rosa_supply", "pcb", "pcba", "dg02", "dg01", "product_label", "barcode_label", "label_print_rule", "tooling_model", "sample_send_time", "pra", "trial_produce_qa1", "pmr", "dl", "ipa", "cri", "ds", "dd", "pl", "pes", "pcb_file", "icd", "smt", "mp", "sqr", "dvs", "dvp", "dvr", "dvt", "rtr", "emr", "mtb", "tsq", "sqc", "ed", "pts", "sp", "ep", "fep", "fsp", "ld", "pd", "pg", "nfc", "frm", "pfc", "wi", "other", "create_time", "create_user", "update_time", "update_user"); for ($i = 1; $i < count($data); $i++) { $num = $i + 1; $row = $data[$i]; if (count($row) < 2 || !$row[1]) { continue; } $code = $row[1]; if (!$this->checkExists($code)) { $insertData = array(); $k = 0; foreach ($cols as $c) { $insertData[$c] = $row[$k]; $k++; } try { $plist->insert($insertData); } catch (Exception $e) { $result['result'] = false; $result['info'] = $e->getMessage(); echo Zend_Json::encode($result); exit; } } else { $errors[] = $code; } } $result['error'] = $errors; } echo Zend_Json::encode($result); exit; }
function extract_text($file, $file0, $source_type, $url, $chrSet) { global $db_con, $tmp_dir, $pdftotext_path, $catdoc_path, $xls2csv_path, $op_system, $mb, $debug; global $catppt_path, $home_charset, $command_line, $no_log, $clear, $converter_dir, $cl, $index_xmeta; $result = array(); $home_charset1 = str_ireplace('iso-', '', $home_charset); $charset_int = str_ireplace('iso', '', $home_charset1); $temp_file = "tmp_file"; $filename = $tmp_dir . "/" . $temp_file; if ($source_type == 'ods') { $filename .= "." . $source_type . ""; } if ($source_type == 'doc') { $filename .= "." . $source_type . ""; } if ($source_type == 'docx') { $filename .= "." . $source_type . ""; } if ($source_type == 'xlsx') { $filename .= "." . $source_type . ""; } if (!($handle = fopen($filename, 'w'))) { die("Cannot open file {$filename} in temp folder"); } mysqltest(); if (fwrite($handle, $file) === FALSE) { die("Cannot write to file {$filename} in temp folder"); } fclose($handle); mysqltest(); // for PDF documents enter here if ($source_type == 'pdf') { /* include('../converter/pdf2text.php'); $a = new PDF2Text(); $a->setFilename($filename); $a->decodePDF(); echo $a->output(); */ if (!($handle = fopen($pdftotext_path, 'rb'))) { printStandardReport('errorNoPDFConv', $command_line); $result[] = 'ERROR'; } else { // prepare command line for PDF converter if ($op_system != 'win') { $command = "" . $pdftotext_path . " -enc UTF-8 " . $filename . ""; } else { $command = "" . $pdftotext_path . " -cfg xpdfrc " . $filename . " -"; } $a = exec($command, $result, $retval); // convert the PDF document if ($retval != '0') { // error handler for PDF file converter if ($retval == '1' || $retval == '3' || $retval == '127') { if ($retval == '1') { printStandardReport('errorOpenPDF', $command_line); } if ($retval == '3') { printStandardReport('permissionError', $command_line); } if ($retval == '127') { printStandardReport('noConverter', $command_line); } } else { printStandardReport('ufoError', $command_line); } $result[] = 'ERROR'; } $result = implode(' ', $result); } // for DOC and RTF files enter here } else { if ($source_type == 'doc' || $source_type == 'rtf') { /* echo "\r\n\r\n<br /> op_system: '$op_system'<br />\r\n"; echo "\r\n\r\n<br /> catdoc_path: '$catdoc_path'<br />\r\n"; echo "\r\n\r\n<br /> charset_int: '$charset_int'<br />\r\n"; echo "\r\n\r\n<br /> filename: '$filename'<br />\r\n"; */ if ($op_system == 'win') { $command = "" . $catdoc_path . " -s " . $charset_int . " -d utf-8 -x " . $filename . ""; $a = exec($command, $result, $retval); if (stristr($result[0], "catdoc.exe")) { printDocReport($result[0], $cl); } } else { $message = " Indexing of .doc and .rtf documents is currently not supported on LINUX OS."; printDocReport($message, $cl); /* $retval = ''; $catdoc_path = str_ireplace("catdoc.exe", "catdoc.lin", $catdoc_path); //$command = "".$catdoc_path." -cfg xpdfrc ".$filename." -"; $command = "".$catdoc_path." -s ".$charset_int." -d utf-8 -w -x ".$filename.""; $a = exec($command, $result, $retval); // convert the DOC document //echo "\r\n\r\n<br /> retval: '$retval'<br />\r\n"; //echo "\r\n\r\n<br>result Array:<br><pre>";print_r($result);echo "</pre>\r\n"; if ($retval) { $result = 'ERROR'; //echo "\r\n\r\n<br /> retval: '$retval'<br />\r\n"; if($retval == '2') { $message = " File to be converted not found"; printDocReport($message, $cl); } else if($retval == '3') { $message = " Path to file not found."; printDocReport($message, $cl); } else if($retval == '11') { $message = " The executable is corrupted."; printDocReport($message, $cl); } else if($retval == '12') { $message = " Out of memory execution."; printDocReport($message, $cl); } else if($retval == '22') { $message = " dll error"; printDocReport($message, $cl); } else if($retval == '31') { $message = " The association is missing, use Shell to try the OpenWith dialog."; printDocReport($message, $cl); } else if($retval == '32') { $message = " File could not be opened."; printDocReport($message, $cl); } else if($retval == '126') { $message = " Command invoked cannot execute (Permission problem or command is not an executable)."; printDocReport($message, $cl); } else if($retval == 127) { $message = " Command not found."; printDocReport($message, $cl); } else if($retval == 128) { $message = " Invalid argument to exit. Exit takes only integer range 0 – 255."; printDocReport($message, $cl); } else if($retval > 128 && $retval < 255) { $message = " Fatal error code $retval"; printDocReport($message, $cl); } else if($retval == 255) { $message = " Exit status out of range. Exit takes only integer range 0 – 255."; printDocReport($message, $cl); } else { $message = " Unknown error code $retval."; printDocReport($message, $cl); } } */ } // for PPT files enter here } else { if ($source_type == 'ppt') { // currently unsupported,as a failure was encountered for large PowerPoint presentations $a = ''; /* $command = $catppt_path." -s $charset_int -d utf-8 $filename"; $a = exec($command, $result, $retval); */ // for XLS spreadsheets enter here } else { if ($source_type == 'xls') { $error = ''; require_once "" . $converter_dir . "/xls_reader.php"; $data = new Spreadsheet_Excel_Reader(); if ($mb == '1') { // if extention exists, change 'iconv' to mb_convert_encoding: $data->setUTFEncoder('mb'); } // set output encoding. $data->setOutputEncoding('UTF-8'); // read this document $data->read($filename); $error = $data->_ole->error; if ($error == '1') { printStandardReport('xlsError', $command_line, $no_log); $result = 'ERROR'; } else { $result = ''; $boundsheets = array(); $sheets = array(); $boundsheets = $data->boundsheets; // get all tables in this file $sheets = $data->sheets; // get content of all sheets in all tables if ($boundsheets) { foreach ($boundsheets as &$bs) { $result .= "" . $bs['name'] . ", "; // collect all table names in this file } if ($sheets) { foreach ($sheets as &$sheet) { $cells = $sheet['cells']; if ($cells) { // ignore all empty cells foreach ($cells as &$cell) { foreach ($cell as &$content) { $result .= "" . $content . ", "; // collect content of all cells } } } } } if (strtoupper($home_charset) == 'ISO-8859-1') { $result = utf8_encode($result); } } } // for ODS spreadsheets enter here } else { if ($source_type == 'ods') { require_once "" . $converter_dir . "/ods_reader.php"; $reader = ods_reader::reader($filename); $sheets = $reader->read($filename); if ($sheets) { $result = ''; foreach ($sheets as &$sheet) { if ($sheet) { foreach ($sheet as &$cell) { if ($cell) { // ignore all empty cells foreach ($cell as &$content) { $result .= "" . $content . " "; // collect content of all cells } } } } } } else { $result = 'ERROR'; } // for ODT documents enter here } else { if ($source_type == 'odt') { require_once "" . $converter_dir . "/odt_reader.php"; $x = new odt_reader(); // Unzip the document $u = $x->odt_unzip($filename, false); // read the document $result = $x->odt_read($u[0], 2); // create some blanks around the <div> tags $result = str_replace("<", " <", $result); $result = str_replace(">", "> ", $result); //echo "\r\n\r\n<br /> odt result: $result<br />\r\n"; // for DOCX files enter here } else { if ($source_type == 'docx') { // converter class supplied by http://www.phpdocx.com $options = array('paragraph' => false, 'list' => false, 'table' => false, 'footnote' => false, 'endnote' => false, 'chart' => 0); $docx_file = "docx.txt"; $result = ''; require_once "" . $converter_dir . "/docx/CreateDocx.inc"; CreateDocx::DOCX2TXT($filename, $tmp_dir . "/" . $docx_file, $options); if ($file = @file_get_contents($tmp_dir . "/" . $docx_file)) { $result = "{$file} "; } if ($index_xmeta) { require_once "" . $converter_dir . "/xmeta_converter.php"; $docxmeta = new x_metadata(); $docxmeta->setDocument($filename); /* echo "Title : " . $docxmeta->getTitle() . "<br>"; echo "Subject : " . $docxmeta->getSubject() . "<br>"; echo "Creator : " . $docxmeta->getCreator() . "<br>"; echo "Keywords : " . $docxmeta->getKeywords() . "<br>"; echo "Description : " . $docxmeta->getDescription() . "<br>"; echo "Last Modified By : " . $docxmeta->getLastModifiedBy() . "<br>"; echo "Revision : " . $docxmeta->getRevision() . "<br>"; echo "Date Created : " . $docxmeta->getDateCreated() . "<br>"; echo "Date Modified : " . $docxmeta->getDateModified() . "<br>"; */ $result .= $docxmeta->getTitle() . $docxmeta->getSubject() . $docxmeta->getCreator() . $docxmeta->getKeywords() . $docxmeta->getDescription() . $docxmeta->getLastModifiedBy() . $docxmeta->getRevision() . $docxmeta->getDateCreated() . $docxmeta->getDateModified(); } @unlink($tmp_dir . "/" . $docx_file); /* if($result && $chrSet != "UTF-8") { $result = @mb_convert_encoding($result, "UTF-8", $chrSet); } */ // for XLSX spreadsheets enter here } else { if ($source_type == 'xlsx') { $result = ''; $i = 1; $name = ''; $finished = false; $names = array(); require_once "" . $converter_dir . "/xlsx_reader.php"; $xlsx = new SimpleXLSX($filename); $names = $xlsx->sheetNames(); //echo "\r\n\r\n<br>names array:<br><pre>";print_r($names);echo "</pre>\r\n"; if ($debug == 2 && $names) { printXLSXreport(count($names), $cl); } foreach ($names as $my_name) { $result .= $my_name . " "; if ($debug == 2) { printActKeyword($my_name); } } while (!$finished) { // get all sheets if ($rows = $xlsx->rows($i)) { foreach ($rows as $key) { foreach ($key as $val) { if ($val) { $result .= " " . $val; // add value of each cell } } } } else { $finished = true; // no more sheets found } //$my_name = $xlsx->sheetName($i); //echo "\r\n\r\n<br /> sheet name $i: '$my_name'<br />\r\n"; $i++; // try to get next sheet } if ($index_xmeta) { require_once "" . $converter_dir . "/xmeta_converter.php"; $xlscxmeta = new x_metadata(); $xlscxmeta->setDocument($filename); /* echo "Title : " . $xlscxmeta->getTitle() . "<br>"; echo "Subject : " . $xlscxmeta->getSubject() . "<br>"; echo "Creator : " . $xlscxmeta->getCreator() . "<br>"; echo "Keywords : " . $xlscxmeta->getKeywords() . "<br>"; echo "Description : " . $xlscxmeta->getDescription() . "<br>"; echo "Last Modified By : " . $xlscxmeta->getLastModifiedBy() . "<br>"; echo "Revision : " . $xlscxmeta->getRevision() . "<br>"; echo "Date Created : " . $xlscxmeta->getDateCreated() . "<br>"; echo "Date Modified : " . $xlscxmeta->getDateModified() . "<br>"; */ $result .= $xlscxmeta->getTitle() . $xlscxmeta->getSubject() . $xlscxmeta->getCreator() . $xlscxmeta->getKeywords() . $xlscxmeta->getDescription() . $xlscxmeta->getLastModifiedBy() . $xlscxmeta->getRevision() . $xlscxmeta->getDateCreated() . $xlscxmeta->getDateModified(); } /* if($result && $chrSet != "UTF-8") { $result = @mb_convert_encoding($result, "UTF-8", $chrSet); } */ // for JavaScript enter here } else { if ($source_type == 'js') { $result = extract_js($file); } } } } } } } } } if ($result != 'ERROR') { if (is_array($result)) { $result = implode(" ", $result); } $count = strlen($result); if ($count == '0') { // if there was not one word found, print warning message if ($source_type == 'js') { printStandardReport('jsEmpty', $command_line, $no_log); } else { printStandardReport('nothingFound', $command_line, $no_log); } $result = 'ERROR'; } } unlink($filename); mysqltest(); if ($clear == 1) { unset($command, $retval, $a, $file, $count); } return $result; }
public static function xls2Array($file, $DOCUMENT_ROOT, $setOutput = false) { mb_internal_encoding("ISO-8859-1"); require_once $DOCUMENT_ROOT . '/vendor/excel/excel_reader2.php'; $data = new Spreadsheet_Excel_Reader(); if ($setOutput) { $data->setOutputEncoding('CP-1251'); } $data->setUTFEncoder('mb'); $data->read($file); $arStrings = $data->sheets; return $arStrings[0]["cells"]; }
$file = "{$url_path}{$file}"; $short_desc = ''; $title = ''; $required = ''; $disallowed = ''; $can_leave_domain = ''; $parent_num = "0"; $lines = array(); // read .xls files into an array if (stristr($file, ".xls")) { $error = ''; require_once "" . $converter_dir . "/xls_reader.php"; $data = new Spreadsheet_Excel_Reader(); if ($mb == '1') { // if extention exists, change 'iconv' to mb_convert_encoding: $data->setUTFEncoder('mb'); } // set output encoding. $data->setOutputEncoding('UTF-8'); // read this document $data->read($file); $error = $data->_ole->error; if ($error == '1') { printStandardReport('xlsError', $command_line, $no_log); $result = 'ERROR'; } else { $result = ' '; $boundsheets = array(); $sheets = array(); $boundsheets = $data->boundsheets; // get all tables in this file
public function uploadAction() { error_reporting(E_ALL); $this->_initLayoutMessages('core/session'); $messages = Mage::getSingleton("customer/session")->getMessages(); $post_data = Mage::app()->getRequest()->getPost(); $uploadFiletype = $post_data['type']; $redirectUrl = $post_data['admin_url']; $file_name = $_FILES["file"]["name"]; $file_type = $_FILES["file"]["type"]; // File Type check if ($file_type == 'application/vnd.ms-excel') { // Code for File upload if (isset($_FILES['file']['name']) && $_FILES['file']['name'] != "") { $uploader = new Varien_File_Uploader("file"); $uploader->setAllowedExtensions(array('xls')); $uploader->setAllowRenameFiles(false); $uploader->setFilesDispersion(false); $path = Mage::getBaseDir("media") . DS . "upload" . DS; $uploader->save($path); } $excel_reader_path = Mage::getBaseDir("media") . DS . "Excel" . DS; $file_sourece = $path . $_FILES['file']['name']; require_once $excel_reader_path . 'excel_reader2.php'; $excel_reader = new Spreadsheet_Excel_Reader(); $excel_reader->setUTFEncoder('iconv'); $excel_reader->setOutputEncoding('CP1251'); $file = $excel_reader->read($file_sourece, "UTF-16"); $file_row = 3; $column_count = $excel_reader->sheets[0]['numCols']; $row_count = $excel_reader->sheets[0]['numRows']; $row_check = 0; for ($file_row; $file_row <= $excel_reader->sheets[0]['numRows']; $file_row++) { @($data1 = addslashes($excel_reader->sheets[0]['cells'][$file_row][1])); @($data2 = addslashes($excel_reader->sheets[0]['cells'][$file_row][2])); @($data3 = addslashes($excel_reader->sheets[0]['cells'][$file_row][3])); @($data4 = $excel_reader->sheets[0]['cells'][$file_row][4]); @($data5 = addslashes($excel_reader->sheets[0]['cells'][$file_row][5])); @($data6 = addslashes($excel_reader->sheets[0]['cells'][$file_row][6])); $pincode = $data4; $collection = Mage::getModel('fileupload/fileupload')->getCollection(); $collection->addFieldToFilter('pincode', array('like' => '%' . $pincode . '%')); $getDatas = $collection->getData(); /* echo "<pre>"; print_r($getDatas); echo "Count ".count($getDatas); echo "</pre>"; exit;*/ $connection = Mage::getSingleton('core/resource')->getConnection('core_write'); $connection->beginTransaction(); $sql = 'INSERT INTO fileupload (`location`, `branch_detail`, `served_by`, `pincode`, `area`, `file_type`, `state`) VALUES("' . $data1 . '","' . $data2 . '","' . $data3 . '","' . $data4 . '","' . $data5 . '","' . $uploadFiletype . '", "' . $data6 . '")'; $sql; $checkData = $connection->query($sql); $connection->commit(); $row_check++; } $this->_redirect('fileupload/adminhtml_fileupload/index/key/' . $redirectUrl . '?fstatus=1&data_cnt=' . $row_check); } else { $this->_redirect('fileupload/adminhtml_fileupload/index/key/' . $redirectUrl . '?fstatus=2'); } }
/** * Update products from given Excel file * @param string $file_path Excel file path */ public function updateProductsFromExcel($file_path, $primary_key = 'id_article', $create_new = false, $id_reserv = 0) { require_once _DIR_PLUGIN_EXCEL . "pear/Spreadsheet/Excel/Reader.php"; ini_set('max_execution_time', 0); switch ($primary_key) { case 'id_article': $col_primary = 1; break; case 'sku': $col_primary = 2; break; default: $col_primary = 1; break; } $path_info = pathinfo($file_path); $type_file = $path_info['extension']; if ($type_file == 'csv') { //echo '<h1>THIS CSV FILE!!!</h1>'; $fd = fopen($file_path, "r"); $i = 0; while (!feof($fd)) { $fields_win1251 = fgets($fd); $fields_UTF8 = iconv("windows-1251", "UTF-8", $fields_win1251); $fields = explode(";", $fields_UTF8); $product_article = $fields[0] != 0 ? $fields[0] : $field[4]; $product_name = $fields[1]; $product_manufacture = $fields[2]; $product_price = $fields[3]; $product_key = $fields[4]; $product_id_rubrique = $fields[5]; $product_id_extended_rubrique = $fields[6]; $id_article = false; $primary_val = $row["{$col_primary}"]; // First cell has index 1! //if ($primary_val) { $product_data = array(); $product_data['sku'] = $product_article; $product_data['titre'] = $product_name; $product_data['extra1'] = $product_manufacture; $product_data['extra2'] = $product_key; $product_data['price'] = $product_price; $article_data = array(); $article_data['titre'] = $product_name; // Update article status? if ($this->UpdateStatus) { $quantity = intval($product_data['quantity']); if ($quantity > 0) { $article_data['statut'] = 'publie'; } else { $article_data['statut'] = 'prepa'; } } // To avoid ambiguity select by id if $primary_key = 'id_article' if ($primary_key == 'id_article' && $primary_val) { $product_exist = $this->product_mdl->select_by_id(intval($primary_val)); } else { if ($primary_val) { $product_exist = $this->product_mdl->select($primary_key . " = '" . mysql_real_escape_string($primary_val) . "'"); if ($product_exist) { $product_exist = $product_exist[0]; } } else { $product_exist = false; } } if ((!isset($product_exist) || !$product_exist) && $create_new) { // Create new product? $id_rubrique = intval($product_id_extended_rubrique); if (!$id_rubrique) { $id_rubrique = intval($product_id_rubrique); if (!$id_rubrique) { $id_rubrique = $id_reserv; } // to do make reserv } $rubrique = $this->rubrique_mdl->select_by_id($id_rubrique); if (!$rubrique) { $rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique); $id_rubrique = $rubrique['id_rubrique']; } //spip_log($rubrique, 'excel.'._LOG_ERREUR); //echo '<pre>'; //print_r($rubrique); //echo '</pre>'; //exit; if (!$rubrique) { continue; } $article_data['id_rubrique'] = $id_rubrique; $article_data['id_secteur'] = $rubrique['id_secteur']; $article_data['statut'] = 'prepa'; // For new article statut must be defined $article_data['lang'] = $rubrique['lang']; // Insert article if (!($id_article = $this->article_mdl->insert($article_data))) { throw new Exception("Error while inserting article " . $primary_val); } // Insert product $product_data['id_article'] = $id_article; if (!$this->product_mdl->insert($product_data)) { throw new Exception("Error while inserting product #" . $id_article); } } else { if ($product_exist) { // Update old product $id_article = $product_exist['id_article']; $id_rubrique = intval($product_id_extended_rubrique); if (!$id_rubrique) { $id_rubrique = intval($product_id_rubrique); if (!$id_rubrique) { $id_rubrique = $id_reserv; } // to do make reserv } $rubrique = $this->rubrique_mdl->select_by_id($id_rubrique); if (!$rubrique) { $rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique); $id_rubrique = $rubrique['id_rubrique']; } if (!$rubrique) { continue; } $article_data['id_rubrique'] = $id_rubrique; $article_data['id_secteur'] = $rubrique['id_secteur']; // Update product if (!$this->product_mdl->update_by_id($id_article, $product_data)) { throw new Exception("Error while updating product #" . $id_article); } // Update article if (!$this->article_mdl->update_by_id($id_article, $article_data)) { throw new Exception("Error while updating article #" . $id_article); } } } $i++; spip_log("{$i}", 'excel.' . _LOG_ERREUR); } fclose($fd); } else { $reader = new Spreadsheet_Excel_Reader(); $reader->setUTFEncoder('iconv'); $reader->setOutputEncoding('UTF-8'); $reader->read($file_path); spip_log("EXCEL IMPORT START", 'excel.' . _LOG_ERREUR); foreach ($reader->sheets as $k => $data) { // $reader->boundsheets[$k]['name']; // Sheet name foreach ($data["cells"] as $row_num => $row) { if ($row_num == 1 || $row_num == 2) { spip_log($row_num, 'excel.' . _LOG_ERREUR); spip_log('row_0' . $row[0], 'excel.' . _LOG_ERREUR); spip_log('row_1' . $row[1], 'excel.' . _LOG_ERREUR); spip_log('row_2' . $row[2], 'excel.' . _LOG_ERREUR); spip_log('row_3' . $row[3], 'excel.' . _LOG_ERREUR); spip_log('row_4' . $row[4], 'excel.' . _LOG_ERREUR); spip_log('row_5' . $row[5], 'excel.' . _LOG_ERREUR); spip_log('row_6' . $row[6], 'excel.' . _LOG_ERREUR); spip_log('row_7' . $row[7], 'excel.' . _LOG_ERREUR); } if ($row_num == 1) { continue; } //omit first row $id_article = false; $primary_val = $row["{$col_primary}"]; // First cell has index 1! //if ($primary_val) { $product_data = array(); $product_data['sku'] = $row["2"]; //$product_data['titre'] = $row["4"]; $product_data['quantity'] = $row["6"]; $product_data['price'] = $row["7"]; $product_data['priceold'] = $row["8"]; $article_data = array(); $article_data['titre'] = $row["5"]; // Update article status? if ($this->UpdateStatus) { $quantity = intval($product_data['quantity']); if ($quantity > 0) { $article_data['statut'] = 'publie'; } else { $article_data['statut'] = 'prepa'; } } // To avoid ambiguity select by id if $primary_key = 'id_article' if ($primary_key == 'id_article' && $primary_val) { $product_exist = $this->product_mdl->select_by_id(intval($primary_val)); } else { if ($primary_val) { $product_exist = $this->product_mdl->select($primary_key . " = '" . mysql_real_escape_string($primary_val) . "'"); if ($product_exist) { $product_exist = $product_exist[0]; } } else { $product_exist = false; } } if ((!isset($product_exist) || !$product_exist) && $create_new) { // Create new product? $id_rubrique = intval($row['3']); if (!$id_rubrique) { $id_rubrique = intval($row['4']); } spip_log('ID_RUBRIQUE:' . $id_rubrique, 'excel.' . _LOG_ERREUR); if (!$id_rubrique) { continue; } $rubrique = $this->rubrique_mdl->select_by_id($id_rubrique); if (!$rubrique) { $rubrique = $this->rubrique_mdl2->get_rubric_by_id_import($id_rubrique); $id_rubrique = $rubrique['id_rubrique']; } //spip_log($rubrique, 'excel.'._LOG_ERREUR); //echo '<pre>'; //print_r($rubrique); //echo '</pre>'; //exit; if (!$rubrique) { continue; } $article_data['id_rubrique'] = $id_rubrique; $article_data['id_secteur'] = $rubrique['id_secteur']; $article_data['statut'] = 'prepa'; // For new article statut must be defined $article_data['lang'] = $rubrique['lang']; // Insert article if (!($id_article = $this->article_mdl->insert($article_data))) { throw new Exception("Error while inserting article " . $primary_val); } // Insert product $product_data['id_article'] = $id_article; if (!$this->product_mdl->insert($product_data)) { throw new Exception("Error while inserting product #" . $id_article); } } else { if ($product_exist) { // Update old product $id_article = $product_exist['id_article']; // Update product if (!$this->product_mdl->update_by_id($id_article, $product_data)) { throw new Exception("Error while updating product #" . $id_article); } // Update article if (!$this->article_mdl->update_by_id($id_article, $article_data)) { throw new Exception("Error while updating article #" . $id_article); } } } //} } } // end foreach woorkbook } }
/** * импорт товаров */ function importAction() { if ($_POST) { // коннектим функцию require_once ADMIN_PATH . '/plagin/excel/reader.php'; // ExcelFile($filename, $encoding); $data = new Spreadsheet_Excel_Reader(); // Set output Encoding. $data->setOutputEncoding('UTF8'); $data->setUTFEncoder('mb'); $data->read($_FILES['file1']['tmp_name']); error_reporting(E_ALL ^ E_NOTICE); $_str = array(); // С отключением товара if ($_POST['type_import'] == 0) { // обнуляем склад $_sql = "UPDATE `catalog` SET `sklad`='0' WHERE (`supplier`='" . $_POST['supplier'] . "')"; $result = mysql::just_query($_sql, 0); } for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { $_str[] = $data->sheets[0]['cells'][$i][$j]; } $_sql = "SELECT * FROM `catalog` WHERE (`artikul`='" . $_str[0] . "')"; $result_artikul = mysql::query_one($_sql, 0); if ($result_artikul) { // работаем с ценой if ($_str[2] == "") { $_sql = "UPDATE `catalog` \n\t\t\t\t\t\t\t\tSET `name`='" . $_str[1] . "', `sklad`='2'\n\t\t\t\t\t\t\t\tWHERE (`artikul`='" . $_str[0] . "')"; $result = mysql::just_query($_sql, 0); } else { $_sql = "UPDATE `catalog` \n\t\t\t\t\t\t\t\tSET `name`='" . $_str[1] . "', `sklad`='1'\n\t\t\t\t\t\t\t\tWHERE (`artikul`='" . $_str[0] . "')"; $result = mysql::just_query($_sql, 0); } // C обновленимем цены if ($_POST['type_import1'] == 0) { $_sql = "UPDATE `catalog` \n\t\t\t\t\t\t\t\tSET `cost`='" . $_str[3] . "'\n\t\t\t\t\t\t\t\tWHERE (`artikul`='" . $_str[0] . "')"; $result = mysql::just_query($_sql, 0); } } else { // товара нет на сайте if ($_str[2] == "") { $_sklad = 2; } else { $_sklad = 1; } $_sql = "INSERT INTO `catalog` (`artikul`, `name`, `cost`, `id_parent`, `status`, `created_at`, `sklad`, `supplier`) \n\t\t\t\t\t\t\tVALUES ('" . $_str[0] . "', '" . $_str[1] . "', '" . $_str[3] . "', '1', '1', '" . date('Y-m-d') . "', '" . $_sklad . "', '" . $_POST['supplier'] . "')"; $result = mysql::just_query($_sql, 0); } $_str = ""; } } // строим запрос $_sql = 'SELECT * FROM `supplier` where status=1 order by sort'; // выполняем запрос + при необходимости выводим сам запрос $select = mysql::query($_sql, 0); return system::show_tpl(array('msg' => $this->msg, 'tpl_folder' => $this->tpl_folder, 'select' => $select), $this->tpl_folder . '/import.php'); }