Converting an image to an excel spreadsheet

2019-03-03

Convert an image to an excel spreadsheet

openxlsx is a pretty powerful package to read/write XLSX spreadsheet files. Attributes and styles can be set programmatically for each cell from within R.

So I’m going to use it to convert a JPG into a spreadsheet.

FAQ:

  • Why?
    • Because.
library(raster)
library(openxlsx)

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Read the R logo from the jpeg package
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
jpeg_filename <- system.file("img", "Rlogo.jpg", package="jpeg")

rlogo <- image_read(jpeg_filename) %>% 
  image_scale("50%") %>%
  image_data()


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Turn it into a raster object - this will get us hex colour codes at 
# every location
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
size  <- dim(rlogo[1,,])
rlogo <- as.raster(as.integer(rlogo)/255)
plot(rlogo)

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Turn the cell data into a data.frame (ready for openxslx)
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
rlogo <- as.character(rlogo)
dim(rlogo) <- size 
rlogo <- as.data.frame(rlogo)


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Figure out the hex colour code for every cell
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
colours <- rlogo %>% 
  gather(col, colour) %>%
  group_by(col) %>%
  mutate(row = seq(n())) %>%
  ungroup() %>%
  mutate(col = readr::parse_number(col))


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Create a blank data.frame the same size as the colours data.frame
# i.e the cells of this spreadsheet will only contain blanks, and then I'll
# set the "fill" colour to create the pixels
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
blank <- rlogo
blank[] <- ""


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# 1. Create a workbook
# 2. Add a worksheet
# 3. Add blank data of the required size to the worksheet
# 4. set the column widths so that the aspect ratio of final image is OK
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
wb <- createWorkbook("coolbutuseless")
addWorksheet(wb, "Rlogo", gridLines = TRUE)
writeData(wb, sheet = 1, blank, colNames = FALSE)
setColWidths(wb, shee=1, cols=seq(size[1]), widths = 2)



#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Set the style of the cells (in order to define the colour)
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
for (col_df in split(colours, colours$colour)) {
  cell_style <- createStyle(fgFill = col_df$colour[[1]])
  addStyle(wb, sheet = 1, cell_style, rows = col_df$col, cols = col_df$row, gridExpand = FALSE)
}


#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Save the workbook
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# openxlsx::saveWorkbook(wb, "crap.xlsx", overwrite = TRUE)

Grab this excel spreadsheet from my dropbox