Generating an MS Excel Sample Submission Spreadsheet

An easy way to get sample data into BaseSpace Clarity LIMS is to import a Microsoft® Excel® sample spreadsheet. However, manually configuring the spreadsheet can be time-consuming and error-prone.

Use this Python application example to generate a custom Excel sample submission spreadsheet that meets the specific needs of your lab.

Solution

Parameters

The script accepts the following parameters:

-a

The base URL to the REST API - no trailing slash (Required)

The URL that points to your main API endpoint

-u

The username of the admin user (Required)

The {username} token

-p

The password of the admin user (Required)

The {password} token

The SampleSubmissionXLSGenerator.py application script is run as follows:

python SampleSubmissionXLSGenerator.py -a http://<hostname or IP address of server>:<port>/api/v2 -u admin_user -p admin_user_pass

An XLS workbook containing the following worksheets is generated:

Sample Import Sheet: Contains columns for all Sample UDFs/UDTs.
Container Type Names: Contains a list of all container type names.

User interaction

In the Clarity LIMS Operations Interface, the lab manager configures Container Types and Sample UDFs/UDTs.
In the Clarity LIMS Web Interface, the lab manager or lab scientist runs the SampleSubmissionXLSGenerator.py application script, providing the required parameters.
An Excel workbook containing the Sample Import Sheet and Container Type Names worksheets is generated.
The Sample Import Sheet is provided to lab scientists for use when creating lists of samples to import; it may also be used by collaborators to import samples via the LabLink Collaborations Interface.

Using the generated spreadsheet

The spreadsheet will contain red and green column headers. Populate the spreadsheet with sample data:
- Red headers: These columns must contain data.
- Green headers: These columns may be left empty.
Import the spreadsheet into Clarity LIMS.

If there are no sample user-defined fields (UDFs) or user-defined types (UDTs) in the system, the generated spreadsheet will only contain four columns. After configuring the UDFs/UDTs, you can re-run the script to add columns to the spreadsheet that reflect the updated configuration.

Example modifications

You can edit the Python application to include supplementary functions. For example, you may want to use other attributes from the resulting XML to generate additional data entry columns.

Inserting additional non-required & non-configured columns

The SampleSubmissionXLSGenerator.py Python application script adds Sample/Volume and Sample/Concentration columns to the spreadsheet.
The script includes a 'commented' section. You can remove the ### NON-REQUIRED COLUMN MODIFICATION ### comments and use this section to add your own columns.

Assumptions & notes

You have downloaded the attached zip file to the server. On a non-production server use the glsai user account.
Unzip the file to the following directory: /opt/glsclarity/customextensions. The contents of the zip file will be installed within that directory, to /CookBook/SpreadSheetGenerator/
Python 2.7 is installed and configured on the system path.
You can run the SampleSubmissionXLSGenerator.py Python application script on any system running Python, provided it has web access to the Clarity LIMS REST API.
This script is not compatible with the 3.x branch of Python.
The example code is provided for illustrative purposes only. It does not contain sufficient exception handling for use 'as is' in a production environment.

All dependencies are preloaded if you install on a non-production server.

Attachments

python-xls-generator-2.0-bundle.zip:

309KB

python-xls-generator-2.0-bundle.zip