public function actionFullupload() { /* $price_update = Prices::model()->findByAttributes(['trade_date'=>'0000-00-00', 'instrument_id' =>43], [ 'condition'=>'price!=:price', 'params'=>array('price'=>17.420), ] ); var_dump($price_update); exit; */ $model = new Uploads(); //$path = Yii::app()->basePath.'../../uploads/'; $path = Yii::getPathOfAlias('webroot') . '/uploads/'; if (isset($_POST['Uploads'])) { Yii::import('ext.phpexcel.XPHPExcel'); XPHPExcel::init(); ini_set('max_execution_time', 150000); ini_set("memory_limit", "128M"); require_once Yii::app()->basePath . '/extensions/XLSXReader/XLSXReader.php'; //OKarray(2) { ["Uploads"]=> array(2) { ["instrument_id"]=> string(2) "12" ["upload_description"]=> string(5) "sfggs" } ["yt0"]=> string(6) "Upload" } $model->attributes = $_POST['Uploads']; if ($upload_file = self::uploadMultifile($model, 'upload_file', $path)) { $model->upload_file = implode(",", $upload_file); } $model->user_id = Yii::app()->user->id; //$instrument_id = $model->instrument_id; ////////////////////////////////////////// if ($model->validate()) { //Upload File // if ($model->save()) { $upload_file_id = Yii::app()->db->getLastInsertID(); $csvFile = CUploadedFile::getInstance($model, 'upload_file', '../../uploads/'); $tempLoc = Yii::getPathOfAlias('webroot') . '/uploads/' . $model->upload_file; $xlsx = new XLSXReader($tempLoc); $data = $xlsx->getSheetData('Sheet1'); $instruments = Instruments::model()->findAll(array('select' => 'id, instrument')); $instruments_for_returns_update = []; foreach ($data as $dat) { $trade_date = gmdate('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dat['0'])); $instrument_name = trim($dat['1']); $price = $dat['2']; $currency = $dat['3']; $instrument = Instruments::model()->findByAttributes(['instrument' => $instrument_name, 'is_current' => 1]); if ($instrument) { $instrument_id = $instrument->id; $instruments_for_returns_update[] = $instrument_id; } else { $new_instrument = new Instruments(); $new_instrument->instrument = $instrument_name; $new_instrument->price_uploaded = 1; $new_instrument->currency = $currency; $new_instrument->save(); $instrument_id = $new_instrument->id; $instruments_for_returns_update[] = $instrument_id; } $existing_record = Prices::model()->findByAttributes(['trade_date' => $trade_date, 'instrument_id' => $instrument_id]); if ($existing_record) { if ($existing_record->price !== $price) { $existing_record->price = $price; $existing_record->upload_file_id = $upload_file_id; $existing_record->save(); } } else { $new_price = new Prices(); $new_price->instrument_id = $instrument_id; $new_price->trade_date = $trade_date; $new_price->price = $price; $new_price->upload_file_id = $upload_file_id; //$new_price->name = $instrument_name; $new_price->save(); } } $unique_instruments_for_returns_update = array_unique($instruments_for_returns_update); Returns::model()->instrumnetReturnsUpdate($unique_instruments_for_returns_update); Yii::app()->user->setFlash('success', "Prices Uploaded!"); @chmod($tempLoc, 0777); @unlink($tempLoc); //unlink(Yii::getPathOfAlias('webroot').'/uploads/'.$model->upload_file); //$this->redirect(array('view','id'=>$model->id)); $user_data = Users::model()->findByPk(Yii::app()->user->id); $step_completed = $user_data->step_completed; if ($user_data->user_role == 2 && $step_completed < 2) { $user_data->step_completed = 1; $user_data->save(); $this->redirect(Yii::app()->baseUrl . '/site/admin'); } //else{ // $this->redirect(Yii::app()->baseUrl.'/site/admin'); // $this->render('overview', ['user_data' => $user_data]); } } } /////////////////////////////////////////// } $this->render('upload_form', array('model' => $model)); }
private function exportEXCEL($excelname, $col, $title, $colAry) { // PHP EXCEL 初始化 XPHPExcel::init(); $fileTitle = "JIT Excel File"; $objPHPExcel = XPHPExcel::createPHPExcel(); $objPHPExcel->getProperties()->setCreator("JIT")->setLastModifiedBy("JIT")->setTitle($fileTitle)->setSubject("")->setDescription($fileTitle)->setKeywords("office 2007 openxml php")->setCategory("Excel File"); // 第一列 填入標題 $column = 0; // 第幾欄. 由第0欄開始 for ($i = 0; $i < count($col); $i++) { if (isset($title[$col[$i]])) { $objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($column, 1, isset($title[$col[$i]]) ? $title[$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING); $column++; } } // 後續 填入內容 // 第幾列, 由第2列開始 $row = 2; for ($j = 0; $j < count($colAry); $j++) { if (isset($colAry[$j][$col[0]])) { $column = 0; // 第幾欄. 由第0欄開始 for ($i = 0; $i < count($col); $i++) { // 若符合篩選欄位. 才進行 if (isset($title[$col[$i]])) { /*$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column, $row, (isset($colAry[$j][$col[$i]]))? $colAry[$j][$col[$i]]:'');*/ //避免(007002變7002) 0被吃掉,所以關鍵 setValueExplicit所有都轉字串 $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->setValueExplicit(isset($colAry[$j][$col[$i]]) ? $colAry[$j][$col[$i]] : '', PHPExcel_Cell_DataType::TYPE_STRING); $column++; } } $row++; } } //CVarDumper::dump($colAry,10,true); // Rename worksheet $objPHPExcel->getActiveSheet()->setTitle($excelname . '-正航匯入表'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a web browser (Excel5) $webroot = Yii::getPathOfAlias('webroot'); //$fileName =$excelname.'-'.time().'.xls'; $fileName = 'PerformOut01' . '.xls'; $filePath = $webroot . '/' . "protected" . '/' . "tmp" . '/'; $fileUrl = $filePath . $fileName; // If you're serving to IE over SSL, then the following may be needed // header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past // header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified // header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1 // header ('Pragma: public'); // HTTP/1.0 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); // $fileName = Yii::app()->request->baseUrl."/protected/tmp/".$fileName; // CVarDumper::dump($fileName); // $objWriter->save($fileName); $objWriter->save($fileUrl); // // Yii::app()->end(); // $objWriter->save(str_replace('.php', '.xls', __FILE__)); // $objWriter->save(str_replace(__FILE__,'/protected/tmp/'.$fileName,__FILE__)); // $objWriter->save('/protected/tmp/'.$fileName); // @readfile('/protected/tmp/'.$fileName); // spl_autoload_register(array('YiiBase','autoload'),true,true); return $fileName; }
public function actionExportResponseStatistics($id, $location) { $survey = $this->loadModel($id, $location); $allowedForUsers = array_map(function ($x) { return $x->userId; }, $survey->surveyUsers); if (!Yii::app()->user->checkAccess('survey.view') && !Yii::app()->user->checkAccess('survey.viewAndDownloadAllowed', array('user_ids' => $allowedForUsers))) { throw new Http403Exception(); } if ($survey->surveyInfo && $survey->surveyInfo->vicidialCampaign) { $statistics = $survey->surveyInfo->vicidialCampaign->getCallsStatistics(); Yii::import('ext.PHPExcel.XPHPExcel'); XPHPExcel::init(); $phpExcel = XPHPExcel::createPHPExcel(); $phpExcel->getProperties()->setCreator("BCGroup"); $phpExcel->getProperties()->setLastModifiedBy("BCGroup"); $phpExcel->getProperties()->setTitle("Статистика отвечаемости по проекту {$survey->title}"); $phpExcel->getProperties()->setSubject("Статистика отвечаемости"); $phpExcel->setActiveSheetIndex(0); $phpExcel->getActiveSheet()->SetCellValue('A1', 'Код статуса'); $phpExcel->getActiveSheet()->SetCellValue('B1', 'Название статуса'); $phpExcel->getActiveSheet()->SetCellValue('C1', 'Количество'); foreach ($statistics as $idx => $statisticsRow) { $phpExcel->getActiveSheet()->SetCellValue('A' . ($idx + 2), $statisticsRow['status']); $phpExcel->getActiveSheet()->SetCellValue('B' . ($idx + 2), $statisticsRow['status_name']); $phpExcel->getActiveSheet()->SetCellValue('C' . ($idx + 2), $statisticsRow['count']); } $phpExcel->getActiveSheet()->setTitle('Статистика отвечаемости'); $phpExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true); $phpExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); $phpExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true); $phpExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true); $objWriter = new PHPExcel_Writer_Excel2007($phpExcel); $filePath = $survey->getResponseStatisticsSrc(); $objWriter->save($filePath); Yii::app()->request->sendFile(basename($filePath), file_get_contents($filePath)); } else { throw new CHttpException(404, 'The requested page does not exist.'); } Yii::app()->end(); }
echo "</div>"; break; case 13: echo "<table class='table'><tr><td>"; $i = 0; $id = $element->id; //echo "<a id=traceability style='color:black; background-color:rgb(200,200,200); border-radius:4px; ' onclick=toggle_visibility('info')> Click for Info </a></td>"; foreach ($element->columns as $column) { echo '<th>' . $column->value . '</th>'; $i++; } echo "</tr>"; $r = 0; echo "<h1>PHPExcel Reader</h1>"; Yii::import('ext.phpexcel.XPHPExcel'); XPHPExcel::init(); //$objPHPExcel = new PHPExcel(); //set_include_path(get_include_path() . PATH_SEPARATOR . 'c:\inetpub\wwwroot\vector2014\protected\vendors\\'); //include 'PHPExcel\IOFactory.php'; $inputFileName = 'protected/extensions/examples/reader/sampledata/example1.xls'; echo 'Loading file ', pathinfo($inputFileName, PATHINFO_BASENAME), ' using IOFactory to identify the format<br />'; $objPHPExcel = PHPExcel_IOFactory::load($inputFileName); echo '<br />'; $sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true); var_dump($sheetData); foreach ($element->rows as $row) { echo '<tr><th>' . $row->value . '</th>'; for ($index = 0; $index < $i; $index++) { $res = $element->getResultTable($issueId, $index, $r); if ($res != null) { echo "<td title='" . $element->getUserDateForTables2($issueId, $r, $index) . "'>\n\t\t\t\t\t\t\t\t<input style='margin:2px;' name='Table[{$id}][{$r}][{$index}]' type='text' value ='{$res}'><br>\n\t\t\t\t\t\t\t\t<div class='info' style='display: none; font-size:small; padding:3px; background-color:rgb(220,220,220); border-radius:4px'>" . $element->getUserDateForTables($issueId, $r, $index) . "</div></td>";