Excel#

NAME#

Kernel::System::Excel - all Excel functions

SYNOPSIS#

All Excel functions.

PUBLIC INTERFACE#

new()#

create an object

my $ExcelObject = $Kernel::OM->Get('Kernel::System::Excel');

Array2Excel()#

Returns the Excel file content, format (for e.g. attachments) is ‘application/vnd.ms-excel’ / ‘xls’.

my $ExcelFileContent = $ExcelObject->Array2Excel(
    Data => [
        {
            Name        => 'Overview',
            FreezePanes => [
                {
                    Row    => 2,
                    Column => 0,
                },
            ],
            TableData   => [
                [
                    {
                        Format => {
                            right    => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Format => {
                            left     => 1,
                            right    => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Format => {
                            left     => 1,
                            right    => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Value  => 'Response Time',
                        Merge  => 1,
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                            valign   => 'vcentre',
                            align    => 'center',
                        }
                    },
                    {
                        Merge => 2,
                    },
                    {
                        Merge => 3,
                    },
                    {
                        Value  => 'Solution Time',
                        Merge  => 1,
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                            valign   => 'vcentre',
                            align    => 'center',
                        }
                    },
                    {
                        Merge => 2,
                    },
                    {
                        Merge => 3,
                    },
                ],
                [
                    {
                        Value  => 'Service',
                        Format => {
                            right    => 1,
                            bottom   => 1,
                            bg_color => 'silver',
                            valign   => 'vcentre',
                            align    => 'center',
                        }
                    },
                    {
                        Value  => 'SLA',
                        Format => {
                            left     => 1,
                            right    => 1,
                            bottom   => 1,
                            bg_color => 'silver',
                            valign   => 'vcentre',
                            align    => 'center',
                        }
                    },
                    {
                        Value  => '#Tickets',
                        Format => {
                            left     => 1,
                            right    => 1,
                            bottom   => 1,
                            bg_color => 'silver',
                            valign   => 'vcentre',
                            align    => 'center',
                        }
                    },
                    {
                        Value  => '#IN',
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Value  => '%IN',
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Value  => '#OUT',
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Value  => '#IN',
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Value  => '%IN',
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                        }
                    },
                    {
                        Value  => '#OUT',
                        Format => {
                            border   => 1,
                            bg_color => 'silver',
                        }
                    },
                ],
                # ...
            ],
        },
    ],
);

GetColumnIdentifierByNumber()#

Returns the column identifier for a number since columns are identified by chars e.g. A, Z or A..Z.

my $ColumnIdentifier = $ExcelObject->GetColumnIdentifierByNumber(
    ColumnNumber => 27,
);

Returns:

my $ColumnIdentifier = 'ZA';

GetFormatDefinition()#

Returns a hash reference of the giving stats/statsnumber.

my $GetFormatDefinition = $ExcelObject->GetFormatDefinition(
    Stat => $Stat,
);

Returns:

my $GetFormatDefinition = {
    'Name' => 'Worksheet',
    '1' => {
        'Format' => {
            'align'    => 'center',
            'bg_color' => 'silver',
            'bold'     => '1',
            'border'   => '1',
            'valign'   => 'vcentre'
        }
    },
    'B3' => {
        'Format' => {
            'bold' => '1'
        }
    },
    'D' => {
        'Format' => {
            'align'  => 'center',
            'bold'   => '1',
            'color'  => 'red',
            'valign' => 'vcentre'
        }
    },
    'FreezePanes' => [
        {
            'Column' => '0',
            'Row' => '1'
        }
    ],
    'H' => {
        'Format' => {
            'align'    => 'center',
            'bg_color' => 'ccffff',
            'bold'     => '1',
            'color'    => 'ED053B',
            'valign'   => 'vcentre'
        }
    },
    'LASTCOLUMN' => {
        'Format' => {
            'border' => '1'
        }
    },
    'LASTROW' => {
        'Format' => {
            'bold'   => '1',
            'bottom' => '1'
        }
    },
};

GetColumnContentFormat()#

Returns the ColumnContentFormat.

my $ColumnContentFormat = $ExcelObject->GetColumnContentFormat(
    Value  => '123',
    Format => {},
);

Returns:

my $ColumnContentFormat = 'String';     # String, URL, DateTime, Number

MergeFormatDefinitions()#

Return merged format definitions as hash.

my %Format = $ExcelObject->MergeFormatDefinitions(
    Merge             => 1,                 # if 1, merges all format definitions from last to first in array 'FormatDefinitions'
                                            # if 0, use the first format definitions in array 'FormatDefinitions'
    FormatDefinitions => [
        # $CellFormatDefinition{Format},
        {
            'color' => 'silver',
            'right' => 1,
            'valign' => 'vcentre',
        },
        # $RowFormatDefinition{Format},
        {
            'color' => 'red',
            'right' => 0,
            'bold'  => 1,
        },
        # $ColumnFormatDefinition{Format},
        # $Merge{Format},
        # $CellData{Format},
        # $DefaultFormatDefinition{Format},
    ],
);

Returns:

my %Format = (
    'color'  => 'silver',
    'right'  => 1,
    'bold'   => 1,
    'valign' => 'vcentre',
);

_ReplaceDataSeries()#

Replaces given value with ‘LastRow’ or ‘LastColumn’ if defined.

my $Value = $ExcelObject->_ReplaceDataSeries(
    Type    => 'LastRow',
    LastRow => 'NewValue',
    Value   => 1,

    # or

    Type       => 'LastColumn',
    LastColumn => 'NewValue',
    Value      => 1,
);

Returns:

my $Value = 1;