Exporting date data to excel

[JAV]

When I export a chart to Excel, values are exported in X & Y, how can I customize the column headers? How can I add rows at the beginning to place information? What can I keep the date-type format because date data are exported as numbers?

[Yeray]

Hello JAV,

Some of this has been discussed here
Here is an example:

Code: Select all

Private Sub Form_Load()  
  TChart1.Aspect.View3D = False

  TChart1.AddSeries scLine

  TChart1.Series(0).XValues.DateTime = True
 
  Dim month, year, i As Integer
  i = 0
  For year = 2003 To 2009
    For month = 1 To 12
      TChart1.Series(0).AddXY DateValue("1/" & Str$(month) & "/" & Str$(year)), Sin(i / 10), "", clTeeColor
      i = i + 1
    Next month
  Next year
  
  TChart1.Series(0).Title = "Series title"
  TChart1.Series(0).XValues.Name = "X Values"
  TChart1.Series(0).YValues.Name = "Y Values"
  
  With TChart1.Export.asText
    .IncludeHeader = True
    .IncludeIndex = True
    .SaveToFile "C:\tmp\test1.xls"
  End With
  
  With TChart1.Export.asXLS
    .IncludeHeader = True
    .IncludeIndex = True
    .SaveToFile "C:\tmp\test2.xls"
  End With
End Sub

How can I add rows at the beginning to place information?

I'm afraid it's not possible right now. I've added it to the wish list to be implemented in future releases (TV52015584).

[JAV]

Hello Yeray:

I reviewed the demo and I've searched in the forum but I cannot export to excel as a date format, it continues exporting as a number (See attached file Fecha_Ventas.jpg)

I am using the following code with TeeChart8.ocx:

Code: Select all

tChart1:Series(0):VerticalAxis = 0.
tChart1:Series(0):XValues:DateTime = TRUE.
tChart1:Series(0):XValues:NAME = "Fecha".
tChart1:Series(0):YValues:NAME = "Valor total".
FOR EACH ventas:
    tChart1:Series(0):ADDXY(ventas.fecha, ventas.valor_total, “ “, 0).
END:

tChart1:Axis:Bottom:Visible  = TRUE.
tChart1:Axis:Bottom:Automatic = TRUE.
tChart1:Axis:Bottom:Title:Caption = "Fecha".
tChart1:Axis:RIGHT:Visible = TRUE.
tChart1:Axis:RIGHT:Automatic = TRUE.
tChart1:Axis:RIGHT:Title:Caption  = "Total".
tChart1:Axis:Left:VISIBLE  = FALSE.
tChart1:Axis:TOP:Visible  = FALSE.

tChart1:EXPORT:asXLS:IncludeHeader = TRUE.
tChart1:EXPORT:asXLS:IncludeIndex  = FALSE.
tChart1:EXPORT:asXLS:IncludeLabels = TRUE.
tChart1:EXPORT:asXLS:Series.
tChart1:EXPORT:asXLS:SaveToFile ("C:\Temp\Fecha_Ventas.xls").

Additionally, now I have a new problem: I have three series referring to the x and y axis (left), and one serie with the x and y axis (right). All series in the same chart. The 3 axes are numerical (bottom, right, left); when I exported to excel only the y-axis coordinates are exported, how can I export the x-axis values? (See attached file 2_ejes_y.jpg)


Best regards,

JAV

[Yeray]

Hello JAV,

I reviewed the demo and I've searched in the forum but I cannot export to excel as a date format, it continues exporting as a number (See attached file Fecha_Ventas.jpg)

Have you seen the summary here of the possibilities available?

Additionally, now I have a new problem: I have three series referring to the x and y axis (left), and one serie with the x and y axis (right). All series in the same chart. The 3 axes are numerical (bottom, right, left); when I exported to excel only the y-axis coordinates are exported, how can I export the x-axis values? (See attached file 2_ejes_y.jpg)

Have you added XValues? If you use the Add method (so you don't add XValues) the XValues will be 0, 1, 2,... and they won't be exported.
However, if you use the AddXY method to add XValues manually, they seem to be exported correctly for me here:

Code: Select all

Private Sub Form_Load() 
  TChart1.Aspect.View3D = False
  Dim i As Integer
  For i = 0 To 6
    TChart1.AddSeries scLine
    With TChart1.Series(i)
      .AddXY 0, Rnd * 100, "", clTeeColor
      Dim j As Integer
      For j = 1 To 20
        .AddXY j * i, .YValues.Value(j - 1) + Rnd * 10 - 5, "", clTeeColor
      Next j
    
      If i > 2 Then
        .VerticalAxis = aRightAxis
      End If
    End With
  Next i
  
  TChart1.Export.asText.SaveToFile "C:\tmp\test.xls"
End Sub

[JAV]

Indeed, I am using AddXY to generate all series. When X-axis is set as date format, the data is exported correctly, I mean, each serie with their own X and Y value, but if x-axis format is changed to numerical format, the X data is not exported, only Y values.
Please your help, I am getting crazy. I am using TeeChart8.ocx and Excel 2007, just in case...

[Yeray]

Hello JAV,

Are the X Values 0, 1, 2,...? If that's the case you could add the indexes:

Code: Select all

TChart1.Export.asText.IncludeIndex = True

[JAV]

This solution could work with the longest series but not all the series that we are plotting have the same value ​​on the x-axis or the same amount of data, for example: Series 1 : (1, 89), (2, 56), (3, 90), etc ... and the series 2: (12, 110), (31, 67). Therefore it is necessary to export each serie completed, with data in X and Y.
Thank you very much for your time and support,

Best Regards,


JAV
--------

Esta solucion podria funcionar con la serie mas larga o con la serie con mayor cantidad de puntos, pero no ocurre porque las series que estamos graficando no tienen ni los mismos valores en el eje x ni la misma cantidad de datos, por ejemplo: serie 1: (1;89), (2;56), (3;90), etc... y la serie 2: (12;110), (31;67). Por lo tanto es necesario que cada serie se exporte completa, con datos en X y en Y.

Muchisimas gracias por tu tiempo y ayuda. Saludos cordiales,

JAV

[Narcís]

Hi JAV,

In that case I'm afraid the only solution is exporting each series individually, for example:

Code: Select all

    TChart1.Export.asXLS.Series = TChart1.Series(0)

I have also added your request (TV52015617) to the wish-list to be considered for inclusion in future releases.

[JAV]

Dear Narcis,

We did what you suggested, we export a serie at once but it didn't work. In this case only one column, Y axis values, was exported.
Just for trying, we increased in 1 each X value of serie 0 and with this change this were successfully exported. Even when exporting all series in the chart with this modification, entire series were exported properly, as shown in the attachment, but column A should be identical to column G, ie it must start at 0. What could be happening? Please note that if serie 0 in X axis starts at 0 we get any column X in every series exported.

Best regards,

JAV

[Yeray]

Hello JAV,

I think the problem is that, when the first series has XValues 0, 1, 2, 3, ..., the XValues aren't exported for any series, even when the other series have different XValues. This possibility will be investigated when the ticket TV52015617 will be addressed.