editing excel files

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

Hi guys. I am trying to write a script to read in an Excel file (saved in text - tab delimeted format, so a text file really), search each file one line at a time to take out cells (i guess by using a regular expression for a pattern following a tab - what is the wildcard for tab?) that matches the pattern "(number/number)", eg (30 / 45). I then want to do the relevant calculation on this, ie in this case 30 divided by 5, and place the answer into the column in place of the original value in the cell. This way i can then re-open the text file in excel and the column of "(num/num)" values will be replaced with the corresponding numerical values. I also then want to take out whole columns and place them under previous columns, for example i want to take columns 4 and 5 and append them to the ends of columns 2 and 3 respectively. And to copy the contents of column 1 (title column) and append it to the bottom of column 1. If this makes sense to anyone i would be very grateful for your help. cheers

Comment on editing excel files

Replies are listed 'Best First'.
Re: editing excel files by jasonk (Parson) on Feb 13, 2003 at 16:44 UTC
Take a look at DBD::Excel, or Spreadsheet::ParseExcel and Spreadsheet::WriteExcel.	[reply]
Re: editing excel files by CountZero (Bishop) on Feb 13, 2003 at 16:58 UTC
In Perl a tab-character is encoded as \t. However, what you will try to do will not work: the tab-delimited file already contains the results of the formulas, so the cell which contains 30/45 (or rather the formula =30/45) gets "translated" to 0.666667 in the tab-delimited file. Of course if your users have failed to write "=30/45" and foolishly put "30/45" instead you need to correct this and Perl can help you. Conceptually I would do it as follows: open the tab-delimited file read one line at a time and split on \t 'for each' through the results of the split searching for the pattern "number/number" and calculating the result. replace the original value by the result print to an output file the values (changed as necessary) of columns 1, 2 and 3 join the values of columns 1, 4 and 5 with \t and push it in an array After having read all lines, output the contents of the array which contains the values of columns 1, 4 and 5 Surely some brighter monks than I will point out that the splitting, regex-ing and replacing can be done in one go through the use of map. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: editing excel files by dragonchild (Archbishop) on Feb 13, 2003 at 18:32 UTC
Learn about Text::CSV which, unlike its name, allows you to work with tab-delimited files. Also, you can work with it yourself, using split. ------ We are the carpenters and bricklayers of the Information Age. Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.	[reply]
Re: editing excel files by steves (Curate) on Feb 13, 2003 at 19:09 UTC
This might be a lot easier using DBD::Excel as suggested by jasonk. I've used Spreadsheet::WriteExcel and Spreadsheet::ParseExcel but not DBD::Excel. I'd be interested to hear if anyone's used it for updates like this.	[reply]
Re: Re: editing excel files by CountZero (Bishop) on Feb 13, 2003 at 21:58 UTC
DBD::Excel has its limits though as it expects the spreadsheet to be in a "typical" format with the first row having the field-names. If you have a more involved (or less strict) spreadsheet your outta luck with DBD::Excel. For such relatively simple things I prefer the tab-separated file way as the Spreadsheet-modules are not so simple to use. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]