Topic
  • 15 replies
  • Latest Post - ‏2012-10-04T13:36:37Z by DGawron
Codeoline
Codeoline
141 Posts

Pinned topic Spreadsheet dropping leading 0's

‏2012-09-25T12:42:13Z |
When exporting data to Excel it is dropping leading 0's and i need those.
Any idea's on what i could do to correct this? I have tried the different Transform builders but didnt see one that would work for this particular problem.
Updated on 2012-10-04T13:36:37Z at 2012-10-04T13:36:37Z by DGawron
  • Codeoline
    Codeoline
    141 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-25T20:03:23Z  
    Any hints at all would be helpfull?
  • Dinger
    Dinger
    117 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-25T21:02:02Z  
    • Codeoline
    • ‏2012-09-25T20:03:23Z
    Any hints at all would be helpfull?
    Depending on your requirements, it might work acceptably if you transform the values in that column so that they contain an apostrophe character at the beginning of the value. So 0090 would become '0090. Excel should interpret this as text instead of as a number, but it won't display the apostrophe character on the sheet.

    Disclaimer: I don't have Excel installed, so I couldn't actually test this. It works for the attached sample when I export as CSV and open it in Symphony.
  • DGawron
    DGawron
    580 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-26T15:45:01Z  
    Spreadsheet Export's default behavior is to assume that numeric-like values are intended to be numbers. However, if you are using 7.0.1.x, then there was a recent fix for this. The APAR is LO70097. The fix lets you override the default behavior by providing a custom class that is called by Spreadsheet Export to determine how to interpret these ambiguous values.
  • Codeoline
    Codeoline
    141 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-26T17:29:17Z  
    • DGawron
    • ‏2012-09-26T15:45:01Z
    Spreadsheet Export's default behavior is to assume that numeric-like values are intended to be numbers. However, if you are using 7.0.1.x, then there was a recent fix for this. The APAR is LO70097. The fix lets you override the default behavior by providing a custom class that is called by Spreadsheet Export to determine how to interpret these ambiguous values.
    We are using 8.0. Are there any examples of this?
  • DGawron
    DGawron
    580 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-26T18:21:54Z  
    • Codeoline
    • ‏2012-09-26T17:29:17Z
    We are using 8.0. Are there any examples of this?
    The WEF 8.0.0.1 fix pack also has the fix. There isn't a published sample, but I do have a simple one that I can post to the Wiki once you upgrade. What you are probably most interested in right now is figuring out how much work is involved. To enable this functionality you need to write a class that extends an abstract base class. Your class has one required method:

    public ElementType getType( WebAppAccess waa, String elementName, String elementValue );

    Your method, called by the builder, determines what element type (number or text) to give to each named element. The elementName is the name of the XML element containing the export data. The code uses the name to recognize those elements containing data requiring special handling. For example, if you had XML rowsets in which a ProductCode column was a numeric-like value with leading zeros that should be treated as just a string, then your code would return a TEXT type for this element. Otherwise you'd return NUMBER (the default assumption of the export builder).
  • dmelby
    dmelby
    131 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-26T18:42:47Z  
    • DGawron
    • ‏2012-09-26T18:21:54Z
    The WEF 8.0.0.1 fix pack also has the fix. There isn't a published sample, but I do have a simple one that I can post to the Wiki once you upgrade. What you are probably most interested in right now is figuring out how much work is involved. To enable this functionality you need to write a class that extends an abstract base class. Your class has one required method:

    public ElementType getType( WebAppAccess waa, String elementName, String elementValue );

    Your method, called by the builder, determines what element type (number or text) to give to each named element. The elementName is the name of the XML element containing the export data. The code uses the name to recognize those elements containing data requiring special handling. For example, if you had XML rowsets in which a ProductCode column was a numeric-like value with leading zeros that should be treated as just a string, then your code would return a TEXT type for this element. Otherwise you'd return NUMBER (the default assumption of the export builder).
    Thanks for the response, I'm working with Codeoline on this also. Where do we download the 8.0.0.1 fix pack?
  • DGawron
    DGawron
    580 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-26T21:59:09Z  
    • dmelby
    • ‏2012-09-26T18:42:47Z
    Thanks for the response, I'm working with Codeoline on this also. Where do we download the 8.0.0.1 fix pack?
    FixCentral: http://www-933.ibm.com/support/fixcentral/swg/selectFix?product=ibm%2FLotus%2FWeb+Experience+Factory&fixids=WEF-8.0.0.1&source=dbluesearch&function=fixId&parent=ibm/Lotus
  • Codeoline
    Codeoline
    141 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-27T13:22:02Z  
    • DGawron
    • ‏2012-09-26T18:21:54Z
    The WEF 8.0.0.1 fix pack also has the fix. There isn't a published sample, but I do have a simple one that I can post to the Wiki once you upgrade. What you are probably most interested in right now is figuring out how much work is involved. To enable this functionality you need to write a class that extends an abstract base class. Your class has one required method:

    public ElementType getType( WebAppAccess waa, String elementName, String elementValue );

    Your method, called by the builder, determines what element type (number or text) to give to each named element. The elementName is the name of the XML element containing the export data. The code uses the name to recognize those elements containing data requiring special handling. For example, if you had XML rowsets in which a ProductCode column was a numeric-like value with leading zeros that should be treated as just a string, then your code would return a TEXT type for this element. Otherwise you'd return NUMBER (the default assumption of the export builder).
    Can you send a link to the example? This is the only WIKI i have and i do not see anything on it pertaining to this.

    http://www-10.lotus.com/ldd/pfwiki.nsf/dx/learning-websphere-portlet-factory#Installing+and+getting+started

    Thanks so much for your help.
  • dmelby
    dmelby
    131 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-27T14:58:37Z  
    • DGawron
    • ‏2012-09-26T18:21:54Z
    The WEF 8.0.0.1 fix pack also has the fix. There isn't a published sample, but I do have a simple one that I can post to the Wiki once you upgrade. What you are probably most interested in right now is figuring out how much work is involved. To enable this functionality you need to write a class that extends an abstract base class. Your class has one required method:

    public ElementType getType( WebAppAccess waa, String elementName, String elementValue );

    Your method, called by the builder, determines what element type (number or text) to give to each named element. The elementName is the name of the XML element containing the export data. The code uses the name to recognize those elements containing data requiring special handling. For example, if you had XML rowsets in which a ProductCode column was a numeric-like value with leading zeros that should be treated as just a string, then your code would return a TEXT type for this element. Otherwise you'd return NUMBER (the default assumption of the export builder).
    I see that the fix pack is up to WEF 8.0.0.4, are these fix packs cumulative?
  • mburati
    mburati
    2561 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-27T16:59:27Z  
    • dmelby
    • ‏2012-09-27T14:58:37Z
    I see that the fix pack is up to WEF 8.0.0.4, are these fix packs cumulative?
    The latest fixpack (as of this posting) that I believe I know of is 8.0.0.1, and that is what I see on the page if I go to the fixpack URL that Dave specified above. Where are you seeing 8.0.0.4? To answer your other question though, WEF fixpacks should be cumulative afaik.

    I hope that info helps,
    ..mb1
    http://www-10.lotus.com/ldd/pfwiki.nsf/
    The postings on this site are my own and do not necessarily represent the positions, strategies, or opinions of IBM.
  • dmelby
    dmelby
    131 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-27T17:11:54Z  
    • mburati
    • ‏2012-09-27T16:59:27Z
    The latest fixpack (as of this posting) that I believe I know of is 8.0.0.1, and that is what I see on the page if I go to the fixpack URL that Dave specified above. Where are you seeing 8.0.0.4? To answer your other question though, WEF fixpacks should be cumulative afaik.

    I hope that info helps,
    ..mb1
    http://www-10.lotus.com/ldd/pfwiki.nsf/
    The postings on this site are my own and do not necessarily represent the positions, strategies, or opinions of IBM.
    That's just bizarre, I'm not sure how the 8.0.0.4 slipped in there - I must be getting old. Somewhere I thought I saw it, well anyway I did download and apply 8.0.0.1 cause that's what Eclipse and the download indicate. Sorry for the confusion.
  • Codeoline
    Codeoline
    141 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-28T12:50:10Z  
    • DGawron
    • ‏2012-09-26T21:59:09Z
    FixCentral: http://www-933.ibm.com/support/fixcentral/swg/selectFix?product=ibm%2FLotus%2FWeb+Experience+Factory&fixids=WEF-8.0.0.1&source=dbluesearch&function=fixId&parent=ibm/Lotus
    Just wondering if you have had time to get your example posted to the Wiki yet?
  • DGawron
    DGawron
    580 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-10-01T18:07:45Z  
    • Codeoline
    • ‏2012-09-28T12:50:10Z
    Just wondering if you have had time to get your example posted to the Wiki yet?
    Have not had the time to write up an article. Attached is the sample WEF package. To activate the classifier you'll need to set bowstreet.builders.spreadsheetExport.elementClassifier to the fully qualified name of the classifier class. Put this property in web-inf/conf/override.properties in your project and redeploy. Then the spreadsheet export builder will then call the classifier for each exported value.
  • dmelby
    dmelby
    131 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-10-03T14:44:10Z  
    • DGawron
    • ‏2012-10-01T18:07:45Z
    Have not had the time to write up an article. Attached is the sample WEF package. To activate the classifier you'll need to set bowstreet.builders.spreadsheetExport.elementClassifier to the fully qualified name of the classifier class. Put this property in web-inf/conf/override.properties in your project and redeploy. Then the spreadsheet export builder will then call the classifier for each exported value.
    We have tried importing your sample, but are getting a red x on the following import statement in SampleClassifier.java:

    import com.bowstreet.builders.webapp.methods.ExportedElementClassifier;

    Any ideas?
  • DGawron
    DGawron
    580 Posts

    Re: Spreadsheet dropping leading 0's

    ‏2012-10-04T13:36:37Z  
    • dmelby
    • ‏2012-10-03T14:44:10Z
    We have tried importing your sample, but are getting a red x on the following import statement in SampleClassifier.java:

    import com.bowstreet.builders.webapp.methods.ExportedElementClassifier;

    Any ideas?
    Open a PMR with WEF support. The Spreadsheet builder containing the classifier implementation was not included 8.0.0.1 fixpack. We can create an APAR test fix that will provide the updated builder code.