asp & xls

Discussion in 'ASP General' started by tom, Sep 28, 2004.

  1. tom

    tom Guest

    hello lads -
    got a problem; I made a file that should write down a report into the excel
    cells.
    the issue is not coming from the sql or summat, but from excel file cannot
    convert
    the date properly.
    if I decided to select 2 dates: the starting date(August for istance) and
    the finishing date of the next month(September),
    what happens is the finishing date swaps the month with the day .
    For Example if I chose to have the report from the 29th August to the 3rd
    September what I will have in the cells, will be the following ones:
    29/08/2004
    30/08/2004
    31/08/2004
    09/01/2004
    09/02/2004
    09/03/2004



    ---> which means is going from the 29th of Aug to the 9th of March instead
    of the 3rd of Sept!!! thats no fair :\
    I reckon the problem is excel thats sets of default the american date.

    - regards tommy

    PS: I did set as well session.LCID = 1040 (italian one) and doesn't change.
     
    tom, Sep 28, 2004
    #1
    1. Advertising

  2. tom

    tom Guest

    Have u got any suggestions about to sort out the problem?
    Cause I don't have a clues.
    - cheers tommy


    "tom" <> wrote in message
    news:%...
    > hello lads -
    > got a problem; I made a file that should write down a report into the

    excel
    > cells.
    > the issue is not coming from the sql or summat, but from excel file cannot
    > convert
    > the date properly.
    > if I decided to select 2 dates: the starting date(August for istance) and
    > the finishing date of the next month(September),
    > what happens is the finishing date swaps the month with the day .
    > For Example if I chose to have the report from the 29th August to the 3rd
    > September what I will have in the cells, will be the following ones:
    > 29/08/2004
    > 30/08/2004
    > 31/08/2004
    > 09/01/2004
    > 09/02/2004
    > 09/03/2004
    >
    >
    >
    > ---> which means is going from the 29th of Aug to the 9th of March

    instead
    > of the 3rd of Sept!!! thats no fair :\
    > I reckon the problem is excel thats sets of default the american date.
    >
    > - regards tommy
    >
    > PS: I did set as well session.LCID = 1040 (italian one) and doesn't

    change.
    >
    >
     
    tom, Sep 28, 2004
    #2
    1. Advertising

  3. tom

    Patrice Guest

    What if you try the MM/JJ/AAAA format ? IMO it should be displayed correctly
    in Excel and Excel will be happy at import time. It could be even better to
    use named months to avoid an ambiguos format.

    Patrice

    --

    "tom" <> a écrit dans le message de
    news:%...
    > hello lads -
    > got a problem; I made a file that should write down a report into the

    excel
    > cells.
    > the issue is not coming from the sql or summat, but from excel file cannot
    > convert
    > the date properly.
    > if I decided to select 2 dates: the starting date(August for istance) and
    > the finishing date of the next month(September),
    > what happens is the finishing date swaps the month with the day .
    > For Example if I chose to have the report from the 29th August to the 3rd
    > September what I will have in the cells, will be the following ones:
    > 29/08/2004
    > 30/08/2004
    > 31/08/2004
    > 09/01/2004
    > 09/02/2004
    > 09/03/2004
    >
    >
    >
    > ---> which means is going from the 29th of Aug to the 9th of March

    instead
    > of the 3rd of Sept!!! thats no fair :\
    > I reckon the problem is excel thats sets of default the american date.
    >
    > - regards tommy
    >
    > PS: I did set as well session.LCID = 1040 (italian one) and doesn't

    change.
    >
    >
     
    Patrice, Sep 28, 2004
    #3
  4. tom

    tom Guest

    Cause first in Europe especially Italy we used that format, second my boss
    wants like that, third all the users are not so ready to switch into
    the american format.thats why, otherwise I would have done straight away.



    "Patrice" <> wrote in message
    news:%23%...
    > What if you try the MM/JJ/AAAA format ? IMO it should be displayed

    correctly
    > in Excel and Excel will be happy at import time. It could be even better

    to
    > use named months to avoid an ambiguos format.
    >
    > Patrice
    >
    > --
    >
    > "tom" <> a écrit dans le message de
    > news:%...
    > > hello lads -
    > > got a problem; I made a file that should write down a report into the

    > excel
    > > cells.
    > > the issue is not coming from the sql or summat, but from excel file

    cannot
    > > convert
    > > the date properly.
    > > if I decided to select 2 dates: the starting date(August for istance)

    and
    > > the finishing date of the next month(September),
    > > what happens is the finishing date swaps the month with the day .
    > > For Example if I chose to have the report from the 29th August to the

    3rd
    > > September what I will have in the cells, will be the following ones:
    > > 29/08/2004
    > > 30/08/2004
    > > 31/08/2004
    > > 09/01/2004
    > > 09/02/2004
    > > 09/03/2004
    > >
    > >
    > >
    > > ---> which means is going from the 29th of Aug to the 9th of March

    > instead
    > > of the 3rd of Sept!!! thats no fair :\
    > > I reckon the problem is excel thats sets of default the american date.
    > >
    > > - regards tommy
    > >
    > > PS: I did set as well session.LCID = 1040 (italian one) and doesn't

    > change.
    > >
    > >

    >
    >
     
    tom, Sep 28, 2004
    #4
  5. You need to discard the idea that you need to store the date in the same
    format in which you are going to display it. Format should be applied when
    displayig dates. Dates should be stored in an unambiguous format so that
    they can be queried correctly as dates.

    Bob Barrows
    tom wrote:
    > Cause first in Europe especially Italy we used that format, second my
    > boss wants like that, third all the users are not so ready to switch
    > into
    > the american format.thats why, otherwise I would have done straight
    > away.
    >
    >
    >
    > "Patrice" <> wrote in message
    > news:%23%...
    >> What if you try the MM/JJ/AAAA format ? IMO it should be displayed
    >> correctly in Excel and Excel will be happy at import time. It could
    >> be even better to use named months to avoid an ambiguos format.
    >>
    >> Patrice
    >>
    >> --
    >>
    >> "tom" <> a écrit dans le message de
    >> news:%...
    >>> hello lads -
    >>> got a problem; I made a file that should write down a report into
    >>> the excel cells.
    >>> the issue is not coming from the sql or summat, but from excel file
    >>> cannot convert
    >>> the date properly.
    >>> if I decided to select 2 dates: the starting date(August for
    >>> istance) and the finishing date of the next month(September),
    >>> what happens is the finishing date swaps the month with the day .
    >>> For Example if I chose to have the report from the 29th August to
    >>> the 3rd September what I will have in the cells, will be the
    >>> following ones: 29/08/2004
    >>> 30/08/2004
    >>> 31/08/2004
    >>> 09/01/2004
    >>> 09/02/2004
    >>> 09/03/2004
    >>>
    >>>
    >>>
    >>> ---> which means is going from the 29th of Aug to the 9th of March
    >>> instead of the 3rd of Sept!!! thats no fair :\
    >>> I reckon the problem is excel thats sets of default the american
    >>> date.
    >>>
    >>> - regards tommy
    >>>
    >>> PS: I did set as well session.LCID = 1040 (italian one) and
    >>> doesn't change.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Sep 28, 2004
    #5
  6. tom

    Patrice Guest

    This is not what I suggested.

    I meant that Excel expects IMO a particular format for dates *in* the import
    file and IMO this format is MM/JJ/AA.

    Once dates are loaded usual formatting rules will apply and dates will be
    displayed as usual for your country (I'm French and uses also JJ/MM/AA).
    Keep in mind that the problme with dates is that the internal representation
    is ok but when you write down a date you have mutliple ways to represent it.
    For historical reasons, Excel uses IMO the MM/JJ/AA as its "transport"
    format instead of other neutral format used by Internet...

    Give it a try and let us know.

    Patrice



    --

    "tom" <> a écrit dans le message de
    news:uce$...
    > Cause first in Europe especially Italy we used that format, second my boss
    > wants like that, third all the users are not so ready to switch into
    > the american format.thats why, otherwise I would have done straight away.
    >
    >
    >
    > "Patrice" <> wrote in message
    > news:%23%...
    > > What if you try the MM/JJ/AAAA format ? IMO it should be displayed

    > correctly
    > > in Excel and Excel will be happy at import time. It could be even better

    > to
    > > use named months to avoid an ambiguos format.
    > >
    > > Patrice
    > >
    > > --
    > >
    > > "tom" <> a écrit dans le message de
    > > news:%...
    > > > hello lads -
    > > > got a problem; I made a file that should write down a report into the

    > > excel
    > > > cells.
    > > > the issue is not coming from the sql or summat, but from excel file

    > cannot
    > > > convert
    > > > the date properly.
    > > > if I decided to select 2 dates: the starting date(August for istance)

    > and
    > > > the finishing date of the next month(September),
    > > > what happens is the finishing date swaps the month with the day .
    > > > For Example if I chose to have the report from the 29th August to the

    > 3rd
    > > > September what I will have in the cells, will be the following ones:
    > > > 29/08/2004
    > > > 30/08/2004
    > > > 31/08/2004
    > > > 09/01/2004
    > > > 09/02/2004
    > > > 09/03/2004
    > > >
    > > >
    > > >
    > > > ---> which means is going from the 29th of Aug to the 9th of March

    > > instead
    > > > of the 3rd of Sept!!! thats no fair :\
    > > > I reckon the problem is excel thats sets of default the american date.
    > > >
    > > > - regards tommy
    > > >
    > > > PS: I did set as well session.LCID = 1040 (italian one) and doesn't

    > > change.
    > > >
    > > >

    > >
    > >

    >
    >
     
    Patrice, Sep 28, 2004
    #6
  7. tom

    tom Guest

    *visdata is a function that converts a number to date(dd/mm/yyyy)


    For Each fld In rs.Fields
    if fld.name = "venditore" or fld.name=" " then
    xlSheet.Cells(1, col).Value = fld.name
    xlSheet.columns(col).NumberFormat = "@"
    else
    xlSheet.Cells(1, col).Value = cdate(visdata(fld.name))
    xlSheet.columns(col).ColumnWidth = 10
    end if
    col = col + 1
    Next

    sorted with this issue. the problem was to put a CDATE before scrolling the
    fields.
    - thanks to everyone




    "Patrice" <> wrote in message
    news:...
    > This is not what I suggested.
    >
    > I meant that Excel expects IMO a particular format for dates *in* the

    import
    > file and IMO this format is MM/JJ/AA.
    >
    > Once dates are loaded usual formatting rules will apply and dates will be
    > displayed as usual for your country (I'm French and uses also JJ/MM/AA).
    > Keep in mind that the problme with dates is that the internal

    representation
    > is ok but when you write down a date you have mutliple ways to represent

    it.
    > For historical reasons, Excel uses IMO the MM/JJ/AA as its "transport"
    > format instead of other neutral format used by Internet...
    >
    > Give it a try and let us know.
    >
    > Patrice
    >
    >
    >
    > --
    >
    > "tom" <> a écrit dans le message de
    > news:uce$...
    > > Cause first in Europe especially Italy we used that format, second my

    boss
    > > wants like that, third all the users are not so ready to switch into
    > > the american format.thats why, otherwise I would have done straight

    away.
    > >
    > >
    > >
    > > "Patrice" <> wrote in message
    > > news:%23%...
    > > > What if you try the MM/JJ/AAAA format ? IMO it should be displayed

    > > correctly
    > > > in Excel and Excel will be happy at import time. It could be even

    better
    > > to
    > > > use named months to avoid an ambiguos format.
    > > >
    > > > Patrice
    > > >
    > > > --
    > > >
    > > > "tom" <> a écrit dans le message de
    > > > news:%...
    > > > > hello lads -
    > > > > got a problem; I made a file that should write down a report into

    the
    > > > excel
    > > > > cells.
    > > > > the issue is not coming from the sql or summat, but from excel file

    > > cannot
    > > > > convert
    > > > > the date properly.
    > > > > if I decided to select 2 dates: the starting date(August for

    istance)
    > > and
    > > > > the finishing date of the next month(September),
    > > > > what happens is the finishing date swaps the month with the day .
    > > > > For Example if I chose to have the report from the 29th August to

    the
    > > 3rd
    > > > > September what I will have in the cells, will be the following ones:
    > > > > 29/08/2004
    > > > > 30/08/2004
    > > > > 31/08/2004
    > > > > 09/01/2004
    > > > > 09/02/2004
    > > > > 09/03/2004
    > > > >
    > > > >
    > > > >
    > > > > ---> which means is going from the 29th of Aug to the 9th of March
    > > > instead
    > > > > of the 3rd of Sept!!! thats no fair :\
    > > > > I reckon the problem is excel thats sets of default the american

    date.
    > > > >
    > > > > - regards tommy
    > > > >
    > > > > PS: I did set as well session.LCID = 1040 (italian one) and doesn't
    > > > change.
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    tom, Sep 28, 2004
    #7
  8. Hello,

    Why not to keep Date fields in Excel format: =DATE(YEAR, MONTH, DATE) ?

    With best regards,

    "tom" <> wrote in message
    news:...
    > Have u got any suggestions about to sort out the problem?
    > Cause I don't have a clues.
    > - cheers tommy
    >
    >
    > "tom" <> wrote in message
    > news:%...
    > > hello lads -
    > > got a problem; I made a file that should write down a report into the

    > excel
    > > cells.
    > > the issue is not coming from the sql or summat, but from excel file

    cannot
    > > convert
    > > the date properly.
    > > if I decided to select 2 dates: the starting date(August for istance)

    and
    > > the finishing date of the next month(September),
    > > what happens is the finishing date swaps the month with the day .
    > > For Example if I chose to have the report from the 29th August to the

    3rd
    > > September what I will have in the cells, will be the following ones:
    > > 29/08/2004
    > > 30/08/2004
    > > 31/08/2004
    > > 09/01/2004
    > > 09/02/2004
    > > 09/03/2004
    > >
    > >
    > >
    > > ---> which means is going from the 29th of Aug to the 9th of March

    > instead
    > > of the 3rd of Sept!!! thats no fair :\
    > > I reckon the problem is excel thats sets of default the american date.
    > >
    > > - regards tommy
    > >
    > > PS: I did set as well session.LCID = 1040 (italian one) and doesn't

    > change.
    > >
    > >

    >
    >
     
    Andrew Zamkovoy, Sep 28, 2004
    #8
  9. tom

    Luis Guest

    On Tue, 28 Sep 2004 14:30:55 +0200, "tom" <> wrote:

    >Have u got any suggestions about to sort out the problem?
    >Cause I don't have a clues.


    Instead of creating an XLS output file generate your output as XML and
    save it as a Excel file. Excel will be able to read it ans you can
    specify exactly what format you want for each column.

    To see what XML you'll require create the type of output that you want
    in an Excel spreadsheet and then save the spreadsheet as a HTML page.
    Then open the html page in a text editor and you'll get the idea. It
    will be quite "bloated" as Excel creates a whole bunch of unnecessary
    crap in the HTML page, but it will give you an idea how to approach
    it.
     
    Luis, Sep 30, 2004
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. A B
    Replies:
    1
    Views:
    2,923
    Peter O'Reilly
    May 13, 2004
  2. Wael Soliman

    ASP.NET Reading problem (reading .xls)

    Wael Soliman, Jan 3, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    4,808
    =?Utf-8?B?dmluYXk=?=
    Jan 3, 2005
  3. =?Utf-8?B?ZGFuaWVsZS5iYWxkdWNjaQ==?=

    Generating XLS files from ASP

    =?Utf-8?B?ZGFuaWVsZS5iYWxkdWNjaQ==?=, Jul 4, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    7,042
    =?Utf-8?B?bG9uZG9uIGNhbGxpbmc=?=
    Jul 4, 2005
  4. bubberz
    Replies:
    1
    Views:
    551
    Steve C. Orr [MVP, MCSD]
    Aug 6, 2005
  5. Lars Netzel
    Replies:
    1
    Views:
    1,131
    S. Justin Gengo
    Nov 10, 2005
Loading...

Share This Page