Let say I have a data matrix in R:
set.seed(1)
m <- matrix(runif(100),10,10)
[,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10]
[1,] 0.26550866 0.2059746 0.93470523 0.4820801 0.8209463 0.47761962 0.91287592 0.3390729 0.4346595 0.23962942
[2,] 0.37212390 0.1765568 0.21214252 0.5995658 0.6470602 0.86120948 0.29360337 0.8394404 0.7125147 0.05893438
[3,] 0.57285336 0.6870228 0.65167377 0.4935413 0.7829328 0.43809711 0.45906573 0.3466835 0.3999944 0.64228826
[4,] 0.90820779 0.3841037 0.12555510 0.1862176 0.5530363 0.24479728 0.33239467 0.3337749 0.3253522 0.87626921
[5,] 0.20168193 0.7698414 0.26722067 0.8273733 0.5297196 0.07067905 0.65087047 0.4763512 0.7570871 0.77891468
[6,] 0.89838968 0.4976992 0.38611409 0.6684667 0.7893562 0.09946616 0.25801678 0.8921983 0.2026923 0.79730883
[7,] 0.94467527 0.7176185 0.01339033 0.7942399 0.0233312 0.31627171 0.47854525 0.8643395 0.7111212 0.45527445
[8,] 0.66079779 0.9919061 0.38238796 0.1079436 0.4772301 0.51863426 0.76631067 0.3899895 0.1216919 0.41008408
[9,] 0.62911404 0.3800352 0.86969085 0.7237109 0.7323137 0.66200508 0.08424691 0.7773207 0.2454885 0.81087024
[10,] 0.06178627 0.7774452 0.34034900 0.4112744 0.6927316 0.40683019 0.87532133 0.9606180 0.1433044 0.60493329
which I then export to XLSX format (and look at the result):
library(xlsx)
XLSXtempfilename <- paste(tempfile(),"xlsx",sep=".")
write.xlsx(m,file=XLSXtempfilename,sheetName="Data",row.names=FALSE)
system("open",XLSXtempfilename)
In the above export the data is plain text.
How could one apply a color to each cell in xlsx output so that the result would like a heat map in excel? I am trying color cells directly in XLSX output. Then I would not need to do manually coloring using conditional formatting in Excel.
Below is shown an example on the kind of heat map coloring of the cells which I would like to obtain in the above xlsx output:
ggplot(melt(m),aes(x=Var1,y=Var2,fill=value)) +
geom_tile() +
scale_fill_gradient(low="green",high="red") +
geom_text(data=round(melt(m),2),aes(x=Var1,y=Var2,label=value))
