Exception error message when running "Read And Write Excel" plugin

export
plugin
fiji
Tags: #<Tag:0x00007fb87d249388> #<Tag:0x00007fb87d249220> #<Tag:0x00007fb87d2490e0>

#1

I used the “update fiji” option under “Help” banner in fiji to download the plugin “Read And Write Exel”.
Everything seemed to download and update successfully, so i ran my macro for measuring intensities and wanted to use the plugin to copy the measurements from the results window into excel, but when I try to run the plugin I only get the error message below:

(Fiji Is Just) ImageJ 2.0.0-rc-59/1.51k; Java 1.8.0_66 [64-bit]; Windows 10 10.0; 617MB of 5312MB (11%)
 
java.lang.NumberFormatException: For input string: "MAX_C1_centrosome:0001-0117"
	at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:2043)
	at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
	at java.lang.Double.parseDouble(Double.java:538)
	at Read_and_Write_Excel.run(Read_and_Write_Excel.java:67)
	at ij.IJ.runUserPlugIn(IJ.java:217)
	at ij.IJ.runPlugIn(IJ.java:181)
	at ij.Executer.runCommand(Executer.java:137)
	at ij.Executer.run(Executer.java:66)
	at java.lang.Thread.run(Thread.java:745)

Have anyone experienced the same?
Any suggestions on how to solve this issue would be greatly appreciated.


#2

I don’t know the plugin.

But it looks like it parses the first row of the data which in your case is a string (header) which can’t be parsed to a Java double type.

What happens if you copy the results without the header?

Disable “Results->Options->Copy Column Headers”

Anyway you can also copy and paste the results directly to Excel or save
the data as *.csv and reopen it in Excel, LibreOffice, etc.


#3

Thank you so much for your reply!

MAX_C1_centrosome:0001-0117 is the name of the first measured object in the results window so i need this to accompany its measured values.

I have been copying and pasting results into Excel earlier, but as im now trying to make a routine for batch analysis, I would prefer the export of results to be automatic.


#4

There are many examples in this forum archives or in the ImageJ mailing list, e.g.:

http://imagej.1557.x6.nabble.com/Macro-help-result-file-save-td3694038.html

http://imagej.1557.x6.nabble.com/save-results-table-as-csv-with-custom-name-td5003427.html

http://imagej.1557.x6.nabble.com/Saving-results-automatically-without-going-through-save-as-dialog-td5018135.html

etc.


#5

I might be able to save as csv file and then merge the files together to a final excel file.

Thank you so much for your help Bio7!


#6

Hi kafrik,

I wrote this plugin at while back (whilst also teaching myself java), so had to refamiliarise myself with it when I spotted your question in the last hour.

As Bio7 astutely points out, the error is related to the way in which the plugin expects data to be found within the results table. In a little more detail:
This code gets the results table a row at a time

  int RowN_alt = Analyzer.getResultsTable().getCounter();
  StringBuilder ResultsSB = new StringBuilder();
    for(int i=0; i<RowN_alt; i++) {
  	String line = Analyzer.getResultsTable().getRowAsString(i);

as string (text) data. The rest of the loop trims and splits it into an ‘ArrayList’ so that it can remove the count values… of all things. Anyway, for whatever reason I had at the time, this String data is then converted to Double data with the aforementioned ‘Double.parseDouble’:

for(int i = 0; i < Results_in_array.length; i++)
    {
    	data_in_array[i] = Double.parseDouble(Results_in_array[i]);
    }

In a later part of the plugin, the ApachePOI api component is also written to assign a NUMERIC ‘CellType’ to the cells that will contain the data:

if (cell == null)
        cell = row.createCell(cell_index);
    cell.setCellType(Cell.CELL_TYPE_NUMERIC);

Now in terms of an easy solution… I think I might be able to simply change all the instantiated ‘Double’ or ‘NUMERIC’ variables to ‘String’ and ‘STRING’.

I’ll have a play with the plugin maybe over the weekend.

Bio7’s suggestions were, as you realised, also good alternatives.

Best regards.


#7

Thank you for your help Antinos!

If the plug-in works it’s a quite briliant way of getting results from batch-analysis exported to Excel.
Fingers crossed for an “easy solution” and thank you so much again for taking the time to look at it.


#8

Hello again,

Try this modified plugin and let me know how it works out for you:
Read_and_Write_Excel-1.0.2.zip (7.4 KB)
With ImageJ closed, go to the plugins folder of your imagej/Fiji install. Move the existing ‘Read_and_Write_Excel-1.0.1.jar’ somewhere (incase you wish to reinstate it later). Copy the above version 1.0.2 into the plugins folder. Re-start ImageJ.

Some things to note with this version of the plugin:

  • If an image is open when the plugin runs, the title of the image is now added as a label to the data in excel. If more than one image is open, the plugin should use the original image window.

  • I have removed the ‘legacy’ feature, whereby the data could be fed to the plugin via the argument statement… this wasn’t relevant to you, or maybe anybody.

  • The plugin argument now provides an opportunity to override the image title as a label for the data in excel. For example, the macro command:

run ("Read and Write Excel", "This is the title I want to use instead of the image title");

will add that stupidly long “This is the title…” label to the excel sheet above where the data is inserted.

  • As indicated in my original plan, and now implemented here, the cells that have the results table fed to them are now formatted to expect text. This doesn’t stop numbers being imported but does make for an ugly excel spreadsheet. In my version of excel, the cells are automatically identified as likely numbers mis-formatted as text, with as auto-reformat dropdown offered. Hopefully this also applies to you.

Anyway, I hope this works for you now. If you really don’t like the exclusive text formatting of the spreadsheet cells, then the plugin could be custom re-written to assign the correct formats for each column of data… I just wasn’t familiar with your results table to attempt this, so I went for the quick fix. There’s maybe even some very easy ‘if’ statement or ApachePOI api class I could use but I didn’t hunt around for it.

Kind regards.


#9

I don’t mean to bump this thread, but I realised I miscoded a part of the plugin yesterday. This is the correct version to test:
Read_and_Write_Excel-1.0.2.zip (7.4 KB)

Regards.


#10

Hi!

Thank you for your quick response.
I replaced the Read_and_Write_Excel-1.0.1 file in the plugin folder with the file in your second post but when i try to run the plugin from the plugin menu nothing happens. There is no error message, but there are no results comming into Excel.

I also tried to reinstall the vanilla fiji package for 64-bit windows system and update without installing the read and write excel plugin. After this I draged the plugin file into the drag-and-drop space and saved it into the plugin folder. This time when running the plugin i get this error message:

(Fiji Is Just) ImageJ 2.0.0-rc-59/1.51k; Java 1.8.0_66 [64-bit]; Windows 10 10.0; 595MB of 5312MB (11%)
 
java.lang.NoClassDefFoundError: org/apache/poi/openxml4j/exceptions/InvalidFormatException
	at java.lang.Class.getDeclaredConstructors0(Native Method)
	at java.lang.Class.privateGetDeclaredConstructors(Class.java:2671)
	at java.lang.Class.getConstructor0(Class.java:3075)
	at java.lang.Class.newInstance(Class.java:412)
	at ij.IJ.runUserPlugIn(IJ.java:215)
	at ij.IJ.runPlugIn(IJ.java:181)
	at ij.Executer.runCommand(Executer.java:137)
	at ij.Executer.run(Executer.java:66)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.openxml4j.exceptions.InvalidFormatException
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	... 9 more

I run Excel 2016 do you think this might cause problems?

Best regards


#11

This plugin depends on the poi-ooxml library. You should always use ImageJ’s update site mechanism to avoid this kind of dependency issues. The updater will then take care that all dependencies are installed.

@antinos may I ask you to upload version 1.0.2 of your plugin to the update site instead of distributing zipped files? That will make it much easier for users :slight_smile:


#12

Thank you for your advise imagejan! That explains why the first and second try to run the plugin gave different results. Since Im a novice with macros in imageJ I just wanted to try different alternatives.

I guess the file was distributed as a zip file in order to test if the modifications solved the problem, but if we find a fix that workes well, I agree it would be nice to have it available in the updater page just like the previous verson.

Best wishes


#13

Hi again!
The plugin works perfectly fine now, even with Excel 2016! The excel file saved to the desktop, but needed to be closed in order to save the results properly.

Thank you so much for all your time and help! :slight_smile:

Best wishes


#14

Hello,

Super! Really glad I could help. You had me scratching my head for a bit wondering why the updated plugin didn’t work. Incidentally, are you satisfied even though all data is now imported as text?

@imagejan indeed I thought the direct distribution may be more sensible for testing purposes. If the new version worked as I hoped then my intention was to upload it properly. On your recommendation, and even before OP relayed the success, I have been trying to upload to “sites.imagej.net/ResultsToExcel/” but have encountered an issue. After entering my password on the ImageJ Updater (which I have checked gets me into imagej.net fine) I get another pop up box which my credentials don’t seem able to get me through:

I may try to use my home computer later, but any ideas in the interim?

Best regards.


#15

The upload password is not the same as your ImageJ wiki password.

Try resetting your upload password at http://imagej.net/Special:ChangeUploadPassword (and also avoid spaces and non-ASCII characters, as this has been causing troubles for some in the past, and I’m not sure if it was fixed yet).

See also:


#16

Sorry about the confusion.
Even though text import into Excel gives an extra step before further analysis, this is pretty easy to fix.
It saves me a lot of time and I am satisfied with the plugin.

Thank you again for taking the time to help me :slight_smile:

Best regards


#17

Thanks @imagejan, the update has gone through. I knew I was somehow being a tool.

@kafrik I may try to fix that issue in the future, but thanks for helping me to improve the plugin!

Best regards.