Hi guys, does anyone know how to access the format from a cell when your taking the value from an excel file to copy it to another file. I am thinking it cannot be done, and if so it's not easy.
Does anyone have any clues as the documentation for Spreadsheet::ParseExcel is not helping me.
Here is my code
my $report_dir = shift;
my $report_type = shift;
my $file_data = shift;
my $msg = shift;
my $ext = shift || 'csv';
my $additional = shift || '';
my $time = UnixDate( "now", "%H.%M_%d%b%Y" );
my $THIS_DIR = $DIR . "/$report_dir";
unless ( -e $THIS_DIR ) { `mkdir -p $THIS_DIR`; }
my $report_file;
my $out_file;
my $cnt = 1;
while(1) { # need to make sure we have a unique filename
$report_file = $report_type . '_' . $time . '.' . $ext;
$report_file = $report_type . '_' . $time . "($cnt)" . '.' . $ext
+if ($cnt > 1);
$out_file = $THIS_DIR . "/" . $report_file;
last unless (-e $out_file);
die 'Could not make a unique filename' unless (++$cnt < 100); # s
+anity check
}
open( REPORTS, ">$out_file" ) || die "couldn't open the $out_file!";
binmode(REPORTS) if ($ext=~/xls/);
print REPORTS $file_data;
close(REPORTS);
my $outfile_tmp = $out_file . '_tmp';
open my $fh, ">$outfile_tmp" or die "Failed to open filehandle: $!"
+;
if ($OVERRIDE_ENCRYPTION) {
if ($ext =~ /XLS/i) {
my $out_workbook = Spreadsheet::WriteExcel->new($outfile_tmp);
# read the excel file.
my $parser = Spreadsheet::ParseExcel->new();
my $ret = '';
my $workbook = $parser->Parse($out_file);
return($ret) if (!defined $workbook);
for my $worksheet ( $workbook->worksheets() ) {
my $out_worksheet = $out_workbook->add_worksheet($worksheet->g
+et_name());
$out_worksheet->write(0, 0, 'Encypted');
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col )->value();
$out_worksheet->write(($row+1), $col, $cell);
}
}
}
$out_workbook->close();
print STDERR ">>>>ENCRYPTED File > $outfile_tmp >>> \n";
} elsif( $ext =~ /CSV/i) {
open( REPORTS, "$outfile_tmp" ) || die "couldn't open the $outfi
+le_tmp for encrytion!";
print REPORTS "encrypted\n";
print REPORTS $file_data;
close(REPORTS);
}
`mv $outfile_tmp $out_file`;
}
if ($stdout) {
$file_data = Reports::xls_to_csv($out_file) if ($ext eq 'xls');
print STDOUT $file_data;
return;
}
print STDERR "Output in: $out_file\n";
my $mail_subject = 'From Vision report: ' . $report_type . ' for ' .
+ UnixDate( "now", '%F' );
my $mail_message =
$msg . ' '
. $report_type
. ' generated on '
. UnixDate( "now", '%F' ) . "\n "
. $given_params . "\n"
. 'If you have any questions or suggestions about the report send
+queries to dev@crjkgmklhle.co.uk'
. "\n\nSincerely,\nReports Admin,\nCSL\n" . "\n\n$additional\n"
. $raw_params;
my $mail_status;
($email) and $mail_status = &sendEmail( $mail_subject, $out_file, $r
+eport_file, $mail_message, $email, $cemail, 'dev@crehggle.co.uk' );
# Save all non-jhfghg report on share drive
if ($email !~/hgf\.co\.uk/) {
Utils::ftp_send($vision_obj,$out_file,$report_file,'Reports');
}
if ( $mail_status or !$email ) {
($mail_status) and &log("Success: mail send\n");
return (1, $report_file, $THIS_DIR . "/" . $report_file);
} else {
&log("No Success : mail send\n");
return (0, $report_file, $THIS_DIR . "/" . $report_file);
}
} # end of sub output_report
The code is working to a certain extent. It is opening the xsl file that has been created and then copying it to another after inserting the word 'Encrypted' on the first row of the file. The only problem is the format of the original file is not carried over.
Has anyone got any idea how to put any code into this to allow the format to be obtained (from a cell) along with the value and passed over to the new document along with the value.
What sounds like a simple task is turning out to become a nightmare.
Any help is very much appreciated
