Exemple #1
0
//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;
}
Exemple #2
0
//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;
	
}
Exemple #3
0
//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;
	
}
Exemple #4
0
//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();
}
Exemple #5
0
//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;

}
Exemple #6
0
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;
}
Exemple #7
0
//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;
}
Exemple #8
0
//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;
}
Exemple #9
0
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);

}
Exemple #10
0
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;
}
Exemple #11
0
//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;
}
Exemple #12
0
// 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;

}
Exemple #13
0
//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;

}
Exemple #14
0
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;
}
Exemple #15
0
//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;
}
Exemple #16
0
//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;
}
Exemple #17
0
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;

}
Exemple #18
0
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;

}
Exemple #19
0
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]) = &param1;
	V_BSTRREF(&rgvarg[1]) = &param2;


	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();

}
Exemple #20
0
//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;

}
Exemple #21
0
//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;
}
Exemple #22
0
//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;

}
Exemple #23
0
//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;
}
Exemple #24
0
//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;
}
Exemple #25
0
//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;

}