1

I'm writing a code that reads a CSV file, and generates two XLSX files, depending if the sites needed updates and where the location is. For example I would have sample data such as:

Data example 1

And the issue I'm having now, is that I want to apply Excel table style "Olive Green, Table Style Medium 18". I have an option where I have to pass a workbook, but since I'm not directly creating a workbook but rather reading a CSV file, how would I continue this?

Right now, I'm just using write.xlsx() to export my data to Excel, but I would like to apply a formatting to the table.

Sample code: 

#Determine the input and output parameters 
input_file <-  choose.files()
output_eu <- "eu.xlsx"
output_noteu <- "noteu.xlsx"

#list of EU countries 
eu <- c("Andorra","Austria","Belarus","Belgium","Bosnia and Herzegovina","Bulgaria","Croatia","Czech Republic","Denmark","Estonia","Finland","France","Germany","Greece","Hungary","Iceland","Ireland","Italy","Latvia","Liechtenstein","Lithuania","Luxembourg","Malta","Moldova","Monaco","Montenegro","Netherlands","Norway","Poland","Portugal","Romania","Russia","San Marino","Serbia","Slovakia","Slovenia","Spain","Sweden","Switzerland","Ukraine","United Kingdom")

#reading the csv table
d <- read.table(input_file, sep = ";", header = TRUE, check.names = FALSE) 

# get all cases where there is some text in the Update field
updates <- d[d$Update != "", ]

#within updates are there countries in EU
i <- updates$Country %in% eu

eu_up <- updates[i,]
noteu_up <- updates[!i,]

#Creating the excel files 
library(openxlsx)
write.xlsx(eu_up, output_eu) 
write.xlsx(noteu_up, output_noteu)

Update1: Added the sample code to show that I use 1 CSV file that goes into two excel files.

Updated Code: 

#Determine the input and output parameters 
input_file <-  choose.files()
output_eu <- "eu.xlsx"
output_noteu <- "noteu.xlsx"

#list of EU countries 
eu <- c("Andorra","Austria","Belarus","Belgium","Bosnia and Herzegovina","Bulgaria","Croatia","Czech Republic","Denmark","Estonia","Finland","France","Germany","Greece","Hungary","Iceland","Ireland","Italy","Latvia","Liechtenstein","Lithuania","Luxembourg","Malta","Moldova","Monaco","Montenegro","Netherlands","Norway","Poland","Portugal","Romania","Russia","San Marino","Serbia","Slovakia","Slovenia","Spain","Sweden","Switzerland","Ukraine","United Kingdom")

#reading the csv table
d <- read.table(input_file, sep = ";", header = TRUE, check.names = FALSE) 

# get all cases where there is some text in the Update field
updates <- d[d$Update != "", ]

#within updates are there countries in EU
i <- updates$Country %in% eu

eu_up <- updates[i,]
noteu_up <- updates[!i,]


#importing openxlsx library 
library(openxlsx)

#create the workbook for each one 
wb_eu <- createWorkbook()
wb_xeu <-createWorkbook()

#adding the data to each corresponding workbook 
addWorksheet(wb_eu, "European Sites") 
addWorksheet(wb_xeu, "Non-European Sites")

#write our tables into each 
writeDataTable(wb_eu, 1, eu_up, startRow=1, startCol=1, tableStyle="TableStyleLight11")
writeDataTable(wb_xeu, 1,  noteu_up, startRow=1, startCol=1, tableStyle="TableStyleLight11")

#setting our column widths 
setColWidths(wb_eu, 1, cols=1:26, widths = "auto") 
setColWidths(wb_xeu, 1, cols=1:26, widths = "auto") 

#saving our workbooks 
saveWorkbook(wb_eu, "European Sites updated.xlsx", overwrite = TRUE) 
saveWorkbook(wb_xeu, "Non-European Sites updated.xlsx", overwrite = TRUE)
Improve this question
3
  • Hello, please avoid uploading pictures of your data and instead upload a small sample of it. Also we will need to see your code in order to test it out. Try editing this question with a reproducible example that contains a small sample of your data. Commented Nov 6, 2018 at 15:55
  • read community guidelines and please post reproducible example...u r question nice but the way u r asking is something. so please avoid attaching or posting images in the question Commented Nov 6, 2018 at 15:59
  • There are several options. Some years ago, I have decided to use the xlsx package to create nicely formatted Excel file output. An alternative might be the openxlsx package which might have matured in the meantime. Commented Nov 6, 2018 at 16:04

1 Answer 1

Reset to default
1

library(openxlsx) works well for this.

library(openxlsx)

First you need to create a workbook:

wb <- createWorkbook()

Then add two worksheets to it:

addWorksheet(wb, "EU")
addWorksheet(wb, "NOTEU")

Then let's write our two tables:

writeDataTable(wb, 1, eu_up, startRow = 1, startCol = 1, tableStyle = "TableStyleMedium18")
writeDataTable(wb, 2, noteu_up, startRow = 1, startCol = 1, tableStyle = "TableStyleMedium18")

saveWorkbook(wb, "Tables_with_Formatting.xlsx", overwrite = TRUE)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you!! It's working now that I added the createWorkbook(), I added an updated code to reflect what I've done!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.