Export Data Frames to Excel via Xlsx with Conditional Formatting

export data frames to Excel via xlsx with conditional formatting

Try this out. I changed a few things, including the a slight change to the call to Fill and limiting the cells included for consideration to those with numeric data. I used lapply to apply the conditional formatting.

  cols <- sample(c(1:5), 1) # number of columns to vary to mimic this unknown
  label <- rep(paste0("label ", seq(from=1, to=10)))
  mydata <- data.frame(label)
  for (i in 1:cols) {
    mydata[,i+1] <- sample(c(1:10), 10)
  }
# exporting data.frame to excel is easy with xlsx package
  sheetname <- "mysheet"
  write.xlsx(mydata, "mydata.xlsx", sheetName=sheetname)
  file <- "mydata.xlsx"
# but we want to highlight cells if value greater than or equal to 5
  wb <- loadWorkbook(file)              # load workbook
  fo <- Fill(foregroundColor="yellow")  # create fill object
  cs <- CellStyle(wb, fill=fo)          # create cell style
  sheets <- getSheets(wb)               # get all sheets
  sheet <- sheets[[sheetname]]          # get specific sheet
  rows <- getRows(sheet, rowIndex=2:(nrow(mydata)+1)     # get rows
                                                         # 1st row is headers
  cells <- getCells(rows, colIndex = 3:(cols+3))       # get cells
# in the wb I import with loadWorkbook, numeric data starts in column 3
# and the first two columns are row number and label number

  values <- lapply(cells, getCellValue) # extract the values

# find cells meeting conditional criteria 
  highlight <- "test"
  for (i in names(values)) {
    x <- as.numeric(values[i])
    if (x>=5 & !is.na(x)) {
      highlight <- c(highlight, i)
    }    
  }
  highlight <- highlight[-1]

lapply(names(cells[highlight]),
       function(ii)setCellStyle(cells[[ii]],cs))

saveWorkbook(wb, file)

How to write dataframe to excel with conditional formatting in Python?

Apart from the Pandas styler you could use Excel's conditional formatting to get a similar, but dynamic, effect. For example:

import pandas as pd
from random import randint

x = [randint(0, 1) for p in range(0, 10)]

sample_dict = {"Col1": [randint(0, 1) for p in range(0, 10)],
               "Col2": [randint(0, 1) for p in range(0, 10)],
               "Col3": [randint(0, 1) for p in range(0, 10)],
               "Col4": [randint(0, 1) for p in range(0, 10)],
               "Col5": [randint(0, 1) for p in range(0, 10)],
               "Col6": [randint(0, 1) for p in range(0, 10)]}

sample = pd.DataFrame(sample_dict)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_conditional.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
sample.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add a format.
format1 = workbook.add_format({'bg_color': 'orange'})

# Get the dimensions of the dataframe.
(max_row, max_col) = sample.shape

# Apply a conditional format to the required cell range.
worksheet.conditional_format(1, 1, max_row, max_col,
                             {'type':     'formula',
                              'criteria': '=$B2<>B2',
                              'format':   format1})

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

How to export the Pandas conditional formatting to an Excel file

you can't export pandas conditional formatting but you can use the module xlsxwriter

https://xlsxwriter.readthedocs.io/example_conditional_format.html#ex-cond-format

Read in single xlsx file, perform conditional formatting and export as as multiple xlsx files in R

You could first create a function using openxlsx to create the workbook.

Then you could split the dataset by division and use map to call the workbook creation over the divisions:

library(openxlsx) 
library(purrr)
library(dplyr)

threshold <- 6

create.workbook <- function(df,threshold) {
 wb <- createWorkbook() 
 highlight.Style <- createStyle(fontColour = "red", fgFill = "yellow")
 addWorksheet(wb, "data")
 writeData(wb, sheet = "data", x = df)
 highlight.rows <- which(df$improvement < threshold)+1
 addStyle(wb, "data", cols = 1:ncol(df), rows = highlight.rows,
          style = highlight.Style, gridExpand = TRUE)
 
 wb.name <- df$Division[1]
 saveWorkbook(wb,paste0(wb.name,'.xlsx'),overwrite = T)
}

df %>% split(.$Division) %>% map(~create.workbook(.x,threshold))

$ASIA
[1] 1

$EUROPE
[1] 1

Easy way to export multiple data.frame to multiple Excel worksheets

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()

sheet = createSheet(wb, "Sheet 1")

addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE)
addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)

sheet = createSheet(wb, "Sheet 2")

addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)

saveWorkbook(wb, "My_File.xlsx")

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx:
http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

---

Related Topics