Page 1 of 1

Export to excel data value format issue

Posted: Mon Jul 31, 2017 9:07 am
by 9526439
Hi,
I'm trying to export series data in excel from tee chart editor. On exporting the excel gets series data as x and y columns in format of two decimal places. I want is to export data in scientific notation.


I had partial success by providing the format in series tab ->general here i can provide format for the values.
1.png
1.png (10.72 KiB) Viewed 1862 times
2.png
2.png (6.77 KiB) Viewed 1860 times
But this changes the format for both columns of the series. what i want is normal default format for x values of the series and scientific format for the Y series in the exported excel.

Please someone guide me how to proceed

Re: Export to excel data value format issue

Posted: Tue Aug 01, 2017 10:42 am
by yeray
Hello,

Since the ValueFormat is a Series property, it is applied to both X and Y ValueLists. The only way around I can think on would be to use a temporal chart splitting each series into two series, one for the X values (with the default ValueFormat) and one for the Y values (with the modified ValueFormat). Ie:

Code: Select all

Private Sub Form_Load()  
  TChart1.Header.Text.Text = TChart1.Version
  
  TChart1.Aspect.View3D = False
  
  TChart1.AddSeries scLine
  TChart1.AddSeries scLine

  TChart1.Series(0).ValueFormat = "0.######0e-0"
  TChart1.Series(1).ValueFormat = "0.######0e-0"

  Dim i As Integer
  For i = 1 To 5
    TChart1.Series(0).AddXY i, Rnd * 100, "", clTeeColor
    TChart1.Series(1).AddXY i, Rnd * 100, "", clTeeColor
  Next i
End Sub

Private Sub Command1_Click()
  Dim tmpChart
  Set tmpChart = CreateObject("TeeChart.TChart")
  
  Dim i, y As Integer
  For i = 0 To TChart1.SeriesCount - 1
    tmpChart.AddSeries scPoint
    tmpChart.AddSeries scPoint

    For j = 0 To TChart1.Series(i).Count - 1
      tmpChart.Series(tmpChart.SeriesCount - 2).Add TChart1.Series(i).XValues.Value(j), "", clTeeColor
      tmpChart.Series(tmpChart.SeriesCount - 1).Add TChart1.Series(i).YValues.Value(j), "", clTeeColor
    Next j

    tmpChart.Series(tmpChart.SeriesCount - 2).YValues.Name = "X"
    tmpChart.Series(tmpChart.SeriesCount - 1).ValueFormat = TChart1.Series(i).ValueFormat
  Next i

  TChart1.Export.asXLS.IncludeHeader = True
  TChart1.Export.asXLS.UseSeriesFormat = True


  TChart1.Export.asXLS.SaveToFile "C:\tmp\axtest.xls"
End Sub
Worth to note here the export to .xlsx files by code, supporting Excel > 2010, hasn't been implemented yet in TeeChart ActiveX: #1670.