blib/lib/Win32/Excel/Refresh.pm

filter: Statementstmt 90.0% 18 / 20 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 7 / 7 Podpod n/a totaltotal 92.5% 25 / 27 CC 1 SCAR 0.4
CC
1
Cov
90%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
↑ Summary
1 package Win32::Excel::Refresh;
2
3 1 ▶use 5.006;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 1 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 1 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
7
8 require Exporter;
9 1 ▶use AutoLoader qw(AUTOLOAD);
Subroutine BEGIN: called CC=1 SCAR=0.0
10
11 1 ▶use File::Spec::Functions ':ALL';
Subroutine BEGIN: called CC=1 SCAR=0.0
12
13 0 ▶use Win32::OLE;
Subroutine BEGIN: called CC=1 SCAR=2.6
14 use Win32::OLE qw(in);
15 use Win32::OLE::Const 'Microsoft Excel';
16
17 # use Data::Dumper;
18
19 our @ISA = qw(Exporter);
20
21 # Items to export into callers namespace by default. Note: do not export
22 # names by default without a very good reason. Use EXPORT_OK instead.
23 # Do not simply export all your public functions/methods/constants.
24
25 # This allows declaration use Win32::Excel::Refresh ':all';
26 # If you do not need this, moving things directly into @EXPORT or @EXPORT_OK
27 # will save memory.
28 our %EXPORT_TAGS = ( 'all' => [ qw() ] );
29 our @EXPORT_OK = ( @{ $EXPORT_TAGS{'all'} } );
30 our @EXPORT = qw(XLRefresh);
31
32 our $VERSION = '0.02';
33
34
35 # Preloaded methods go here.
36
37 # ---------------------------------------------------------------
38 # SUBROUTINE: XLRefresh
39 # Usage: XLRefresh( $filename, $opts );
40 #
41 # $filename is a relative or absoulte filename.
42 #
43 # $opts is a hash reference of parameters
44 # { all => [ 0 | 1 ] }
45 # { query-tables => [ 0 | 1 ] }
46 # { pivot-tables => [ 0 | 1 ] }
47 # { visible => [ 0 | 1 ] }
48 # { macros => [list] }
49 #
50 # list is string of macros and argumants, "macro(args)", "marco(args)"
51 # e.g. macros => "RefreshAllPivotTables( true, true )",
52 #
53 # 2) Implement more of a Excel Feel
54 # XLRefresh.exe -m Sheet1!RefreshAllPivotTables( true, true );
55 # ---------------------------------------------------------------
56 sub XLRefresh {
57
58 my $filename = shift || die("No filename supplied");
59 my $opts = shift;
60
61 # print Dumper( $opts );
62
63 # -------------------------------------------------------------
64 # CLEAN AND TRAP FILENAME INPUT
65 # -------------------------------------------------------------
66 $filename = rel2abs($filename);
67 croak("$filename does not exist.\n") if ( !-e $filename);
68
69 # -------------------------------------------------------------
70 # OPEN A NEW APPLICATION INSTANCE.
71 # Opening a new application instance prevents a workbook of
72 # same name and a generation of an error and prevents the the
73 # decision as to whether to close the existing application or
74 # not. Cf the deprecated subroutines at the end to see how
75 # it was done previously.
76 # -------------------------------------------------------------
77 my $Excel;
78 $Excel = Win32::OLE->new('Excel.Application', 'Quit');
79
80 # -------------------------------------------------------------
81 # Set the visibility of the operations
82 # -------------------------------------------------------------
83 $Excel->{DisplayAlerts} = "False";
84 $Excel->{Visible} = $opts->{'visible'} || 0 ; # if you want to see what's going on
85
86
87 # -------------------------------------------------------------
88 # OPEN FILE:
89 # There is no need to trap if the workbook is open. If the file is open elsewhere
90 # do not save this file.
91 # The open function:
92 # expression.Open(FileName, RefreshLinks, ReadOnly, Format, Password, WriteResPassword,
93 # IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)
94 # -------------------------------------------------------------
95 my $wb = $Excel->Workbooks->Open( $filename ); # open the file
96
97 # -------------------------------------------------------------
98 # Workbook.RefreshAll
99 # Refreshes all external data ranges and PivotTable reports in the specified workbook.
100 # -------------------------------------------------------------
101 $wb->RefreshAll if ( $opts->{all} );
102
103 # -------------------------------------------------------------
104 # Refresh Charts
105 # -------------------------------------------------------------
106 #_refreshall( $wb, "Charts", "Refresh" );
107 # foreach my $chart ( in($Excel->Charts) ) {
108 # $chart->Calculate;
109 #}
110
111
112 # -------------------------------------------------------------
113 # REFRESH: Query Tables and Pivot tables
114 # Iterate through worksheets
115 # -------------------------------------------------------------
116 foreach my $ws ( in( $wb->WorkSheets ) ) {
117
118 # print "Updating Worksheet: $ws->{Name}\n";
119
120 # ------------------------------------------------------
121 # Query Tables
122 # ------------------------------------------------------
123 if ( $opts->{'query-tables'} ){
124 _refreshall( $ws, "QueryTables", "Refresh");
125 # print "\tRefreshing QueryTable(s)\n";
126 }
127
128 # ------------------------------------------------------
129 # Pivot Tables
130 # ------------------------------------------------------
131 if ( $opts->{'pivot-tables'} ) {
132 _refreshall( $ws, "PivotTables", "RefreshTable") ;
133 # print "\tRefreshing Pivot Table(s)\n";
134 }
135
136 }
137
138
139 # -------------------------------------------------------------
140 # Run Macros: Query Tables and Pivot tables
141 # -------------------------------------------------------------
142 foreach my $macro ( @{ $opts->{'macros'} } ) {
143 $Excel->Run( $macro );
144 # $Excel->Run('Sheet1.macro1');
145 # print "Running Macro: $macro\n";
146 }
147
148 # -------------------------------------------------------------
149 # SAVE WORKBOOK
150 # expression.SaveAs(Filename, FileFormat, Password, WriteResPassword,
151 # ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution,
152 # AddToMru, TextCodePage, TextVisualLayout)
153 # -------------------------------------------------------------
154 ## Recalculate before closing
155 $Excel->Calculate;
156 $wb->Save;
157 $wb->Close;
158
159 # $wb->SaveAs(
160 # { Filename =>$filename,
161 # AddToMru => 'FALSE' ,
162 # }
163 #);
164
165 $Excel->Quit(); # if ( $close_on_exit == TRUE );
166
167 } ## END SUBROUTINE XLRefresh
168
169
170 ## SUBROUTINE _refresh, _refreshall
171 ## $self->_refreshall( $obj, collection_method, individual_method );
172 ## e.g. $self->refreshall($app, "workbooks", "refresh");
173 ## Generic method for refreshing object.
174 ## object: A suitable Win32::Object
175 ## collection_method: Method for returning an array of individuals in a collection
176 ## individual_method: Method to be executed for each individual
177 sub _refreshall {
178
179 my $obj = shift;
180 my $collection_method = shift;
181 my $individual_method = shift;
182
183 return if ( $obj->$collection_method->Count < 1 );
184
185 print "\tUpdating $collection_method: $obj->{Name}\n";
186
187 foreach my $individual ( in( $obj->$collection_method ) ) {
188 $individual->$individual_method;
189 }
190
191 ## Error Trap.
192 if ( Win32::OLE->LastError() ) {
193 print "TRAPPING ERROR\n";
194 print "Win32::OLE->LastError()\n";
195 }
196
197 } # END SUBROUTINE _refresh_all
198
199
200 sub _refresh {
201
202 my $individual = shift;
203 my $individual_method = shift;
204
205 $individual->individual_method;
206
207 } # END SUBROUTINE _refresh
208
209
210
211
212 # Autoload methods go after =cut, and are processed by the autosplit program.
213
214 1;
215 __END__
↑ Summary