Re^7: Delete entire row without using Win32::OLE

Has the spreadsheet created by R been opened in Excel and saved before you read it with Perl ?

see this note in the R package openxslx http://cran.r-project.org/web/packages/openxlsx/openxlsx.pdf (page 34)

Formulae written using writeFormula to a Workbook object will not get picked up by read.xlsx().
This is because only the formula is written and left to be evaluated when the file is opened in Excel.
Opening, saving and closing the file with Excel will resolve this.

poj

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

Replies are listed 'Best First'.
Re^8: Delete entire row without using Win32::OLE by ravi45722 (Pilgrim) on Sep 04, 2015 at 04:07 UTC
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.	[reply]
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]
Re^10: Delete entire row without using Win32::OLE by ravi45722 (Pilgrim) on Sep 04, 2015 at 07:13 UTC
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]
Re^10: Delete entire row without using Win32::OLE by ravi45722 (Pilgrim) on Sep 04, 2015 at 07:08 UTC
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]

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

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