Finalize Interface for 1.0.0

[olivroy]

I think that some features may need a little bit of revisiting before the release

wb <- wb_workbook()

What I think would be good to have a simpler interface

get_named_regions()

Could be a workbook wrapper? Seems to be one of the only functions to use x. Suggest users to use wb <- wb_load(xl_file)
wb$get_named_regions()

Simple comment

wb$add_comment(x = "a comment")

For a simple comment, comment <- wb_comment(x) could happen under the hood. It would not be too dangerous.
I think that create_comment() should be deprecated in favour of wb_comment() #758

Simple text hyperlink

At the moment, the interface is not very user-friendly.. It seems like you just have to follow a tutorial you don't understand to make it work

wb$add_hyperlink(dims = "A1", text = "my text", url = "https://google.ca") 

copy cells

wb <- wb_workbook()
wb$add_worksheet()
wb$add_data(x = mtcars)
wb$copy_cells(data = wb_dims(x = mtcars, select = "col_names"), dims  = "W1", transpose = TRUE)

Examples

There are still examples with the old write_() functions. Attempted to fix many of them in #749.
Follow-up to finalize clean-up later this week. (will help see if something is wrong.

Vignettes

Some stuff could be removed from vignettes. Especially the one that has been copied over from openxlsx. I think that guides should display only the essential info, other info could be provided elsewhere.

Later

The create functions

They don't all behave the same...

Which functions do what?

#91 (comment)

Many function work at the worksheet level, while others play at the workbook level. improved
Basically, I reviewed the function title to add suffix to a worksheet or to a workbook

Function parameters

• There is a num_fmt argument somewhere
• create_numfmt() has camelCase, create_sparklines() has camel case
• wb$createCols is camelCase and is used with wb_group, but it's not documented. maybe could be private? fixed by [wb_group_cols()] automate column creation #781

Script of function use in docs

https://gist.github.com/olivroy/d671e54a817f1e6cec31505491462b70

[JanMarvin]

i really understand and i'm glad that my little project means a lot to you. at the end of the day, though, i've been doing this every day for almost 2 years now. i'm happy when i can use the package myself and i'm happy when others use it and tell me or write me when they like it. after all that's my reward for all the effort. but after that time i'm also a bit tired and don't want to prolong everything just for a more polished 1.0 release.

most likely i know more about xlsx files than the vast majority of other people and more than is healthy. for me, 1.0 is in a way a conclusion of a chapter. the package is quite big, there will always be something to do. and like I've said, there will be new releases. the hyperlink function has other issues, it uses HYPERLINK() is one to begin with, the Excel one in #753 doesn't. ever tried to write a few like character cells? i will try to fix any bug coming up to the weekend, test the vignette codes and if nothing breaks I'll submit it. sorry, but no further delays. 1.0 is just a number. one with a project specific meaning, but otherwise its just a floating point value.

[olivroy]

Hi, thanks for your hard work!

In #753, it made me wonder how to create a reprex. And I was not able to do it in a clean and easy fashion. hence why my hyperlinks questions.

It is just that you said you didn't want to create breaking changes after 1.0, and it's just that I see that some functions exist, but are a bit superfluous or buggy.

I think that openxlsx2 is very powerful, and well maintained, that's why I am very interested!

[JanMarvin]

Thanks, I appreciate your support, your pull requests speak for themselves! I wrote because I don't feel the same enthusiasm about some things as you do, at least not anymore. E.g. the comment function above. Yes, it's not the best designed feature, but it works. It works pretty well, and I say that because I spent literally days improving it over the old, wonky version in openxlsx (where someone else obviously spent a lot of time researching and implementing it in the first place).

But the function ... I don't use it at all. Never have and probably never will. I have Excel at work and I get paid to work with it. I can open a spreadsheet, click on a cell and write something in it. And that often eliminates any problems associated with using any software other than Excel. So when I work to improve the commenting feature in openxlsx2, I am tweaking something I don't care about. Same goes for hyperlinks (yes, they shouldn't break the workbook when loading, but otherwise I don't care about them at all. Are they hard to construct? Hmm, I don't know, but they are a pain to maintain. This is what I can say.

Same goes with camelCase etc. Yeah, I get it, use snake_case. But the world is not stopping for turning, if there are a few functions or arguments that use camelCase ... and if some user somewhere is frustrated because of that, maybe they should step outside and go for a walk.

I don't want to break people's code. That is my main goal. And from 1.0 to 2.0, your code shouldn't break for unnecessary reasons (like changing camelCase to snake_case or rearranging function arguments or whatever). But if something is reimplemented in 1.2 and fits into the given structure, then that's not a problem after all.

wb_add_formula() is not the best function to begin with and I'd love to update it, but then again the current implementation works well enough, that it is not a high priority. If write_formula() examples exist in the documentation and we can not switch them to wb_add_formula() we can simply remove them. Not sure about the vignette, the conditional formatting vignette is carried over, but has been improved and extended and is actually useful to me, but maybe you are talking about something else.

[olivroy]

About the create_comment() function, I have no complain about how it works. It works well. It is just about the name. Since it creates a wbComment object, I just wanted to suggest to name it wb_comment() instead. And also to suggest accepting a string as comment, and have openxlsx2 do the heavy lifting. (i.e. not let the front user know about another function if they do not need it) (I will research that and open a draft PR)

About the hyperlinks, I just had issue trying to create a reprex when I opened #753, instead of sharing the xlsx file itself. hence why I raised the issue. After a bit of digging, I just saw some things that I wanted to point out. Such as wb_hyperlink(), an exported function with no documentation or use. Let me know what your views are on that and I can implement.

About camelCade, I don't really care either, as you did tremendous work already. Just wanted to point them out as you may not be aware. There is a lot of code in openxlsx2.

Overall, openxlsx2 can do many things. I just want to help reduce the amount of things a user needs to know to use openxlsx2 proficiently, and to list its limitations.

[olivroy]

I was thinking adding a few more @keywords internal to functions that are more buggy and / or don't fit that much in openxlsx2 API. Not to take care of them now, but at least, so they are flagged as incomplete to warn users. "Use at your own risks.

I will open a PR. but awaiting your suggestions!

• Add @keywords internal + a note in the docs to exported functions that are buggy and or do not fit well in the API, or not ready to use widely or should not be used widely.
  • create_tablestyle
  • wb_hyperlink / create_hyperlink
  • wbChartSheet (possibly should not export that, afaik)
  • Any other you feel that should not be here (but should still be exported in the meantime)
    

[JanMarvin]

• create_tablestyle() works well, it's just really complicated to use =)
• don't care about the hyperlinks unless they are broken, they might remain where they are. Why are you against this (updated to replace write_formula()):

wb <- wb_workbook()$
  add_worksheet("Sheet1")$add_worksheet("Sheet2")$add_worksheet("Sheet3")

## Internal Hyperlink - create hyperlink formula manually
x <- '=HYPERLINK(\"#Sheet2!B3\", "Text to Display - Link to Sheet2")'
wb$add_formula(sheet = "Sheet1", x = x, dims = "A1")

## Internal - No text to display using create_hyperlink() function
x <- create_hyperlink(sheet = "Sheet3", row = 1, col = 2)
wb$add_formula(sheet = "Sheet1", x = x, dims = "A2")

## Internal - Text to display
x <- create_hyperlink(sheet = "Sheet3", row = 1, col = 2,text = "Link to Sheet 3")
wb$add_formula(sheet = "Sheet1", x = x, dims = "A3")

## Link to file - No text to display
x <- create_hyperlink(sheet = "Sheet1", row = 3, col = 10, file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"))
wb$add_formula(sheet = "Sheet1", x = x, dims = "A4")

## Link to file - Text to display
x <- create_hyperlink(sheet = "Sheet2", row = 3, col = 10, file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"), text = "Link to File.")
wb$add_formula(sheet = "Sheet1", x = x, dims = "A5")

## Link to external file - Text to display
x <- '=HYPERLINK("[C:/Users]", "Link to an external file")'
wb$add_formula(sheet = "Sheet1", x = x, dims = "A6")

x <- create_hyperlink(text = "test.png", file = "D:/somepath/somepicture.png")
wb$add_formula(x = x, dims = "A7")

if (interactive()) wb$open()

• with wbChartSheet I'm always confused that I did not rename it wbChartsheet already, but yes there is no need to export this. Similar to wbWorksheet there is no use for a user

[JanMarvin]

I've updated the manual to include this, but I'm going to bed now, almost 11pm in Düsseldorf.

[olivroy]

Thanks for the example. I will look more closely.

What I don't like is this:

df <- data.frame(
  x = 1:3,
  y = 1:3,
  z = paste(paste0("A", 1:3 + 1L), paste0("B", 1:3 + 1L), sep = " + "),
  z2 = sprintf("ADDRESS(1,%s)", 1:3),
  stringsAsFactors = FALSE
)

class(df$z) <- c(class(df$z), "formula")
class(df$z2) <- c(class(df$z2), "formula")

wb$add_worksheet("Sheet 2")
wb$add_data(sheet = 2, x = df)

But I will try to see if I can reproduce things with your examples.

[olivroy]

Other items that popped in my mind.

• wb_add_style -> wb_set_style() since it operates on a wb?
• wb_remove_worksheet -> wb_remove_sheet (can remove chartsheet and worksheets)
• styles_on_sheet -> wb_get_sheet_styles
• Deprecate x in wb_get_named_regions(), make it a wb wrapper

I have no really strong feelings about them. But I can open PRs if any of these suggestions sound reasonable to you,

[JanMarvin]

Just for reference: wb_add_style() is working very much as intended. It's part of the style handling in openxlsx2 (see the bare metal approach in the styles vignette). Using this you are able to tweak basically every little style piece you want. It's a bit unfortunate that I came up with the named styles so late in development, but the Excel developers have the same issue (how the switched comment to note just to add a new comment ...).

[olivroy]

Oh, I am not worried about the functionalities here. (my concerns with functionalities are above #754 (comment)) These are just function names that confused me at one point or another, hence the suggestions here.

[olivroy]

Could you regenerate this table whenever you have time? #38 (comment)

I'd be curious to see it ! And what is the code you use to create that?

[JanMarvin]

Sure, I don't have the actual code snippet around anymore, but I used something like this (with kableExtra for the markdown table):

library(openxlsx2)
funs <- ls("package:openxlsx2")
funs <- funs[!funs %in% c("wbChartSheet", "wbWorkbook")]

arggs <- NULL
for (fun in funs) {
  arggs <- unique(c(arggs, names(formals(fun))))
}

mm <- matrix("", nrow = length(funs), ncol = length(arggs), dimnames = list(funs, arggs))
mm <- as.data.frame(mm)

for (fun in funs) {
  mm[rownames(mm) == fun, names(formals(fun))] <- "X"
}

if (interactive()) View(mm)

I had another version that was working on subsets (like the function families).

[olivroy]

Closing this! long live openxlsx2. Hope I was not too annoying

[JanMarvin]

I can live with this kind of annoying. Annoyingly trying to help openxlsx2 to get better :)