[R] MongoDB Connection Example with export data

library(devtools)
library("mongolite")
library(xlsx)
library(xtable)

library(gridExtra)
library(grid)

# ---------------------------------------------
# connect to your local mongodb
# ---------------------------------------------
country_data <- mongo(collection = "country",
                      db = "auditor",
                      url = "mongodb://localhost")

# ---------------------------------------------
# count data
# ---------------------------------------------
total <- country_data$count(
  query='{}'
)
# print(total)

# ---------------------------------------------
# get all data
# ---------------------------------------------
data_all_1 <- country_data$find('{}')

# ---------------------------------------------
# get specific columns
# ---------------------------------------------
data_all_2 <- country_data$find(
  query='{}',
  fields = '{"_id" : true, "name_br" : true}',
  limit=0
)
#print(data_all_2)

# ---------------------------------------------
# export data frame to xlsx
# ---------------------------------------------
write.xlsx(data_all_2, "D:/mydata2019.xlsx")

# ---------------------------------------------
# latex table code
# ---------------------------------------------
my_table <- xtable(data_all_2)
print(my_table)

# ---------------------------------------------
# html table code
# ---------------------------------------------
print(my_table, type = "html")

# ---------------------------------------------
# data iteraction with limit
# ---------------------------------------------
data_all_3 <- country_data$iterate(
  '{}',
  fields = '{"_id" : true, "name_br" : true}',
  limit = 7
)
while(!is.null(x <- data_all_3$one())){
  output = paste("id =",x$"_id", "| name_br =", x$name_br, sep = " ")
  print(output)
}

# ---------------------------------------------
# export data frame to pdf
# ---------------------------------------------
maxrow=35
npages=ceiling(nrow(data_all_2)/maxrow)
pdf(file='D:/data_in_pdf.pdf',height=11,width = 8.5)
idx=seq(1, maxrow)
grid.table(data_all_2[idx, ],rows=NULL)
for(i in 2:npages){
  grid.newpage()
  if(i*maxrow <= nrow(data_all_2)){
    idx = seq(1+((i-1)*maxrow), i*maxrow)
  }
  else{
    idx=seq(1+((i-1)*maxrow), nrow(data_all_2))
  }
  grid.table(data_all_2[idx, ],rows=NULL)
}
dev.off()