Ndtyjky

In an old .Rnw file I was using the RODBC package to read an excel sheet and display a latex table containing checkmarks.

Recently, I tried to change the way I used to read the excel file, and use the XLConnect package instead of RODBC, but I came across a problem. I can't make the checkmark symbol be displayed correctly anymore. Note that the object I pass to xtable for printing is identical with the previous one, but the result is not!

Get here the excel file that I use.

This is the working .Rnw. (I know the code isn't by no means optimal). The file encoding is UTF-8, and I weave the file using knitr and XeLaTeX

documentclass[a4paper]{article}

usepackage{fontspec}

usepackage{bbding} % checkmark symbol

setmainfont[Scale=1.0]{Times New Roman} % Times New Roman

setsansfont{Times New Roman}



<<eval=TRUE,echo=FALSE,warning=FALSE,message=FALSE,error=FALSE>>=

library(plyr)

library(XLConnect)

library(lubridate)

library(RODBC)

library(stringr)

library(xtable)

library(magrittr)

@



begin{document}



begin{center}



{small

<<eval=TRUE,echo=FALSE,results='asis',warning=FALSE,message=FALSE,error=FALSE>>=



sheet = 'DATA-BOOK.xls' 

con = odbcConnectExcel2007(sheet)

tbls = sqlTables(con)

df <- sqlQuery(con,"SELECT * FROM `r-crosstab$`",stringsAsFactors =FALSE)

close(con)





dfr <- df[,2:ncol(df)]

for (i in 1:ncol(dfr)){

  dfr[i][is.na(dfr[i])] <- ""

}



temp <- adply(dfr,1,function(x) length(x[x!=""]))



dfr <-  apply(dfr,2,function(x) gsub("\?","\\Checkmark",x))



aitia <- paste0("c",1:nrow(dfr))

aitia <- unlist(sapply(aitia,function(x) if (nchar(x) == 2) {x=paste0(str_sub(x, 1, 1),"0",str_sub(x, 2, 2))} else{x} ))

names(aitia)<-NULL

dfr <- cbind(aitia,dfr,temp[ncol(temp)])



dfr1 <- t(dfr)

dfr1 <- apply(dfr1,2,as.character)

rownames(dfr1) <- c(colnames(dfr)[1:(length(colnames(dfr))-1)],"Αθρ.")

colnames(dfr1) <- dfr1[1,]

dfr1 <- dfr1[-1,]



dfr1[nrow(dfr1),] <- paste0("\textbf{",dfr1[nrow(dfr1),],"}")



print(xtable(dfr1, align=paste0("l|",paste(rep("c|",length(aitia)),collapse="")),

             label='tab_crosstab'), 

      scalebox=0.80, 

      include.rownames=TRUE, 

      hline.after=c(-1:nrow(dfr1)),

      sanitize.text.function = function(x){x}

)



@

}

end{center}



end{document}

That's what I get when I compile it (it is the expected result)

That is the .Rnw where I face the problem. Can you tell me what I need to change?

documentclass[a4paper]{article}

usepackage{fontspec}

usepackage{bbding} % checkmark symbol

setmainfont[Scale=1.0]{Times New Roman} % Times New Roman

setsansfont{Times New Roman}



<<eval=TRUE,echo=FALSE,warning=FALSE,message=FALSE,error=FALSE>>=

library(plyr)

library(XLConnect)

library(lubridate)

library(RODBC)

library(stringr)

library(xtable)

library(magrittr)

@



begin{document}



begin{center}



{small

<<eval=TRUE,echo=FALSE,results='asis',warning=FALSE,message=FALSE,error=FALSE>>=



wb <- loadWorkbook('DATA-BOOK.xls', create = FALSE)

df <- readWorksheet(wb, sheet="r-crosstab", check.names=FALSE)



df %>%

dplyr::select(- dplyr::contains("ΑITIO")) %>%

names() -> vnames 



df[vnames] %<>%

dplyr::mutate_if(is.factor, as.character)





dfr <- df[,2:ncol(df)]

for (i in 1:ncol(dfr)){

  dfr[i][is.na(dfr[i])] <- ""

}



temp <- adply(dfr,1,function(x) length(x[x!=""]))



dfr <-  apply(dfr,2,function(x) gsub("√","\\Checkmark",x)) # √   <U+221A>



aitia <- paste0("c",1:nrow(dfr))

aitia <- unlist(sapply(aitia,function(x) if (nchar(x) == 2) {x=paste0(str_sub(x, 1, 1),"0",str_sub(x, 2, 2))} else{x} ))

names(aitia)<-NULL

dfr <- cbind(aitia,dfr,temp[ncol(temp)])



dfr1 <- t(dfr)

dfr1 <- apply(dfr1,2,as.character)

rownames(dfr1) <- c(colnames(dfr)[1:(length(colnames(dfr))-1)],"Αθρ.")

colnames(dfr1) <- dfr1[1,]

dfr1 <- dfr1[-1,]



dfr1[nrow(dfr1),] <- paste0("\textbf{",dfr1[nrow(dfr1),],"}")



print(xtable(dfr1, align=paste0("l|",paste(rep("c|",length(aitia)),collapse="")),

             label='tab_crosstab'), 

      scalebox=0.80, 

      include.rownames=TRUE, 

      hline.after=c(-1:nrow(dfr1)),

      sanitize.text.function = function(x){x}

)

@

}

end{center}



end{document}

Here is how the compliled pdf looks like now:

> sessionInfo()

R version 3.5.1 (2018-07-02)

Platform: i386-w64-mingw32/i386 (32-bit)

Running under: Windows >= 8 x64 (build 9200)



Matrix products: default



locale:

[1] LC_COLLATE=Greek_Greece.1253 

[2] LC_CTYPE=Greek_Greece.1253   

[3] LC_MONETARY=Greek_Greece.1253

[4] LC_NUMERIC=C                 

[5] LC_TIME=Greek_Greece.1253    



attached base packages:

[1] stats     graphics  grDevices utils     datasets 

[6] methods   base     



other attached packages:

 [1] magrittr_1.5         extrafont_0.17      

 [3] xtable_1.8-3         stringr_1.3.1       

 [5] RODBC_1.3-15         lubridate_1.7.4     

 [7] XLConnect_0.2-15     XLConnectJars_0.2-15

 [9] plyr_1.8.4           tidyr_0.8.2         

[11] dplyr_0.7.7         



loaded via a namespace (and not attached):

 [1] Rcpp_0.12.19     Rttf2pt1_1.3.7   rstudioapi_0.8  

 [4] bindr_0.1.1      tidyselect_0.2.5 R6_2.3.0        

 [7] rlang_0.3.0.1    tools_3.5.1      extrafontdb_1.0 

[10] yaml_2.2.0       assertthat_0.2.0

EDIT

In the following pair of code snipets I tried to minimize the examples, leaving out some irrelevant parts. Again, the first is the one that works ok. In the meantime I noticed that no matter what string I use as a replacement in gsub (in the second example), the result is always the same!!!

Working code:

documentclass[a4paper]{article}

usepackage{fontspec}

usepackage{bbding} % checkmark symbol

setmainfont[Scale=1.0]{Times New Roman} % Times New Roman

setsansfont{Times New Roman}



<<eval=TRUE,echo=FALSE,warning=FALSE,message=FALSE,error=FALSE>>=

library(XLConnect)

library(RODBC)

library(xtable)

library(dplyr)

library(magrittr)

@



begin{document}



{small

<<eval=TRUE,echo=FALSE,results='asis',warning=FALSE,message=FALSE,error=FALSE>>=



sheet = 'DATA-BOOK.xls' 

con = odbcConnectExcel2007(sheet)

tbls = sqlTables(con)

df <- sqlQuery(con,"SELECT * FROM `r-crosstab$`",stringsAsFactors =FALSE)

close(con)



df %>%

dplyr::select(- dplyr::contains("ΑITIO")) %>%

names() -> vnames 



df[vnames] %<>%

mutate_if(is.factor, as.character) %>% 

mutate_if(is.character, funs(coalesce(., "")))

names(df)[1] <- "TITLE"



dfr <-  apply(df,2,function(x) gsub("\?","\\Checkmark",x))

rownames(dfr) <- NULL

dfr1 <- t(dfr)

dfr1[1,] <- paste0("c",1:19)



print(xtable(dfr1), 

      scalebox=0.80, 

      hline.after=c(-1:nrow(dfr1)),

      sanitize.text.function = function(x){x}

)

@

}



end{document}

NOT working code:

documentclass[a4paper]{article}

usepackage{fontspec}

usepackage{bbding} % checkmark symbol

setmainfont[Scale=1.0]{Times New Roman} % Times New Roman

setsansfont{Times New Roman}



<<eval=TRUE,echo=FALSE,warning=FALSE,message=FALSE,error=FALSE>>=

library(XLConnect)

library(RODBC)

library(xtable)

library(dplyr)

library(magrittr)

@



begin{document}



{small

<<eval=TRUE,echo=FALSE,results='asis',warning=FALSE,message=FALSE,error=FALSE>>=



wb <- loadWorkbook('DATA-BOOK.xls', create = FALSE)

df <- readWorksheet(wb, sheet="r-crosstab", check.names=FALSE)



df %>%

dplyr::select(- dplyr::contains("ΑITIO")) %>%

names() -> vnames 



df[vnames] %<>%

mutate_if(is.factor, as.character) %>% 

mutate_if(is.character, funs(coalesce(., "")))

names(df)[1] <- "TITLE"



dfr <-  apply(df,2,function(x) gsub("√","\\Checkmark",x)) # √   <U+221A>

dfr1 <- t(dfr)

dfr1[1,] <- paste0("c",1:19)



print(xtable(dfr1), 

      scalebox=0.80, 

      include.rownames=TRUE, 

      hline.after=c(-1:nrow(dfr1)),

      sanitize.text.function = function(x){x}

)

@

}



end{document}

The issue is the formatting of your gsub line.

Proposed Solution:

Replace:

dfr <- apply(df,2,function(x) gsub("√","\\Checkmark",x)) # √ <U+221A>

with

dfr <- apply(dfr,2,function(x) gsub("u221A","\\Checkmark",x)) # √ <U+221A>

Result:

https://www.dropbox.com/s/evzknzobfwh0ag1/53243041v2.pdf?dl=0

Setup:

I tested this on Windows 10, with RStudio and R3.5.1, using knitr and XeLatex for typesetting.

Explanation:

The .rnw file when compiled creates an intermediate .tex file as output, this is then converted to pdf. You have to pass the code u221A" in the correct format through to the pdf formatting application.