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

[download]

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

In reply to Need help getting format along with the value from excel using Workdheet::ParseExcel by bennierounder

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.