You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Robert Tomanek <us...@mail.robert.tomanek.org> on 2010/03/05 09:39:11 UTC
Suppressing cell errors like xlNumberAsText
Hi,
I need to write values into Excel cells. I don't have much control
over these values as they come from an external source via XML (so
all of them are Strings).
Most of the time it works fine for me however sometimes Excel is
trying to be too smart and displays the dreaded green triangles, like
the one for "Number Stored as Text": "The number in this cell is
formatted as a number or preceded by an apostrophe" -> "Convert to
Number" / "Ignore Error".
I would like to programmatically (via POI) set the Ignore property
for cell errors to True. In VBA it looks like this:
cell.Errors.Item(xlNumberAsText).Ignore = True
More info:
http://msdn.microsoft.com/en-us/library/bb223803.aspx
http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.errors.aspx
How do I go about that in POI?
Being smart about cell types (e.g. trying to detect integers and
write them as such) is not really an option for me -- the values from
the external source can have really fancy formatting and I can never
be sure what will trigger Excel's filters. Let alone the unneeded
code complexity.
So, is there any way to set cell error ignoring in POI?
Kind regards,
r.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Suppressing cell errors like xlNumberAsText
Posted by MSB <ma...@tiscali.co.uk>.
I do not know if this is possible and I say that because the setting is not
related to the workbook but it is a global setting related to Excel itself
if I remember correctly; setting this through Excel, you would use the
Tools>Options>Error Checking menu. Also, the green triangle indicates more
than simple data type errors and would your users want to have this support
disabled?
Having said all of that, it may be possible to modify this setting through
the workbook options but I do not know this for certain and will try to
experiment a little if I have the time this week.
Yours
Mark B
Robert Tomanek-3 wrote:
>
> Hi,
>
> I need to write values into Excel cells. I don't have much control
> over these values as they come from an external source via XML (so
> all of them are Strings).
>
> Most of the time it works fine for me however sometimes Excel is
> trying to be too smart and displays the dreaded green triangles, like
> the one for "Number Stored as Text": "The number in this cell is
> formatted as a number or preceded by an apostrophe" -> "Convert to
> Number" / "Ignore Error".
>
> I would like to programmatically (via POI) set the Ignore property
> for cell errors to True. In VBA it looks like this:
>
> cell.Errors.Item(xlNumberAsText).Ignore = True
>
> More info:
> http://msdn.microsoft.com/en-us/library/bb223803.aspx
>
> http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.errors.aspx
>
> How do I go about that in POI?
>
> Being smart about cell types (e.g. trying to detect integers and
> write them as such) is not really an option for me -- the values from
> the external source can have really fancy formatting and I can never
> be sure what will trigger Excel's filters. Let alone the unneeded
> code complexity.
>
> So, is there any way to set cell error ignoring in POI?
>
> Kind regards,
> r.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Suppressing-cell-errors-like-xlNumberAsText-tp27810457p27818158.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: Suppressing cell errors like xlNumberAsText
Posted by MSB <ma...@tiscali.co.uk>.
Thanks, you (I) really do live and learn.
Yours
Mark B
Nick Burch-11 wrote:
>
> On Mon, 8 Mar 2010, MSB wrote:
>> That's a surprise Nick because I just did some simple testing myself -
>> using Excel - and could not store these settings in the file.
>
> It's not impossible that your version of excel isn't storing them in the
> file, despite that being possible....!
>
> Pages 669 and 670 of the excel docs cover FeatFormulaErr2, which is held
> in FeatRecord shared features, which in theory allows you to store the
> fact that "number as text" should be ignored for a cell range
>
> We've also got two test files, one with the warnings on, and one with them
> off - 46136-NoWarnings.xls and 46136-WithWarnings.xls . I didn't create
> them however!
>
> Nick
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Suppressing-cell-errors-like-xlNumberAsText-tp27810457p27823222.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: Suppressing cell errors like xlNumberAsText
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 8 Mar 2010, MSB wrote:
> That's a surprise Nick because I just did some simple testing myself -
> using Excel - and could not store these settings in the file.
It's not impossible that your version of excel isn't storing them in the
file, despite that being possible....!
Pages 669 and 670 of the excel docs cover FeatFormulaErr2, which is held
in FeatRecord shared features, which in theory allows you to store the
fact that "number as text" should be ignored for a cell range
We've also got two test files, one with the warnings on, and one with them
off - 46136-NoWarnings.xls and 46136-WithWarnings.xls . I didn't create
them however!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Suppressing cell errors like xlNumberAsText
Posted by MSB <ma...@tiscali.co.uk>.
That's a surprise Nick because I just did some simple testing myself - using
Excel - and could not store these settings in the file. In short, I created
a new workbook, changed the error checking options and then saved the file
away. Afdter closing that file, I changed the error checking options again -
without the need to have a workbook open - and then reopened the file I had
created previously. Opening that file did nto lead to any change on the
error checking options and that is why I suspect they are set at the
application level and not the workbook level. Admittedly, I did not embark
on an serious testing and so will happily be proven wrong.
Yours
Mark B
Nick Burch-11 wrote:
>
> On Fri, 5 Mar 2010, Robert Tomanek wrote:
>> I would like to programmatically (via POI) set the Ignore property
>> for cell errors to True.
>
> There's currently some low level record support for this, via FeatRecord /
> FeatFormulaErr2. However, there's no usermodel support for this, as
> no-one's come up with a good way to expose this.
>
> Short term, you can probably hack something up to inject the required
> records (look at the tests for examples of what they need to contain).
> Longer term, patches to the usermodel are always gratefully received! :)
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Suppressing-cell-errors-like-xlNumberAsText-tp27810457p27822473.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: Suppressing cell errors like xlNumberAsText
Posted by Nick Burch <ni...@alfresco.com>.
On Fri, 5 Mar 2010, Robert Tomanek wrote:
> I would like to programmatically (via POI) set the Ignore property
> for cell errors to True.
There's currently some low level record support for this, via FeatRecord /
FeatFormulaErr2. However, there's no usermodel support for this, as
no-one's come up with a good way to expose this.
Short term, you can probably hack something up to inject the required
records (look at the tests for examples of what they need to contain).
Longer term, patches to the usermodel are always gratefully received! :)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org