Writing into an existing excelsheet

Satish@Stag has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Writing into an existing excelsheet by Sinistral (Monsignor) on Sep 19, 2007 at 14:14 UTC
First off, n8g's points are very well taken. If I may, I would like to add a little. If you are using a fully Win32 environment, and actually have Excel as the environment for manipulating your spreadsheets, then Excel itself, perhaps with the aid of Visual Basic for Applications (the programming language built into Excel) would be an ideal solution. If you are using a non-Windows platform to do the spreadsheet manipulation, then the combination of Spreadsheet::ParseExcel and Spreadsheet::WriteExcel are what you are looking for. ParseExcel will allow you to read the Excel file and pick and choose cells and retrieve the data. Once you have that, you can then use WriteExcel to create your new Excel file. If you really do want to use Perl, and you are on a Windows platform, then I recommend the ActiveState Perl distribution. It includesWin32::OLE to allow you to programmatically work with Windows applications. If you use a barebones Windows Perl, you can get Win32::OLE from CPAN, if you don't want to install the ActiveState Perl. You can find details from various sites using the proper Win32::OLE Perl Excel search. What you'll have to do in this case is translate the API that is included in MS Office from the Visual Basic for Applications to the Perl Win32::OLE equivalent. As an aside, for large spreadsheets, the Win32::OLE solution is faster than the Spreadsheet::WriteExcel solution, at least in my experience from a while back. The WriteExcel folks might have greatly improved performance, and I am not criticizing their excellent work, but it is a consideration.	[reply]
Re^2: Writing into an existing excelsheet by philc (Acolyte) on Sep 20, 2007 at 06:43 UTC
Regarding Spreadsheet::ParseExcel, if you have a choice between this PM and doing it in Excel manually or with VB, go with the latter. Spreadsheet::ParseExcel is bit unweildly, slow and a memory hog in my experience. If using VB is not your thing and you're on Windows box, then definitely try Win32:OLE...a much cleaner interface than Spreadsheet::ParseExcel...just my humble two cents...I agree though that it may be best to do it via Excel/VB. pc	[reply]
Re: Writing into an existing excelsheet by n8g (Sexton) on Sep 19, 2007 at 13:32 UTC
I may be out of line suggesting this here but maybe Perl is not the best tool for this job. If you just want to share information between excel spreadsheets you can use references and formulas to do that. I might be misunderstanding your request though. Perhaps you could add some detail. Are you always reading from the same spreadsheets or does that change often? What are the source spreadsheets location relative to the one you are using to aggregate the data to? Are the source documents readily accessible to people who will be using the final spreadsheet? These questions should help determine whether or not Perl is the right tool for the job.	[reply]
Re^2: Writing into an existing excelsheet by Satish@Stag (Novice) on Sep 19, 2007 at 14:04 UTC
I have some 50 excel files which contains say 10 columns in each xls. From these 50 xls files, I want to generate a single xls report which contains say only 5 columns data from each 50 excel files. i.e., some data (say Col A, B, C, D, E, F...) from 50 excel sheets to be extracted to single report (say Col A, D, E only) with single worksheet only...	[reply]
Re: Writing into an existing excelsheet by marto (Cardinal) on Sep 19, 2007 at 14:54 UTC
How does this differ from your previous question? Was there something you did not understand with the answers you were given? Martin	[reply]
Re: Writing into an existing excelsheet by moritz (Cardinal) on Sep 19, 2007 at 13:43 UTC
You could assemble the data somewhere else, for example in a database, and then write it into the target spreadsheet when you're finished, or you could override it every time you assemble new data.	[reply]