#1110 closed task (fixed)

Support for Excel files as an alternative to tab-separated files

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
Priority: major Milestone: Reggie v4.22
Component: net.sf.basedb.reggie Keywords:
Cc:

Description

There are indications that software and hardware that we are interacting with via tab-separated file will have support for Excel files in the future. In some cases they may even lack support for tab-separated files.

It would be a good time to start testing if we can work with Excel files. In the first stage:

  • Sample names for the Qubit step in the library preparation. There is both an export step and an import step.
  • Label files for the printer software

As for an API to use, the Apache POI project seems to be doing what we need: https://poi.apache.org/

Change History (19)

comment:1 Changed 19 months ago by Nicklas Nordborg

In 5296:

References #1110: Support for Excel files as an alternative to tab-separated files

Added JAR files from the Apache POI project. The commons-compress-1.18.jar and xmlbeans-3.0.1.jar are dependencies that we have found so far while trying to generate a simple HelloWorld-like spreadsheet.

comment:2 Changed 19 months ago by Nicklas Nordborg

In 5297:

References #1110: Support for Excel files as an alternative to tab-separated files

Implemented an xlsx version of the Qubit sample name export. A simple helper class XlsxTableWriter was implemented that acts as a wrapper for the TableWriter (from BASE) that is used to create CSV files. With this helper class it is possible to let the same code export either a CSV file or XLSX file as long as the TableWriter.tablePrintData() method is used.

The helper class is currently limited to string values, but it should not be difficult to support also other values types if required.

comment:3 Changed 19 months ago by Nicklas Nordborg

In 5298:

References #1110: Support for Excel files as an alternative to tab-separated files

Added file format option to the regular plug-in api dialogs for the QubitSampleNameExporter.

Added styling to comments and the header line in the generated xlsx file.

comment:4 Changed 19 months ago by Nicklas Nordborg

In 5299:

References #1110: Support for Excel files as an alternative to tab-separated files

The partition wizard can now create the label file in Excel format.

comment:5 Changed 19 months ago by Nicklas Nordborg

In 5300:

References #1110: Support for Excel files as an alternative to tab-separated files

Prepared the Library registration wizard with support for Excel files. We may have to rename the QUBIT_CSV subtype to something else. I tried defining a new subtype (QUBIT_XLSX), but when there is more than one to select from BASE will not automatically assign a subtype to uploaded files. This causes the filter to not display the file after upload which is very annoying. Unless this can be solved at the BASE level, we need to use a single subtype for both CSV and XLSX files.

comment:6 Changed 19 months ago by Nicklas Nordborg

In 5301:

References #1110: Support for Excel files as an alternative to tab-separated files

The Library registration wizard should now support Qubit files in Excel format. In order to not complicate the importer code, the XlsxToCxsUtil class was implemented. It can read an Excel file and generate CSV as output (per worksheet), which is then fed to the already existing importer code.

Last edited 19 months ago by Nicklas Nordborg (previous) (diff)

comment:7 Changed 19 months ago by Nicklas Nordborg

In 5303:

References #1110: Support for Excel files as an alternative to tab-separated files

Make sure that opened stream while reading Qubit and Caliper files closed.

comment:8 Changed 19 months ago by Nicklas Nordborg

In 5304:

References #1110: Support for Excel files as an alternative to tab-separated files

The "Lab tracking protocol for Allprep isolation" wizard and the "DNA/RNA/FlowThrough registration" can now also generate label files in Excel format.

comment:9 Changed 19 months ago by Nicklas Nordborg

In 5305:

References #1110: Support for Excel files as an alternative to tab-separated files

Added support for evaluating formulas when converting Excel file to CSV.

comment:10 Changed 19 months ago by Nicklas Nordborg

In 5306:

References #1110: Support for Excel files as an alternative to tab-separated files

The Excel worksheet should be named ReExtractionLabels when creating labels for re-extraction from lysate.

comment:11 Changed 19 months ago by Nicklas Nordborg

In 5307:

References #1110: Support for Excel files as an alternative to tab-separated files

Do not evaluate formulas by default since we do not have any use case that requires it yet.

comment:12 Changed 19 months ago by Nicklas Nordborg

In 5308:

References #1110: Support for Excel files as an alternative to tab-separated files

The "Storage box creation wizard" now has support for Excel when downloading labels.

comment:13 Changed 19 months ago by Nicklas Nordborg

In 5309:

References #1110: Support for Excel files as an alternative to tab-separated files

The "Download HE glass labels wizard" now has support for Excel when downloading labels.

comment:14 Changed 19 months ago by Nicklas Nordborg

In 5310:

References #1110: Support for Excel files as an alternative to tab-separated files

The "Protocols and labels for sample outtakes wizard" now has support for Excel when downloading labels.

comment:15 Changed 19 months ago by Nicklas Nordborg

Status: newaccepted

comment:16 Changed 19 months ago by Nicklas Nordborg

In 5316:

References #1110: Support for Excel files as an alternative to tab-separated files

Added support for setting specific number and date formatter implementations to make sure that values come out in a predictable format. Without this we get a mix of the formatting used in the original document and locale-specific changes that can happen if the document was created with a different default locale than the server is using.

comment:17 Changed 19 months ago by Nicklas Nordborg

In 5317:

References #1110: Support for Excel files as an alternative to tab-separated files

Adding DoubleFormatter. It should have been included in [5316].

comment:18 Changed 18 months ago by Nicklas Nordborg

In 5338:

References #1110: Support for Excel files as an alternative to tab-separated files

Fixed an issue that caused JSON data to be appended to the output file when downloading labels. It was simply ignored when creating an Excel file, but the CSV file had an extra line at the end: {"status":"ok"}

comment:19 Changed 18 months ago by Nicklas Nordborg

Resolution: fixed
Status: acceptedclosed
Note: See TracTickets for help on using tickets.