Can I provide a columns array in Table::create ?

Peter Wendel commented on Feb 23, 2022

Hello !

For formatting a table in a view it is possible to provide the columns-array to order and format it.

Ist it in Excel-Export also possible to do so?

Table::create([
            'dataSource' => $this->dataStore('ergebnis'),
            'columns' => [
        'atc_code' => [
            'label' => 'ATC-Code',
            'type' => 'string',
           
        ],
        'atc_bed' => [
            'label' => 'Wirkstoff',
            'type' => 'string',
          
        ],
        'anz_vo' => [
            'label' => 'Anzahl-VO',
            'type' => 'number',
           "decimals" => 0,
            "decPoint" => ",",
            "thousandSep" => ".",
        ],
]
        ]);

KoolReport commented on Feb 24, 2022

Do you mean you have an column with array type and would like to format and display it manually.

Peter Wendel commented on Feb 24, 2022

No. The result comes via report from database and was piped into DataStore. But due to some Join-Processes the columns are not in the desired order. So I want to order the columns as desired and some of them need special assistance, because ExcelExport formats an ID-Number wrong ( it has to be "7232107" instead of "7.232.107").

Sebastian Morales commented on Feb 24, 2022

Especially for excel export, pls change the column type to "string" instead of "number". That should make excel export to not format your column value. Rgds,

Peter Wendel commented on Feb 24, 2022

Okay. But let's return to my problem: Is it possible to order and format of the columns in ExcelExport/BigSpreadsheet like in a normal Table? If I do so, processing seems not to come to an end...

Sebastian Morales commented on Feb 24, 2022

If you don't define an excel Table's "columns" property then it automatically uses the datasource's column order. If you define excel Table's "columns" and the output excel has incorrect column order let us know. A screenshot of the output would be appreciated for better understanding of your problem. Tks,

Peter Wendel commented on Feb 24, 2022

So I gather from your comment, that I can provide format options to the export. Okay. So i'll have a look at it more patient and wait longer for the processing to end.

Sebastian Morales commented on Feb 24, 2022

I would suggest try to export with formatted columns with a limited number of data rows first to check the result faster. If it works you can run export with normal data. Rgds,

Peter Wendel commented on Feb 24, 2022

I'll normally agree you, but this report will be started from the user itself. Thefore it has to be fast. Without any options the test report needs 32 sec in BigSpreadsheet and 1min 30 in ExcelExport.

But I HAVE to set some format in it, because I work in Germany: We use ',' instead of '.' and Excel does it wrong... ;-)

Okay. Thanks so far. I have to rethink this feature and I'll make spme experiments to get a fast solution.

Sebastian Morales commented on Feb 24, 2022

If you meant decimal point and thousand separator in number format, when an excel file is opened, the number format is decided by users' Excel/Spreadsheet application locale and not by our export process or the file itself. But you can control the number format code with this property:

https://www.koolreport.com/docs/excel/excel_widgets/#table-widget-(version-%3E=-6.0.0)-excelformatcode-(version-%3E=-8.5.0)

Let us know if you have any question with the format. Tks,

Peter Wendel commented on Feb 24, 2022

Okay. So let's see if we'll get a solution. I select a result out of this table:

create table atcpzn_test
(
    jahr      smallint       not null,
    quartal   tinyint        not null,
    bsnr      int            not null,
    lanr      char(9)        not null,
    pzn       int unsigned   not null,
    atc_code  varchar(13)    not null,
    atc_bed   varchar(150)   not null,
    praepname varchar(100)   null,
    anz_vo    int    null,
    menge     int    null,
    ddd       decimal(15,3) null,
    vk        decimal(15,2)    null
);

For German data protection rules I cannot give you some example data.

The selected result may contain 500 or 15000 rows, maybe more.

The result is selected and piped via KoolReport to $this->dataStore('ergebnis')

The user has at minmum to work with the fields 'anz_vo', 'menge', 'ddd' and 'vk',so this fields have to have german number notation with ',' as decimal seperator when appearing in Excel.

For this reason, we'll use a template file containig a "Table::create" section like this:

use koolreport\excel\Table;

Table::create([
            'dataSource' => $this->dataStore('ergebnis'),
            'columns' => [ 
                ....
               ],
        ]);

Can you help me out with one example for an int-column , a decimal-column and a string-column?

So thanks for your time to help me... :)

Sebastian Morales commented on Feb 28, 2022

Hi, other than decimal point and thousand separator which are decided by users' Excel application you can control a column type of the number of decimals for numeric column like this:

//MyReportExce.view.php
use koolreport\excel\Table;

Table::create([
            'dataSource' => $this->dataStore('ergebnis'),
            'columns' => [ 
                "column_1" => [
                    "type" => "number",
                    "decimals" => 0, // for int column, show zero number after decimal point
                ],
                "column_2" => [
                    "type" => "number",
                    "decimals" => 2, // for real/float column, show 2 numbers after decimal point
                ],
                "column_3" => [
                    "type" => "string", // string column, do not format anything
                ],
               ],
        ]);

Pls let us know if you have any problem with these column settings. Rgds,

Peter Wendel commented on Feb 28, 2022

Hi again!

But I cannot transform the decimal point to ',' in this?

People getting this Report are calculating all day Excel-Sheets in German setup, no one wil change his local settings just for one Report who isn't able to come in correct settings...

Greets, Peter

Peter Wendel commented on Feb 28, 2022

So okay: Tested the above, but still no report build in above 2 Minutes.

Report without any column-settings put out in seconds.

That means definitly for the task I have to solve: Cannot use KoolReport here. Thanks for your help so far!

Peter Wendel commented on Mar 1, 2022

Hello !

I digged a little bit deeper in the dependecies of KoolReport and found phphoffice.

With

use koolreport\excel\Table;

\PhpOffice\PhpSpreadsheet\Shared\StringHelper::setDecimalSeparator(',');
\PhpOffice\PhpSpreadsheet\Shared\StringHelper::setThousandsSeparator('.');

Table::create([
    'datasource' => $this->dataStore('ergebnis')
]);

one can change the decimal point to an comma in a KoolReport-Excel-Export.

For getting completely formatted columns in time I'll test now generating the Excel directly in PhpSpreadsheet. Problem solved.

Maybe you can add this to the coming features of KoolReport.

Sebastian Morales commented on Mar 2, 2022

Thank you, Peter, for your suggestion. We will check this and improve decimal point and thousand separator for excel column format. Rgds,

KoolReport's Forum

Can I provide a columns array in Table::create ? #2590

Build Your Excellent Data Report