Search code examples
rexcelrjavamousehovervba

Excel mouse hover text with xlsx package

Is there a way to programm a mouse hover text, something like this: "look at 7:35 min"

Im using the xlsx package in R.

It doesnt have to look anything fancy like in the video. I just want some text to appear when i hover over the "???" cell.

Any suggestions?

To get you guys started:

library(xlsx)

write.xlsx("???",file="hoverText.xlsx",row.names = F,col.names = F)

wb    <- loadWorkbook("hoverText.xlsx")
sheet <- getSheets(wb)

EDIT:

adds a comment (something like a hover-text), but the comment box is way to small, for the text i intent to write. (in Excel i can manually change the size, lets try to find a R way to change the comment box size)

library(xlsx)

write.xlsx("???",file="hoverText.xlsx",row.names = F,col.names = F)

wb    <- loadWorkbook("hoverText.xlsx")
sheet <- getSheets(wb)[[1]]

row   <- xlsx::getRows(sheet)
cell  <- xlsx::getCells(row, colIndex = 1)

comment <- "most foobar comment of all time\nhopefully with newline"

createCellComment(cell[[1]], string=comment, author=NULL, visible=TRUE)
saveWorkbook(wb,file="hoverText.xlsx")

EDIT2:

after hours of searching the web still no success. It has something to do with those functions:

ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(cell.getRowIndex()); anchor.setRow2(cell.getRowIndex()+ 1);
anchor.setDx1(100);
anchor.setDx2(100);
anchor.setDy1(100);
anchor.setDy2(100);

There is a vba solution for the comment box auto sizing

CELL.Comment.Shape.TextFrame.AutoSize = True

dont know yet how to run VBA code in excel from R

Solution
    1. Take the code from 1st Edit:
    2. Change createCellComment to createCellComment2 or overwrite the function
    3. This is mostly the xlsx::createCellComment with height and width of the comment box
    createCellComment2 <- function (cell, string, author = NULL, visible = TRUE,height=2,width=2) {
    sheet <- .jcall(cell, "Lorg/apache/poi/ss/usermodel/Sheet;", "getSheet")
    wb <- .jcall(sheet, "Lorg/apache/poi/ss/usermodel/Workbook;", "getWorkbook")
    factory <- .jcall(wb, "Lorg/apache/poi/ss/usermodel/CreationHelper;", "getCreationHelper")
    anchor <- .jcall(factory, "Lorg/apache/poi/ss/usermodel/ClientAnchor;", "createClientAnchor")
    .jcall(anchor, "V", "setCol2", as.integer(width))
    .jcall(anchor, "V", "setRow2", as.integer(height))

    drawing <- .jcall(sheet, "Lorg/apache/poi/ss/usermodel/Drawing;", "createDrawingPatriarch")
    comment <- .jcall(drawing, "Lorg/apache/poi/ss/usermodel/Comment;", "createCellComment", anchor)

    rtstring <- factory$createRichTextString(string)

    comment$setString(rtstring)

    if (!is.null(author)) 
        .jcall(comment, "V", "setAuthor", author)
    if (visible) 
        .jcall(cell, "V", "setCellComment", comment)
            invisible(comment)
}