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;