//Open Microsoft Excel file and switch to the firs available worksheet.
BOOL CXLAutomation::OpenExcelFile(CString szFileName)
{
//Leave if the file cannot be open
if(NULL == m_pdispExcelApp)
return FALSE;
if(szFileName.IsEmpty())
return FALSE;
VARIANTARG varg1, vargWorkbook, vargWorksheet;
ClearAllArgs();
if (!ExlInvoke(m_pdispExcelApp, L"Workbooks", &varg1, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(L"Filename", 0, szFileName);
if (!ExlInvoke(varg1.pdispVal, L"Open", &vargWorkbook, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Now let's get the first worksheet of this workbook
ClearAllArgs();
AddArgumentInt2(NULL, 0, 1);
if (!ExlInvoke(vargWorkbook.pdispVal, L"Worksheets", &vargWorksheet, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Close the empty worksheet
ClearAllArgs();
if (!ExlInvoke(m_pdispWorkbook, L"Close", NULL, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Remember the newly open worksheet
m_pdispWorkbook = vargWorkbook.pdispVal;
m_pdispWorksheet = vargWorksheet.pdispVal;
return TRUE;
}
//May be new points have been added to the plot data source. Update plot range
BOOL CXLAutomation::UpdatePlotRange(int nYColumn)
{
if((NULL == m_pdispWorksheet) || (NULL == m_pdispWorkbook) || (NULL == m_pdispActiveChart))
return FALSE;
VARIANTARG varg1;
IDispatch *pdispRange = NULL;
IDispatch *pdispActiveChart = NULL;
BOOL bResult = TRUE;
ClearAllArgs();
AddArgumentDouble(NULL, 0, nYColumn);
if (!ExlInvoke(m_pdispWorksheet, L"Columns", &varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
pdispRange = varg1.pdispVal;
//ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A:A, B:B"), PlotBy:= _
// xlColumns
ClearAllArgs();
AddArgumentInt2(L"PlotBy", 0, xlColumns);
AddArgumentDispatch(L"Source", 0, pdispRange); // will auto-free
pdispRange = NULL;
if (!ExlInvoke(m_pdispActiveChart, L"SetSourceData", NULL, DISPATCH_METHOD, DISP_FREEARGS))
bResult = FALSE;
ClearAllArgs();
if(NULL != pdispRange)
{
pdispRange->Release();
pdispRange = NULL;
}
return bResult;
}
//Get worksheet name as CString
//The worksheet is defined by number, i.e., 1,2,3 ...
CString CXLAutomation::GetWorksheetName(int nWorksheet)
{
CString szName = _T("");
VARIANTARG varg1, varg2;
ClearAllArgs();
AddArgumentInt2(NULL, 0, nWorksheet);
if(!ExlInvoke(m_pdispWorkbook, L"Worksheets",&varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS)) //DISP_FREEARGS))
return szName;
//Execute macros
//szName = Worksheets(nWorksheet).Name
ClearAllArgs();
if (!ExlInvoke(varg1.pdispVal, L"Name", &varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return szName;
//Get the name from varg2
VARTYPE Type = varg2.vt;
if(Type == VT_BSTR)
{
BSTR b = varg2.bstrVal;
szName = b;
}
return szName;
}
//Create an empty worksheet
int CXLAutomation::AddNewWorkSheet()
{
if(NULL == m_pdispExcelApp)
return -1;
BOOL fResult;
VARIANTARG varg1, varg2;
IDispatch *pdispRange = NULL;
IDispatch *pdispActiveSheet = NULL;
IDispatch *pdispActiveCell = NULL;
IDispatch *pdispCrt = NULL;
// Set wb = [application].Workbooks.Add(template := xlWorksheet)
ClearAllArgs();
if (!ExlInvoke(m_pdispExcelApp, L"Workbooks", &varg1, DISPATCH_PROPERTYGET, 0))
return -1;
//First time here: no workbook yet. Create new workbook.
if(NULL == m_pdispWorkbook)
{
ClearAllArgs();
AddArgumentInt2(L"Template", 0, xlWorksheet);
fResult = ExlInvoke(varg1.pdispVal, L"Add", &varg2, DISPATCH_METHOD, 0);
ReleaseVariant(&varg1);
if (!fResult)
return -1;
m_pdispWorkbook = varg2.pdispVal;
// Set ws = wb.Worksheets(nNextWorksheet)
ClearAllArgs();
AddArgumentInt2(NULL, 0, 1);
if (!ExlInvoke(m_pdispWorkbook, L"Worksheets", &varg2, DISPATCH_PROPERTYGET, 0))
return -1;
m_pdispActiveWorksheet = varg2.pdispVal;
return GetWorksheetsCount();
}
//Add a new worksheet to the existing workbook
ClearAllArgs();
if(!ExlInvoke(m_pdispWorkbook, L"Sheets",&varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return -1;
if (!ExlInvoke(varg1.pdispVal, L"Add", &varg2, DISPATCH_PROPERTYGET, 0))
return -1;
m_pdispActiveWorksheet = varg2.pdispVal;
return GetWorksheetsCount();
}
//Create an empty workshet
BOOL CXLAutomation::CreateWorkSheet()
{
if(NULL == m_pdispExcelApp)
return FALSE;
BOOL fResult;
VARIANTARG varg1, varg2;
IDispatch *pdispRange = NULL;
IDispatch *pdispActiveSheet = NULL;
IDispatch *pdispActiveCell = NULL;
IDispatch *pdispCrt = NULL;
// Set wb = [application].Workbooks.Add(template := xlWorksheet)
ClearAllArgs();
if (!ExlInvoke(m_pdispExcelApp, L"Workbooks", &varg1, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentInt2(L"Template", 0, xlWorksheet);
fResult = ExlInvoke(varg1.pdispVal, L"Add", &varg2, DISPATCH_METHOD, 0);
ReleaseVariant(&varg1);
if (!fResult)
return FALSE;
m_pdispWorkbook = varg2.pdispVal;
// Set ws = wb.Worksheets(1)
ClearAllArgs();
AddArgumentInt2(NULL, 0, 1);
if (!ExlInvoke(m_pdispWorkbook, L"Worksheets", &varg2, DISPATCH_PROPERTYGET, 0))
goto CreateWsBail;
m_pdispWorksheet = varg2.pdispVal;
fResult = TRUE;
CreateWsExit:
if (pdispRange != NULL)
pdispRange->Release();
if (pdispCrt != NULL)
pdispCrt->Release();
return fResult;
CreateWsBail:
fResult = FALSE;
goto CreateWsExit;
}
BOOL CXLAutomation::ExlInvoke(IDispatch *pdisp, LPOLESTR szMember, VARIANTARG * pvargReturn,
WORD wInvokeAction, WORD wFlags)
{
HRESULT hr;
DISPPARAMS dispparams;
unsigned int uiArgErr;
EXCEPINFO excep;
// Get the IDs for the member and its arguments. GetIDsOfNames expects the
// member name as the first name, followed by argument names (if any).
m_alpszArgNames[0] = szMember;
hr = pdisp->GetIDsOfNames( IID_NULL, m_alpszArgNames,
1 + m_iNamedArgCount, LOCALE_SYSTEM_DEFAULT, m_aDispIds);
if (FAILED(hr))
{
if (!(wFlags & DISP_NOSHOWEXCEPTIONS))
ShowException(szMember, hr, NULL, 0);
return FALSE;
}
if (pvargReturn != NULL)
ClearVariant(pvargReturn);
// if doing a property put(ref), we need to adjust the first argument to have a
// named arg of DISPID_PROPERTYPUT.
if (wInvokeAction & (DISPATCH_PROPERTYPUT | DISPATCH_PROPERTYPUTREF))
{
m_iNamedArgCount = 1;
m_aDispIds[1] = DISPID_PROPERTYPUT;
pvargReturn = NULL;
}
dispparams.rgdispidNamedArgs = m_aDispIds + 1;
dispparams.rgvarg = m_aVargs;
dispparams.cArgs = m_iArgCount;
dispparams.cNamedArgs = m_iNamedArgCount;
excep.pfnDeferredFillIn = NULL;
hr = pdisp->Invoke(m_aDispIds[0], IID_NULL, LOCALE_SYSTEM_DEFAULT,
wInvokeAction, &dispparams, pvargReturn, &excep, &uiArgErr);
if (wFlags & DISP_FREEARGS)
ClearAllArgs();
if (FAILED(hr))
{
// display the exception information if appropriate:
if (!(wFlags & DISP_NOSHOWEXCEPTIONS))
ShowException(szMember, hr, &excep, uiArgErr);
// free exception structure information
SysFreeString(excep.bstrSource);
SysFreeString(excep.bstrDescription);
SysFreeString(excep.bstrHelpFile);
return FALSE;
}
return TRUE;
}
//Perform Worksheets.Cells(x,y).Value = szStr
BOOL CXLAutomation::SetCellsValueToString(double Column, double Row, CString szStr)
{
if(NULL == m_pdispWorksheet)
return FALSE;
if(szStr.IsEmpty())
return FALSE;
long nBuffSize = szStr.GetLength();
VARIANTARG vargRng;
ClearAllArgs();
AddArgumentDouble(NULL, 0, Column);
AddArgumentDouble(NULL, 0, Row);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
AddArgumentCString(NULL, 0, szStr );
if (!ExlInvoke(vargRng.pdispVal, L"Value", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
ReleaseVariant(&vargRng);
return TRUE;
}
//Perform Worksheets.Cells(x,y).Value = szStr
BOOL CXLAutomation::SetCellsValueToString(double Column, double Row, CString szStr)
{
if(NULL == m_pdispWorksheet)
return FALSE;
if(szStr.IsEmpty())
return FALSE;
long nBuffSize = szStr.GetLength();
VARIANTARG vargRng;
ClearAllArgs();
AddArgumentDouble(NULL, 0, Column);
AddArgumentDouble(NULL, 0, Row);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
AddArgumentCString(NULL, 0, szStr );
if (!ExlInvoke(vargRng.pdispVal, L"Value", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
if(ReleaseVariant(&vargRng)>=1)
{
CString error;
error.Format("Error in outputing row %d, colume %d, value %s", Row, Column, szStr);
AfxMessageBox(error);
}
return TRUE;
}
BOOL CXLAutomation::SetExcelVisible(BOOL bVisible)
{
if (m_pdispExcelApp == NULL)
return FALSE;
ClearAllArgs();
AddArgumentBool(NULL, 0, bVisible);
return ExlInvoke(m_pdispExcelApp, L"Visible", NULL, DISPATCH_PROPERTYPUT, DISP_FREEARGS);
}
BOOL CXLAutomation::SetWorksheetTitle(CString wsTitle)
{
//Sheets(Sheets.Count).Name = OtherName
ClearAllArgs();
AddArgumentCString(NULL, 0, wsTitle);
if (!ExlInvoke(m_pdispActiveWorksheet, L"Name", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
return TRUE;
}
//Return the number of worksheets in this book by Worksheets.Count
int CXLAutomation::GetWorksheetsCount()
{
if(NULL == m_pdispWorkbook)
return -1;
VARIANTARG varg1, varg2;
ClearAllArgs();
if (!ExlInvoke(m_pdispWorkbook, L"Worksheets", &varg1, DISPATCH_PROPERTYGET, 0))
return -1;
ClearAllArgs();
if (!ExlInvoke(varg1.pdispVal, L"Count", &varg2, DISPATCH_PROPERTYGET, 0))
return -1;
VARTYPE Type = varg2.vt;
if(Type != VT_I4)
return -1;
int n = varg2.lVal;
return varg2.lVal;
}
// bShowAlert = FALSE will disable alerts
// bShowAlert = TRUE will enable alerts
BOOL CXLAutomation::EnableAlert(BOOL bShowAlert)
{
if(NULL == m_pdispExcelApp)
return FALSE;
ClearAllArgs();
AddArgumentBool(NULL, bShowAlert, true);
if(!ExlInvoke(m_pdispExcelApp, L"Worksheets", NULL, DISPATCH_METHOD, DISP_FREEARGS))
return FALSE;
else
return TRUE;
}
//Delete entire line from the current worksheet
//Worksheet.Rows(nLine).Select
//Selection.Delete Shift:=xlUp
BOOL CXLAutomation::DeleteRow(long nRow)
{
if(NULL == m_pdispWorksheet)
return FALSE;
VARIANTARG varg1;
ClearAllArgs();
AddArgumentDouble(NULL, 0, nRow);
if (!ExlInvoke(m_pdispWorksheet, L"Rows", &varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
AddArgumentInt2(L"Shift", 0, xlUp);
if (!ExlInvoke(varg1.pdispVal, L"Delete", NULL, DISPATCH_METHOD, DISP_FREEARGS))
return FALSE;
return TRUE;
}
BOOL CXLAutomation::SetChartTitle(CString cTitle)
{
//Sheets(Sheets.Count).Name = OtherName
ClearAllArgs();
AddArgumentCString(NULL, 0, cTitle);
if (!ExlInvoke(m_pdispActiveChart, L"Name", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
//.ChartTitle.Characters.Text = "My Chart Title"
VARIANTARG vargTitle, vargChar;
ClearAllArgs();
if (!ExlInvoke(m_pdispActiveChart, L"ChartTitle", &vargTitle, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(vargTitle.pdispVal, L"Characters", &vargChar, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, cTitle);
if (!ExlInvoke(vargTitle.pdispVal, L"Text", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
return TRUE;
}
//Set worksheet for input and output
BOOL CXLAutomation::SetActiveWorksheet(int nWorksheet)
{
// Set ws = Sheets(nNextWorksheet).Select
//How many worksheets we have?
int nCount = GetWorksheetsCount();
if((nWorksheet < 1) || (nWorksheet > nCount))
return FALSE;
//Worksheets are counted a strange way: the last added have to be called as 1
int nActiveWorksheet = nCount - nWorksheet + 1;
VARIANTARG varg1, varg2;
ClearAllArgs();
AddArgumentInt2(NULL, 0, nActiveWorksheet);
if(!ExlInvoke(m_pdispWorkbook, L"Worksheets",&varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS)) //DISP_FREEARGS))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(varg1.pdispVal, L"Select", &varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
m_pdispActiveWorksheet = varg1.pdispVal;
return TRUE;
}
//Save current workbook as an Excel file:
//ActiveWorkbook.SaveAs
//FileName:=szFileName, FileFormat:=xlNormal,
//Password:=szPassword,
//WriteResPassword:=szWritePassword,
//ReadOnlyRecommended:= bReadOnly,
//CreateBackup:= bBackup
BOOL CXLAutomation::SaveAs(CString szFileName, int nFileFormat, CString szPassword, CString szWritePassword, BOOL bReadOnly, BOOL bBackUp)
{
if(NULL == m_pdispWorkbook)
return FALSE;
ClearAllArgs();
AddArgumentBool(L"CreateBackup", 0, bBackUp);
AddArgumentBool(L"ReadOnlyRecommended", 0, bReadOnly);
AddArgumentCString(L"WriteResPassword", 0, szWritePassword);
AddArgumentCString(L"Password", 0, szPassword);
AddArgumentCString(L"FileName", 0, szFileName);
if (!ExlInvoke(m_pdispWorkbook, L"SaveAs", NULL, DISPATCH_METHOD, DISP_FREEARGS))
return FALSE;
return TRUE;
}
BOOL CXLAutomation::SetRangeValueDouble(LPOLESTR lpszRef, double d)
{
if(NULL == m_pdispWorksheet)
return FALSE;
VARIANTARG vargRng;
BOOL fResult;
ClearAllArgs();
AddArgumentOLEString(NULL, 0, lpszRef);
if (!ExlInvoke(m_pdispWorksheet, L"Range", &vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
AddArgumentDouble(NULL, 0, d);
fResult = ExlInvoke(vargRng.pdispVal, L"Value", NULL, DISPATCH_PROPERTYPUT, 0);
ReleaseVariant(&vargRng);
return fResult;
}
BOOL CXLAutomation::ReleaseExcel()
{
if (m_pdispExcelApp == NULL)
return TRUE;
// Tell Excel to quit, since for automation simply releasing the IDispatch
// object isn't enough to get the server to shut down.
// Note that this code will hang if Excel tries to display any message boxes.
// This can occur if a document is in need of saving. The CreateChart() code
// always clears the dirty bit on the documents it creates, avoiding this problem.
ClearAllArgs();
ExlInvoke(m_pdispExcelApp, L"Quit", NULL, DISPATCH_METHOD, 0);
// Even though Excel has been told to Quit, we still need to release the
// OLE object to account for all memory.
ReleaseDispatch();
return TRUE;
}
void LaunchWord(CString sPathWord,CString sPathOutput){
BSTR param2 = sPathWord.AllocSysString();//SysAllocString(OLESTR("HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Windows\\CurrentVersion\\App Paths\\IEXPLORE.EXE"));
BSTR param1 = sPathOutput.AllocSysString();//SysAllocString(OLESTR(""));
BSTR name1 = SysAllocString(OLESTR("PathWord"));
BSTR name2 = SysAllocString(OLESTR("PathOutput"));
AddArgumentCString(name1,0,param1);
AddArgumentCString(name2,0,param2);
VARIANTARG FAR rgvarg[2];
V_BSTRREF(&rgvarg[0]) = ¶m1;
V_BSTRREF(&rgvarg[1]) = ¶m2;
DISPPARAMS dpNoArgs = {g_aVargs, NULL, 2, 0};
VARIANT vResult;
OLECHAR FAR* szFunction;
//BSTR bstrTemp;
// IDispatch pointers for Word's objects
//IDispatch* pDispDocs; //Documents collection
// DISPIDs
DISPID dispid_Docs; //Documents property of Application
//object
//Initialize the COM libraries
::CoInitialize(NULL);
// Create an instance of the Word application and obtain the
// pointer to the application's IDispatch interface.
CLSID clsid;
CLSIDFromString(L"UtilsVB.clsLauncher", &clsid);
IUnknown* pUnk;
HRESULT hr = ::CoCreateInstance( clsid, NULL, CLSCTX_SERVER,
IID_IUnknown, (void**) &pUnk);
IDispatch* pDispApp;
hr = pUnk->QueryInterface(IID_IDispatch, (void**)&pDispApp);
// Get IDispatch* for the Documents collection object
szFunction = OLESTR("Launcher");
hr = pDispApp->GetIDsOfNames (IID_NULL, &szFunction, 1,
LOCALE_USER_DEFAULT, &dispid_Docs);
unsigned int FAR uArgErr;
hr = pDispApp->Invoke (dispid_Docs, IID_NULL,
LOCALE_USER_DEFAULT, DISPATCH_METHOD,
&dpNoArgs, &vResult, NULL, &uArgErr);
//Execute Internet Explorer
/*
CString sPath;
HINSTANCE hinstance=ShellExecute(
AfxGetMainWnd( )->m_hWnd, //HWND hwnd,
"open", //LPCTSTR lpVerb,
CString(vResult.bstrVal),//"C:\\Archivos de programa\\Internet Explorer\\IEXPLORE.EXE",//"C:\\Archivos de programa\\Microsoft Office\\Office\\WINWORD.EXE",//LPCTSTR lpFile,
sPath,//"\"file://C:\\2002\\DraftCable\\Src\\testfile.htm\"", //LPCTSTR lpParameters,
NULL, //LPCTSTR lpDirectory,
SW_SHOWNORMAL //INT nShowCmd
);
*/
ClearAllArgs();
pDispApp->Release();
pUnk->Release();
//...
::CoUninitialize();
}
//Get Worksheet.Calls(nColumn, nRow).Value
//This method is not fully tested - see code coments
CString CXLAutomation::GetCellValueCString(int nColumn, int nRow)
{
CString szValue =_T("");
if(NULL == m_pdispWorksheet)
return szValue;
VARIANTARG vargRng, vargValue;
ClearAllArgs();
AddArgumentDouble(NULL, 0, nColumn);
AddArgumentDouble(NULL, 0, nRow);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return szValue;
if (!ExlInvoke(vargRng.pdispVal, L"Value", &vargValue, DISPATCH_PROPERTYGET, 0))
return szValue;
VARTYPE Type = vargValue.vt;
switch (Type)
{
case VT_UI1:
{
unsigned char nChr = vargValue.bVal;
// comment out szValue = nChr;
}
break;
case VT_I4:
{
long nVal = vargValue.lVal;
szValue.Format("%i", nVal);
}
break;
case VT_R4:
{
float fVal = vargValue.fltVal;
szValue.Format("%f", fVal);
}
break;
case VT_R8:
{
double dVal = vargValue.dblVal;
szValue.Format("%f", dVal);
}
break;
case VT_BSTR:
{
BSTR b = vargValue.bstrVal;
szValue = b;
}
break;
case VT_BYREF|VT_UI1:
{
//Not tested
unsigned char* pChr = vargValue.pbVal;
// comment out szValue = *pChr;
}
break;
case VT_BYREF|VT_BSTR:
{
//Not tested
BSTR* pb = vargValue.pbstrVal;
szValue = *pb;
}
case 0:
{
//Empty
szValue = _T("");
}
break;
}
// ReleaseVariant(&vargRng);
// ReleaseVariant(&vargValue);
return szValue;
}
//Insert picture from file szFileName to worksheet
//The left top corner of the picture is position in (Column, nRow)
//on the worksheet
//Size of the picture in % of original size is given by dPicWidth, dPicHeight
//If dPicWidth = 0.0, dPicHeight = 0.0 or dPicWidth = 1.0, dPicHeight = 1.0
//the picture has default (i.e., original) size
BOOL CXLAutomation::InsertPictureToWorksheet(CString szFileName, int Column, int Row, double dPicWidth, double dPicHeight)
{
//No file name provided. Leave.
if(szFileName.IsEmpty())
return FALSE;
if(NULL == m_pdispWorksheet)
return FALSE;
//First, select cell where you want copy the picture (i.e., the top left corner of the picture
//Leave if Column and Row are outside the worksheet
if((Column < 1) || (Row < 1))
return FALSE;
VARIANTARG vargRng, vargActiveCell;
VARIANTARG varg1, varg2;
ClearAllArgs();
AddArgumentDouble(NULL, 0, Column);
AddArgumentDouble(NULL, 0, Row);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargRng, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
if(!ExlInvoke(vargRng.pdispVal, L"Select",&vargActiveCell, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ReleaseVariant(&vargRng);
ReleaseVariant(&vargActiveCell);
//Run this macros to incert picture from file
//ActiveSheet.Pictures.Insert("c:\mypicture.bmp").Select
//or
//ActiveSheet.Pictures.Insert ("C:\mypicture.bmp")
//ActiveSheet.Pictures.ShapeRange.ScaleWidth 0.31, msoScaleFromTopLeft
ClearAllArgs();
if (!ExlInvoke(m_pdispWorksheet, L"Pictures", &varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, szFileName);
if (!ExlInvoke(varg1.pdispVal, L"Insert", &varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
if((dPicWidth != 0.0) && (dPicHeight != 0.0))
{
//Run this macros to resize the picture:
//Selection.ShapeRange.ScaleWidth dPicWidth, msoFalse, msoScaleFromTopLeft
//Selection.ShapeRange.ScaleHeight dPicWidth, msoFalse, msoScaleFromTopLeft
VARIANTARG vargImage;
ClearAllArgs();
if (!ExlInvoke(varg1.pdispVal, L"ShapeRange", &vargImage, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
//msoScaleFromTopLeft = 0 - this argument indicate scaling from top left
AddArgumentInt2(NULL, 0, 0);
AddArgumentDouble(NULL, 0, dPicWidth);
if (!ExlInvoke(vargImage.pdispVal, L"ScaleWidth", NULL, DISPATCH_METHOD, 0)) //DISP_FREEARGS))
return FALSE;
if (!ExlInvoke(vargImage.pdispVal, L"ScaleHeight", NULL, DISPATCH_METHOD, 0)) //DISP_FREEARGS))
return FALSE;
ClearAllArgs();
ReleaseVariant(&vargImage);
}
ReleaseVariant(&varg1);
ReleaseVariant(&varg2);
return TRUE;
}
//Copy string to clipboard and paste it to worksheet
BOOL CXLAutomation::PasteStringToWorksheet(CString pDataBuffer)
{
if(NULL == m_pdispWorksheet)
return FALSE;
if(pDataBuffer.IsEmpty())
return FALSE;
long nBuffSize = pDataBuffer.GetLength();
//Nothing to copy
if(0 == nBuffSize)
return FALSE;
HANDLE hMem = GlobalAlloc(GMEM_MOVEABLE|GMEM_DDESHARE, 24);
if (OpenClipboard(NULL) )
{
HGLOBAL hClipMem;
char* buffer;
BOOL bResult = TRUE;
EmptyClipboard();
hClipMem = GlobalAlloc(GMEM_DDESHARE, nBuffSize);
buffer = (char*)GlobalLock(hClipMem);
memcpy(buffer, pDataBuffer, nBuffSize);
GlobalUnlock(hClipMem);
SetClipboardData(CF_TEXT, hClipMem);
CloseClipboard();
//Paste data from clipboard
// set sourceRange = ws.Range("A1")
IDispatch* pdispRange = NULL;
VARIANTARG varg1, varg2;
ClearAllArgs();
AddArgumentOLEString(NULL, 0, L"A1");
if (!ExlInvoke(m_pdispWorksheet, L"Range", &varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
pdispRange = varg2.pdispVal;
//Force past from clipboard
//set as = sourceRange.Select
ClearAllArgs();
if (!ExlInvoke(pdispRange, L"Select", &varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
goto Failed;
// set wb.ActiveSheet.Paste
ClearAllArgs();
if (!ExlInvoke(m_pdispWorkbook, L"ActiveSheet", &varg1, DISPATCH_PROPERTYGET, 0))
goto Failed;
ClearAllArgs();
// set wb.ActiveSheet.Paste
ClearAllArgs();
if (!ExlInvoke(m_pdispWorkbook, L"ActiveSheet", &varg1, DISPATCH_PROPERTYGET, 0))
goto Failed;
ClearAllArgs();
bResult = ExlInvoke(varg1.pdispVal, L"Paste", &varg2, DISPATCH_METHOD, 0);
ReleaseVariant(&varg1);
goto Success;
Failed:
bResult = FALSE;
Success:
if(NULL != pdispRange)
{
pdispRange->Release();
pdispRange = NULL;
}
return bResult;
}
return FALSE;
}
//Insert image to worksheet using clipboard
BOOL CXLAutomation::InsertPictureToWorksheet(BYTE *pImage, int Column, int Row, double dPicWidth, double dPicHeight)
{
//Place image to clipboard
if(NULL != pImage)
if(!PlaceImageToClipboard(pImage))
return FALSE;
//Select cell where you want copy the picture (i.e., the top left corner of the picture
//Leave if Column and Row are outside the worksheet
if((Column < 1) || (Row < 1))
return FALSE;
VARIANTARG vargCell, vargActiveCell;
VARIANTARG vargActiveSelection;
ClearAllArgs();
AddArgumentDouble(NULL, 0, Column);
AddArgumentDouble(NULL, 0, Row);
if(!ExlInvoke(m_pdispWorksheet, L"Cells",&vargCell, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
if(!ExlInvoke(vargCell.pdispVal, L"Select",&vargActiveCell, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ReleaseVariant(&vargCell);
ReleaseVariant(&vargActiveCell);
//Paste image from clipboard
//by runing:
//ActiveSheet.Paste
ClearAllArgs();
if (!ExlInvoke(m_pdispWorksheet, L"Paste", &vargActiveSelection, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ReleaseVariant(&vargActiveSelection);
//Select image
if (!ExlInvoke(m_pdispWorksheet, L"Pictures", &vargActiveSelection, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Resize image
if((dPicWidth != 0.0) && (dPicHeight != 0.0))
{
//Run this macros to resize the picture:
//Selection.ShapeRange.ScaleWidth dPicWidth, msoFalse, msoScaleFromTopLeft
//Selection.ShapeRange.ScaleHeight dPicWidth, msoFalse, msoScaleFromTopLeft
VARIANTARG vargImage;
ClearAllArgs();
if (!ExlInvoke(vargActiveSelection.pdispVal, L"ShapeRange", &vargImage, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
//msoScaleFromTopLeft = 0 - this argument indicate scaling from top left
AddArgumentInt2(NULL, 0, 0);
AddArgumentDouble(NULL, 0, dPicWidth);
if (!ExlInvoke(vargImage.pdispVal, L"ScaleWidth", NULL, DISPATCH_METHOD, 0)) //DISP_FREEARGS))
return FALSE;
if (!ExlInvoke(vargImage.pdispVal, L"ScaleHeight", NULL, DISPATCH_METHOD, 0)) //DISP_FREEARGS))
return FALSE;
ClearAllArgs();
ReleaseVariant(&vargImage);
}
ReleaseVariant(&vargActiveSelection);
return TRUE;
}
//Set chart's X and Y titles by executing this macros:
//ActiveChart.ChartArea.Select
//ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
//ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = szXTitle
//ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
// ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = szYTitle
//xlCategory = 1
//xlPrimary = 1
//xlValue = 2
BOOL CXLAutomation::SetAxesTitle(CString szXTitle, CString szYTitle)
{
//Leave if there is no chart
if(NULL == m_pdispActiveChart)
return FALSE;
int xlCategory = 1;
int xlPrimary = 1;
int xlValue = 2;
VARIANTARG varg1, varg2;
//Select chart: ActiveChart.ChartArea.Select
ClearAllArgs();
if(!ExlInvoke(m_pdispActiveChart, L"ChartArea",&varg1, DISPATCH_PROPERTYGET, DISP_FREEARGS)) //DISP_FREEARGS))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(varg1.pdispVal, L"Select", NULL, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
//Set title only if the title has been provided by user
if(!szXTitle.IsEmpty())
{
//ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
ClearAllArgs();
AddArgumentInt2(NULL, 0, xlPrimary);
AddArgumentInt2(NULL, 0, xlCategory);
if(!ExlInvoke(m_pdispActiveChart, L"Axes",&varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
AddArgumentBool(NULL, 0, TRUE);
if(!ExlInvoke(varg2.pdispVal, L"HasTitle",NULL, DISPATCH_PROPERTYPUT, DISP_FREEARGS))
return FALSE;
//ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = szXTitle
VARIANTARG vargTitle, vargChar;
ClearAllArgs();
if (!ExlInvoke(varg2.pdispVal, L"AxisTitle", &vargTitle, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(vargTitle.pdispVal, L"Characters", &vargChar, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, szXTitle);
if (!ExlInvoke(vargTitle.pdispVal, L"Text", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
ReleaseVariant(&vargChar);
ReleaseVariant(&vargTitle);
ReleaseVariant(&varg2);
}
if(!szYTitle.IsEmpty())
{
//ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ClearAllArgs();
AddArgumentInt2(NULL, 0, xlPrimary);
AddArgumentInt2(NULL, 0, xlValue);
if(!ExlInvoke(m_pdispActiveChart, L"Axes",&varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
return FALSE;
ClearAllArgs();
AddArgumentBool(NULL, 0, TRUE);
if(!ExlInvoke(varg2.pdispVal, L"HasTitle",NULL, DISPATCH_PROPERTYPUT, DISP_FREEARGS))
return FALSE;
//ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = szYTitle
VARIANTARG vargTitle, vargChar;
ClearAllArgs();
if (!ExlInvoke(varg2.pdispVal, L"AxisTitle", &vargTitle, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
if (!ExlInvoke(vargTitle.pdispVal, L"Characters", &vargChar, DISPATCH_PROPERTYGET, 0))
return FALSE;
ClearAllArgs();
AddArgumentCString(NULL, 0, szYTitle);
if (!ExlInvoke(vargTitle.pdispVal, L"Text", NULL, DISPATCH_PROPERTYPUT, 0))
return FALSE;
}
return TRUE;
}
//Create XY chart. Y values are in column nYColumn.
BOOL CXLAutomation::CreateXYChart(int nYColumn)
{
if(NULL == m_pdispWorksheet)
return FALSE;
BOOL fResult;
VARIANTARG varg1, varg2;
IDispatch *pdispRange = NULL;
IDispatch *pdispCrt = NULL;
// set sourceRange = ws.Columns(nYColumn)
ClearAllArgs();
AddArgumentDouble(NULL, 0, nYColumn);
if (!ExlInvoke(m_pdispWorksheet, L"Columns", &varg2, DISPATCH_PROPERTYGET, DISP_FREEARGS))
goto CreateChartBail;
pdispRange = varg2.pdispVal;
// set crt = wb.Charts.Add
ClearAllArgs();
if (!ExlInvoke(m_pdispWorkbook, L"Charts", &varg1, DISPATCH_PROPERTYGET, 0))
goto CreateChartBail;
ClearAllArgs();
fResult = ExlInvoke(varg1.pdispVal, L"Add", &varg2, DISPATCH_METHOD, 0);
ReleaseVariant(&varg1);
if (!fResult)
goto CreateChartBail;
pdispCrt = varg2.pdispVal;
ClearAllArgs();
if (!ExlInvoke(m_pdispWorkbook, L"ActiveChart", &varg1, DISPATCH_PROPERTYGET, 0))
goto CreateChartBail;
m_pdispActiveChart = varg1.pdispVal;
ClearAllArgs();
AddArgumentInt2(NULL, 0, xlXYScatterLinesNoMarkers);
if (!ExlInvoke(m_pdispActiveChart, L"ChartType", &varg1, DISPATCH_PROPERTYPUT, 0))
goto CreateChartBail;
//Charts.Add
//ActiveChart.ChartType = xlXYScatterLinesNoMarkers
//ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A:A, B:B"), PlotBy:= _
// xlColumns
ClearAllArgs();
AddArgumentInt2(L"PlotBy", 0, xlColumns);
AddArgumentDispatch(L"Source", 0, pdispRange); // will auto-free
pdispRange = NULL;
if (!ExlInvoke(m_pdispActiveChart, L"SetSourceData", NULL, DISPATCH_METHOD, DISP_FREEARGS))
goto CreateChartBail;
fResult = TRUE;
CreateChartExit:
if (pdispRange != NULL)
pdispRange->Release();
if (pdispCrt != NULL)
pdispCrt->Release();
return fResult;
CreateChartBail:
fResult = FALSE;
goto CreateChartExit;
return TRUE;
}
