[ANN] ExcelUtilities.jl

Package Announcements
,
1

In my line of work, I’m in and out of Excel a lot and wanted to be able to go back and forth between Excel and Julia. So ExcelUtilities.jl enables you to copy and paste to and from Excel:

image

I spiked this out of it’s original home, ActuaryUtilities.jl, which is why the gif shows that package name (same functionality though). Folks on slack thought I should make this a standalone package.

Under the hood, it uses CSV.jl to parse the input as-if it were a tab delimited source. This is nice, because it will use the same type-detecting wizardry that CSV uses.

Some limitations:

  • The only thing that seems to be available on the clipboard is the plain text, so sometimes you will get result that you don’t want (e.g. 1,000,000 or (1234.32) will likely be parsed as strings).
  • Package is not yet registered.
  • Working through doc build key issues

See also:

Questions for the community:

  • Would ExcelUtilites or InteractiveExcelUtilities be a better package name?
  • Is there other quality-of-life functionality that you’d like to see when working with Excel?
  • Sugestions on dealing with formatted numbers like (1234.32)?
25 Likes
2

Thanks for doing this; I’m quite a fan of this functionality as I often need it but have to resort to more cumbersome approaches. I just tried it out, and it seems that maybe you aren’t exporting xlclip or importing InteractiveUtils? At least, I got error messages along those lines; it’s possible I’m missing something.

In terms of other features one might want, I suppose the most natural next step would be the ability to move rectangular chunks (rather than just rows or columns) back and forth between Julia and the spreadsheet.

3

Thanks for the feedback, these should be corrected now.

This is implemented already. I need to figure out my Documenter build as this is described in the docstring :confused:

image
image838×410 43.9 KB

3 Likes
4

I’m not an Excel user, but I’m wondering - would this work with cut/paste from LibreOffice and maybe plain CSV, too? Or maybe from GoogleDocs and similar? That would come in quite handy.

4 Likes
5

That’s great; I think I misinterpreted some documentation I came across in ActuarialUtilities.

@oschulz In my experience, these kinds of things do work pretty consistently across spreadsheet apps. In particular, I checked that xlclip does work both ways (at least on a basic example) in Google Sheets.

1 Like
6

Awesome that this is possible! I’d also be interested if you could get it to work with just plain CSV copies as well.

1 Like
7

Wow, very nice, just tested this with Google Docs, it just worked.

Very cool, @Alec_Loudenback ! How about calling this SpreadsheetUtilities.jl or so, if it’s not limited to Excel?

2 Likes
8

Hi everyone,

@pdeffebach has taken the idea here to another level with ClipData.jl.

I recommend using that package going forward:

  • cliparray does the same things xlclip used to
  • cliptable is similar idea, but will automatically parse headers and provide a Tables.jl compatible object

In addition, ClipData:

  • will pass kwargs given to the underling CSV.jl parser, so you can do really nice things like normalizenames for the column names you’ve got on your clipboard
  • mwe version of the above functions which facilitate scripting data in/out.

cliparray:

cliptable:

In the meantime, consider ExcelUtilities.jl in archive mode unless other folks have ideas on actual spreadsheet focused utilities.

6 Likes
9

I should also note that ClipData.jl has been submitted to the registry and should be add-able in the next day or two.

10

I want to plug the mwetable and mwearray features a bit more, since I think they will be particularly helpful with Discourse.

If you have a dataframe imported from a CSV file and need help working with it. mwetable makes it super easy to copy and paste the data into the session.

julia> using DataFrames, ClipData, CSV

julia> mytable = DataFrame(name = ["Joe", "Charlie", "Miranda"], id = [1123, 3242, 8392])
3×2 DataFrame
 Row │ name     id    
     │ String   Int64 
─────┼────────────────
   1 │ Joe       1123
   2 │ Charlie   3242
   3 │ Miranda   8392

julia> @mwetable mytable
mytable = """
name,id
Joe,1123
Charlie,3242
Miranda,8392
""" |> IOBuffer |> CSV.File

julia> mytable = """
       name,id
       Joe,1123
       Charlie,3242
       Miranda,8392
       """ |> IOBuffer |> CSV.File |> DataFrame
3×2 DataFrame
 Row │ name     id    
     │ String   Int64 
─────┼────────────────
   1 │ Joe       1123
   2 │ Charlie   3242
   3 │ Miranda   8392

NOTE: Please, please, please only do this for very small tables. It would be very annoying if people used this feature to paste 100s of lines of data on here. I’ve been wondering if we should show a warning if the data is too big to discourage this.

Though I don’t use Excel a lot myself. I imagine that there are lots of people out there who have an existing excel workbook and are slowly transitioning the workbook to a script. The mwetable() function takes something from the clipboard and makes it easy to construct a tabular object.

A major benefit of excel is that users can see their data in the same place where it’s being analyzed. Hopefully mwetable() makes it easier to do that in a script (again, for small datasets only).

11

Update: ClipData.jl has been registered! Now you can do

] add ClipData

Again docs are here.

3 Likes