Excel Formatting and CSS Styles

Jae Muzzin commented on Jul 16, 2018

I have a simple question about the Excel package but can't seem to find the answer on forums or in examples. If I develop a report using a Table that uses custom CSS styles, such as fonts, and cell background colors, will those custom styles be exported with Excel?

What I am looking for is the extent to which KoolReport will allow custom formatting in the Excel export.

How are other page elements exported to excel, such as the report Header and company logo?. Is it possible to separate things into separate Excel worksheets in the same document?

Lastly if my report has two different tables, how is that rendered on the excel export?

Thanks

David Winterburn commented on Jul 17, 2018

Hi Jae,

Currently the Excel packace exports each datastore into a separate sheet of the excel file. You could use the following command to specify datastores to export

$report = new SalesQuarters();
$report->run();
$report->exportToExcel(array(
$report->exportToExcel(array(
    'dataStores' => array(
        'salesCustomer' => array(
            'columns' => array('customerName', 'Total', 4, 3, 2),
        ),
        'salesProduct' => array(
            'columns' => array('productName', 'Total', 0, 1, 2),
        )
    )
))
->toBrowser("SalesQuarters.xlsx");

Regarding custom CSS style, the Excel package hasn't supported it yet but it's an excellent idea. We will consider adding custom formatting option for the exported excel file. You are very welcome to suggest how you want such formatting works. Thanks alot!

KoolReport commented on Jul 17, 2018

Currently, the Excel exporting will export raw data from report's dataStores. Each datastore will be on a excel sheet. You may choose which dataStore to export. However there is no option to format Excel file like adding logo, you may need to add them on later to Excel file by yourself.

We will take your question as suggestion to improve the package.

Jae Muzzin commented on Jul 17, 2018

Thank you for the comments. Regarding the suggestion: "there is no option to format Excel file like adding logo, you may need to add them on later to Excel file by yourself." Can you suggest the starting point for this, such as the hook in the API or what data would be acted upon to accomplish this?

Anna Tolve commented on Jun 23, 2021

Good morning, I will also need to export in .xlsx to keep the same css style that I use in exporting .pdf, is it possible to do it with the current version of the Excel package? Also I would also need it if the grouping functionality for exporting to .xlsx and .csv has been informed.

Sebastian Morales commented on Jun 28, 2021

Anna, Excel package supports row grouping and some styles now. Pls check our examples and the code below each:

Excel table with styles

Excel table with row grouping

Let us know if you have any question. Tks,

Anna Tolve commented on Jun 28, 2021

Thank you very much Sebastian, can you tell me if it is possible to associate a style to the cells that come out of the grouping function? if so, do you have an example that shows this?

Sebastian Morales commented on Jun 29, 2021

Yes, you can add style to the row groups of Excel Table widget. There's a missing excel style property called "rowGroup":

Table::create(array(
    ...
    "excelStyle" => [
        ...
        "rowGroup" => function($rgField, $colName) {
            ...
            return $styleArray;

In the next version of Excel package we will add a group value argument to this row group style function. Rgds,

Anna Tolve commented on Feb 3, 2022

Good morning Sebastian, can you tell me the correct way to set the formatValue in rowGroup type cells created in .xls export? My code is as follows:

"rowGroup" => [
 "ANNO" => [
                'calculate' => [
				"sum1" => ["sum","ARRIVI_ITALIANI"],
				"sum2" => ["sum","ARRIVI_STRANIERI"],
				"sum3" => ["sum","PRESENZE_ITALIANI"],
				"sum4" => ["sum","PRESENZE_STRANIERI"],
				"sum5" => ["sum","TOTALE_ARRIVI"],
				"sum6" => ["sum","P_ARRIVI"],
				"sum7" => ["sum","TOTALE_PRESENZE"],
				"sum8" => ["sum","P_PRESENZE"]
                ],
                "columnBottoms" => [
				"ARRIVI_ITALIANI" => "{sum1}",
				"ARRIVI_STRANIERI" => "{sum2}",
				"PRESENZE_ITALIANI" => "{sum3}",
				"PRESENZE_STRANIERI" => "{sum4}", 
				"TOTALE_ARRIVI" => "{sum5}",
				"P_ARRIVI" => "{sum6}",
				"TOTALE_PRESENZE" => "{sum7}",
				"P_PRESENZE" => "{sum8}"
                ],
		"bottom" => "TOTALE COMPLESSIVO",
            ],

I would need to set

"decimals" => 2,
"decimalPoint" => ",",
"thousandSeparator" => ".",
"suffix" => "%"

to these cells as well. What is the correct way to set them for these cells?

Sebastian Morales commented on Feb 7, 2022

Hi Anna, it looks like Excel Table's row group value hasn't had formatted value yet. We will support this in the next version. Meanwhile, we will try to find a work around for you. Tks,

Anna Tolve commented on Feb 7, 2022

OK thank you Sebastian!

Anna Tolve commented on Mar 10, 2022

Hi Sebastian, have you found a possible solution for me?

Sebastian Morales commented on Mar 15, 2022

Hi Anna, pls send us an email to support@koolreport.com or support@koolphp.net and mention this topic. We would send you the latest version of the Excel package which supports formatting table's row group values like what you described. Tks,

KoolReport's Forum

Excel Formatting and CSS Styles #376

Build Your Excellent Data Report