Converting Hex/RGB values to HSL and writing the L values in to excel

[Dignitate]

Hello everyone,

I have an Excel sheet which only consists of a row of hex values which I want to transform into HSL values - I specifally only need the L values. These L should be written into the excel file. Following the code I'm trying and 5 hex number:

library("plotwidgets")
library("here")
library("openxlsx")

hexlist <- list("data/ahex.xlsx")
for (i in 1:1295) { 
   print(i)
  write.xlsx(col2hsl(i),"data/ahex.xlsx", asTable = F)
  } 
  

Hex
#DD5802
#B40215
#EF0001
#EE6C01
#C11D0C

This code only give the HSL values of the first hex number. But as stated i only need the L one from every 1295. I'm fairly new to Rstudio so please excuse if my code is horrible.

Thanks in advance

[jms]

Hi!

Your code has indeed a bit room for improvements , but no worries, we've all been there.

At first, you don't import your hex data correctly. To import an excel spreadsheet, you can use hexdata<-read.xlsx( "data/ahex.xlsx") which is also provided by the openxlsx package. The list function does not import data.

Here is a solution that allows you to create a data frame that contains the Hex value in one column, and the HSL in three corresponding columns. You can then use write.xlsx() to export the whole data frame, or you can extract only the L column and export it in the same way.

suppressPackageStartupMessages({
library(plotwidgets)
library(tidyverse)
})
# sample data
hexdata<-tibble::tribble(~ Hex,
                        "#DD5802",
                        "#B40215",
                        "#EF0001",
                        "#EE6C01",
                        "#C11D0C")

# apply to col2hsl function to each hex
my_outp<-hexdata%>%mutate(HSL=map(Hex,col2hsl)) 

# create an output with seperate HSL columns
my_outp<-my_outp%>%unnest_wider(HSL)
colnames(my_outp)<-c("Hex","H","S","L")

#show the output
my_outp
#> # A tibble: 5 x 4
#>   Hex          H     S     L
#>   <chr>    <dbl> <dbl> <dbl>
#> 1 #DD5802  23.6  0.982 0.437
#> 2 #B40215 354.   0.978 0.357
#> 3 #EF0001 360.   1     0.469
#> 4 #EE6C01  27.1  0.992 0.469
#> 5 #C11D0C   5.64 0.883 0.402

^{Created on 2020-11-10 by the reprex package (v0.3.0)}

You won't need to use a for loop for such a problem in R, but I have some pointers about your attempt that might be useful in the future.
What your loop does is, that it goes through the numbers from 1 to 1295, and for each of these numbers, it does two things:

1. It prints the number in R, i.e. it shows the number, but it does not safe it
2. It applies to col2hsl function on that number (not the hex value) and writes an excel document with the result. However, with every iteration, it creates a new spreadsheet with the same name as the previous one, therby it overwrites the spreadsheet every time.

Thus, the only output you get is the result for col2hsl(1295).
In general, you would need to provide your input data (hexdata) within the for-loop, and I would not recommend you to use write.xlsx or similar functions within a loop (creating a few thousand excel spreadsheets would make your code increadibly slow). But as I said before, there is no need to use a for loop for your kind of application.

I hope this wasn't too much unnesseary information and I could help with your problem!

[Dignitate]

Hello,
Thank you for your detailed answer - it helped me a lot and there wasn't any unnecessary information at all

Cheers