ERROR: String literals in formulas can't be bigger than 255 characters ASCII

[Mead Lai <la...@gmail.com>]

Hello all,

I am now generate a Excel with POI. I need a Dropdown list in one column,
so I use the following code.
But if the set is too large, will cause Error: "String literals in formulas
can't be bigger than 255 characters ASCII".
I read the configuration from database, and create the excel-document with
poi,
so I am not sure which column need dropdown-list, and also don't know the
set will bigger than 255 chars.
Anyone face this problem? Thanks...[?]


     int EXPORT_BLANK_LINES = 50;
     Collection<String> set = map.values();
     // create a rectangle region. although is a column yet.
     CellRangeAddressList addressList = new CellRangeAddressList(1,
EXPORT_BLANK_LINES, index, index);
     DVConstraint dvConstraint =
DVConstraint.createExplicitListConstraint(set.toArray(new
String[set.size()] ));
     DataValidation dataValidation = new
HSSFDataValidation(addressList, dvConstraint);
     dataValidation.setSuppressDropDownArrow(false);
     sheet.addValidationData(dataValidation);

Regards,
Mead

Re: ERROR: String literals in formulas can't be bigger than 255 characters ASCII

[Mark Beardsley <ma...@tiscali.co.uk>]

Yes, it should be quite a trivial task. Obviously, I do not know how the data
is structured in your database but it should be possible to get the data for
all of the drop downs and store it into a series of List(s); one List for
each drop down. Next, create a named area from each list on the workbook
and, finally, create the drop-down list(s) based on each named area. Excel -
unlike OpenOffice which is why I cannot post a working example - allows you
to set the scope for a named area. Setting the scope to Workbook means that
an area defined in one sheet can be referenced in another. So, the data for
your drop-downs can be written into a separate sheet in the workbook which
can then be hidden so that the user need never know about it. By far the
best approach - to my mind at least - is to use the INDIRECT() function to
refer to a named area when creating a drop down. Imagine you have a named
area on a sheet in your workbook with the name of DATA. To use this in a
drop down, all you need to do is create a formula list constraint with the
function INDIRECT(DATA).

The code you posted that made use of a multi-row CellRangeAddressList object
would result in a drop-down list being created in each of the cells in the
range. So, again to use your example, one of the cell in rows 2 to 52 would
have a drop-down list in it. The drop-down in each cell will be identical.
Is this what you are seeking to create?

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/ERROR-String-literals-in-formulas-can-t-be-bigger-than-255-characters-ASCII-tp5507262p5508394.html
Sent from the POI - User mailing list archive at Nabble.com.

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

Re: ERROR: String literals in formulas can't be bigger than 255 characters ASCII

[Mark Beardsley <ma...@tiscali.co.uk>]

Good to hear everything is working and thanks for letting us know.

All the best with the project and if you have any other questions/problems
just post to the list.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/ERROR-String-literals-in-formulas-can-t-be-bigger-than-255-characters-ASCII-tp5507262p5511913.html
Sent from the POI - User mailing list archive at Nabble.com.

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

Re: ERROR: String literals in formulas can't be bigger than 255 characters ASCII

[Mead Lai <la...@gmail.com>]

Hello Mark,

I did it nice with your help. Maybe the last message was ambiguity and not
clear.
My case is multi-columns, which maybe have drop-down list, maybe not.
I use a hidden sheet to save the data-list. It works perfect.
Thank you, Mark.

Regards,
Mead

Re: ERROR: String literals in formulas can't be bigger than 255 characters ASCII

[Mark Beardsley <ma...@tiscali.co.uk>]

Just re-read a little bit of your last message and you talk about a template
that someone else is building. Does this mean that you will have an Excel
template that you must open and populate with data? Furthermore, will there
be validations on one or more of the sheets that you must modify with data
recovered from the database? If so, then this may cause problems as, at the
current moment, only the XSSF stream allows you to recover validations from
a sheet as far as I am aware. I have never done so but think, from looking
at the javadoc, that it should be possible to modify existing validations if
you are working with one of the newer OpenXML based files (a .xlsx file).

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/ERROR-String-literals-in-formulas-can-t-be-bigger-than-255-characters-ASCII-tp5507262p5508419.html
Sent from the POI - User mailing list archive at Nabble.com.

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

Re: ERROR: String literals in formulas can't be bigger than 255 characters ASCII

[Mead Lai <la...@gmail.com>]

thank you,Mark.
My drop-down list is really large, almost 50 rows.
My question is, I have to get configuration from database, then create this
excel-file,
Maybe, there are two columns need drop-down list, maybe ten cols,
it depends who config the template in our system.
Do you think it's possible to do it with your solution?
Anyway, you provide a useful way, appreciate you very much...

Regards,
Mead


On Thu, Feb 23, 2012 at 4:34 PM, Mark Beardsley <ma...@tiscali.co.uk>wrote:

> CellRangeAddressList(0, 0, 0, 0) - to make the drop down appear

Re: ERROR: String literals in formulas can't be bigger than 255 characters ASCII

[Mark Beardsley <ma...@tiscali.co.uk>]

Well, if you need lots of options in the drop-down list, why not write the
data into contiguous cells on the sheet, make that into a named area and use
it as the basis for the drop-down? I am sure there is a limit to the size of
the drop-down using this technique also and you may need to experiment to
discover what that is. Earlier, I posted an example that uses a separate
sheet to contain the data for the drop down lists; this sheet can be hidden
so that the final user of the workbook is unaware of it. If you do this, you
will need formula list constraints and not straightforward list constraints,
but Excels' formulae and functions are nothing to be scared of.

Also, I noticed that, in your example, your drop-down list appears to be
very large. Is this what you want? Typically, a drop-down list is contained
in a single cell and only appear fully when the user clicks on the downward
facing arrow alongside that cell. When using drop-downs myself, I have
typically created CellRangeAddressList instances like this; new
CellRangeAddressList(0, 0, 0, 0) - to make the drop down appear in cell A1
in this case.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/ERROR-String-literals-in-formulas-can-t-be-bigger-than-255-characters-ASCII-tp5507262p5507344.html
Sent from the POI - User mailing list archive at Nabble.com.

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