nfdi4plants / swate

Excel Add-In for annotation of experimental data and computational workflows.

Swate

Swate - something or someone that gets you absolutely joyed (Urban dictionary)

Swate is a Swate workflow annotation tool for excel.

Swate aims to provide a low-friction workflow annotation experience that makes the usage of controlled vocabularies (ontologies) as easy and intuitive as possible. It is designed to integrate in the familiar spreadsheet environment that is the center of a great deal of data-focused wetlab work.

Table of contents

• Docs
• Install/Use
• Contact

Docs

• Ontology term search
• ISA conform output
• Minimal information standards

Try our quickstart or a more in-depth walkthrough.

For a full in-depth view of all Swate features check the documentation.

Install/Use

Swate installation

Contact

If you have any issues using Swate, missing features or found a nasty bug 🐛 you can always contact us via:

• GitHub Issues
• DataPLANT Helpdesk

Dev

These instructions are only relevant if you too want to participate in developing Swate!

Requirements

• .NET SDK, >= 8.0.0
  • verify with dotnet --version
• nodejs, >=18
  • verify with node --version
• npm, >=9
  • likely part of nodejs installation
  • verify with npm --version
• docker, >= 24
  • verify with docker --version
  • this is required for database setup

Setup

• clone this repo
• Run dotnet tool restore
• Run npm install

Development

Start

./build.cmd run, to start up Swate (+ Database network)

Swate runs on localhost:8080 (and swobup on localhost:8000).

Available commands

Usage: ./build.cmd <command>

run (--nodb)                        Start .net backend server, vite frontend (and database, 
                                    swobup with docker if not `--nodb`)

release (pre)                       Run .net tests tag current branch and force push to 
                                    release branch (nightly if `pre`), this will trigger
                                    Github release with docker image

bundle                              Create distributable, used in docker image creation.

docker  
    create                          Create new swate:new image
                
    test                            Start test instance of docker compose network from swate:new image

version
    create-file <version>           Create new `src/Server/Version.fs` with `<version>`.

swate's Issues

Add Generator form for high level metadata

While current abstractions should work well for study and assay parts of the ISA model, the investigation part which contains metadata about authors, publications etc. associated with the study is not supported currently. Using excel for that seems impractical though, a better alternative may be a full screen form modal inside of excel triggered by the web app, which then from the input generates a new tab and/or file containing this metadata.

Add Unit Column when selecting a unit for a term

Currently, units are only saved in the cell format of the respective column. To make this more ISA conform, the annotation of a column with a unit requires the Unit column to spawn, with associated Term source columns

[PR] Add is_a directed search functionality to term search

This issue is related to the PR #34. The following notes are copies form the PR.

Features added:

• Add is_a directed search functionality to term search.
• Restored bulma imports in style sheets for bulma-extensions switch and tooltip.
• change addBuildingBlock function to add 3 columns with two cols hidden. These extra columns will hold additional ontology term info in the future
• add new sql dump with is_a directed search procedure and is_a relationships in TermRelationship table

Do not merge until:

• is_a directed search returns all children and not only direct children of is_a relationships.
• insert term function also inserts the additional info in the new extra columns of addBuildingBlock.

As mentioned above there are still major features of this PR missing, so do not merge it yet.

Check for usable API sets depending on function used

Many functions in the Office.js API need a minimum version of Excel. when using more recent APIs, it is necessary to catch the case where the user has an old excel version so that the function call doesn't fail. An example can be seen here, where the auto-resize of columns is API set specific.

Create Template Sheets for common workflows or types of datasets

• open dialogue box on first interaction
• ask the user if they want to duplicate the current sheet (if present)
• functionality to duplicate sheet

Add "unique identifier" for column names

As it is, we cannot add a column with the name of an already existing column. To circumvent this we need a unique identifier.

I first thought of actual Guids but the result bloated the column width too much to allow for effortless working on the table.

Example:
Parameter [instrument model; #75e44bc6-f5b3-4bbc-874b-1fd76a78129f]

So i went with the approach to test the column headers for the new-would-be-name and if it exists i go one up for the id.
Example:

The logic behind this creates a name with id 0 (will not be shown) and if it finds this name in the table it will loop over the name creation with (previousId+1). To circumvent any problems with the hidden column names, it also checks for those.

Move OntologyDB to separate project eventually

The Database API does not have to be in the same project as the addin. For easier maintenance it could be beneficial to move it to a separate repo in the future.

[BUG] Checkboxes in ValidationView disappear.

How to reproduce:

1. Create table and add some building blocks
2. Go to ValidationView
3. Click on table column so you can see checkboxes.
4. Resize window so checkbox and slider are shown side by side.
5. Click around on the checkboxes. Some of them will disappear (visually).

[BUG] Excel addin triggers new model/page initialization on first navigation event

Clicking the taskpane button initializes the addin with an initial model. When changing the page (by clicking any navigation tab) the first time, a new initialization is triggered. This should not happen, and could be either due to the addin not using a common runtime or the taskpane buttons not pointing on the root page.

Make annotation column insert dependent on selected column in the table

related SO post

Register swate in the microsoft add-in store

Now that we host a stable beta version of swate, we can think about making the usage easier by publishing it to the microsoft add-in appsource. The steps are outlined here:

https://docs.microsoft.com/de-de/office/dev/store/submit-to-appsource-via-partner-center

Copy current worksheet when starting to annotate

Excel does not track an undo stack for operations done by addins, therefore it is beneficial if the user does not screw his complete sheet up when they make a mistake.

Use DataPLANT and Swate logos/socials

Logos:
- DataPLANT
- Swate

Socials:
- DataPLANT twitter

Set up multipaging

This application will do better when run as multipage application. It is then possible to expose different functionality for different ribbon and/or context menu items inside Excel. Things that need to be done:

• Create layout template common to all sites displayed in the ribbon
• Seperate views for pages
• Implement PageModel that gets updated on url change

take heavy inspiration from here : https://stackoverflow.com/questions/54970180/how-can-i-do-a-simple-elmish-router

Map unit ontology to custom cell formats

Autogenerate manifest.xml file

One could auto generate the manifest.xml file from an abstraction layer generated on the F# side. This would make adding pages that must correspond to ribbon buttons / context menu buttons easier.

Color code column types in annotation table

Color coding the columns (for example header fill) could make it possible to get rid of type prefixes (e.g. Parameter [x] -> x). This must be visual only, dont omit prefixes on export

Advanced search on all simple search fields as modal

Move advanced search from term search only to a modal that can be opened on any simple search.

• Autocomplete as generic component that can be used on any input field
• Advanced search as generic component that can be triggered from autocomplete components (e.g. 'Cant find what you are looking for? Use advanced search')
• Routing of advanced search results to the inputs where it was triggered from

[BUG] Page 1 button is shown twice if advanced search finds few search results

Describe the bug
If advanced search finds enough results to show them on one page, page number is shown twice.

To Reproduce
Steps to reproduce the behavior:

1. Go to TermSearch
2. Insert anything into the search field
3. Click on 'Use Advanced Search'
4. Click on 'Term name keywords'
5. Insert anything into the search field that leads to result few enough to display them on 1 page
6. See bug

Expected behavior
Only 1 page number (or none) should be displayed.

Screenshots

OS and framework information (please complete the following information):

• OS: Windows 10 Home 64-bit
• OS Version 10.0, Build 18363
• MS Excel: Excel Desktop
• MS Excel version 2008 (Build 13127.20760)

Add functionality to insert columns based on the annotation 'building blocks'

• Source column is always the first in a fresh annotation table
• Form for creation of all blocks:
  • Basic dropdown select
  • Additional information to help understand the blocks, keep in sync with these
  • Select: This block has a unit , see #16
• Handle insert into annotation table based on type

Data formatting and validation in the annotation table

Formatting rules can be generated based on the annotation building block type and optional unit after inserting the column into the annotation table

Validate Data in office.js:
https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-data-validation

Cell formatting:
https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges#set-range-format

Validation and formatting seems to work on a per-cell basis, and can be set in bulk for ranges. therefore, it would be best to create formatting/validation properties of the ranges that are inserted as building blocks

• Cell formatting for units
• Cell formatting for term selections, eg one of [Term1,Term2;Term3]
• [ ] Data Validation of the whole table (e.g. on export)
• [ ] Warning when term name does not fit REF/Accession number (happens when user changes term name while he cant change the read only hidden columns associated with it)

Change history tab to info tab

This tab should contain:

• a high level description of the tool's functionality
• links to both feature and API docs
• direct link to GitHub issues to discuss and report bugs
• social links to get in touch (add the bird icon and stuff)
• the log history should move to a auto-collapsed dropdown below all of this, associated with a hint that this is fo dev/bug report purposes.

Add a version information endpoint to the application

Both the API and the client application should contain version info. I imagine it like this:

• the major API version can be shown in the backend via routing: /IAnnotatorAPI/v1/YOUR/ROUTE/HERE
• The client should have either an info tab or footer () that displays the version of the deployed application (e.g. 0.1-beta atm), and this should be populated via a call to the backend.
• Bonus points if this gets automated via FAKE release notes versioning

Add Advanced Term search

Besides suggestion based quick search, the addin also needs the option for advanced search on Terms:

• Quick search for Ontology constraint (search in Ontology X only) using preload of all ontology ids on init
• Full text search on at least Name, Accession, and Definition columns
• Pagination on results as there can be quite a bit of them
• mustContain, startsWith and endsWith queries

Add documentation for featureset and API

Feature documentation should be linked via the info tab (see #46) and describe the functionality that is done in the frontend, e.g. the annotation functionality and term search. It is up for discussion whether to host this on gh-pages, the application itself, or use a wiki for that. API docs can be auto generated but must be populated via examples.

• Api documentation

• Feature documentation

Improve FilePicker functionality

Necessary features:

• Fill excel cells with file name(s)
• make file names from list selectable -> insert only selected
• fill column range with file names, extend range when file name list is larger than range

Edit: As of ..

Your approach filling only the selected range seems better to me.

This seems like an extra step that will not be used regulary. As we currently allow users to remove unwated file names it is not necessary to select those you want to insert.

Are files taken by the order they are selected, or by the ordering of the explorer (e.g. alphabetical)? If the latter is true, at least reordering of the filelist must be possible if you are against selections. But you are right, we can take a minimal approach at all of this, as reordering can also be done via excel ui

• Add option to reorder uploaded file names.

[BUG] create annotation table fails if whole Row(s) is/are selected

Describe the bug
If one selects one or several rows when creating the annotation table, the application fails.

To Reproduce
Steps to reproduce the behavior:

1. Select one or several rows (Click on the row names for this, e.g. row A to C)
2. Click on 'create annotation table'
3. See error

Expected behavior
Since creating a table into column eternity (1 048 576 columns) may not be a viable option, it would be good to have a fixed limit for this (e.g. 100 rows).

OS and framework information (please complete the following information):

• OS: Windows 10 Home 64-bit
• OS Version 10.0, Build 18363
• MS Excel: Excel Desktop
• MS Excel version 2008 (Build 13127.20760)

Additional context
This error seems to occur much more often if the system is under stress.

Add Unit (UO) picker

analogously to the term search component, but limited to unit ontology entries in the database.

• Parse and add UO to database
• Picker component

Change responsive design to use .scss file

Currently the responsive design is regulated by using the following scheme for switching between designs.

let mediaQuery = window.matchMedia("(min-width:575px)")
if mediaQuery.matches then
    str (pageLink |> Routing.Page.toString)
else
     pageLink |> Routing.Page.toIcon

This should be done via

@media only screen and (max-width: XXXpx) { 
   previousClass: {
       style
   }
 }

[BUG] Fable.Remoting api docs do not show examples with >5 parameters

Currently our api docs will not show the example for advanced term search. My tests showed that this happens with apis that have 5 or more parameters. I already opened an issue about this.

Add Quicksearch for ontology terms

The AddIn needs a basic autocomplete search on term names:

• Serverside ranking of search results using sorensen-dice
• Serverside prefiltering by like search
• UI components (input with dropdown showing suggestions)

Annotation table not recognized on addin initialization

Option to fill hidden cols according to main column

Is your feature request related to a problem? Please describe.
While working on Issue #48 a rising need to have an option to fill all hidden columns with the correct input. An average user would paste a term into a column (together with the info for the hidden cols) or even paste it into multiple rows at once. But if he then extends the rows and copy pastes the info in the new rows, the hidden cols would be empty.

Describe the solution you'd like
For now we should just implement a button to fill empty cells in hidden cols.

Describe alternatives you've considered
Optimally this would happen onChange. But currently we don't know how to allow excel to dispatch app messages.

Add File Picker component

Add a file picker react component to frontend that can be used to get file names (which later will be inserted into excel cells)

[BUG] Annotation table warning is not shown on new sheets

While it is now possible to use multiple annotation tables across sheets, The warning about a missing table is only displayed once, before creating the first table.

Use Regex pattern matching to correctly identify column header patterns

While working on the next few issues i noticed a growing need to work with the column headers in a more efficient way. After you (@kMutagene ) sent me this quote regarding fable and regex:

Regex will always behave as if passed RegexOptions.ECMAScript flag (e.g., no negative look-behind or named groups)

I gave it another try and found a working pattern, which will be implemented in the next commit on my feauture branch.

[BUG] In advanced search: Field 'select ontology' is missing in drop-down menu after selecting an ontology

Describe the bug
If using advanced search in TermSearch and selecting an ontology, it can not be switched back to 'select ontology', thus it is not possible to search for all ontologies without cancelling and restarting the advanced search again.

To Reproduce
Steps to reproduce the behavior:

1. Go to 'TermSearch'
2. Insert anything into the search field
3. Click on 'Use Advanced Search'
4. Click on 'select ontology'
5. Choose an ontology from the drop-down menu
6. Click the field again
7. In drop-down menu, no 'select ontology' can be found

Expected behavior
'select ontology' should reappear again.

Screenshots

'select ontology' field before selecting an ontology.

No 'select ontology' anymore after an ontology was selected.

OS and framework information (please complete the following information):

• OS: Windows 10 Home 64-bit
• OS Version 10.0, Build 18363
• MS Excel: Excel Desktop
• MS Excel version 2008 (Build 13127.20760)

Add Ribbon buttons and/or context menu buttons for the different application endpoints

The addin functionality is exposed via hash based urls for different functionality e.g. /#TermSearch for the termSearch component. All Endpoints need a respective UI element in Excel.

This would be much simpler when considering #8

Expand auto fills for terms to the reference annotations

When using the excel autofill features, the fill should expand to the annotation reference columns (e.g. TERM SOURCE REF)

[BUG] If search text field is active, textcursor is always set to the end of the text if a letter is added

Describe the bug
If search text field is active, the textcursor is always set to the end of the text if a letter is added.

To Reproduce
Steps to reproduce the behavior:

1. Go to 'TermSearch'
2. Click on the text field, insert a word, move the textcursor to the beginning of the word
3. Add a letter
4. Textcursor is moved to the end of the word

Expected behavior
Textcursor stays at the expected position (1 character further than before)

OS and framework information (please complete the following information):

• OS: [e.g. Windows 10]
• OS Version [e.g. 1809]
• MS Excel: [e.g. Excel Desktop, Excel Online]
• MS Excel version [only for Excel Desktop]

[PR] Update to SAFE stack v2.2 & Migrate to MySql #32

This issue is related to the pull request #32 .

The following list of points is copied from the pull request:

Update to only dotnetcore3.1 projects
simplify paket groups
[BUGFIX] Error with Fable solved by setting the version for Fable.SimpleJson to 3.11.0
Migrate database from MSSQL to MySql
-> Add docker-compose.yml file for MySql+Adminer instance
-> Add db.sql dump file to initialize Swate DB to MySql docker instance
-> Adjust fake.fsx to automatically start docker-compose with "OfficeDebug" target
Update README with information about docker.

In addition i though about automaticall adding the LocalConnectionString to the user secrets, but was unsure if this is wanted.

If you found room for improvement or want something changed, just tell me.

Add functionality to render activity log as tab separated file

project template for office addin development

When this project matured, one should create a project template to make this easier for anyone that may follow. I had a lot of roadblocks that could be prevented by such a template, so let this issue serve as a reminder that it should be done eventually.

Refactor model and Message types

• Refactor Model to contain isolated states for different parts of the app
• Refactor Messages as nested DU of Messages for different parts of the app instead of one single large top level DU

Handle Column groups

Columns and their term source references and/or associated other columns (such as units) have to be grouped or at least created as groups when inserting them.

Current plan:

• Create Term Source REF and Term Accession Number Columns for every annotation column
• Make these columns Hidden and read-only

Handle color theme dependent on excel theme

How properties of the current excel theme can be accessed:
https://docs.microsoft.com/en-us/javascript/api/office/office.officetheme?view=word-js-preview

[BUG] Not possible to create another annotation table in another worksheet

Describe the bug
If one annotation table is created, it is not possible to create another one in another worksheet.

To Reproduce
Steps to reproduce the behavior:

1. Open a new excel file
2. Open CSBAnnotator in Data tab
3. Click create annoation [sic] table
4. Create another worksheet
5. In CSBAnnotator tab, there is no option to create another annotation table. This does not change if the Add-In or the excel file is reloaded

Expected behavior
Create annotation table button should appear when switching to a worksheet without an annotation table.

Screenshots

New excel file without annotation table. It is possible to add a new one.

Annotation table created.

Not able to add another annotation table, even after reloading the Add-In.

OS and framework information:

• OS: Windows 10 Home
• OS Version: 10.0, Build 18363
• .Net core SDK version: 5.0.100

Additional context
2 things to notice:

1. getActiveWorksheet method/function seems to not work properly.
2. Tables are not assigned to the specific worksheets in which they are located. Instead, it is only their reference which is assigned to the worksheet.

[BUG] Not all api docs show their examples.

testOntologyInsert and getTermsForAdvancedSearch do not show their examples in the api docs.

Optionally restrict input for columns where the value has been set by an ontology term

Column headers that define the value via an ontology term should have their input optionally restricted to children/is-a relationships pointing to that term.

Example: creating the following parameter header : Parameter[MassSpectrometerInstrument(i made this term up)] should only allow valid mass spectrometers (meaning is-a relationships in that ontology).

Provide validation information via XML metadata

It is possible to set metadata of cells via office.js. Especially for users that create templates for others to use, information on the proposed cell contents is helpful. This would be set via a JSON string in the XML tag with the key "validationParams" and should have the following fields:

• importance - an integer between 0 and 100

• content type - one of : ontologyTerm, text, number, int, decimal, url, boolean

• if the content type is ontologyTerm, asdd the isA restriction if set: term[restriction]

• Create a JSON schema for this

• Create a form to generate this for a column

Edit: Office.js only supports custom tags, not custom attributes on existing tags. Therefore, we need to investigate alternative places to save this information. For now we can add the validation table to the app model and later use that data to generate the file/tags/etc we need.

Edit2(by @Freymaurer): As of the latest decision all swate validation data will now be saved in the excel custom xml, as desribed here.

• Add validation data to the app model.
• Swate can write validation data into excel custom xml.