Ignore "Number Stored As Text"

Apr 7, 2011 at 2:34 PM

Hi,

I have been trying out extremeML Today and think I am halfway to my end result. Can you give me a pointer to get me on the right way please?

I have text column that consists of number like '0505','0354' and excel show the well know "NumberStoredAsText" tooltip for each cell. I think I can tell the worksheet to ignore these errors by doing someting like

package.WorkbookPart.GetTablePart("Table1").Table.Worksheet.IgnoredErrors.IgnoredErrors

But this returns a collection of ignorederrors that I cannot add to. I specifacly see a properoty "NumberStoredAsText" documented in the api so I think I'm close.

Can you be so kind to give me a pointer here? I will contribute back with a sample :) cause this project is worth it.

Kind Regards, Tom Pester

Coordinator
Apr 7, 2011 at 7:13 PM

Hi Tom,

I have to admit that this isn't something I have come across before, so it may be difficult for me to advise you.

As general advice, I would be careful about accessing properties like the one you mentioned above, as many of these exist only because they are code-generated from the meta-data in the OpenXML SDK and they require additional plumbing code (either within ExtremeML core or within your application) in order to make proper use of them. This is because any OpenXML worksheet is a complex set of inter-element relations and it is often not safe to modify one element without properly handling all the consequential modifications. In fact, this is the core role of ExtremeML, but it is currently limited to the scope of functionality described in the User Guide. It's true that some Excel functionality "just works" in ExtremeML, but you can't rely on every piece of Excel functionality working properly.

One possible solution to your scenario would be to use the methods of the CellWrapper class that set or return strongly-typed data. For example, if you want to store a string value into a cell you can call SetValue() with a string and the data type assignment will be handled automatically. Similarly, if you want to retrieve a string value, you can call GetValue<string>().

I hope this helps, but I can't think of anything else to suiggest - sorry :)

Tim

Apr 13, 2011 at 12:23 AM

Hello Tim,

Thanks for your reply. I managed to solve it by setting the datatable column  type to numeric and apply a formatting so it's correctly padded as per my requirement. (6 numbers in total, pad zero's to the left).

I am sorry to say that I can't use ExtremeML for my current requirements. I tests with some 100 records and it performed well. Today was the day it went into production and it performed horribly even with 1000 records. Im saying it as it is, it became unusable for me :(

So for now I have to use another solutions. For (very) small reports the template approach is unbeatable though. But this makes the solution not generic enough :( It seems to be a problem in the SDK itself since my research confirms that many people face this problem.

A person from MS blogged about it suggesting an alternative (SAX instead of DOM) http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx

Seems we have to wait for V3 or V4 to get an API that's friendly _and_ fast. I have a feeling someone at MS ignored some important aspects or maybe I am dumb :)

 

Kind Regards, Tom

Coordinator
Apr 13, 2011 at 10:07 AM

Hi Tom,

I’m sorry to hear that things didn’t work out for you, but I’m not entirely surprised about the performance problems, as I have seen various comments on this subject and I know that the OpenXML SDK is facing a difficult challenge to handle large object graphs. ExtremeML just adds a further layer of abstraction, so it compounds the problem.

I have made several attempts to optimise things, but without much success, since there are some fundamental obstacles to overcome. As you say, maybe we’ll see some improvements in a future release of the SDK. Let’s hope so.

In any case, I wish you success with your projects.

Best regards,

Tim

From: buckley [email removed]
Sent: 13 April 2011 01:24
To: tim.coulter@online.fr
Subject: Re: Ignore "Number Stored As Text" [extrememl:252951]

From: buckley

Hello Tim,

Thanks for your reply. I managed to solve it by setting the datatable column type to numeric and apply a formatting so it's correctly padded as per my requirement. (6 numbers in total, pad zero's to the left).

I am sorry to say that I can't use ExtremeML for my current requirements. I tests with some 100 records and it performed well. Today was the day it went into production and it performed horribly even with 1000 records. Im saying it as it is, it became unusable for me :(

So for now I have to use another solutions. For (very) small reports the template approach is unbeatable though. But this makes the solution not generic enough :( It seems to be a problem in the SDK itself since my research confirms that many people face this problem.

A person from MS blogged about it suggesting an alternative (SAX instead of DOM) http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx

Seems we have to wait for V3 or V4 to get an API that's friendly _and_ fast. I have a feeling someone at MS ignored some important aspects or maybe I am dumb :)

Kind Regards, Tom

Apr 13, 2011 at 11:12 AM

Hello Tim,

I wish you also all the best with this project. You have a hard dependency on the SDK so its out of your control :(

Beste Regards,

Tom