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.

• Swate
  • Features
    • Swate Features sorted by Excel Versions
  • Install/Use
    • Excel desktop
    • Using a shared folder as system admin
    • Excel online
  • Discuss/Get help
    • Documentation
      • Feature documentation
      • Guides
    • Report problems and request features
    • (Beta) Discuss in the GitHub Discussion forum
  • Develop
    • Contribute
    • Prerequisites
    • Use install.cmd
    • Set up Sql Dump
    • Project Decription

Features

The base features of Swate are:

• Gradual workflow annotation table building via the basic annotation building blocks
• No-fuss ontology term search:
  • search for ontology terms quickly via autocomplete
  • use is-a relational term search (e.g. search all terms that are also a 'plant organ') to constrain your annotation column to a subset of ontologies
  • use Advanced term search with multiple query parameters if quick search does not do it for you

There are many more small and incremental features. Check the latest release notes for the newest additions.

Swate Features sorted by Excel Versions

Feature	Excel 365/ Office online	Excel 2019
Core
Adding and managing building blocks	✓	✓
Ontology Term search and insert	✓	✓
Ontology Term update	✓	✓
Add Templates	✓	✓
Copy filenames into table	✓	✓
Display DAG	✓	x
Experts
Export Swate information	✓	x
Create Template Metadata	✓	x
Write Checklist Custom Xml	✓	✓
Edit Custom Xml	✓	✓

Install/Use

You can test swate by following the steps below. If you decide to do so, please take the time to set up a Github account to report your issues and suggestions here. You can also search existing issues for solutions for your questions and/or discussions about your suggestions.

Excel desktop

If you have an older Swate version installed already, it might be necessary to clear the cache to apply the changes to Swate.

• Download the Windows Installer.
• Close all Office instances (Excel, Powerpoint, Word..)
• Unzip (Right click ➞ Extract All..)
• Double click the install.cmd. You will be asked to give administrative permissions and in the end to allow changes to the registry. Allow both and you are good to go!
• Open Excel, go to Insert ➞ My Add-ins ➞ Shared Folder. There you should see both Swate and Swate.Experts.

Alternative | Using the previous Swate installer

[Swate installer](https://github.com/omaus/Swate_Install#swate-installer)

Alternative | Using the release archive

#### Using the release archive

- Install [node.js LTS](https://nodejs.org/en/) (needed for office addin related tooling)
- Download the [latest test release archive](https://github.com/nfdi4plants/Swate/releases) and extract it
- Execute the test.cmd (windows, as administrator) or test.sh (macOS, you will need to make it executable via chmod 
a+x) script.

Using a shared folder as system admin

If you have administrative access in your organization, you can create a network share folder and follow this guide to make the addin available without any further downloads from your users

Excel online

• Download the latest test release archive and extract it
• Launch Excel Online (this requires a Microsoft account), open a (blank) workbook
• Under the Insert tab, select Add-Ins
• Go to Manage my Add-Ins and select Upload my Add-In
• select and upload the manifest.xml file contained in the test release archive.

Discuss/Get help

Documentation

Feature documentation

Documentation of the features is now a top priority as we completed the POC stage 🚀. You can discuss needed docs and the available ones here

Guides

Guides will be coming in the near future.

Report problems and request features

Please use Github issues to track problems, feature requests, questions, and discussions. Additionally, you can get in touch with us on Twitter

(Beta) Discuss in the GitHub Discussion forum

GitHub recently added forum-like features to repositories, which makes it possible to discuss and share things you do with swate without creating issues. You are very welcome to post content in the Discussions tab, but please use issues for bug reports/feature requests (or, if some of those result from a discussion, create a new issue referencing the discussion)

Develop

Contribute

Starting at Prerequisites we will explain the set up, but please read the following first. Before you contribute to the project remember to return all placeholders to your project:

• webpack.config.js

  server: {
          type: 'https',
          options: {
              key: "{USERFOLDER}/.office-addin-dev-certs/localhost.key",
              cert: "{USERFOLDER}/.office-addin-dev-certs/localhost.crt",
              ca: "{USERFOLDER}/.office-addin-dev-certs/ca.crt"
          },
      },
  

• .db/docker-compose.yml

  MYSQL_ROOT_PASSWORD: {PASSWORD}
  

• Server/dev.json

  "Swate:ConnectionString": "server=127.0.0.1;user id=root;password={PASSWORD}; port=42333;database=SwateDB;allowuservariables=True;persistsecurityinfo=True"
  

Prerequisites

• .NET Core SDK at of at least the version in the global.json file

• Docker with Docker-compose

• Node.js with npm/npx

• To setup all dev dependencies, you can run the following commands or the install.cmd file (explained further below). The first run will take some time to import the database from the .sql file:

  dotnet tool restore (to restore local dotnet tools)

  dotnet fake build -t setup pw:example, which will take care of installing necessary certificates and loopback exempts for your browser. Here are the steps if you want to execute them by yourself:

  • 'pw:example' is an optional parameter for the setup target to use a custom password for the local MySql docker instance. If this argument is not passed the instance will be created with the password 'example'.

  • connections from excel to localhost need to be via https, so you need a certificate and trust it. office-addin-dev-certs does that for you.

    you can also use the fake build target for certificate creation and installation by using dotnet fake build -t createdevcerts in the project root (after restoring tools via dotnet tool restore).

    This will use office-addin-dev-certs to create the necessary certificates, and open the installation dialogue for you:

    

    installing this ca certificate under your trusted root certification authorities will enable you to use https via localhost.

    The paths to these certificates are then added to your webpack.config file.

  • You may need a loopback exemption for Edge/IE (whatever is run in your excel version):

    CheckNetIsolation LoopbackExempt -a -n="microsoft.win32webviewhost_cw5n1h2txyewy"

Use install.cmd

The install.cmd executes several console commands for one of which it needs adminstratorial rights (dotnet fake build -t setup) to install the certificate mentioned above. Open powershell as adminstrator and navigate to the Swate-folder cd your\folder\path\Swate then use .\install.cmd to initialize the setup. While running a installation dialogue for the certificate will open and can be handled as described above.

By installing this repo via the install.cmd file the MySql password will default to 'example'.

Set up Sql Dump

The database became too large for GitHub to upload the dump directly. Therefore we now provide the basic Database structure as .sql dump file in this repo, including the data for ontologies and all template related tables. If you wish to participate in development open an issue and we can figure out how to share a ready-to-use database dump.

Project Decription

This project uses the SAFE Stack to create a website that uses office.js to interop with Excel.

The utilized Fable bindings for office-js can be found in office-fable.

To debug the AddIn locally, use the build target officedebug:

dotnet run officedebug

this will launch an Excel instance with the AddIn sideloaded, while also running docker with a MySql- and a Adminer instance. The MySql user/password will be root/example and can be set in .db/docker-compose.yml. Adminer can be accessed at localhost:8085, MySql at localhost:42333, and the app runs at localhost:3000 for client and localhost:8080 for the server.

To debug add-in instances you can now easily open debug console/inspect for newer Excel versions. For Excel 2019 and earlier you can use the create a .cmd file with C:\Windows\SysWOW64\F12\IEChooser.exe to easily open the f12 tools (on windows). Alternatively use office online:

• open Excel online in your favorite browser
• create a blank new workbook (or use any workbook, but be warned that you can't undo stuff done via Office.js)
• Go to Insert > Office-Add-Ins and upload the manifest.xml file contained in this repo
• You will now have the full debug experience in your browser dev tools.

Alternatively, you can debug all functionality that does not use Excel Interop in your normal browser (the app runs on port 3000 via https)