CODEWITHSUNDEEP
rencodingspecial-charactersrdcomclientrexcel
sayan chakraborty
sayan chakraborty

Reputation: 21

Rstudio - RDCOMCLIENT writing a dataframe to excel generates special characters

While writing some dataframe to excel, via asCOMArray, most of the data is fine. But due to encoding problem, some characters like " - " gets converted to : " †" ; "é" gets changed to : "é"

Here lies the code example :

xlApp <- COMCreate("Excel.Application")
xlApp[["Visible"]] <- TRUE
xlApp[["DisplayAlerts"]] <- F
xlApp[["EnableEvents"]] <- F

#add new wb
wb <- xlApp$Workbooks()$Add()
#rename first active sheet
sheet <- xlApp$ActiveSheet()
wbActiveNum <- xlApp$ActiveSheet()[['Index']]
wbSheet <- xlApp$Worksheets()[[wbActiveNum]]
wbSheet[['Name']] <- 'Data'
#save file
Filenm <- paste0(Output_path,"XXX File as Of ",format(Sys.Date() , "%B-%d-%Y"), " .xlsb")
Filenm <- suppressWarnings(normalizePath(Filenm))
wb$SaveAs(Filename = Filenm ,FileFormat = 50)
Sys.sleep(2)

sheet <- wb$Worksheets("Data")
I1 <- as.numeric(ncol(DF))%%26                                     
I2 <- round(as.numeric(ncol(DF))/26)                             
LC <- toupper(paste0(letters[as.numeric(I2)], letters[as.numeric(I1)]))
SC <- toupper(letters[1])
range<-sheet$Range(paste0(SC,"14:",LC,nrow(DF)+13))
range[["Value"]] <- asCOMArray(DF)
try( range[["Borders"]][["ColorIndex"]] <- 1 , silent = T )
Font <- range$Font()
Font[["Size"]] <- 9
Font[["Name"]] <- "Calibri"
EntCol <-range$EntireColumn()
EntCol$AutoFit()

Now "DF" is my dataframe, which gets pasted successfully, but generates special characters at various places, as shown above.

Is there a way to change the encoding to "latin9" or "latin1" to help overcome these special characters generation.

Can RDCOMclient change the encoding type before writing data to excel.

(above is a random code chunk, used to write data to excel sheet)

Upvotes: 0

Views: 463

Answers (1)

Emmanuel Hamel
Emmanuel Hamel

Reputation: 2233

You have to convert the text from UFT-8 to latin1. I have been able to write special caracters properly in the Excel file with the following code :

library(RDCOMClient)
xlApp <- COMCreate("Excel.Application")
xlApp[["Visible"]] <- TRUE
xlApp[["DisplayAlerts"]] <- FALSE
xlApp[["EnableEvents"]] <- FALSE

wb <- xlApp$Workbooks()$Add()
sheet <- xlApp$ActiveSheet()
wbActiveNum <- xlApp$ActiveSheet()[['Index']]
wbSheet <- xlApp$Worksheets()[[wbActiveNum]]
wbSheet[['Name']] <- 'Data'

y <- c("é", "ê")
y <- iconv(y, from = "UTF-8", to = "latin1")
x <- c("ô", "û")
x <- iconv(x, from = "UTF-8", to = "latin1")

DF <- data.frame(y = y, x = x)

sheet <- wb$Worksheets("Data")
range <- sheet$Range("A1:B2")
range[["Value"]] <- asCOMArray(DF)

Upvotes: 0

Related Questions