1. eliotrayner
  2. RO CSVI
  3. Friday, 16 September 2022
  4.  Subscribe via email
Hi Support/Tharuna

I have an Excel export template setup with virtuemart sales price set as price field YES

. export template 1.jpg

In the template settings I have the following settings:

export template 2.jpg

However in the exported excel file the formatting is gone/lost.

Screenshot 2022-09-16 124528.png

Please can yiou help,

regards
Eliot
Attachments (3)
Accepted Answer Pending Moderation
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.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. one week ago
  2. RO CSVI
  3. # 1
Accepted Answer Pending Moderation
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
  1. one week ago
  2. RO CSVI
  3. # 2
Accepted Answer Pending Moderation
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.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. one week ago
  2. RO CSVI
  3. # 3
Accepted Answer Pending Moderation
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
  1. 2 days ago
  2. RO CSVI
  3. # 4
Accepted Answer Pending Moderation
Hello Eliot,
Thank you for sharing your solution. We will look into it.
Kind regards,

Tharuna

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. 2 days ago
  2. RO CSVI
  3. # 5
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!