Colors according to value (Excel-Export)

classic Classic list List threaded Threaded
9 messages Options
Mat
Reply | Threaded
Open this post in threaded view
|

Colors according to value (Excel-Export)

Mat
Hello together,

i have a list of numbers, like this one
A    B     C
1.1 1.2  1.3

i export this list into an excel file and want now color these numbers according to her value.
I want every color under 1,25 is red, and every color over 1,25 is green.

How can i do this with "RDCOMClient" ?

I know how to colour a complete row, like this task:

U3R <- sh$Range(zellen_blue)
U3RF <- U3R$Font()
U3RF[["Bold"]] <- TRUE
U3RF[["Size"]] <- "10"
U3RF[["Color"]] <-"-10526881"

but how can i color numbers accordung to her value?

Thanks.

Mat
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Bart Joosen
This post has NOT been accepted by the mailing list yet.
I think you can use Cells(row,column) instead of range.
Mat
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Mat
This post was updated on .
i tried it this way, but this won't help.

O3R <- sh$PT_IST(zellen_other)
O3RF <- O3R$Font()
O3RF[["Bold"]] <- TRUE
O3RF[["Size"]] <- "10"
O3RF[["Color"]] <-"5287936"

perhabs you can help me, how i can difference between the values of more and less than 1,25?

Thanks.

Mat
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Bart Joosen
This post has NOT been accepted by the mailing list yet.
Could you post a reproducible example?
That way I can test some stuff.
Mat
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Mat
here is one:
i have this in my excel sheet
                                               PT_IST
Projekt A                                 1,1
Projekt B                                1,15
Projekt C                                 1,2

Difference 1,15

This number is generated from the data above and is ok.
The Excel sheet was generated every day. And the numbers can change from day to day.
I know how to color the whole row, but i don't know how to color the number "Difference --> 1,15" according to her value, like: if>1,2 <- RED, if <1,2 <- GREEN

Thanks.

Mat
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Bart Joosen
This post has NOT been accepted by the mailing list yet.
I meant a working example:
eg:
require(RCOM)
sh <- ...
....

that way I can follow the steps you are taking.

Bart
Mat
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Mat
i tried it this way. With this Code the Row "Difference" gets complete green. But i want the color change to red according to a different value.

highlight_other<-out.ex1[(out.ex1$AUFTR_NAME=="Difference"),]$LFD2
zellen_other<-paste(paste("A",highlight_other,sep=""),paste("S",highlight_other,sep=""),sep=":")


# Libray
library(RDCOMClient)

#Start Excel
xl <- COMCreate("Excel.Application")  

#Öffnen von Excel
xl[["Visible"]] <- FALSE                      
xl[['DisplayAlerts']] <- FALSE

#Datei öffnen
wkbk <- xl$Workbooks()$Open(pfad)      
 
#Formel einfügen
sh <- xl$ActiveSheet()

#Difference
O3R <- sh$Range(zellen_other)
O3RF <- O3R$Font()
O3RF[["Bold"]] <- TRUE
O3RF[["Size"]] <- "10"
O3RF[["Color"]] <-"5287936"
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Bart Joosen
This post has NOT been accepted by the mailing list yet.
Hi,

maybe you can try this (not tested)

for (i in rows) {
  for (j in columns) {
       r = sh$Range(sheet$Cells(i,j), sheet$Cells(i, j))  
      tmp = r[["Value"]]
    if (tmp < 1.25) r[["Color"]] <-"5287936"
   }
}

you just need to check how many columns and rows you have and then loop over this.
If you use R to first send a list of values to Excel, you can use a bit of different setup to check which values should be coloured and only use this indexes to loop instead of looping through all the cells.


Bart
Reply | Threaded
Open this post in threaded view
|

Re: Colors according to value (Excel-Export)

Bart Joosen
This post has NOT been accepted by the mailing list yet.
Typo: sheet$Cells should be sh$Cells:

for (i in rows) {
  for (j in columns) {
       r = sh$Range(sh$Cells(i,j), sh$Cells(i, j))  
      tmp = r[["Value"]]
    if (tmp < 1.25) r[["Color"]] <-"5287936"
   }
}

Maybe you need to convert tmp to numeric, not sure about that

Good luck

Bart