Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
15 replies Latest Post - ‏2012-10-04T13:36:37Z by DGawron
Codeoline
Codeoline
141 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-25T20:03:23Z  in response to Codeoline
    Any hints at all would be helpfull?
    • Dinger
      Dinger
      117 Posts
      ACCEPTED ANSWER

      Re: Spreadsheet dropping leading 0's

      ‏2012-09-25T21:02:02Z  in response to Codeoline
      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
    ACCEPTED ANSWER

    Re: Spreadsheet dropping leading 0's

    ‏2012-09-26T15:45:01Z  in response to Codeoline
    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
      ACCEPTED ANSWER

      Re: Spreadsheet dropping leading 0's

      ‏2012-09-26T17:29:17Z  in response to DGawron
      We are using 8.0. Are there any examples of this?
      • DGawron
        DGawron
        580 Posts
        ACCEPTED ANSWER

        Re: Spreadsheet dropping leading 0's

        ‏2012-09-26T18:21:54Z  in response to Codeoline
        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
          ACCEPTED ANSWER

          Re: Spreadsheet dropping leading 0's

          ‏2012-09-26T18:42:47Z  in response to DGawron
          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
            ACCEPTED ANSWER

            Re: Spreadsheet dropping leading 0's

            ‏2012-09-26T21:59:09Z  in response to dmelby
            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
              ACCEPTED ANSWER

              Re: Spreadsheet dropping leading 0's

              ‏2012-09-28T12:50:10Z  in response to DGawron
              Just wondering if you have had time to get your example posted to the Wiki yet?
              • DGawron
                DGawron
                580 Posts
                ACCEPTED ANSWER

                Re: Spreadsheet dropping leading 0's

                ‏2012-10-01T18:07:45Z  in response to Codeoline
                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
                  ACCEPTED ANSWER

                  Re: Spreadsheet dropping leading 0's

                  ‏2012-10-03T14:44:10Z  in response to DGawron
                  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
                    ACCEPTED ANSWER

                    Re: Spreadsheet dropping leading 0's

                    ‏2012-10-04T13:36:37Z  in response to dmelby
                    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.
        • Codeoline
          Codeoline
          141 Posts
          ACCEPTED ANSWER

          Re: Spreadsheet dropping leading 0's

          ‏2012-09-27T13:22:02Z  in response to DGawron
          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
          ACCEPTED ANSWER

          Re: Spreadsheet dropping leading 0's

          ‏2012-09-27T14:58:37Z  in response to DGawron
          I see that the fix pack is up to WEF 8.0.0.4, are these fix packs cumulative?
          • mburati
            mburati
            2552 Posts
            ACCEPTED ANSWER

            Re: Spreadsheet dropping leading 0's

            ‏2012-09-27T16:59:27Z  in response to dmelby
            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
              ACCEPTED ANSWER

              Re: Spreadsheet dropping leading 0's

              ‏2012-09-27T17:11:54Z  in response to mburati
              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.