Export data with colors in excel from heatmap

General
1

I am very much new in r. Can anyone can help to extrcat data from genereated heatmap with color in excel using openxlsx.
I am able to extract data but color is missing in excel.

2

I don't know how to do it with openxlsx, but the {tidyxl} package has a function called xlsx_formats. I can get cell background colours with

filename = "data/heatmap_demo.xlsx"
formats <- tidyxl::xlsx_formats(filename)
formats$local$fill$patternFill$fgColor$rgb |>  stringr::str_replace("^FF", "#")

Note that excel prefixes hex colour values with FF, and we want #, "FFE97132" should be "#E97132" hence the str_replace.

Also note that this doesn't work with conditional formatting.

3

filename = "hmap/heatmap_demo.xlsx"
formats <- tidyxl::xlsx_formats(filename)
Error: 'hmap/heatmap_demo.xlsx' does not exist in current working directory ('/Users/abuhenamostafakamal/Desktop/R-Studio_Working Space').
formats$local$fill$patternFill$fgColor$rgb |> stringr::str_replace("^FF", "#")
Error: object 'formats' not found

It is showing some error.

4

You'll have to make sure that the filename is a referring to an excel file that exists on your computer, and you'll have to install tidyxl package. Either use the Packages pane to install it or run: install.packages("tidyxl") in the console.

5

library(tidyxl)

filename = "~/Desktop/R-Studio_Working Space/heatmap_demo.xlsx"
formats <- tidyxl::xlsx_formats(filename)
formats$local$fill$patternFill$fgColor$rgb |> stringr::str_replace("^FF", "#")
[1] NA

Excel file is empty, How do I indicate my heatmap where value should be extracted. I believe I am missing that part.
Thank you so much for your kind help.

6

Hi @abuhena7 ,

Is there a reason you need to import the heat map itself? You could import the data that the heat map is based on, and use that data to produce a heat map in R.

7

Thanks for asking. Yes, we want to compare the exported Excel color and the heatmap color side by side.

8

You could do this by creating an image of the Excel heat map, then creating an image of the R heat map from the Excel numerical data, and then comparing the images side by side. Why would you need to import the Excel heat map into r?

9

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.