http://www.perlmonks.org?node_id=636555

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

I am writing an CSV_to_Excel script for my wife. In the process she wants me to open an existing Excel file, add the CSV data, and save a new Excel file with all of the data in it.

I am using Text::CSV_XS, Spreadsheet::ParseExcel and Spreadsheet::WriteExcel.

The problem is in the existing excel file. The data in the cells has information like

=56.43+45.31+34.41

I want to take the CSV value (i.e. 99.99) and just append it to get the following:

=56.43+45.31+34.41+99.99

When I get the cell's value from Spreadsheet::ParseExcel I get the result (136.15). I can add 99.99 to it but that isn't the goal (and you're welcome to argue with my wife about the reasoning of keeping those original values but you will not get anywhere ;-). Is there a way to get that original value and append a new value?

I read that ParseExcel only reads the results so I am open to any suggestions.

On a side note, I am using OpenOffice.org to view the Excel files and I am trusting that it is accurate.

-- rogueFalcon
Why do you people insist on doing things sdrawkcab? RogueFalcon.com

  • Comment on CSV to Excel (where I keep the excel formula)

Replies are listed 'Best First'.
Re: CSV to Excel (where I keep the excel formula)
by Cody Pendant (Prior) on Sep 02, 2007 at 04:32 UTC
    On CPAN, the TODO list includes "formula support". I don't know if this is the same issue as the "known problem" but it would seem that access to formulas, which is what you want, is problematic.

    Nobody says perl looks like line-noise any more
    kids today don't know what line-noise IS ...
[reply]

      Yeah... I read that but I was hoping for a different solution than Spreadsheet::ParseExcel. Everything else I have found seems really complex or it uses Spreadsheet::ParseExcel. I would like to think I could patch ParseExcel to get the formulas but didn't want to spend that much time on it.

      -- rogueFalcon
      Why do you people insist on doing things sdrawkcab?

[reply]
Re: CSV to Excel (where I keep the excel formula)
by strat (Canon) on Sep 02, 2007 at 10:08 UTC

    if you want to do it with a one-liner (or look at the code to do it in a program), get excelPerl from ExcelPerl 0.12 released and try something like

    Update: Sorry, I misread you. The code for ExcelPerl would be (if your col is number 2):

    excelPerl.pl -ane "$C->[2]->{FormulaLocal} .= '+99'" -f test.xls

    [download]

    If you have got a headline in the first row, just skip it with the additional parameter -h=1

    Code to convert excel to csv with formulas (doesn't match the question):

    perl excelPerl.pl -begin "use Text::CSV_XS; $csv = Text::CSV_XS->new( 
+{ binary => 1 } )" -ane "$csv->combine( map { $_->{FormulaLocal} } @C
+); print $csv->string, $/" -f test.xls

    [download]

    which produces something like

    a,b,a+b
1,4,=A2+B2
2,5,=A3+B3
3,2,=A4+B4
4,1,=A5+B5
5,5,=A6+B6

    [download]

    Best regards,
    perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"

[reply]
[d/l]
[select]
      I never said thanks but seriously... thank you. That made my life so much easier :-) I really appreciate it.

      -- rogueFalcon
      Why do you people insist on doing things sdrawkcab?

[reply]
Re: CSV to Excel (where I keep the excel formula)
by Cody Pendant (Prior) on Sep 02, 2007 at 04:40 UTC
    As far as I can tell, there's only one other way and that's through OLE; what platform are you on?

    Nobody says perl looks like line-noise any more
    kids today don't know what line-noise IS ...
[reply]
      Win32

      -- rogueFalcon
      Why do you people insist on doing things sdrawkcab?

[reply]
        Well, in that sense you're lucky then. You can use the Win::OLE modules instead of the Parsers.

        Nobody says perl looks like line-noise any more
        kids today don't know what line-noise IS ...
[reply]
Re: CSV to Excel (where I keep the excel formula)
by planetscape (Chancellor) on Sep 03, 2007 at 08:06 UTC

    Apologies for "coming late to the party." While I don't have a complete solution for you, I'd like to share some things that may help.

    I believe the advice you've already been given re: Win32::OLE is sound. You will want to use something like: rng.Formula (VBA; where rng is a Range Object that can refer to single or multiple cells) to access a cell's formula (the =56.43+45.31+34.41 part).

    I think you might benefit from replies to the thread VBA to Perl; marto has some great links on translating VBA to Perl, and I've got a few references to some utilities as well.

    An indispensable site for all things Excel- and spreadsheet-related is the aptly-named Spreadsheet Page, by John Walkenbach.

    Oh, and AFAIK, OpenOffice should be every bit as accurate as Excel. :-)

    HTH,

    planetscape
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom