Guriddo » Topic: export to excel

export to excel

Guriddo
export to excel

This topic has 8 replies, 2 voices, and was last updated 7 years, 8 months ago by gskidds.

Creator

Topic
August 3, 2018 at 3:19 pm #126930

gskidds
Participant

trying to use the new options: “formatoptions”=>array(“isExported”=>true,”exporttype”=>”excel”, for a currency column, not working, column in excel is still general numeric, what am I doing wrong?
Creator

Topic

Viewing 8 replies - 1 through 8 (of 8 total)

Author

Replies

August 3, 2018 at 5:15 pm #126931

gskidds

Participant

Sorry, here is the full line of code in the PHP

$grid->setColProperty(‘REV_TOT’, array(“label”=>”Revenue”, “formatter”=>”currency”,”formatoptions”=>array(“isExported”=>true,”exporttype”=>”excel”,”decimalPlaces”=>2,”thousandsSeparator”,”,”),”align”=>”right”));

August 6, 2018 at 2:21 pm #126933

guriddo.support

Participant

Hello,

Sorry for the late answer and the property is described not correct in the presentation.

First of all the options isExported and exporttype are passed as parameters in the formatter and not into the formatoptions.

Second which is needed to be note is that these are valid only when the datatype is local. This is described well in the JavaScript documentation here and here.

Below is exmple of using these options in PHP

$grid-&gt;setGridOptions(array(
   ...
   &quot;datatype&quot;=&gt;&quot;local&quot;,
   ...
));
$grid-&gt;setColProperty(&quot;OrderID&quot;,  array(
   &quot;label&quot;=&gt;&quot;ID&quot;, 
   &quot;width&quot;=&gt;60,
   &quot;formatter&quot;=&gt;&quot;js:&quot;.&quot;function( value, options) {
       if( options.isExported) { 
          if(options.exporttype === &#039;pdf&#039;) {
              return &#039;(&#039; + value + &#039;)&#039;;
          }
       }
       return value
   }&quot;
));

$grid->setGridOptions(array(

...

"datatype"=>"local",

...

));

$grid->setColProperty("OrderID", array(

"label"=>"ID",

"width"=>60,

"formatter"=>"js:"."function( value, options) {

if( options.isExported) {

if(options.exporttype === 'pdf') {

return '(' + value + ')';

}

return value

));

I need to note again that the datatype should be set local this to work.

The same can be done in PHP.

To set a global formatting in excel for integers use

...
$grid-&gt;setExcelOptions( array( 
  &quot;format_int&quot;=&gt;&quot;#0&quot;
));

...

$grid->setExcelOptions( array(

"format_int"=>"#0"

));

To set a particular formatting for certain row use excel_format

...
$grid-&gt;setColProperty(‘myfield’, array( &quot;formatoptions&quot; =&gt; array( &quot;excel_format&quot; =&gt; &quot;#.0&quot;)));

1 2	... $grid->setColProperty(‘myfield’, array( "formatoptions" => array( "excel_format" => "#.0")));

More on this can be read here

Please let us know if you need more assistance on this or explain us in detail what is need to be formatted and how

Kind Regards,
Will

Guriddo Support Team

August 6, 2018 at 4:38 pm #126934

gskidds

Participant

Thank You Will, looks like it works now, the only change I had to correct was changing options.exporttype === ‘pdf’ to options.exporttype === ‘excel’

I will know Wednesday when my customer tries it and tells me it is good (or yells at me and says ‘wtf’ it still isn’t what I want LOL.

gordon.

August 8, 2018 at 7:20 pm #126935

gskidds

Participant

Will,

It work now but the customer wants to freeze the first two rows when outputting to excel. I could not find anyway to do it via the parameters, so I did a temporary fix for it by modifying jqGridExport.php with the line:

$objWorksheet = $objPHPExcel->getActiveSheet()->freezePane(‘B3’); //gws 20180808

following:

$objWorksheet = $objPHPExcel->getActiveSheet();

Is there a way to do it via the export parameters? Or will that be available in a future release?

Thanks.

Gordon.

August 9, 2018 at 3:14 pm #126936

guriddo.support

Participant

Hello Gordon,

Thank you for the feedback.

I have a impression that you use local export and now you tell us about server side export.

At the moment it is not possible to manipulate the excel export in a way you do it, but this is a good idea and we will consider this in our future release.

In all cases I will let you know about the result.

Thank you for the recommendation

Kind Regards,
Will

Guriddo Support Team

August 16, 2018 at 4:05 pm #126939

gskidds

Participant

You are welcome,

Just an FYI: when I freeze the first 2 rows like that, excel sizes the first column to the length of the Header row information. To avoid that you can add:

$objWorksheet = $objPHPExcel->getActiveSheet()->mergeCells(‘A1:Z1’);

after the freeze statement:

$objWorksheet = $objPHPExcel->getActiveSheet()->freezePane(‘B3’);

Column 1 will then size correctly.

Regards,

Gordon.

August 20, 2018 at 11:19 am #126943

guriddo.support

Participant

Hello Gordon,

First of all I apologize for my mistake and that I have missed a existing feature in the Guriddo Suito PHP.

Using the existing code you can do this very easy without to modify the source code.

Here is how you can achieve this.

There is a option called excelFunc, which you can call before rendering the grid. Here is the code:

...
$grid-&gt;excelFunc = &quot;myExcel&quot;;
function myExcel( $objPHPExcel ) {
    $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells(&#039;A1:Z1&#039;);
    $objPHPExcel-&gt;getActiveSheet()-&gt;freezePane(&#039;B3&#039;);
    // return the modified
    return $objPHPExcel;
}
....
$grid-&gt;renderGrid(...);

...

$grid->excelFunc = "myExcel";

function myExcel( $objPHPExcel ) {

$objPHPExcel->getActiveSheet()->mergeCells('A1:Z1');

$objPHPExcel->getActiveSheet()->freezePane('B3');

// return the modified

return $objPHPExcel;

}

....

$grid->renderGrid(...);

Again sorry for my oversight.

If you have any problem with this, please let me know.

Kind Regards
Will

Guriddo Support Team

August 22, 2018 at 4:03 pm #126944

export to excel

Search Forum

Recent Replies

Forum Statistics

UserOnline