#####
Excel
#####


****
NAME
****


Kernel::System::Excel - all Excel functions


********
SYNOPSIS
********


All Excel functions.


****************
PUBLIC INTERFACE
****************


new()
=====


create an object


.. code-block:: perl

     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'.


.. code-block:: perl

     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.


.. code-block:: perl

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


Returns:


.. code-block:: perl

     my $ColumnIdentifier = 'ZA';



GetFormatDefinition()
=====================


Returns a hash reference of the giving stats/statsnumber.


.. code-block:: perl

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


Returns:


.. code-block:: perl

     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.


.. code-block:: perl

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


Returns:


.. code-block:: perl

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



MergeFormatDefinitions()
========================


Return merged format definitions as hash.


.. code-block:: perl

     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:


.. code-block:: perl

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



_ReplaceDataSeries()
====================


Replaces given value with 'LastRow' or 'LastColumn' if defined.


.. code-block:: perl

     my $Value = $ExcelObject->_ReplaceDataSeries(
         Type    => 'LastRow',
         LastRow => 'NewValue',
         Value   => 1,
 
         # or
 
         Type       => 'LastColumn',
         LastColumn => 'NewValue',
         Value      => 1,
     );


Returns:


.. code-block:: perl

     my $Value = 1;