excel properties doesn't affect to exported file

saiful commented on Aug 13

hi, i have problem with excel export package, my styling properties doesn't affect to exported file. i have tried to use styleArray, column width, options columnAutoSize, excelStyle but its not working at all. i use msoffice 2021. does the office version have any effect on the results?


<?php

    use \koolreport\Excel\Table;
    $sheet1 = "Report Voucher Create";

?>

<meta charset="UTF-8">
<meta name="description" content="<?php echo $sheet1; ?>">
<meta name="keywords" content="Excel,HTML,CSS,XML,JavaScript">
<meta name="creator" content="Gurita">
<meta name="subject" content="<?php echo $sheet1; ?>">
<meta name="title" content="<?php echo $sheet1; ?>">
<meta name="category" content="category1">

<!-- sheet 1 -->
<div sheet-name="<?php echo $sheet1; ?>">

    <?php
    $allStylesArray = [
        'font' => [
            'name' => 'Calibri', //'Verdana', 'Arial'
            'size' => 12,
            'bold' => false,
            'italic' => false,
            'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
            'strikethrough' => FALSE,
            'superscript' => false,
            'subscript' => false,
            'color' => [
                'rgb' => '000000'
            ]
        ],
        'alignment' => [
            'horizontal' => 'left',//left, right, center, centerContinuous, justify, fill, distributed
            'vertical' => 'center',//top, center, justify, distributed
            'textRotation' => 0,
            'wrapText' => false,
            'shrinkToFit' => true,
            'indent' => 0,
            'readOrder' => 0,
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => 'none', //dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            //left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
        ],
        'fill' => [
            'fillType' => 'none', //'solid', 'linear', 'path', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray'
            'rotation' => 90,
            'color' => [
                'rgb' => 'A0A0A0',
                'argb' => 'FFA0A0A0',
            ],
            'startColor' => [
                'rgb' => 'A0A0A0',
                'argb' => 'FFA0A0A0',
            ],
            'endColor' => [
                'argb' => 'FFFFFF',
                'argb' => 'FFFFFFFF',
            ],
        ],
    ];
    $styleArray = [
        'font' => [
            'italic' => false,
            'color' => [
                'rgb' => '000000',
            ]
        ],
    ];
    ?>

    <!-- <div>Voucher Create</div> -->
    <div>
    <?php
        Table::create(array(
            "dataSource"        => $this->dataStore("AGL100"),
            "columns"           => array(
                "ins_dt"        => array(
                    "label"     => "Voucher Insert Date",
                    "type"      => "date(d/m/Y)",
                    // "width"     => 40                # ---> not working
                ),
                "ins_id"        => array(
                    "label"     => "User",
                ),
                "voucher_type"  => array(
                    "label"     => "Voucher Type",
                    // "width"     => 30
                ),
                "cnt"           => array(
                    "label"     => "Qty",
                )
            ),
            'rowHeight' => function($row, $rowIndex) {
                // return 'auto';
                return 10 * ($rowIndex + 1);            # ---> not working
            },
            // 'columnWidth' => '30',
            'columnWidth' => function($columnName, $columnIndex) {
                if ($columnName === 'voucher_type') return '10';
                else return 'auto';                     # ---> not working
            },
            // 'columnAutoSize' => false,
            // "options" => array(
            //     // "columnAutoSize" => true,
            //     // ),                                   # ---> not working
            //     "columnWidth" => function($columnName, $columnIndex){
            //         if($columnName === 'voucher_type') return 30;
            //         else return 'auto';                 # ---> not working
            //     },
            // ),
            // 'excelStyle' => array(
            //     'dataField' => function($dataFields) use ($allStylesArray) {
            //         return $allStylesArray;
            //     },
            //     'dataHeader' => function($dataFields, $fieldInfo) use ($allStylesArray) {
            //         return $allStylesArray;
            //     },
            //     'columnHeader' => function($header, $headerInfo) use ($allStylesArray) {
            //         return $allStylesArray;
            //     },
            //     'rowHeader' => function($header, $headerInfo) use ($allStylesArray) {
            //         return $allStylesArray;
            //     },
            //     'dataCell' => function($value, $cellInfo) use ($allStylesArray) {                    
            //         return $allStylesArray;
            //     },
            // )                                            # ---> not working
        ));
    ?>
    </div>
   </div>

Sebastian Morales commented on Aug 14

Would you pls list and describe the issues specifically in order for us? Tks,

saiful commented on Sep 3

i want to customize the excel exported files, i need to:

change the headers color to maroon
set the header to center-aligned
change font type for some column values

I have used some styling properties but it still has no effect on the excel results

Sebastian Morales commented on Sep 4

Supposed you want to style the excel\Table widget, here's its excelStyle property:

Table::create(array(
    ...
    "excelStyle" => [
        "header" => function($colName) { 
            ...
            return $styleArray; 
        },
        "bottomHeader" => function($colName) { 
            ...
            return $styleArray; 
        },
        "cell" => function($colName, $value, $row) { 
            ...
            return $styleArray; 
        },
        "footer" => function($colName, $footerValue) { 
            ...
            return $styleArray;  
        },
    ] 
));

The $styleArray includes "font" and "alignment" keys for you to adjust a cell style:

    $styleArray = [
        'font' => [
            'name' => 'Calibri', //'Verdana', 'Arial'
            'size' => 30,
            'bold' => true,
            'italic' => FALSE,
            'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
            'strikethrough' => FALSE,
            'superscript' => false,
            'subscript' => false,
            'color' => [
                'rgb' => '000000',
                'argb' => 'FF000000',
            ]
        ],
        'alignment' => [
            'horizontal' => 'general',//left, right, center, centerContinuous, justify, fill, distributed
            'vertical' => 'bottom',//top, center, justify, distributed
            'textRotation' => 0,
            'wrapText' => false,
            'shrinkToFit' => false,
            'indent' => 0,
            'readOrder' => 0,
        ]
    ];

For example, if you want change table header's font color and alignment:

Table::create(array(
    ...
    "excelStyle" => [
        "header" => function($colName) { 
            return [
                'font' => [
                    'color' => [
                        'rgb' => '000000', // color hex code
                    ]
                ],
                'alignment' => [
                    'horizontal' => 'center'
                ]
            ]; 
        },

saiful commented on Sep 5

Hello, this question is related to my another question here https://www.koolreport.com/forum/topics/3329

Just use Excel export template and the styling properties will be applied to the exported Excel file.

KoolReport's Forum

Excel properties doesn't affect to exported file #3325

Build Your Excellent Data Report