function getRowCount($filename, $attributes) { App::import('Vendor', 'import.excelreader/excelreader'); if (is_file($filename)) { if (isset($attributes['delimiter']) && (isset($attributes['excel_reader']) && $attributes['excel_reader'])) { $xl = new ExcelReader(); $xl->read($filename); $tmp_array = $xl->toArray(); return count($tmp_array); } else { $row_count = count(file($filename)); } } return false; }
function getRowCount() { App::import('Vendor', 'import.excelreader/excelreader'); if (is_file($this->filename)) { if (isset($this->options['delimiter']) && (isset($this->options['excel_reader']) && $this->options['excel_reader'])) { $xl = new ExcelReader(); $xl->read($this->filename); $tmp_array = $xl->toArray(); return count($tmp_array); } else { return count(file($this->filename)); } } return false; }
<?php require '../include/init.inc.php'; if (Common::isPost()) { if (empty($_FILES['excel'])) { OSAdmin::alert("error", "empty file"); } else { if ($_FILES['excel']['error'] != 0) { $message = 'ио╢╚нд╪Чй╖╟э,error number(' . $_FILES['excel']['error'] . ')'; OSAdmin::alert("error", $message); } $file = $_FILES['excel']['tmp_name']; $excel_array = ExcelReader::readXLS($file); $output = print_r($excel_array, true); } } Template::assign("_POST", $_POST); Template::assign("output", $output); Template::display('sample/read_excel.tpl');
static function openExcel($file_name) { require_once ROOT_PATH . 'includes/excel_reader.php'; self::$xls_reader = new Spreadsheet_Excel_Reader($file_name); // Đọc file excel, hỗ trợ Unicode UTF-8 }
public function actionNewimport() { //$path = _APP_PATH_ . DS . "data"; //$pathSource = 'E:\phuongnv\Vega\chacha_cloud\src\trunk\chacha\data'; $pathSource = _APP_PATH_ . DS . "data"; try { $log = new KLogger('LOG_IMPORT_FILE_SONG_PNV', KLogger::INFO); $log->LogInfo("Start New Import", false); $model = new AdminImportSongModel(); $importer = self::IMPORT_SONG_CACHE . Yii::app()->user->id; $result = array(); if (isset($_POST['AdminImportSongModel'])) { $this->layout = false; $autoconfirm = Yii::app()->request->getParam('autoconfirm'); $autoconfirm = isset($autoconfirm) ? 1 : 0; $created_time = $_POST['AdminSongModel']['created_time']; $updated_time = $_POST['AdminSongModel']['updated_time']; $path = Yii::app()->params['importsong']['store_path']; $file_path = $pathSource . DS . "tmp" . DS . $_POST['AdminImportSongModel']['source_path']; $fileName = explode(DS, $file_path); $fileName = $fileName[count($fileName) - 1]; if (file_exists($file_path)) { $count = 0; $start_row = $_POST['AdminImportSongModel']['start_row'] > 0 ? $_POST['AdminImportSongModel']['start_row'] : 0; $start_row += self::START_ROW; $limit_row = 65000; $limit_row += $start_row; $log->LogInfo("Start Read File and put Memcache | " . $file_path, false); $data = new ExcelReader($file_path); $resultSql = array(); //insert file $sql = "INSERT INTO import_song_file(file_name,importer,status,created_time)\n \t\t\tVALUE('" . $fileName . "', '{$importer}',0,NOW())\n\t \t\t\t"; $insertFileRess = Yii::app()->db->createCommand($sql)->execute(); $fileImportId = Yii::app()->db->getLastInsertID(); for ($i = $start_row; $i < $limit_row; $i++) { if ($data->val($i, 'B') != "" && $data->val($i, 'G') != "" && $data->val($i, 'C') != "") { $stt = $data->val($i, Yii::app()->params['importsong']['excelcolumns']['stt']); $name = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['name'])); $category = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['category'])); $sub_category = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['sub_category'])); $composer = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['composer'])); $artist = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['artist'])); $album = $model->my_encoding($data->val($i, Yii::app()->params['importsong']['excelcolumns']['album'])); $path = str_replace('\\', DS, $data->val($i, Yii::app()->params['importsong']['excelcolumns']['path'])); $file = $data->val($i, Yii::app()->params['importsong']['excelcolumns']['file']); $sql = "("; $sql .= "'" . $autoconfirm . "'"; $sql .= ",'" . $created_time . "'"; $sql .= ",'" . $updated_time . "'"; $sql .= ",'" . $stt . "'"; $sql .= ",'" . addslashes($name) . "'"; $sql .= ",'" . $category . "'"; $sql .= ",'" . $sub_category . "'"; $sql .= ",'" . addslashes($composer) . "'"; $sql .= ",'" . addslashes($artist) . "'"; $sql .= ",'" . addslashes($album) . "'"; //$sql .= ",'".str_replace('\\', '\\\\', $path)."'"; $sql .= ",'" . addslashes($path) . "'"; //$sql .= ",'".str_replace('\\', '\\\\', $file)."'"; $sql .= ",'" . addslashes($file) . "'"; $sql .= ",'" . $importer . "'"; //$sql .= ",'".str_replace('\\', '\\\\', $file_path)."'"; $sql .= ",'" . addslashes($file_path) . "'"; $sql .= ",'" . $fileImportId . "'"; $sql .= ")"; $resultSql[] = $sql; $count++; } /* if($count==10) echo '<pre>';print_r($result);die(); */ } //insert data to db if ($insertFileRess) { $sql = "INSERT INTO import_song(autoconfirm,created_time,updated_time,stt,name,category,sub_category,composer,artist,album,path,file,importer,file_name,file_id) VALUES"; $sql .= implode(',', $resultSql); if (Yii::app()->db->createCommand($sql)->execute()) { $sql = "UPDATE import_song_file set total_song={$count} WHERE id={$fileImportId}"; Yii::app()->db->createCommand($sql)->execute(); } //insert false } //remove file source after insert $fileSystem = new Filesystem(); $fileSystem->remove($file_path); echo CJSON::encode(array('errorCode' => 0, 'errorDesc' => 'Success imported Total Record: ' . count($resultSql))); } else { //if ($_POST['AdminImportSongModel']['ajax']) echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'Chưa upload file excel')); } Yii::app()->end(); } } catch (Exception $e) { $log->LogError("actionAjaxImport | Exception Error: " . $e->getMessage(), false); echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'Chưa upload file excel')); Yii::app()->end(); } $uploadModel = new XUploadForm(); $this->render('newimport', array('model' => $model, 'listSong' => $result, 'uploadModel' => $uploadModel)); }
/** * Creates a new model. * If creation is successful, the browser will be redirected to the 'view' page. */ public function actionCreate() { $model = new ImportUserFileModel(); $content_type = Yii::app()->request->getParam('content_type', 'subscribe'); Yii::app()->session['content_type'] = $content_type; if (Yii::app()->request->isPostRequest) { $content_type = Yii::app()->session['content_type']; $package_id = Yii::app()->request->getParam('package_id', null); if (!isset($package_id)) { $model->addError("name", "Chưa chọn gói cước!"); } if (!$_FILES['file']['error']) { $ext = Utils::getExtension($_FILES['file']['name']); $type = $_FILES['file']['type']; if ($ext == 'xls' && ($type == 'application/xls' || $type == 'application/vnd.ms-excel')) { $storage = Yii::app()->params['storage']['baseStorage'] . "uploads" . DS . "user" . DS; Utils::makeDir($storage); $fileName = $_FILES['file']['name']; if (move_uploaded_file($_FILES['file']['tmp_name'], $storage . DS . $fileName)) { $transaction = Yii::app()->db->beginTransaction(); try { $file_path = $storage . DS . $fileName; $model->file_name = $fileName; $model->created_by = $this->userId; $model->file_path = $file_path; $model->package_id = $package_id; $model->created_time = new CDbExpression("NOW()"); $model->content_type = $content_type; if ($model->save(false)) { $data = new ExcelReader($file_path); $start_row = 2; $limit_row = $data->rowcount(); $cell_name = "A"; $arrayVal = array(); for ($i = $start_row; $i <= $limit_row; $i++) { if ($data->val($i, $cell_name) == "") { continue; } $name = $model->my_encoding($data->val($i, $cell_name)); $arrayVal[] = "('{$name}','{$model->id}','{$package_id}')"; } /** * Start insert here: split 200 line per command */ $arrs = array_chunk($arrayVal, 200); foreach ($arrs as $arr) { $vals = implode(",", $arr); $sql = "INSERT INTO import_user_content (`msisdn`,`file_id`,`package_id`) VALUES {$vals}"; $command = Yii::app()->db->createCommand($sql); $command->execute(); } $transaction->commit(); $this->redirect(array('view', 'id' => $model->id, 'content_type' => $content_type)); } else { $transaction->rollback(); } } catch (Exception $e) { $transaction->rollback(); $model->addError("exception", $e->getMessage()); } } else { $model->addError("name", "Không upload được file vào thư mục:" . $storage . DS . $fileName); } } else { $model->addError("name", "Chỉ upload file xls"); } } else { $model->addError("name", "Chưa upload file"); // $model->content_type = $content_type; } } $this->render('create', array('model' => $model, 'content_type' => $content_type)); }
/** * 初期化 * */ public function __construct() { parent::__construct(); ini_set('memory_limit', '256M'); }
/** * Creates a new model. * If creation is successful, the browser will be redirected to the 'view' page. */ public function actionCreate() { $model = new CopyrightInputFileModel(); if (Yii::app()->request->isPostRequest) { $content_type = Yii::app()->request->getParam('content_type', null); if (!isset($content_type)) { $model->addError("name", "Chưa chọn loại nội dung content_type!"); } else { if (!$_FILES['file']['error']) { $ext = Utils::getExtension($_FILES['file']['name']); $type = $_FILES['file']['type']; if ($ext == 'xls' && ($type == 'application/xls' || $type == 'application/vnd.ms-excel')) { $storage = Yii::app()->params['storage']['baseStorage'] . "uploads" . DS . "copyright"; Utils::makeDir($storage); $fileName = $_FILES['file']['name']; if (move_uploaded_file($_FILES['file']['tmp_name'], $storage . DS . $fileName)) { $transaction = Yii::app()->db->beginTransaction(); try { $model->file_name = $fileName; $model->content_type = $content_type; $model->created_by = $this->userId; $model->created_time = new CDbExpression("NOW()"); if ($model->save()) { $file_path = $storage . DS . $fileName; $data = new ExcelReader($file_path); $start_row = 2; $limit_row = $data->rowcount(); $cell_content_id = "A"; $cell_name = "B"; $cell_artist = "C"; $cell_copyright_code = "F"; $copyright_id = "J"; $arrayVal = array(); for ($i = $start_row; $i < $limit_row; $i++) { if ($data->val($i, $cell_name) == "" || $data->val($i, $cell_artist) == "" || $data->val($i, $copyright_id) == "") { continue; } $contentId = $model->my_encoding($data->val($i, $cell_content_id)); $name = $model->my_encoding($data->val($i, $cell_name)); $name = '"' . $name . '"'; $artist = $model->my_encoding($data->val($i, $cell_artist)); $ccode = $model->my_encoding($data->val($i, $cell_copyright_code)); $ccid = $model->my_encoding($data->val($i, $copyright_id)); $arrayVal[] = "('" . $contentId . " ', " . $name . " ,'" . $artist . " ','" . $ccode . " '," . $ccid . " ,'{$model->id}')"; } /** * Start insert here: split 200 line per command */ $arrs = array_chunk($arrayVal, 200); foreach ($arrs as $arr) { $vals = implode(",", $arr); $sql = "INSERT INTO copyright_input_content (`content_id`,`name`,`artist`,`copyright_code`,`copyright_id`,`input_file`) VALUES {$vals}"; $command = Yii::app()->db->createCommand($sql); $command->execute(); } $transaction->commit(); $this->redirect(array('view', 'id' => $model->id)); } else { $transaction->rollback(); } } catch (Exception $e) { $transaction->rollback(); $model->addError("exception", $e->getMessage()); } } else { $model->addError("name", "Không upload được file vào thư mục:" . $storage . DS . $fileName); } } else { $model->addError("name", "Chỉ upload file xls"); } } else { $model->addError("name", "Chưa upload file"); $model->content_type = $content_type; } } } $this->render('create', array('model' => $model)); }
public static function save($param, $table, $month, $province_id, $record_id) { foreach ($param as $key => $value) { $param[$key] = addslashes($value); } $param[] = $record_id; $columns['base'] = self::$base; $columns['custom'] = self::$custom; $columns['complaints'] = self::$complaints; $columns['income'] = self::$income; $columns['value_income'] = self::$value_income; $columns['black_list'] = self::$black_list; $columns['complaint_province'] = self::$complaint_province; $columns['complaint_class'] = self::$complaint_class; // unset($param[0]); // var_dump(count(explode(',', $columns[$table])),count($param));exit; $bussLine = array('联通在信' => 1, '彩信' => 2); $db = self::__instance(); if ($table == 'complaint_province') { $param[0] = Info::getProvinceByName($param[0]); $param[2] = strtotime($param[2] . '01'); if (strtotime($month . '-01') != $param[2]) { return false; } } if ($table == 'complaint_class') { $param[2] = strtotime($param[2] . '01'); if (strtotime($month . '-01') != $param[2]) { return false; } } if ($table == 'base') { $param[0] = Info::getProvinceByName($param[0]); // $param[6] = ExcelReader::xlsTime($param[6]); $param[25] = strtotime($param[25] . '01'); if (strtotime($month . '-01') != $param[25]) { return false; } if ($province_id != $param[0]) { return false; } $tmp = array($param[3], $param[0], $param[6], $param[25], $param[5], '', 1, '一年'); $num = $db->count('co_base', array('complaint_phone' => $param[3])); if ($num > 0) { $db->delete('co_black_list', array('complaint_phone' => $param[3])); $tmp = array($param[3], $param[0], $param[6], $param[25], $param[5], '', 2, '五年'); } $sql = 'insert into co_black_list (' . $columns['black_list'] . ') values ("' . implode('","', $tmp) . '")'; if ($param[3]) { $r = $db->query($sql); } } if ($table == 'custom') { $param[29] = strtotime($param[29] . '01'); if (strtotime($month . '-01') != $param[29]) { return false; } $param[22] = Info::getProvinceByName($param[22]); // if($province_id != $param[22]) // return true; $param[25] = ExcelReader::xlsTime($param[25]); //$param[6] = ExcelReader::xlsTime($param[6]); $param[6] = strtotime($param[6]); $param[7] = $bussLine[$param[7]]; $tmp = array($param[16], $param[22], $param[6], $param[29], $param[2], '', 1, '一年'); $num = $db->count('co_custom', array('complaint_phone' => $param[16])); if ($num > 0) { $db->delete('co_black_list', array('complaint_phone' => $param[16])); $tmp = array($param[16], $param[22], $param[6], $param[29], $param[2], '', 2, '五年'); } $sql = 'insert into co_black_list (' . $columns['black_list'] . ') values ("' . implode('","', $tmp) . '")'; if ($param[16]) { $db->query($sql); } // var_dump(Info::getProvinceByName($param[22]));exit; } if ($table == 'complaints') { // $param[17] = ExcelReader::xlsTime($param[17]); // $param[18] = ExcelReader::xlsTime($param[18]); // $param[19] = ExcelReader::xlsTime($param[19]); $param[7] = Info::getProvinceByName($param[7]); // if($province_id != $param[7]) // return true; $param[47] = strtotime($param[47] . '01'); if (strtotime($month . '-01') != $param[47]) { return false; } $tmp = array($param[4], $param[7], $param[42], $param[47], $param[41], '', 3, '永久屏蔽'); $sql = 'insert into co_black_list (' . $columns['black_list'] . ') values ("' . implode('","', $tmp) . '")'; $db->query($sql); } if ($table == 'income') { $param[0] = Info::getProvinceByName($param[0]); // if($province_id != $param[0]) // return true; $param[13] = strtotime($param[13] . '01'); if (strtotime($month . '-01') != $param[13]) { return false; } } if ($table == 'value_income') { $param[0] = strtotime($param[0] . '01'); if (strtotime($month . '-01') != $param[0]) { return false; } } // var_dump($param);exit; $sql = "insert into co_" . $table . " (" . $columns[$table] . ") values ('" . implode("','", $param) . "')"; // echo $sql.'<br>';exit; $r = $db->query($sql); // if(!$r) // echo $sql; return $r; }
public function actionNewimport() { //$path = _APP_PATH_ . DS . "data"; //$pathSource = 'E:\phuongnv\Vega\chacha_cloud\src\trunk\chacha\data'; $pathSource = _APP_PATH_ . DS . "data"; $log = new KLogger('LOG_CHECK_SONG_IMPORT', KLogger::INFO); $log->LogInfo("Start New Import", false); try { $model = new AdminImportSongModel(); $importer = self::IMPORT_SONG_CACHE . Yii::app()->user->id; $result = array(); if (isset($_POST['AdminImportSongModel'])) { $this->layout = false; $file_path = $pathSource . DS . "tmp" . DS . $_POST['AdminImportSongModel']['source_path']; $fileName = explode(DS, $file_path); $fileName = $fileName[count($fileName) - 1]; if (file_exists($file_path)) { $log->LogInfo("Start Read File and put Memcache | " . $file_path, false); $data = new ExcelReader($file_path); //insert file $sql = "INSERT INTO import_song_file(file_name,importer,status,created_time,type)\n \t\t\tVALUE('" . $fileName . "', '{$importer}',0,NOW(),'CHECK')\n\t \t\t\t"; $log->LogInfo("SQL: " . $sql, false); $insertFileRess = Yii::app()->db->createCommand($sql)->execute(); $fileImportId = Yii::app()->db->getLastInsertID(); $i = 1; $err = 0; $count = 0; $total = 0; $sqlAr = array(); $flag = true; while ($flag) { if ($data->val($i, 'A') != "" && $data->val($i, 'B') != "" && $data->val($i, 'C') != "") { $stt = $data->val($i, 'A'); $songName = $model->my_encoding($data->val($i, 'B')); $artistName = $model->my_encoding($data->val($i, 'C')); $artistId = $this->getArtistId($artistName); $artistId = implode(',', $artistId); $sqlAr[] = "('{$stt}','" . addslashes($songName) . "', '" . addslashes($artistName) . "', {$fileImportId}, '{$importer}','{$artistId}')"; $count++; $total++; } else { $err++; } $i++; if ($err == 3) { $flag = false; } if ($count == 200 || !$flag) { $sql = "INSERT INTO import_song(stt,name,artist,file_id,importer,album) VALUES"; $sql .= implode(',', $sqlAr); $log->LogInfo("SQL: " . $sql, false); if (Yii::app()->db->createCommand($sql)->execute()) { //reset $count = 0; $sqlAr = array(); } } } $sql = "UPDATE import_song_file set total_song={$total} WHERE id={$fileImportId}"; Yii::app()->db->createCommand($sql)->execute(); //remove file source after insert /* $fileSystem = new Filesystem(); $fileSystem->remove($file_path); */ echo CJSON::encode(array('errorCode' => 0, 'errorDesc' => 'Success imported Total Record: ' . $total . ' Go to <a href="' . Yii::app()->createUrl('/tools/importSong/index&fileId=' . $fileImportId) . '">Scan</a>')); } else { echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'File không tồn tại:' . $file_path)); $log->LogInfo("File không tồn tại: " . $file_path, false); } Yii::app()->end(); } } catch (Exception $e) { echo CJSON::encode(array('errorCode' => 1, 'errorDesc' => 'Exception:' . $e->getMessage())); $log->LogError("actionAjaxImport | Exception Error: " . $e->getMessage(), false); Yii::app()->end(); } $uploadModel = new XUploadForm(); $this->render('newimport', array('model' => $model, 'listSong' => $result, 'uploadModel' => $uploadModel)); }