MFC GDI+ ActiveX Arrow Control For Excel

• Download source files - 51 Kb
• Download demo project - 33 Kb

Introduction

Hello everyone, this is my first article here. I have gained a lot from The Code Project and it is my time to give something here. What is this article about? It is just about a simple control on Excel so that the position of the arrow in the dashboard will change whenever the value of a cell changes. That means, the control is linked with the cell in Excel. From the figure, you can see that whenever the slider changes its position, the arrow will change its position too. It is because the cell value "A1" is changed by the slider, and so, because my control is linked to the cell, the position of the arrow in my control is changing too. So, don't be confused that my control is linked with the slider. It is indeed linked with the cell "A1". It is not a great article or application. After reading this article, I hope that you will learn the following few things:

1. How to use GDI+?
2. How to create ActiveX applications using MFC 6.0?
3. How to add properties and link cell to Excel?
4. How to use what we learnt in secondary school mathematics (Geometry, I studied in Hong Kong)?

Background

Why there is such an application? I have been a programmer since my university time, and been a professional C++ programmer after graduation. One day, my lovely boss asked me to create a dashboard control in Excel so that users of Excel will always be notified by such a control of how their cell values are changing. Useful or not? For me, I don't think it is useful, but it was a challenge for me. However, for some business, like some resource planning software, data is important, and if there are over hundreds of values, a manager can't just use his eyes to clarify which resources are under critical condition. However, with the help of dashboard, they can be notified easily and they can make decisions right away. This application is just the first step. After I created the application, I started to create a traffic light which needed multi-threading technology to flash users for their greater attention. I will not post the code of the traffic light here, let's see how the reader response to what is here first ;). That is all why there is such a control. I know nothing about ActiveX, Excel programming, COM... etc. I can only find very little resources talking about this on the Internet. So, from the learning phase to the design phase, to the implementation phase, all was stiff work to me and gave me a very hard time. I hope everyone reading this article can be benefited.

How to use

1. Extract the file to some location that you know.
2. Open a command prompt (by choosing "Start"->"Run" and type "cmd" in the pop up dialog ... sorry, I am not treating you guys as fools here ... but there maybe someone you know ... ).
3. In the command prompt, "cd" to the directory that contains the file.
4. Type "regsvr32 <filename>", in this case, type "regsvr32 ActiveXArrow.ocx" to install the ActiveX Control.
5. To show it in Excel, open Excel first.
6. Show the "control toolbars" by clicking on the menu "View"->"Toolbars"->"Control Toolbars".
7. A new toolbar is shown, try to find the "Hammer" like icon on the toolbar and click on it.
8. A list of controls in text format is shown, locate the item named "ActiveXArrow Control" and click on it.
9. Good, you did it. Now, your mouse icon should become a cross rather than an arrow. Try to drag a square on the Excel sheet and a control will be shown.
10. Right click on the control, and in the popup menu, try to choose "Properties".
11. A Properties list box is shown. Try to find the item "LinkedCell" in the list box and type "A1" on its properties. (Remember to push "enter" once you typed it, to activate the changes).
12. On the "Control Toolbars", there is a ruler like icon (always on the top left corner), click on it to finish the design phase (before you are designing).
13. Try to change the value of cell "A1", and see if there are changes to the arrow position of the control. (There should be, otherwise, re-do the process again).

    In order to uninstall the control, you have to type "regsvr32 /u ActiveXArrow.ocx" in the command prompt described in step 2..

Using the code

Before talking about the code, I would like to introduce the structure of the whole control first. When you open the project using VC++ 6.0, you will find that there are a lot of classes. For those who are new to ActiveX, it may look strange. In fact, there are only three main classes that implement the main features of the controls. They are "CActiveXArrowCtrl", "CArrowObj", and "CPieForm".

CActiveXArrowCtrl is the main class to handle the drawing of the control. You can find there is a member class function:

void CActiveXArrowCtrl::Draw(CDC* pdc, const CRect& rcBounds, CRect* rcClip)

and inside the function, the following three lines of code handle the most complicated drawing of the arrow:

m_pieFormObj.SetGraphic(g);
m_pieFormObj.DrawPie(rcBounds, FALSE, TRUE);
m_pieFormObj.DrawArrow(m_angle, TRUE);

Of course, it is not just that simple. GDI+ will draw the image in EMF format, but that is not compatible with the printing structure of Excel (as Excel can only recognize image in WMF format). So, we have to find a way to convert the GDI+ image to WMF format. The full code of Draw function can be found below:

// pdc is the device context of the drawing area, that is,
// what you drag on the excel worksheet
// rcBounds is the rectangle of the drawing area,
// with (0,0) at the top left corner

void CActiveXArrowCtrl::Draw(CDC* pdc, const CRect& rcBounds, CRect* rcClip)
{
  // Rect is a GDI+ object
  Rect oRect(rcBounds.left, rcBounds.top, rcBounds.right, rcBounds.bottom);

  TCHAR lpBuffer[256];
  DWORD len = ::GetTempPath(256, lpBuffer);
  lpBuffer[len]= '\0';
  CString stemp;
  stemp.Format(_T("%s"), lpBuffer);

  // create the emf file name
  CString path = stemp + _T("h") + m_myUID + _T("e.emf");

  // create the emf object using the filename
  Metafile* myMeta = new Metafile(path, pdc->m_hDC);

{
 // create the gdi+ graphic object and draw the image
 // on the emf object created just before
 Graphics* g = new Graphics(myMeta);
 g->SetSmoothingMode(SmoothingModeAntiAlias);

 // draw the image
 // if the m_BkImage have path exist
 {
   if(m_BkImage != _T(""))
   {
     // create the background image from the specified image path
     // (m_BkImage store the path of the background image)
     Image* img = new Image(m_BkImage.GetBuffer(m_BkImage.GetLength()));

     Status st;
     st = g->DrawImage(img, oRect);
     if(st != Ok)
     {
       // if fail to create the background img, try to create the background
       // using the resources file
       Bitmap* img2 = Bitmap::FromResource(AfxGetApp()->m_hInstance, 
                                    MAKEINTRESOURCE(IDB_BITMAP_BK));
       g->DrawImage(img2, oRect); 

       delete img2;
     }
     delete img;
  }
  // if there is no path exist, just create
  // the image from the resources file
  else
  {
    Bitmap* img = Bitmap::FromResource(AfxGetApp()->m_hInstance, 
                  MAKEINTRESOURCE(IDB_BITMAP_BK));

    if(!img)
      AfxMessageBox(_T("fail to load bitmap"));

    g->DrawImage( img, oRect);
  } 

  // succeed to draw the background,
  // now, is the time to draw the arrow ... 
  m_pieFormObj.SetGraphic(g);

  m_pieFormObj.DrawPie(rcBounds, FALSE, TRUE);
  m_pieFormObj.DrawArrow(m_angle, TRUE);
}
delete g;
}

delete myMeta;

// OK, now, we succeed to draw all the things, however,
// all are in emf format and stored in the file "path"
// we have to load it using GDI method and so, it will be
// in wmf format and excel can print it out ~ 

// create the Bitmap object from the path
Bitmap mybitmap(path.GetBuffer(path.GetLength()));

// get the bitmap handle
HBITMAP hbm = NULL;
mybitmap.GetHBITMAP(NULL, &hbm);

if(!hbm)
{
  // AfxMessageBox(_T("fail to get hbm"));
  // if fail to get the handle, mean there is no such file,
  // just load a default image from resources
  Bitmap* img = Bitmap::FromResource(AfxGetApp()->m_hInstance, 
                MAKEINTRESOURCE(IDB_BITMAP_BK));

  if(!img)
    AfxMessageBox(_T("fail to load bitmap"));

// Rect rect2(0, 0, rcBounds.BottomRight().x, rcBounds.BottomRight().y);

// g.DrawImage(
// img, 
// rect2);

img->GetHBITMAP(NULL, &hbm);
}

// create a DC, but don't create it in any device context
// but system display
CDC memDC;
memDC.CreateCompatibleDC( NULL );


// re-draw it ... all are straight forward .. 
//memDC.SelectObject( &bitmap );
HBITMAP hBmOld = (HBITMAP)::SelectObject( memDC.m_hDC, hbm );

// Get logical coordinates

BITMAP bm;
::GetObject( hbm, sizeof( bm ), &bm );


if(!rcClip)
  pdc->StretchBlt(rcBounds.left, rcBounds.top,
                     rcBounds.Width(), rcBounds.Height(),
                     &memDC, 
                     0, 0, bm.bmWidth, bm.bmHeight, SRCCOPY);
else
{
  pdc->SetStretchBltMode(STRETCH_DELETESCANS);
  pdc->StretchBlt(rcClip->left, rcClip->top, 
                     rcClip->Width(), rcClip->Height(), 
                     &memDC, rcClip->left, rcClip->top, 
                     rcClip->Width(), rcClip->Height(), SRCCOPY);

}
::SelectObject( memDC.m_hDC, hBmOld );

::DeleteObject(hbm);
::DeleteObject(hBmOld);
::DeleteObject(memDC); 
}

How to use GDI+

When I first designed the projects, I was thinking which graphic library I should use. In using MFC, DirectX, OpenGL, GDI, GDI+ can be employed. Finally, I chose to use GDI+ as what I wanted to show to the user at most just a transparent arrow. In using GDI+, I am benefited by one of the CodeProject contributors (Author: Ryan Johnston, see article). He does all the trouble work in initializing the GDI+ library for us. Thanks a lot. In initializing the GDI+ library, what we have to do is just create a member variable using his class and call just a few lines of code to do the initialization, that is, see below:

// ... in stdafx.h, declare sth below

#include <gdiplus.h>

#pragma comment(lib, "gdiplus.lib")

using namespace Gdiplus;

// In class declaration
#include "GDIpInitializer.h"

class CActiveXArrowCtrl : public COleControl
{
public:
 CGDIpInitializer m_gdip;
...
};

// In class definition

CActiveXArrowCtrl::CActiveXArrowCtrl()
{
 ... 
 m_gdip.Initialize();
 ...
}

CActiveXArrowCtrl::~CActiveXArrowCtrl()
{
 m_gdip.Deinitialize();
}
 
// then, you can see that I declared
// a graphic object at the ::Draw function
void CActiveXArrowCtrl::Draw(CDC* pdc, 
     const CRect& rcBounds, CRect* rcClip)
{
...
    Graphics* g = new Graphics(myMeta);
    // this is the gdi+ graphic library
...
}

How to create ActiveX application using MFC 6.0

It is simple, I am benefited from a book called "ActiveX Inside Out" (something like that, I forgot the exact name). It is a very good book. For anyone who want to learn ActiveX, I recommend this book. OK, below are the steps to create an ActiveX application using MFC 6.0. The procedure is specific to this application only, for other kinds of ActiveX controls, there may be something different.

1. Open VC++ 6.0.
2. Click "File"->"New", and choose "MFC ActiveX Control Wizard" in the project list, type the project name. In here, let's set the name as "MyFirstTest".
3. On the first popup wizard, just click "Next", leave all the things to be default.
4. On the next wizard, you will see a button "Advanced", click on it and there will be a popup dialog, and you should enable the option "Flicker-Free Activation".
5. Click Finish. You did it! ... Try to compile the projects first.
6. Run Excel and do the same steps as creating my control to create your "MyFirstTest" object in Excel. You will see an ellipse drawn on the Excel sheet.

How to add properties and link cell to Excel

If you succeed to create your "MyFirstTest" ActiveX control, you can try to right click the control and choose "Properties". You will find that there are some default properties. However, you will never see the properties "LinkedCell" as shown in my control's properties list and also other properties like "Max", "Min" .. etc. In order to add custom properties, you have to follow the steps below:

1. Press "Ctrl-W" to popup the class wizard.
2. Choose the page "Automation".
3. Click on the button "Add Property ..."
4. On the popup dialog, in the field "External name", try to type "Max". In the field "Type", choose it to be type "long".
5. In the middle of the dialog, choose "Get/Set Method" rather than the default "Member variable".
6. Click OK.
7. Then, you will see there is a new property added. You can try to create your new control in Excel and right click to see if the new property was added. However, you may find that the property was not added in Excel Properties list, why?? It took me a week to find the reason. The answer is that, MS Excel will always save a copy of Properties list to somewhere in your hard disk; whenever you create a control, it will try to load the Properties list from that location rather than from the control. Therefore, you have to delete the file. The file is always located in "C:\DOCUME~1\LIU\LOCALS~1\Temp\Excel8.0\". You can find that there are a lot of files with extension ".exd". You have to delete the .exd file with the name same as your control name. Notice that you don't have to delete it every time, just in case you have new properties added, you have to do such an action in order to allow Excel to load Properties list properly. By using the same way, you can add properties that can link to the cell of the Excel. Following extra steps have to be taken:
8. Try to add one more property named "Value" of type "long" by following steps 1 to 5. Try to select the property and click the button "Data Binding ...", there will be a dialog popup, try to enable "Default bindable property".
9. Recompile the project and remember to delete the corresponding .exd file.
10. Open Excel and create the control. Have a look on the Properties list, a new property was added, it is the "LinkedCell".

It is finished. So, whenever there is change of property's values (for example, the "Value" property), the Set method will be called (in fact, the Set method is a callback function). Therefore, the programmer should try to write code to handle the change of properties' values so that the drawing can be updated live. In doing so, we can just add a code "InvalidateControl()" to force the control to redraw itself by applying all the new values.

How to use what we learnt in secondary school mathematics (Geometry, I studied in Hong Kong)

OK, it is a point of interest. I like mathematics and physics very much. However ... in HK, it is hard for me to choose the way of being a pure science student. The reason why I chose computing is that ... I even didn't know how to turn off a PC when I was in my last year of high school...

The circle above is the simple layout of the dashboard. What is interesting is that it will not always be a circle, it can be an ellipse when the user drags the control as a rectangle. So, by using a simple ellipse formula, we can calculate the "a" and "b" values and pass them to the GDI+ function to draw the ellipse.

For the arrow, there is a rule that the arrow angle θ is always kept constant. And for me, as a programmer, I have to know three points in order for the GDI+ function to draw an arrow. The three points are (Px, Py), two tangent points (Ux, Uy) (<- there are two points of Ux, Uy). So, what is the known value here? What is the unknown here?

Known Value:

1. Center Pt (Cx, Cy) // how to get Cx, Cy? So easy .. think about it .. hint is .. using simple geometry.
2. Vertex Pt (Px, Py) // how to get Px, Py? So easy, think about it, hint is .. using simple ellipse equation.
3. Length L // how to get L? So easy .. using simple geometry.
4. Angle θ (<- I want to keep it constant).

Unknown Value:

1. The two tangent points (Ux, Uy)

   So, after long calculation, I derived the following equation:

   Uy = Py * sin<SUP>2</SUP>(θ/2) + Cy * cos<SUP>2</SUP>(θ/2) ± 
       (1/2) * sin(θ) * √(L2 – (Cy – Py)2)

   How about Ux? I leave it to you as an exercise here ...

That's all, thank you for reading.