• Data Types and format

    By Jan Schulz 2 decades ago

    Hi You,



    I've a report where I have a filed, which is filed with ZIP codes (in germany they are like 12345). The problem is, that they come out in excel as numbers. As the zip codes can start with '0', '01234' becomes '1234' despite having specified 'text' as Data Type.



    It would be nice if you could implement something like 'in Notes it is this data type, export to Excel as that data type'. If I understand the code right, it should already be there, but then this is a bugreport :-) The easy fix would be to prefix the value with "'" (single quotation mark) -> will mark this value as string in excel.



    Also, there seems to be a bug in the 'getNumberFormat': "Case Is = "EuriDateTime" :" looks wrong to me :-) Also, we (Germany :-) ) have 24 hours instead of AM/PM.



    Nice greetings and thanks for this app!



    Jan

    • Questions

      By Sean Burgess 2 decades ago

      I found the bug in the getNumberFormat function and have fixed it. I will add the 24 hour time in the next version.



      As for the export of numbers as text, are you running the export from the web or from Notes? If it is run in Notes, it should come across as Text in the cell since I set the NumberFormat via OLE. I will investigate it on my end if I can find some data that I can use to test.

      • By Jan Schulz 2 decades ago

        I'm running from notes. I've tried the next RC, but still the same result: "01234" is going to excel as "1234" and the type is "Standard" (I suspect thats the german for General :-).



        Interestingly, the first two columns are text (like I defined them in the export), but after the next (defined as General) everything is "Standard". If I change this to text as well (every column is now text), also the ZIP Codes become text (Huray).



        So:

        "Text -> General -> Text" is in Excel "Text -> General -> General"



        Seems there is still a bug in the column properties. :-/



        BTW: Notes 7.0.2 (EN) Excel 2007 (DE), Win XP SP2 (DE).



        Nice greetings,



        Jan

        • Bug found

          By Jan Schulz 2 decades ago

          Ok, I found the bug:

          If I put the second column to "General", the SetColumnValue Function jumps to the ErrorProc Handler, when processing teh second column, so all other columns don't get the value set. The Error Message, which comes from Excel, is "Microsoft Office Excel: Die NumberFormat-Eigenschaft des Range-Objektes kann nicht festgelegt werden." (roughly: "The NumberFormat-Value of the Range-Object could not be set").



          Unfortunatelly there wasn't any error message, so I didn't look into the Error Log. The other improvment would be in the setColumnValue to catch the error there, so that when a problems occure, that the rest of the columns are set anyway.



          Unfortunately I've no idea, whats wrong with the 'General' Value :-) Setting it to "Standard" it works with my german 2007 excel, but I suspect it won't work with your english excel…



          Nice greetings,



          Jan

          • Investigating

            By Sean Burgess 2 decades ago

            It seems that this is an issue with the German version of Excel. I am going to try to find a way to determine if General or Standard is the correct NumberFormat at run time. I'll let you know what I find out.



            In the mean time, you can change the word General to Standard in the getNumberFormat function in the ASNDExportFacility Script Library.