% !TEX TS-program = Sweave

\documentclass{article}

%\VignetteIndexEntry{The XLConnect Package}
%\VignetteDepends{XLConnect}
%\VignetteDepends{ggplot2}
%\VignetteDepends{zoo}
% \usepackage{Sweave}

\usepackage[utf8]{inputenc}
% So that more colours can be used, not only the pre-defined ones
% List of colours here: https://en.wikibooks.org/wiki/LaTeX/Colors
\usepackage[dvipsnames]{xcolor}
\usepackage[colorlinks]{hyperref}
\usepackage{datetime}

% To get URLs in a normal font, by using \urlwofont
\usepackage{url}

\newcommand{\urlwofont}[1]
{
\urlstyle{same}\url{#1}
}

% Set colours of links in the document
\hypersetup
{
    colorlinks,
    citecolor	=	black,		% color of links to bibliography
    filecolor	=	black,		% color of file links
    linkcolor	=	CadetBlue,	% color of internal links, e.g. Table of Contents
    urlcolor	=	CadetBlue	% color of external links
}


% Extra text effects (strikethrough)
\usepackage{ulem}

% Sexyness
\usepackage{tikz}
\usetikzlibrary{calc,positioning}
\usetikzlibrary{shapes.multipart,shapes.geometric,shapes.symbols,shapes.arrows}
\usetikzlibrary{decorations.shapes,decorations.text,decorations.pathreplacing}

% Page layout
\usepackage[parfill]{parskip}
\usepackage[letterpaper,margin=1in]{geometry}
\usepackage{multicol}
\usepackage{pdflscape}

% Graphics control.
\usepackage{graphicx}
\usepackage{float}

% Bibliography
\usepackage{natbib}
\bibliographystyle{agufull04}

% Extra pretty pretty printing.
\usepackage{tikzCodeBlock}

% Macros created for this document
\input{tikzDeviceMacros}

% Make sure you input tikzDeviceMacros first- or
% LaTeX will smite you!
\usepackage{Sweave}

<<setup,echo=F,results=hide>>=
if (!file.exists('figs')) dir.create('figs')
@

<<load,echo=F,results=hide>>=
require(XLConnect)
@

\title{The \pkg{XLConnect} Package\\
{\small\urlwofont{https://mirai-solutions.ch}}\\[-0.3cm]
{\small\urlwofont{https://cran.r-project.org/package=XLConnect}}
}


% \author{\small{Author of the \pkg{XLConnect} package:} \\\textcolor{Fuchsia}{\bf{Martin Studer}}, \\\small{Mirai Solutions GmbH}
\author{\small{Author of this Vignette:}	\\\textcolor{Fuchsia}{\bf{Anna Maria Ksiezopolska}}, \\\small{\href{https://mirai-solutions.ch}{\textcolor{Black}{Mirai Solutions GmbH}}}}

\date{\pkg{XLConnect} Version \Sexpr{getOption("XLConnect.Version")}\qquad \monthname, \the\year}

\begin{document}
\SweaveOpts{concordance=TRUE}
	
\SweaveOpts{echo=F,prefix.string=figs/fig}


%----------------------------  Cover Page -----------------------------------------------------------

\thispagestyle{empty}

% The title page

\vbox{}
\vspace{0.5\textheight}
\begin{tikzpicture}[remember picture,overlay]

	\node[anchor=west,scale=3, inner sep=0pt, outer sep=0pt] (title) at (1cm,0) 
		{\LARGE\bfseries{\color{black}XLConnect}\thinspace\Huge};
	
	\draw[line width=2pt] ([yshift=-5pt]title.base west) -- ([yshift=-5pt]title.base east);	


	\coordinate (titleOut) at ($(title.north east) + (24pt,0pt)$);
	\coordinate (titleIn) at ($(title.base west) - (6pt,24pt)$);
		
	\node[color=black!80,below=24pt of title.base east,anchor=west] (sub) at (3cm,-1)
		{\bfseries\itshape\Large{} A platform-independent interface to Excel};

\end{tikzpicture}
\vfill	
\vbox{}

\begin{tikzpicture}[remember picture,overlay,>=stealth]

	\node (R) at ($(current page.south west) + (17cm,3cm)$)
		 {\scalebox{0.4}{\includegraphics[width=0.8\textwidth]{img/Rlogo}}};

	\tikzset{
		bigger/.style={decoration={shape start size=.125cm, shape end size=.3cm}},
		decoration={shape backgrounds,
			shape sep={.25cm, between borders},shape scaled,shape=circle}
	}
			
	\fill[decorate,bigger, black!25] (R) to[out=170,in=240] (titleIn);	
	
\end{tikzpicture}

% Excel icon on top right of the title page
% Source: https://web.archive.org/web/20240821110005/https://www.iconarchive.com/show/softdimension-icons-by-benjigarner/Excel-icon.html

\begin{tikzpicture}[remember picture,overlay,>=stealth]

	\node (Excel) at ($(current page.north west) + (3.5cm,-3.5cm)$)
		 {\scalebox{0.35}{\includegraphics[width=0.8\textwidth]{img/Excel}}};
		 
	\tikzset{
		bigger/.style={decoration={shape start size=.125cm, shape end size=.3cm}},
		decoration={shape backgrounds,
			%shape sep={.25cm, between borders},shape scaled,shape=dart}
			shape sep={.25cm, between borders},shape scaled,shape=circle}
	}

	\fill[decorate,thick,bigger,color=black!25]
		(titleOut) to[out=20,in=260] (Excel);
	
\end{tikzpicture}

\newpage

%----------------------------  Table of Contents -----------------------------------------------------------

\pagenumbering{roman}

\maketitle

\tableofcontents


\newpage


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

% Documentation starts here.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

\pagenumbering{arabic}

\section{Introduction}

\subsection{Scope and purpose of this document}
This document is a user manual for the \pkg{XLConnect} R package. It is meant to be a top-level introduction and some of the 
more advanced features of \pkg{XLConnect} are not presented here. For such details, please refer to the
\href{https://CRAN.R-project.org/package=XLConnect/XLConnect.pdf}{Reference Manual}.

\vspace{0.5cm}

\tikzDocDisclaim{\bfseries{Examples}\vspace{2mm}}
{
\vskip\baselineskip
Any examples presented in this vignette can be found in the XLConnect.R script file, which you can find in the top level library
directory. You can also open the document by using the following command:
\vspace{0.2cm}
\\\code{edit(file = system.file("XLConnect.R", package = "XLConnect"))}
\vskip\baselineskip
\vspace{0.5cm}
}

\subsection{Introduction to XLConnect}

\pkg{XLConnect} is a package that allows for reading, writing and manipulating Microsoft Excel 
files from within R. It uses the Apache POI API\footnote{ For more information on the Apache POI API, see the \url{https://poi.apache.org/} webpage.}  
as the underlying interface.

%\footnotetext{ For more information on the Apache POI API, see the \url{https://poi.apache.org/} webpage.} 

\pkg{XLConnect} allows you to produce formatted Excel reports, including graphics, straight from within R. 
This enables automation of manual formatting and reporting processes. Reading and writing named ranges 
enables you to process complex inputs and outputs in an efficient way.

\vspace{0.5cm}

% Box with XLConnect's main features
\tikzDocDisclaim{\bfseries{XLConnect}'s Main Features\vspace{2mm}}
{
\vskip\baselineskip

\begin{itemize}

  \item{Reading \& writing of {\bf Excel worksheets} (via data.frames)}
  \item{Reading \& writing of {\bf named ranges} (via data.frames)}
  \item{Creating, removing, renaming and cloning worksheets}
  \item{Adding {\bf graphics}}
  \item{Specifying {\bf cellstyles}: data formats, borders, back- and foreground fill color, fill pattern, text wrapping}
  \item{Controlling {\bf sheet visibility}}
  \item{Defining {\bf column width} and {\bf row height}}
  \item{{\bf Merging/unmerging} cells}
  \item{Setting/getting {\bf cell formulas}}
  \item{Defining {\bf formula recalculation} behavior (when workbooks are opened)}
  \item{Setting {\bf auto-filters}}
  \item{{\bf Style actions}: controlling application of cell styles when writing (e.g. when using templates)}
  \item{Defining behavior when {\bf error cells} are encountered}

\end{itemize}
  
\vskip\baselineskip

\vspace{0.5cm}

}

\section{Installation}
\label{part:one}

%----------------------------  Package Loading -----------------------------------------------------------

\subsection{Software Requirements}

\pkg{XLConnect} is completely cross-platform and as such runs under Windows, Unix/Linux and Mac (32- and 64-bit).
It does {\bf not} require an installation of Microsoft Excel, or any special drivers. 

All you need to use \pkg{XLConnect} are the following:

\begin{itemize}
\item{R, version 2.10.0 or higher}
\item{Java Runtime Environment (JRE), version 6 or higher}
\end{itemize}

\vspace{0.5cm}

\subsection{Package Installation}

The \pkg{XLConnect} package is part of the Comprehensive R Archive Network (CRAN). 
It can be easily installed by using the \code{install.packages()} command in your R session:

\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
	install.packages("XLConnect")
\end{tikzCodeBlock}	

\section{Basic Usage and Examples}
\label{part:two}

\subsection{Getting Started}

To load the package, use the \code{library()} or \code{require()} command in your R session:

\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
	library(XLConnect)
\end{tikzCodeBlock}

Now, you are ready to use \pkg{XLConnect}! 

The User Manual (this document) can be opened by entering the following command:

\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
	vignette("XLConnect")
\end{tikzCodeBlock}

The Reference Manual, containing help pages for each function within the package, can be opened by using the \code{help()} command.

\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
	help(XLConnect)
\end{tikzCodeBlock}

\subsection{loadWorkbook() - loading/creating an Excel workbook}
The \code{loadWorkbook()} function loads a Microsoft Excel workbook, so that it can then be further manipulated. 
Setting the \code{create} argument to \code{TRUE} will ensure the file will be created, if it does not exist yet. 

Both .xls and .xlsx file formats can be used.
\begin{tikzCodeBlock}[title={Load an Excel workbook (create if not existing)},listing style=sweavechunk]
loadWorkbook(filename, create = TRUE)
\end{tikzCodeBlock}

\subsection{Writing Excel files: basic functions}

\subsubsection{createSheet() - creating sheets within an Excel workbook}

\code{createSheet()} creates a sheet of a chosen \code{name} in the workbook specified as the \code{object} argument. 
\begin{tikzCodeBlock}[title={Create a worksheet of a chosen name within a workbook},listing style=sweavechunk]
createSheet(object, name)
\end{tikzCodeBlock}

\subsubsection{writeWorksheet() - writing into sheets within an Excel workbook}
\code{writeWorksheet()} writes \code{data} into a worksheet (name or index specified as the \code{sheet} argument) 
of an Excel workbook (\code{object}). The \code{startRow} and \code{startCol} are both 1 by default, meaning that if they are
not explicitly specified, the data will start being filled into the A1 cell of the worksheet.

\begin{tikzCodeBlock}[title={Write into a worksheet},listing style=sweavechunk]
writeWorksheet(object, data, sheet, startRow = 1, startCol = 1,
               header = TRUE)
\end{tikzCodeBlock}

\subsubsection{createName() - creating names within an Excel workbook}

\code{createName()} creates a \code{name} for a specified \code{formula} in a workbook.
The \code{overwrite} argument lets you define behaviour if the name already exists. If set to \code{TRUE}, the existing
name will be removed before creating a new one. If set to \code{FALSE} (default setting), an exception will be thrown.
\begin{tikzCodeBlock}[title={Create a name for a specified formula within a workbook},listing style=sweavechunk]
createName(object, name, formula, overwrite)
\end{tikzCodeBlock}

\subsubsection{writeNamedRegion() - writing named regions into an Excel workbook}
 
\code{writeNamedRegion()} writes a named range into a workbook. The \code{data} is assumed to be 
a \code{data.frame} (or list of \code{data.frames}, if multiple named regions are written with one call). 
The \code{header} argument allows you to specify whether column names should be written. 
\begin{tikzCodeBlock}[title={Write a named range into a workbook},listing style=sweavechunk]
writeNamedRegion(object, data, name, header)
\end{tikzCodeBlock}

\subsubsection{saveWorkbook() - saving a workbook to an Excel file}

\code{saveWorkbook()} saves a workbook to the corresponding Excel file and writes the file to disk.
\begin{tikzCodeBlock}[title={Save a workbook to a chosen Excel file},listing style=sweavechunk]
saveWorkbook(object)
\end{tikzCodeBlock}

\subsubsection{writeWorksheetToFile() - writing into sheets of an Excel file}

\code{writeWorksheetToFile()} is a wrapper function, calling \code{loadWorkbook()}, \code{createSheet()} and 
\code{saveWorkbook()} functions subsequently. It therefore allows for writing data into worksheets of an Excel 
file in one call. 

The \code{file} is the path name of the file to write to. The \code{styleAction} argument controls
the application of cell styles when writing to Excel (type \code{?setStyleAction} into the console to
see the supported styles). Within the \code{'...'} argument, arguments for the \code{writeWorksheet} function
can be passed.

\begin{tikzCodeBlock}[title={Write data to an Excel worksheet},listing style=sweavechunk]
writeWorksheetToFile(file, styleAction = XLC$STYLE_ACTION.XLCONNECT, ...)
\end{tikzCodeBlock}

\subsubsection{writeNamedRegionToFile() - writing named regions into an Excel file}
\code{writeNamedRegionToFile()} is a convenient wrapper function, calling \code{loadWorkbook()}, \code{createSheet()},
\code{createName()}, \code{writeNamedRegion()} and \code{saveWorkbook()} functions subsequently, therefore allowing
for writing named regions to an Excel file in one call.

The \code{file} is the path name of the file to write to. The name of the named region is passed as the \code{name}
argument and the data to write, as \code{data}. The \code{header} argument specifies if column names should be written. 
If you are writing data to a template in which the named region already exists, you don't need to specify the \code{formula}.
Otherwise, if you are creating the file from scratch, supply the named region location as \code{formula}. The \code{styleAction} 
argument controls the application of cell styles when writing to Excel (type \code{?setStyleAction} into the console to
see the supported styles).

\begin{tikzCodeBlock}[title={Write data into an Excel named region},listing style=sweavechunk]
writeNamedRegionToFile(file, data, name, formula = NA, header = TRUE,
                       styleAction = XLC$STYLE_ACTION.XLCONNECT)
\end{tikzCodeBlock}

\subsection{Example: writing a sheet}\label{Example1}
Let's see how the basic functions introduced in this section can be used to create and save an Excel file.
We will use the {\bf ChickWeight} dataset (built-in R dataset) for this simple example.

The code below first loads the "XLConnectExample1.xlsx" workbook, using \code{loadWorkbook()}. 
If the workbook does not exist yet, the function creates it (since the \code{create} argument is set to \code{TRUE}).

Then, via \code{createSheet()}, a sheet named "chickSheet" is created within the workbook. 
We then use \code{writeWorksheet()} to write the {\bf ChickWeight} data frame into chickSheet.

At the end, we use \code{saveWorkbook()} to save the XLConnectExample1.xlsx file.

<<removeFile1,echo=F,results=hide>>=
if (file.exists("XLConnectExample1.xlsx"))
  file.remove("XLConnectExample1.xlsx")
@

<<simpleEx1,echo=T,results=hide>>=
require(XLConnect)
wb <- loadWorkbook("XLConnectExample1.xlsx", create = TRUE)
createSheet(wb, name = "chickSheet")
writeWorksheet(wb, ChickWeight, sheet = "chickSheet", startRow = 3, startCol = 4)
saveWorkbook(wb)
@

Please note that only at the point when we call \code{saveWorkbook()}, the Excel file is written to disk. 
All the previous operations are performed in-memory, which has great performance advantages.

\begin{figure}[htb]
\begin{center}
\caption{ChickWeight data frame written into the XLConnectExample1.xlsx file}
\includegraphics[width=9cm,keepaspectratio]{img/XLConnectExample1.png}
\end{center}
\end{figure}

\subsection{Example: writing a sheet with one call}\label{sec:options}
The four lines of code presented in the previous example can be replaced with a single call of
the \\\code{writeWorksheetToFile()} function:

<<removeFile2,echo=F,results=hide>>=
if (file.exists("XLConnectExample2.xlsx"))
  file.remove("XLConnectExample2.xlsx")
@

<<simpleEx2,echo=T,results=hide>>=
require(XLConnect)
writeWorksheetToFile("XLConnectExample2.xlsx", data = ChickWeight,
                     sheet = "chickSheet", startRow = 3, startCol = 4)
@

\code{writeWorksheetToFile()} loads the workbook, creates the sheet and finally saves the workbook. When you only need 
to write one sheet into an Excel file, this is probably the better choice. If you need to write more sheets, however, 
using the functions presented in the previous example will be more efficient. This is because calling \code{writeWorksheetToFile()}
multiple times will open, write and close the Excel file with each call. Using the functions in the first example will,
in contrast, allow you to open the workbook, do multiple operations on it and only then close it.

\subsection{Example: writing a named region}\label{Example3}

In this example we will show how to write data into a named region. We will use the {\bf women} dataset.

Similarly as in the example of writing an Excel sheet, we first load the workbook, using \code{loadWorkbook()}, 
and then create a sheet named "womenData", using \code{createSheet()}.

We then use \code{createName()} to produce a named region "womenName", starting in the C5 cell of the "womenData" sheet.
Calling \code{writeNamedRegion()} writes the name into the workbook. At the end, we use \code{saveWorkbook()} to write 
the Excel file to disk.

<<removeFile3,echo=F,results=hide>>=
if (file.exists("XLConnectExample3.xlsx"))
  file.remove("XLConnectExample3.xlsx")
@

<<simpleEx3,echo=T,results=hide>>=
require(XLConnect)
wb <- loadWorkbook("XLConnectExample3.xlsx", create = TRUE)
createSheet(wb, name = "womenData")
createName(wb, name = "womenName", formula = "womenData!$C$5", overwrite = TRUE)
writeNamedRegion(wb, women, name = "womenName")
saveWorkbook(wb)
@

\begin{figure}[htb]
\begin{center}
\caption{women dataset written into womenName named region in the XLConnectExample3.xlsx file}
\includegraphics[height=9cm,keepaspectratio]{img/XLConnectExample3.png}
\end{center}
\end{figure}

\newpage\subsection{Example: writing a named region with one call}\label{sec:options}
The \code{writeNamedRegionToFile()} function can be used to produce the same result as in
the previous example, with only one function call:

<<removeFile4,echo=F,results=hide>>=
if (file.exists("XLConnectExample4.xlsx"))
  file.remove("XLConnectExample4.xlsx")
@

<<simpleEx4,echo=T,results=hide>>=
require(XLConnect)
writeNamedRegionToFile("XLConnectExample4.xlsx", women, name = "womenName",
                       formula = "womenData!$C$5")
@

\subsection{Reading Excel files: basic functions}

\subsubsection{readWorksheet() - reading worksheets of an Excel workbook}
\code{readWorksheet()} allows for reading data from a workbook that has been previously loaded
and is passed as the \code{object} argument. The name or index of the worksheet to read from should
be passed as the \code{sheet} argument. The \code{startRow} and \code{startCol} arguments specify the
location of the top left corner of data to be read, while \code{endRow} and \code{endCol} specify
the bottom right corner. If \code{header = TRUE}, the first row is interpreted as column names of
the data.frame object read in. 

If the \code{startRow}, \code{startCol}, \code{endRow} and \code{endCol} arguments are not specified, 
or are passed as \code{<= 0}, the "bounding box" of the data is treated as the corresponding boundaries.

All arguments (except \code{object}) are vectorized, which allows for reading of multiple worksheets 
with one call.

\begin{tikzCodeBlock}[title={Read data from a worksheet of an Excel workbook},listing style=sweavechunk]
readWorksheet(object, sheet, startRow, startCol, endRow, endCol,
              header = TRUE)
\end{tikzCodeBlock}

\subsubsection{readWorksheetFromFile() - reading worksheets of an Excel file}
\code{readWorksheetFromFile()} allows for reading data from a workbook with one call, without 
loading the workbook first. The \code{file} argument is the path of the file to read from. All
arguments of the \code{readWorksheet()} function can be passed within the \code{'...'} argument.

\begin{tikzCodeBlock}[title={Read data from a worksheet of an Excel workbook with one call},listing style=sweavechunk]
readWorksheetFromFile(file, ...)
\end{tikzCodeBlock}

\subsubsection{readNamedRegion() - reading named regions of an Excel workbook}
\code{readNamedRegion()} can be used for reading data from named region in an Excel workbook.
The workbook must first be loaded and passed as the \code{object} argument. Remaining arguments are
the \code{name} of the named region and \code{header}, specifying whether the first row of data
should be interpreted as column names.

\begin{tikzCodeBlock}[title={Read data from a named region of an Excel workbook},listing style=sweavechunk]
readNamedRegion(object, name, header = TRUE)
\end{tikzCodeBlock}

\subsubsection{readNamedRegionFromFile() - reading named regions of an Excel file}
\code{readNamedRegionFromFile()} is a convenient wrapper function which allows for reading named regions
from an Excel file with one call. The function subsequently calls \code{loadWorkbook()} and 
\code{readNamedRegion()}.

The \code{file} argument specifies the path to the Excel file to be read and \code{name} - the named
region to be read. The \code{header} argument specifies whether the first row of data should be interpreted 
as column names.

\begin{tikzCodeBlock}[title={Read data from a named region of an Excel file},listing style=sweavechunk]
readNamedRegionFromFile(file, name, header = TRUE)
\end{tikzCodeBlock}

\subsection{Example: reading from an Excel sheet}
In this example, we will show how you can use \pkg{XLConnect} to read from an Excel sheet. For this purpose,
we will use the file created in the example in section \ref{Example1}. We set the \code{endRow} argument to
\code{10}, to limit the result. We set the rest of arguments specifying boundaries as \code{0}, so that they
are automatically determined.

Please note, that alternatively to setting the \code{sheet} argument to the name of the sheet we want to read from,
\code{"chickSheet"}, we could also specify it with the sheet index, as \code{sheet = 1}.

<<latexEx1,echo=T,keep.source=T>>=
require(XLConnect)
wb <- loadWorkbook("XLConnectExample1.xlsx", create = TRUE)
data <- readWorksheet(wb, sheet = "chickSheet", startRow = 0, endRow = 10,
                      startCol = 0, endCol = 0)
data
@

\subsection{Example: reading from an Excel sheet with one call}
The \code{readWorksheetFromFile()} function can be used to, with only one call, obtain the same result
as was shown in the previous example:

<<simpleEx5,echo=T,results=hide>>=
require(XLConnect)
data <- readWorksheetFromFile("XLConnectExample1.xlsx", sheet = "chickSheet",
                              startRow = 0, endRow = 10, startCol = 0, endCol = 0)
@

\subsection{Example: reading from an Excel named region}
In this example, we will show how to use \pkg{XLConnect}'s functions to read from a named region
in an Excel file. We will use the file created in the example in section \ref{Example3}.

<<latexEx2,echo=T,keep.source=T>>=
require(XLConnect)
wb <- loadWorkbook("XLConnectExample3.xlsx", create = TRUE)
data <- readNamedRegion(wb, name = "womenName")
data
@

\subsection{Example: reading from an Excel named region with one call}
The result obtained in the previous example can be reproduced by calling the wrapper function:
\\\code{readNamedRegionFromFile()}, witout the necessity of loading the workbook first:

<<simpleEx6,echo=T,results=hide>>=
require(XLConnect)
data <- readNamedRegionFromFile("XLConnectExample3.xlsx", "womenName")
@

\section{Advanced Usage and Examples}
\subsection{Generating an Excel Report of Swiss Franc (CHF) Exchange Rates}
In this example, we will show an example use case of \pkg{XLConnect}: generation of 
a simple Excel report, showing the development of exchange rates
(CHF vs EUR, USD and GBP).

For this example we will be using additional packages:
\begin{itemize}
	\item \pkg{zoo}: S3 Infrastructure for Regular and Irregular Time Series
	\item \pkg{ggplot2}: An implementation of the Grammar of Graphics
\end{itemize}

\begin{tikzCodeBlock}
	install.packages(c("XLConnect", "zoo", "ggplot2"))
\end{tikzCodeBlock}

Loading the required packages:
<<AdvancedExampleP1,echo=T,results=hide>>=
require(XLConnect)
require(zoo)
require(ggplot2) # >= 0.9.3
@ 

Preparing the data to be reported:

<<AdvancedExampleP2,echo=T,results=hide,keep.source=T>>=
# Read in (historical) currency data
# For this example take sample data set 'swissfranc' from XLConnect
curr <- XLConnect::swissfranc
curr <- curr[order(curr$Date),]
@ 

Now, let's write the data to Excel:

<<removeFile2,echo=F,results=hide>>=
if (file.exists("swiss_franc.xlsx"))
  file.remove("swiss_franc.xlsx")
@

<<AdvancedExampleP3,echo=T,results=hide,keep.source=T>>=

# Workbook filename
wbFilename <- "swiss_franc.xlsx"

# Create a new workbook
wb <- loadWorkbook(wbFilename, create = TRUE)

# Create a new sheet named 'Swiss_Franc'
sheet <- "Swiss_Franc"
createSheet(wb, name = sheet)

# Create a new Excel name referring to the top left corner
# of the sheet 'Swiss_Franc' - this name is going to hold
# our currency data
dataName <- "currency"
nameLocation <- paste(sheet, "$A$1", sep = "!")
createName(wb, name = dataName, formula = nameLocation)

# Instruct XLConnect to only apply a data format for a cell
# but not to apply any other cell styling
setStyleAction(wb, XLC$"STYLE_ACTION.DATA_FORMAT_ONLY")

# Set the default format for numeric data to display
# four digits after the decimal point
setDataFormatForType(wb, type = XLC$"DATA_TYPE.NUMERIC", format = "0.0000")

# Write the currency data to the named region created above
# Note: the named region will be automatically redefined to encompass all
# written data
writeNamedRegion(wb, data = curr, name = dataName, header = TRUE)

# Save the workbook (this actually writes the file to disk)
saveWorkbook(wb)
@

Now, we are going to enhance the report further. Let's say that for each currency 
we want to highlight the points in time when there was a change of more than 2\% 
compared to the previous day.

<<AdvancedExampleP4,echo=T,results=hide,keep.source=T>>=

# Load the workbook created above
wb <- loadWorkbook(wbFilename)

# Create a cell style for the header row
csHeader <- createCellStyle(wb, name = "header")
setFillPattern(csHeader, fill = XLC$FILL.SOLID_FOREGROUND)
setFillForegroundColor(csHeader, color = XLC$COLOR.GREY_25_PERCENT)

# Create a date cell style with a custom format for the Date column
csDate <- createCellStyle(wb, name = "date")
setDataFormat(csDate, format = "yyyy-mm-dd")

# Create a highlighting cell style
csHlight <- createCellStyle(wb, name = "highlight")
setFillPattern(csHlight, fill = XLC$FILL.SOLID_FOREGROUND)
setFillForegroundColor(csHlight, color = XLC$COLOR.CORNFLOWER_BLUE)

# Apply header cell style to the header row
setCellStyle(wb, sheet = sheet, row = 1,  
             col = seq(length.out = ncol(curr)),
             cellstyle = csHeader)

# Index for all rows except header row
allRows <- seq(length = nrow(curr)) + 1

# Apply date cell style to the Date column
setCellStyle(wb, sheet = sheet, row = allRows, col = 1, 
             cellstyle = csDate)

# Set column width such that the full date column is visible
setColumnWidth(wb, sheet = sheet, column = 1, width = 2800)

# Check if there was a change of more than 2% compared 
# to the previous day (per currency)
idx <- rollapply(curr[, -1], width = 2,
                 FUN = function(x) abs(x[2] / x[1] - 1),
                 by.column = TRUE) > 0.02

idx <- rbind(rep(FALSE, ncol(idx)), idx)
widx <- lapply(as.data.frame(idx), which)
# Apply highlighting cell style
for (i in seq(along = widx)) {
  if (length(widx[[i]]) > 0) {
    setCellStyle(wb, sheet = sheet, row = widx[[i]] + 1, col = i + 1,
                 cellstyle = csHlight)
  }

  # Note:
  # +1 for row since there is a header row
  # +1 for column since the first column is the time column
}

saveWorkbook(wb)
@

As a next step, let's add a graph to our report.

<<AdvancedExampleP5,echo=T,results=hide,keep.source=T>>=
wb <- loadWorkbook(wbFilename)

# Stack currencies into a currency variable (for use with ggplot2 below)
currencies <- names(curr)[-1]
gcurr <- reshape(curr, varying = currencies, direction = "long", v.names = "Value",
                 times = currencies, timevar = "Currency")

# Create a png graph showing the currencies in the context 
# of the Swiss Franc
png(filename = "swiss_franc.png", width = 800, height = 600)
p <- ggplot(gcurr, aes(Date, Value, colour = Currency)) +
  geom_line() + stat_smooth(method = "loess") +
  scale_y_continuous("Exchange Rate CHF/CUR") +
  labs(title = paste0("CHF vs ", paste(currencies, collapse = ", ")), 
       x = "") +
  theme(axis.title.y = element_text(size = 10, angle = 90, vjust = 0.3))
print(p)
dev.off()

# Define where the image should be placed via a named region;
# let's put the image two columns left to the data starting 
# in the 5th row
createName(wb, name = "graph",
formula <- paste(sheet, idx2cref(c(5, ncol(curr) + 2)), sep = "!"))
# Note: idx2cref converts indices (row, col) to Excel cell references

# Put the image created above at the corresponding location
addImage(wb, filename = "swiss_franc.png", name = "graph",
         originalSize = TRUE)

saveWorkbook(wb)
@

As a result, we obtain the following graph:
\begin{center}
<<fig=TRUE,figs.only=TRUE,height=4>>=
p
@
\end{center}

Check out the Excel file to see how the data and the graph have been exported!

\subsection{Scope of named regions}
In XLConnect functions that use named regions, it is possible to specify an argument \code{worksheetScope}.
This argument can be used to specify the worksheet in which the targeted named region is defined.
\begin{itemize}
	\item \code{worksheetScope = "Sheet1"} targets named regions scoped to the worksheet named \textit{Sheet1}.
	\item \code{worksheetScope = ""} targets the named region defined in the global (workbook-wide) scope.
	\item when omitting the parameter (leaving the default value \code{NA}), the first matching name will be targeted.
\end{itemize}

All methods that return a value based on a named region (e.g. \code{readNamedRegion}) 
can optionally set a \textit{worksheetScope} attribute on the returned value,
which will contain the name of the the worksheet scope in which the named region was found.
To enable this behavior, set the option \code{XLConnect.setCustomAttributes} to \code{TRUE}:
\\\code{options(XLConnect.setCustomAttributes = TRUE)}

You can then access the worksheet scope value using \textsf{R}'s \code{attr} function.

\pagebreak

\section{What's new in XLConnect version \Sexpr{getOption("XLConnect.Version")}}

A summary of news in \pkg{XLConnect} \Sexpr{getOption("XLConnect.Version")} can be found in the 
\href{https://cran.r-project.org/package=XLConnect/NEWS}{NEWS} file on CRAN.

\section{Frequently Asked Questions}

\subsection{Running out of memory when processing large data sets}
{\bf \large Question}: \\\\ 
I'm running out of memory when processing large data sets:
\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
	writeNamedRegionToFile(file = "huge.xls", data = giant.data.frame, 
	namedRegion = "LargeRegion", formula = "LotsOfData!A1")

	Error: OutOfMemoryError (Java): Java heap space
\end{tikzCodeBlock}

\vspace{3 mm}
{\bf \large Answer}: \\
 
This is caused by the fact that \pkg{XLConnect} needs to copy your entire data object over 
to the JVM in order to write it to a file and the JVM has to be initialized with a fixed 
upper limit on its memory size. To change this amount, you can pass parameters to R's 
JVM just like you can to a command line Java process via rJava's options support:
 
\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
	options(java.parameters = "-Xmx1024m")
	library(XLConnect)
\end{tikzCodeBlock}
 
Note, however, that these parameters are evaluated exactly once per R session when the JVM is 
initialized - this is usually once you load the first package that uses Java support, so you 
should do this as early as possible.

\newpage Some general Java advice: The upper limit of the Xmx parameter is system dependent - most prominently, 
32bit Windows will fail to work with anything much larger than 1500m, and it is usually a bad idea to 
set Xmx larger than your physical memory size because garbage collection and virtual memory do not play 
well together.

% Another Question for FAQ
\subsection{How can I style my output - set fonts, colors etc?}

{\bf \large Question}: \\\\ 
How can I style my output - set fonts, colors etc? \\

{\bf \large Answer}: \\\\
\pkg{XLConnect} does not currently allow direct access to low-level formatting options. However, it is possible to assign 
named cell styles to cells, so the preferred workflow would be to:

\begin{enumerate}
	\item define some named cell styles in an Excel document ("Format" -> "Styles" in pre-2007 Excel, "Cell styles" on the default pane of Excel 2007 and on)
	\item save the document to a file
	\item then load this template in \pkg{XLConnect} and use the setCellStyle method to assign the predefined styles:
\end{enumerate}
	
\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
	library(XLConnect)
	w <- loadWorkbook("template.xls")
	df <- data.frame("foo")
	c <- getCellStyle(w, "FatFont")

	writeWorksheet(w, data = df, sheet = 1, startRow = 1, startCol = 1,
	               header = TRUE)
	setCellStyle(w, sheet = 1, row = 1, col = 1, cellstyle = c)
	saveWorkbook(w)
\end{tikzCodeBlock}

% Yet another Question for FAQ
\subsection{I'm getting errors trying to import Excel data}

{\bf \large Question}: \\\\ 
I'm getting errors trying to import Excel data like:
\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
Unexpected eval type 
(org.apache.poi.hssf.record.formula.eval.MissingArgEval)
\end{tikzCodeBlock}	
or
\begin{tikzCodeBlock}[listing style=bashsource, code body/.append style={codebody color=white}]
Error: NotImplementedException (Java): Error evaluating cell Sheet1!A1
\end{tikzCodeBlock}	

\vspace{3mm}
{\bf \large Answer}: \\\\
This type of error is triggered when \pkg{XLConnect} can not determine the value of a cell in the 
region you're trying to import. Usually, this happens because Apache POI does not support all possible 
Excel formulae.

There is no direct solution for accessing the values of fields that Apache POI doesn't know how to compute. 
However, if you can live without the cell values and just want to ignore uncomputable cells, have a look 
at the \code{onErrorCell} function to tell \pkg{XLConnect} that you want to 
ignore errors.

If you have Excel and are willing to invest some manual effort, you can manually create a static copy, 
that can be imported as follows:

\begin{itemize}
	\item Select the region containing your data
	\item {\bf Edit} -> {\bf Copy}
	\item Select an empty cell and {\bf Edit} -> {\bf Paste Special}
	\item In the Paste radio group, select {\bf Values}
\end{itemize}

You should then be able to import the pasted region without problems.

\section{Contact and Support}

We are very glad that you are using \pkg{XLConnect} and we would be happy to hear from you!\\
 
\begin{minipage}[c]{0.08\textwidth}
\includegraphics[width=10mm, height=10mm]{img/BrownBug.png}
\end{minipage}
\begin{minipage}[c]{0.9\textwidth}
Please log any bug reports, feature requests or technical inquiries at: \\
\urlwofont{https://github.com/miraisolutions/xlconnect/issues}
\end{minipage}

\begin{minipage}[c]{0.08\textwidth}
\includegraphics[width=10mm, height=10mm]{img/Feedback.png}
\end{minipage}
\begin{minipage}[c]{0.9\textwidth}
For any other feedback you may have, please contact us at: \\
\href{mailto:xlconnect@mirai-solutions.com}{xlconnect@mirai-solutions.com}
\end{minipage}

\begin{minipage}[c]{0.08\textwidth}
\includegraphics[width=10mm, height=10mm]{img/LinkedIn-Icon.png}
\end{minipage}
\begin{minipage}[c]{0.9\textwidth}
You can also follow and contact us on {\bf LinkedIn}: \\
\urlwofont{https://www.linkedin.com/company/mirai-solutions-gmbh/}
\end{minipage}

\vspace{3mm}
We will do our best to reply to your enquires as quickly as possible!

\section{Further Resources and References}

\begin{enumerate}
	\item	Mirai Solutions website: 
			\\\urlwofont{https://mirai-solutions.ch}
	\item Mirai Solutions on LinkedIn: 
			\\\urlwofont{https://www.linkedin.com/company/mirai-solutions-gmbh/}
	\item \pkg{XLConnect} on CRAN: 
			\\\urlwofont{https://CRAN.R-project.org/package=XLConnect}
	\item \pkg{XLConnect} on RDocumentation:
	    \\\urlwofont{https://www.rdocumentation.org/packages/XLConnect}
	\item \pkg{XLConnect} on METACRAN:
	    \\\urlwofont{https://www.r-pkg.org/pkg/XLConnect}
	\item \pkg{XLConnect} on r-universe: 
			\\\urlwofont{https://miraisolutions.r-universe.dev/XLConnect}
	\item \pkg{XLConnect} in the R Programming section on Wikibooks:
			\\\urlwofont{https://web.archive.org/web/20240821111632/https://en.wikibooks.org/wiki/R_Programming/Importing_and_exporting_data}
  \item R and Excel: Making Your Data Dumps Pretty with XLConnect:
      \\\urlwofont{https://www.youtube.com/watch?v=G3mOUbZr4HI}
      \\\urlwofont{https://web.archive.org/web/20240821105927/https://amunategui.github.io/excel-data-dumps/}
\end{enumerate}

\end{document}