Excel as a DataSource for Line Series

TeeChart for ActiveX, COM and ASP
Post Reply
nbp
Newbie
Newbie
Posts: 24
Joined: Tue Apr 28, 2015 12:00 am

Excel as a DataSource for Line Series

Post by nbp » Wed Sep 16, 2015 10:37 pm

I want to add a feature to import Excel data into a line series. Where is the documentation for the API? I'm using Visual Studio 2013, C++, TeeChart Pro ActiveX 2015.

Also, if you could point me to a tutorial or anything in the feature demo.

Regards.

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

Re: Excel as a DataSource for Line Series

Post by Yeray » Thu Sep 17, 2015 11:05 am

Hello,

You can do it at designtime, selecting "Excel" as datasource for the series:
AXfromXLS.png
AXfromXLS.png (15.05 KiB) Viewed 22845 times
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

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

Re: Excel as a DataSource for Line Series

Post by Yeray » Thu Sep 17, 2015 11:45 am

Hello again,

Here an example loading data from an xls at runtime with C#:

Code: Select all

        private void Form1_Load(object sender, EventArgs e)
        {
            axTChart1.AddSeries(TeeChart.ESeriesClass.scBar);

            OleDbConnection dbConnection;
            OleDbDataAdapter dbAdapter;

            //Connection string to the Excel spreadsheet. HDR states that the sheet has row headings. 
            string conString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=F:\\Downloads\\ImportExcelSheet_AX_CSharp\\charts.xls;" +
                "Extended Properties='Excel 8.0;HDR=Yes'";

            try
            {
                dbConnection = new OleDbConnection(conString);
                dbConnection.Open();

                dbAdapter = new OleDbDataAdapter("SELECT * FROM [Charts.CSV.$]", dbConnection);
                DataSet ds = new DataSet();
                dbAdapter.Fill(ds, "MyData");
                int i = 0;
                axTChart1.Axis.Bottom.Labels.Clear();
                foreach (DataRow dbRow in ds.Tables["MyData"].Rows)
                {
                    if (dbRow[0].ToString() != "")
                        axTChart1.Series(0).Add(Convert.ToDouble(dbRow[2]), dbRow[1].ToString(), Convert.ToUInt32(TeeChart.EConstants.clTeeColor));
                    axTChart1.Axis.Bottom.Labels.Add(i, dbRow[0].ToString());
                    i++;
                }
                axTChart1.Axis.Bottom.Labels.Angle = 90;
                ds.Dispose();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (InvalidOperationException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //
            }
        }
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

nbp
Newbie
Newbie
Posts: 24
Joined: Tue Apr 28, 2015 12:00 am

Re: Excel as a DataSource for Line Series

Post by nbp » Thu Oct 01, 2015 4:14 pm

I need to have a C++ equivalent for OleDBConnection as the rest of my code is in C++ not C#.

I cannot link/compile with "System.data.OleDB" What is the alternative? Is there an API that is available via TChart classes that I can use directly instead of going through OLDB?

Regards.

nbp
Newbie
Newbie
Posts: 24
Joined: Tue Apr 28, 2015 12:00 am

Re: Excel as a DataSource for Line Series

Post by nbp » Thu Oct 01, 2015 9:58 pm

Can I possibly do it with the Series.SetDataSource() call instead of OleDB?

If so, how can I set up the connection string etc.?

I would like to see an example.

Thanks.

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

Re: Excel as a DataSource for Line Series

Post by Yeray » Fri Oct 02, 2015 1:11 pm

Hello,

I'm not sure to understand why can't you use OleDbConnection. According to this, it should be able to use OleDbConnection in C++.
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

nbp
Newbie
Newbie
Posts: 24
Joined: Tue Apr 28, 2015 12:00 am

Re: Excel as a DataSource for Line Series

Post by nbp » Fri Oct 02, 2015 10:11 pm

Thanks for the reply.

Yes, it looks like we can use OldDBConnection class in C++ from the above link. However, I am still having issues. What do I need to include or what reference to do I need to add to my Visual C++/MFC project so that I can use the OleDB objects? I have the following:

#include <oledb.h>

OleDbConnection myConnection;

I get an undefined object for OleDbConnection.
error C2065: 'OleDbConnection' : undeclared identifier

If I try to add ATL support to my MFC project I get an error saying "ATL support can only be added to MFC exes or MFC Regular DLLs'

Not sure what is going on. Any help would be appreciated.

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

Re: Excel as a DataSource for Line Series

Post by Yeray » Mon Oct 05, 2015 3:30 pm

Hello,
nbp wrote:error C2065: 'OleDbConnection' : undeclared identifier
Here it is a user with a similar error that found a way to make it work:
http://www.tech-archive.net/Archive/Dat ... 00011.html

I hope it will help.
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

nbp
Newbie
Newbie
Posts: 24
Joined: Tue Apr 28, 2015 12:00 am

Re: Excel as a DataSource for Line Series

Post by nbp » Thu Oct 15, 2015 6:47 pm

Thanks. That helped. I was able to progress. However, I've run into some issues. I needed to compile my DLL that references TChart objects with /clr in order to add references to "system" and "system.data" and use OleDB components. So my DLL is now mixed mode. My calling program is native and when it loads this DLL, I ran into the loader lock problem when loading mixed dlls or a combination of managed and native DLLs. I got past this issue by adding the /NoEntry option to the linker.

But now I'm running into a problem allocating memory while initializing TChart. It seems like I've got into an endless loop of changing compiler/linker settings in my DLL that was originally a native DLL, in order to access OleDB components.

Is there any way around this? Is there any plan in the future to provide a TChart class (API) for Excel import that we can interface with instead of going via the OleDB route? This would be a much cleaner approach for us.

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

Re: Excel as a DataSource for Line Series

Post by Yeray » Fri Oct 16, 2015 11:46 am

Hello,

I've added it to the public tracker:
http://bugs.teechart.net/show_bug.cgi?id=1342
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

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

Re: Excel as a DataSource for Line Series

Post by Yeray » Tue Aug 23, 2016 7:32 am

Hello,
Yeray wrote: I've added it to the public tracker:
http://bugs.teechart.net/show_bug.cgi?id=1342
Studying what impact of implementing such feature, we realized a simple example we can run as-is would help us to see how are you exactly doing this and it would allow us to debug the sources to see if there's a bug in TeeChart ending in the allocating memory problem you described:
nbp wrote:But now I'm running into a problem allocating memory while initializing TChart.
So could you please try to arrange a simple example project we can run as-is to reproduce the problem here?
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