Hey,
Here's a little more info about my specific situation.
I created a .xls template, which contains the basic structure of what my
Excel should look like.
This is then opened using POI, and data from a database is inserted.
My problem before was that when I wanted to add a selection list with a
cell range specified to this .xls file, I received the error message
that I will include below.
That lead me to the conclusion that POI was unable to read files with a
selection list like this.
After your email, though, I decided to do a little more testing and
found out that there is a huge difference between files created by Excel
or Openoffice Spreadsheet (which I use by default).
It seems that when I create a file with MS Excel, containing a selection
list, POI will read it and create a new file with inserted data just
fine.
Strangely enough, this .xls file works perfectly when I open it with
OOS. When I open it in MS Excel, all cells using the VLOOKUP function
show '#N/B'. When I edit one of these cells, and click enter, everything
works as normal. However, the selection list is empty, and cannot be
made to work.
Now, when I create a file using OOS, containing a selection list, POI
just throws the Exception below. Files without this selection list, will
open perfectly in both OOS and MS Excel, with VLOOKUP working as
expected.
At this point I believe I will just exclude the selection list from my
template, as it is not a vital part of my output file (although it would
have been nice), and use the version created by OOS.
I am especially confused by the fact that for both the selection list
and the VLOOKUP function, MS Excel will create a file that works in OOS,
but not in MS Excel itself.
If anyone can clear up why this is happening, feel free to give
support ;-)
Regards,
Elvis Willems
PS: I am interested in contributing to the POI API. For my current
project I needed the option to 'protect document' on top of the existing
'protect sheet', which I implemented in my own source code, and intend
to share once I find some time to make sure everything is done according
to POI standards, which of course could be a while...
PS2: POI error below:
17:03:11,720 ERROR AppExcelGenerator:143 -
org.apache.poi.hssf.record.RecordFormatException: Unable to construct
record instance
Exception in thread "main"
org.apache.poi.hssf.record.RecordFormatException: Unable to construct
record instance
at
org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:199)
at
org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:117)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:206)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:260)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:241)
*cut non-poi-stuff*
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at
org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:187)
... 8 more
Caused by: java.lang.ArrayIndexOutOfBoundsException
at
org.apache.poi.hssf.record.RecordInputStream.checkRecordPosition(RecordInputStream.java:132)
at
org.apache.poi.hssf.record.RecordInputStream.readShort(RecordInputStream.java:146)
at
org.apache.poi.hssf.util.HSSFCellRangeAddress.fillFields(HSSFCellRangeAddress.java:77)
at
org.apache.poi.hssf.util.HSSFCellRangeAddress.<init>(HSSFCellRangeAddress.java:65)
at org.apache.poi.hssf.record.DVRecord.fillFields(DVRecord.java:204)
at org.apache.poi.hssf.record.Record.<init>(Record.java:53)
at org.apache.poi.hssf.record.DVRecord.<init>(DVRecord.java:134)
... 13 more
On Mon, 2007-12-10 at 15:15 +0000, Tim Wilkins wrote:
> I'm not sure if this will be of any help to you but I know you can get
> Excel to use a named range to define the values of a drop down.
>
> I have spreadsheets that use a column in another worksheet to store the
> drop down values that are available, and as such all the values are
> readable in POI.
>
> Tim
>
> Lauren Riley wrote:
> >
> > Elvis,
> >
> > I've tried to have an existing spreadsheet that has just my drop downs
> > so I could copy them into a new sheet. I can only copy the first entry
> > in the drop down...I can't get to the other values.
> >
> > This is driving me nuts because I really need this ability! So I'm
> > trying to figure out a different solution.
> >
> > Lauren
> >
> > Inactive hide details for Elvis Willems <[EMAIL PROTECTED]>Elvis
> > Willems <[EMAIL PROTECTED]>
> >
> >
> > *Elvis Willems <[EMAIL PROTECTED]>*
> >
> > 12/10/2007 08:52 AM
> > Please respond to
> > "POI Users List" <[email protected]>
> >
> >
> >
> > To
> >
> > POI Users List <[email protected]>
> >
> > cc
> >
> >
> > Subject
> >
> > Re: Drop Down List Cell
> >
> >
> >
> >
> > Am I right in thinking that .xls files that already have a selection
> > list pre-made, can not be read by POI either?
> >
> > Regards,
> >
> > Elvis Willems
> >
> >
> > On Mon, 2007-12-10 at 13:13 +0000, Nick Burch wrote:
> >
> > > On Thu, 6 Dec 2007, Lauren Riley wrote:
> > > > Have dropdown lists been added to the POI API?
> > >
> > > I don't believe so, no
> > >
> > > > If not, when will they?
> > >
> > > As soon as someone wants to contribute the code for it!
> > >
> > > If you are interested in adding support for it, please join the dev
> > list
> > > and we can give you advice on the best way to get started with
> > writing the
> > > support for it. Otherwise, a few of the poi developers do offer
> > > consulting - if you're interested, people can contact you off-list.
> > >
> > > Nick
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> > **** DISCLAIMER ****
> > http://www.schaubroeck.be/maildisclaimer.htm
> >
> >
> >
> > __________________________________________
> > The information contained in this message may be privileged and
> > confidential and protected from disclosure. If you are not the
> > intended recipient of this message, you are hereby notified that any
> > dissemination, distribution, or copying of this communication is
> > strictly prohibited. If you have received this communication in error,
> > please notify us immediately by replying to the message, and please
> > delete it from your computer.
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
**** DISCLAIMER ****
http://www.schaubroeck.be/maildisclaimer.htm
