R/writeFunctions.R

In mverouden/wurmc: WUR Process and Grade Multiple Choice Answer Sheets

#' Convert a FormScanner file to Microsoft Excel file.
#'
#' Convert responses of a multiple choice answer sheet as obtained by Formscanner
#' ,as a csv-file, into a Microsoft Excel workbook xlsx-file.
#'
#' Multiple choice answers sheets from students at Wageningen University and
#' Research are generally scanned and processed into a Microsoft Excel worbook,
#' containing a worksheet named with the course code, by EDUsupport (part of
#' ITsupport). The used multiple choice answer sheets, however, also allow for
#' scanning and processing with the free, open source OMR (optical mark
#' recognition) software FormScanner (\url{http://www.formscanner.org/}).
#'
#' This function converts the resulting FormScanner csv-file (in reality a
#' semicolon separated file) into a Microsoft Excel workbook with the same
#' content layout as generated by EDUsupport.
#'
#' @param fileName A character string with the name of the FormScanner csv-file
#'   to be converted into a Microsoft Excel xlsx-file.
#' @param noItems An integer, not exceeding 40, specifying the number of items
#'   (questions) on the answers sheet to be considered.
#' @param courseCode A character string with the course code, to be used as the
#'   name of the worksheet in the Microscoft Excel workbook, e.g. MAT15403.
#'   Preferably use the course code as specified in the university study guide.
#' @param saveName A character string containing the name of how the Microsoft
#'   Excel workbook should be saved. The extension is not needed, automatically
#'   xlsx will be added as file extension.
#' @return A Microsoft Excel workbook xlsx-file named with the name specified
#'   by the \code{saveName} function argument.
#'
#' @seealso \code{\link{processMC}}
#'
#' @export
#'
#' @examples
#' \dontrun{
#'   formScanner2Excel(fileName = "examples/2015-2016_P1_MAT15403_xm151020_scan_results.csv",
#'                     noItems = 25,
#'                     courseCode = "MAT15403",
#'                     saveName = "2015-2016_P1_MAT15403_xm151020_ICT_results")
#' }
formScanner2Excel <- function(fileName,
                              noItems,
                              courseCode,
                              saveName = "ICT_results") {
  ## Check whether a *.csv has been provided
  if (!(tolower(strsplit(x = fileName, split = "\\.")[[1]][2]) == "csv")) {
    stop("FormScanner responses not read, due to an invalid responses file extension (should be *.csv file)")
  }
  ## Convert noItems into an integer
  noItems <- as.integer(noItems)
  ## Read the student multiple-choice responses into a data.frame responses
  responses <- read.csv2(file = fileName)
  ## Order responses by responses$File.name
  responses <- responses[order(responses$File.name), ]
  ## Generate the student registration number (reg.number) from columns
  ## ID_NO_001:ID_NO_012
  responses <- tidyr::unite(data = responses,
                            col = "Reg Nummer",
                            "X.IDStudent..IDNo001":"X.IDStudent..IDNo012", # to join
                            sep = "",
                            remove = TRUE)
  ## Select the columns reg.number, Version and the student responses to test
  ## items
  responses <- dplyr::select(responses,
                             "Reg Nummer",
                             "X.Exam..Version",
                             grep("^X\\.Q", colnames(responses)))
  ## Select columns "reg.number","Version" and item responses equal to noItems
  ## in the key.
  responses <- responses[, seq_len(2 + noItems)]
  ## Change all columns of the responses data.frame to character
  for (j in seq_len(ncol(responses))) {
    responses[, j] <- as.character(responses[, j])
  }
  ## Fill empty fields with "BLANK"
  responses[!is.na(responses) & responses == ""] <- "BLANK"
  ## Change column names
  colnames(responses) <- c("Reg Nummer",
                           "Versie",
                           paste0("Q", seq_len(noItems)))
  ## Create a Microsoft Office Excel workbook with the specified saveename,
  ## the default is ICT_results.xlsx.
  wb <- XLConnect::loadWorkbook(filename = paste(saveName, "xlsx", sep = "."),
                                create = TRUE)
  ## Create a worksheet named after the supplied courseCode in the workbook wb
  XLConnect::createSheet(object = wb,
                         name = courseCode)
  ## Write the student responses to the worksheet courseCode in the workbook wb
  XLConnect::writeWorksheet(object = wb,
                            data = responses,
                            sheet = courseCode,
                            startRow = 1,
                            startCol = 1,
                            header = TRUE)
  ## Save the workbook to an Excel file
  XLConnect::saveWorkbook(wb)
  return()
}