Read and Write Excel

The content of this page has not been vetted since shifting away from MediaWiki. If you’d like to help, check out the how to help guide!

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.

Usage

Youtube tutorial:

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 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

Change log

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