biohisham has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I have written this program which is supposed (Update!) TO WRITE TO AN EXCEL file a new set of records, everytime it runs, it works fine the first time I run it, the second time I run it though, it does not append records to the file like it is supposed to, (UPDATE!) I mean it does not update the excel file with new sets of records, in fact it does not add anything and I notice that the excel file size would increase everytime I run the program even though it does not append... I am aware that in programming i am still taking my very first 3 weeks, bt for the past 3 hours I could only come up with this program and got stuck different places, I don't think this is normal :( 
#!/usr/local/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;

our %information;

open FILE, ">>",'D:\My Code\Records.xls' or die "$!";
binmode FILE;
my $workbook=Spreadsheet::WriteExcel->new(\*FILE);
my @info = ('Name','Age','Specialization');

#adding a worksheet and writing headings
my $worksheet=$workbook->add_worksheet('Personal');
$worksheet -> set_column('A:C',20);
for (my $i=0; $i<=$#info;$i++){
        foreach my $key(@info){
                $worksheet->write(0,$i,$info[$i]);
                }
        }

#adding records and initializing a row counter since each value in the
+ hash will have a row
print "How many Records do you want to enter?\n";
my $records =<>;
chomp $records;
my $counter=1;


while($counter<=$records){
        my $col_counter=0;
        foreach my $key (@info){
                print "Enter $key\n";
                $information{$key}=<>;
                chomp $information{$key};
                $worksheet->write($counter, $col_counter, $information
+{$info[$col_counter]}); #indexing @info will give $key
                $col_counter++  if ($col_counter<=$#info);
                }
        $counter++;

        }

[download]
any feedback on how to improve this programming style, and on how to make this program does what it is supposed to will be greatly appreciated

Replies are listed 'Best First'.
Re: a stubborn excel file
by Illuminatus (Curate) on Jun 23, 2009 at 19:53 UTC
    Spreadsheet::WriteExcel only knows how to write spreadsheets -- not how to read them in. By opening the file in 'append' mode, you are simply catenating another complete spreadsheet file to your existing complete file. I guess excel does not care if there is extraneous data after the file. I think you want to:
    1. test if the file is present
    2. use Spreadsheet::ParseExcel to read it if it is there
    3. make your modifications, then use WriteExcel to write it
[reply]
Re: a stubborn excel file
by toolic (Bishop) on Jun 23, 2009 at 20:03 UTC
    From the POD: MODIFYING_AND_REWRITING_EXCEL_FILES
    An Excel file is a binary file within a binary file. It contains several interlinked checksums and changing even one byte can cause it to become corrupted. As such you cannot simply append or update an Excel file. The only way to achieve this is to read the entire file into memory, make the required changes or additions and then write the file out again.
[reply]
      wow, this is way difficult than what I have imagined... I think my answer would be in using Spreadsheet::ParseExcel::SaveParser since it can let me get the current cells and then update them, but the syntax is a bit over my head anyways... I will try it though
[reply]
Re: a stubborn excel file
by afoken (Chancellor) on Jun 23, 2009 at 19:53 UTC

    Your code concatenates several excel files into the output file. I'm surprised that Excel accepts this broken file as input. The Excel file format is a binary dump, simply appending yet another dump does not work. If you want to CHANGE the data inside an existing file, you need to read that file into memory, modifiy the in-memory-representation of the file, and write back the changed structure as a single binary dump to the file, replacing the original dump, just as Excel does. Spreadsheet::ReadExcel may help. Choosing a different file format is another option. Simple CSV files can be appended to, but they don't give you formulas, macros, and formats.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
Re: a stubborn excel file
by imrags (Monk) on Jun 24, 2009 at 11:02 UTC
    If you are on Win32 box, i'd suggest use of "Win32::OLE"
    To get you started, here's a small piece of code: 
    use Win32::OLE;
$Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OL
+E->new('Excel.Application');
$Excel->{'Visible'} = 1; #to make it visible
#my $Book = $Excel->Workbooks->Open('E:/perl scripts/abc.xls') || die 
+"could not open excel file"; # open Excel file
 
$Excel->{SheetsInNewWorkBook} = 1; 
$workbook = $Excel->Workbooks->Add(); 
$Sheet = $workbook->Worksheets(1); 
$Sheet->{Name} = "New WorkSheet"; 
$Sheet->Range("A1")->{Value}="My cell number 1";
$workbook->SaveAs({Filename =>'abc.xls',  FileFormat => xlWorkbookNorm
+al});

    [download]
    Raghu
[reply]
[d/l]
Re: a stubborn excel file
by VinsWorldcom (Prior) on Jun 24, 2009 at 12:22 UTC
    Spreadsheet::WriteExcel can not modify existing Excel spreadsheets. You will need Win32::OLE to accomplish this.

    Have a look at Parse Excel Spreadsheets To Single before *potentially* reinventing the wheel.

[reply]

Back to Seekers of Perl Wisdom