library(readxl)

filename <- "Dixson et al. 2014 Raw Data .xlsx"
# Code to read all sheets borrowed from https://stackoverflow.com/questions/12945687/
sheetnames <- readxl::excel_sheets(filename)
alldata <- lapply(sheetnames, function(x) readxl::read_excel(filename, sheet=x))
alldata <- lapply(alldata, as.data.frame)     # use data frames rather than tibbles
names(alldata) <- sheetnames

fish.sheet.first <- 1
fish.sheet.last <- 15
n.fish.sheets <- (fish.sheet.last - fish.sheet.first + 1)

# Convert a column sequence number to an Excel alphabetic column name.
excel_column <- function (n)
{
  l1 <- floor((n - 1) / 26)
  l2 <- ((n - 1) %% 26) + 1

  result <- rawToChar(as.raw(l2 + 64))
  if (l1 > 0) {
    result <- paste0(rawToChar(as.raw(l1 + 64)), result)
  }

  return(result)
}

cols.per.band <- 4          # each set of two tests has four columns of data
rows.per.chunk <- 20        # each test chunk consists of observations of 20 fish

extra.row.messages <- c()
ranges <- matrix(0, ncol=24, nrow=n.fish.sheets)
range.messages <- c()
not960.messages <- c()
total.obs <- 0

tests <- list(rep(NA, n.fish.sheets * 6 * 10 * 2 * 2))
ntest <- 0

for (sheet in fish.sheet.first:fish.sheet.last) {     # fish species
  sheet.name <- sheetnames[sheet]
  fishdata <- alldata[[sheet]]
  fish.colnames <- names(fishdata)

  usecols <- c()
  for (cn in 1:length(fish.colnames)) {   # eliminate housekeeping columns
    cname <- fish.colnames[cn]

    if (    (substr(cname, 1, 12) == "Location Cap")
         || (substr(cname, 1, 12) == "Individ. Ave")
         || (substr(cname, 1, 11) == "Indivd. Ave")
         || (substr(cname, 1, 8) == "Score...")
         || (substr(cname, 1, 7) == "Species")
         || (substr(cname, 1, 4) == "n...")
         || (substr(cname, 1, 3) == "...")
    ) {
      next
    }

    usecols <- c(usecols, cn)
  }

  for (n.band in seq(1, length(usecols), cols.per.band)) {
    cols <- usecols[n.band:(n.band + cols.per.band - 1)]

    for (row1 in (((0:5) * 26) + 1)) {
      if (    (sheet == 15)     # hack for last sheet, where last two chunks are one line higher
           && (n.band >= 33)
           && (row1 > 130)
      ) {
        row1 <- row1 - 1
      }

      chunk <- fishdata[row1:(row1 + rows.per.chunk), cols]     # 21 rows for now

      if (    (sheet == 8)     # hack for NA in sheet 8
           && (n.band == 5)
           && (row1 == 1)
           && (is.na(chunk[3, 2]))
      ) {
        chunk[3, 2] <- 0
      }

      last.row <- chunk[(rows.per.chunk + 1),]
      if (!is.na(last.row[1])) {                   # 21st row is not empty
        extra.row.messages <- c(extra.row.messages, paste0(
          sheet.name, "\t"
        , (as.numeric(row.names(last.row)) + 1), "\t"     # add 1 for Excel header row (not included in data frame)
        , excel_column(cols[1]), "\t"
        , paste(last.row, collapse=" ")
        ))
      }

      chunk <- chunk[1:rows.per.chunk,]
      obs <- sum(rowSums(chunk))
      total.obs <- total.obs + obs
      if (obs != 960) {
        t.strings <- rep("\t", 2)

        for (test in 1:2) {
          ti <- ((test - 1) * 2) + 1
          sc1 <- sum(chunk[ti])
          sc2 <- sum(chunk[ti + 1])
          ts <- sc1 + sc2
          if (ts != 480) {
            t.strings[test] <- paste0(sc1, "+", sc2, "=", ts)
          }
        }

        not960.messages <- c(not960.messages, paste0(
          sheet.name, "\t"
          , (row1 + 1), "\t"      # add 1 for Excel header row (not included in data frame)
          , excel_column(cols[1]), "\t"
          , obs, "\t\t"
          , t.strings[1], "\t"
          , t.strings[2]
        ))
      }

      for (cn in 1:cols.per.band) {     # use this line to consider all waters
        coldata <- chunk[cn]

        excel.col <- excel_column(usecols[(n.band + cn - 1)])
        range <- max(coldata) - min(coldata) + 1
        ranges[sheet, range] <- ranges[sheet, range] + 1
        if ((range < 4) || (range > 9)) {
          range.messages <- c(range.messages, paste0(
            sheet.name, "\t"
          , (row1 + 1), "\t"      # add 1 for Excel header row (not included in data frame)
          , excel.col, "\t"
          , range, "\t"
          , min(coldata), "-", max(coldata)
          ))
        }

        ntest <- ntest + 1
        tests[[ntest]] <- list("sheet"=sheet, "sheetname"=sheet.name, "row1"=(row1 + 1), "column"=excel.col, "values"=coldata)
      }
    }
  }
}

if (length(extra.row.messages) > 0) {
  cat("Extra rows\n")
  cat("Sheet\t\tRow\tColumn1\tValues\n")
  for (m in extra.row.messages) {
    cat(m, "\n", sep="")
  }
}

if (length(range.messages) > 0) {
  cat("Exceptional ranges\n")
  cat("Sheet\t\tRow1\tColumn\tRange\tMin-Max\n")
  for (m in range.messages) {
    cat(m, "\n", sep="")
  }
}

if (length(not960.messages) > 0) {
  cat("Groups with a number of observations other than 960\n")
  cat("Sheet\t\tRow1\tColumn\tObservations\tTest 1\t\tTest 2\n")
  for (m in not960.messages) {
    cat(m, "\n", sep="")
  }
}

cat("Total observations = ", total.obs, "\n", sep="")

if (0) {
  cat("Sheet#\tSheet name\tRow1\tColumn\tResults\n")
  for (t in tests) {
    cat(t$sheet, "\t", t$sheetname, "\t", t$row1, "\t", t$column, "\t", paste(unlist(t$values), collapse=" "), "\n", sep="")
  }
}