Hi all,

I am using DataTables with Export to excell button. My column configuration is:

                "Iznos"=>array(
                        "label"=>"Iznos",
                        "type"=>"number",
                        "footer"=>"sum",
                        "decimals"=>2,
                        "dec_point"=>",",
			"thousand_sep"=>".",
                        "formatValue"=>function($value,$row){
                            $pad = str_pad(number_format($value,0,"",""),11,"0",STR_PAD_LEFT);
                            return number_format($value,2,",",".");
                        },
                    )

This gives nice looking format for price, like "8,00" as example. But when I export that to excel, I get "400" as value. And if number is "18,00", excel will show it as "1,800".

Is there any way to sort this out and have same values in excel as well?

Thanks

Hi Sebastian, I am using filter (like search field) to filter results in table and than would love to export them. That is why I need to use Buttons to export data which is filtered after query.

   "buttons" => [
                        'csv', 'excel', 'pdf', 'print'
                    ],

I am not familiar with Map and idea you suggested. Can I do that with this filtered data?

One of possible solutions I have found here: https://datatables.net/forums/discussion/46207/export-to-excel-and-format-a-numeric-column-as-currency

And example below, which adds "$" sign, converting value in excel column to currency.

buttons: [
    {
     extend: 'excelHtml5',
     customize: function( xlsx, row ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
             $('row c[r^="G"], row c[r^="H"]', sheet).attr( 's', 57);
     }
}]

This example is for JS and I could not make it fit to style used in yours plugin. I have changed my code to this below but it does not work really:

"buttons" => [
                        "extend" => 'excelHtml5',
                        'csv', 'excel', 'pdf', 'print',
                        "customize"=>function($xlsx, $row, $value){
                              $sheet = xlsx.xl.worksheets['sheet1.xml'];
                              //$('row c[r^="G"], row c[r^="H"]', $sheet).attr( 's', 57);
                              return number_format($value,2,",",".");
                        }
                    ],

How to modify this and convert to JS style? Thanks

Hi Sebastian,

I've added this and final code looks like:

        ->params(array(
                ":korisnik"=>$this->params["korisnik"],
                ":nadgrupa"=>$this->params["nadgrupa"]."%",
                ":start"=>$this->params["dateRange"][0],
                ":end"=>$this->params["dateRange"][1],
            ))
            ->pipe(new \koolreport\processes\Map(array(
                "{value}" => function($row) {
                    $value = str_pad(number_format($row["Iznos"],0,"",""),11,"0",STR_PAD_LEFT);
                    $row["formattedIznos"] = number_format($value,2,",",".");            
                    return $row;                    
                }
            )))
            ->pipe($this->dataStore("sumirana_prodaja"));

And I've added column on the view:

        "formattedIznos"=>array(
                        "label"=>"Formatted price",
                        "type"=>"number",
                    ),

Excel does contain column "Formatter price" but data is missing. Is this correct way of implementing it?

Yes I can see data, column contains values, which look ok but excel has wrong formatting. Check attached images. What I need in excel is (for example of first row) "22,00" as value. Comma has to be shifted right.

I did and no difference :/ Maybe if I change decimal separator from comma to dot? Not sure how to do that, but just thinking if that would make difference (only for exported type)

EDIT:

I have changed number_format __number_format($value,2); __ and example is below:

->pipe(new \koolreport\processes\Map(array(
                "{value}" => function($row) {
                    $value = str_pad(number_format($row["Iznos"],0,"",""),11,"0",STR_PAD_LEFT);
                    $row["formattedIznos"] = number_format($value,2);            
                    return $row;                    
                }
            )))

It shows number as "22.00" (with dot) and exports to excel in same way. I would like to display "," (comma) and export with "." (dot). Looks like excel shifts decimal places.

It gives same result in excel. Looks like my previous solution works, not sure what is going to happen if I get value 1520 eur for example. How it's gonna be formatted.