Open Excel while writing & Save file

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

Hello, Please let me know: 1) How to repeatedly save the excel file that I am writing data to, sometimes my code, equipments dont work and all the collected data gets lost. I wanna save the excel file while it is being written. The data in excel is only visible if my .pl file completes execution. If for some reason it gets stuck the data is lost. 2) Also is there a way to open the excel file while data is being sent to the file? (My program controls various equipments and takes 6-7 hours to complete) therefore I wanna see whats being written to the excel file & save repeatedly Please see the folloing code that I am using... "


   use strict;
               use Spreadsheet::WriteExcel;
       
               # Create a new Excel workbook called perl.xls
               my $workbook = Spreadsheet::WriteExcel->new("XYZ.xls");
       system("C:/Perl_Scripts/XYZ.xls");

               # Add some worksheets
               #my $sheet1 = $workbook->add_worksheet();
               my $sheet1 = $workbook->add_worksheet("Summary");
                         .............................................
+...........................................
#this loops for 6-7 hours to collect data
$sheet2->write($i,0,$freqbk);}
$sheet2->write($i,$f+1,$pwr);
[download]

Comment on Open Excel while writing & Save file Download Code

Replies are listed 'Best First'.
Re: Open Excel while writing & Save file by ikegami (Patriarch) on Jan 03, 2010 at 03:06 UTC
It might be less fragile if you saved the data to a TSV file and imported it into Excel at the end of the process. If you turned autoflushing on for the TSV file handle, there would be next to no data loss on failure.	[reply]
Re: Open Excel while writing & Save file by bobr (Monk) on Jan 03, 2010 at 08:52 UTC
You can also catch errors with eval or __DIE__ signal and save before quit. --Roman	[reply]
Re^2: Open Excel while writing & Save file by afoken (Chancellor) on Jan 03, 2010 at 09:21 UTC
You should not mess with `$SIG{__DIE__}` unless you have excellent reasons to do so. A simple `eval { ... }` is sufficient to trap any exception thrown by `die` calls, plus it avoids spooky actions-at-a-distance. `$SIG{__DIE__}` has some nasty side effects inside explicit or implicit `eval` blocks. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re: Open Excel while writing & Save file by Ollie Jones (Novice) on Jan 04, 2010 at 03:07 UTC
I am afraid you can't do what you're trying to do directly with WriteExcel. The Excel data format isn't your typical Unix-like data stream that can be written to by one process and read by another. If I were doing this, I would consider splitting the program into two pieces. One piece would write the data that comes out of your equipment control to some kind of simple log file format. This would run continuously throughout your data run. If you do this right it will be very simple, so you won't run a big risk of losing data or crashing your run because of a software error. The second piece would read that log file format and use WriteExcel to pop out an up-to-the-minute copy of your spreadsheet. It would run to completion quickly, rather than having to wait for your equipment. You could run this anytime you need to see results. For example, you might run it every hour during your seven-hour run to see how things are going.	[reply]