Checking checkboxes using an Excel writer template

Hi @jwk​ ,

I gathered some hints from this previous Q&A where it looks like we have limited support for reading check boxes, given that the check boxes are linked to their respective cells. I noticed that we seem to be reading in Unchecked and Mixed values as Missing, so I've filed an issue for that tracked as (FMEENGINE-66721), and I'll update this thread when it has been addressed.

In the meantime you may have some success with a template that is already set up with all the check boxes. You should be able to change the values of the check boxes by reading in the linked column as Boolean (writing out to Boolean as well), and setting TRUE or FALSE as a value. (Thanks to @debbiatsafe​ ​ for the lead on trying the Boolean data type!)

I haven't had any success in writing the Mixed or #N/A value, so as a workaround perhaps start with a template that is checked Mixed and alter the other values as needed if Mixed is needed in your output. If this is something that you would like to see added into FME, it'd be worth posting a new idea for our product team. Sorry for the inconvenience!

This may be outside the scope of the original question but adding this in case it may help other users. If Mixed checkboxes are required in the output given you are using an Excel template with linked checkboxes, try using following Excel formula within the workspace:

=NA()

As noted in Excel writer documentation here, a formula for calculating the value of an attribute can be set in a separate attribute named <attributename>.formula. Note that in order for the formula to be applied to the cell, the writer must contain both attributename (which can be empty) and attributename.formula. 

For example, if you wish to create mixed checkboxes in the output Excel workbook for a column named N/A, create a new attribute called N/A.formula and set this new attribute value to =NA(). Screenshot is included below.