Working with spreadsheets (MSO Excel, LO Calc) in UniChrom way.

In vast cases application of analytical instruments ends up with the final calculation and reporting into spreadsheet application (99% it is MS Excel).

Spreadsheets do the result verification and additional calculations which probably impossible or hard-to-understand with built-in analytical software functionality.

Exporting to the spreadsheet is done in obvious straight-through process:

Analytical Data → [Optional Excel Template processing] → Final report in Excel workbook → Additional manual work (or with the help of macro-programming) to get the final result.

Data is passed to the Excel in following manner:

1. Compose Excel file offline by binary/text editing, then open composed file in Excel
2. Using OLE Automation (subset of MS COM) create the workbook object and fill it with resultant data

Both variants imply hard-coded or somewhat customizable template or selector for desired report sections.

The main idea in UniChrom reporting is more human-oriented. The analyst knows better – what report he/she needs. So place formulas, tables, pivot data, averages as it is required to get the things done.

Only one step remains – feed the formulae with analytical data.

Solution is obvious – provide a function which return required data into desired cell.

Spreadsheet will always contain live data. There is only one drawback – data supplier (UniChrom) should be running.

How the things get done.

UniChrom provides OLE Automation COM interface (similar to one of Excel). Functions implemented in macro-module of the workbook provide access to UniChrom internals and analytical data.

Functions library

Having macro module accompanied every Excel workbook in some environments is not very good idea, as the policy may require every macro virus checking. Each workbook would be scanned on opening which may lead to serious performance impact.
Moving UniChrom-related functions to single library (Excel Add-In) provide single point of control (and possible one-step upgrade) for every workbook using such approach. Virus checking is done once Excel starts.

One good consequence – analyst can start new report template or complex calculator from clean empty workbook. The only dependence this workbook will have – is the mentioned above library – unichrom.xla

• unichrom.xla - MS Office Excel Add-In for OLE Automation interface. Just download and install.

RPC - remote procedure call

UniChrom for Linux lives in different world, where is no vendor-locked COM technology, where open standards require more common solutions.

The software extension (service) UniChrom-UCWEB runs tiny web-server inside UniChrom process which responds to JSON-RPC requests wrapped around UniChrom SPECAPI.
SPECAPI – is the Spectral API which have nearly the same functionality as mentioned above COM interface but is cross-platform.

Initially SPECAPI was the interface UniChrom provides for each file converter (import/export filter). The interface allows programmatically manipulate most UniChrom internal objects. Each converter uses SPECAPI calls to read the known file format and represent it into UniChrom data structure and vice-versa - represent UniChrom data structure into known data format.

With such approach – every reporting spreadsheet application capable of calling SPECAPI – in that case not platform- and vendor-locked can be simply web client.

• ucweb - service module providing JSON-RPC interface to UniChrom SPECAPI

IPC - inter-process communication

Obviously the basic OS primitives, such as files working nearly identically from programming language point of view. Both systems have such entities as bidirectional channels (under linux there are UNIX sockets, under Windows - named pipes). This allows also executing JSON-requests in local process interaction.
So the identical in form JSON-requests can be transferred either atop TCP socket (JSON-RPC), or atop system channels (JSON-IPC).
The JSON-IPC handing is built into UniChrom. By default IPC handler is turned off and can be enabled either running:

• ./uc --ipc
  single process IPC handler
• ./uc --multirun
  every document runs in separate process

IPC / RPC in single library

Interface selection (RPC/IPC) is made on spreadsheet calling function:

• =setucpid(number) - where number is the PID of UniChrom process
• =setucweb("http://localhost:7777") - URL of JSON-RPC server (ucweb extension running)

All the spreadsheet application details are packed into single library:

• unichromw.xla – under MS Office for Windows.
• unichromw.oxt – under LibreOffice for every platform

Both libraries provide identical functional interface and the same user experience for all the platforms. As the example – consider alcohol intoxication analysis template implemented both in LibreOffice and MS Office with identical functionality.

Extension in Action

Publication-grade graphics

From the early years UniChrom provide vector graphics for signal graphs (Edit/Copy when active page is the Graph). COM interface made the vector graphics available through clipboard for reporting applications.

Recent changes of MS Office and LibreOffice lead to surprising consequences.

SVG (scalable vector graphics)

Cross-platform open standard supported by vector-editing graphic packages and internet browsers.

MS Office periodically adds then removes support for open-standard SVG. At the moment of document creation SVG was not officially supported in MS Office (except online version Office-365).

LibreOffice was supporting SVG from early days.

EMF (enhanced metafile)

Windows specific files which in fact is the sequence of Windows GDI drawing commands recorded. EMF is windows specific and requires separate libraries for other platforms.

At the moment of this document creation MS Office was officially supporting only one vector graphic format – EMF

LibreOffice supporting EMF under all platforms, the problem is in generation that kind of files under non-Windows.

The UniChrom SPECAPI was expanded to provide vector graphic via special function call.

function GetPictureStream(

ASpec:HSpec;AStreamHandle:THandle;
atype,alay,mmWidth,mmHeight:integer;
sx,ex,sy,ey:single

Function returns platform-dependent vector graphic (EMF for Windows and SVG for other platforms),

atype – 0 – signal graphic, 1 – calibration C=F(R) , 2 – calibration R=F(C)

alay – number of layer or number of calibration

mmXXXX – graphic size in millimeters

sx,ex,sy,ey – X and Y-scale bounds desired. Specifying 0,0,0,0 gets currently (last) visible on-screen bounds.

(MS|Libre) Office function in library wraps the function to provide graphic insertion in specified cell and returns insertion status.