Date-Time not showing in correct format after export excel

[essar]

Chart in my application has Date-Time data on X axis. when i am exporting data to excel format, X-col data is not showing in proper date-time format. This application has been made in Asp
eg:
X axis value shown in chart : 16/6/2009 11:29:01 AM
After export to excel : 39980.47848

Kindly provide me solution to resolve this issue.

[Yeray]

Hi essar,

Here you have an answer to a customer who asked the same in the past.

[essar]

Hi Yeray,
Thanks for reply. I need to show heat-no on x axis. As heat no. is in varchar format for eg "D12345". My chart is not able to show this type of data on x axis.This data is being taken out by database. When i am trying to show any data other than of numerical or datetime, its showing me error that data is not in double format. I know i might be going wrong somewhere but still i need your help. do these type data can be shown on x axis. Kindly reply and thanks for all your prompt replies.

[Yeray]

Hi essar,

TeeChart needs doubles or integers (datetimes are also doubles) as X values to display the charts. If you don't want to set an irregular X interval, you can let TeeChart to add the values in X integer sequence (0, 1, 2, 3,...) without specifying any X. Then, if you have your values with a label (for example D12345), you could set your bottom axis to display your series labels instead of the X values:

Code: Select all

Chart1.Axes.Bottom.LabelStyle := talText;

If you need more precise help, please, try to arrange a simple example project we can run as-is here to reproduce the issue here and we'll try to give you some tips.

[essar]

Dear yeray,
Code given by you worked to some extent. I got fixed value on x axis but the value i got was like (1,2,3,4,...)...as you said in you previous reply. But i need the Heat no. values on x axis instead of these integer values. and heat no are also not fixed, it depends on user selection from the interface. Heat no. is also take from database. The sample of my code is some what like this:

Code: Select all

mQuery = "select heat_no,avg_cast_speed ,L2Speed from heat_report where heat_start_time >= to_date('06/06/2009 00:00:00','mm/dd/yyyy hh24:mi:ss') and heat_start_time <= to_date('06/06/2009 23:59:59','mm/dd/yyyy hh24:mi:ss') and caster_no=1"

 

                    mRSAim.CursorLocation = 3    //adUseClient

                                mRSAim.Open mQuery,mDBC,adOpenStatic

 

 

                document.graph.Chart1.ClearChart

                document.graph.Chart1.style.visibility = "visible"

                document.graph.Chart1.Environment.IEPrintWithPage=true

                document.graph.Chart1.Aspect.View3D = false

                

 

 

                for i=0 to mRSAim.fields.count -2

                                document.graph.Chart1.Axis.left.Automatic = true

                                

                                document.graph.Chart1.AddSeries(scBubble)

 

                                document.graph.Chart1.Series(i).DataSource = mRSAim

                                document.graph.Chart1.Series(i).YValues.ValueSource = mRSAim.fields(i+1).name

                                //document.graph.Chart1.Series(i).XValues.ValueSource = mRSAim.fields(0).name

                                document.graph.Chart1.Series(i).Title = mRSAim.fields(i+1).name                                                           

                                document.graph.Chart1.Axis.Bottom.Labels.Style = talValue

                next

Here i have commented x axis values, that i was taking prior to your reply and added the last line as u suggested. Kindly help me to rectify this code and achieve the desired results.

[Yeray]

Hi essar,

I'm not sure of what you should do because I still don't understand what are you trying to retrieve from your database. What data type is heat_no? Is that what you want as X values? Or you want a continuous X (like the integers 1, 2, 3,...) but showing heat_no string?

See this example:
If you have X values or dates in the field 0:

Code: Select all

document.graph.Chart1.Series(0).XValues.ValueSource = mRSAim.fields(0).name

If you don't get any X from database, the continuous integers will be used.

If you have Y values in the field 1:

Code: Select all

document.graph.Chart1.Series(0).YValues.ValueSource = mRSAim.fields(1).name

If you have labels in the field 2:

Code: Select all

document.graph.Chart1.Series(0).LabelsSource = mRSAim.fields(2).name
document.graph.Chart1.Axis.Bottom.Labels.Style = talText

If you don't get any labels from database nor set bottom axis labels style to show labels, the X values will be shown.

[essar]

Thanks Yeray,
Your last code gave me the exact answer i was searching for. I am highly impressed by your promptness. Thanks..

[Yeray]

Hi essar,

You are very welcome!
I'm glad to see that you solved it!