C++에서 MFC나 #import를 사용하지 않고 Excel을 자동화하는 방법

CppAutomateExcel.zip

AutoMation CPP Code

#include "stdafx.h"
#include  // OLE2 Definitions
#include 

// 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, L"NULL IDispatch passed to AutoWrap()", L"Error", 0x10010);
		_exit(0);
	}

	// Variables used...
	DISPPARAMS dp = { NULL, NULL, 0, 0 };
	DISPID dispidNamed = DISPID_PROPERTYPUT;
	DISPID dispID;
	HRESULT hr;
	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; iInvoke(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()
{
	// 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, L"CLSIDFromProgID() failed", L"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, L"Excel not registered properly", L"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;
	}

	// Call Workbooks.Open() to get a new workbook...
	IDispatch *pXlBook;
	{
		VARIANT vtFileName;
		vtFileName.vt = VT_BSTR;
		vtFileName.bstrVal = SysAllocString(L"C:\\Users\\UserName\\Desktop\\test.xls");

		VARIANT result;
		VariantInit(&result);
		AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open", 1, vtFileName);             // reversed order is using more options
		pXlBook = result.pdispVal;
	}

	// Get ActiveSheet object
	IDispatch *pXlSheet;
	{
		VARIANT result;
		VariantInit(&result);
		AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
		pXlSheet = result.pdispVal;
	}

	// pXlBook->SaveAs
	{
		// Convert the NULL-terminated string to BSTR.
		VARIANT vtFileName;
		vtFileName.vt = VT_BSTR;
		vtFileName.bstrVal = SysAllocString(L"C:\\Users\\UserName\\Desktop\\test_sheet1.csv");

		VARIANT vtFormat;
		vtFormat.vt = VT_I4;
		vtFormat.lVal = 6;     // XlFileFormat::xlCSV

		// If there are more than 1 parameters passed, they MUST be pass in 
		// reversed order. Otherwise, you may get the error 0x80020009.
		AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, vtFormat,
			vtFileName);

		VariantClear(&vtFileName);
	}

	//Changing the ActiveSheet    
	{
		{
			VARIANT itemn;
			itemn.vt = VT_I4;
			itemn.lVal = 2;

			VARIANT result;
			VariantInit(&result);
			AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Worksheets", 1, itemn);
			pXlSheet = result.pdispVal;
		}

		{
			VARIANT result;
			VariantInit(&result);
			AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"Activate", 0);
		}
	}

	// pXlBook->SaveAs
	{
		// Convert the NULL-terminated string to BSTR.
		VARIANT vtFileName;
		vtFileName.vt = VT_BSTR;
		vtFileName.bstrVal = SysAllocString(L"C:\\Users\\UserName\\Desktop\\test_sheet2.csv");

		VARIANT vtFormat;
		vtFormat.vt = VT_I4;
		vtFormat.lVal = 6;     // XlFileFormat::xlCSV

		// If there are more than 1 parameters passed, they MUST be pass in 
		// reversed order. Otherwise, you may get the error 0x80020009.
		AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, vtFormat,
			vtFileName);

		VariantClear(&vtFileName);
	}

	// Set .Saved property of workbook to TRUE so we aren't prompted
	// to save when we tell Excel to quit...
	{
		VARIANT x;
		x.vt = VT_I4;
		x.lVal = 1;
		AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
	}

	// Tell Excel to quit (i.e. App.Quit)
	AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);

	// Release references...
	pXlSheet->Release();
	pXlBook->Release();
	pXlBooks->Release();
	pXlApp->Release();

	// Uninitialize COM for this thread...
	CoUninitialize();

	return 0;
}