R/export.R

In nivesh22/descriptive: Generate Descriptive Statistics and Export them as a Formatted Excel File

#' export
#' This function helps in converting the tables created through descriptive() to a formatted excel report ()
#' @import openxlsx
#' @param excel_workbook The workbook object created using wb()
#' @param file_name The name of the file to be generated. Make sure this file is not opened in any window.
#' @param index Boolean indicating whether an index page has to be created, default being TRUE
#' @param title Title for the excel file. This title will be added at the top of every sheet
#' @param heading_fill Hex code for fill color for the table headings
#' @param heading_font_color Hex code for font color for the table headings
#' @param font_size Font size of the data, default font size is 10.
#' @param zoom The zoom percentage for the sheets, default being 90.
#' @param logo The address of the image which has to be added as a logo in the report
#' @export export
#' @return A list containing the tables, subtitle and sheetname

export <-
  function(excel_workbook,
           file_name,
           index = T,
           title = "Descriptive report",
           heading_fill = "#C00000",
           heading_font_color = "#A9A9A9",
           font_size = 10,
           zoom = 90,
           logo = NULL) {
    if (is.null(file_name)) {
      file_name <- title
    }

    file_name <- gsub("[[:blank:]]", "_", file_name)
    if (grepl(".xlsx", file_name) == FALSE) {
      file_name <- paste(file_name, ".xlsx", sep = "")
    }
    isHexColor <- function  (hex) {
      return(is.character(hex) == TRUE
             & nchar(hex) == 7
             & !is.nan(base::strtoi(paste(
               '0x', sub('#', '', hex), sep = ''
             ))))
    }

    ########################### Checking the input arguments #########################
    if (!isHexColor(heading_fill)) {
      message <- paste(heading_fill, "is not a hex color code")
      stop(message)
    }

    if (!isHexColor(heading_font_color)) {
      message <- paste(font_size, "is not a hex color code")
      stop(message)
    }



    if (zoom < 10 | zoom > 400) {
      stop("Inavlid zoom percentage. Try values between 10-400")
    }
    # if (!file.exists(logo)) {
    #   warning("The file path for logo doesn't exist. Hence, logo will not be added")
    # }

    #defining the formats
    hs2 <- openxlsx::createStyle(
      fontColour = "#ffffff",
      fontSize = font_size,
      fgFill = heading_fill,
      halign = "center",
      valign = "center",
      textDecoration = "bold",
      border = "TopBottomLeftRight",
      borderColour = heading_font_color,
      borderStyle =  getOption("openxlsx.borderStyle", "thin")
    )
    titleStyle <- openxlsx::createStyle(
      fontSize = 18,
      fontColour = "black",
      textDecoration = "bold"
    )
    subtitleStyle <- openxlsx::createStyle(
      fontSize = 14,
      fontColour = "black",
      textDecoration = "bold"
    )
    bold <- openxlsx::createStyle(textDecoration = "bold")
    grey_bg <- openxlsx::createStyle(
      fontSize = font_size,
      fgFill = "#F2F2F2",
      bgFill = NULL,
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =  getOption("openxlsx.borderStyle", "thin")
    )
    bold_grey <- openxlsx::createStyle(
      fontSize = font_size,
      fgFill = "#F2F2F2",
      bgFill = NULL,
      textDecoration = "bold",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =  getOption("openxlsx.borderStyle", "thin")
    )
    bold_grey_center <- openxlsx::createStyle(
      fontSize = font_size,
      fgFill = "#F2F2F2",
      bgFill = NULL,
      textDecoration = "bold",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      halign = "center",
      valign = "center",
      borderStyle =  getOption("openxlsx.borderStyle", "thin")
    )
    borderStyle <- openxlsx::createStyle(
      halign = "CENTER",
      border = "Top",
      borderColour = "red",
      borderStyle = "thick"
    )
    #  % style
    percentColStyle <- openxlsx::createStyle(
      fontName = "Calibri",
      fontSize = font_size,
      fontColour = "black",
      numFmt = "#,##0.00%",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =
        getOption("openxlsx.borderStyle", "thin"),
      bgFill = NULL,
      fgFill = NULL,
      halign = NULL,
      valign = NULL,
      textDecoration = NULL,
      wrapText = FALSE,
      textRotation = NULL,
      indent = NULL
    )

    # Add N style
    NColStyle <- openxlsx::createStyle(
      fontName = "Calibri",
      fontSize = font_size,
      fontColour = "black",
      numFmt = "#,##",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =
        getOption("openxlsx.borderStyle", "thin"),
      bgFill = NULL,
      fgFill = NULL,
      halign = NULL,
      valign = NULL,
      textDecoration = NULL,
      wrapText = FALSE,
      textRotation = NULL,
      indent = NULL
    )
    # Add N.00 style
    NDecColStyle <- openxlsx::createStyle(
      fontName = "Calibri",
      fontSize = font_size,
      fontColour = "black",
      numFmt = "#,##0.00",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =
        getOption("openxlsx.borderStyle", "thin"),
      bgFill = NULL,
      fgFill = NULL,
      halign = NULL,
      valign = NULL,
      textDecoration = NULL,
      wrapText = FALSE,
      textRotation = NULL,
      indent = NULL
    )
    #  % style
    percentBoldColStyle <- openxlsx::createStyle(
      fontName = "Calibri",
      fontSize = font_size,
      fontColour = "black",
      numFmt = "#,##0.00%",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =
        getOption("openxlsx.borderStyle", "thin"),
      bgFill = NULL,
      fgFill = NULL,
      halign = NULL,
      valign = NULL,
      textDecoration = "bold",
      wrapText = FALSE,
      textRotation = NULL,
      indent = NULL
    )

    # Add N style
    NBoldColStyle <- openxlsx::createStyle(
      fontName = "Calibri",
      fontSize = font_size,
      fontColour = "black",
      numFmt = "#,##",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =
        getOption("openxlsx.borderStyle", "thin"),
      bgFill = NULL,
      fgFill = NULL,
      halign = NULL,
      valign = NULL,
      textDecoration = "bold",
      wrapText = FALSE,
      textRotation = NULL,
      indent = NULL
    )
    # Add N.00 style
    NDecBoldColStyle <- openxlsx::createStyle(
      fontName = "Calibri",
      fontSize = font_size,
      fontColour = "black",
      numFmt = "#,##0.00",
      border = "TopBottomLeftRight",
      borderColour = "#A9A9A9",
      borderStyle =
        getOption("openxlsx.borderStyle", "thin"),
      bgFill = NULL,
      fgFill = NULL,
      halign = NULL,
      valign = NULL,
      textDecoration = "bold",
      wrapText = FALSE,
      textRotation = NULL,
      indent = NULL
    )



    #creating the workbook
    outwb <- createWorkbook()

    if (index) {
      #get the name of all the worksheets
      sheet_names <- c()
      subtitle <- "Index page"
      for (sheet in excel_workbook) {
        sheet_names <- c(sheet_names, unlist(sheet[3]))
      }
      index_table <-
        as.data.frame(rbind(cbind(Sheet = sheet_names, Link = sheet_names)))

      #adding the worksheet
      openxlsx::addWorksheet(outwb, "Index", zoom = zoom)
      openxlsx::showGridLines(outwb, "Index", showGridLines = FALSE)

      #writing the title and the subtitles
      if (!is.null(logo)) {
        openxlsx::insertImage(
          outwb,
          "Index",
          logo,
          startRow = 1,
          startCol = 1,
          width = 0.7,
          height = 0.8,
          units = "in"
        )
      }

      openxlsx::writeData(
        outwb,
        "Index",
        title,
        startCol = 2,
        startRow = 1,
        borders = "none"
      )
      openxlsx::addStyle(
        outwb,
        sheet = "Index",
        titleStyle,
        rows = 1,
        cols = 2,
        gridExpand = TRUE
      )
      openxlsx::addStyle(
        outwb,
        sheet = "Index",
        borderStyle,
        rows = 2,
        cols = 2:16384,
        gridExpand = TRUE
      )
      openxlsx::writeData(
        outwb,
        "Index",
        subtitle,
        startCol = 2,
        headerStyle = subtitleStyle,
        startRow = 3,
        borders = "none"
      )
      openxlsx::addStyle(
        outwb,
        sheet = "Index",
        subtitleStyle,
        rows = 3,
        cols = 2,
        gridExpand = TRUE
      )
      #writing the data to the worksheet
      openxlsx::writeData(
        outwb,
        "Index",
        index_table,
        startCol = 2,
        startRow = 7,
        borders = "all",
        borderColour = "#bfbfbf",
        headerStyle = hs2,
        borderStyle = "thin"
      )

      for (i in 1:nrow(index_table)) {
        x <- index_table[i, 1]
        writeFormula(
          outwb,
          "Index",
          startRow = 7 + i,
          startCol = 3,
          x = makeHyperlinkString(
            sheet = x,
            row = 1,
            col = 1,
            text = x
          )
        )
      }
      width_vec <- apply(index_table, 2,
                         function(x)
                           max(nchar(as.character(x)) + 1,
                               na.rm = TRUE))
      width_vec_header <- nchar(colnames(index_table))  + 1
      max_vec_header_param <- pmax(width_vec, width_vec_header)
      openxlsx::setColWidths(outwb,
                             "Index",
                             cols = 2:(ncol(index_table) + 1),
                             widths = max_vec_header_param)


    }


    #going through each sheet

    for (sheet in excel_workbook) {
      #subtitle
      subtitle <- unlist(sheet[2])
      #sheet name
      sheet_name <- unlist(sheet[3])
      #sheet name
      stack <- unlist(sheet[4])
      #tables
      tables <- sheet[[1]]

      if (!(stack == "sideways" | stack == "below")) {
        message <-
          paste(stack,
                "is not a valid input for stack. Try 'sideways' or 'below'")
        stop(message)
      }

      #adding the worksheet
      openxlsx::addWorksheet(outwb, sheet_name, zoom = zoom)
      openxlsx::showGridLines(outwb, sheet_name, showGridLines = FALSE)

      #writing the title and the subtitles
      if (!is.null(logo)) {
        openxlsx::insertImage(
          outwb,
          sheet_name,
          logo,
          startRow = 1,
          startCol = 1,
          width = 0.7,
          height = 0.8,
          units = "in"
        )
      }
      openxlsx::writeData(
        outwb,
        sheet_name,
        title,
        startCol = 2,
        startRow = 1,
        borders = "none"
      )
      openxlsx::addStyle(
        outwb,
        sheet = sheet_name,
        titleStyle,
        rows = 1,
        cols = 2,
        gridExpand = TRUE
      )
      openxlsx::addStyle(
        outwb,
        sheet = sheet_name,
        borderStyle,
        rows = 2,
        cols = 2:16384,
        gridExpand = TRUE
      )
      openxlsx::writeData(
        outwb,
        sheet_name,
        subtitle,
        startCol = 2,
        headerStyle = subtitleStyle,
        startRow = 3,
        borders = "none"
      )
      openxlsx::addStyle(
        outwb,
        sheet = sheet_name,
        subtitleStyle,
        rows = 3,
        cols = 2,
        gridExpand = TRUE
      )



      if (index) {
        openxlsx::writeFormula(
          outwb,
          sheet_name,
          startRow = 4,
          startCol = 1,
          x = makeHyperlinkString(
            sheet = "Index",
            row = 1,
            col = 1,
            text = "<-Index"
          )
        )
        openxlsx::addStyle(
          outwb,
          sheet_name,
          rows = 4,
          style = createStyle(textDecoration = NULL, fontColour = "#0000FF"),
          cols = 1
        )
      }
      if (length(tables) != 4) {
        reference_row <- 5
        reference_col <- 2
        for (table in tables) {
          #sheet type
          sheet_type <- as.character(unlist(table$type))
          #heading name
          table_heading <- table$heading
          #table
          table <- as.data.frame(table$table)

          #writing the data to the worksheet
          openxlsx::writeData(
            outwb,
            sheet_name,
            table,
            startCol = reference_col,
            startRow = reference_row - 1 + nrow(table_heading),
            borders = "all",
            borderColour = "#bfbfbf",
            headerStyle = hs2,
            borderStyle = "thin"
          )

          #writing the heading
          openxlsx::writeData(
            outwb,
            sheet_name,
            table_heading,
            startCol = reference_col,
            startRow = reference_row - 1,
            borders = "all",
            borderColour = "#bfbfbf",
            borderStyle = "thin"
          )


          if (sheet_type == "categorical" |
              sheet_type == "binary") {
            #writing the logic to merge
            tfdf <-
              as.data.frame(matrix(
                rep(F, nrow(table_heading) * ncol(table_heading)),
                nrow = nrow(table_heading),
                ncol = ncol(table_heading)
              ))
            j <- 1
            while (j <= ncol(table_heading)) {
              merge_column_start <- j
              merge_column_end <- j
              i <- 1
              while (i < nrow(table_heading)) {
                if (tfdf[i, j] == T) {
                  i <- i + 1
                  next()
                }
                temp <- table_heading[i, j]
                merge_row_start <- i
                merge_row_end <- i
                merge_column_end <- j
                for (k in ((i + 1):(nrow(table_heading) - 1))) {
                  if (table_heading[k, j] == temp) {
                    merge_row_end <- k
                    next()
                  } else{
                    break()
                  }
                }
                while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end +
                                                 1]) ==
                            rep(temp, length(
                              unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
                            )))) &
                       merge_column_end < ncol(table_heading)) {
                  merge_column_end <- merge_column_end + 1
                }
                if (merge_column_end == 1) {
                  openxlsx::mergeCells(
                    outwb,
                    sheet_name,
                    cols = (
                      reference_col - 1 + c(merge_column_start:merge_column_end)
                    ),
                    rows = (reference_row - 1 + c(
                      merge_row_start:(merge_row_end +
                                         1)
                    ))
                  )
                } else{
                  openxlsx::mergeCells(
                    outwb,
                    sheet_name,
                    cols = reference_col - 1 + c(merge_column_start:merge_column_end),
                    rows = reference_row - 1 + c(merge_row_start:merge_row_end)
                  )
                }

                tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
                  T
                i <- i + 1
              }
              j <- j + 1
            }


            # Add N style to table
            for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +  reference_row -
                                                             1)) {
              openxlsx::addStyle(
                outwb,
                sheet_name,
                NColStyle,
                rows = i,
                cols = c(reference_col - 1 + which(grepl(
                  "All", colnames(table)
                ))),
                gridExpand = TRUE,
                stack = FALSE
              )
            }
            # Add % style to table
            for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                             reference_row -
                                                             1)) {
              openxlsx::addStyle(
                outwb,
                sheet_name,
                percentColStyle,
                rows = i,
                cols = c(reference_col - 1 + which(grepl(
                  "Percent", colnames(table)
                ))),
                gridExpand = TRUE,
                stack = FALSE
              )

            }
            #Adding format style to the table header
            openxlsx::addStyle(
              outwb,
              sheet = sheet_name,
              hs2,
              rows = reference_row:(nrow(table_heading) + reference_row -
                                      1),
              cols = (reference_col:(
                ncol(table_heading) + reference_col - 1
              )),
              gridExpand = TRUE
            )
            deleteData(
              outwb,
              sheet = sheet_name,
              cols = (reference_col:(
                reference_col - 1 + ncol(table_heading)
              )),
              rows = reference_row - 1,
              gridExpand = TRUE
            )


            if (sum(rowSums(is.na(table))) == 0) {
              grey_style <- bold_grey
            } else{
              grey_style <- grey_bg
            }

            #adding grey to the labels
            addStyle(
              outwb,
              sheet = sheet_name,
              grey_style,
              rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                              reference_row -
                                                              1),
              cols = reference_col,
              gridExpand = TRUE
            )

            #adding bold grey to the variables
            addStyle(
              outwb,
              sheet = sheet_name,
              bold_grey,
              rows = c((
                reference_row - 1 + nrow(table_heading) + (which(is.na(table[, 2])))
              )),
              cols = (reference_col:(ncol(table) + reference_col - 1)),
              gridExpand = TRUE
            )

            # Add N style to table

            openxlsx::addStyle(
              outwb,
              sheet_name,
              NBoldColStyle,
              rows = (reference_row + nrow(table_heading)),
              cols = c(reference_col - 1 + which(grepl(
                "All", colnames(table)
              ))),
              gridExpand = TRUE,
              stack = FALSE
            )
            # Add % style to table

            openxlsx::addStyle(
              outwb,
              sheet_name,
              percentBoldColStyle,
              rows = reference_row + nrow(table_heading),
              cols = c(reference_col - 1 + which(grepl(
                "Percent", colnames(table)
              ))),
              gridExpand = TRUE,
              stack = FALSE
            )

            openxlsx::addStyle(
              outwb,
              sheet_name,
              bold_grey,
              rows = reference_row + nrow(table_heading) ,
              cols = reference_col,
              gridExpand = TRUE,
              stack = FALSE
            )



          } else{
            #writing the logic to merge
            tfdf <-
              as.data.frame(matrix(
                rep(F, nrow(table_heading) * ncol(table_heading)),
                nrow = nrow(table_heading),
                ncol = ncol(table_heading)
              ))
            j <- 1
            while (j <= ncol(table_heading)) {
              merge_column_start <- j
              merge_column_end <- j
              i <- 1
              while (i < nrow(table_heading)) {
                if (tfdf[i, j] == T) {
                  i <- i + 1
                  next()
                }
                temp <- table_heading[i, j]
                merge_row_start <- i
                merge_row_end <- i
                merge_column_end <- j

                for (k in ((i + 1):(nrow(table_heading)))) {
                  if (table_heading[k, j] == temp) {
                    merge_row_end <- k
                    next()
                  } else{
                    break()
                  }
                }
                while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1]) ==
                            rep(temp, length(
                              unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
                            )))) &
                       merge_column_end < ncol(table_heading)) {
                  merge_column_end <- merge_column_end + 1
                }

                openxlsx::mergeCells(
                  outwb,
                  sheet_name,
                  cols = (reference_col - 1 + c(merge_column_start:merge_column_end)),
                  rows = (reference_row - 1 + c(merge_row_start:merge_row_end))
                )


                tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
                  T
                i <- i + 1
              }
              j <- j + 1
            }


            #logic to merge the rows
            final <- c()
            for (i in 2:nrow(table)) {
              after <- table[i, 1]
              if (!is.na(after)) {
                final <- c(final, (i - 1))
              }
            }
            final <- c(final, nrow(table))
            final <- final + reference_row + nrow(table_heading)
            for (i in (1:(length(final) - 1))) {
              openxlsx::mergeCells(outwb,
                                   sheet_name,
                                   rows = c(final[i]:(final[i + 1] - 1)),
                                   cols = reference_col)
            }



            for (i in (reference_row + 1 + nrow(table_heading)):(reference_row + nrow(table_heading) + nrow(table))) {

            }
            #Adding format style to the table header
            openxlsx::addStyle(
              outwb,
              sheet = sheet_name,
              hs2,
              rows = reference_row:(nrow(table_heading) + 7),
              cols = (reference_col:(
                reference_col - 1 + ncol(table_heading)
              )),
              gridExpand = TRUE
            )
            deleteData(
              outwb,
              sheet = sheet_name,
              cols = (reference_col:(
                reference_col - 1 + ncol(table_heading)
              )),
              rows = reference_row - 1,
              gridExpand = TRUE
            )



            #merge the All row
            openxlsx::mergeCells(
              outwb,
              sheet_name,
              rows = reference_row + nrow(table_heading),
              cols = ((reference_col):(reference_col + 1))
            )

            # Add N style to table
            for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                             reference_row -
                                                             1)) {
              openxlsx::addStyle(
                outwb,
                sheet_name,
                NDecColStyle,
                rows = i,
                cols = ((reference_col + 1):(
                  reference_col - 1 + ncol(table_heading)
                )),
                gridExpand = TRUE,
                stack = FALSE
              )
            }
            #adding grey to the labels
            addStyle(
              outwb,
              sheet = sheet_name,
              grey_bg,
              rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                              reference_row -
                                                              1),
              cols = reference_col + 1,
              gridExpand = TRUE
            )
            #adding bold grey to the variables
            addStyle(
              outwb,
              sheet = sheet_name,
              bold_grey_center,
              rows = ((reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) + reference_row -
                                                               1)
              ),
              cols = reference_col,
              gridExpand = TRUE
            )


            openxlsx::addStyle(
              outwb,
              sheet_name,
              NColStyle,
              rows = reference_row - 1 + nrow(table_heading) + which(grepl("All", table[, 2])),
              cols = ((reference_col + 1):(
                reference_col - 1 + ncol(table_heading)
              )),
              gridExpand = TRUE,
              stack = FALSE
            )
            openxlsx::addStyle(
              outwb,
              sheet_name,
              NBoldColStyle,
              rows = (reference_row + nrow(table_heading)),
              cols = ((reference_col + 1):(
                reference_col - 1 + ncol(table_heading)
              )),
              gridExpand = TRUE,
              stack = FALSE
            )

          }



          #setting the width of the column
          width_vec <- apply(table, 2,
                             function(x)
                               max(nchar(as.character(x)) + 2,
                                   na.rm = TRUE))
          width_vec_header <- nchar(colnames(table_heading))  + 2
          max_vec_header <- pmax(width_vec, width_vec_header)
          openxlsx::setColWidths(outwb,
                                 sheet_name,
                                 cols = (reference_col:(ncol(table) + reference_col -
                                                          1)),
                                 widths = max_vec_header)
          if (stack == "below") {
            reference_row <- reference_row + nrow(table_heading) + nrow(table) + 4
          }
          if (stack == "sideways") {
            reference_col <- reference_col + ncol(table) + 2
          }

        }



      } else{
        reference_row <- 1
        reference_col <- 0
        #sheet type
        sheet_type <- as.character(unlist(tables$type))
        #heading name
        table_heading <- tables$heading
        #table
        table <- as.data.frame(tables$table)
        reference_row <- reference_row + 4
        reference_col <- reference_col + 2
        #writing the data to the worksheet
        openxlsx::writeData(
          outwb,
          sheet_name,
          table,
          startCol = reference_col,
          startRow = reference_row - 1 + nrow(table_heading),
          borders = "all",
          borderColour = "#bfbfbf",
          headerStyle = hs2,
          borderStyle = "thin"
        )

        #writing the heading
        openxlsx::writeData(
          outwb,
          sheet_name,
          table_heading,
          startCol = reference_col,
          startRow = reference_row - 1,
          borders = "all",
          borderColour = "#bfbfbf",
          borderStyle = "thin"
        )


        if (sheet_type == "categorical" |
            sheet_type == "binary") {
          #writing the logic to merge
          tfdf <-
            as.data.frame(matrix(
              rep(F, nrow(table_heading) * ncol(table_heading)),
              nrow = nrow(table_heading),
              ncol = ncol(table_heading)
            ))
          j <- 1
          while (j <= ncol(table_heading)) {
            merge_column_start <- j
            merge_column_end <- j
            i <- 1
            while (i < nrow(table_heading)) {
              if (tfdf[i, j] == T) {
                i <- i + 1
                next()
              }
              temp <- table_heading[i, j]
              merge_row_start <- i
              merge_row_end <- i
              merge_column_end <- j
              for (k in ((i + 1):(nrow(table_heading) - 1))) {
                if (table_heading[k, j] == temp) {
                  merge_row_end <- k
                  next()
                } else{
                  break()
                }
              }
              while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end +
                                               1]) ==
                          rep(temp, length(
                            unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
                          )))) &
                     merge_column_end < ncol(table_heading)) {
                merge_column_end <- merge_column_end + 1
              }
              if (merge_column_end == 1) {
                openxlsx::mergeCells(
                  outwb,
                  sheet_name,
                  cols = reference_col - 1 + c(merge_column_start:merge_column_end),
                  rows = reference_row - 1 + c(merge_row_start:(merge_row_end +
                                                                  1))
                )
              } else{
                openxlsx::mergeCells(
                  outwb,
                  sheet_name,
                  cols = reference_col - 1 + c(merge_column_start:merge_column_end),
                  rows = reference_row - 1 + c(merge_row_start:merge_row_end)
                )
              }

              tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
                T
              i <- i + 1
            }
            j <- j + 1
          }


          # Add N style to table
          for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +  reference_row -
                                                           1)) {
            openxlsx::addStyle(
              outwb,
              sheet_name,
              NColStyle,
              rows = i,
              cols = c(reference_col - 1 + which(grepl(
                "All", colnames(table)
              ))),
              gridExpand = TRUE,
              stack = FALSE
            )
          }
          # Add % style to table
          for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                           reference_row -
                                                           1)) {
            openxlsx::addStyle(
              outwb,
              sheet_name,
              percentColStyle,
              rows = i,
              cols = c(reference_col - 1 + which(grepl(
                "Percent", colnames(table)
              ))),
              gridExpand = TRUE,
              stack = FALSE
            )

          }
          #Adding format style to the table header
          openxlsx::addStyle(
            outwb,
            sheet = sheet_name,
            hs2,
            rows = reference_row:(nrow(table_heading) + reference_row -
                                    1),
            cols = reference_col:(ncol(table_heading) + 1),
            gridExpand = TRUE
          )
          deleteData(
            outwb,
            sheet = sheet_name,
            cols = reference_col:(1 + ncol(table_heading)),
            rows = reference_row - 1,
            gridExpand = TRUE
          )


          if (sum(rowSums(is.na(table))) == 0) {
            grey_style <- bold_grey
          } else{
            grey_style <- grey_bg
          }

          #adding grey to the labels
          addStyle(
            outwb,
            sheet = sheet_name,
            grey_style,
            rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                            reference_row -
                                                            1),
            cols = reference_col,
            gridExpand = TRUE
          )

          #adding bold grey to the variables
          addStyle(
            outwb,
            sheet = sheet_name,
            bold_grey,
            rows = c((
              reference_row - 1 + nrow(table_heading) + (which(is.na(table[, 2])))
            )),
            cols = reference_col:(ncol(table) + 1),
            gridExpand = TRUE
          )

          # Add N style to table

          openxlsx::addStyle(
            outwb,
            sheet_name,
            NBoldColStyle,
            rows = (reference_row + nrow(table_heading)),
            cols = c(reference_col - 1 + which(grepl(
              "All", colnames(table)
            ))),
            gridExpand = TRUE,
            stack = FALSE
          )
          # Add % style to table

          openxlsx::addStyle(
            outwb,
            sheet_name,
            percentBoldColStyle,
            rows = reference_row + nrow(table_heading),
            cols = c(reference_col - 1 + which(grepl(
              "Percent", colnames(table)
            ))),
            gridExpand = TRUE,
            stack = FALSE
          )

          openxlsx::addStyle(
            outwb,
            sheet_name,
            bold_grey,
            rows = reference_row + nrow(table_heading) ,
            cols = reference_col,
            gridExpand = TRUE,
            stack = FALSE
          )



        } else{
          #writing the logic to merge
          tfdf <-
            as.data.frame(matrix(
              rep(F, nrow(table_heading) * ncol(table_heading)),
              nrow = nrow(table_heading),
              ncol = ncol(table_heading)
            ))
          j <- 1
          while (j <= ncol(table_heading)) {
            merge_column_start <- j
            merge_column_end <- j
            i <- 1
            while (i < nrow(table_heading)) {
              if (tfdf[i, j] == T) {
                i <- i + 1
                next()
              }
              temp <- table_heading[i, j]
              merge_row_start <- i
              merge_row_end <- i
              merge_column_end <- j

              for (k in ((i + 1):(nrow(table_heading)))) {
                if (table_heading[k, j] == temp) {
                  merge_row_end <- k
                  next()
                } else{
                  break()
                }
              }
              while ((all(unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1]) ==
                          rep(temp, length(
                            unique(table_heading[merge_row_start:merge_row_end, merge_column_end + 1])
                          )))) &
                     merge_column_end < ncol(table_heading)) {
                merge_column_end <- merge_column_end + 1
              }

              openxlsx::mergeCells(
                outwb,
                sheet_name,
                cols = reference_col - 1 + c(merge_column_start:merge_column_end),
                rows = reference_row + 1 + c(merge_row_start:merge_row_end)
              )


              tfdf[merge_row_start:merge_row_end, merge_column_start:merge_column_end] <-
                T
              i <- i + 1
            }
            j <- j + 1
          }


          #logic to merge the rows
          final <- c()
          for (i in 2:nrow(table)) {
            after <- table[i, 1]
            if (!is.na(after)) {
              final <- c(final, (i - 1))
            }
          }
          final <- c(final, nrow(table))
          final <- final + reference_row + nrow(table_heading)
          for (i in 1:(length(final) - 1)) {
            openxlsx::mergeCells(outwb,
                                 sheet_name,
                                 rows = c(final[i]:(final[i + 1] - 1)),
                                 cols = reference_col)
          }



          for (i in (reference_row + 1 + nrow(table_heading)):(reference_row + nrow(table_heading) + nrow(table))) {

          }
          #Adding format style to the table header
          openxlsx::addStyle(
            outwb,
            sheet = sheet_name,
            hs2,
            rows = reference_row:(nrow(table_heading) + 7),
            cols = reference_col:(ncol(table_heading) + 1),
            gridExpand = TRUE
          )
          deleteData(
            outwb,
            sheet = sheet_name,
            cols = reference_col:(1 + ncol(table_heading)),
            rows = reference_row - 1,
            gridExpand = TRUE
          )



          #merge the All row
          openxlsx::mergeCells(
            outwb,
            sheet_name,
            rows = reference_row + nrow(table_heading),
            cols = (reference_col):(reference_col + 1)
          )

          # Add N style to table
          for (i in (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                           reference_row -
                                                           1)) {
            openxlsx::addStyle(
              outwb,
              sheet_name,
              NDecColStyle,
              rows = i,
              cols = (reference_col + 1):(1 + ncol(table_heading)),
              gridExpand = TRUE,
              stack = FALSE
            )
          }
          #adding grey to the labels
          addStyle(
            outwb,
            sheet = sheet_name,
            grey_bg,
            rows = (reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) +
                                                            reference_row -
                                                            1),
            cols = reference_col + 1,
            gridExpand = TRUE
          )
          #adding bold grey to the variables
          addStyle(
            outwb,
            sheet = sheet_name,
            bold_grey_center,
            rows = ((reference_row + nrow(table_heading)):(nrow(table) + nrow(table_heading) + reference_row -
                                                             1)
            ),
            cols = reference_col,
            gridExpand = TRUE
          )


          openxlsx::addStyle(
            outwb,
            sheet_name,
            NColStyle,
            rows = reference_row - 1 + nrow(table_heading) + which(grepl("All", table[, 2])),
            cols = (reference_col = 1):(1 + ncol(table_heading)),
            gridExpand = TRUE,
            stack = FALSE
          )
          openxlsx::addStyle(
            outwb,
            sheet_name,
            NBoldColStyle,
            rows = reference_row + nrow(table_heading),
            cols = ((reference_col + 1):(
              reference_col - 1 + ncol(table_heading)
            )),
            gridExpand = TRUE,
            stack = FALSE
          )

        }



        #setting the width of the column
        width_vec <- apply(table, 2,
                           function(x)
                             max(nchar(as.character(x)) + 2,
                                 na.rm = TRUE))
        width_vec_header <- nchar(colnames(table_heading))  + 2
        max_vec_header <- pmax(width_vec, width_vec_header)
        openxlsx::setColWidths(outwb,
                               sheet_name,
                               cols = reference_col:(ncol(table) + 1),
                               widths = max_vec_header)


      }

    }

    #saving the workbook
    openxlsx::saveWorkbook(outwb, file = file_name, overwrite = TRUE)

  }