Read and Write Excel

Introduction

By default, this plugin extracts data from the Results Table and adds it to a sheet-tab “A” in an .xlsx Excel file on the user’s desktop. The plugin will create a file named “Rename me after writing is done” on the desktop if none-exists already, and will update this file if it has already been created. Data is added to the latest sheet (created as “A” if the file is new) or to a user specified sheet using a passed argument (see below). Within the latest sheet, data will be added adjacent to previous data, unless an argument is passed to stack data underneath pre-existing column headers and data. Results Table column headers are added automatically. A row count is added by default but the user can choose to deny this with a passed argument.

Created in Eclipse IDE. Requires jre 1.8, which is current ImageJ compatible.

class=”nowrap” | Release version dates

Usage

Youtube tutorial:

</span> This plugin can be called from a macro using:

run("Read and Write Excel");

and it works very well with batching. A major change for v1.1.0, is that, if called slightly differently (see below), the plugin is now much better at handling large data sets and/or excel files that become large over time.

The title of the most recent open image is added as a label for exported results data by default, but this can be changed with passed arguments.

Optional arguments

Arguments will be parsed from the second argument string as follows.

“dataset_label=” The label to write in the cell above the data in the excel file. Strings and numbers can also be fed to the custom title modifier from a macro/loop:

run("Read and Write Excel", "dataset_label=[My new results data label]");
//example with passed variable 'i'
run("Read and Write Excel", "dataset_label=Table"+i);

“no_count_column” Prevents the plugin from adding a “Count” column automatically:

run("Read and Write Excel", "no_count_column");

“stack_results” Causes the plugin to export results data underneath pre-existing data (instead of adjacent to it). NOTE: The dataset label for the now stacked data will be that determined for the most recent import:

run("Read and Write Excel", "stack_results");

“file=” The path to the excel file to create and/or update (uses the default desktop file otherwise):

run("Read and Write Excel", "file=[C:/Users/antinos/Desktop/My_file.xlsx]");

“sheet=” Which sheet in the excel file to put the results in. For example, “sheet=banana” will create and/or append a sheet named ‘banana’:

run("Read and Write Excel", "sheet=banana");

Join arguments as follows:

run("Read and Write Excel", "no_count_column file=[C:/Users/antinos/Documents/My_cool_file.xlsx] sheet=[Custom name here] dataset_label=[Custom data title here]");

Large data import and export

“file_mode=” This requires a slightly different way of calling the plugin but is very useful for expected large datasets. There are three options.

“file_mode=read_and_open” Will just load/open an excel file into computer memory (the one you specify with file=)…make sure you do write_and_close when you’re done or you’ll have problems:

run("Read and Write Excel", "file=[C:/Users/antinos/Documents/My_cool_file.xlsx] file_mode=read_and_open");

“file_mode=queue_write” Will queue something to be written to the excel file you’ve opened previously with read_and_open:

run("Read and Write Excel", "file_mode=queue_write");

“file_mode=write_and_close” Will write everything you’ve queued with queue_write, then close the excel file:

run("Read and Write Excel", "file_mode=write_and_close");

Example large data macro workflow:

//macro to read and write large datasets

//open or create the .xlsx file we want to add data to
run("Read and Write Excel", "file=[C:/Users/antinos/Documents/My_cool_file.xlsx] file_mode=read_and_open");

//add multiple and/or large datasets
///do macro analysis and generate a big results table
run("Read and Write Excel", "file_mode=queue_write");
///do macro analysis and generate a big results table
run("Read and Write Excel", "file_mode=queue_write");
///do macro analysis and generate a big results table
run("Read and Write Excel", "file_mode=queue_write");
///do macro analysis and generate a big results table
run("Read and Write Excel", "file_mode=queue_write");
//for example in a loop
for (i = 0; i < 100; i++) {
    //macro code here
    run("Read and Write Excel", "file_mode=queue_write");
}

//finally write the queued results data to the file
run("Read and Write Excel", "file_mode=write_and_close");

Also see https://github.com/bkromhout/Read_and_Write_Excel_Modified for examples. (However please note: whilst Brenden did an amazing job updating the plugin for the version 1.1 release, the most up-to-date version is currently not represented on his github repo)

Disclaimers and Licencing stuff

I am aware that another Excel_Writer plugin exists but it did not work very well for me (hence me creating this). I did not consult their source code but did find out about Apache POI(*) from them.

Since Brenden modified the plugin, the code is now a lot neater. Feel free to consult it as per your needs.

(*)This plugin uses the Apache POI api, which is distributed under the terms of the Apache Licence (available from https://poi.apache.org/legal.html). I believe this software to be free and open source.

Plugin technical details

Dependencies

Update site

http://sites.imagej.net/ResultsToExcel

Installation

• With ImageJ open, navigate to ‘Help’
• In the presented drop down menu, then select ‘Update…’
• Allow ImageJ to check for updates
• If required, download updates and restart ImageJ as prompted
• Repeat the first two steps = Help>Update…
• This time ImageJ should let you know that there are no updates available
• Click on ‘OK’ but do not close the ‘ImageJ Updater’ window that is also open
• In the bottom left of the ‘ImageJ Updater’ window, find and click on ‘Manage update sites’
• In the newly presented ‘Manage update sites’ window, find and check the ‘ResultsToExcel’ check box
• Then close the ‘Manage update sites’ window
• The ‘ImageJ Updater’ window will now present you with the plugin files for installation
• Press ‘Apply changes’
• After the update has been applied successfully, restart ImageJ