#
# Copyright (c) 2009 Nokia Corporation and/or its subsidiary(-ies).
# All rights reserved.
# This component and the accompanying materials are made available
# under the terms of "Eclipse Public License v1.0"
# which accompanies this distribution, and is available
# at the URL "http://www.eclipse.org/legal/epl-v10.html".
#
# Initial Contributors:
# Nokia Corporation - initial contribution.
#
# Contributors:
#
# Description:
#
###################################################
###################################################
# This module contains common helper functions for 
# handling excel sheets from perl.
###################################################
###################################################
package excel_support;

##########################################
# Libraries used
##########################################
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Exporter ();     # Needed to make constants available to caller.

##########################################
# Defining all the "global" variables, that we are using.
##########################################
use vars qw(
            @ISA
            @EXPORT
            );


@ISA = qw(Exporter); # Inherit from Exporter module (import method needed).

# Define all the functions defined by this module
use subs qw(
	    getExcelApp
	    quitExcelApp
	    openWorkbook
	    getWorkSheet
	    getIndexByColName
	    );

##########################################
# Define the exports from this module that can be used
# Outside
##########################################
@EXPORT = qw(
             &getExcelApp
	     &quitExcelApp
	     &openWorkbook
	     &getWorkSheet
	     &getIndexByColName
             );


###########################################################################
# Function that creates an excel application and returns a reference to it.
#
# Params: -
#
# Return value: a reference to a excel application
###########################################################################
sub getExcelApp
{
    # get already active Excel application or open new
    my $excel = Win32::OLE->GetActiveObject('Excel.Application') || 
      Win32::OLE->new('Excel.Application', 'Quit');
    
    return \$excel;									    
}

###########################################################################
# Function that deletes an excel application given
#
# Params: refToExcelApp
#
# Return value: a reference to a excel application
###########################################################################
sub quitExcelApp
{
    my $refToExcelApp = shift;

    $$refToExcelApp->Quit();
    # For some reason this needs to be done twice inorder to close the excel.
    undef $$refToExcelApp;
    undef $refToExcelApp;
}

###########################################################################
# Function opens the given filename as workbook
#
# Params: Filename, referenceToExcelApp
#
# Return: reference to a workbook object 
###########################################################################
sub openWorkbook
{
    my $filename = shift;
    my $refToExcel = shift;

    # Open the workbook as read only
    my $workBook = ${$refToExcel}->Workbooks->Open( { FileName => $filename, ReadOnly=> xlReadOnly } );
    die "$filename: cannot open file\n" unless (defined $workBook );

    return \$workBook;
}

###########################################################################
# Function finds the wanted worksheet from the given workbook
#
# Params: referenceToWorkbook,wantedSheetName
#
# Return: reference to a worksheet object 
###########################################################################
sub getWorkSheet
{
    my $refToWb = shift;
    my $wantedSheetName = shift;
    
    my $workSheet;

    # Loop through all the workbooks and find a matching name.
    for( my $i = 0; $i < $$refToWb->WorkSheets->Count; $i++)
    {
	# The index is from 1-> and the counter starts from 0 => +1.
	$workSheet = $$refToWb->WorkSheets($i + 1);
        if( $workSheet->Name eq $wantedSheetName ) 
        {
	    # match found, return reference to the found worksheet
	    return \$workSheet;
	}
    }

    # No match found, error, thus return undef.
    return undef;
}           


###########################################################################
# Function that finds the index of the column based on given name
#
# Params: referenceToWorksheet,
#         wantedColName   (== the wanted column name)
#         maxcolCount     (== how many columns are checked for the given name) 
#
# Return: index to Column, 
#         -1, if no match found
###########################################################################
sub getIndexByColName
{
    my $refToWorksheet = shift;
    my $colName = shift;
    my $maxColCount = shift;
    
    my %i;
    # loop through columns to find the index, which 
    # matches to the given column name.
    for( my $i = 1; $i < $maxColCount; $i++ )
    {
	# The headers are always in row 1.
	my $colNameFromSheet = $$refToWorksheet->Cells( 1, $i  )->{ 'Value' };

	# check the name against the given name. If same then return the index.
        if($colName eq $colNameFromSheet)
	{
	    return $i;
	}	       
    }

    # No match found, return -1 as an error.
    return -1;
}


1;