Hi Support/Tharuna
I have an Excel export template setup with virtuemart sales price set as price field YES
.
In the template settings I have the following settings:
However in the exported excel file the formatting is gone/lost.
Please can yiou help,
regards
Eliot
- eliotrayner
- RO CSVI
- Friday, 16 September 2022
- Subscribe via email
Accepted Answer
Pending Moderation
Hello Eliot,
Thank you for sharing your solution. We will look into it.
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
Tharuna
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
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.
regards
Eliot
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
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.
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
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
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
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
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.
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
RolandD
=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
- Page :
- 1
There are no replies made for this post yet.
Be one of the first to reply to this post!
Be one of the first to reply to this post!
Please login to post a reply
You will need to be logged in to be able to post a reply. Login using the form on the right or register an account if you are new here. Register Here »