colearendt/xlsx

An R package to interact with Excel files using the Apache POI java library

xlsx

An R package to read, write, format Excel 2007 and Excel 97/2000/XP/2003 files

The package provides R functions to read, write, and format Excel files. It depends on Java, but this makes it available on most operating systems.

Install

Stable version from CRAN

install.packages('xlsx')

Or development version from GitHub

devtools::install_github('colearendt/xlsx')

Common Problems

This package depends on Java and the rJava package to make the connection between R and Java seamless. In order to use the xlsx package, you will need to:

• Ensure you have a jdk (Java Development Kit, version >= 1.5) installed for your Operating System. More information can be found on Oracle's website

• Ensure that the system environment variable JAVA_HOME is configured appropriately and points to your jdk of choice. Typically, this will be included in your PATH environment variable as well. Options and system environmental variables that are available from R can be seen with Sys.getenv()

• Particularly on UNIX systems, if you continue experiencing issues, you may need to reconfigure R's support for Java on your system. From a terminal, use the command R CMD javareconf. You may need to run this as root or prepended with sudo to ensure it has appropriate permission.

More detail can be found in the rJava docs.

Quick start

To read the first sheet from spreadsheet into a data.frame

read.xlsx2('file.xlsx', 1)

To write a data.frame to a spreadsheet

write.xlsx2(iris, file='iris.xlsx')

The package has many functions that make it easy to style and formalize output into Excel, as well.

wb <- createWorkbook()
s <- createSheet(wb,'test')

cs <- CellStyle(wb) + 
  Font(wb,heightInPoints = 16, isBold = TRUE) +
  Alignment(horizontal='ALIGN_CENTER')
  

r <- createRow(s,1)
cell <- createCell(r,1:ncol(iris))

setCellValue(cell[[1]],'Title for Iris')
for (i in cell) {
  setCellStyle(i,cs)
}

addMergedRegion(s, 1,1, 1,ncol(iris))

addDataFrame(iris, s, row.names=FALSE, startRow=3)

saveWorkbook(wb,'iris_pretty.xlsx')

Issues/Mailing list

To report a bug, use the Issues page at: https://github.com/colearendt/xlsx/issues

If you are wrestling with the Java dependency, there are some very good alternatives that do not require Java. Your choice will vary depending on what you are trying to accomplish.

• openxlsx
• readxl
• writexl

Acknowledgements

The package is made possible thanks to the excellent work on Apache POI.

Code of Conduct

Please note that the xlsx project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

xlsx's Issues

adding emf files to a workbook does not work

What steps will reproduce the problem?
library(devEMF)
emf(file="example.emf", bg="white")
boxplot(rnorm(100))
dev.off()

# inserting example.emf into an Excel-sheet by hand works

# not trying to do it with add picture

wb <- createWorkbook()
sheet <- createSheet(wb, "EMF_Sheet")
addPicture(file="example.emf")
saveWorkbook(wb, file="WB_with_EMF.xlsx")


What is the expected output? What do you see instead?


Please use labels and text to provide additional information.

Original issue reported on code.google.com by [email protected] on 2 Sep 2013 at 12:20

xlsxjars infinite loop

What steps will reproduce the problem?
1. load rJava
2. load xlsxjars

What is the expected output? What do you see instead?
Expect xlsxjars to load, but all that happens is an infinite loading loop.

What version of the product are you using? On what operating system?
R-3.0.1, rJava0.9.5, xlsxjars0.5.0.  Windows 7

Original issue reported on code.google.com by [email protected] on 17 Jul 2013 at 7:39

Add function to set row height

Currently the column width can be set. It would be useful to also be able to 
set the row height. 

Original issue reported on code.google.com by [email protected] on 24 Apr 2014 at 3:46

customize the format of Datetime when you output to a file

What steps will reproduce the problem?
1. Currently the Excel US locale is hardcoded mm/dd/yyyy HH:MM:SS

What is the expected output? What do you see instead?
Would be best to use the user locale format as default and provide a way to 
customize it. 

Original issue reported on code.google.com by [email protected] on 30 Nov 2013 at 1:20

jcall error in xlsx package.

What steps will reproduce the problem?
1.The last step to write an xlsx to produce an output from a statistical script
2.
3.

What is the expected output? What do you see instead?

I saw an error "Error in .jcall(wb, "Lorg/apache/poi/ss/usermodel/Sheet;", 
"createSheet",  : 
  java.lang.IllegalArgumentException: The workbook already contains a sheet of this name"


What version of the product are you using? On what operating system?
R 3.0.1, 32-bit in Window XP.

Please provide any additional information below.
I ran a script written for SPRT calculation by an programming expert. I was 
able to use this script a month ago. Today, when I repeated the same script, I 
was not able to produce a proper excel output. Basically, I would expect to see 
a number of trials to succeed a set upper or lower bound values. However, I saw 
only with trial 1 or 2, all the subjects have had succeed rate over the set 
criterion.

The programmer had run the same script with his mac and it was fine. I tried 
using this script for different data set and the problem still exists. The 
major problem unresolved might be the java update. I realised this update only 
today and I thought the problem would disappear after I updated the java. I 
tried uninstalled and reinstalled the java, but the outcome is still 
unsuccessul. I am out of my mind. I would be really appreciate if you could 
provide me some advice on solving this issue.

Many thanks,
Lay

Original issue reported on code.google.com by [email protected] on 22 Jun 2013 at 1:57

Error When Importing xlsx File from BLS Website

I get an error when I attempt to import an xlsx file downloaded from the BLS 
website:

a<-read.xlsx("SeriesReport-20140821112715.xlsx", 1, startRow=11)

Error in .jcall(row[[ir]], "Lorg/apache/poi/ss/usermodel/Cell;", "getCell",  : 
  java.lang.IllegalArgumentException: Cell index must be >= 0

However, when I open the Excel file and save a new copy, the import works.  Do 
you have any idea why the import fails for the SeriesReport-20140821112715.xlsx 
file?

Original issue reported on code.google.com by [email protected] on 21 Aug 2014 at 3:35

Attachments:

• SeriesReport-20140821112715.xlsx

Request for a more general CellBlock constructor

What steps will reproduce the problem?
1. run the attached test.R

What is the expected output? What do you see instead?

My ultimate goal is to write a general tool for creating reports by pasting 
data into templates. In the template, the cells where the data will be pasted 
may have formats or not. I would like the final report to preserve any existing 
formats.

To illustrate this, I am going to try and paste a 3-by-3 matrix into the 
Sheet1!A1:C3, Sheet2!A1:C3, and Sheet3!A1:C3 ranges of the attached 
template.xlsx:
  * all the cells in Sheet1!A1:C3 have no formats
  * all the cells in Sheet2!A1:C3 have formats
  * some cells in Sheet3!A1:C3 have formats, some do not

Now compare the attached template.xlsx and the report.xlsx created by the 
attached test.R script. You will notice that the formats (yellow cells) in the 
template are not present in the final report.

See that my script test.R uses create = TRUE. If I used create = FALSE, it will:
   * fail when trying to paste into Sheet1!A1:C3
   * succeed when trying to paste into Sheet2!A1:C3
   * partially (not all data pasted) succeed when trying to paste into Sheet3!A1:C3.

I believe the problem comes from the code located at 
http://code.google.com/p/rexcel/source/browse/tags/0.5.0/other/src/main/java/org
/cran/rexcel/RCellBlock.java

On rows 39 and 40, the doc says: "param create if true, rows and cells are 
created as necessary [...]" but on line 56~58, you see that when create=TRUE, 
existing cells are replaced with blank new cells via a createCell call. I 
believe that when create=TRUE, getCell should be called first. If the output is 
non-null, use it, otherwise call createCell.


What version of the product are you using? On what operating system?

R.2.15.3
xlsx 0.5.0
xlsxjars 0.4.0
rJava 0.9-3


Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 27 Aug 2013 at 8:00

Attachments:

• test.R
• template.xlsx

Generated xlsx files can't be imported into SPSS, but re-saving in excel solves the problem.

What steps will reproduce the problem?
1. Export data frame (in my case more than 256 columns, if that may matter) to 
xlsx
2. Try to import using SPSS's Open->Data->xlsx dialog.
GET DATA /TYPE=XLSX 
  /FILE='/Users/ruben/Desktop/merged_selfinsight.xlsx' 
  /SHEET=name 'Sheet1' 
  /CELLRANGE=full 
  /READNAMES=on 
  /ASSUMEDSTRWIDTH=32767. 
EXECUTE. 

>Error # 105.  Command name: EXECUTE 
>This command is not valid before a working file has been defined. 
>Execution of this command stops.
3. Open file in Excel, data is fine, save to a new file as xlsx.
4. Repeat import, now it works.

What is the expected output? What do you see instead?
It would be great if you could track down what is putting SPSS off. Apparently 
the file is a valid xlsx file and excel doesn't complain, but it would be great 
if that extra step weren't required. Probably the bug sits with SPSS but I have 
bigger hopes that you could fix it than they. 

What version of the product are you using? On what operating system?
R version 2.15.2 (2012-10-26)
Platform: x86_64-apple-darwin9.8.0/x86_64 (64-bit)

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] xlsx_0.5.0     xlsxjars_0.4.0 rJava_0.9-3   

loaded via a namespace (and not attached):
[1] tools_2.15.2

IBM SPSS Statistics 20.0.0

Excel 14.1.3 (2011)

Original issue reported on code.google.com by [email protected] on 7 Mar 2013 at 1:23

color="black" not set properly in Font

What steps will reproduce the problem?
wb <- createWorkbook()
tmp <- Font(wb, color="black")
> tmp
$ref
[1] "Java-Object{<xml-fragment 
xmlns:main=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">\r\n  
<main:name val=\"Calibri\"/>\r\n  <main:sz val=\"11.0\"/>\r\n  <main:color 
rgb=\"FFFFFF\"/>\r\n</xml-fragment>}"

attr(,"class")
[1] "Font"

Other colors work.  If this is a POI issue, report to the developers.


What is the expected output? What do you see instead?


Please use labels and text to provide additional information.

Original issue reported on code.google.com by [email protected] on 27 Aug 2013 at 3:33

Apple AWT Java VM was loaded on first thread -- can't start AWT

I am trying to write a workbook with hyperlinked cell values.

However, when I invoke addHyperlink, I get this error:

2013-04-06 20:40:27.951 rsession[5063:903] Apple AWT Java VM was loaded on 
first thread -- can't start AWT.
Error in .jnew("java.awt.Color", rgb[1], rgb[2], rgb[3]) : 
  java.lang.InternalError: Can't start the AWT because Java was started on the first thread.  Make sure StartOnFirstThread is not specified in your application's Info.plist or on the command line

I'm running R in RStudio within an R markdown document.

The code I'm trying to run (in an R markdown document in RStudio 0.97.312 on 
Mac OS 10.6.8) is:

wb=createWorkbook(type="xlsx")
sheet=createSheet(wb, sheetName="Drought Counts")
colnamesStyle=CellStyle(wb,alignment=Alignment(h="ALIGN_CENTER"),
                        font=Font(wb,isBold=T))
addDataFrame(drought.dat,sheet,row.names=F,col.names=T,
             colnamesStyle=colnamesStyle,
             showNA=T,characterNA='NA')
for (i in seq(2,nrow(drought.dat))) {
  row=getRows(sheet,i)
  cell=getCells(row,1)[[1]]
  address=addresses2[i]
  addHyperlink(cell,address)
}

The error is printed to the console when the "for" loop completes.

When I save the workbook and open it in Excel, everything looks fine except the 
hyperlinks were not added.

Here is my R session info:

R version 2.15.0 (2012-03-30)
Platform: x86_64-apple-darwin9.8.0/x86_64 (64-bit)

locale:
[1] en_US/en_US/en_US/C/en_US/en_US

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] xlsx_0.5.0     xlsxjars_0.4.0 rJava_0.9-3    reshape_0.8.4  plyr_1.8      

loaded via a namespace (and not attached):
[1] tools_2.15.0

How do I tell rJava not to start java on the first thread? 

Original issue reported on code.google.com by [email protected] on 7 Apr 2013 at 12:57

Suggestion: Allow for colors to be specified as RGB in hex format

Currently, only R internal colors can be specified and are then internally 
converted to RGB. My suggestion would be to allow to specifiying of RGB colors 
as a hex string, i.e. #FF00FF.

Original issue reported on code.google.com by [email protected] on 23 Feb 2014 at 6:58

irreversible change of dec option in write.table

# A) Create data.frame
dfr<-data.frame(a=.5)
# B) Writes with dec="." which is the default
write.table(dfr)
# C) Lodaing changes the behaviour to dec=","
library(xlsx)
write.table(dfr)
# C) Which can't be reversed even not by detaching:
write.table(dfr,dec=".")
detach("package:xlsx", unload=TRUE)
write.table(dfr,dec=".")

Original issue reported on code.google.com by [email protected] on 31 Jul 2013 at 6:21

~ in filenames causes error

What steps will reproduce the problem?
1. setting filename in write.xlsx2 to ~/file.xls 
2. setting filename in write.xlx2 to /home/myusername/file.xls works fine.
3.

What is the expected output? What do you see instead?
Expect file to be written.

What version of the product are you using? On what operating system?
see below

Please provide any additional information below.

9: stop(list(message = "java.io.FileNotFoundException: ~/QuSAGE_Analysis.xlsx 
(No such file or directory)", 
       call = .jnew("java/io/FileOutputStream", jFile), jobj = <S4 object of class "jobjRef">))
8: .External(RcreateObject, class, ..., silent = silent)
7: .jnew("java/io/FileOutputStream", jFile)
6: saveWorkbook(wb, file)
5: write.xlsx2(eset.QuSAGEresults.varUNEQUAL.n15.c1.all.v4.0.symbols.gmt.table, 
       xlsx.filename, sheetName = "c1.all.v4", append = FALSE, row.names = FALSE) at qusage_createXLSXfile.R#5

> sessionInfo()
R version 3.0.2 (2013-09-25)
Platform: x86_64-pc-linux-gnu (64-bit)

locale:
 [1] LC_CTYPE=en_AU.UTF-8          LC_NUMERIC=C                 
 [3] LC_TIME=en_AU.UTF-8           LC_COLLATE=en_AU.UTF-8       
 [5] LC_MONETARY=en_AU.UTF-8       LC_MESSAGES=en_AU.UTF-8      
 [7] LC_PAPER=en_AU.UTF-8          LC_NAME=en_AU.UTF-8          
 [9] LC_ADDRESS=en_AU.UTF-8        LC_TELEPHONE=en_AU.UTF-8     
[11] LC_MEASUREMENT=en_AU.UTF-8    LC_IDENTIFICATION=en_AU.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods  
[7] base     

other attached packages:
[1] xlsx_0.5.5           xlsxjars_0.5.0       rJava_0.9-5         
[4] qusage_1.2.0         limma_3.18.4         BiocInstaller_1.12.1

loaded via a namespace (and not attached):
[1] Biobase_2.22.0     BiocGenerics_0.8.0 parallel_3.0.2    
[4] tools_3.0.2   

Original issue reported on code.google.com by [email protected] on 26 May 2014 at 12:46

Columns of Type "Time" Read as Dates

I imported a column of Time values such as 4:30 PM, 22:45, etc from Excel, and 
the package reads them as 1899-12-30 22:45:00, etc. 

This seems like a very easy fix.  It may be safe to assume that if you bring in 
a value with 1899-12-30 to assume that the user has typed in a time value.  I 
personally have no need for time formats but someone else may.

Original issue reported on code.google.com by [email protected] on 17 Jan 2014 at 5:54

Error message in trying to read a few rows from a large file

I have an xlsx file with 6 sheets, 3 of which are about 9000x11 and another 3 
of which are 9000x7. Half of the sheets are data, and the other half are 
formulas

Using this type of call (to read in one of the formula sheets)

read.xlsx2(file="xxx.xlsx",sheetName="yyy",startRow=2,endRow=10)

results in this error message:

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
  java.lang.OutOfMemoryError: Java heap space

Note that I was only asking to read in a small number of rows.

BTW, I also just made the post from a few minutes ago, but under another 
address ... The file in question here has the same data structure as the one I 
just posted, but this was the real example, not the toy example I just sent. 
Because this error call may be connected to the previous issue, I didn't 
generate a file here as a test case.

Should xlsx2 be able to handle this size problem?

R 1.14.1, Windows 7, xlsx just loaded today (1/20/12) from CRAN

Original issue reported on code.google.com by [email protected] on 20 Jan 2012 at 6:34

please export the getNumberOfSheets() function

please export the getNumberOfSheets() function
so i don't have to do voodoo magic

Original issue reported on code.google.com by [email protected] on 30 Jul 2014 at 10:34

make cell ranges use absolute references

What steps will reproduce the problem?
On Jun 19, 2014 1:50 PM, "Stephen Stamatis" wrote:
Hi Adrian,

First, let me say thank you for providing this package to the R community. It 
has certainly made my life easier!

For a recent project, I have found that using named ranges makes the excel 
followup work easier. The R routine in the package called: createRange() in the 
NamedRanges.R file is relevant.

It seems that by default, the ranges are created as relative(i.e. without $'s) 
, rather than as absolute(i.e. with $'s) . The resulting ranges work as 
expected for further processing in R as the references are all relative to cell 
A1. However upon opening the document in Excel, the ranges move with the active 
cell and therefore break.

I am not sure if this is designed behavior, but it seems like it is a bug to me.

You can force the references to be created as absolute by using the following 
form of the call to POI CellReference

CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) 

In your code, simply putting in TRUE, TRUE for the arguments worked for my test 
cases, but there are certainly other and likely better approaches. Perhaps the 
most obvious one to recommend if you were so inclined to address this issue, is 
that you could add the row and col absolute arguments to the createRange 
function definition. Defaulting them to FALSE should not break existing code.

Either way, I found a work around for me needs.  Again I wanted to thank you 
for sharing this work and wish you the best of luck.

All the best,
Stephen

Original issue reported on code.google.com by [email protected] on 26 Jun 2014 at 6:40

Zero column data frames make addDataFrame fail

What steps will reproduce the problem?
wb <- createWorkbook()
sheet <- createSheet(wb)
dfr <- data.frame(x = 1:5)[,FALSE] #Data frame with some rows, no columns.
addDataFrame(dfr, sheet)
## Error in .jcall(cellBlock$ref, "V", setDataMethod, as.integer(j - 1L),  : 
##   java.lang.ArrayIndexOutOfBoundsException: 1

What is the expected output? What do you see instead?
I expected addDataFrame to gracefully decline to add any content to the 
worksheet rather than throwing an error.

What version of the product are you using? On what operating system?
xlsx version '0.5.5'.

Please provide any additional information below.

The problem is caused by line 46 (depending upon how you count them):

    for (j in 1:ncol(x)) {

When x has zero columns, then 1:ncol(x) is c(1, 0), not an empty vector.

To fix the problem, change the line to:

    for(j in seq_along(x)) {

Original issue reported on code.google.com by [email protected] on 30 Jan 2014 at 11:32

Font + Fill does not set the Font in CellStyle

  require(xlsx)
  wb <- createWorkbook()
  sheet <- createSheet(wb, "Sheet1")

  rows   <- createRow(sheet, rowIndex=1:24)         
  cells  <- createCell(rows, colIndex=1:8)      

  setCellValue(cells[[1,1]], "Hello!")

  cs <- CellStyle(wb) +
        Font(wb, heightInPoints=25, isBold=TRUE, isItalic=TRUE, color="red", name="Arial") + 
        Fill(backgroundColor="lavender", foregroundColor="lavender", pattern="SOLID_FOREGROUND") +
        Alignment(h="ALIGN_RIGHT")

  setCellStyle(cells[[1,1]], cs)

  fileOut <- paste("C:/temp/issue3x_out.xlsx", sep="")
  saveWorkbook(wb, file=fileOut)  


If you add the Font before the Fill it does not currently work.  There is a 
copy&paste bug in CellStyle.

 } else if (is.Fill(object)) {
    CellStyle.default(cs1$wb, dataFormat=cs1$dataFormat,
      alignment=cs1$alignment, border=cs1$border, fill=object,
      font= !!!cs1$fill!!!, cellProtection=cs1$cellProtection) <------
  } else if (is.Font(object)) {

If you do Fill + Font it works. 

Original issue reported on code.google.com by [email protected] on 17 Jun 2014 at 6:52

setCellStyle() for cell ranges

In my use case I have to apply different cell styles to different blocks of a 
rather large table. With lapply( cells, setCellStyle, style ) it is rather slow.

Would it be possible to vectorize setCellStyle()?

Even more, maybe something like setCellStyle(rowIndexes,colIndexes,style ) (or 
setCellStyle(CellRange, style), where CellRange class is universally used 
throughout API to provide straightforward vectorization of many operations) 
would provide even higher performance boost?

Original issue reported on code.google.com by [email protected] on 2 Feb 2012 at 5:15

Class POSIXt/POSIXct for DateTime vectors

Yes, I was aware of this issue.  I will fix it soon. 

Thanks

From: Marco Giuliano 
Date: Sat, Jul 19, 2014 at 11:55 AM
Subject: Package xlsx bug
To:


Dear maintaner,
I think I found a bug in the xlsx package.


Basically, xlsx.datetime.format is not applied to POSIXct columns when using 
write.xlsx function.
Drilling down to the code, it seems that this check (in .write_block function) 
is not completely correct:

indDT <- which(sapply(y, class) == "POSIXct")

In fact, this works fine only if all the columns in "y" have just one class 
attribute defined (and in this case sapply can simplify the result to a 
vector). 
But POSIXct derives from POSIXt hence when you call class() it returns 
c("POSIXct","POSIxt"). 

Maybe you should use a different check, something like this:

indDT <- which(sapply(y, FUN=function(x) any(class(x) == "POSIXct")))

The same kind of check is performed on Date objects in (.write_block function) 
and probably should be changed as well (because it fails if just one of the 
columns belongs to more than one class).

If you want an example of the problem, have a look at the following question on 
stackoverflow:
http://stackoverflow.com/questions/24841068/format-dates-in-write-xlsx-of-xlsx-p
ackage/24841434

Original issue reported on code.google.com by [email protected] on 29 Jul 2014 at 5:51

Character  being added amongst text being read in

What steps will reproduce the problem?
1.Exporting an Excel document from database
2.Use the read.xlsx command
3.Check output

What is the expected output? What do you see instead?
I am expecting a string of text.  I'm getting a string of text with the 
character  interspersed.

What version of the product are you using? On what operating system?
I am running 32-bit R, on Windows 7.  I just downloaded the package a couple 
weeks ago.

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 25 Jul 2014 at 3:14

loading xlsx in R 3.0 script fails to load rJava

Tried loading xlsx in R 3.0.2. Failed to load saying it requred 'rJava' with 
the following error - 

  error: No CurrentVersion entry in Software/JavaSoft registry! Try re-installing Java and make sure R and Java have matching architectures.
Failed with error:  ‘package ‘rJava’ could not be loaded’

I updated my Java, re-started R, and no change. Should the package be calling 
rJava? The documentation says all the necessary Java stuff is supposed to be in 
a jar ... 

thx. 
ed.

Original issue reported on code.google.com by [email protected] on 2 Jan 2014 at 7:19

readColumns, read.xlsx2 do not read formulas correctly

What steps will reproduce the problem?
  wb <- loadWorkbook("test_formulas.xlsx")
  sheets <- getSheets(wb)
  sheet <- sheets[["Sheet1"]]
  x1 <- readColumns(sheet, startColumn=1, endColumn=3, startRow=1)
  x2 <- read.xlsx("C:/temp/Downloads/test_formulas.xlsx", 1)
  x3 <- read.xlsx2("C:/temp/Downloads/test_formulas.xlsx", 1)

What is the expected output? What do you see instead?
Only x2 is correct.  x1, x3 have NA's for the 3rd column.

Original issue reported on code.google.com by [email protected] on 14 Mar 2014 at 3:02

Attachments:

• test_formulas.xlsx

incompatibility with shiny (a web interface of R)

What steps will reproduce the problem?
1. wanted to use this functionality in Shiny. 
http://www.inside-r.org/packages/cran/shiny/docs/downloadHandler
2. if i use write.xlsx function in xlsx package, it will detect the file name 
and decide the extension
3. However, Shiny package will automatically assign a temporary file name (some 
random number & meaning less letters) as the temp file name. xlsx package 
cannot figure out what ext to use correctly.

What is the expected output? What do you see instead?
xlsx cannot decide what extension to use.

What version of the product are you using? On what operating system?
windows, shiny 0.9.1





Please provide any additional information below.
suggested modification: Modify write.xlsx function to be the following:

### start of code:

write.xlsx <- function(x, file, sheetName="Sheet1",
  col.names=TRUE, row.names=TRUE, append=FALSE, showNA=TRUE, forceext="") ##add additional parameter: forceext
{
  if (!is.data.frame(x))
    x <- data.frame(x)    # just because the error message is too ugly

  iOffset <- jOffset <- 0
  if (col.names)
    iOffset <- 1
  if (row.names)
    jOffset <- 1

  if (append && file.exists(file)){
    wb <- loadWorkbook(file)
  } else {
    if(forceext!=""){## if provided forceext, use that one. if not, determine extension by filename.
        ext <- forceext
    } else {
    ext <- gsub(".*\\.(.*)$", "\\1", basename(file))
    }
    wb  <- createWorkbook(type=ext)
  }  
  sheet <- createSheet(wb, sheetName)

### omitted more codes...

Original issue reported on code.google.com by [email protected] on 21 Apr 2014 at 10:49

rJava not imported: relies on Depends

What steps will reproduce the problem?
1. importing read.xlsx into another package using importFrom(xlsx,read.xlsx)

See: https://github.com/trinker/reports/issues/42

What is the expected output? What do you see instead?

Using this in this way causes rJava not to be imported because it is being 
relied upon with the Dependencies field in the DESCRIPTION file.  If 
import(rJava) were added to the NAMESPACE the issue would be fixed.  This is 
part of the reason xlsx gets this warning: 

http://www.r-project.org/nosvn/R.check/r-devel-windows-ix86+x86_64/xlsx-00check.
html

What version of the product are you using? On what operating system?

$Version
[1] "0.5.1"

on Windows 7 machine:

R version 3.0.1 (2013-05-16)
Platform: i386-w64-mingw32/i386 (32-bit)


Please provide any additional information below.

The issue opened at reports package GitHub that imports xlsx:

https://github.com/trinker/reports/issues/42

Original issue reported on code.google.com by [email protected] on 15 Sep 2013 at 5:55

install.packages("xlsx") fails on R 3.0.0 on CentOS 6.4

What steps will reproduce the problem?
install.packages("xlsx")

What is the expected output? Successful installation.
What do you see instead?
* installing *source* package 'xlsx' ...
** R
** inst
** preparing package for lazy loading
** help
*** installing help indices
  converting help for package 'xlsx'
    finding HTML links ... done
    Alignment                               html  
    Border                                  html  
    Cell                                    html  
    CellBlock                               html  
    CellProtection                          html  
    CellStyle-plus                          html  
    CellStyle                               html  
    Comment                                 html  
    DataFormat                              html  
    Fill                                    html  
    Font                                    html  
    NamedRanges                             html  
    OtherEffects                            html  
    POI_constants                           html  
    Picture                                 html  
    PrintSetup                              html  
    Row                                     html  
    Sheet                                   html  
    Workbook                                html  
    addDataFrame                            html  
    addHyperlink                            html  
    read.xlsx                               html  
    readColumns                             html  
    readRows                                html  
    write.xlsx                              html  
Rd warning: 
/home/data/tmp/RtmptmiZhH/R.INSTALL2c9f554dd59b/xlsx/man/write.xlsx.Rd:38: 
missing file link 'write.csv'
    xlsx-package                            html  
** building package indices
** installing vignettes
   'xlsx.Rnw' 
** testing if installed package can be loaded
Error : .onAttach failed in attachNamespace() for 'xlsx', details:
  call: .jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook")
  error: java.lang.NoClassDefFoundError: org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTWorkbookImpl
Error: loading failed
Execution halted
ERROR: loading failed
* removing '/usr/local/Rlibs/xlsx'
Warning message:
In install.packages("xlsx_0.5.1.tar.gz", type = "source") :
  installation of package 'xlsx_0.5.1.tar.gz' had non-zero exit status

What version of the product are you using? On what operating system?
Tried the CRAN 0.5.0 packages as well as the source xlsx_0.5.1.tar.gz and both 
fail with the same error 
(java.lang.NoClassDefFoundError:org.openxmlformats.schemas.spreadsheetml.x2006.m
ain.impl.CTWorkbookImpl)

Please provide any additional information below.
I have installed both dependencies "rJava" and "xlsxjars" successfully.

Original issue reported on code.google.com by [email protected] on 17 May 2013 at 1:03

showNA=FALSE as default for write.xlsx() ?

What steps will reproduce the problem?
1. save a data frame with missing values with write.xlsx() 
2. open the *.xlsx-file in Excel 2007


What is the expected output? What do you see instead?
I expect blank cells in Excel where there are missing values in R, instead 
these cells will have the value "#N/A" (not available). 
This causes confusion at the recipient end (I use Excel to exchange data with 
my colleagues) and all kinds of problems in Excel calculations. I have to use 
write.xlsx2() with the option showNA=FALSE to create a spreadsheet with the 
expected format. 

What version of the product are you using? On what operating system?
Windowss 7 64-bit, R 2.14.1/Rstudio 0.96.22, Excel 2007

Please provide any additional information below.

Original issue reported on code.google.com by [email protected] on 16 Feb 2012 at 1:14

Cannot install on Mac OS X Lion

I am running R verson 2.15.1 on Mac OS 10.7.4
At CRAN it shows for package xlsx:
MacOS X binary:     not available, see check log.
The log shows:
checking examples ... ERROR
Running examples in 'xlsx-Ex.R' failed
The error most likely occurred in:

> ### Name: NamedRanges
> ### Title: Functions to manipulate named ranges.
> ### Aliases: NamedRanges Range getRanges readRange createRange
>
> ### ** Examples
>
>
> file <- system.file("tests", "test_import.xlsx", package = "xlsx")
>
> wb <- loadWorkbook(file)
> sheet <- getSheets(wb)[["deletedFields"]]
> ranges <- getRanges(wb)
>
> res <- readRange(ranges[[1]], sheet, colClasses="numeric") # read it
Error in .jnew("dev/RInterface") :
java.lang.UnsupportedClassVersionError: Bad version number in .class file
Calls: readRange -> .jnew -> .External
Execution halted 

Within R I do seem to detect java installed:

> .jcall('java.lang.System','S','getProperty','java.version')
[1] "1.6.0_33"

On a different Mac with the same OS version, but running R 2.14.1, I am running 
version 0.3.0 of the package successfully, and have the same java version 
number.  I would like to upgrade R on that machine but am afraid of breaking 
xlxs.

Original issue reported on code.google.com by [email protected] on 7 Jul 2012 at 10:55

Date origin wrong if non-date in column

As posted as a StackOverflow question, with sample file and code:

http://stackoverflow.com/questions/25158969/read-xlsx-reading-dates-wrong-if-non
-date-in-column

Basically, if there's a non-date in a column then read.xlsx will convert dates 
from using the excel 1899 epoch, rather than 1970 and so be 70 years in the 
future. Skip reading the non-date item and it all converts correctly.

Original issue reported on code.google.com by [email protected] on 6 Aug 2014 at 3:49

CellBlock does not maintain the formatting of the entire column

Hi,

I have been using the xlsx package for a while, it's been very helpful to me. I 
recently noticed a bug when formatting is applied to entire columns and I use 
CellBlock. When using CellBlock with format applied to entire columns, some of 
the cells loose their formatting when saving the workbook. However, if format 
is only applied to cells, this problem doesn't arise. The problem I'm facing in 
my application is that the initial Excel spreadsheets are created by clients 
and they may apply formats over entire columns 

Thanks a lot for your help,
Benoit

Here is a short reproducible example (the files "bad_format.xlsx" and 
"good_format.xlsx" are attached):

library("xlsx")
wb <- loadWorkbook("bad_format.xlsx") #using formatting on entire columns (A to 
L)
sh <- getSheets(wb)
s1 <- sh[[1]]

cb <- CellBlock(sheet = s1, startRow = 1, startCol = 1, noRows = 11, noColumns 
= 50, create = FALSE)
saveWorkbook(wb, "format_lost.xlsx") # some of the formatting is lost (col I to 
L, rows 1 to 11)





wb <- loadWorkbook("good_format.xlsx") #using cells formatting only (columns A 
to L rows 1 to 10,000)
sh <- getSheets(wb)
s1 <- sh[[1]]

cb <- CellBlock(sheet = s1, startRow = 1, startCol = 1, noRows = 11, noColumns 
= 50, create = FALSE)
saveWorkbook(wb, "format_kept.xlsx") # the formatting is kept

Original issue reported on code.google.com by [email protected] on 13 Feb 2014 at 7:51

Attachments:

• bad_format.xlsx
• good_format.xlsx

read.xlsx2 doesn't evaluate formulas - values are NA

I'm trying to import a simple .xlsx file containing a single worksheet with 
tabular data. One of the columns is evaluated by a formula, which is just a sum 
of some other columns. When using read.xlsx2, the values in the column just 
appear as NA. The read.xlsx2 help indicates that formulas should be evaluated. 
read.xlsx works as expected - numerical values are present.

The syntax I'm using is: read.xlsx2("readxlsx2demo.xlsx",1)

I'm using xlsx package version 0.5.0 on R 2.15.1 (64bit Linux).

Original issue reported on code.google.com by [email protected] on 7 May 2013 at 11:30

Attachments:

• readxlsx2demo.xlsx

#N/A values are imported as 0 (zero) by read.xlsx

What steps will reproduce the problem?
1. create an xlsx file containing some cells with an #N/A value, for example, 
using the formula =NA()
2. read the spreadsheet into R using read.xlsx

What is the expected output? What do you see instead?
Excel cells containing #N/A should be imported as NA, not 0 (zero).

What version of the product are you using? On what operating system?
Windows 7 64 bit, R version 2.14.1 (2011-12-22), Platform: 
x86_64-pc-mingw32/x64 (64-bit), xlsx_0.4.2, xlsxjars_0.3.0, rJava_0.9-3 

Original issue reported on code.google.com by [email protected] on 19 Mar 2012 at 4:31

Columns of data (as formulas) are being read in as NA

Try

read.xlsx2(file="xlxs2Test.xlsx",sheetName="data",startRow=2,endRow=10, 
colIndex=c(1,3:5,7:9), colClasses=c("character",rep("numeric",6)) )

Two col's get NA's, but have data in them. Or try this:

read.xlsx2(file="xlxs2Test.xlsx",sheetName="data",startRow=2,endRow=10, 
colIndex=c(1,4:5,8:9), colClasses=c("character",rep("numeric",4)) )

Now it's two other columns.

When I copy/paste this sheet into another file "as values" the problem 
disappears.

R 2.14.1, Windows 7, with xlsx downloaded today (1/20/12) from CRAN.

Original issue reported on code.google.com by [email protected] on 20 Jan 2012 at 6:20

Attachments:

• xlxs2Test.xlsx

Set cell height

On Jul 10, 2014 11:17 AM, "Sven Neulinger" <[email protected]> wrote:
Dear Adrian,

I am very fond of your R package "xlsx" and use it frequently in my daily work 
with R.

I was just wondering whether there is (or could be) an option to set the height 
of the cells in a sheet? If it is possible, perhaps you could set this on the 
"wish list" for the next version of your great package.


Best wishes from Kiel,
Sven



-- 
Dr. Sven Neulinger

Institute for General Microbiology
at Christian Albrecht University
Am Botanischen Garten 1-9
24118 Kiel
Germany

Original issue reported on code.google.com by [email protected] on 11 Jul 2014 at 9:45

The append argument in write.xlsx/write.xlsx2

I have two comments about the append argument to write.xlsx, where I think that 
the behaviour could be improved.  (I couldn’t find the development version of 
xlsx; is there an issue tracker somewhere?)

One important use of Excel files is to store several data frames in a single 
file (which can be a little tidier than having multiple CSV files).

Here's a list of data frames, and the plan is to call write.xlsx2 in a loop.

data_list <- list(
  first  = data.frame(x = 1:3, y = letters[1:3]),
  second = data.frame(x = 1:4, y = runif(4), z = rnorm(4))
)
sheetNames <- names(data_list)

Since we want to write to the same file several times, it's natural to set 
append = TRUE.

for(i in seq_along(data_list))
{
  write.xlsx2(
    data_list[[i]],
    "test.xlsx",
    sheetName = sheetNames[i],
    append    = TRUE
  )
}
## Error in loadWorkbook(file) : Cannot find test.xlsx

Unfortunately, this fails, since the file doesn't exist to begin with.  It can 
be fixed by changing to append = i > 1, but this takes some thinking about.  It 
also makes it more difficult to call write.xlsx from lapply or mapply.

I think that I more intuitive behaviour of append would be "if the file exists, 
then append new contents, otherwise create the file".  This would also make the 
behaviour consistent with write.table and cat.

The fix for this is easy.  In write.xlsx and write.xlsx2, change

if (append) {
    wb <- loadWorkbook(file)
}
else {
    ext <- gsub(".*\\.(.*)$", "\\1", basename(file))
    wb <- createWorkbook(type = ext)
}

to

if (append && file.exists(file)) {
    wb <- loadWorkbook(file)
}
else {
    ext <- gsub(".*\\.(.*)$", "\\1", basename(file))
    wb <- createWorkbook(type = ext)
}

Since this snippet appears twice, you might want to wrap it in a function, like

loadOrCreateWorkbook <- function(file, append)
{
    if (append && file.exists(file)) {
        loadWorkbook(file)
    }
    else {
        ext <- gsub(".*\\.(.*)$", "\\1", basename(file))
        createWorkbook(type = ext)
    }
}


The other issue I found is that it isn't obvious how to append values to an 
existing worksheet. If I tweak the loop to write the data frames in the same 
worksheet, one below the other, I get an error.

startRow <- c(1, nrow(df1) + 2)   
for(i in seq_along(data_list))
{
  write.xlsx2(
    data_list[[i]],
    "test2.xlsx",
    startRow = startRow,
    append   = i > 1
  )
}
## Error in .jcall(wb, "Lorg/apache/poi/ss/usermodel/Sheet;", "createSheet",  :
##   java.lang.IllegalArgumentException: The workbook already contains a sheet 
of this name 

My suggested fix is to replace this line in write.xlsx and write.xlsx2:

sheet <- createSheet(wb, sheetName)

with

sheet <- getOrCreateSheet(wb, sheetName, append)

where getOrCreateSheet is defined as

getOrCreateSheet <- function(wb, sheetName, append)
{
  if(append)
  {
    sheets <- getSheets(wb)
    if(sheetName %in% names(sheets))
    {
      return(sheets[[sheetName]])
    }
  }
    createSheet(wb, sheetName)
  } 
}

Let me know if these fixes are worth considering.

Regards,
Richie

Original issue reported on code.google.com by [email protected] on 30 Nov 2013 at 1:33

read.xlsx will throw java error with NA in first row of a numeric column

What steps will reproduce the problem?

file <- system.file("tests", "test_import2.xlsx", package="xlsx")
res <- read.xlsx(file, sheetName="all (2)", rowIndex=3:5, colIndex=3:5)

What is the expected output? What do you see instead?

the read.xlsx call should return without error and res should be
> res
  mon day year
1 Jan   1   NA
2 Feb   2 2001
> dput(res)
structure(list(mon = structure(1:2, .Label = c("Jan", "Feb"), class = 
"factor"), 
    day = c(1, 2), year = c(NA, 2001)), .Names = c("mon", "day", 
"year"), row.names = 1:2, class = "data.frame")

The actual response is

> res <- read.xlsx(file, sheetName="all (2)", rowIndex=3:5, colIndex=3:5)
Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
  java.lang.IllegalStateException: Cannot get a numeric value from a text cell

What version of the product are you using? On what operating system?

xlsx version 0.5.0
xlsxjars version 0.4.0
rJava 0.9.3
R version 2.15.2 (2012-10-26)
Windows 7 - 64 bit

Please provide any additional information below.

The problem only occurs when as.data.frame is TRUE. In that case, during the 
loop through cols, in the check for numeric columns, the following lines are 
problematic:

cell <- cells[[paste(ind + header, ".", ic, 
  sep = "")]]
isDatetime <- dateUtil$isCellDateFormatted(cell)

The first one indexes into the list cell, but with the wrong indicies. 
ind+header and ic give the row and column in VV (1 indexed), but cells is 
indexed by the row and column number in the worksheet. I think the call should 
be

cell <- cells[[paste(row.names[ind + header], ".", col.names[ic], 
  sep = "")]]

but I have not tested it. The problem only seems to manifest if ind is not 1.

The attached file is a modification of the given testing file for import with 
an addition sheet which is a copy of all but with the first year value missing.

Original issue reported on code.google.com by [email protected] on 6 Feb 2013 at 12:15

Attachments:

• test_import2.xlsx

CB.setMatrixData has issues for text data

What steps will reproduce the problem?
wb = createWorkbook()
sheet = createSheet(wb)
mtx = as.matrix(data.frame(a="hello"))
cb = CellBlock(sheet, 1, 1, 1, 1)
CB.setMatrixData(cb, mtx, 1, 1)
saveWorkbook(wb, "test.xlsx")

What is the expected output? What do you see instead?
see a #NUM! cell instead of the string!

Original issue reported on code.google.com by [email protected] on 30 Jul 2013 at 5:03

Using any RGB color

I'm providing hex code of RGB color for Fill(backgroundColor=).
However, it does not seem to work -- in the workbook all cells have some 
default fill color.

In Apache POI I see XSSFCellStyle.setFillBackgroundColor(XSSFColor) method, so 
it should be possible to specify non-indexed colors for XLSX workbooks.

Original issue reported on code.google.com by [email protected] on 20 Jan 2012 at 1:09

java.lang.OutOfMemoryError error message when calling saveWorkbook or CellBlock

What steps will reproduce the problem?
1. run the attached script. You may have to crank up the number of sheets (ns)

What is the expected output? What do you see instead?
I expect an xlsx to be created, with ns tabs, each containing the same matrix 
of nc*nr data cells. Instead, I get an error saying 
"java.lang.OutOfMemoryError: Java heap space". Under other circumstances (e.g. 
increase the value of ns), I also get a "java.lang.OutOfMemoryError: GC 
overhead limit exceeded" error.

What version of the product are you using? On what operating system?

R version 3.0.2 (2013-09-25)
Platform: x86_64-unknown-linux-gnu (64-bit)
xlsx_0.5.5
xlsxjars_0.5.0
rJava_0.9-6

Please provide any additional information below.

Thanks for your help.

Original issue reported on code.google.com by [email protected] on 19 Feb 2014 at 9:39

Attachments:

• xlsx_OutOfMemoryError.txt

Allow non american date formats in addDataFrame()

addDataFrame() only allows the usage of date format 'm/d/yyyy'. So all dates 
from R objects are presented in north american date style. Users of other 
regions have to convert the date style in Excel afterwards.

The patch shows how this could be solved for other date formats. If 
addDataFrame() is called without setting dateStyle or dateTimeStyle, the 
default behaviour is used. Otherwise date[Time]Style should be used. 

This requires a correct date format string in date[Time]Style. I think it would 
be nice to have some checks here about date formats conforming to objects of 
classes "POSIXlt" and "POSIXct", like as.Date() does.

An example to write ISO 8601 dates, which are often used in European data sets, 
is shown below:

  addDataFrame(x, sheet, dateStyle="yyyy-mm-dd")

This should create date columns presented like "2013-07-29".

Tested on FreeBSD 10.0-CURRENT with R-devel r63392 and xlsx_0.5.3.tar.gz.

Original issue reported on code.google.com by [email protected] on 29 Jul 2013 at 6:18

Attachments:

• xlsx_patch-addDataFrame.diff

Problem with CB.setFill() in xlsx package

I have come across a problem with the CB.setFill() method. Whatever I do, I 
only seem to able to properly set the fill color of a CellBlock object, if the 
cell block contains the first column. So in the below example, it only works as 
I expect it if col=c(1,2,3) or col=c(1,2), but not if col=c(2,3) .
Am I using it incorrectly here? I would be very pleased if you could help me 
with this.

Thank you very much in advance
Holger


df = data.frame(c(1,2,3,4,5), c(6,7,8,9,10), c(11, 12, 13, 14, 15)) 
colnames(df) <- c('x', 'y', 'z') 

wb <- createWorkbook() 
sh1  <- createSheet(wb, sheetName="TestFill") 
addDataFrame(df, sh1, row.names=F) 

numRows = nrow(df)+1 

# col=c(1,2,3)
# col=c(1,2) 
col=c(2,3) 

print(col) 

rIdx = c() 
cIdx = c() 
for(ri in 1 : numRows ) 
{ 
  for(ci in col) 
  { 
    rIdx = c(rIdx, ri) 
    cIdx = c(cIdx, ci) 
  } 
} 

print(rIdx) 
print(cIdx) 

fill <- Fill(foregroundColor = 'red') 
cb =  CellBlock(sh1, startRow=1, startColumn=min(col), noRows=numRows, 
noColumns=length(col), create=F) 
CB.setFill( cb, fill, rowIndex=rIdx, colIndex=cIdx ) 

saveWorkbook(wb, "C:/PLAYGROUND/TestFill.xlsx")

Original issue reported on code.google.com by [email protected] on 26 May 2014 at 12:26

facilities to work with password protected spreadsheets

On Thu, Aug 14, 2014 at 4:34 PM, Heather Turner <> wrote:
> Dear Adrian,
>
> I have extended your read.xlsx and write.xlsx functions so that they can
> handle password-protected xlsx files and wondered if you might like to add
> this functionality to your package.
>
> I attach my versions, along with a simple example of their use. In each
> case, the functions gain a new argument "password" and some new code wrapped
> in an if (!is.null(password)){} statement.
>
> The advantage of this code - as with your package in general - is that it
> does not require the user to have Excel and it works on non-Windows
> operating systems (I have tested it on Ubuntu 12.04 and Windows 7). The
> disadvantage is that the encryption/decryption works by streaming the data,
> so it does not scale well.
>
> I am not a Java programmer - the only Java I know is what I picked up in
> writing this code - so I hope you may see a way to improve the code. However
> as I think the only other way to read/write password-protected xlsx files
> with R is via rcom, I think it would be useful to make this functionality
> available, even if it comes with a health warning in its current state.
>
> I have only considered xlsx files, a different approach would be needed to
> support xls files as well (see http://poi.apache.org/encryption.html).
>
> Best wishes,
>
> Heather

Original issue reported on code.google.com by [email protected] on 15 Aug 2014 at 2:15

Get an NPE when reading .xls files when they are not properly constructed and return more rows than they actually exist.

If you read an Excel file produced by other software.  It can have wrong 
metadata.  Excel opens it OK but POI chokes on it.

I fixed one case when the file was having wrong total number of rows.  Hard to 
replicate but be aware of it if your Excel file wasn't created by Excel.

Original issue reported on code.google.com by [email protected] on 11 Jul 2013 at 3:53

readColStrings does not deal gracefully with numeric cells that are exact integers

What steps will reproduce the problem?
Hello Adrian,

read.xlsx2 uses readColumns and can be used for demonstration. Please apply the 
following to the excel-sheet which is attached to this email:

> read.xlsx2(file="Mappe6.xlsx", sheetIndex=1,header=T, startRow=1)
            ID     Var1     Var2     Var3     Var4     Var5
1      some_ID 140795.0 139823.0 127481.0 139428.0 124504.0
2      some_ID 136730.0 139271.0 122904.0 135334.0 125054.0
3      some_ID    599.0    602.0    588.0    608.0    585.0
4      some_ID    591.0    606.0    590.0    607.0    572.0
5      some_ID  59216.0  60628.0  55937.0  60009.0  56593.0
6      some_ID  56161.0  60005.0  53225.0  61313.0  55045.0
7      some_ID 173512.0 183931.0 176187.0 183145.0 177161.0
8      some_ID 187564.0 191837.0 179728.0 203337.0 185503.0
9      some_ID 293328.0 304834.0 279916.0 299825.0 291336.0
10     some_ID 286142.0 308373.0 274668.0 290699.0 299876.0
11     some_ID 568922.0 592473.0 543189.0 608836.0 585318.0
12     some_ID 604358.0 604527.0 574823.0 636996.0 561347.0
13   ID_173377 130794.0 135325.0 129704.0 135719.0 120876.0
14   ID_173377 131573.0 141457.0 126680.0 129302.0 130420.0
15   ID_173378 274295.0 278379.0 254431.0 270977.0 282473.0
16   ID_173378 273824.0 289909.0 249555.0 268955.0 271803.0
17        DIL1    535.0    552.0    542.0    568.0    538.0
18        DIL1    543.0    558.0    546.0    568.0    549.0
19       DIL12    534.0    555.0    547.0    579.0    551.0
20       DIL12    545.0    542.0    558.0    552.0    549.0
21        DIL2    542.0    549.0    561.0    565.0    548.0
22        DIL2    545.0    549.0    545.0    561.0    536.0
23       DIL22    541.0    563.0    548.0    542.0    533.0
24       DIL22    539.0    554.0    546.0    571.0    546.0
25   8561731.0    642.0    648.0    637.0    662.0    637.0
26   8561731.0    626.0    641.0    627.0    637.0    636.0
27   8562027.0  86586.0  89251.0  76589.0  85297.0  81265.0
28   8562027.0  86805.0  88403.0  75821.0  82734.0  78306.0
29   8561687.0 120873.0 125302.0 114683.0 120088.0        -
30   8561687.0 121806.0 125760.0 111067.0 123733.0        -
31   8561693.0 187846.0 193084.0 170045.0 179625.0        -
32   8561693.0 179427.0 186746.0 165031.0 180891.0        -
33   8561695.0 295880.0 311290.0 257194.0 275443.0 276352.0
34   8561695.0 289880.0 308724.0 262690.0 283313.0 284507.0
35 1.1101705E7 250829.0 276172.0 239355.0 252497.0        -
36 1.1101705E7 257470.0 268823.0 238330.0 244641.0        -
37   8561807.0 324990.0 339518.0 299721.0 329293.0        -
38   8561807.0 335870.0 358638.0 303811.0 313192.0        -
39   8561833.0 127564.0 134044.0 123011.0 131601.0        -
40   8561833.0 127555.0 135941.0 119576.0 133136.0        -
41   8561835.0 170857.0 176093.0 157760.0 168544.0        -
42   8561835.0 168666.0 167638.0 151018.0 154477.0        -
43 1.0917428E7  89913.0  93122.0  79531.0  96574.0  82483.0

The output on my HP Z420 workstation with Win 7 Enterprise, with the the 
following R-versioni: 

R version 3.0.1 Patched (2013-07-14 r63298)
Platform: i386-w64-mingw32/i386 (32-bit)

locale:
[1] LC_COLLATE=German_Switzerland.1252  LC_CTYPE=German_Switzerland.1252    
LC_MONETARY=German_Switzerland.1252 LC_NUMERIC=C                       
[5] LC_TIME=German_Switzerland.1252   

is shown above. I put the strange values in bold.

Please let me know if this testcase is sufficient.

Best André

What is the expected output? What do you see instead?

You should see 11101705 but you see 1.1101705E7. 

Original issue reported on code.google.com by [email protected] on 22 Sep 2013 at 9:53

Add auto filter

missing an auto filter

Original issue reported on code.google.com by [email protected] on 2 Aug 2014 at 3:57

change the .onLoad functions to allow other packages to control Java VM when loading xlsxjars

Hi Adrian,

my request would be to remove the function .onLoad from xlsxjars and add the 
line 

.jpackage("xlsxjars")

to the .onLoad function from package xlsx.

Consequences:
1) I doubt that normal users will use package xlsxjars without xlsx , so this 
shouldn't do much harm.
2) But this way packages using xlsxjars can initialize the Java virtual machine 
themselves, since it is impossible to change the parameters of an already 
running VM (started by the .jpackage call in .onLoad).
3) POI is a great library with a decent Excel support - but for Word files 
there are more bugs and for gMCP I needed to patch some of the classes.
Therefore I would like to put these files into the classpath first and 
afterwards call .jpackage("xlsxjars"), so that the patched version is used. 
(Yes, the best solution would be to write a custom class loader...)

Btw. why is .jpackage and the Java version check called in .onAttach in package 
xlsx instead of .onLoad? That way packages that want to use xlsx as import 
(that means the package is not attached) can't use functions like readColumns, 
since the RInterface class would not be in the class path, if the package 
author isn't aware that he must call .jpackage("xlsx") himself. (But on the 
other hand moving the code from .onLoad to .onAttach in xlsxjars would also 
solve all my problems!!)

Best regards, Kornelius

Original issue reported on code.google.com by [email protected] on 25 Mar 2014 at 3:22

Error Generated on Importing Empty Sheet

Importing an empty Excel sheet does not contain header names on the first row, 
hence resulting in a generated error.  The read.xlsx(2) function should return 
a NULL object for these cases.

Original issue reported on code.google.com by [email protected] on 27 Dec 2013 at 3:48

rowIndex does not work as previously

I just reran a collection of scripts coded before the last update, and rowIndex 
does not work as it previously did. This has affected many of my scripts, where 
I have ended up using startRow and endRow instead, even though they do not 
allow the same flexibility.

Original issue reported on code.google.com by [email protected] on 22 Jun 2013 at 7:43

transpose=T/F parameter for addDataFrame()

It would be nice to add transpose=T/F (FALSE by default) parameter for 
addDataFrame().

If transpose=TRUE, the columns in the dataframe become rows in Excel sheet.

Original issue reported on code.google.com by [email protected] on 8 Feb 2012 at 2:35