1 # |
|
2 # Copyright (c) 2009 Nokia Corporation and/or its subsidiary(-ies). |
|
3 # All rights reserved. |
|
4 # This component and the accompanying materials are made available |
|
5 # under the terms of "Eclipse Public License v1.0" |
|
6 # which accompanies this distribution, and is available |
|
7 # at the URL "http://www.eclipse.org/legal/epl-v10.html". |
|
8 # |
|
9 # Initial Contributors: |
|
10 # Nokia Corporation - initial contribution. |
|
11 # |
|
12 # Contributors: |
|
13 # |
|
14 # Description: |
|
15 # |
|
16 ################################################### |
|
17 ################################################### |
|
18 # This module contains common helper functions for |
|
19 # handling excel sheets from perl. |
|
20 ################################################### |
|
21 ################################################### |
|
22 package excel_support; |
|
23 |
|
24 ########################################## |
|
25 # Libraries used |
|
26 ########################################## |
|
27 use strict; |
|
28 use Win32::OLE qw(in with); |
|
29 use Win32::OLE::Const 'Microsoft Excel'; |
|
30 use Exporter (); # Needed to make constants available to caller. |
|
31 |
|
32 ########################################## |
|
33 # Defining all the "global" variables, that we are using. |
|
34 ########################################## |
|
35 use vars qw( |
|
36 @ISA |
|
37 @EXPORT |
|
38 ); |
|
39 |
|
40 |
|
41 @ISA = qw(Exporter); # Inherit from Exporter module (import method needed). |
|
42 |
|
43 # Define all the functions defined by this module |
|
44 use subs qw( |
|
45 getExcelApp |
|
46 quitExcelApp |
|
47 openWorkbook |
|
48 getWorkSheet |
|
49 getIndexByColName |
|
50 ); |
|
51 |
|
52 ########################################## |
|
53 # Define the exports from this module that can be used |
|
54 # Outside |
|
55 ########################################## |
|
56 @EXPORT = qw( |
|
57 &getExcelApp |
|
58 &quitExcelApp |
|
59 &openWorkbook |
|
60 &getWorkSheet |
|
61 &getIndexByColName |
|
62 ); |
|
63 |
|
64 |
|
65 ########################################################################### |
|
66 # Function that creates an excel application and returns a reference to it. |
|
67 # |
|
68 # Params: - |
|
69 # |
|
70 # Return value: a reference to a excel application |
|
71 ########################################################################### |
|
72 sub getExcelApp |
|
73 { |
|
74 # get already active Excel application or open new |
|
75 my $excel = Win32::OLE->GetActiveObject('Excel.Application') || |
|
76 Win32::OLE->new('Excel.Application', 'Quit'); |
|
77 |
|
78 return \$excel; |
|
79 } |
|
80 |
|
81 ########################################################################### |
|
82 # Function that deletes an excel application given |
|
83 # |
|
84 # Params: refToExcelApp |
|
85 # |
|
86 # Return value: a reference to a excel application |
|
87 ########################################################################### |
|
88 sub quitExcelApp |
|
89 { |
|
90 my $refToExcelApp = shift; |
|
91 |
|
92 $$refToExcelApp->Quit(); |
|
93 # For some reason this needs to be done twice inorder to close the excel. |
|
94 undef $$refToExcelApp; |
|
95 undef $refToExcelApp; |
|
96 } |
|
97 |
|
98 ########################################################################### |
|
99 # Function opens the given filename as workbook |
|
100 # |
|
101 # Params: Filename, referenceToExcelApp |
|
102 # |
|
103 # Return: reference to a workbook object |
|
104 ########################################################################### |
|
105 sub openWorkbook |
|
106 { |
|
107 my $filename = shift; |
|
108 my $refToExcel = shift; |
|
109 |
|
110 # Open the workbook as read only |
|
111 my $workBook = ${$refToExcel}->Workbooks->Open( { FileName => $filename, ReadOnly=> xlReadOnly } ); |
|
112 die "$filename: cannot open file\n" unless (defined $workBook ); |
|
113 |
|
114 return \$workBook; |
|
115 } |
|
116 |
|
117 ########################################################################### |
|
118 # Function finds the wanted worksheet from the given workbook |
|
119 # |
|
120 # Params: referenceToWorkbook,wantedSheetName |
|
121 # |
|
122 # Return: reference to a worksheet object |
|
123 ########################################################################### |
|
124 sub getWorkSheet |
|
125 { |
|
126 my $refToWb = shift; |
|
127 my $wantedSheetName = shift; |
|
128 |
|
129 my $workSheet; |
|
130 |
|
131 # Loop through all the workbooks and find a matching name. |
|
132 for( my $i = 0; $i < $$refToWb->WorkSheets->Count; $i++) |
|
133 { |
|
134 # The index is from 1-> and the counter starts from 0 => +1. |
|
135 $workSheet = $$refToWb->WorkSheets($i + 1); |
|
136 if( $workSheet->Name eq $wantedSheetName ) |
|
137 { |
|
138 # match found, return reference to the found worksheet |
|
139 return \$workSheet; |
|
140 } |
|
141 } |
|
142 |
|
143 # No match found, error, thus return undef. |
|
144 return undef; |
|
145 } |
|
146 |
|
147 |
|
148 ########################################################################### |
|
149 # Function that finds the index of the column based on given name |
|
150 # |
|
151 # Params: referenceToWorksheet, |
|
152 # wantedColName (== the wanted column name) |
|
153 # maxcolCount (== how many columns are checked for the given name) |
|
154 # |
|
155 # Return: index to Column, |
|
156 # -1, if no match found |
|
157 ########################################################################### |
|
158 sub getIndexByColName |
|
159 { |
|
160 my $refToWorksheet = shift; |
|
161 my $colName = shift; |
|
162 my $maxColCount = shift; |
|
163 |
|
164 my %i; |
|
165 # loop through columns to find the index, which |
|
166 # matches to the given column name. |
|
167 for( my $i = 1; $i < $maxColCount; $i++ ) |
|
168 { |
|
169 # The headers are always in row 1. |
|
170 my $colNameFromSheet = $$refToWorksheet->Cells( 1, $i )->{ 'Value' }; |
|
171 |
|
172 # check the name against the given name. If same then return the index. |
|
173 if($colName eq $colNameFromSheet) |
|
174 { |
|
175 return $i; |
|
176 } |
|
177 } |
|
178 |
|
179 # No match found, return -1 as an error. |
|
180 return -1; |
|
181 } |
|
182 |
|
183 |
|
184 1; |
|