Hi guys, anyone can help me to solve my problem uploading excel file (xls, xlsx) with extension JPHPExcelReader. This is my table lba(id, campaign_id, campaign_name, target_location, status, customer, sender_id, start_periode, stop_periode, success, failed) and lba_upload[id, filename, request_by, date_created]. I couldn’t read data from uploaded file. This is my code in lba controller :
public function actionIndex()
{
Yii::import("application.extensions.phpexcelreader.JPhpExcelReader");
$model=new Excel;
$filename = date("YmdHis") . rand(101, 999) . rand(1, 9) . rand(1001, 9999) . '.xls';
if(isset($_POST['Excel']))
{
$model->attributes=$_POST['Excel'];
$model->filename = $filename;
$up=CUploadedFile::getInstance($model,'file');
$model->file = $up;
$path=Yii::app()->basePath.'/uploads/'.$filename.".xls";
$up->saveAs($path);
if ($model->save()) {
$data = new JPhpExcelReader($path);
$campaign_id=array();
$campaign_name=array();
$target_location=array();
$status=array();
$customer=array();
$sender_id=array();
$start_periode=array();
$stop_periode=array();
$success=array();
$failed=array();
for ($j = 2; $j <= $data->sheets[0]['numRows']; $j++)
{
if (empty($data->sheets[0]['cells'][$j][1]))
continue;
$campaign_id=$data->sheets[0]['cells'][$j][1];
$campaign_name=$data->sheets[0]['cells'][$j][2];
$target_location=$data->sheets[0]['cells'][$j][3];
$status=$data->sheets[0]['cells'][$j][4];
$customer=$data->sheets[0]['cells'][$j][5];
$sender_id=$data->sheets[0]['cells'][$j][6];
$start_periode=$data->sheets[0]['cells'][$j][7];
$stop_periode=$data->sheets[0]['cells'][$j][8];
$success=$data->sheets[0]['cells'][$j][9];
$failed=$data->sheets[0]['cells'][$j][10];
}
for($i=0;$i<count($id);$i++)
{
$model=new Lba;
$model->campaign_id=$campaign_id[$i];
$model->campaign_name=$campaign_name[$i];
$model->target_location=$target_location[$i];
$model->status=$status[$i];
$model->customer=$customer[$i];
$model->sender_id=$sender_id[$i];
$model->start_periode=$start_periode[$i];
$model->stop_periode=$stop_periode[$i];
$model->success=$success[$i];
$model->failed=$failed[$i];
$model->save();
}
//unlink($path);
Yii::app()->db->createCommand("INSERT INTO lba(campaign_id,campaign_name,target_location,status,customer,sender_id,start_periode,stop_periode,success, failed)
VALUES('$campaign_id','$campaign_name','$target_location','$status','$customer','$sender_id','$start_periode','$stop_periode','$success', '$failed') ")->execute();
}
$this->redirect(array('admin'));
}
$this->render('index',array('model'=>$model));
spl_autoload_register(array('YiiBase','autoload'));
}
Excel Model :
<?php
class Excel extends CActiveRecord
{
public $file;
public function beforeSave() {
$this->date_created = new CDbExpression('NOW()');
return parent::beforeSave();
}
public function tableName()
{
return 'lba_upload';
}
public function rules()
{
return array(
array('filename', 'required'),
array('filename', 'length', 'max'=>1000),
array('file','file','types'=>'xls'),
array('file','safe','on'=>'index'),
array('request_by', 'length', 'max'=>30),
array('id, filename, request_by, date_created', 'safe', 'on'=>'search'),
);
}
public function attributeLabels()
{
return array(
'id' => 'ID',
'filename' => 'Filename',
'request_by' => 'Request By',
'date_created' => 'Date Created',
);
}
public static function model($className=__CLASS__)
{
return parent::model($className);
}
}
?>
Form upload (index.php) :
<?php
/* @var $this LbaController */
/* @var $dataProvider CActiveDataProvider */
$this->breadcrumbs=array(
'LBA',
);
$this->menu=array(
//array('label'=>'Create Lba', 'url'=>array('create')),
array('label'=>'Manage LBA', 'url'=>array('admin')),
);
?>
<h1>Upload</h1>
<div class="form">
<?php $form=$this->beginWidget(‘CActiveForm’, array(
'id'=>'excel-form',
'enableAjaxValidation'=>false,
'htmlOptions'=>array('enctype'=>'multipart/form-data'),
)); ?>
<div class="row">
<b>Upload :</b>
<?php echo $form->fileField($model,'file'); ?>
<?php echo $form->error($model,'file'); ?>
</div>
<div class="row">
<?php echo $form->labelEx($model,'request_by'); ?>
<?php echo $form->textField($model,'request_by',array('size'=>30,'maxlength'=>30)); ?>
<?php echo $form->error($model,'request_by'); ?>
</div>
<div class="row">
<?php echo CHtml::submitButton($model->isNewRecord ? 'Upload' : 'Save'); ?>
</div>
<?php $this->endWidget(); ?>
</div>
View/admin.php :
<?php
/* @var $this LbaController */
/* @var $model Lba */
$this->breadcrumbs=array(
'LBA'=>array('admin'),
'Manage',
);
$this->menu=array(
//array('label'=>'List Lba', 'url'=>array('index')),
array('label'=>'Upload', 'url'=>array('index')),
);
Yii::app()->clientScript->registerScript(‘search’, "
$(’.search-button’).click(function(){
$('.search-form').toggle();
return false;
});
$(’.search-form form’).submit(function(){
$('#lba-grid').yiiGridView('update', {
data: $(this).serialize()
});
return false;
});
");
?>
<h1>LBA Report</h1>
<!–<?php echo CHtml::link(‘Advanced Search’,’#’,array(‘class’=>‘search-button’)); ?>–>
<div class="search-form" style="display:visible">
<?php $this->renderPartial(’_search’,array(
'model'=>$model,
)); ?>
</div><!-- search-form -->
<div><?php
$imgpdf=CHtml::image(‘images/pdf.png’, ‘pdf’);
echo CHtml::link($imgpdf, array(‘lba/pdf’, ‘id’=>$model->id), array(“target”=>"_blank"));?> <?php
$imgpdf=CHtml::image(‘images/excel.png’, ‘excel’);
echo CHtml::link($imgpdf, array(‘lba/excel’, ‘id’=>$model->id), array(“target”=>"_blank"));?> <?php
$imgpdf=CHtml::image(‘images/csv.png’, ‘csv’);
echo CHtml::link($imgpdf, array(‘lba/csv’, ‘id’=>$model->id), array(“target”=>"_blank"));?></div>
<?php $this->widget(‘zii.widgets.grid.CGridView’, array(
'id'=>'lba-grid',
'dataProvider'=>$model->search(),
'summaryText'=>'',
//'filter'=>$model,
'columns'=>array(
array(
'header'=>'No.',
'value'=>'$this->grid->dataProvider->pagination->currentPage * $this->grid->dataProvider->pagination->pageSize + ($row+1)',
),
//'campaign_id',
array(
'name'=>'campaign_name',
'htmlOptions'=>array('style'=>'font-weight:bold; width:250px'),
'value' => '$data->campaign_name',
'type' => 'raw',
),
//'target_location',
//'status',
//'customer',
'sender.sender_id',
'start_periode',
array(
'name'=>'stop_periode',
'footer'=>'Total : ',
'footerHtmlOptions'=>array(
'style'=>'text-align: right; padding-right: 5px; font-weight: bold'
),),
array(
'class'=>'ext.gridcolumns.TotalColumn',
'name'=>'success',
'htmlOptions'=>array('style'=>'font-weight:normal'),
'output'=>'Yii::app()->format->formatNumber($value)',
'footer'=>true,
//'footer'=>'Total : Rp. '.number_format($model->getTotals()),
'footerHtmlOptions'=>array(
'style'=>'text-align: right; padding-right: 5px; font-weight: bold'
),),
//'failed',
//'date_created',
/*array(
'header'=>'Action',
'class'=>'CButtonColumn',
),
*/
),
)); ?>
I want uploaded file’s data stored in table lba and viewed with Cgridview in admin.php. Anyone help??