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));
}
<?php
date_default_timezone_set('UTC');
require 'XLSXReader.php';
$xlsx = new XLSXReader('sample.xlsx');
$sheetNames = $xlsx->getSheetNames();
?>
<!DOCTYPE html>
<html>
<head>
<title>XLSXReader Sample</title>
<style>
body {
font-family: Helvetica, sans-serif;
font-size: 12px;
}
table, td {
border: 1px solid #000;
border-collapse: collapse;
padding: 2px 4px;
}
</style>
</head>
<body>
<h1>XLSXReader Examples</h1>
<h2>Sheet Names</h2>
<p>List of the sheets in this workbook (indexed by sheetId):</p>
public function import($list_value)
{
$added = 0;
$fail = 0;
$update = 0;
$tempReport = 1;
//ID инфоблка путём обрезки из выбронного поля списка
$IBlockId = str_replace('[', '', explode("]", $list_value)[0]);
$IBlock = $this->getBlockByID($IBlockId);
if (isset($_FILES['file']) && $_FILES['file']['size'] > 0) {
$fileSrc = $_FILES['file']['tmp_name'];
$xlsx = new \XLSXReader($fileSrc);
//Используем методы класса XLSXReader
$sheetNames = $xlsx->getSheetNames();
foreach ($sheetNames as $sheetName) {
$sheet = $xlsx->getSheet($sheetName);
$arSheetList[$sheetName] = $sheet->getData();
//Массив с ключами элементов
$arRowNames = $arSheetList[$sheetName][0];
foreach ($arSheetList[$sheetName] as $row) {
for ($i = 0; $i < count($row); $i++) {
$arTemp[$arRowNames[$i]] = $row[$i];
}
$arListRows[] = $arTemp;
}
unset($arListRows[0]);
//Удаляем массив с ключами
$arList[$sheetName] = $arListRows;
//Форматированный массив файла .xlsx
$arListRows = array();
//обнуляем массив для для случая с несколькими листами
}
$arResult['LIST'] = $arList;
$el = new \CIBlockElement();
?>
<div class="report_detail">
<button class="close_show_list" id="closeReportDetail" onclick="closeReportDetail();">X</button>
<?php
foreach ($arResult['LIST'] as $arItem) {
?>
<?php
foreach ($arItem as $item) {
if (!$item['CODE']) {
$arParams = array("replace_space" => "-", "replace_other" => "-");
$item['CODE'] = Cutil::translit($item['NAME'], "ru", $arParams);
}
global $USER;
$arLoadProductArray = array("MODIFIED_BY" => $USER->GetID(), "IBLOCK_SECTION_ID" => false, "IBLOCK_ID" => $IBlock['ID'], "NAME" => $item['NAME'], "CODE" => $item['CODE'], "ACTIVE" => $item['ACTIVE'], "PREVIEW_TEXT" => $item['PREVIEW_TEXT'], "DETAIL_TEXT" => $item['DETAIL_TEXT']);
$IBlockElement = $this->getBlockElementByCode($item['CODE']);
if ($PRODUCT_ID = $el->Add($arLoadProductArray)) {
echo $tempReport . ": Added: " . $item['CODE'] . "<br/>";
$added++;
$tempReport++;
} elseif (isset($IBlockElement)) {
if ($arLoadProductArray != array_intersect_key($IBlockElement, $arLoadProductArray)) {
$el->Update($IBlockElement['ID'], $arLoadProductArray);
echo $tempReport . ": Update: " . $IBlockElement['CODE'] . "<br/>";
$update++;
$tempReport++;
}
} else {
echo $tempReport . ": Error: " . $el->LAST_ERROR;
$fail++;
$tempReport++;
}
}
}
if ($tempReport == 1) {
echo "Пусто";
}
?>
</div>
<div class="report">Added: <?php
echo $added;
?>
; Updated: <?php
echo $update;
?>
; Fail: <?php
echo $fail;
?>
;</div>
<button class="show_logs">Show Logs</button><?php
} else {
echo "<span class='error_mes'><b>Файл не выбран!</b></span> ";
}
}
<?php
require 'XLSXReader/XLSXReader.php';
if (!empty($_FILES['file'])) {
$file = $_FILES['file'];
$fileSrc = $_FILES['file']['tmp_name'];
$xlsx = new XLSXReader($fileSrc);
$sheetNames = $xlsx->getSheetNames();
}
if (isset($sheetNames)) {
foreach ($sheetNames as $sheetName) {
$sheet = $xlsx->getSheet($sheetName);
$arSheetList[$sheetName] = $sheet->getData();
$arRowNames = $arSheetList[$sheetName][0];
foreach ($arSheetList[$sheetName] as $row) {
for ($i = 0; $i < count($row); $i++) {
$arTemp[$arRowNames[$i]] = $row[$i];
}
$arListRows[] = $arTemp;
}
unset($arListRows[0]);
//”дал¤ем массив с ключами
$arList[$sheetName] = $arListRows;
//‘орматированный массив файла .xlsx
$arListRows = array();
}
echo json_encode($arList);
}
