//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;
}
//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;
}
//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;
}
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;
}
//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;
}
//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;
}
//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;
}
//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;
}
//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;
}
