Revered Monks,
I have a process in which date is populated in a MS Excel Sheet and posted on an MS Portal Server. The data population in a excel sheet from a Sybase Result set is done using Spreadsheet::WriteExcel

The problem is when a column has a string like "038E1234" it is interpreted as exponential form and displayed as "3.8E+123".

I have checked that the format of the cells is indeed text.This is because the leading *zero* is not truncated for strings like "038123A45" etc

This exponential display occures when there is an "E" in the string.

How can this be avoided?
~Mihir 

#!/ms/dist/perl5/bin/perl5.6

use strict;
use lib '/ms/dev/clienttech/datamover/0.7/src/lib/';
use FindBin;
use lib "$FindBin::Bin";
use MSDW::Version 'Spreadsheet-WriteExcel' => '2.16',;
use Sybase;
use Spreadsheet::WriteExcel;
use DataMover::Messenger;

my ($srv, $db, $usr, $pwd, $process) = ( $ENV{"SRC_SRV"}, $ENV{"SRC_DB
+"}, $ENV{"SRC_USR"}, $ENV{"SRC_PWD"},  $ENV{"PROCESS_NAME"} );

my $report_number= $ENV{"REPORT_CODE"};
my $dbh = connectsybase($srv, $db, $usr, $pwd, $process);
my $reportpath = $ENV{"SPREADSHEET_PATH"};
my $workbook = Spreadsheet::WriteExcel->new($reportpath);
my $i = 1;

while (1) {

    my $envsrcqry = "SRC_QRY".$i; 
    my $envsrcqryfile = "SRC_QRY_FILE".$i; 
    my $envworksheet = "WORKSHEET_NAME".$i;

    #read query from file if required
    if(isblank($ENV{$envsrcqry})) {
        if(!isblank($ENV{$envsrcqryfile})) {
                $ENV{$envsrcqry} = getfilecontents($ENV{$envsrcqryfile
+});
        } else {
            last; 
            printlog("Both environments $envsrcqry and $envsrcqryfile 
+are blank.");
        }
    }

    my $qry = $ENV{$envsrcqry};
    my $wsname = $ENV{$envworksheet};

    my $sth = querysybase($dbh, $qry);
###########################################################
# this new logic will update variable $sth_statistics by selecting fro
+m the REPORT_LOG Table
#my $qry_statistics = "select OPEN_DATE, CLOSED_DATE, NO_OF_DAYS_OPEN 
+" ." C01, C02, C03, C04, C05, C06, C07, C08, C09, C10, C11, C12, C13,
+ C14,C15, C16, C17 " . " from PB_DBDQ..DQDS_REPORT_LOG where convert(
+varchar(10), PROCESS_DATE,103)=convert(varchar(10), getdate(),103)"."
+ and REPORT_CODE = '$report_number'";

my $qry_statistics = "select OPEN_DATE, CLOSED_DATE, NO_OF_DAYS_OPEN,C
+01, C02, C03, C04, C05, C06, C07, C08, C09, C10, C11, C12, C13, C14,C
+15, C16, C17  from PB_DBDQ..DQDS_REPORT_LOG where convert(varchar(10)
+, PROCESS_DATE,103)=convert(varchar(10), getdate(),103) and REPORT_CO
+DE = '$report_number'";

my $sth_statistics = querysybase($dbh, $qry_statistics);

# this logic will access the REPORT_LOOKUP Headings Metadata
#my $qry_headings = "select * from PB_DBDQ..REPORT_LOOKUP where REPORT
+_CODE='$report_number' ";

my $qry_headings = "select 'Open Date' as A1, 'Closed_Date' as A2, 'Da
+ys Open' as A3, "."C01, C02, C03, C04, C05, C06, C07, C08, C09, C10, 
+C11, C12, C13, C14, C15, C16, C17, C18, C19, C20 "."from PB_DBDQ..REP
+ORT_LOOKUP where REPORT_CODE = '$report_number' ";

my $sth_headings = querysybase($dbh, $qry_headings);
my @sth_columns = $sth_headings;
###########################################################
    my @cols = @{$sth->{NAME}};
    my $colnum = $#cols + 1;

    printlog ("Source query contains $colnum columns: @cols");
    my $worksheet = $workbook->add_worksheet($wsname);

    $worksheet->keep_leading_zeros();
###################################################
#        foreach (@sth_columns) {
#          $worksheet->write(0, 0, $_);
#         }

###########################################################
my $headerformat = $workbook->add_format(bold => 1, num_format => '@')
+;
my $dataformat = $workbook->add_format(num_format => '@');
printlog ("Writing Header");

       $worksheet->write_col(0, 0, $sth_headings->fetchall_arrayref(),
+ $headerformat);

       printlog ("Writing Data");

       $worksheet->write_col(1, 0, $sth_statistics->fetchall_arrayref(
+), $dataformat);

       my $rownum = $sth_statistics->rows();

       printlog ("Successfully wrote $rownum rows of data");
       print $rownum;
       $sth_statistics->finish();

       $i++;
}

$workbook->close();
#printlog ("Spreadsheet closed");

disconnectsybase($dbh);

###########################################################

sub getfilecontents {
  my $qryfile = $_[0];
  my $sql;
  if (!open(SQL, $qryfile)) {
    exitmessage("Could not open file $qryfile.");
  }
  while (<SQL>) {
    $sql .= $_;
  }                       
  return $sql;
  printlog $sql;
}

sub isblank {

        my $input = $_[0];
        my $ret = 0;
        if ($input eq "") {
          $ret = 1;
        }
        return $ret;
}
__END__

this code will not be executed because its after the end statement

#my $dataformat = $workbook->add_format(num_format => '@');
#print (@{$sth->fetchrow_arrayref()});
#print (@{$sth->fetchall_arrayref()->[1]});


#my ($srv, $db, $usr, $pwd, $process, $qry) = ("NYP_IPB11", "CIA_MIGRA
+TE", "ciatasks", "ciatasks", "write_report", "exec SP_FB_EMPLOYEE_EXT
+ENSION");

#$worksheet->write_row(1, 0, $sth->fetchrow_arrayref());
#$worksheet->write_row(2, 0, $sth->fetchrow_arrayref());


#while (my $row = $sth->fetchrow_arrayref() ) {
#        print("@$row\n");
#       $worksheet->write_row(1, 0, $row);
#}


#$worksheet->write(@{$sth->{NAME}});



#    print(@{$sth->{NAME}});
#    $sth->dump_results();    
#    while (my @row = $sth->fetchrow_array ) {
#        print("@row\n");
#    }

[download]

20070810 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

In reply to string incorrectly displyed in exponential form in MSExcel by MH

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.