Open Excel in VC++ and write data

[Tom Frohman]

Just like before I couldn't get ChartWizard to work.
In the example I attached above, I was able to do it by creating the chart manually.

Sorry.

[minagu]

So there's no way to modify the chart charachteristics? the tittles, spaces between the grapchic lines.., if you know any way to do it i'll thank you very much.

And I can use with no compilation and execution problems the next line:

"AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard",1,var);"

where var is obtained steps before with the next sentences (data cells range):

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
var.vt = VT_DISPATCH;
var.pdispVal = result.pdispVal;

But theres no result, and as i can execute the autowrap with "chartwizard" I supose that there must be a way to use it.., with more variables or any thing, but if it's posible to execute the "ChartWizard" Dispatch_Method, I think that there's a way to do it.

[Tom Frohman]

In this long and tedious example I open a save worksheet called "junk.xls" which already has my x and y data in columns A and B. I create the chart using the chart wizard and then set the chart type, title, and grid line spacing on the x axis.
This worked for me in Visual C++ 6.0 using Excel 2000.

   // Initialize COM for this thread...
    CoInitialize(NULL);
    
    // Get CLSID for our server...
    CLSID clsid;
    HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
    
    if (FAILED(hr)) 
        {
        ::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
        return -1;
        }
    
    // Start server and get IDispatch...
    IDispatch *pXlApp;
    hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
    if (FAILED(hr)) 
        {
        ::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);
        return -2;
        }
    
    // Make it visible (i.e. app.visible = 1)
        {
        VARIANT x;
        x.vt = VT_I4;
        x.lVal = 1;
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
        }
        
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}

IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"c:\\junk.xls\0");
AutoWrap(DISPATCH_METHOD, &result, pXlBooks, L"Open", 1, fname);
pXlBook = result.pdispVal;
}

IDispatch *pXlSheets;
{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
pXlSheets = result.pdispVal;
}

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_I4;
itemn.lVal = 1;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
}

VARIANT darange;

IDispatch *pXlRange;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"A1:B7");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
darange.vt = VT_DISPATCH;
darange.pdispVal = result.pdispVal;
pXlRange = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlRange, L"Select", 0);
}

IDispatch *pXlChartObjects;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"ChartObjects", 0);
pXlChartObjects = result.pdispVal;
}

IDispatch *pXlChartObject;
{
VARIANT result;
VariantInit(&result);
VARIANT left, top, width, height;
left.vt = VT_R8;
left.dblVal = 100.;
top.vt = VT_R8;
top.dblVal = 200.;
width.vt = VT_R8;
width.dblVal = 350.;
height.vt = VT_R8;
height.dblVal = 250.;

AutoWrap(DISPATCH_METHOD, &result, pXlChartObjects, L"ADD", 4, left, top, width, height);
pXlChartObject = result.pdispVal;
}

IDispatch *pXlChart;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlChartObject, L"Chart", 0);
pXlChart = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard", 1, darange);
}


{
VARIANT result;
VariantInit(&result);
VARIANT hastitle;
hastitle.vt=VT_BOOL;
hastitle.boolVal=TRUE;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChart, L"HasTitle", 1,hastitle);

}







IDispatch *pXlChartTitle;
	{
    VARIANT result;
	VariantInit(&result);
	AutoWrap(DISPATCH_PROPERTYGET,&result,pXlChart,L"ChartTitle",0);
	pXlChartTitle=result.pdispVal;
	}
IDispatch *pXlChars;
	{
    VARIANT result;
	VariantInit(&result);
	AutoWrap(DISPATCH_METHOD,&result,pXlChartTitle,L"Characters",0);
	pXlChars=result.pdispVal;
	}

{
VARIANT result;
	VariantInit(&result);
VARIANT thetitle;
thetitle.vt=VT_BSTR;
thetitle.bstrVal = ::SysAllocString(L"This is my title");
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChars, L"Text", 1,thetitle);
}

//set to xl_XYScatterNoMarkers
{
VARIANT result;
VariantInit(&result);
VARIANT type;
type.vt = VT_I4;
type.lVal = 75;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChart, L"ChartType", 1, type);
}
//get x axis
IDispatch *pXlXAxis;
	{
     VARIANT result;
	VariantInit(&result);
    VARIANT axisind;
	axisind.vt=VT_I4;
	axisind.lVal=1;
	AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"Axes", 1, axisind);
	pXlXAxis=result.pdispVal;
	

	}
	{
	VARIANT result;
	VariantInit(&result);
    VARIANT hasgrid;
	hasgrid.vt=VT_BOOL;
	hasgrid.boolVal=TRUE;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"HasMajorGridlines", 1, hasgrid);

	}
//set x axis grid spacing to 2 units
	{
	 VARIANT result;
	VariantInit(&result);
    VARIANT spacing;
	spacing.vt=VT_R8;
	spacing.dblVal=2.0;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"MajorUnit", 1, spacing);
    }
//Get the active workbook
IDispatch *pXlActiveWorkBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveWorkbook", 0);
pXlActiveWorkBook = result.pdispVal;
}

//Save the work book.
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"C:\\output.xls");
VARIANT fformat;
fformat.vt = VT_I4;
fformat.lVal=-4143;
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname);
}


pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
CoUninitialize();

[Tom Frohman]

In this second example I was able to get ChartWizard to work. However, I had to put the arguments in in reverse order. Probably something wrong with my Autowrap function. The entire program follows. I still had to set the chart gallery independently. I've found problems before with using gallery type 75 in the chartwizard when using a MFC version of all this.

 

#include "stdafx.h"
#include <ole2.h> // OLE2 Definitions

// AutoWrap() - Automation helper function...
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) 
{
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);

if (!pDisp) 
    {
    MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);
    _exit(0);
    }

// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;
char buf[200];
char szName[200];


// Convert down to ANSI
WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);

// Get DISPID for name passed...
hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
if (FAILED(hr)) 
    {
    sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
    MessageBox(NULL, buf, "AutoWrap()", 0x10010);
    _exit(0);
    return hr;
    }

// Allocate memory for arguments...
VARIANT *pArgs = new VARIANT[cArgs + 1];
// Extract arguments...
for (int i = 0; i < cArgs; i++) 
    {
    pArgs[i] = va_arg(marker, VARIANT);
    }

// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;

// Handle special-case for property-puts!
if (autoType & DISPATCH_PROPERTYPUT) 
    {
    dp.cNamedArgs = 1;
    dp.rgdispidNamedArgs = &dispidNamed;
    }

// Make the call!
hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
if (FAILED(hr)) 
    {
    sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
    MessageBox(NULL, buf, "AutoWrap()", 0x10010);
    _exit(0);
    return hr;
    }
// End variable-argument section...
va_end(marker);

delete[] pArgs;

return hr;
}
					
int main(int argc, char* argv[])
    {
    // Initialize COM for this thread...
    CoInitialize(NULL);
    
    // Get CLSID for our server...
    CLSID clsid;
    HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
    VARIANT vopt;
	vopt.vt=VT_ERROR;
	vopt.scode=DISP_E_PARAMNOTFOUND;
	vopt.lVal=DISP_E_PARAMNOTFOUND;

    if (FAILED(hr)) 
        {
        ::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
        return -1;
        }
    
    // Start server and get IDispatch...
    IDispatch *pXlApp;
    hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
    if (FAILED(hr)) 
        {
        ::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);
        return -2;
        }
    
    // Make it visible (i.e. app.visible = 1)
        {
        VARIANT x;
        x.vt = VT_I4;
        x.lVal = 1;
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
        }
        
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}

IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"c:\\junk.xls\0");
AutoWrap(DISPATCH_METHOD, &result, pXlBooks, L"Open", 1, fname);
pXlBook = result.pdispVal;
}

IDispatch *pXlSheets;
{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
pXlSheets = result.pdispVal;
}

IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_I4;
itemn.lVal = 1;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1, itemn);
pXlSheet = result.pdispVal;
}

VARIANT darange;

IDispatch *pXlRange;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"A1:B7");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);
darange.vt = VT_DISPATCH;
darange.pdispVal = result.pdispVal;
pXlRange = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlRange, L"Select", 0);
}

IDispatch *pXlChartObjects;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"ChartObjects", 0);
pXlChartObjects = result.pdispVal;
}

IDispatch *pXlChartObject;
{
VARIANT result;
VariantInit(&result);
VARIANT left, top, width, height;
left.vt = VT_R8;
left.dblVal = 600.;
top.vt = VT_R8;
top.dblVal = 600.;
width.vt = VT_R8;
width.dblVal = 200.;
height.vt = VT_R8;
height.dblVal = 200.;

AutoWrap(DISPATCH_METHOD, &result, pXlChartObjects, L"ADD", 4, left, top, width, height);
pXlChartObject = result.pdispVal;
}

IDispatch *pXlChart;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlChartObject, L"Chart", 0);
pXlChart = result.pdispVal;
}



//use the chart wizard with the arugments in reverse order
{
VARIANT result;
VARIANT maintitle;
maintitle.vt=VT_BSTR;
maintitle.bstrVal=::SysAllocString(L"Main Title");

VARIANT ytitle;
ytitle.vt=VT_BSTR;
ytitle.bstrVal=::SysAllocString(L"Ytitle");
VARIANT xtitle;
xtitle.vt=VT_BSTR;
xtitle.bstrVal=::SysAllocString(L"Xtitle");
VARIANT gall;
gall.vt=VT_I4;
gall.lVal=-4169;

VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard", 11, vopt,ytitle,xtitle,maintitle,vopt,vopt,vopt,vopt,vopt,gall,darange);
}
//set to xl_XYScatterNoMarkers
{
VARIANT result;
VariantInit(&result);
VARIANT type;
type.vt = VT_I4;
type.lVal = 75;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlChart, L"ChartType", 1, type);
}

//get x axis
IDispatch *pXlXAxis;
	{
     VARIANT result;
	VariantInit(&result);
    VARIANT axisind;
	axisind.vt=VT_I4;
	axisind.lVal=1;
	AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"Axes", 1, axisind);
	pXlXAxis=result.pdispVal;
	}
	{
	VARIANT result;
	VariantInit(&result);
    VARIANT hasgrid;
	hasgrid.vt=VT_BOOL;
	hasgrid.boolVal=TRUE;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"HasMajorGridlines", 1, hasgrid);

	}
//set x axis grid spacing to 2 units
	{
	 VARIANT result;
	VariantInit(&result);
    VARIANT spacing;
	spacing.vt=VT_R8;
	spacing.dblVal=2.0;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlXAxis, L"MajorUnit", 1, spacing);
    }

pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
CoUninitialize();

return 0;
}

[minagu]

Thank you very much Tom, it does work it! and I've tested the ChartWizard with the arguments in the reverse order, and it's ok, that was the problem, and this is because I needed to put my range var as the first parameter. And after that I've only had to change the Axes lVal=1 to lVal=2 to specify the major units for the axis y. Thank you.

[minagu]

Hello, i just need to know whats the function, if there's any one, to make disappear in excel all the cells borders, like in word, all in blank.

And i would like to know if there's a way to hide cells, and how to insert an image (gif,jpg,..anyone).

Thanks.

[Tom Frohman]

Two partial examples:
In the first part I hide the gridlines. In the second part, I hide a column.

To hide a cell you would set the font color to white. Otherwise you would have to hide whole rows or whole columns.
To do this you would have to select the range, Get the font of the range
and then set the font color to white (if your background is white.
Probably best to protect those cells so noone can edit them

To include an image I generally copy the image to the clipboard, select a cell to position it and then paste the image. You can adjust the positioning of the image after this programmatically.

I include an example of pasting the graphic using MFC. You can translate it to the without MFC version.

//***Don't display grids

 IDispatch *pXlWindows;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Windows", 0);
pXlWindows = result.pdispVal;
}
	
IDispatch *pXlWindow;
{
VARIANT result;
VARIANT xwind;
xwind.vt=VT_I4;
xwind.lVal=1;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlWindows, L"Item", 1,xwind);
pXlWindow = result.pdispVal;
}

{


VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=FALSE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlWindow, L"DisplayGridlines", 1,xprop);
}	

//******Hide A Column
IDispatch *pXlRange1;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}

IDispatch *pXlCols;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Columns", 0);

pXlCols = result.pdispVal;
}

{


VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlCols, L"Hidden", 1,xprop);
}

Pasting an image using MFC.

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
OpenClipboard(); // Reserve clipboard for this program
          EmptyClipboard();
          CBitmap MyBitmap;
          MyBitmap.LoadBitmap(IDB_BITMAP1);  // A Bitmap you drew in the
          //  Resource Editor
          HBITMAP MyBitmapHandle = (HBITMAP)MyBitmap;  // Cast it to a HBITMAP
          SetClipboardData(CF_BITMAP, MyBitmapHandle);
          CloseClipboard();
          app.SetVisible(TRUE);
          app.SetUserControl(TRUE);
          sheet.Activate();
          range = sheet.GetRange(COleVariant("A1"), COleVariant("a1"));
          range.Select();
          sheet.Paste(covOptional, covOptional);

[minagu]

Thank you, the whiting font is what i did, and that's what i'm gonna do, because the hiding dosen't avoid me to show the graphs, thankyou anyway.

The gridlines has been helpfull to me.

And how could you insert images without MFC, do you have any example?

Thanks Tom.

[Tom Frohman]

Importing a picture from a file

IDispatch *pXlShapes;
{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
pXlShapes = result.pdispVal;
}
IDispatch *pXlShape;
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"C:\\toad1.jpg\0");
VARIANT xpropf;
xpropf.vt=VT_BOOL;
xpropf.boolVal=FALSE;
VARIANT xpropt;
xpropt.vt=VT_BOOL;
xpropt.boolVal=TRUE;

VARIANT xtop;
xtop.vt=VT_R8;
xtop.dblVal=10.0;
VARIANT xleft;
xleft.vt=VT_R8;
xleft.dblVal=10.0;
VARIANT xwidth;
xwidth.vt=VT_R8;
xwidth.dblVal=200.0;
VARIANT xheight;
xheight.vt=VT_R8;
xheight.dblVal=200.0;

//I need to put the arguments in reverse order
AutoWrap(DISPATCH_METHOD, &result, pXlShapes, L"AddPicture", 7,xheight,xwidth,xleft,xtop,xpropt,xpropf,fname);
pXlShape = result.pdispVal;
}

[minagu]

Thankyou very much Tom.

[minagu]

Hi Tom, do you know how to get, with c++, the exact PID of the EXCEL opened?

[Tom Frohman]

No, I've never tried it.

See this thread and the articles it references:

http://www.codeguru.com/forum/showth...&highlight=PID

[minagu]

thanks, the problem is that i'm having some problems (error which stops the program) with the "CoUninitialize()" function, called when i delete the Excel class, and i don`t know if there's another way of do the delete of the Excel process diferent of CoUninitialize. If you know some way i'll be pleased to read you.

Thanks.

[neyadakim]

Hi Tom,
Thanks.
I am trying Excel automation. My problem is formatting excel cells;
I cant give font properties (bold,italic,underline, size, type) and
cell format (is cell currency,text,time,date..) and number of digit after decimal.
If you know some way i'll be pleased to read you.

[Tom Frohman]

If you are using the non-MFC methods of this thread you
would set the NumberFormat Property of the range class to "0.000" to
get three digits after the decimal.

A MFC version:
Range range;
range=sheet.GetRange(COleVariant("A1","A1"));
range.SetNumberFormat(COleVariant("0.000"));

You would get the dispatch of the Font from the Range
and then set the Name, Bold, Underline, etc properties of the Font class.

Font font;
font=range.GetFont();

font.SetBold(covTrue);
font.SetSize(COleVariant("12"));
font.SetUnderline(COleVariant(2L));
font.SetUnderline(COleVariant(-4142L)) to unset the underline.

Of course with the non-MFC method this would be a little more involved.
I'm feeling lazy this morning or I'd write down a couple of examples.
Maybe I'll do that in a couple of hours when I have time.

Tom