Resolved Virtuemart Salesprice Excel Export - Decimal Places

Hello Elliot,

This is not an issue with your export template but rather how spreadsheets show prices. By default they hide values that end with a 0. Same way they strip any 0 that is used at the start of a value.

If you look at your exported file with a text editor, if you would export it as CSV, you will see the decimals are there.

All you should need to do is select the column and set its formatting to currency. That will bring back the 0s.

Let us know how it goes.

Hi RolandD

Thanks, I was aware of those Excel issues.

I had hoped your new excel output might have been able to automatically turn into currency format when creating the excel output.

Could I customize PhpSpreadsheet in an override ( administrator/templates/<admin template>/html/com_csvi/<component>/model/<import or export>/<file>.php) ?

thanks for your help

regards
Eliot

Hello Eliot,

We are looking into the possibilities of customizing the XLSX formatting but have not yet done anything concrete. It is not possible to make an override of the export helpers. All you can do for now is modify the existing class at administrator/components/com_csvi/helper/file/export/xlsx/csvimproved.php in the prepareContent function I think or otherwise the contentText.

As I said, we do not yet have a clear idea of what is possible yet and what changes are needed.

Hi RolandD

I modified administrator\components\com_csvi\assets\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Worksheet\Worksheet.php

Following works for me by modifying the fromArray function.

/**

     * Price column.

     *

     * @var string

     */

    private $priceColumn = '';



public function fromArray(array $source, $nullValue = null, $startCell = 'A1', $strictNullComparison = false)

    {

        //    Convert a 1-D array to 2-D (for ease of looping)

        if (!is_array(end($source))) {

            $source = [$source];

        }



        // start coordinate

        [$startColumn, $startRow] = Coordinate::coordinateFromString($startCell);

        

        $foundPrice = false;



        // Loop through $source

        foreach ($source as $rowData) {

            $currentColumn = $startColumn;

            foreach ($rowData as $cellValue) {

                // get column header

                // if ($field->number_field)

                $is_price = false;

                if ($startRow > 1 && $this->priceColumn == '' && $this->priceColumn !== '99' ) { 

                    $column_header = $this->getCell($currentColumn . 1)->getValue();

                    if(stripos($column_header, 'price') !== false){

                        $is_price = true;

                        $foundPrice = true;

                        $this->priceColumn = $currentColumn;

                    }

                } 

                elseif ($startRow > 1 && $currentColumn == $this->priceColumn) {

                    $is_price = true; $foundPrice = true;

                }

                if ($strictNullComparison) {

                    if ($cellValue !== $nullValue) {

                        // Set cell value

                        if ($is_price) {

                            $this->getCell($currentColumn . $startRow)->setValueExplicit($cellValue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

                            $this->getStyle($currentColumn . $startRow)->getNumberFormat()->setFormatCode('#,##0.00');

                        } else {

                            // original

                            $this->getCell($currentColumn . $startRow)->setValue($cellValue);

                        }

                    }

                } else {

                    if ($cellValue != $nullValue) {

                        // Set cell value

                        if ($is_price) {

                            $this->getCell($currentColumn . $startRow)->setValueExplicit($cellValue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

                            $this->getStyle($currentColumn . $startRow)->getNumberFormat()->setFormatCode('#,##0.00');

                        } else {

                            // original

                            $this->getCell($currentColumn . $startRow)->setValue($cellValue);

                        }

                    }

                }

                ++$currentColumn;

            }

            if ($startRow > 1 && !$foundPrice) $this->priceColumn = '99';



            ++$startRow;

        }



        return $this;

    }

regards
Eliot

Hello Eliot,
Thank you for sharing your solution. We will look into it.