XLtwoWay: Two-way Contingency Tables exported to a spreadsheet
In table1xls: Exports Reproducible Summary Tables to Multi-Tab Spreadsheet Files (.xls or .xlsx)

Description Usage Arguments Details Value Note Author(s) See Also Examples

Produces 2-way contingency tables, optionally with percentages, exports them to a spreadsheet, and saves the file.

XLtwoWay(wb, sheet, rowvar, colvar, table1mode = FALSE, sumby = 1,
  rowTitle = "", rowNames = NULL, colNames = NULL, ord = NULL,
  row1 = 1, col1 = 1, title = NULL, header = FALSE, purge = FALSE,
  digits = ifelse(length(rowvar) >= 500, 1, 0), useNA = "ifany",
  percents = TRUE, combine = percents, testname = "chisq.test",
  pround = 3, testBelow = FALSE, margins = TRUE, ...)

`wb`	an `workbook-class` object
`sheet`	numeric or character: a worksheet name (character) or position (numeric) within `wb`.
`rowvar`	vector: categorical variable (logical, numeric, character, factor, etc.) for the table's rows
`colvar`	vector: categorical variable (logical, numeric, character factor, etc.) for the table's columns
`table1mode`	logical: is the function called from `XLtable1`? If `TRUE`, some modifications will be made to the output. Default `FALSE`.
`sumby`	whether percentages should be calculated across rows (1, default) or columns (2).
`rowTitle`	character: the title to be placed above the row name column (default empty string)
`rowNames, colNames`	character vector of row and column names. Default behavior (`NULL`): automatically determined from data
`ord`	numeric vector specifying row-index order in the produced table. Default (`NULL`) is no re-ordering.
`row1, col1`	numeric: the first row and column occupied by the table (title included if relevant).
`title`	character: an optional overall title to the table. Default (`NULL`) is no title.
`header`	logical: should a header row with the captions "Counts:" and "Percentages:" be added right above the tables? Relevant only when `combine=FALSE,percents=TRUE`)
`purge`	logical: should `sheet` be created anew, by first removing the previous copy if it exists? (default `FALSE`)
`digits`	numeric: how many digits (after the decimal point) to show in the percents? Defaults to 1 if n>=500, 0 otherwise.
`useNA`	How to handle missing values. Passed on to `table` (see help on that function for options).
`percents`	logical: would you like only a count table (`FALSE`), or also a percents table side-by-side with the the count table (`TRUE`, default)?
`combine`	logical: should counts and percents be combined to the popular `"Count(percent)"` format, or presented side-by-side in separate tables? (default: same value as `percents`)
`testname`	string, the name of a function to run a significance test on the table. Default `chisq.test`. If you want no test, set `testname=NULL`
`pround`	number of significant digits in test p-value representation. Default 3.
`testBelow`	logical, should test p-value be placed right below the table? Default `FALSE`, which places it next to the table's right edge, one row below the column headings.
`margins`	logical: should margins with totals be returned? Default `TRUE`.
`...`	additional arguments as needed, to pass on to `get(textfun)`

This function produces two-way cross-tabulated counts of unique values of rowvar, colvar, optionally with percentages, calculated either by row (sumby=1, default) or column (sumby=2). Row and column margins are also produced. ##' Tables are automatically saved to the file associated with the wb spreadsheet object.

There is an underlying asymmetry between rows and columns, because the tables are converted to data frame in order for writeWorksheet to export them. The percents can be in parentheses in the same cells as the counts (combine=TRUE, default), in an identically-sized table on the side (combine=FALSE,percents=TRUE), or absent (combine=FALSE,percents=FALSE). If you want no margins, just use the simpler function XLgeneric.

The function returns invisibly, after writing the data into sheet.

The worksheet sheet does not have to pre-exist; the function will create it if it doesn't already exist.

By default, if sheet exists, it will be written into - rather than completely cleared and rewritten de novo. Only existing data in individual cells that are part of the exported tables' target range will be overwritten. If you do want to clear an existing sheet while exporting the new tables, set purge=TRUE. This behavior, and the usage of purge, are the same across all table1xls export functions.

This function uses the internal function fancytab2 which produces 2-way tables with counts, percentages and margins.

Assaf P. Oron <assaf.oron.at.seattlechildrens.org>

Uses writeWorksheet to access the spreadsheet. See setStyleAction to control the output style. If interested in one-way tables, see XLoneWay.

### Contrived example looking at, e.g., the distribution of A-K-Q card counts
### in two partners' Bridge hands


hand1=rhyper(1000,12,40,13)
hand2=rhyper(1000,12-hand1,27+hand1,13)
handNames=c("0-1",2:4,"5 or more")


### The problem is ridiculously symmetric, so I de-symmetrize the presentation slightly:

book3<-XLwriteOpen("hands.xls") 
XLtwoWay(book3,"PartnersAKQcounts",cut(hand1,c(0,2:6,14)-0.5),cut(hand2,c(0,2:5,14)-0.5),
         rowTitle="AKQ's in Hand 1 (rows) vs. Hand 2",
         rowNames=c(handNames[-5],5,"6 or more","Total"),
         colNames=c(handNames,"Total"),header=TRUE)

## Same table, but percents now condition on columns rather than rows, 
## counts/pct header row removed - but a title added.
## Also, Chi-square p-value now placed below the table rather than the default top-right corner
XLtwoWay(book3,"PartnersAKQcounts",cut(hand1,c(0,2:6,14)-0.5),cut(hand2,c(0,2:5,14)-0.5),
         rowTitle="AKQ's in Hand 1 (rows) vs. Hand 2",
         rowNames=c(handNames[-5],5,"6 or more","Total"),
         colNames=c(handNames,"Total"),header=FALSE,row1=12,sumby=2,
         title="Now Percents are Summed by Column:",testBelow=TRUE)

cat("Look for",paste(getwd(),"hands.xls",sep='/'),"to see the results!\n")