Export to excel data value format issue

TeeChart for ActiveX, COM and ASP
Post Reply
amol
Advanced
Posts: 231
Joined: Tue Mar 29, 2005 5:00 am

Export to excel data value format issue

Post by amol » Mon Jul 31, 2017 9:07 am

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 10619 times
2.png
2.png (6.77 KiB) Viewed 10617 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

Yeray
Site Admin
Site Admin
Posts: 9514
Joined: Tue Dec 05, 2006 12:00 am
Location: Girona, Catalonia
Contact:

Re: Export to excel data value format issue

Post by Yeray » Tue Aug 01, 2017 10:42 am

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.
Best Regards,
ImageYeray Alonso
Development & Support
Steema Software
Av. Montilivi 33, 17003 Girona, Catalonia (SP)
Image Image Image Image Image Image Please read our Bug Fixing Policy

Post Reply