Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > asp & xls

Reply
Thread Tools

asp & xls

 
 
tom
Guest
Posts: n/a
 
      09-28-2004
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.


 
Reply With Quote
 
 
 
 
tom
Guest
Posts: n/a
 
      09-28-2004
Have u got any suggestions about to sort out the problem?
Cause I don't have a clues.
- cheers tommy


"tom" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>
>



 
Reply With Quote
 
 
 
 
Patrice
Guest
Posts: n/a
 
      09-28-2004
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" <(E-Mail Removed)> a écrit dans le message de
news:%(E-Mail Removed)...
> 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.
>
>



 
Reply With Quote
 
tom
Guest
Posts: n/a
 
      09-28-2004
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" <(E-Mail Removed)> wrote in message
news:%23%(E-Mail Removed)...
> 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" <(E-Mail Removed)> a écrit dans le message de
> news:%(E-Mail Removed)...
> > 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.
> >
> >

>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      09-28-2004
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" <(E-Mail Removed)> wrote in message
> news:%23%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> a écrit dans le message de
>> news:%(E-Mail Removed)...
>>> 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.


 
Reply With Quote
 
Patrice
Guest
Posts: n/a
 
      09-28-2004
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" <(E-Mail Removed)> a écrit dans le message de
news:uce$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> a écrit dans le message de
> > news:%(E-Mail Removed)...
> > > 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.
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
tom
Guest
Posts: n/a
 
      09-28-2004
*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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> a écrit dans le message de
> news:uce$(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%23%(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> a écrit dans le message de
> > > news:%(E-Mail Removed)...
> > > > 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.
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Andrew Zamkovoy
Guest
Posts: n/a
 
      09-28-2004
Hello,

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

With best regards,

"tom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Have u got any suggestions about to sort out the problem?
> Cause I don't have a clues.
> - cheers tommy
>
>
> "tom" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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.
> >
> >

>
>



 
Reply With Quote
 
Luis
Guest
Posts: n/a
 
      09-30-2004
On Tue, 28 Sep 2004 14:30:55 +0200, "tom" <(E-Mail Removed)> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sending xls file from Server to Client via asp.net Lars Netzel ASP .Net 1 11-10-2005 03:07 PM
Upload .xls file to SQL Server via ASP.NET app bubberz ASP .Net 1 08-06-2005 12:32 AM
Generating XLS files from ASP =?Utf-8?B?ZGFuaWVsZS5iYWxkdWNjaQ==?= ASP .Net 3 07-04-2005 06:30 PM
ASP.NET Reading problem (reading .xls) Wael Soliman ASP .Net 2 01-03-2005 05:33 PM
ASP.NET Excel download defaults to HTML with XLS extension A B ASP .Net 1 05-13-2004 06:18 PM



Advertisments