How to assign a currency Symbol when formatting cell?

[recci]

Maybe I am being dumb but I cant see any way to assign a currency symbol when formatting cells? Is it possible to do this? I am creating a document with different currency's in each row so I cant just set in the template.

[johmue]

You can use ExcelBook::addCustomFormat() a test can be found here: test

[recci]

I can get this to work but I cant seem to be able to add a custom background colour to the cell when using addCustomFormat().

`$xlBook = $this->createBook(); // Load the template $xlBook->loadFile($this->template); $colour = $xlBook->colorPack(198,104,104);

    $currencyformat1 = $xlBook->addCustomFormat("#.00[$ kr]");
    $currency1 = $xlBook->addFormat();
    $currency1->patternForegroundColor($colour);
    $currency1->fillPattern(1);
    $currency1->numberFormat($currencyformat1);

    $currencyformat2 = $xlBook->addCustomFormat("[$ £]#.00");
    $currency2 = $xlBook->addFormat();
    $currency2->numberFormat($currencyformat2);
    $currency2->fillPattern(1);
    $currency2->patternForegroundColor($colour);`

Any ideas?

[johmue]

It seems to be broken somehow, but if you're creating XLSX files you can use the following:

$xlBook = new \ExcelBook(null, null, true);
$xlBook->setLocale('UTF-8');
$xlBook->setRgbMode(true);

$colour = $xlBook->colorPack(198, 104, 104);

$currencyformat1 = $xlBook->addCustomFormat("#.00[$ kr]");
$currency1 = $xlBook->addFormat();
$currency1->patternForegroundColor($colour);
$currency1->fillPattern(ExcelFormat::FILLPATTERN_SOLID);
$currency1->numberFormat($currencyformat1);

$xlSheet = new \ExcelSheet($xlBook, 'test');
$xlSheet->write(1,1,100,$currency1);

$xlBook->save('issue_229.xlsx');

[recci]

There must be something else wrong with my code. Even if i comment out the custom currency stuff it still does not colour the columns that get the custom format. The rest of the cells use a coopied format from the template:

  ` public function populateBook(){
    $xlBook = $this->createBook();
    $xlBook->setRGBMode(true);
    $xlBook->setLocale('UTF-8');
    // Load the template
    $xlBook->loadFile($this->template);
    $colour = $xlBook->colorPack(198,104,104);

   // $currencyformat1 = $xlBook->addCustomFormat("#.00[$ kr]");
    $currency1 = $xlBook->addFormat();
    $currency1->fillPattern(ExcelFormat::FILLPATTERN_SOLID);
    $currency1->patternForegroundColor($colour);
   // $currency1->numberFormat($currencyformat1);

   // $currencyformat2 = $xlBook->addCustomFormat("[$ £]#.00");
    $currency2 = $xlBook->addFormat();
    $currency2->fillPattern(ExcelFormat::FILLPATTERN_SOLID);
    $currency2->patternForegroundColor($colour);
   // $currency2->numberFormat($currencyformat2);

    $exchangeRateSheet = $xlBook->getSheetByName('Exchange rates');

    foreach ($this->reportDates as $reportPeriod => $reportDate){
        if($reportPeriod == 'Monthly') {
            // Set the reference to a specific Sheet
            $xlSheet = $xlBook->getSheetByName('Monthly');
            //get the month name for top of sheet
            $month = new DateTime($reportDate['startDate']);
            $xlSheet->write(1, 0, $month->format('F'));

            $xlSheetRaw = $xlBook->getSheetByName('Monthly_Raw');
            //get data from the db
            $results = $this->getResults($reportDate['startDate'], $reportDate['endDate']);
            //process the data
            $dealers = $this->processResults($results, $reportDate['startDate'], $reportDate['endDate']);
        }
        elseif ($reportPeriod == 'Yearly'){
            $xlSheet = $xlBook->getSheetByName('YTD');
            $xlSheetRaw = $xlBook->getSheetByName('YTD_Raw');

            //get the year for top of sheet
            $year = new DateTime($reportDate['startDate']);
            $xlSheet->write(1, 0, 'Year up to '.$year->format('Y-m-d'));

            //get data from the db
            $results = $this->getResults($reportDate['startDate'], $reportDate['endDate']);
            //process the data
            $dealers = $this->processResults($results, $reportDate['startDate'], $reportDate['endDate']);
        }

        $j = 4;
        $l = 1;//used for raw sheet row numbering
        foreach ($dealers as $dealer => $row) {

            $k = 0;
            foreach ($row as $col) {
                if($k==5 || $k==17 || $k==29){
                    $k++;
                }

                if($k == 9 || $k == 21){
                    if(array_key_exists($row['country'], $this->countries)){
                        if($row['country']['symbol'] == '£'){
                            $format = $currency2;
                        }
                        else {
                            $format = $currency1;
                        }
                    }
                }
                else {
                    // read format for specific cell
                    $format = $xlSheet->cellFormat(4, $k);
                }

                //write formated tab
                $xlSheet->write($j, $k, $col,$format);
                //$xlSheet->setCellFormat($j, $k, $addFormat);
                //write raw tab
                $xlSheetRaw->write($l, $k, $col);
                $k++;
            }
            $j++;
            $l++;
        }
        //add totals row
        $xlSheet->write($j, 1, 'Total');
        $m=6;
        foreach ($this->totals as $total){
            if($m==17 || $m==29){
                $m++;
            }
            $xlSheet->write($j, $m, $total);
            $m++;
        }
    }

    return $xlBook;
}` @

[recci]

I found the issue, $xlBook->setRGBMode(true); $xlBook->setLocale('UTF-8'); have to be called after you load the template.

` $xlBook = $this->createBook(); // Load the template $xlBook->loadFile($this->template); $xlBook->setRGBMode(true); $xlBook->setLocale('UTF-8'); $colour = $xlBook->colorPack(198,104,104); ``