Abstract
In this chapter, we explore the possibilities for data exchange offered by the Office Open XML (OOXML) standard. Many of the office suites have adopted OOXML for their spreadsheets, word processing, and presentation tools. We demonstrate the kinds of functionality that can be built using the tools in the XML package to interface with XML-based spreadsheets from within R. Examples include: reading an entire xlsx file into an R data frame (or list of data frames, one per sheet); extracting and setting cell values in a worksheet; and adding style information on cells, R plots to sheets, and rda files to the xlsx archive. While the focus is on Excel and xlsx files, the ideas presented in this chapter can be extended to other spreadsheet applications, e.g., Google Docs and Open Office, and to other office tools, e.g., Word and PowerPoint. The ROOXML [19] package provides the basic infrastructure for Microsoft Office, and, for example, RWordXML provides facilities for working with word processing files.
Access provided by Autonomous University of Puebla. Download to read the full chapter text
Chapter PDF
Similar content being viewed by others
Keywords
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.
References
Apache Software Foundation. OpenOffice: The free and open productivity suite; 3.0 New Features. http://www.openoffice.org/dev_docs/features/3.0/ , 2011.
Apple, Inc. Numbers for iOS: Supported file formats. http://support.apple.com/kb/HT4642 , 2011.
Adrian Dragulescu. xlsx: Read, write, format Excel 2007 and Excel 97/2000/XP/2003 files. http://cran.r-project.org/package=xlsx , 2011. R package version 0.5.0.
ECMA International. Ecma Office Open XML file formats standard, Part 3: Primer. http://www.ecma-international.org/news/TC45_current_work/TC45_available_docs.htm , 2011.
Federal Election Commission. Top 50 house incumbents by contributions from PACs and other committees, January 1, 2011 – June 30, 2011. http://www.fec.gov/press/summaries/2012/PAC/6mnth/1pac6mosummary11.xlsx , 2011.
KDE e.V. KOffice: Standards-compliant office and productivity applications. http://userbase.kde.org/KOffice , 2011.
LibreOffice; The Document Foundation. Calc: The LibreOffice spreadsheet program. http://www.libreoffice.org/features/calc/ , 2011.
B.D. McCullough and B. Wilson. On the accuracy of statistical procedures in Microsoft Excel 2000 and Excel XP. Computational Statistics & Data Analysis, 40:713–721, 2002.
B.D. McCullough and B. Wilson. On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis, 52:4570–4578, 2008.
Eric Neuwirth. RExcel: Interface between R and Excel. http://cran.r-project.org/package=RExcel , 2011. R package version 3.2.6.
Tom Ngo. Office Open XML overview. http://www.ecma-international.org/news/TC45_current_work/OpenXMLWhitePaper.pdf , 2005.
R Core Team. R Data Import/Export, 2012. http://cran.r-project.org/doc/manuals/R-data.html .
Frank Rice. Introducing the Office (2007) Open XML file formats. http://msdn.microsoft.com/en-us/library/aa338205(v=office.12).aspx , 2006.
Brian Ripley. RODBC: ODBC database access. http://cran.r-project.org/package=RODBC , 2011. R package version 1.3-3.
Marc Schwartz. WriteXLS: Cross-platform PERL-based R function to create Excel 2003 (XLS) files. http://cran.r-project.org/package=WriteXLS , 2011. R package version 2.3.0.
Miria Solutions. XLConnect: Manipulate Excel files from R. http://cran.rproject.org/package=XLConnect , 2011. R package version 0.2-3.
Hans-Peter Suter. xlsReadWrite: Natively read and write Excel files. http://cran.rproject.org/package=xlsReadWrite , 2011. R package version 1.5-4.
Duncan Temple Lang. RExcelXML: Tools for working with Excel XML documents. http://www.omegahat.org/RExcelXML , 2011. R package version 0.5-0.
Duncan Temple Lang. ROOXML: Simple tools for Open Office XML documents. http://www.omegahat.org/ROOXML , 2011.
Duncan Temple Lang. ROpenOffice: Basic reading of Open Office spreadsheets and workbooks. http://www.omegahat.org/ROpenOffice , 2011. R package version 0.4-1.
Duncan Temple Lang. XML: Tools for parsing and generating XML within R and S-PLUS. http://www.omegahat.org/RSXML , 2011. R package version 3.4.
Duncan Temple Lang. Rcompression: In-memory decompression for GNU zip and bzip2 formats. http://www.omegahat.org/Rcompression , 2012. R package version 0.94-0.
Duncan Temple Lang. RGoogleDocs: Primitive interface to Google Documents from R. http://www.omegahat.org/RGoogleDocs , 2012. R package version 0.7-0.
Duncan Temple Lang and Gabriel Becker. RWordXML: Tools for Open Office word processing XML documents. http://www.omegahat.org/RWordXML , 2010. R package version 0.1-0.
Guido van Steen. dataframes2xls: Write data frames to xls files. http://cran.rproject.org/package=dataframes2xls , 2011. R package version 0.4.5.
Wouter van Vugt. Open XML: The markup explained. http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2007/08/13/1970.aspx , 2007.
Gregory Warnes. gdata: Various R programming tools for data manipulation. http://cran.r-project.org/package=gdata , 2011. R package version 2.12.0.
World Bank Group. WDR2011 dataset. http://databank.worldbank.org/databank/download/WDR2011Dataset.xlsx , 2011.
World Bank Group. World development report 2011 on conflict, security and development. http://data.worldbank.org/data-catalog/wdr2011 , 2011.
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2014 Springer Science+Business Media New York
About this chapter
Cite this chapter
Nolan, D., Lang, D.T. (2014). Spreadsheets. In: XML and Web Technologies for Data Sciences with R. Use R!. Springer, New York, NY. https://doi.org/10.1007/978-1-4614-7900-0_15
Download citation
DOI: https://doi.org/10.1007/978-1-4614-7900-0_15
Published:
Publisher Name: Springer, New York, NY
Print ISBN: 978-1-4614-7899-7
Online ISBN: 978-1-4614-7900-0
eBook Packages: Mathematics and StatisticsMathematics and Statistics (R0)