How to Upload and Read an Excel file into Displayr
Displayr has in-built support for reading many file types, including Excel files. There are three different ways to import Excel files. In this post I'll show you all three methods and explain when you'll want to pick each method and its advantages.
The first method is the easiest. But if you want to upload an Excel file containing more than a single table or use more advanced features such as automatic updating - you'll need to know how to use the second and third method as well.
Method 1: Add Dataset from your local computer
All three methods begin by selecting, from the top ribbon, Insert > Data Set which will bring up the dialog box below showing all the options for importing data:
The simplest way is click on My Computer. This brings up the familiar Open File dialog box, allowing you to select an Excel file on your computer. This will upload all the data in the first sheet of the Excel file. You will want to remove any comments or non-tabular data.
Any changes that you make to the Excel file on your computer will not be reflected in the Displayr document unless you manually update the data set. (To do this, click on the data set you want to update, and then click on the Update button in the object inspector on the right of the screen).
Method 2: Add Dataset from URL
If your Excel data file will be updated regularly, it'll be better to give Displayr a URL to your data file. Note that you can adjust how frequently the file is refreshed.
This method can handle direct links (e.g., the Excel file is hosted on a webpage), but it may have difficulty downloading files from URLs to cloud storage. For example, if you have a link to a file in Dropbox, it will generally look like
To make this link direct, you need to replace dl=0 with dl=1. For other cloud storage services, such as One Drive or Google Drive, it may be easier to use Method 3.
Method 3: Add Dataset from R
This approach requires a small amount of R coding, but it is more flexible than the two methods above.
Select R from the Where is your data? dialog box, which brings up a new box where you can type the following code:
library(flipAPI) message("R output expires in 3600") DownloadXLSX(myurl)
The second line tells Displayr that this R code should be re-run if it is older than 3600 seconds (i.e., an hour). You can change the periodicity as long as it is greater than 600 seconds.
The importing of the Excel file is performed by the DownloadXLSX function (line 3). The only parameter that is always required in the URL. But for extra flexibility, you may want to try other parameters to DownloadXLSX. For example, you can replace the third line above with
DownloadXLSX("https://wiki.q-researchsoftware.com/images/b/b9/Cola_Discriminant_Functions.xlsx", want.col.names = TRUE, want.row.names = FALSE, sheet=2, range="AB2:AC330")
The additional parameters indicate that the input table contains column names but no row names, and that only cells AB2:AC330 from the second sheet should be imported. You do not need to reformat your Excel file for data importing.
Once you have imported the data set, you can check its properties by clicking on it and looking at the object inspector on the right of the screen. For example, you can check the last time it was updated (in UTC). Changes can also be made to the R code that generated the data set by clicking on the Update button.