We help IT Professionals succeed at work.

Question on : Excel Chart automation with VC++

jubinedachery
on
2,957 Views
Last Modified: 2007-12-19
Hi,
I'm new to Office automation with VC++. I wanted to create a graph in Excel from my VC++ program. I looked around and found some code at some coding site and used it with my objects. I have created Excel objects from the type library automatically created classes:

---------------------------

CApplication oExcel; // this class is an auto created wrapper for _Application object
CWorkbook oBook;  // same for this and so on....

CWorkbooks oBooks;
CWorksheets oSheets;
CWorksheet oSheet;
CRange oRange;
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);

// If you have not created Excel, create a new instance.
if (oExcel.m_lpDispatch == NULL) {
         oExcel.CreateDispatch("Excel.Application");
}

// Show Excel to the user.
oExcel.put_Visible(TRUE);
oExcel.put_UserControl(TRUE);

// Add a new workbook and get the first worksheet in that book.
oBooks = oExcel.get_Workbooks();
oBook = oBooks.Add(covOptional);
oSheets = oBook.get_Worksheets();
oSheet = oSheets.get_Item(COleVariant((short)1));      

// Get a range of data.
oRange = oSheet.get_Range(COleVariant("A1"),covOptional);
oRange = oRange.get_Resize(COleVariant((short)5),COleVariant((short)5));

// here i'm putting some int values into the sheet just for testing the graph
COleSafeArray saRet;
DWORD numElements[2];
numElements[0] = 5;
numElements[1] = 5;
long index[2];

// Create a double safe array
saRet.Create(VT_R8,2,numElements);

// Fill the array with data.
for (int iRow = 1; iRow <= 5; iRow++) {
          for (int iCol = 1; iCol <= 5; iCol++) {
      index[0]=iRow-1;
      index[1]=iCol-1;
      double d = iRow + 2*iCol;
      saRet.PutElement(index,&d);
           }
}
// Send the array to Excel.
oRange.put_Value(COleVariant(saRet));

// now create the graph      

CChartObjects ccobj(oSheet.ChartObjects(covOptional));
      
int left = 100;
int top = 100;
int width = 350;
int height = 250;

CChartObject chartobject = ccobj.Add(left, top, width, height);

CChart chart(chartobject.get_Chart());

LPDISPATCH lpdisp = oSheet.get_Range(COleVariant("A1"), COleVariant("B5"));
// Range containing the data to be charted.

CRange range(lpdisp);
range.Select();
range.Activate();
VARIANT var; // ChartWizard needs a Variant for the Source range.
      
var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT
// Struct. Its value is a union of options.
var.pdispVal = lpdisp; // Assign IDispatch pointer
// of the Source range to var.
COleVariant covTF = COleVariant((short)FALSE);

chart.ChartWizard(     var, // Source.
            COleVariant((short)3), // Gallery = xy. (?? where do we get this number)
            COleVariant((short)1), // Format, use default. (?? same here and so on)
            COleVariant((short)2), // PlotBy xlColumns.
            COleVariant((short)1), // CategoryLabels.
            COleVariant((short)0), // SeriesLabels.
            covTF, // HasLegend. // i understand this - means no legend
            COleVariant("Title"), // Title. (ok, this means set the title as "Title")
            COleVariant("Frequency"), // CategoryTitle.
            COleVariant("Whatever"), // ValueTitles.
            covOptional // ExtraTitle.
      );
-------------------------

Ok. This creates the chart. The problem is, there is absolutely no documentation on the objects here for vc++ - i havent seen any. I went thru the mfc support links and found some sample code but no documentation.

I dont know how to change this chart or change the color or manipulate it. i can guess with the function names and tried chaning the integer values in the chatwizard and it produces weird results at times and errors at times. Does anyone have any documentation on these functions.
Is there any sample code i can use to see how to manipulate charts? And can i select range from different places in excel instead of the initial range (A5-B5 - here)?

Any help is greatly appreciated.
Thanks a lot,
Jubin
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for your answer. Do you know we can run VB code in VC++. That should then solve my problem.
Thanks
Jubin
Do I get the points?

Author

Commented:
Sure. I didnt get the answer with vc++ put your answer gave me some other pointers to work on. Thanks a lot for that. I'll give you the points.
Thanks and happy coding.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.