| 1 | package Apache::FastForward::Spreadsheet; | ||
| 2 | # Copyright 2006 Jerzy Wachowiak | ||
| 3 | |||
| 4 | use strict; | ||
| 5 | use warnings; | ||
| 6 | use Carp; | ||
| 7 | use Text::CSV_XS; | ||
| 8 | |||
| 9 | use vars qw( $VERSION ); | ||
| 10 | $VERSION = '1.01'; | ||
| 11 | |||
| 12 | # Perl tie methods: DELETE, CLEAR, EXISTS, FIRSTKEY, NEXTKEY, | ||
| 13 | # SCALAR, UNTIE, DESTROY are not implemented as the package | ||
| 14 | # mimics a spreadsheet array of array behaviour | ||
| 15 | |||
| 16 | sub TIEHASH { | ||
| 17 | |||
| 18 | my $class = shift; | ||
| 19 | my $self = {}; | ||
| 20 | $self->{VERSION} = $VERSION; | ||
| 21 | $self->{template} = []; | ||
| 22 | bless ( $self, $class ); | ||
| 23 | return $self | ||
| 24 | } | ||
| 25 | |||
| 26 | sub LoadTemplate { | ||
| 27 | |||
| 28 | #Contract: | ||
| 29 | # [1] Path to a CSV file and CSV parameters as input | ||
| 30 | # [2] Method can either suceed (true) or everything dies... | ||
| 31 | |||
| 32 | my $self = shift; | ||
| 33 | my $path = shift; | ||
| 34 | my $csv_atrributes = shift; | ||
| 35 | |||
| 36 | defined( $csv_atrributes ) or | ||
| 37 | croak 'Missing CSV parameters for spreadsheet template'; | ||
| 38 | my $csv = Text::CSV_XS->new( $csv_atrributes ); | ||
| 39 | |||
| 40 | open( TEMPLATE, '< '.$path ) or | ||
| 41 | croak "Spreadsheet template $path -> $!"; | ||
| 42 | while ( defined( my $line = <TEMPLATE> ) ){ | ||
| 43 | $csv->parse( $line ) or | ||
| 44 | croak "Spreadsheet template $path -> The file cannot be parsed"; | ||
| 45 | push( @{ $self->{template} }, [ $csv->fields() ] ); | ||
| 46 | } | ||
| 47 | close( TEMPLATE ) | ||
| 48 | } | ||
| 49 | |||
| 50 | sub FETCH { | ||
| 51 | |||
| 52 | #Contract: | ||
| 53 | # [1] ('A12') spreadsheet address or row & column number eg ('12,1') as input | ||
| 54 | # [2] Cell value or undef if adresse does not exist | ||
| 55 | |||
| 56 | my $self = shift; | ||
| 57 | my $cell_address = shift; | ||
| 58 | |||
| 59 | my $coordinate = convert_spreadsheet_address( $cell_address ); | ||
| 60 | |||
| 61 | return $self->{template}->[$coordinate->{x}][$coordinate->{y}] | ||
| 62 | } | ||
| 63 | |||
| 64 | sub STORE { | ||
| 65 | |||
| 66 | #Contract: | ||
| 67 | # [1] ('A12') spreadsheet address or row & column number eg ('12,1') as input | ||
| 68 | # [2] Method can only suceed as array can always be expanded... | ||
| 69 | |||
| 70 | my $self = shift; | ||
| 71 | my $cell_address = shift; | ||
| 72 | my $cell_value = shift; | ||
| 73 | |||
| 74 | my $coordinate = convert_spreadsheet_address( $cell_address ); | ||
| 75 | |||
| 76 | $self->{template}->[$coordinate->{x}][$coordinate->{y}] = $cell_value | ||
| 77 | } | ||
| 78 | |||
| 79 | |||
| 80 | sub DumpAsCSV { | ||
| 81 | |||
| 82 | #Contract: | ||
| 83 | # [1] CSV file parameters as input | ||
| 84 | # [2] Method can return result string or everything dies... | ||
| 85 | |||
| 86 | my $self = shift; | ||
| 87 | my $csv_atrributes = shift; | ||
| 88 | |||
| 89 | defined( $csv_atrributes ) or | ||
| 90 | croak 'Missing CSV parameters for spreadsheet template'; | ||
| 91 | my $csv = Text::CSV_XS->new( $csv_atrributes ); | ||
| 92 | |||
| 93 | my $csv_string = ''; | ||
| 94 | foreach my $row ( @{$self->{template}} ){ | ||
| 95 | if ( $csv->combine( @$row ) ){ | ||
| 96 | $csv_string .= $csv->string()."\n"; | ||
| 97 | } | ||
| 98 | else{ | ||
| 99 | croak 'The spreadsheet cannot be dumped for value '.$csv->error_input(); | ||
| 100 | } | ||
| 101 | } | ||
| 102 | return $csv_string | ||
| 103 | } | ||
| 104 | |||
| 105 | sub ShiftRow { | ||
| 106 | |||
| 107 | #Contract: | ||
| 108 | # [1] No input | ||
| 109 | # [2] Similar to shift @ARRAY | ||
| 110 | # [3] Returns shifted row | ||
| 111 | |||
| 112 | my $self = shift; | ||
| 113 | my $row_number = shift; | ||
| 114 | |||
| 115 | return @{ shift( @{$self->{template}} ) } | ||
| 116 | } | ||
| 117 | |||
| 118 | sub PopRow { | ||
| 119 | |||
| 120 | #Contract: | ||
| 121 | # [1] No input | ||
| 122 | # [2] Similar to pop @ARRAY | ||
| 123 | # [3] Returns poped row | ||
| 124 | |||
| 125 | my $self = shift; | ||
| 126 | my $row_number = shift; | ||
| 127 | |||
| 128 | return @{ pop( @{$self->{template}} ) } | ||
| 129 | } | ||
| 130 | |||
| 131 | sub PushRow{ | ||
| 132 | |||
| 133 | #Contract: | ||
| 134 | # [1] Array as input | ||
| 135 | # [2] Similar to push @ARRAY | ||
| 136 | # [3] Returns the new number of elements in the spreadsheet | ||
| 137 | |||
| 138 | my $self = shift; | ||
| 139 | my @cell_values = @_; | ||
| 140 | |||
| 141 | push( @{$self->{template}}, [@cell_values] ); | ||
| 142 | |||
| 143 | return scalar( @{$self->{template}} ) | ||
| 144 | } | ||
| 145 | |||
| 146 | sub GetRow { | ||
| 147 | |||
| 148 | #Contract: | ||
| 149 | # [1] Row number as input | ||
| 150 | # [2] Returns array of cell values | ||
| 151 | |||
| 152 | my $self = shift; | ||
| 153 | my $row_number = shift; | ||
| 154 | |||
| 155 | if ( $row_number < 1 ){ | ||
| 156 | croak "Row number '$row_number' is out of range" | ||
| 157 | } | ||
| 158 | if ( $row_number > scalar( @{$self->{template}} ) ){ | ||
| 159 | croak "Row number '$row_number' is out of this spreadsheet" | ||
| 160 | } | ||
| 161 | |||
| 162 | my $x = $row_number - 1; | ||
| 163 | return @{$self->{template}->[$x]} | ||
| 164 | } | ||
| 165 | |||
| 166 | sub SetRow { | ||
| 167 | |||
| 168 | #Contract: | ||
| 169 | # [1] Row number and array as input | ||
| 170 | # [2] Returns nothing | ||
| 171 | |||
| 172 | my $self = shift; | ||
| 173 | my $row_number = shift; | ||
| 174 | my @cell_values = @_; | ||
| 175 | |||
| 176 | if ( $row_number < 1 ){ | ||
| 177 | croak "Row number '$row_number' is out of range" | ||
| 178 | } | ||
| 179 | if ( $row_number > scalar( @{$self->{template}} ) ){ | ||
| 180 | croak "Row number '$row_number' is out of this spreadsheet" | ||
| 181 | } | ||
| 182 | |||
| 183 | my $x = $row_number - 1; | ||
| 184 | $self->{template}->[$x] = \@cell_values | ||
| 185 | } | ||
| 186 | |||
| 187 | sub RemoveRow { | ||
| 188 | |||
| 189 | #Contract: | ||
| 190 | # [1] Row number as input | ||
| 191 | # [2] Returns removed row as array | ||
| 192 | |||
| 193 | my $self = shift; | ||
| 194 | my $row_number = shift; | ||
| 195 | |||
| 196 | if ( $row_number < 1 ){ | ||
| 197 | croak "Row number '$row_number' is out of range" | ||
| 198 | } | ||
| 199 | if ( $row_number > scalar( @{$self->{template}} ) ){ | ||
| 200 | croak "Row number '$row_number' is out of this spreadsheet" | ||
| 201 | } | ||
| 202 | |||
| 203 | my $x = $row_number - 1; | ||
| 204 | |||
| 205 | return @{ splice( @{$self->{template}}, $x, 1 ) } | ||
| 206 | } | ||
| 207 | |||
| 208 | sub RowCount { | ||
| 209 | |||
| 210 | #Contract: | ||
| 211 | # [1] Returns the number of elements in the spreadsheet | ||
| 212 | |||
| 213 | my $self = shift; | ||
| 214 | |||
| 215 | return scalar( @{$self->{template}} ) | ||
| 216 | } | ||
| 217 | |||
| 218 | sub CopySheetTo{ | ||
| 219 | |||
| 220 | #Contract: | ||
| 221 | # [1] Name of the 'SpreadSheet' tied pointer to hash as input | ||
| 222 | # [2] Method succeeds or everything dies ... | ||
| 223 | |||
| 224 | my $self = shift; | ||
| 225 | my $new_sheet = shift; | ||
| 226 | |||
| 227 | @{$new_sheet->{template}} = @{$self->{template}}; | ||
| 228 | foreach my $row ( @{$new_sheet->{template}} ){ | ||
| 229 | $row = [ @$row ] | ||
| 230 | } | ||
| 231 | } | ||
| 232 | |||
| 233 | sub convert_spreadsheet_address { | ||
| 234 | |||
| 235 | #Contract: | ||
| 236 | # [1] ('A12') spreadsheet address or row & column number eg ('12,1') as input | ||
| 237 | # [2] Method returns hash with keys {x} and {y} or or everything dies... | ||
| 238 | |||
| 239 | my $address = shift; | ||
| 240 | |||
| 241 | $address = uc( $address ); | ||
| 242 | |||
| 243 | if ( $address =~ /^\s*([0-9]+)\s*\,\s*([0-9]+)\s*$/ ){ | ||
| 244 | my $row_number = $1; | ||
| 245 | my $column_number = $2; | ||
| 246 | |||
| 247 | if ( $row_number < 1 ){ | ||
| 248 | croak "Row number in cell address '$address' is out of range" | ||
| 249 | } | ||
| 250 | if ( $column_number < 1 ){ | ||
| 251 | croak "Column number in cell address '$address' is out of range" | ||
| 252 | } | ||
| 253 | return { x => ( $row_number -1 ), y => ( $column_number -1 ) } | ||
| 254 | } | ||
| 255 | |||
| 256 | if ( $address =~ /^\s*([A-Z]+)\s*([0-9]+)\s*$/ ){ | ||
| 257 | my $column_AAA = $1; | ||
| 258 | my $row_number = $2; | ||
| 259 | |||
| 260 | if ( $row_number < 1 ){ | ||
| 261 | croak "Row number in cell address '$address' is out of range" | ||
| 262 | } | ||
| 263 | |||
| 264 | my $column_number = 0; | ||
| 265 | while( $column_AAA =~ s/^([A-Z])// ){ | ||
| 266 | $column_number = 26 * $column_number + 1 + ord ($1) - ord ("A"); | ||
| 267 | } | ||
| 268 | if ( $column_number < 1 ){ | ||
| 269 | croak "Column number in cell address '$address' is out of range" | ||
| 270 | } | ||
| 271 | return { x => ( $row_number -1 ), y => ( $column_number -1 ) } | ||
| 272 | } | ||
| 273 | croak "Cell address '$address' has wrong format" | ||
| 274 | } | ||
| 275 | 1; | ||
| 276 | __END__ |