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

poj I know reading this will bore you. But i cant upload my excel sheet here. I tried ur code. Its working well. But my problem is somewhat different. I am working on SDP data. I have a xlsx datasheet which created through "R" scripting. When I am trying to read the values in that sheet it returning the default value but not exact value.

When ur code runs it reverts like this 

AA            AB    AC    AD    AE            AF     AG             AH
+    AI    AJ
0.7          SubScore    2    SubScore98            SubScore 98       
+    SubScore        
0.448840495    7.48        7.48    99.66021651    2.26    99.78083497 
+   2.26    97.33    97.33
0.584906759    7.48        7.48    99.66985331    2.26    99.79158604 
+   2.26    97.33    97.33
0.632038673    7.48        7.48    99.67817389    2.26    99.79316929 
+   2.26    97.33    97.33
0.599607733    7.48    #N/A    7.48    99.6643225    2.26    99.792901
+4    2.26    97.33    97.33

[download]

The values in the excel sheet 

0.448840495    7.48        7.48    99.66021651    2.42    99.78083497 
+   2.45    99.34    99.34
0.584906759    6.93        7.48    99.66985331    2.42    99.79158604 
+   2.45    98.80    98.80
0.632038673    6.87        7.48    99.67817389    2.43    99.79316929 
+   2.45    98.69    98.69

[download]

The formulaes in the excel sheet 

AA 0.448840495386917   
AB =IF(AA15<AA$13,IF(AA15<AA$12,AA$8,((AA15-AA$13)*(AA$8-AA$9)/(AA$12-
+AA$13))+AA$9),IF(AA15>AA$14,AA$10,AA$9-((AA$13-AA15)*(AA$9-AA$10)/(AA
+$13-AA$14))))
AC [EMPTY CELL]
AD =IF(AC15<AC$13,IF(AC15<AC$12,AC$8,((AC15-AC$13)*(AC$8-AC$9)/(AC$12-
+AC$13))+AC$9),IF(AC15>AC$14,AC$10,AC$9-((AC$13-AC15)*(AC$9-AC$10)/(AC
+$13-AC$14))))
AE 99.6602165102621
AF =IF(AE15>AE$13,IF(AE15>AE$12,AE$8,((AE15-AE$13)*(AE$8-AE$9)/(AE$12-
+AE$13))+AE$9),IF(AE15<AE$14,AE$10,AE$9-((AE$13-AE15)*(AE$9-AE$10)/(AE
+$13-AE$14))))
AG 99.7808349699302
AH =IF(AG15>AG$13,IF(AG15>AG$12,AG$8,((AG15-AG$13)*(AG$8-AG$9)/(AG$12-
+AG$13))+AG$9),IF(AG15<AG$14,AG$10,AG$9-((AG$13-AG15)*(AG$9-AG$10)/(AG
+$13-AG$14))))
AI =SUMIF($C$14:$AH$14,"SubScore",C15:AH15)    (I need to read this va
+lue. By default its 97.33. Exact value is 99.34)
AJ =AI15

[download]

if we read the cell->value it have to read it as 99.34 but its reading it as 97.33 (Default values occur through formulae)

Note: Suppose if you open the sheet and delete the last empty (Nothing in that rows. Saved with formulae for next days) rows or if you change any small change (editing any cell overall the sheet) and save it. Then its showing the exact value 99.34.

But i dont know how that other cell doing what here. So, I think to open the xlsx sheet and delete a row(just for check) from last of the sheet through perl and check it either it showing 99.34 or 97.33.

Replies are listed 'Best First'.
Re^7: Delete entire row without using Win32::OLE
by poj (Abbot) on Sep 03, 2015 at 12:48 UTC

    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
[reply]

      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]
        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]

        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]

In Section Seekers of Perl Wisdom