Import * cell formatting * information from excel file to R

Question

I have been provided with excel files ( .xlsx ) where the cell format is the relevant information that I need to capture. Key formatting is (1) the color of the cell and (2) the border (left + right, full box or missing).

Is it possible to read this in R?

Answer 1

I was really pleased with the openpyxl Python project. Perhaps you can manipulate (preprocess) xlsx in python to be digestible R (possibly CSV or XLS).

http://openpyxl.readthedocs.org/en/latest/index.html#tutorial

http://openpyxl.readthedocs.org/en/latest/usage.html#read-an-existing-workbook

There XLS is imported into Quick R:

http://www.statmethods.net/input/importingdata.html

Answer 2

This is a very old question, but it still comes across in search, so I think it is useful to point people to the tidyxl package.

tidyxl::xlsx_cells() reads an Excel spreadsheet in the form of a data frame, where each row represents a separate cell in the spreadsheet with its address (for example, A1 ), contents and properties.

tidyxl::xlsx_formats() returns a nested list of all the various cell formats in an Excel spreadsheet.

The local_format_id column in the data frame returned by xlsx_cells() allows you to search for formatting information for each cell in the list returned by xlsx_formats() .

Additional information is included in the tidyxl vignette .