search

marcschwartz / WriteXLS

CRAN Package WriteXLS: Cross-platform Perl based R function to create Excel 2003 (XLS) and Excel 2007 (XLSX) files from one or more data frames. Each data frame will be written to a separate named worksheet in the Excel spreadsheet. The worksheet name will be the name of the data frame it contains or can be specified by the user.
GNU General Public License v2.0
19 stars 9 forks source link

Issue reading xls written with WriteXLS #9

Closed carlganz closed 8 years ago

carlganz commented 8 years ago

Hello,

I have found that if I write an XLS with WriteXLS and then read it with readxl::read_excel I lose a column even though the actual XLS document has all the columns. Here is a reproducible example: 

z <- tempdir()
ncol(mtcars) # should be 11
WriteXLS::WriteXLS(mtcars,paste0(z,"/mtcars.xls)
mtcars.xls <- readxl::read_excel(paste0(z,"/mtcars.xls"))
ncol(mtcars.xls) # is 10 for some reason

The rightmost column is not read in.

I have no idea whether this is an issue with WriteXLS or readxl but I will file an issue with readxl as well to be safe.

Thanks for the wonderful package.

Regards

marcschwartz commented 8 years ago

Hi,

Thanks for the report.

I can confirm your finding on my Mac, running R 3.3.0.

However, if I write 'mtcars' to an XLSX file:

WriteXLS(mtcars, "mtcars.xlsx")

and then read that file back in with read_excel():

read_excel("mtcars.xlsx")

I get a data frame with 11 columns:

str(read_excel("mtcars.xlsx")) Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 32 obs. of 11 variables: $ mpg : num 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ... $ cyl : num 6 6 4 6 8 6 8 4 4 6 ... $ disp: num 160 160 108 258 360 ... $ hp : num 110 110 93 110 175 105 245 62 95 123 ... $ drat: num 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ... $ wt : num 2.62 2.88 2.32 3.21 3.44 ... $ qsec: num 16.5 17 18.6 19.4 17 ... $ vs : num 0 0 1 1 0 1 0 1 1 1 ... $ am : num 1 1 1 0 0 0 0 0 0 0 ... $ gear: num 4 4 4 3 3 3 3 4 4 4 ... $ carb: num 4 4 1 1 2 1 4 2 2 4 ...

Trying to use the XLConnect package to read the XLS file:

str(readWorksheetFromFile("mtcars.xls", sheet = 1)) 'data.frame': 32 obs. of 11 variables: $ mpg : num 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ... $ cyl : num 6 6 4 6 8 6 8 4 4 6 ... $ disp: num 160 160 108 258 360 ... $ hp : num 110 110 93 110 175 105 245 62 95 123 ... $ drat: num 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ... $ wt : num 2.62 2.88 2.32 3.21 3.44 ... $ qsec: num 16.5 17 18.6 19.4 17 ... $ vs : num 0 0 1 1 0 1 0 1 1 1 ... $ am : num 1 1 1 0 0 0 0 0 0 0 ... $ gear: num 4 4 4 3 3 3 3 4 4 4 ... $ carb: num 4 4 1 1 2 1 4 2 2 4 ...

So that package appears to get all 11 columns.

Thus, at least based upon the above, it suggests that readxl may have some issues in reading an XLS file for some reason.

Hope this helps in moving forward.

Marc