in reply to Re^7: Delete entire row without using Win32::OLE
in thread Delete entire row without using Win32::OLE

Ohhh poj. You are becoming my lovely monk here. That's the reason I am not getting the values. Then how can i get values of that .xlsx through perl. I cannot open and save that excel sheet. Its has to be made automated in the sever of SDP on daily basis. Is there any other way to do that.

Note: At any instance I cannot open that excel manually. I want it as automated script.

  • Comment on Re^8: Delete entire row without using Win32::OLE

Replies are listed 'Best First'.
Re^9: Delete entire row without using Win32::OLE
by afoken (Chancellor) on Sep 04, 2015 at 06:37 UTC
    Note: At any instance I cannot open that excel manually. I want it as automated script.

    Consider using a different exchange format between R and Perl, and write an Excel file as the final step from Perl.

    I've no idea how powerful R is regarding writing output files, but writing CSV, JSON, or XML is easily done in most languages. Perl has modules on CPAN for reading all three formats, and can write old (*.xls) and new (*.xlsx) Excel formats, with using one of the spreadsheet writer modules.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]

      Thanks for suggesting me this. But its a heavy script taking data from several servers and a lot of permissions needed. It's not possible now. If you want to go that way it will take my month away.

      Please suggest me some other ways that possible to read that R script created sheet in perl. Once again thanks for suggestion

[reply]
Re^9: Delete entire row without using Win32::OLE
by poj (Abbot) on Sep 04, 2015 at 06:16 UTC

    I'm surprised you get any values at all, on my R generated test xlsx I get Can't call method "contents" on an undefined value. In theory I guess you could calculate the expression =SUMIF($C$14:$AH$14,"SubScore",C15:AH15) yourself in your perl script. In practice that depends on how fixed your columns and the formulas in them are likely to be.

    poj
[reply]
[d/l]
[select]

      I am getting the values which cell does not contains formulae. As you said I am trying to open my excel and save it through perl. Through `libreoffice --calc test.xlsx` we can open the file. But I dont know how to save. After saving we can kill the process through pid. I dont know that it is correct way or not. Tell me if you know how to save .xlsx through command line

[reply]

In Section Seekers of Perl Wisdom