Please use this thread for any issue related to the tlbExcelView widget
Please use this thread for any issue related to the tlbExcelView widget
Hi,
My problem is still exist. I cannot display the ‘No’ column on excel although it can display perfectly on the gridview.
This is my code, view:
...
<?php
$columns = array(
array(
'header' => 'No',
'value' => '$this->grid->dataProvider->pagination->currentPage * $this->grid->dataProvider->pagination->pageSize + $row + 1', // row is zero based
'htmlOptions' => array('style' => 'text-align: center; width: 50px;'),
),
array(
'name' => 'date',
'type' => 'raw',
'value' => '$data->transaction->date',
'htmlOptions' => array('style' => 'text-align: center; width: 80px;'),
),
array(
'name' => 'transactionNo',
'type' => 'raw',
'value' => '$data->transaction->transactionNo',
'htmlOptions' => array('style' => 'text-align: center; width: 80px;'),
),
array(
'name' => 'supplier',
'type' => 'raw',
'value' => '$data->transaction->partnerFk == NULL ? "" : $data->transaction->partner->name',
),
array(
'name' => 'itemCode',
'type' => 'raw',
'value' => '$data->item->code',
'htmlOptions' => array('style' => 'width: 120px;'),
),
array(
'name' => 'itemName',
'value' => '$data->item->name',
),
array(
'name' => 'itemUnit',
'value' => '$data->item->unit',
'htmlOptions' => array('style' => 'width: 120px;'),
),
array(
'name' => 'quantity',
'value' => 'number_format($data->quantity, 0, ",", ".")',
'htmlOptions' => array('style' => 'width: 120px; text-align: right;'),
),
);
//if ($production == 'grid') {
// array_unshift($columns, array(
// 'header' => 'No',
// 'value' => '$this->grid->dataProvider->pagination->currentPage * $this->grid->dataProvider->pagination->pageSize + $row + 1', // row is zero based
// 'htmlOptions' => array('style' => 'text-align: center; width: 50px;'),
// )
// );
//}
$this->widget('application.components.widgets.tlbExcelView', array(
'id' => 'mutation-grid',
// 'type' => 'striped bordered',
'emptyText' => 'Data tidak tersedia!',
'summaryText' => 'Menampilkan baris {start}-{end} dari {count} baris - ' . CHtml::dropDownList('pageSize', $pageSize, array(5 => 5, 10 => 10, 25 => 25, 50 => 50, 100 => 100), array('class' => 'change-pageSize', 'style' => 'width: auto;')) . ' baris per halaman.',
'dataProvider' => $model->filterReport($merge),
'filter' => $model,
'enablePagination' => true,
'template' => "{pager}\n{summary}\n{items}\n{pager}",
'grid_mode' => $production, // Same usage as EExcelView v0.33
//'template' => "{summary}\n{items}\n{exportbuttons}\n{pager}",
'title' => 'Laporan penerimaan barang - ' . date('d-m-Y - H:i:s'),
'creator' => Yii::app()->user->name,
'subject' => 'Laporan penerimaan barang',
'description' => 'Laporan penerimaan barang',
'lastModifiedBy' => Yii::app()->user->name,
'sheetTitle' => 'Laporan penerimaan barang ',
'keywords' => '',
'category' => '',
'landscapeDisplay' => false, // Default: false
'A4' => true, // Default: false - ie : Letter (PHPExcel default)
'pageFooterText' => '&RHalaman &P dari &N', // Default: '&RPage &P of &N'
'automaticSum' => false, // Default: false
'decimalSeparator' => '.', // Default: '.'
'thousandsSeparator' => ',', // Default: ','
//'displayZeros' => false,
//'zeroPlaceholder' => '-',
// 'sumLabel' => 'Grand total:', // Default: 'Totals'
// 'borderColor' => '00FF00', // Default: '000000'
// 'bgColor' => 'FFFF00', // Default: 'FFFFFF'
// 'textColor' => 'FF0000', // Default: '000000'
// 'rowHeight' => 45, // Default: 15
// 'headerBorderColor' => 'FF0000', // Default: '000000'
// 'headerBgColor' => 'CCCCCC', // Default: 'CCCCCC'
// 'headerTextColor' => '0000FF', // Default: '000000'
// 'headerHeight' => 10, // Default: 20
// 'footerBorderColor' => '0000FF', // Default: '000000'
// 'footerBgColor' => '00FFCC', // Default: 'FFFFCC'
// 'footerTextColor' => 'FF00FF', // Default: '0000FF'
// 'footerHeight' => 50, // Default: 20
'columns' => $columns,
));
?>
...
code on the model:
.../**
* Retrieves a list of models based on the current search/filter conditions.
* @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
*/
public function filterReport($merge = null) {
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria = new CDbCriteria;
$criteria->with = array('item', 'transaction');
$criteria->compare('transaction.date', $this->date);
$criteria->compare('transaction.transactionNo', $this->transactionNo);
if ($this->supplier != null || $this->supplier != '') {
$criteria->compare('transaction.partnerFk', Partner::model()->find('name=:name', array(':name' => $this->supplier))->id, true);
}
$criteria->compare('item.code', $this->itemCode);
$criteria->compare('item.name', $this->itemName);
$criteria->compare('item.unit', $this->itemUnit);
$criteria->compare('quantity', $this->quantity, true);
if ($merge != null) {
$criteria->mergeWith($merge);
}
if ($this->fromDate != '') {
$criteria->compare('transaction.date', '>=' . $this->fromDate);
}
if ($this->toDate != '') {
$criteria->compare('transaction.date', '<=' . $this->toDate);
}
$sort = new CSort;
$sort->defaultOrder = 'item.name ASC';
$sort->attributes = array(
'date' => array(
'asc' => 'transaction.date ASC',
'desc' => 'transaction.date DESC',
),
'transactionNo' => array(
'asc' => 'transaction.transactionNo ASC',
'desc' => 'transaction.transactionNo DESC',
),
'supplier' => array(
'asc' => 'transaction.partnerFk ASC',
'desc' => 'transaction.code DESC',
),
'itemCode' => array(
'asc' => 'item.code ASC',
'desc' => 'item.code DESC',
),
'itemName' => array(
'asc' => 'item.name ASC',
'desc' => 'item.name DESC',
),
'itemUnit' => array(
'asc' => 'item.unit ASC',
'desc' => 'item.unit DESC',
),
'quantity' => 'quantity',
);
$sort->applyOrder($criteria);
return new CActiveDataProvider($this, array(
'criteria' => $criteria,
'sort' => $sort,
'pagination' => array(
'pageSize' => Yii::app()->user->getState('TransactionDetail_pageSize', Yii::app()->params['defaultPageSize']),
'currentPage' => Yii::app()->user->getState('TransactionDetail_page', 0),
),
));
}
...
and code on the controller:
...
/**
* Manages all models.
*/
public function actionReceiveReport() {
$model = new TransactionDetail('search');
$merge = new CDbCriteria;
$merge->condition = 'transaction.typeFk = :typeFk AND t.transactionFk = transaction.id AND t.itemFk = item.id';
$merge->params = array(':typeFk' => $this->getTypeId());
if (intval(Yii::app()->request->getParam('clearFilters')) == 1) {
Yii::app()->user->setState('TransactionDetail_page', null);
unset($_GET['TransactionDetail_page']);
Yii::app()->user->setState('TransactionDetail_sort', null);
unset($_GET['sort']);
EButtonColumnWithClearFilters::clearFilters($this, $model); //where $this is the controller
}
$pageSize = Yii::app()->user->getState('TransactionDetail_pageSize', Yii::app()->params['defaultPageSize']);
if (isset($_GET['pageSize'])) {
$pageSize = (int) $_GET['pageSize'];
Yii::app()->user->setState('TransactionDetail_pageSize', $pageSize);
unset($_GET['pageSize']);
}
if (isset($_GET['TransactionDetail_page'])) {
$transactionPage = (int) $_GET['TransactionDetail_page'] - 1;
Yii::app()->user->setState('TransactionDetail_page', $transactionPage);
unset($_GET['TransactionDetail_page']);
} else if (isset($_GET['ajax'])) {
Yii::app()->user->setState('TransactionDetail_page', 0);
}
if (isset($_GET['sort'])) {
$transactionSort = $_GET['sort'];
Yii::app()->user->setState('TransactionDetail_sort', $transactionSort);
} else if (Yii::app()->user->hasState('TransactionDetail_sort')) {
$_GET['sort'] = Yii::app()->user->getState('TransactionDetail_sort');
}
if (isset($_GET['export'])) {
$production = 'export';
} else {
$production = 'grid';
}
$this->render('receiveReport', array(
'model' => $model,
'merge' => $merge,
'pageSize' => $pageSize,
'production' => $production,
));
}
...
Could you help me with this?
Thanks a lot.
Daniel
Hi bennouna,
In addition, how could I export the result to PDF? is there any additional setup?
Cheers,
Daniel
Hi Daniel
I haven’t had time yet to see your issue. Let me get back to you later.
In order to export to pdf, you have to add to your widget call:
'exportType' => 'PDF', // default is Excel5, which generates .xls Excel workbooks
Hi bennouna,
How to make the footer sum works? I have a problem to display it, it always return =TRUE().
I tried to export to PDF, but gave me this error message:
PDF Rendering library has not been defined.
Cheers,
Daniel
Hi Benouna,
How to set the jquery in view?
I have error
Here’s my code in view
Yii::app()->clientScript->registerScript('search', "
$('#exportToExcel').click(function(){
window.location = '". $this->createUrl('bonus') . "?' + $(this).parents('form').serialize() + '&export=true';
return false;
});
$('.search-form form').submit(function(){
$.fn.yiiGridView.update('ledgers-detail-grid', {
data: $(this).serialize()
});
return false;
});
");
Thanks b4…
Well it seems from your other post on the extension page that you are using standard urls.
I guess you should replace that line
window.location = '". $this->createUrl('bonus') . "?' + $(this).parents('form').serialize() + '&export=true';
with
window.location = '". $this->createUrl('Ledgers_detail/bonus') . "&' + $(this).parents('form').serialize() + '&export=true';
Daniel, it seems you have to download a 3rd-party library to PDF with PHPExcel, which is logical imho. Please see this thread.
See also this post from PHPExcel author Mark Baker.
I’m not sure what may cause this issue. Can you paste the code where you call the widget?
Thanks Benouna, it works well…
I have another problem using this extension…
When export to excel, give me this error
when executing this line
array('header'=>'No.',
'value'=>'$this->grid->dataProvider->pagination->currentPage*$this->grid->dataProvider->pagination->pageSize + $row+1',
),
can you help me on this?
Thanks b4…
Have you enabled pagination in the widget call? Can you post the relevant parts of your view code?
Here’s my view code
$this->widget('application.components.widgets.tlbExcelView', array(
'id' => 'ledgers-detail-grid',
'dataProvider' => $model->search(),
'grid_mode' => $production, // Same usage as EExcelView v0.33
'title' => 'Bonus - ' . date('d-m-Y - H-i-s'),
'creator' => 'KUP',
'description' => mb_convert_encoding('Etat de production généré à la demande par l\'administrateur (some text in French).', 'ISO-8859-1', 'UTF-8'),
'lastModifiedBy' => 'KUP',
'sheetTitle' => 'Bonus' . date('m-d-Y H-i'),
'keywords' => '',
'category' => '',
'landscapeDisplay' => true, // Default: false
'A4' => false, // Default: false - ie : Letter (PHPExcel default)
'pageFooterText' => '&RThis is page no. &P of &N pages', // Default: '&RPage &P of &N'
'automaticSum' => false, // Default: false
'decimalSeparator' => ',', // Default: '.'
'thousandsSeparator' => '.', // Default: ','
'sumLabel' => 'Total:', // Default: 'Totals'
'borderColor' => '00FF00', // Default: '000000'
'bgColor' => 'FFFF00', // Default: 'FFFFFF'
'textColor' => 'FF0000', // Default: '000000'
'rowHeight' => 15, // Default: 15
'headerBorderColor' => 'FF0000', // Default: '000000'
'headerBgColor' => 'CCCCCC', // Default: 'CCCCCC'
'headerTextColor' => '0000FF', // Default: '000000'
'headerHeight' => 20, // Default: 20
'footerBorderColor' => '0000FF', // Default: '000000'
'footerBgColor' => '00FFCC', // Default: 'FFFFCC'
'footerTextColor' => 'FF00FF', // Default: '0000FF'
'footerHeight' => 20, // Default: 20
'columns' => array(
array('header'=>'No.',
'value'=>'$this->grid->dataProvider->pagination->currentPage*$this->grid->dataProvider->pagination->pageSize + $row+1',),
array('name'=>'tanggal_search','value'=>'date("d-m-Y",strtotime($data->ledgers->tanggal))'),
array('header'=>'WIL','value'=>'$data->ledgers->company_->wilayah_->kode_wilayah'),
array('header'=>'PJ','value'=>'$data->ledgers->pj_->PJ_id'),
array('header'=>'Kode DS','value'=>'$data->ledgers->company_->company_id'),
array('name'=>'faktur_search','value'=>'$data->ledgers->faktur'),
array('header'=>'Nama','value'=>'$data->ledgers->company_->company_name'),
array('header'=>'Alamat','value'=>'$data->ledgers->company_->company_address'),
),
));
@Wiwit Iwan SEP
It’s clear that you haven’t enabled pagination.
Please:
[list=1]
[*]Set the [font="Courier New"]enablePagination[/font] property to [font="Courier New"][color="#2E8B57"]true[/color][/font] in your tlbExcelView widget call (the default value is [color="#FF0000"][font="Courier New"]false[/font][/color])
[*]Make sure that you actually have a CPagination component attached to your dataprovider in your model’s search() method.
[/list]
Thanks for this extension, it’s been a great help.
I just wanted to point out something I found while using it …
On line 449 of tlbExcelView.php, you hard code a zoom value which could confuse a lot of people (including myself)
->getSheetView()->setZoomScale(50);
This would probably be better set as an option that defaults to 100.
Thanks again.
Thanks, a great share of credit should go to phreak for the original extension.
Anyway, for the zoom scale, you’re probably right, it should be at most an option that could be set in the widget.
Hi bennouna,
Did exactly as you described in tlbExcelView widget page
I cant export to excel when clicking on "Export to Excel" button.
After clicking this button I get only texts from admin view without any theme.
this is my Controller code
public function actionAdmin() {
$model = new Depocem('search');
$model->unsetAttributes();
if (isset($_GET['Depocem'])) {
$model->attributes = $_GET['Depocem'];
}
if (isset($_GET['export'])) {
$production = 'export';
} else {
$production = 'grid';
}
$this->render('admin', array('model' => $model, 'production' => $production));
}
this is my admin code
$('#exportToExcel').click(function(){
window.location = '". $this->createUrl('admin') . "?' + $(this).parents('form').serialize() + '&export=true';
return false;
});
.....
$this->widget('application.components.widgets.tlbExcelView', array(
'id' => 'depocem-grid',
'dataProvider' => $model->search(),
'filter' => $model,
'grid_mode' => $production, // Same usage as EExcelView v0.33
//'template' => "{summary}\n{items}\n{exportbuttons}\n{pager}",
'title' => 'Some title - ' . date('d-m-Y - H-i-s'),
'creator' => 'Your Name',
'lastModifiedBy' => 'Some Name',
'sheetTitle' => 'Report on ' . date('m-d-Y H-i'),
'keywords' => '',
'category' => '',
'landscapeDisplay' => true, // Default: false
'A4' => true, // Default: false - ie : Letter (PHPExcel default)
'pageFooterText' => '&RThis is page no. &P of &N pages', // Default: '&RPage &P of &N'
'automaticSum' => true, // Default: false
'decimalSeparator' => ',', // Default: '.'
'thousandsSeparator' => '.', // Default: ','
//'displayZeros' => false,
//'zeroPlaceholder' => '-',
'sumLabel' => 'Column totals:', // Default: 'Totals'
'borderColor' => '00FF00', // Default: '000000'
'bgColor' => 'FFFF00', // Default: 'FFFFFF'
'textColor' => 'FF0000', // Default: '000000'
'rowHeight' => 45, // Default: 15
'headerBorderColor' => 'FF0000', // Default: '000000'
'headerBgColor' => 'CCCCCC', // Default: 'CCCCCC'
'headerTextColor' => '0000FF', // Default: '000000'
'headerHeight' => 10, // Default: 20
'footerBorderColor' => '0000FF', // Default: '000000'
'footerBgColor' => '00FFCC', // Default: 'FFFFCC'
'footerTextColor' => 'FF00FF', // Default: '0000FF'
'footerHeight' => 50, // Default: 20
'columns' => array(
'id',
'malAd',
'miqdarCem',
'qiymetSon',
array('name'=>'olcuVahidi','header'=>'Ölçü vahidi'),
array('name'=>'novId', 'header'=>'Növ', 'value'=>'$data->malzemenov->nov', 'filter' => $novFilter),//'novId',
array(
'class'=>'CButtonColumn',
'template'=>'{view}{update}',
),
) // an array of your CGridColumns
));
and this is _search code
<div class="row buttons">
<?php echo CHtml::submitButton('Search'); ?>
<?php echo CHtml::button(Yii::t('app', 'Export to Excel (xls)'), array('id' => 'exportToExcel')); ?>
</div>
There is no any error messages too.
Hi Atilla,
Ok, I don’t see exactly what is going on, so please answer these:
[list=1]
[*]Before clicking on the Export button, can you see the grid correctly?
[*]If the first answer is yes : How many records do you attempt to export?
[/list]
Yes, I can see grid correctly.
And I attempted to export at least 4 records,
I am only testing project, there is not so many data in tables.
Ok, what does your url look like when your first load the grid?
At first load my URL is:
http://localhost/depo/depocem/admin
And this is url after clicking on Export button
admin?Depocem%5Bid%5D=&Depocem%5BnovId%5D=&Depocem%5BmalAd%5D=&Depocem%5BmiqdarCem%5D=&Depocem%5BqiymetSon%5D=&export=true