writeOpenxlsx {jamba} | R Documentation |
Export a data.frame to 'Excel' 'xlsx' format
Description
Export a data.frame to 'Excel' 'xlsx' format
Usage
writeOpenxlsx(
x,
file = NULL,
wb = NULL,
sheetName = "Sheet1",
startRow = 1,
startCol = 1,
append = FALSE,
headerColors = c("lightskyblue1", "lightskyblue2"),
columnColors = c("aliceblue", "azure2"),
highlightHeaderColors = c("tan1", "tan2"),
highlightColors = c("moccasin", "navajowhite"),
borderColor = "gray75",
borderPosition = "BottomRight",
highlightColumns = NULL,
numColumns = NULL,
fcColumns = NULL,
lfcColumns = NULL,
hitColumns = NULL,
intColumns = NULL,
pvalueColumns = NULL,
numFormat = "#,##0.00",
fcFormat = "#,##0.0",
lfcFormat = "#,##0.0",
hitFormat = "#,##0.0",
intFormat = "#,##0",
pvalueFormat = "[>0.01]0.00#;0.00E+00",
numRule = c(1, 10, 20),
fcRule = c(-6, 0, 6),
lfcRule = c(-3, 0, 3),
hitRule = c(-1.5, 0, 1.5),
intRule = c(0, 100, 10000),
pvalueRule = c(0, 0.01, 0.05),
numStyle = c("#F2F0F7", "#B4B1D4", "#938EC2"),
fcStyle = c("#4F81BD", "#EEECE1", "#C0504D"),
lfcStyle = c("#4F81BD", "#EEECE1", "#C0504D"),
hitStyle = c("#4F81BD", "#EEECE1", "#C0504D"),
intStyle = c("#EEECE1", "#FDA560", "#F77F30"),
pvalueStyle = c("#F77F30", "#FDC99B", "#EEECE1"),
doConditional = TRUE,
doCategorical = TRUE,
colorSub = NULL,
freezePaneColumn = 0,
freezePaneRow = 2,
doFilter = TRUE,
fontName = "Arial",
fontSize = 12,
minWidth = getOption("openxlsx.minWidth", 8),
maxWidth = getOption("openxlsx.maxWidth", 40),
autoWidth = TRUE,
colWidths = NULL,
wrapCells = FALSE,
wrapHeaders = TRUE,
headerRowMultiplier = 5,
keepRownames = FALSE,
verbose = FALSE,
...
)
Arguments
x |
|
file |
|
wb |
|
sheetName |
|
startRow , startCol |
|
append |
|
headerColors , columnColors , highlightHeaderColors , highlightColors , borderColor , borderPosition |
default values for the 'Excel' worksheet background and border colors. As of version 0.0.29.900, colors must use valid 'Excel' color names. |
highlightColumns , numColumns , fcColumns , lfcColumns , hitColumns , intColumns , pvalueColumns |
|
numFormat , fcFormat , lfcFormat , hitFormat , intFormat , pvalueFormat |
|
numRule , fcRule , lfcRule , hitRule , intRule , pvalueRule |
|
numStyle , fcStyle , lfcStyle , intStyle , hitStyle , pvalueStyle |
|
doConditional |
|
doCategorical |
|
colorSub |
|
freezePaneColumn , freezePaneRow |
|
doFilter |
|
fontName |
|
fontSize |
|
minWidth , maxWidth , autoWidth |
|
colWidths |
|
wrapCells |
|
wrapHeaders |
|
headerRowMultiplier |
|
keepRownames |
|
verbose |
|
... |
additional arguments are passed to |
Details
This function is a minor but useful customization of the
openxlsx::saveWorkbook()
and associated functions, intended
to provide some pre-configured formatting of known column
types, typically relevant to statistical values, and
in some cases, gene or transcript expression values.
There are numerous configurable options when saving an 'Excel' worksheet, most of the defaults in this function are intended not to require changes, but are listed as formal function arguments to make each option visibly obvious.
If colorSub
is supplied as a named vector of colors, then
by default text values will be colorized accordingly, which
can be especially helpful when including data with categorical
text values.
This function pre-configures formatting options for the following column data types, each of which has conditional color-formatting, defined numeric ranges, and color scales.
- int
integer values, where numeric values are formatted without visible decimal places, and the
big.mark=","
standard is used to help visually distinguish large integers. The color scale is by default c(0, 100, 10000).- num
numeric values, with fixed number of visible decimal places, which helps visibly align values along each row.
- hit
numeric type, a subset of "int" intended when data is flagged with something like a "+1" or "-1" to indicate a statistical increase or decrease.
- pvalue
P-value, where numeric values range from 1 down near zero, and values are formatted consistently with scientific notation.
- fc
numeric fold change, whose values are expected to range from 1 and higher, and -1 and lower. Decimal places are by default configured to show one decimal place, to simplify the 'Excel' visual summary.
- lfc
numeric log fold change, whose values are expected to be centered at zero. Decimal places are by default configured to show one decimal place, to simplify the 'Excel' visual summary.
- highlight
character and undefined columns to be highlighted with a brighter background color, and bold text.
For each column data type, a color scale and default numeric range is defined, which allows conditional formatting of cells based upon expected ranges of values.
A screenshot of the file produced by the example is shown below.
Value
Workbook
object as defined by the openxlsx
package
is returned invisibly with invisible()
. This Workbook
can be used in argument wb
to provide a speed boost when
saving multiple sheets to the same file.
See Also
Other jam export functions:
applyXlsxCategoricalFormat()
,
applyXlsxConditionalFormat()
,
readOpenxlsx()
,
set_xlsx_colwidths()
,
set_xlsx_rowheights()
Examples
# set up a test data.frame
set.seed(123);
lfc <- -3:3 + stats::rnorm(7)/3;
colorSub <- nameVector(
rainbow2(7),
LETTERS[1:7])
df <- data.frame(name=LETTERS[1:7],
int=round(4^(1:7)),
num=(1:7)*4-2 + stats::rnorm(7),
fold=2^abs(lfc)*sign(lfc),
lfc=lfc,
pvalue=10^(-1:-7 + stats::rnorm(7)),
hit=sample(c(-1,0,0,1,1), replace=TRUE, size=7));
df;
# write to tempfile for examples
if (check_pkg_installed("openxlsx")) {
out_xlsx <- tempfile(pattern="writeOpenxlsx_", fileext=".xlsx")
writeOpenxlsx(x=df,
file=out_xlsx,
sheetName="jamba_test",
colorSub=colorSub,
intColumns=2,
numColumns=3,
fcColumns=4,
lfcColumns=5,
pvalueColumns=6,
hitColumn=7,
freezePaneRow=2,
freezePaneColumn=2,
append=FALSE);
# now read it back
df_list <- readOpenxlsx(xlsx=out_xlsx);
sdim(df_list)
}