Advertisement

Spreadsheets

  • Deborah Nolan
  • Duncan Temple Lang
Chapter
Part of the Use R! book series (USE R)

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.

Keywords

Excel Spreadsheet Data Frame Excel Document Political Action Committee Format Spreadsheet 
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.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Apache Software Foundation. OpenOffice: The free and open productivity suite; 3.0 New Features. http://www.openoffice.org/dev_docs/features/3.0/, 2011.
  2. 2.
    Apple, Inc. Numbers for iOS: Supported file formats. http://support.apple.com/kb/HT4642, 2011.
  3. 3.
    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.
  4. 4.
    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.
  5. 5.
    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.
  6. 6.
    KDE e.V. KOffice: Standards-compliant office and productivity applications. http://userbase.kde.org/KOffice, 2011.
  7. 7.
    LibreOffice; The Document Foundation. Calc: The LibreOffice spreadsheet program. http://www.libreoffice.org/features/calc/, 2011.
  8. 8.
    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.MathSciNetCrossRefzbMATHGoogle Scholar
  9. 9.
    B.D. McCullough and B. Wilson. On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis, 52:4570–4578, 2008.MathSciNetCrossRefzbMATHGoogle Scholar
  10. 10.
    Eric Neuwirth. RExcel: Interface between R and Excel. http://cran.r-project.org/package=RExcel, 2011. R package version 3.2.6.
  11. 11.
  12. 12.
    R Core Team. R Data Import/Export, 2012. http://cran.r-project.org/doc/manuals/R-data.html.
  13. 13.
    Frank Rice. Introducing the Office (2007) Open XML file formats. http://msdn.microsoft.com/en-us/library/aa338205(v=office.12).aspx, 2006.
  14. 14.
    Brian Ripley. RODBC: ODBC database access. http://cran.r-project.org/package=RODBC, 2011. R package version 1.3-3.
  15. 15.
    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.
  16. 16.
    Miria Solutions. XLConnect: Manipulate Excel files from R. http://cran.rproject.org/package=XLConnect, 2011. R package version 0.2-3.
  17. 17.
    Hans-Peter Suter. xlsReadWrite: Natively read and write Excel files. http://cran.rproject.org/package=xlsReadWrite, 2011. R package version 1.5-4.
  18. 18.
    Duncan Temple Lang. RExcelXML: Tools for working with Excel XML documents. http://www.omegahat.org/RExcelXML, 2011. R package version 0.5-0.
  19. 19.
    Duncan Temple Lang. ROOXML: Simple tools for Open Office XML documents. http://www.omegahat.org/ROOXML, 2011.
  20. 20.
    Duncan Temple Lang. ROpenOffice: Basic reading of Open Office spreadsheets and workbooks. http://www.omegahat.org/ROpenOffice, 2011. R package version 0.4-1.
  21. 21.
    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.
  22. 22.
    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.
  23. 23.
    Duncan Temple Lang. RGoogleDocs: Primitive interface to Google Documents from R. http://www.omegahat.org/RGoogleDocs, 2012. R package version 0.7-0.
  24. 24.
    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.
  25. 25.
    Guido van Steen. dataframes2xls: Write data frames to xls files. http://cran.rproject.org/package=dataframes2xls, 2011. R package version 0.4.5.
  26. 26.
    Wouter van Vugt. Open XML: The markup explained. http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2007/08/13/1970.aspx, 2007.
  27. 27.
    Gregory Warnes. gdata: Various R programming tools for data manipulation. http://cran.r-project.org/package=gdata, 2011. R package version 2.12.0.
  28. 28.
  29. 29.
    World Bank Group. World development report 2011 on conflict, security and development. http://data.worldbank.org/data-catalog/wdr2011, 2011.

Copyright information

© Springer Science+Business Media New York 2014

Authors and Affiliations

  • Deborah Nolan
    • 1
  • Duncan Temple Lang
    • 2
  1. 1.Department of StatisticsUniversity of CaliforniaBerkeleyUSA
  2. 2.Department of StatisticsUniversity of CaliforniaDavisUSA

Personalised recommendations