Guriddo » Topic: Excel export

Excel export

Tagged: excel, export, number format

This topic has 15 replies, 4 voices, and was last updated 8 years, 3 months ago by sko.

Creator

Topic
August 19, 2016 at 1:00 pm #125467

rapantti
Participant

Hi,

How can I define cell type for columns in excel export? Now every column is in excel’s ‘General’ format.
Creator

Topic

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

Author

Replies

August 22, 2016 at 12:01 pm #125473

guriddo.support

Participant

Hello,

The type of column is detected automatically from the script (based on the database table properties). There was some small problems with numeric type in true excel export.

Which Guriddo PHP version is used and which type of excel export is used?

It is possible to send us a script and a demo table definition demonstrating the problem?

Thank you.

Kind Regards

Will

Guriddo Support Team

August 22, 2016 at 12:25 pm #125477

rapantti

Participant

Hi,

We’re using version 5.0.0 and I’ve tried with Excel5, Excel2007 and XML. XML works fine with numbers but it throws following error: “The file format differs from the format that the file name extension specifies” so it’s no good for us. Only real solution would be export in genuine excel -format. I’m not sure if demo helps because it’s pretty simple export with few text / number fields.

And one more question. I created a colModel for jqGrid. It contains following fields:

        array(
            'name'     => XXXX::FIELD_ACCOUNT_,
            'classes'   => '',
            'editable'  => false,
            'align'     => 'left',
            'hidden'    => false,
            'hidedlg'   => false,
            'label'     => '',
            'sorttype'  => 'number',
            'formatter' => 'numeric',
            'width'     => 250,
        ),

array(

'name' => XXXX::FIELD_ACCOUNT_,

'classes' => '',

'editable' => false,

'align' => 'left',

'hidden' => false,

'hidedlg' => false,

'label' => '',

'sorttype' => 'number',

'formatter' => 'numeric',

'width' => 250,

So does the ‘formatter’ -field have any influence on export cell types?

August 22, 2016 at 2:30 pm #125479

guriddo.support

Participant

Hello,

Can you please try our demo download with 5.1.1 and see if the problem is fixed for you?

Unfortunately the formatter does not apply to excel (it is a client side solution) export. The formating depend on current (locale) excel settings for numbers and integers.

Kind Regards,

Will

Guriddo Support Team

August 22, 2016 at 3:05 pm #125481

rapantti

Participant

Hi,

I tried it and it didn’t work as expected. Every cell is still in general -format.

Sample data:


array(
                array (
                    'AccountName' => 'Example Account #1',
                    'ProjectName' => 'Amigurumivirkkaus',
                    'SubprojectName' => 'Virkkausprosessi',
                    'SubprojectEndDate' => '29.10.2014',
                    'EstimatedCost' => '100.00',
                    'ResourcedInvoicing' => '200.00',
                ),
                array (
                    'AccountName' => 'Example Account #2',
                    'ProjectName' => '1036',
                    'SubprojectName' => 'awsdf',
                    'SubprojectEndDate' => '14.08.2015',
                    'EstimatedCost' => '200.00',
                    'ResourcedInvoicing' => '300.00',
                ),
);

array(

array (

'AccountName' => 'Example Account #1',

'ProjectName' => 'Amigurumivirkkaus',

'SubprojectName' => 'Virkkausprosessi',

'SubprojectEndDate' => '29.10.2014',

'EstimatedCost' => '100.00',

'ResourcedInvoicing' => '200.00',

array (

'AccountName' => 'Example Account #2',

'ProjectName' => '1036',

'SubprojectName' => 'awsdf',

'SubprojectEndDate' => '14.08.2015',

'EstimatedCost' => '200.00',

'ResourcedInvoicing' => '300.00',

);

Excel options:


array(
            "file_type"  => "Excel5",
            "start_cell" => "A1",
            "font"       => "Calibri",
            "font_size"  => 11,
            "protect"    => false,
            "creator"    => "xxx",
            "author"     => "xxx",
);

array(

"file_type" => "Excel5",

"start_cell" => "A1",

"font" => "Calibri",

"font_size" => 11,

"protect" => false,

"creator" => "xxx",

"author" => "xxx",

);

Extracted from our controller:


        $conn = new \jqGridArray();
        $grid = new \jqGridRender($conn);
        
        // Data is it in $GLOBALS[Export::EXPORT_KEY]
        $grid->ExportCommand = "SELECT  * FROM " . Export::EXPORT_KEY;
        $grid->setExcelOptions($this->getDefaultExcelOptions());
        $grid->exportfile  = $this->getExportFileName();
        $grid->gSQLMaxRows = Export::EXPORT_MAX_ROWS; // Currently 100000 rows
        $grid->exportToExcel();

$conn = new \jqGridArray();

$grid = new \jqGridRender($conn);

// Data is it in $GLOBALS[Export::EXPORT_KEY]

$grid->ExportCommand = "SELECT * FROM " . Export::EXPORT_KEY;

$grid->setExcelOptions($this->getDefaultExcelOptions());

$grid->exportfile = $this->getExportFileName();

$grid->gSQLMaxRows = Export::EXPORT_MAX_ROWS; // Currently 100000 rows

$grid->exportToExcel();

So basically it’s pretty simple. I’ve tried with $grid->setColModel($colModel) and it doesn’t make any difference except pdf exports will break if I don’t define it. I tried to check your php code but it’s obfuscated so it’s impossible. Any help will be appreciated.

August 22, 2016 at 4:25 pm #125484

guriddo.support

Participant

hello,

The problem is the use of array driver and data. In case of number try to set the values as numbers and not as strings something like:

array(
                array (
                    'AccountName' => 'Example Account #1',
                    'ProjectName' => 'Amigurumivirkkaus',
                    'SubprojectName' => 'Virkkausprosessi',
                    'SubprojectEndDate' => '29.10.2014',
                    'EstimatedCost' => 100.00,
                    'ResourcedInvoicing' => 200.00,
                ),
                array (
                    'AccountName' => 'Example Account #2',
                    'ProjectName' => '1036',
                    'SubprojectName' => 'awsdf',
                    'SubprojectEndDate' => '14.08.2015',
                    'EstimatedCost' => 200.00,
                    'ResourcedInvoicing' => 300.00,
                ),
);

array(

array (

'AccountName' => 'Example Account #1',

'ProjectName' => 'Amigurumivirkkaus',

'SubprojectName' => 'Virkkausprosessi',

'SubprojectEndDate' => '29.10.2014',

'EstimatedCost' => 100.00,

'ResourcedInvoicing' => 200.00,

array (

'AccountName' => 'Example Account #2',

'ProjectName' => '1036',

'SubprojectName' => 'awsdf',

'SubprojectEndDate' => '14.08.2015',

'EstimatedCost' => 200.00,

'ResourcedInvoicing' => 300.00,

);

Regards
Will

Guriddo Support Team

August 22, 2016 at 4:35 pm #125486

rapantti

Participant

I’ll try tomorrow. I’ve also tested values as float (not strings) but as I remember it didn’t work. So the problem is with the array driver and not the data set or should it work with array driver? Have you documented this somewhere?

August 22, 2016 at 5:05 pm #125487

guriddo.support

Participant

Hello,

Using the last version it work as expected. See here

Kind Regards,

Will

Guriddo Support Team

August 23, 2016 at 7:50 am #125492

rapantti

Participant

Hi,

I tried it and no difference. Still “number” cells are in general format. So I can’t show them in two decimal format. 100.00 will be 100. Yeah sure they are aligned to right. Same happens in your example.

August 23, 2016 at 10:54 am #125493

guriddo.support

Participant

Hello,

We will investigate the problem. Seems the problem is in PHPExcel lib. Will replay ASAP.

Thank you

Kind Regards,

Will

Guriddo Support Team

August 24, 2016 at 12:27 pm #125496

guriddo.support

Participant

Hello,

We have investigate the problem deeper. It is a PHPExcel lib default behavior instead that we set explicit the field as number in the code – the field is created as General Number without formatting.

To fix this we have made a decision to set default number formatting (in excel options) and in case there is a need of custom one we will include a special excel formatting in formatoptions array for a particular field.

This all will be available in the next upcoming release October, 2016.

Kind Regards,

Will

Guriddo Support Team

November 29, 2016 at 9:06 am #125708

sko

Participant

Hello,

we have jqGrid PHP 5.0.0 version and our licence only covers minor updates. Sadly new version, in which this is fixed, is major update and therefore we don’t have access to it.

Isn’t this bug a kind of a bug that should be fixed to older versions too? Since this has been working before and you have decided to use 3rd party library which has this behavior. Now exports doesn’t look like they should and it reduces user experiance significantly.

November 30, 2016 at 10:48 am #125709

tony

Keymaster

Hello,

I understand your point and agree with you.
Please write to the support in order to get the fixed update.

Kind Regards,

January 3, 2017 at 1:19 pm #125746

sko

Participant

Hello,

we now have newest version 5.2.0 but we’re still having problems with exports as rapantti stated earlier.

Can you offer example how to work with number fields when exporting to Excel? Earlier Will wrote:

To fix this we have made a decision to set default number formatting (in excel options) and in case there is a need of custom one we will include a special excel formatting in formatoptions array for a particular field.

How is this done, we’ve tried setting formatter to number (which should affect to grid only) and that didn’t do any good.

Does header row somehow affect to column format, if header is text should that set the whole column to string and if so, how can that be changed

Thanks
Seppo

January 3, 2017 at 1:26 pm #125747

guriddo.support

Participant

Hello,

Can you please set for us a small example with data of course demonstrating what you want to achieve – i.e what you expect and what is happen?

Thank you

Kind Regards,
Will

Guriddo Support Team

January 3, 2017 at 1:36 pm #125749

sko

Participant

Data

array (
‘Invoicing’ => 1000.01,
‘TargetPricePerHour’ => 1000.01,
)

colmodel

array (
0 =>
array (
‘name’ => ‘Invoicing’,
‘label’ => ‘Laskutus’,
‘formatter’ =>
array (
‘decimalSeparator’ => ‘.’,
‘thousandsSeparator’ => ‘ ‘,
‘decimalPlaces’ => 2,
‘defaultValue’ => ‘0.00’,
),
‘align’ => ‘right’,
‘sorttype’ => ‘numeric’,
),
1 =>
array (
‘name’ => ‘TargetPricePerHour’,
‘label’ => ‘Tavoitehinta / h’,
‘formatter’ =>
array (
‘decimalSeparator’ => ‘.’,
‘thousandsSeparator’ => ‘ ‘,
‘decimalPlaces’ => 2,
‘defaultValue’ => ‘0.00’,
),
‘align’ => ‘right’,
‘sorttype’ => ‘numeric’,
)

Excel options

“file_type”=>”Excel2007”,
“font”=>”Arial”,
“font_size”=>10,
“path_to_phpexcel_class”=>”External/phpexcel/PHPExcel.php”

Code

$conn = new \jqGridArray();

$grid = new \jqGridRender($conn);

$conn = new \jqGridArray();

$grid = new \jqGridRender($conn);

$grid->setColModel($this->getColModel());

$grid->setExcelOptions([as mentioned above]);

$grid->setColModel($this->getColModel());

$grid->setExcelOptions([as mentioned above]);

$grid->renderGrid('#grid', '#pager', true, null, null, true, true);

1	$grid->renderGrid('#grid', '#pager', true, null, null, true, true);

Expecting

Excel with Invoicing and TargetPricePerHour cells to be number with 2 decimals and thousand separator space

Getting

Excel with Invoicing and TargetPricePerHour cells being general format

This reply was modified 8 years, 3 months ago by sko.

Author

Replies

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

You must be logged in to reply to this topic.

Registered Users: 12,254
Forums: 7
Topics: 10,950
Replies: 23,150
Topic Tags: 324
Empty Topic Tags: 9

36 Users Online

Users: 19 Guests, 17 Bots

Stay connected with us in your favorite flavor!

Excel export

Search Forum

Recent Replies

Forum Statistics

UserOnline