Need to export a plot of a large data set (>4000 points) to Excel

[AALP]

I'm currently using LabVIEW 7.1 full development version to create a data analysis application.  One function my customer would like is to be able to export the data, analysis, & graphs to an Excel file.  I currently have functional code using Report Generation Toolkit VIs, but the Excel_Insert_Chart.vi function (called via Excel Insert Graph.vi) errors when I try to export a plot of a very large data set.  The error code is -41114 and states "Graphs you create or edit in Microsoft Graph cannot contain more than 4,000 ros or columns...".  However, I can create a plot of the same data set manually in Excel using the chart wizard.

Why is there a size limit in the toolkit, but not when working directly in Excel?

I would like to avoid writting my own Excel interaction routines from scratch, as I have very limited time remaining to complete this project.

Is there an update and/or simple workaround to allow the export of a plot of a very large data set to Excel using the Report Generation Toolkit VIs?

[Darren]

This is a limitation of the ActiveX interface to Excel, and to my knowledge, there is no workaround for sending the entire large set of data.  I recommend you use the Decimate 1D Array function (on the Array palette) to decimate the data before you send it to the Excel graph.  Alternatively, you can start with an Excel template that already has a graph configured to contain a range of data from the spreadsheet, then just use the Append Table to Report VI to place your raw data in the configured range in the spreadsheet...I've never tried that approach, but I imagine a graph should update if new data is placed in its configured range.

-D

[Joe_H]

Or another suggestion would be to create a macro for the creation of the graph in the excel template and then use the report generation toolkit to call the macro each time you generate the report. this way you are not using the active X interface, which has this limitation and only using excel, which does not.

[AALP]

Darren

I understand the use of the Decimate 1D Array function to split the data set into smaller, manageable pieces, but then you have a number of decimated output arrays and I don't understand how you would reconstitute them into a single graph on the Excel side with the proper element order?  If you called the Excel Insert Graph successively for each of the decimation output arrays, wouldn't it just produce multiple graphs?  Is there some kind of interleave function built into one of the Excel graphing VIs?

I don't think I can use a preconfigured template graph because the size of the data set is always variable, as it depends on a date range specified by the user and the number of records per date is not predictable.  Also, the user wants the ability to specify the file into which the report should be generated.

I would appreciate any further input you may have.

[Darren]

The reason I suggested the Decimate 1D Array function is that usually, when you're plotting something, you're looking at the overall appearance of the data.  If you took an array of 4000 points that looked like a sine wave, and you removed every other point, the resulting 2000 points would still look like a sine wave...in fact, if you were viewing a plot of it, you probably wouldn't be able to tell a difference, given that your screen resolution is probably not greater than 2000 pixels in width, and the smallest amount of screen space your points can take up is 1 pixel.  So to clarify my suggestion, you should try using Decimate 1D Array to divide the array into segments, such that one segment is less than 4000 points, then send that segment to your Excel graph.  I don't know the nature of your data, but you can try it and see if there is any noticeable difference in the appearance of your graphs.

Note that if you're using Excel for anything other than visualization (like if you're analyzing the data in Excel), this suggestion is not appropriate.  In that case, you may want to investigate the macro approach suggested earlier.

Good luck,
-D

[Michael_Munroe]

<< This is a limitation of the ActiveX interface to Excel  >>

This is a limitation of Microsoft Graph which stores the data array in the graph and not on the worksheet.  The Insert Chart function uses MS Graph and not Excel to creat the chart. 

As mentioned  by Jhoskins, the best bet is to record a macro to create the chart and run it with the toolkit.

[Darren]

Right, the Microsoft Graph functionality has been used by the Report Generation Toolkit since version 1.0.  We should probably evaluate other methods of generating graphs (if any reasonable ones exist) for a future version of the toolkit.

-D