poi/hssf capability question re: xls checkbox

[brent <br...@3pg.us>]

Hello,

  I've got a capabilities question for the POI gurus.

I'm pretty much a php guy and don't have a lot of experience with the java
so bear with me.

==========
Situation:
==========

I have a .xls excel file generated by some 3rd party software It makes a
workbook with several worksheets. Each worksheet has a variable (but known)
number of checkboxes The cells in the sheet are filled out with data and the
checkboxes are either checked or unchecked.

However!! The wonderful people who made this little gem didn't link the 
checkboxes to any cells (although they did position them in known places, 1
checkbox right above 1 cell). And since I have no control of that end of the
workflow I'm stuck trying to process the generated files.

The .xls gets pushed to my linux box and I need to extract the data to drop
it into mysql.

I'm using the spreadsheet::parsexcel routines and they work just fine but
because the checkboxes are not linked to a cell they are effectivly
invisible.

I tested manually by linking a few checkboxes to the associated cells that
if they are linked the data comes out fine.

I also tested with ssconvert making a gnumeric conversion - this sees the
checkboxes but as with the parseexcel it does not get the state unless the
checkbox is tied to the cell.

==============
Question:
==============

Does HSSF or POI, or perhaps the biff reader have any ability to 
dump the checkboxe states in an excel .xls IF THEY ARE NOT LINKED to a cell?

This should be a pretty easy test but I really don't want to install all the
POI stuff if it can't actually get to the important data.

Or alternativly given a checkbox which is NOT linked to a cell but is
positioned above it, can some calls in these programs LINK that checkbox to
the CELL without losing the check state?

Either of these would work for me because if they're linked I call pull out
the data with a parser or if I can just dump out the cell's states for each
worksheet in order (ie: true,false,false,false,true <-- row 1 etc) I can
pull it back together after the fact.


Help me oh gurus? Is POI where I need to be? Can someone confirm an ability
to read the state of checkboxes NOT linked to cells or (sadly) tell me that
it cannot be done with this framework?

Thank you!!!

Please reply here or direct to my email 'brent at 3pg.us' (yes it's a 3)

Thanks!

=========================================
This email is considered confidential and proprietary and is for the
intended parties only.  
If you have received this email by accident please reply to it, then delete
this email and any copies of it.
Content and any attached documents are intended for review purposes only,
they are not to be modified in any way.  



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: poi/hssf capability question re: xls checkbox

[Nick Burch <ni...@torchbox.com>]

On Mon, 31 Mar 2008, brent wrote:
> Does HSSF or POI, or perhaps the biff reader have any ability to dump 
> the checkboxe states in an excel .xls IF THEY ARE NOT LINKED to a cell?

My hunch is that the checkboxes will be escher records, in the main 
workbook stream. (There would normally be a link to them from the cells, 
but not in your case).

You'd probably want to use org.apache.poi.hssf.dev.BiffViewer and 
org.apache.poi.ddf.EscherDump to poke around in the file, and track these 
checkboxes down.

Once you've found them, I suspect you'll discover they are stored one per 
clump of escher records. Hopefully they'll be in order, but if not, the 
anchor details ought to let you figure out what order the records 
correspond to. Then, it'd be a case of writing some code to open the 
workbook, grab the records, find the checkboxes, and output their values


So, should be do-able, but you'll almost certainly need to dive into the 
record level code, and learn more about the file formats than you'd 
probably want to... at least there are now docs for much of it!
    http://www.microsoft.com/interop/docs/OfficeBinaryFormats.mspx

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org