QuantLib › quantlib-users

Excel viewer for vectors

Classic

List

Threaded

4 messages Options

amar singh

Excel viewer for vectors

I have attached a helper class file, which can be used for quick visualization of data in the C++ library, by adding a few lines to show something like forward curve, or printing the trinomial tree and saving in HTML file,etc.

Regards,

Amar

If anyone is interested,please read on..

Here is a sample usage in file BermudanSwaption.cpp, which shows up the fowrad and zero curves in one chart, discount curve in another :

(Header file ExcelHelper.hpp to be included at the top)

(code is inserted after getting the term structure)

   //---------------------------------------------------
        std::vector<double> forwardcurve,zerocurve1,zerocurve2,discountcurve;
        std::vector<double> timeAxis,timeAxis2;
        {
          double dbli;
          for( dbli=.1;dbli<30;dbli +=.1)
          {
            Time t = dbli;
            timeAxis2.push_back(t);
            forwardcurve.push_back(myTermStructure->instantaneousForward(t));
          }
          for( dbli=.1;dbli<30;dbli +=1)
          {
            Time t = dbli;
            timeAxis.push_back(t);
            discountcurve.push_back(myTermStructure->discount(t));
            zerocurve1.push_back(myTermStructure->zeroCoupon(t,2));
            zerocurve2.push_back(myTermStructure->zeroCoupon(t,4));
          }
          ExcelChart chart("Termstructure");
          chart.AddSeries(GetStringVector(timeAxis2),GetStringVector(forwardcurve),"forward");
          chart.AddSeries(GetStringVector(timeAxis),GetStringVector(zerocurve1));
          chart.AddSeries(GetStringVector(timeAxis),GetStringVector(zerocurve2),"zero 2");
          ExcelWorkBook workBook;
          workBook.ShowChart(chart);
          ExcelChart chart2("Discount");
          chart2.AddSeries(GetStringVector(timeAxis),GetStringVector(discountcurve),"discount");
          workBook.ShowChart(chart2);
          return 0;
        }
        //--------------------------------------------------------

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

#include <iostream>
#include <objbase.h>
#include <oleauto.h>
#include <windows.h>
#include <ole2.h>
#include <comutil.h>
#include <string>
#include <vector>
#include <stdio.h>

HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...);
std::vector<std::string> GetStringVector(std::vector<double> inVector);
IDispatch* GetRangePointer(int startRow,int startCol,int endRow,int endCol,IDispatch* pSheet);
BSTR GetBString(std::string string);

class ExcelWorkBook;

class ExcelLine
{
public:
ExcelLine(float startX,float startY,float endX,float endY):
m_startX(startX),m_startY(startY),m_endX(endX),m_endY(endY){};
private:
float m_startX;
float m_startY;
float m_endX;
float m_endY;
friend class ExcelWorkBook;
};

class ExcelLines
{
public:
ExcelLines(){};
AddLine(const ExcelLine& excelLine);
private:
std::vector<ExcelLine> m_linesVector;
friend class ExcelWorkBook;
};
class ExcelCell
{
public:
ExcelCell(int row,int col,std::string value = "");
int m_row;
int m_col;
std::string m_str;

};

class ExcelRange
{
friend class ExcelWorkBook;

public:
typedef std::vector<std::string> StringVectorType;
ExcelRange(){};
void AddCell(const ExcelCell& cell);
void AddVector(int startRow,int startCol,int cArgs...);

private:
std::vector<ExcelCell> m_cellsVector;
std::vector<StringVectorType> m_vectorOfVectors;
int m_startRow;
int m_startCol;
};

class ExcelChart
{
public:
typedef std::vector<std::string> StringVectorType;
ExcelChart(std::string chartName = "");
void AddSeries(std::vector<std::string> xAxisVec,std::vector<std::string> seriesVector,std::string seriesName= "");
private:
friend class ExcelWorkBook;
std::vector<StringVectorType> m_xAxisVectors;
std::vector<StringVectorType> m_seriesVectors;
StringVectorType m_seriesNames;
std::string m_chartName;
};

class ExcelWorkBook
{
public :

ExcelWorkBook();
ExcelWorkBook(std::string fileName);
ShowRange(const ExcelRange& range,const std::string sheetName = "");
ShowLines(const ExcelLines& excelLines);
SaveAsHTML(const std::string fileName);
void ShowChart(const ExcelChart& chart);
virtual ~ExcelWorkBook();
private:
void ShowRange(const ExcelRange& range,IDispatch *pXlSheet);
InitializeApplication();

IDispatch *pXlApp;
IDispatch *pXlBooks;
IDispatch *pXlBook;
IDispatch *pXlSheets;
};

//---------------------------------------------------------------------------------

void ExcelWorkBook::ShowChart(const ExcelChart& chart)
{
if (chart.m_xAxisVectors.size() == 0)
{
::MessageBox(NULL, "ExcelWorkBook::ShowChart -> No data to show. Please polulate.", "Notice", 0x10000);
return;
}
int i,j;

std::cout << "pxlbook = " << pXlBook << std::endl;
std::cout << "pXlSheets = " << pXlSheets << std::endl;
IDispatch* pXLCharts;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Charts",0);
pXLCharts = result.pdispVal;
}
std::cout << "pXLCharts = " << pXLCharts << std::endl;
VARIANT sheetsCount;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Count",0);
sheetsCount = result;
}
IDispatch *pXlLastSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1,sheetsCount);
pXlLastSheet = result.pdispVal;
}
std::cout << "pXlLastSheet = " << pXlLastSheet << std::endl;

IDispatch* pXLChart;
{
VARIANT parm[2];
parm[0].vt = VT_ERROR; parm[0].scode = DISP_E_PARAMNOTFOUND;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlLastSheet;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLCharts, L"Add", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXLChart = result.pdispVal;
}
std::cout << "pXLChart = " << pXLChart << std::endl;
{
VARIANT parm;
int xlLine = 4;
int xlXYScatterLines = 74;
parm.vt = VT_I4; parm.lVal = xlXYScatterLines;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLChart, L"ChartType", 1, parm);
}
if (chart.m_chartName != "")
{
VARIANT parm;
std::string chartName;
chartName = chart.m_chartName + "_Chart";
parm.vt = VT_BSTR; parm.bstrVal = GetBString(chartName);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLChart, L"Name", 1, parm);
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Count",0);
sheetsCount = result;
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1,sheetsCount);
pXlLastSheet = result.pdispVal;
}
std::cout << "pXlLastSheet = " << pXlLastSheet << std::endl;
IDispatch* pXLNewChartSheet;
{
VARIANT parm[2];
parm[0].vt = VT_ERROR;; parm[0].scode = DISP_E_PARAMNOTFOUND;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlLastSheet;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlSheets, L"Add", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXLNewChartSheet = result.pdispVal;
}
std::cout << "pXLNewChartSheet = " << pXLNewChartSheet << std::endl;

if (chart.m_chartName != "")
{
VARIANT parm;
std::string chartName;
chartName = chart.m_chartName + "_Data";
parm.vt = VT_BSTR; parm.bstrVal = GetBString(chartName);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLNewChartSheet, L"Name", 1, parm);
}
IDispatch* pXLSeriesCollection;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLChart, L"SeriesCollection",0);
pXLSeriesCollection = result.pdispVal;
}
std::cout << "pXLSeriesCollection = " << pXLSeriesCollection << std::endl;
IDispatch* pXLSeries;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLSeriesCollection, L"NewSeries",0);
pXLSeries = result.pdispVal;
}
std::cout << "pXLSeries = " << pXLSeries << std::endl;
int totalRecords(0);
int totalSeries(0);

totalSeries= chart.m_seriesVectors.size();
if (totalSeries > 0)
totalRecords = chart.m_seriesVectors[0].size();
IDispatch* pXLXAxis;
{
VARIANT parm;
int xlCategory = 1;
parm.vt = VT_I4; parm.lVal = xlCategory;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLChart, L"Axes", 1, parm);
VariantClear(&parm);
pXLXAxis = result.pdispVal;

}
std::cout << "pXLXAxis = " << pXLXAxis << std::endl;
IDispatch* pXLXAxisTickLabels;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXLXAxis, L"TickLabels", 0);
pXLXAxisTickLabels = result.pdispVal;
}
std::cout << "pXLXAxisTickLabels = " << pXLXAxisTickLabels << std::endl;
{
VARIANT parm;
int xlTickLabelOrientationUpward = -4171;
parm.vt = VT_I4; parm.lVal = xlTickLabelOrientationUpward;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLXAxisTickLabels, L"Orientation",1,parm);
VariantClear(&parm);
}
{
VARIANT parm;
parm.vt = VT_BSTR;
std::string format("0.00");
const char *ansistr = format.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
parm.bstrVal = ::SysAllocString(unicodestr);

AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLXAxisTickLabels, L"NumberFormat",1,parm);
VariantClear(&parm);
}

for(i=0;i< chart.m_xAxisVectors.size();i++)
{
std::cout << "i = " << i << std::endl;
std::cout << "X-axis length = " << chart.m_xAxisVectors[i].size() << std::endl;
pXLNewChartSheet->AddRef();
ExcelRange rangeObjXAxis;
rangeObjXAxis.AddVector(1,i*2+1,1,chart.m_xAxisVectors[i]);
ShowRange(rangeObjXAxis,pXLNewChartSheet);
IDispatch* pXAxisRange = GetRangePointer(1,i*2+1,chart.m_xAxisVectors[i].size(),i*2+1,pXLNewChartSheet);
std::cout << "pXAxisRange = " << pXAxisRange << std::endl;

pXLNewChartSheet->AddRef();
ExcelRange rangeObjSeries;
rangeObjSeries.AddVector(1,i*2+2,1,chart.m_seriesVectors[i]);
ShowRange(rangeObjSeries,pXLNewChartSheet);
IDispatch* pSeriesRange = GetRangePointer(1,i*2+2,chart.m_seriesVectors[i].size(),i*2+2,pXLNewChartSheet);
std::cout << "pSeriesRange = " << pSeriesRange << std::endl;
if (i > 0)
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLSeriesCollection, L"NewSeries",0);
pXLSeries = result.pdispVal;
}
if (chart.m_seriesNames[i] != "")
{
VARIANT parm;
parm.vt = VT_BSTR; parm.bstrVal = GetBString(chart.m_seriesNames[i]);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"Name", 1, parm);
}
{
VARIANT parm;
int xlMarkerStyleNone = -4142;
parm.vt = VT_I4; parm.lVal = xlMarkerStyleNone;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"MarkerStyle",1,parm);
VariantClear(&parm);
}
{
VARIANT parm;
parm.vt = VT_DISPATCH; parm.pdispVal = pXAxisRange;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"XValues",1,parm);
VariantClear(&parm);
}
{
VARIANT parm;
parm.vt = VT_DISPATCH; parm.pdispVal = pSeriesRange;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"Values",1,parm);
VariantClear(&parm);
}

}

AutoWrap(DISPATCH_METHOD, NULL, pXLChart, L"Activate",0);

pXLSeriesCollection->Release();
pXLChart->Release();
//pXlLastSheet->Release();
pXLCharts->Release();

return;

}

ExcelChart::ExcelChart(std::string chartName)
{
m_chartName = chartName;
}

void ExcelChart::AddSeries(std::vector<std::string> xAxisVec,std::vector<std::string> seriesVector,std::string seriesName)
{
if (xAxisVec.size() == 0)
{
::MessageBox(NULL, "ExcelChart::AddSeries -> X-axis is empty. Please polulate.", "Notice", 0x10000);
return;
}
if (seriesVector.size() == 0)
{
::MessageBox(NULL, "ExcelChart::AddSeries -> Series is empty. Please polulate.", "Notice", 0x10000);
return;
}
if (seriesVector.size() != xAxisVec.size())
{
::MessageBox(NULL, "ExcelChart::AddSeries -> X-axis and Series length should be same", "Notice", 0x10000);
return;
}
m_xAxisVectors.push_back(xAxisVec);
m_seriesVectors.push_back(seriesVector);
m_seriesNames.push_back(seriesName);
}

ExcelLines::AddLine(const ExcelLine& excelLine)
{
m_linesVector.push_back(excelLine);
}

ExcelCell::ExcelCell(int row,int col,std::string value):m_row(row),m_col(col),m_str(value)
{

}

void ExcelRange::AddVector(int startRow,int startCol,int cArgs...)
{
if (m_vectorOfVectors.size() > 0)
{
::MessageBox(NULL, "This range is already populated in the object.Create one more range object.", "Notice", 0x10000);
return;
}
m_startRow = startRow;
m_startCol = startCol;
va_list marker;
va_start(marker, cArgs);
int tempValue;
int tempValueprev;
tempValue = 0;
tempValueprev = 0;
for (int i = 0; i < cArgs;i++)
{
StringVectorType hj;
hj = va_arg(marker, StringVectorType);
tempValue = hj.size();
if ( (tempValueprev > 0) && (tempValue != tempValueprev) )
{
m_vectorOfVectors.clear();
::MessageBox(NULL, "All columns should be of equal length for ExcelRange::AddVector", "Notice", 0x10000);
return;
}
tempValueprev = tempValue;
m_vectorOfVectors.push_back(hj);
}
}

void ExcelRange::AddCell(const ExcelCell& cell)
{
m_cellsVector.push_back(cell);
}

ExcelWorkBook::SaveAsHTML(const std::string fileName)
{
VARIANT parm[2];
parm[0].vt = VT_BSTR;

const char *ansistr = fileName.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
parm[0].bstrVal = ::SysAllocString(unicodestr);

parm[1].vt = VT_I4;
int xlHtml = 44;
parm[1].intVal = xlHtml;

AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, parm[1], parm[0]);

}

ExcelWorkBook::ShowLines(const ExcelLines& excelLines)
{

int i;
IDispatch *pXlSheet;//(NULL);
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
IDispatch *pShapes;//(NULL);
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
pShapes = result.pdispVal;
}
float screenXWidth;
float screenYHeight;
screenXWidth = 900;
screenYHeight = 400;

float lowestX;
float highestX;
float lowestY;
float highestY;
float XOffest;
float YOffest;
float scalingX;
float scalingY;
lowestX = 999999;
lowestY = 999999;
highestX = 0;
highestY = 0;
for(i=0;i<excelLines.m_linesVector.size();i++)
{
ExcelLine excelLine = excelLines.m_linesVector[i];
if ( excelLine.m_startX < lowestX )
lowestX = excelLine.m_startX;
if ( excelLine.m_endX < lowestX )
lowestX = excelLine.m_endX;

if ( excelLine.m_startY < lowestY )
lowestY = excelLine.m_startY;
if ( excelLine.m_endY < lowestY )
lowestY = excelLine.m_endY;

if (excelLine.m_startX > highestX )
highestX = excelLine.m_startX;
if (excelLine.m_endX > highestX )
highestX = excelLine.m_endX;

if (excelLine.m_startY > highestY )
highestY = excelLine.m_startY;
if (excelLine.m_endY > highestY )
highestY = excelLine.m_endY;

}
float currXWidth = highestX - lowestX;
scalingX = screenXWidth/currXWidth;
float currYHeight = highestY - lowestY;
scalingY = screenYHeight/currYHeight;
std::cout << "lowestX=" << lowestX << std::endl;
std::cout << "lowestY=" << lowestY << std::endl;
std::cout << "highestX=" << highestX << std::endl;
std::cout << "highestY=" << highestY << std::endl;
std::cout << "scalingX=" << scalingX << std::endl;
std::cout << "scalingY=" << scalingY << std::endl;

for(i=0;i<excelLines.m_linesVector.size();i++)
{
ExcelLine excelLine = excelLines.m_linesVector[i];
VARIANT parm[4];
parm[0].vt = VT_R4; parm[0].fltVal = (excelLine.m_startX-lowestX)*scalingX;
parm[1].vt = VT_R4; parm[1].fltVal = (excelLine.m_startY-lowestY)*scalingY;
parm[2].vt = VT_R4; parm[2].fltVal = (excelLine.m_endX-lowestX)*scalingX;
parm[3].vt = VT_R4; parm[3].fltVal = (excelLine.m_endY-lowestY)*scalingY;
std::cout << "Original : " << excelLine.m_startX << "," << excelLine.m_startY << "," << excelLine.m_endX << "," << excelLine.m_endY << std::endl;
std::cout << "Transaformed : " << parm[0].fltVal << "," << parm[1].fltVal << "," << parm[2].fltVal << "," << parm[3].fltVal << std::endl << std::endl;

AutoWrap(DISPATCH_METHOD, NULL, pShapes, L"AddLine", 4, parm[3], parm[2], parm[1], parm[0]);

}

}
void ExcelWorkBook::ShowRange(const ExcelRange& range,IDispatch *pXlSheet)
{
int i;
int j;
IDispatch *pXlRange;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Cells", 0);
pXlRange = result.pdispVal;
}

int totalRows(0);
int totalCols(0);

totalCols= range.m_vectorOfVectors.size();
if (totalCols > 0)
totalRows = range.m_vectorOfVectors[0].size();

if (totalRows > 0)
{
VARIANT arr;
arr.vt = VT_ARRAY | VT_VARIANT;
{
// std::cout << "totalRows = " << totalRows << " totalCols = " << totalCols << std::endl;
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1; sab[0].cElements = totalRows+2;
sab[1].lLbound = 1; sab[1].cElements = totalCols+2;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
}
for (i=0; i<range.m_vectorOfVectors.size(); i++)
{
ExcelRange::StringVectorType stringVector;
stringVector = range.m_vectorOfVectors[i];
for (j=0;j < stringVector.size();j++)
{
std::string str = stringVector[j];
// std::cout << "i=" << i << " j = " << j << " " << str << std::endl;
VARIANT tmp;
tmp.vt = VT_BSTR;
const char *ansistr = str.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
tmp.bstrVal = ::SysAllocString(unicodestr);
long indices[] = {j+1,i+1};
SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
}//for end j
}//for end i
IDispatch *pXlUpperLeftCell;//(NULL);
{
VARIANT parm[2];
parm[0].vt = VT_I4; parm[0].lVal = range.m_startRow;
parm[1].vt = VT_I4; parm[1].lVal = range.m_startCol;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXlUpperLeftCell = result.pdispVal;
}

IDispatch *pXlBottomRightCell;//(NULL);
{
VARIANT parm[2];
parm[0].vt = VT_I4; parm[0].lVal = range.m_startRow+totalRows;
parm[1].vt = VT_I4; parm[1].lVal = range.m_startCol+totalCols;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXlBottomRightCell = result.pdispVal;
}
IDispatch *pXlRange3;//(NULL);
{
VARIANT parm[2];
parm[0].vt = VT_DISPATCH; parm[0].pdispVal = pXlUpperLeftCell;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlBottomRightCell;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);

pXlRange3 = result.pdispVal;
}
IDispatch *pXlRange2;//(NULL);
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(L"C5:D6");

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm);

pXlRange2 = result.pdispVal;
}
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange3, L"Value", 1, arr);
pXlRange2->Release();
pXlRange3->Release();
//pXlUpperLeftCell->Release();
//pXlBottomRightCell->Release();
VariantClear(&arr);

}//totalrows > 0

for (i=0; i<range.m_cellsVector.size(); i++)
{
int row = range.m_cellsVector[i].m_row;
int col = range.m_cellsVector[i].m_col;
std::string str = range.m_cellsVector[i].m_str;

IDispatch *pXlCell;
{
VARIANT parm[2];
parm[0].vt = VT_I4; parm[0].lVal = row;
parm[1].vt = VT_I4; parm[1].lVal = col;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXlCell = result.pdispVal;
}

{
VARIANT parm;
parm.vt = VT_BSTR;
const char *ansistr = str.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
parm.bstrVal = ::SysAllocString(unicodestr);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlCell, L"Value", 1, parm);
}
pXlCell->Release();
}

pXlRange->Release();
pXlSheet->Release();

}
ExcelWorkBook::ShowRange(const ExcelRange& range,const std::string sheetName)
{

IDispatch *pXlSheet;

if (sheetName == "")
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
else
{

VARIANT parm;
parm.vt = VT_BSTR;
const char *ansistr = sheetName.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
parm.bstrVal = ::SysAllocString(unicodestr);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 1,parm);
pXlSheet = result.pdispVal;
}
ShowRange(range,pXlSheet);

}

ExcelWorkBook::ExcelWorkBook(std::string fileName):pXlApp(NULL),pXlBooks(NULL),pXlBook(NULL)
{
if (pXlApp == NULL)
InitializeApplication();
{
VARIANT parm;
parm.vt = VT_BSTR;
const char *ansistr = fileName.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
parm.bstrVal = ::SysAllocString(unicodestr);

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open",1, parm);
pXlBook = result.pdispVal;
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
pXlSheets = result.pdispVal;
}
}

ExcelWorkBook::InitializeApplication()
{
std::string str("hello");
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);

}
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);

}
// 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);
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}

}

ExcelWorkBook::ExcelWorkBook():pXlApp(NULL),pXlBooks(NULL),pXlBook(NULL)
{
if (pXlApp == NULL)
InitializeApplication();

// Call Workbooks.Add() to get a new workbook...

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
pXlBook = result.pdispVal;
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
pXlSheets = result.pdispVal;
}
}

ExcelWorkBook::~ExcelWorkBook()
{
::MessageBox(NULL, "Click OK to Quit Excel.", "Notice", 0x10000);

// 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...

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

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

void ErrHandler(HRESULT hr, EXCEPINFO excep)
{
if(hr==DISP_E_EXCEPTION)
{
char errDesc[512];
char errMsg[512];
wcstombs(errDesc, excep.bstrDescription, 512);
sprintf(errMsg, "Run-time error %d:\n\n %s",
excep.scode & 0x0000FFFF, //Lower 16-bits of SCODE
errDesc); //Text error description
::MessageBox(NULL, errMsg, "Server Error", MB_SETFOREGROUND |
MB_OK);
}
else
{
LPVOID lpMsgBuf;
::FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM |
FORMAT_MESSAGE_IGNORE_INSERTS, NULL, hr,
MAKELANGID(LANG_NEUTRAL,
SUBLANG_DEFAULT),(LPTSTR) &lpMsgBuf,
0, NULL);
::MessageBox(NULL, (LPCTSTR)lpMsgBuf, "COM Error",
MB_OK | MB_SETFOREGROUND);
::LocalFree( lpMsgBuf );
}

}

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...
std::cout << "Before GetIDsOfNames " << std::endl;
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!
EXCEPINFO excep;
std::cout << "Before pDisp->Invoke " << std::endl;
hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, &excep, NULL);
if(FAILED(hr)) {
ErrHandler(hr, excep);
sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
MessageBox(NULL, buf, "AutoWrap()", 0x10010);
_exit(0);
return hr;
}
std::cout << "after pDisp->Invoke " << std::endl;
// End variable-argument section...
va_end(marker);

delete [] pArgs;

return hr;
}

std::vector<std::string> GetStringVector(std::vector<double> inVector)
{
int i;
std::vector<std::string> outVector;

//for (i = 0; i < 10;i++)//inVector.size(); i++)
for (i = 0; i < inVector.size(); i++)
{
char s[64];
sprintf(s,"%*.*f",6,10,inVector[i]);
std::string tmp(s);
outVector.push_back(tmp);

}
return outVector;
}

IDispatch* GetRangePointer(int startRow,int startCol,int endRow,int endCol,IDispatch* pSheet)
{
IDispatch *pXlFullRange;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pSheet, L"Cells", 0);
pXlFullRange = result.pdispVal;
}
std::cout << "pXlFullRange = " << pXlFullRange << std::endl;
IDispatch *pXlUpperLeftCell;
{
VARIANT parm[2];
parm[0].vt = VT_I4; parm[0].lVal = startRow;
parm[1].vt = VT_I4; parm[1].lVal = startCol;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlFullRange, L"Item",2, parm[1], parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXlUpperLeftCell = result.pdispVal;
}
std::cout << "pXlUpperLeftCell = " << pXlUpperLeftCell << std::endl;

IDispatch *pXlBottomRightCell;
{
VARIANT parm[2];
parm[0].vt = VT_I4; parm[0].lVal = endRow;
parm[1].vt = VT_I4; parm[1].lVal = endCol;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlFullRange, L"Item",2, parm[1], parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXlBottomRightCell = result.pdispVal;
}
std::cout << "pXlBottomRightCell = " << pXlBottomRightCell << std::endl;
IDispatch *pXlRange;
{
VARIANT parm[2];
parm[0].vt = VT_DISPATCH; parm[0].pdispVal = pXlUpperLeftCell;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlBottomRightCell;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pSheet, L"Range", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);

pXlRange = result.pdispVal;
}
std::cout << "pXlRange = " << pXlRange << std::endl;
pXlFullRange->Release();
return pXlRange;
}

BSTR GetBString(std::string string)
{
const char *ansistr = string.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
return ::SysAllocString(unicodestr);
}

Frankie Mak

Re: Excel viewer for vectors

Hi Amar,

tks for that.

However, I got error in the following lines

zerocurve1.push_back(myTermStructure->zeroCoupon(t,2));
zerocurve2.push_back(myTermStructure->zeroCoupon(t,4));

it's seems ok if I changed them to

zerocurve1.push_back(myTermStructure->zeroYield(t,2));
zerocurve2.push_back(myTermStructure->zeroYield(t,4));

any idea ?

Frankie

----- Original Message -----

From: [hidden email]

To: [hidden email]

Sent: Thursday, October 02, 2003 7:24 AM

Subject: [Quantlib-users] Excel viewer for vectors

I have attached a helper class file, which can be used for quick visualization of data in the C++ library, by adding a few lines to show something like forward curve, or printing the trinomial tree and saving in HTML file,etc.

Regards,

Amar

If anyone is interested,please read on..

Here is a sample usage in file BermudanSwaption.cpp, which shows up the fowrad and zero curves in one chart, discount curve in another :

(Header file ExcelHelper.hpp to be included at the top)

(code is inserted after getting the term structure)

   //---------------------------------------------------
        std::vector<double> forwardcurve,zerocurve1,zerocurve2,discountcurve;
        std::vector<double> timeAxis,timeAxis2;
        {
          double dbli;
          for( dbli=.1;dbli<30;dbli +=.1)
          {
            Time t = dbli;
            timeAxis2.push_back(t);
            forwardcurve.push_back(myTermStructure->instantaneousForward(t));
          }
          for( dbli=.1;dbli<30;dbli +=1)
          {
            Time t = dbli;
            timeAxis.push_back(t);
            discountcurve.push_back(myTermStructure->discount(t));
            zerocurve1.push_back(myTermStructure->zeroCoupon(t,2));
            zerocurve2.push_back(myTermStructure->zeroCoupon(t,4));
          }
          ExcelChart chart("Termstructure");
          chart.AddSeries(GetStringVector(timeAxis2),GetStringVector(forwardcurve),"forward");
          chart.AddSeries(GetStringVector(timeAxis),GetStringVector(zerocurve1));
          chart.AddSeries(GetStringVector(timeAxis),GetStringVector(zerocurve2),"zero 2");
          ExcelWorkBook workBook;
          workBook.ShowChart(chart);
          ExcelChart chart2("Discount");
          chart2.AddSeries(GetStringVector(timeAxis),GetStringVector(discountcurve),"discount");
          workBook.ShowChart(chart2);
          return 0;
        }
        //--------------------------------------------------------

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

#include <iostream>
#include <objbase.h>
#include <oleauto.h>
#include <windows.h>
#include <ole2.h>
#include <comutil.h>
#include <string>
#include <vector>
#include <stdio.h>

HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...);
std::vector<std::string> GetStringVector(std::vector<double> inVector);
IDispatch* GetRangePointer(int startRow,int startCol,int endRow,int endCol,IDispatch* pSheet);
BSTR GetBString(std::string string);

class ExcelWorkBook;

class ExcelLine
{
public:
ExcelLine(float startX,float startY,float endX,float endY):
      m_startX(startX),m_startY(startY),m_endX(endX),m_endY(endY){};
private:
float m_startX;
float m_startY;
float m_endX;
float m_endY;
friend class ExcelWorkBook;
};

class ExcelLines
{
public:
ExcelLines(){};
AddLine(const ExcelLine& excelLine);
private:
std::vector<ExcelLine> m_linesVector;
friend class ExcelWorkBook;
};
class ExcelCell
{
public:
ExcelCell(int row,int col,std::string value = "");
int m_row;
int m_col;
std::string m_str;

};

class ExcelRange
{
friend class ExcelWorkBook;

public:
typedef std::vector<std::string> StringVectorType;
ExcelRange(){};
void AddCell(const ExcelCell& cell);
void AddVector(int startRow,int startCol,int cArgs...);

private:
std::vector<ExcelCell> m_cellsVector;
std::vector<StringVectorType> m_vectorOfVectors;
int m_startRow;
int m_startCol;
};

class ExcelChart
{
public:
typedef std::vector<std::string> StringVectorType;
ExcelChart(std::string chartName = "");
void AddSeries(std::vector<std::string> xAxisVec,std::vector<std::string> seriesVector,std::string seriesName= "");
private:
friend class ExcelWorkBook;
std::vector<StringVectorType> m_xAxisVectors;
std::vector<StringVectorType> m_seriesVectors;
StringVectorType m_seriesNames;
std::string m_chartName;
};

class ExcelWorkBook
{
public :

ExcelWorkBook();
ExcelWorkBook(std::string fileName);
ShowRange(const ExcelRange& range,const std::string sheetName = "");
ShowLines(const ExcelLines& excelLines);
SaveAsHTML(const std::string fileName);
void ShowChart(const ExcelChart& chart);
virtual ~ExcelWorkBook();
private:
void ShowRange(const ExcelRange& range,IDispatch *pXlSheet);
InitializeApplication();

IDispatch *pXlApp;
IDispatch *pXlBooks;
IDispatch *pXlBook;
IDispatch *pXlSheets;
};

//---------------------------------------------------------------------------------

void ExcelWorkBook::ShowChart(const ExcelChart& chart)
{
if (chart.m_xAxisVectors.size() == 0)
{
    ::MessageBox(NULL, "ExcelWorkBook::ShowChart -> No data to show. Please polulate.", "Notice", 0x10000);
    return;
}
int i,j;

std::cout << "pxlbook = " << pXlBook << std::endl;
std::cout << "pXlSheets = " << pXlSheets << std::endl;
IDispatch* pXLCharts;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Charts",0);
pXLCharts = result.pdispVal;
}
std::cout << "pXLCharts = " << pXLCharts << std::endl;
VARIANT sheetsCount;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Count",0);
sheetsCount = result;
}
IDispatch *pXlLastSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1,sheetsCount);
pXlLastSheet = result.pdispVal;
    }
std::cout << "pXlLastSheet = " << pXlLastSheet << std::endl;

IDispatch* pXLChart;
{
VARIANT parm[2];
parm[0].vt = VT_ERROR; parm[0].scode = DISP_E_PARAMNOTFOUND;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlLastSheet;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLCharts, L"Add", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXLChart = result.pdispVal;
}
std::cout << "pXLChart = " << pXLChart << std::endl;
{
VARIANT parm;
int xlLine = 4;
int xlXYScatterLines = 74;
parm.vt = VT_I4; parm.lVal = xlXYScatterLines;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLChart, L"ChartType", 1, parm);
}
if (chart.m_chartName != "")
{
VARIANT parm;
    std::string chartName;
    chartName = chart.m_chartName + "_Chart";
parm.vt = VT_BSTR; parm.bstrVal = GetBString(chartName);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLChart, L"Name", 1, parm);
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Count",0);
sheetsCount = result;
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1,sheetsCount);
pXlLastSheet = result.pdispVal;
}
std::cout << "pXlLastSheet = " << pXlLastSheet << std::endl;
IDispatch* pXLNewChartSheet;
{
VARIANT parm[2];
parm[0].vt = VT_ERROR;; parm[0].scode = DISP_E_PARAMNOTFOUND;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlLastSheet;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlSheets, L"Add", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXLNewChartSheet = result.pdispVal;
}
std::cout << "pXLNewChartSheet = " << pXLNewChartSheet << std::endl;

if (chart.m_chartName != "")
{
VARIANT parm;
    std::string chartName;
    chartName = chart.m_chartName + "_Data";
parm.vt = VT_BSTR; parm.bstrVal = GetBString(chartName);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLNewChartSheet, L"Name", 1, parm);
}
IDispatch* pXLSeriesCollection;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLChart, L"SeriesCollection",0);
pXLSeriesCollection = result.pdispVal;
}
std::cout << "pXLSeriesCollection = " << pXLSeriesCollection << std::endl;
IDispatch* pXLSeries;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLSeriesCollection, L"NewSeries",0);
pXLSeries = result.pdispVal;
}
std::cout << "pXLSeries = " << pXLSeries << std::endl;
int totalRecords(0);
int totalSeries(0);

totalSeries= chart.m_seriesVectors.size();
if (totalSeries > 0)
totalRecords = chart.m_seriesVectors[0].size();
IDispatch* pXLXAxis;
{
VARIANT parm;
int xlCategory = 1;
parm.vt = VT_I4; parm.lVal = xlCategory;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLChart, L"Axes", 1, parm);
    VariantClear(&parm);
    pXLXAxis = result.pdispVal;

}
std::cout << "pXLXAxis = " << pXLXAxis << std::endl;
IDispatch* pXLXAxisTickLabels;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXLXAxis, L"TickLabels", 0);
    pXLXAxisTickLabels = result.pdispVal;
}
std::cout << "pXLXAxisTickLabels = " << pXLXAxisTickLabels << std::endl;
{
    VARIANT parm;
    int xlTickLabelOrientationUpward = -4171;
    parm.vt = VT_I4; parm.lVal = xlTickLabelOrientationUpward;
    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLXAxisTickLabels, L"Orientation",1,parm);
    VariantClear(&parm);
}
{
    VARIANT parm;
    parm.vt = VT_BSTR;
    std::string format("0.00");
    const char *ansistr = format.c_str();
    int a = lstrlenA(ansistr)+1;
    BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
    MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
    parm.bstrVal = ::SysAllocString(unicodestr);

    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLXAxisTickLabels, L"NumberFormat",1,parm);
    VariantClear(&parm);
}

for(i=0;i< chart.m_xAxisVectors.size();i++)
{
std::cout << "i = " << i << std::endl;
std::cout << "X-axis length = " << chart.m_xAxisVectors[i].size() << std::endl;
pXLNewChartSheet->AddRef();
ExcelRange rangeObjXAxis;
rangeObjXAxis.AddVector(1,i*2+1,1,chart.m_xAxisVectors[i]);
ShowRange(rangeObjXAxis,pXLNewChartSheet);
IDispatch* pXAxisRange = GetRangePointer(1,i*2+1,chart.m_xAxisVectors[i].size(),i*2+1,pXLNewChartSheet);
std::cout << "pXAxisRange = " << pXAxisRange << std::endl;

    pXLNewChartSheet->AddRef();
    ExcelRange rangeObjSeries;
    rangeObjSeries.AddVector(1,i*2+2,1,chart.m_seriesVectors[i]);
    ShowRange(rangeObjSeries,pXLNewChartSheet);
    IDispatch* pSeriesRange = GetRangePointer(1,i*2+2,chart.m_seriesVectors[i].size(),i*2+2,pXLNewChartSheet);
std::cout << "pSeriesRange = " << pSeriesRange << std::endl;
if (i > 0)
    {
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_METHOD, &result, pXLSeriesCollection, L"NewSeries",0);
      pXLSeries = result.pdispVal;
    }
    if (chart.m_seriesNames[i] != "")
    {
      VARIANT parm;
      parm.vt = VT_BSTR; parm.bstrVal = GetBString(chart.m_seriesNames[i]);
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"Name", 1, parm);
    }
    {
      VARIANT parm;
      int xlMarkerStyleNone = -4142;
      parm.vt = VT_I4; parm.lVal = xlMarkerStyleNone;
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"MarkerStyle",1,parm);
      VariantClear(&parm);
    }
{
VARIANT parm;
parm.vt = VT_DISPATCH; parm.pdispVal = pXAxisRange;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"XValues",1,parm);
VariantClear(&parm);
    }
    {
      VARIANT parm;
      parm.vt = VT_DISPATCH; parm.pdispVal = pSeriesRange;
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"Values",1,parm);
      VariantClear(&parm);
    }

}

AutoWrap(DISPATCH_METHOD, NULL, pXLChart, L"Activate",0);

pXLSeriesCollection->Release();
pXLChart->Release();
//pXlLastSheet->Release();
pXLCharts->Release();

return;

}

ExcelChart::ExcelChart(std::string chartName)
{
m_chartName = chartName;
}

void ExcelChart::AddSeries(std::vector<std::string> xAxisVec,std::vector<std::string> seriesVector,std::string seriesName)
{
if (xAxisVec.size() == 0)
{
    ::MessageBox(NULL, "ExcelChart::AddSeries -> X-axis is empty. Please polulate.", "Notice", 0x10000);
    return;
}
if (seriesVector.size() == 0)
{
::MessageBox(NULL, "ExcelChart::AddSeries -> Series is empty. Please polulate.", "Notice", 0x10000);
return;
}
if (seriesVector.size() != xAxisVec.size())
{
::MessageBox(NULL, "ExcelChart::AddSeries -> X-axis and Series length should be same", "Notice", 0x10000);
return;
}
m_xAxisVectors.push_back(xAxisVec);
m_seriesVectors.push_back(seriesVector);
m_seriesNames.push_back(seriesName);
}

ExcelLines::AddLine(const ExcelLine& excelLine)
{
m_linesVector.push_back(excelLine);
}

ExcelCell::ExcelCell(int row,int col,std::string value):m_row(row),m_col(col),m_str(value)
{

}

void ExcelRange::AddVector(int startRow,int startCol,int cArgs...)
{
if (m_vectorOfVectors.size() > 0)
{
    ::MessageBox(NULL, "This range is already populated in the object.Create one more range object.", "Notice", 0x10000);
    return;
}
m_startRow = startRow;
m_startCol = startCol;
va_list marker;
va_start(marker, cArgs);
int tempValue;
int tempValueprev;
tempValue = 0;
tempValueprev = 0;
for (int i = 0; i < cArgs;i++)
{
    StringVectorType hj;
    hj = va_arg(marker, StringVectorType);
    tempValue = hj.size();
    if ( (tempValueprev > 0) && (tempValue != tempValueprev) )
    {
      m_vectorOfVectors.clear();
      ::MessageBox(NULL, "All columns should be of equal length for ExcelRange::AddVector", "Notice", 0x10000);
      return;
    }
    tempValueprev = tempValue;
    m_vectorOfVectors.push_back(hj);
}
}

void ExcelRange::AddCell(const ExcelCell& cell)
{
m_cellsVector.push_back(cell);
}

ExcelWorkBook::SaveAsHTML(const std::string fileName)
{
VARIANT parm[2];
parm[0].vt = VT_BSTR;

const char *ansistr = fileName.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
parm[0].bstrVal = ::SysAllocString(unicodestr);

parm[1].vt = VT_I4;
int xlHtml = 44;
parm[1].intVal = xlHtml;

AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, parm[1], parm[0]);

}

ExcelWorkBook::ShowLines(const ExcelLines& excelLines)
{

int i;
IDispatch *pXlSheet;//(NULL);
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
    pXlSheet = result.pdispVal;
}
IDispatch *pShapes;//(NULL);
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
    pShapes = result.pdispVal;
}
float screenXWidth;
float screenYHeight;
screenXWidth = 900;
screenYHeight = 400;

float lowestX;
float highestX;
float lowestY;
float highestY;
float XOffest;
float YOffest;
float scalingX;
float scalingY;
lowestX = 999999;
lowestY = 999999;
highestX = 0;
highestY = 0;
for(i=0;i<excelLines.m_linesVector.size();i++)
{
ExcelLine excelLine = excelLines.m_linesVector[i];
if ( excelLine.m_startX < lowestX )
lowestX = excelLine.m_startX;
if ( excelLine.m_endX < lowestX )
lowestX = excelLine.m_endX;

if ( excelLine.m_startY < lowestY )
lowestY = excelLine.m_startY;
if ( excelLine.m_endY < lowestY )
lowestY = excelLine.m_endY;

    if (excelLine.m_startX > highestX )
highestX = excelLine.m_startX;
    if (excelLine.m_endX > highestX )
highestX = excelLine.m_endX;

    if (excelLine.m_startY > highestY )
highestY = excelLine.m_startY;
    if (excelLine.m_endY > highestY )
highestY = excelLine.m_endY;

}
float currXWidth = highestX - lowestX;
scalingX = screenXWidth/currXWidth;
float currYHeight = highestY - lowestY;
scalingY = screenYHeight/currYHeight;
std::cout << "lowestX=" << lowestX << std::endl;
std::cout << "lowestY=" << lowestY << std::endl;
std::cout << "highestX=" << highestX << std::endl;
std::cout << "highestY=" << highestY << std::endl;
std::cout << "scalingX=" << scalingX << std::endl;
std::cout << "scalingY=" << scalingY << std::endl;

for(i=0;i<excelLines.m_linesVector.size();i++)
{
    ExcelLine excelLine = excelLines.m_linesVector[i];
    VARIANT parm[4];
    parm[0].vt = VT_R4; parm[0].fltVal = (excelLine.m_startX-lowestX)*scalingX;
    parm[1].vt = VT_R4; parm[1].fltVal = (excelLine.m_startY-lowestY)*scalingY;
    parm[2].vt = VT_R4; parm[2].fltVal = (excelLine.m_endX-lowestX)*scalingX;
    parm[3].vt = VT_R4; parm[3].fltVal = (excelLine.m_endY-lowestY)*scalingY;
std::cout << "Original : " << excelLine.m_startX << "," << excelLine.m_startY << "," << excelLine.m_endX << "," << excelLine.m_endY << std::endl;
    std::cout << "Transaformed : " << parm[0].fltVal << "," << parm[1].fltVal << "," << parm[2].fltVal << "," << parm[3].fltVal << std::endl << std::endl;

    AutoWrap(DISPATCH_METHOD, NULL, pShapes, L"AddLine", 4, parm[3], parm[2], parm[1], parm[0]);


}

}
void ExcelWorkBook::ShowRange(const ExcelRange& range,IDispatch *pXlSheet)
{
int i;
int j;
IDispatch *pXlRange;
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Cells", 0);
    pXlRange = result.pdispVal;
}

int totalRows(0);
int totalCols(0);

totalCols= range.m_vectorOfVectors.size();
if (totalCols > 0)
    totalRows = range.m_vectorOfVectors[0].size();

if (totalRows > 0)
{
    VARIANT arr;
    arr.vt = VT_ARRAY | VT_VARIANT;
    {
      // std::cout << "totalRows = " << totalRows << " totalCols = " << totalCols << std::endl;
      SAFEARRAYBOUND sab[2];
      sab[0].lLbound = 1; sab[0].cElements = totalRows+2;
      sab[1].lLbound = 1; sab[1].cElements = totalCols+2;
      arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
    }
    for (i=0; i<range.m_vectorOfVectors.size(); i++)
    {
      ExcelRange::StringVectorType stringVector;
      stringVector = range.m_vectorOfVectors[i];
      for (j=0;j < stringVector.size();j++)
      {
        std::string str = stringVector[j];
        // std::cout << "i=" << i << " j = " << j << "   " << str << std::endl;
        VARIANT tmp;
        tmp.vt = VT_BSTR;
        const char *ansistr = str.c_str();
        int a = lstrlenA(ansistr)+1;
        BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
        MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
        tmp.bstrVal = ::SysAllocString(unicodestr);
        long indices[] = {j+1,i+1};
        SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
      }//for end j
    }//for end i
    IDispatch *pXlUpperLeftCell;//(NULL);
    {
      VARIANT parm[2];
      parm[0].vt = VT_I4; parm[0].lVal = range.m_startRow;
      parm[1].vt = VT_I4; parm[1].lVal = range.m_startCol;
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);
      pXlUpperLeftCell = result.pdispVal;
    }

    IDispatch *pXlBottomRightCell;//(NULL);
    {
      VARIANT parm[2];
      parm[0].vt = VT_I4; parm[0].lVal = range.m_startRow+totalRows;
      parm[1].vt = VT_I4; parm[1].lVal = range.m_startCol+totalCols;
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);
      pXlBottomRightCell = result.pdispVal;
    }
    IDispatch *pXlRange3;//(NULL);
    {
      VARIANT parm[2];
      parm[0].vt = VT_DISPATCH; parm[0].pdispVal = pXlUpperLeftCell;
      parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlBottomRightCell;

      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 2, parm[1],parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);

      pXlRange3 = result.pdispVal;
    }
    IDispatch *pXlRange2;//(NULL);
    {
      VARIANT parm;
      parm.vt = VT_BSTR;
      parm.bstrVal = ::SysAllocString(L"C5:D6");

      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
      VariantClear(&parm);

      pXlRange2 = result.pdispVal;
    }
    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange3, L"Value", 1, arr);
    pXlRange2->Release();
    pXlRange3->Release();
    //pXlUpperLeftCell->Release();
    //pXlBottomRightCell->Release();
    VariantClear(&arr);


}//totalrows > 0

for (i=0; i<range.m_cellsVector.size(); i++)
{
    int row = range.m_cellsVector[i].m_row;
    int col = range.m_cellsVector[i].m_col;
    std::string str = range.m_cellsVector[i].m_str;


    IDispatch *pXlCell;
    {
      VARIANT parm[2];
      parm[0].vt = VT_I4; parm[0].lVal = row;
      parm[1].vt = VT_I4; parm[1].lVal = col;
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);
      pXlCell = result.pdispVal;
    }

    {
      VARIANT parm;
      parm.vt = VT_BSTR;
      const char *ansistr = str.c_str();
      int a = lstrlenA(ansistr)+1;
      BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
      MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
      parm.bstrVal = ::SysAllocString(unicodestr);
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlCell, L"Value", 1, parm);
    }
    pXlCell->Release();
}

pXlRange->Release();
pXlSheet->Release();

}
ExcelWorkBook::ShowRange(const ExcelRange& range,const std::string sheetName)
{

IDispatch *pXlSheet;

if (sheetName == "")
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
    pXlSheet = result.pdispVal;
}
else
{

    VARIANT parm;
    parm.vt = VT_BSTR;
    const char *ansistr = sheetName.c_str();
    int a = lstrlenA(ansistr)+1;
    BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
    MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
    parm.bstrVal = ::SysAllocString(unicodestr);
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 1,parm);
    pXlSheet = result.pdispVal;
}
ShowRange(range,pXlSheet);

}

ExcelWorkBook::ExcelWorkBook(std::string fileName):pXlApp(NULL),pXlBooks(NULL),pXlBook(NULL)
{
if (pXlApp == NULL)
    InitializeApplication();
{
    VARIANT parm;
    parm.vt = VT_BSTR;
const char *ansistr = fileName.c_str();
    int a = lstrlenA(ansistr)+1;
    BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
    MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
    parm.bstrVal = ::SysAllocString(unicodestr);

    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open",1, parm);
    pXlBook = result.pdispVal;
}
{
          VARIANT result;
          VariantInit(&result);
          AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
          pXlSheets = result.pdispVal;
}
}

ExcelWorkBook::InitializeApplication()
{
std::string str("hello");
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);

}
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
    ::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);

}
// 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);
}
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
    pXlBooks = result.pdispVal;
}

}

ExcelWorkBook::ExcelWorkBook():pXlApp(NULL),pXlBooks(NULL),pXlBook(NULL)
{
if (pXlApp == NULL)
    InitializeApplication();

// Call Workbooks.Add() to get a new workbook...

{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
    pXlBook = result.pdispVal;
}
   {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
        pXlSheets = result.pdispVal;
}
}

ExcelWorkBook::~ExcelWorkBook()
{
   ::MessageBox(NULL, "Click OK to Quit Excel.", "Notice", 0x10000);

   // 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...

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

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

void ErrHandler(HRESULT hr, EXCEPINFO excep)
{
    if(hr==DISP_E_EXCEPTION)
    {
        char errDesc[512];
        char errMsg[512];
        wcstombs(errDesc, excep.bstrDescription, 512);
        sprintf(errMsg, "Run-time error %d:\n\n %s",
                excep.scode & 0x0000FFFF, //Lower 16-bits of SCODE
                errDesc);                  //Text error description
        ::MessageBox(NULL, errMsg, "Server Error", MB_SETFOREGROUND |
                     MB_OK);
    }
    else
    {
        LPVOID lpMsgBuf;
        ::FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER |
                        FORMAT_MESSAGE_FROM_SYSTEM |
                        FORMAT_MESSAGE_IGNORE_INSERTS, NULL, hr,
                        MAKELANGID(LANG_NEUTRAL,
                        SUBLANG_DEFAULT),(LPTSTR) &lpMsgBuf,
                        0, NULL);
        ::MessageBox(NULL, (LPCTSTR)lpMsgBuf, "COM Error",
                     MB_OK | MB_SETFOREGROUND);
        ::LocalFree( lpMsgBuf );
    }

}

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...
std::cout << "Before GetIDsOfNames " << std::endl;
    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!
EXCEPINFO excep;
    std::cout << "Before pDisp->Invoke " << std::endl;
    hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, &excep, NULL);
    if(FAILED(hr)) {
    ErrHandler(hr, excep);
        sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
        MessageBox(NULL, buf, "AutoWrap()", 0x10010);
        _exit(0);
        return hr;
    }
std::cout << "after pDisp->Invoke " << std::endl;
    // End variable-argument section...
    va_end(marker);

    delete [] pArgs;

    return hr;
}

std::vector<std::string> GetStringVector(std::vector<double> inVector)
{
int i;
std::vector<std::string> outVector;

//for (i = 0; i < 10;i++)//inVector.size(); i++)
for (i = 0; i < inVector.size(); i++)
{
    char s[64];
    sprintf(s,"%*.*f",6,10,inVector[i]);
    std::string tmp(s);
    outVector.push_back(tmp);

}
return outVector;
}

IDispatch* GetRangePointer(int startRow,int startCol,int endRow,int endCol,IDispatch* pSheet)
{
IDispatch *pXlFullRange;
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pSheet, L"Cells", 0);
    pXlFullRange = result.pdispVal;
}
std::cout << "pXlFullRange = " << pXlFullRange << std::endl;
IDispatch *pXlUpperLeftCell;
{
    VARIANT parm[2];
    parm[0].vt = VT_I4; parm[0].lVal = startRow;
    parm[1].vt = VT_I4; parm[1].lVal = startCol;
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlFullRange, L"Item",2, parm[1], parm[0]);
    VariantClear(&parm[0]);
    VariantClear(&parm[1]);
    pXlUpperLeftCell = result.pdispVal;
}
std::cout << "pXlUpperLeftCell = " << pXlUpperLeftCell << std::endl;

IDispatch *pXlBottomRightCell;
{
    VARIANT parm[2];
    parm[0].vt = VT_I4; parm[0].lVal = endRow;
    parm[1].vt = VT_I4; parm[1].lVal = endCol;
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlFullRange, L"Item",2, parm[1], parm[0]);
    VariantClear(&parm[0]);
    VariantClear(&parm[1]);
    pXlBottomRightCell = result.pdispVal;
}
std::cout << "pXlBottomRightCell = " << pXlBottomRightCell << std::endl;
IDispatch *pXlRange;
{
    VARIANT parm[2];
    parm[0].vt = VT_DISPATCH; parm[0].pdispVal = pXlUpperLeftCell;
    parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlBottomRightCell;

    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pSheet, L"Range", 2, parm[1],parm[0]);
    VariantClear(&parm[0]);
    VariantClear(&parm[1]);

    pXlRange = result.pdispVal;
}
std::cout << "pXlRange = " << pXlRange << std::endl;
pXlFullRange->Release();
return pXlRange;
}

BSTR GetBString(std::string string)
{
const char *ansistr = string.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
return ::SysAllocString(unicodestr);
}

amar singh

Re: Excel viewer for vectors

On the top of my head , I remember there is an open defect in the termstructure::zerocoupon function for 0/0 division, but I don't know if it is related. I have used a temporary or dummy fix in my local copy .

I wonder if Nando/Ligui can give a temporary fix for the implemenation to apply to our local copy, that will be great.

Regards,

Amar
Astroboy <[hidden email]> wrote:

Hi Amar,

tks for that.

However, I got error in the following lines

zerocurve1.push_back(myTermStructure->zeroCoupon(t,2));
zerocurve2.push_back(myTermStructure->zeroCoupon(t,4));

it's seems ok if I changed them to

zerocurve1.push_back(myTermStructure->zeroYield(t,2));
zerocurve2.push_back(myTermStructure->zeroYield(t,4));

any idea ?

Frankie

----- Original Message -----

From: [hidden email]

To: [hidden email]

Sent: Thursday, October 02, 2003 7:24 AM

Subject: [Quantlib-users] Excel viewer for vectors

I have attached a helper class file, which can be used for quick visualization of data in the C++ library, by adding a few lines to show something like forward curve, or printing the trinomial tree and saving in HTML file,etc.

Regards,

Amar

If anyone is interested,please read on..

Here is a sample usage in file BermudanSwaption.cpp, which shows up the fowrad and zero curves in one chart, discount curve in another :

(Header file ExcelHelper.hpp to be included at the top)

(code is inserted after getting the term structure)

   //---------------------------------------------------
        std::vector<double> forwardcurve,zerocurve1,zerocurve2,discountcurve;
        std::vector<double> timeAxis,timeAxis2;
        {
          double dbli;
          for( dbli=.1;dbli<30;dbli +=.1)
          {
            Time t = dbli;
            timeAxis2.push_back(t);
            forwardcurve.push_back(myTermStructure->instantaneousForward(t));
          }
          for( dbli=.1;dbli<30;dbli +=1)
          {
            Time t = dbli;
            timeAxis.push_back(t);
            discountcurve.push_back(myTermStructure->discount(t));
            zerocurve1.push_back(myTermStructure->zeroCoupon(t,2));
            zerocurve2.push_back(myTermStructure->zeroCoupon(t,4));
          }
          ExcelChart chart("Termstructure");
          chart.AddSeries(GetStringVector(timeAxis2),GetStringVector(forwardcurve),"forward");
          chart.AddSeries(GetStringVector(timeAxis),GetStringVector(zerocurve1));
          chart.AddSeries(GetStringVector(timeAxis),GetStringVector(zerocurve2),"zero 2");
          ExcelWorkBook workBook;
          workBook.ShowChart(chart);
          ExcelChart chart2("Discount");
          chart2.AddSeries(GetStringVector(timeAxis),GetStringVector(discountcurve),"discount");
          workBook.ShowChart(chart2);
          return 0;
        }
        //--------------------------------------------------------

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

#include <iostream>
#include <objbase.h>
#include <oleauto.h>
#include <windows.h>
#include <ole2.h>
#include <comutil.h>
#include <string>
#include <vector>
#include <stdio.h>

HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...);
std::vector<std::string> GetStringVector(std::vector<double> inVector);
IDispatch* GetRangePointer(int startRow,int startCol,int endRow,int endCol,IDispatch* pSheet);
BSTR GetBString(std::string string);

class ExcelWorkBook;

class ExcelLine
{
public:
ExcelLine(float startX,float startY,float endX,float endY):
      m_startX(startX),m_startY(startY),m_endX(endX),m_endY(endY){};
private:
float m_startX;
float m_startY;
float m_endX;
float m_endY;
friend class ExcelWorkBook;
};

class ExcelLines
{
public:
ExcelLines(){};
AddLine(const ExcelLine& excelLine);
private:
std::vector<ExcelLine> m_linesVector;
friend class ExcelWorkBook;
};
class ExcelCell
{
public:
ExcelCell(int row,int col,std::string value = "");
int m_row;
int m_col;
std::string m_str;

};

class ExcelRange
{
friend class ExcelWorkBook;

public:
typedef std::vector<std::string> StringVectorType;
ExcelRange(){};
void AddCell(const ExcelCell& cell);
void AddVector(int startRow,int startCol,int cArgs...);

private:
std::vector<ExcelCell> m_cellsVector;
std::vector<StringVectorType> m_vectorOfVectors;
int m_startRow;
int m_startCol;
};

class ExcelChart
{
public:
typedef std::vector<std::string> StringVectorType;
ExcelChart(std::string chartName = "");
void AddSeries(std::vector<std::string> xAxisVec,std::vector<std::string> seriesVector,std::string seriesName= "");
private:
friend class ExcelWorkBook;
std::vector<StringVectorType> m_xAxisVectors;
std::vector<StringVectorType> m_seriesVectors;
StringVectorType m_seriesNames;
std::string m_chartName;
};

class ExcelWorkBook
{
public :

ExcelWorkBook();
ExcelWorkBook(std::string fileName);
ShowRange(const ExcelRange& range,const std::string sheetName = "");
ShowLines(const ExcelLines& excelLines);
SaveAsHTML(const std::string fileName);
void ShowChart(const ExcelChart& chart);
virtual ~ExcelWorkBook();
private:
void ShowRange(const ExcelRange& range,IDispatch *pXlSheet);
InitializeApplication();

IDispatch *pXlApp;
IDispatch *pXlBooks;
IDispatch *pXlBook;
IDispatch *pXlSheets;
};

//---------------------------------------------------------------------------------

void ExcelWorkBook::ShowChart(const ExcelChart& chart)
{
if (chart.m_xAxisVectors.size() == 0)
{
    ::MessageBox(NULL, "ExcelWorkBook::ShowChart -> No data to show. Please polulate.", "Notice", 0x10000);
    return;
}
int i,j;

std::cout << "pxlbook = " << pXlBook << std::endl;
std::cout << "pXlSheets = " << pXlSheets << std::endl;
IDispatch* pXLCharts;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Charts",0);
pXLCharts = result.pdispVal;
}
std::cout << "pXLCharts = " << pXLCharts << std::endl;
VARIANT sheetsCount;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Count",0);
sheetsCount = result;
}
IDispatch *pXlLastSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1,sheetsCount);
pXlLastSheet = result.pdispVal;
    }
std::cout << "pXlLastSheet = " << pXlLastSheet << std::endl;

IDispatch* pXLChart;
{
VARIANT parm[2];
parm[0].vt = VT_ERROR; parm[0].scode = DISP_E_PARAMNOTFOUND;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlLastSheet;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLCharts, L"Add", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXLChart = result.pdispVal;
}
std::cout << "pXLChart = " << pXLChart << std::endl;
{
VARIANT parm;
int xlLine = 4;
int xlXYScatterLines = 74;
parm.vt = VT_I4; parm.lVal = xlXYScatterLines;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLChart, L"ChartType", 1, parm);
}
if (chart.m_chartName != "")
{
VARIANT parm;
    std::string chartName;
    chartName = chart.m_chartName + "_Chart";
parm.vt = VT_BSTR; parm.bstrVal = GetBString(chartName);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLChart, L"Name", 1, parm);
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Count",0);
sheetsCount = result;
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 1,sheetsCount);
pXlLastSheet = result.pdispVal;
}
std::cout << "pXlLastSheet = " << pXlLastSheet << std::endl;
IDispatch* pXLNewChartSheet;
{
VARIANT parm[2];
parm[0].vt = VT_ERROR;; parm[0].scode = DISP_E_PARAMNOTFOUND;
parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlLastSheet;

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlSheets, L"Add", 2, parm[1],parm[0]);
VariantClear(&parm[0]);
VariantClear(&parm[1]);
pXLNewChartSheet = result.pdispVal;
}
std::cout << "pXLNewChartSheet = " << pXLNewChartSheet << std::endl;

if (chart.m_chartName != "")
{
VARIANT parm;
    std::string chartName;
    chartName = chart.m_chartName + "_Data";
parm.vt = VT_BSTR; parm.bstrVal = GetBString(chartName);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLNewChartSheet, L"Name", 1, parm);
}
IDispatch* pXLSeriesCollection;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLChart, L"SeriesCollection",0);
pXLSeriesCollection = result.pdispVal;
}
std::cout << "pXLSeriesCollection = " << pXLSeriesCollection << std::endl;
IDispatch* pXLSeries;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLSeriesCollection, L"NewSeries",0);
pXLSeries = result.pdispVal;
}
std::cout << "pXLSeries = " << pXLSeries << std::endl;
int totalRecords(0);
int totalSeries(0);

totalSeries= chart.m_seriesVectors.size();
if (totalSeries > 0)
totalRecords = chart.m_seriesVectors[0].size();
IDispatch* pXLXAxis;
{
VARIANT parm;
int xlCategory = 1;
parm.vt = VT_I4; parm.lVal = xlCategory;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXLChart, L"Axes", 1, parm);
    VariantClear(&parm);
    pXLXAxis = result.pdispVal;

}
std::cout << "pXLXAxis = " << pXLXAxis << std::endl;
IDispatch* pXLXAxisTickLabels;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXLXAxis, L"TickLabels", 0);
    pXLXAxisTickLabels = result.pdispVal;
}
std::cout << "pXLXAxisTickLabels = " << pXLXAxisTickLabels << std::endl;
{
    VARIANT parm;
    int xlTickLabelOrientationUpward = -4171;
    parm.vt = VT_I4; parm.lVal = xlTickLabelOrientationUpward;
    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLXAxisTickLabels, L"Orientation",1,parm);
    VariantClear(&parm);
}
{
    VARIANT parm;
    parm.vt = VT_BSTR;
    std::string format("0.00");
    const char *ansistr = format.c_str();
    int a = lstrlenA(ansistr)+1;
    BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
    MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
    parm.bstrVal = ::SysAllocString(unicodestr);

    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLXAxisTickLabels, L"NumberFormat",1,parm);
    VariantClear(&parm);
}

for(i=0;i< chart.m_xAxisVectors.size();i++)
{
std::cout << "i = " << i << std::endl;
std::cout << "X-axis length = " << chart.m_xAxisVectors[i].size() << std::endl;
pXLNewChartSheet->AddRef();
ExcelRange rangeObjXAxis;
rangeObjXAxis.AddVector(1,i*2+1,1,chart.m_xAxisVectors[i]);
ShowRange(rangeObjXAxis,pXLNewChartSheet);
IDispatch* pXAxisRange = GetRangePointer(1,i*2+1,chart.m_xAxisVectors[i].size(),i*2+1,pXLNewChartSheet);
std::cout << "pXAxisRange = " << pXAxisRange << std::endl;

    pXLNewChartSheet->AddRef();
    ExcelRange rangeObjSeries;
    rangeObjSeries.AddVector(1,i*2+2,1,chart.m_seriesVectors[i]);
    ShowRange(rangeObjSeries,pXLNewChartSheet);
    IDispatch* pSeriesRange = GetRangePointer(1,i*2+2,chart.m_seriesVectors[i].size(),i*2+2,pXLNewChartSheet);
std::cout << "pSeriesRange = " << pSeriesRange << std::endl;
if (i > 0)
    {
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_METHOD, &result, pXLSeriesCollection, L"NewSeries",0);
      pXLSeries = result.pdispVal;
    }
    if (chart.m_seriesNames[i] != "")
    {
      VARIANT parm;
      parm.vt = VT_BSTR; parm.bstrVal = GetBString(chart.m_seriesNames[i]);
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"Name", 1, parm);
    }
    {
      VARIANT parm;
      int xlMarkerStyleNone = -4142;
      parm.vt = VT_I4; parm.lVal = xlMarkerStyleNone;
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"MarkerStyle",1,parm);
      VariantClear(&parm);
    }
{
VARIANT parm;
parm.vt = VT_DISPATCH; parm.pdispVal = pXAxisRange;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"XValues",1,parm);
VariantClear(&parm);
    }
    {
      VARIANT parm;
      parm.vt = VT_DISPATCH; parm.pdispVal = pSeriesRange;
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXLSeries, L"Values",1,parm);
      VariantClear(&parm);
    }

}

AutoWrap(DISPATCH_METHOD, NULL, pXLChart, L"Activate",0);

pXLSeriesCollection->Release();
pXLChart->Release();
//pXlLastSheet->Release();
pXLCharts->Release();

return;

}

ExcelChart::ExcelChart(std::string chartName)
{
m_chartName = chartName;
}

void ExcelChart::AddSeries(std::vector<std::string> xAxisVec,std::vector<std::string> seriesVector,std::string seriesName)
{
if (xAxisVec.size() == 0)
{
    ::MessageBox(NULL, "ExcelChart::AddSeries -> X-axis is empty. Please polulate.", "Notice", 0x10000);
    return;
}
if (seriesVector.size() == 0)
{
::MessageBox(NULL, "ExcelChart::AddSeries -> Series is empty. Please polulate.", "Notice", 0x10000);
return;
}
if (seriesVector.size() != xAxisVec.size())
{
::MessageBox(NULL, "ExcelChart::AddSeries -> X-axis and Series length should be same", "Notice", 0x10000);
return;
}
m_xAxisVectors.push_back(xAxisVec);
m_seriesVectors.push_back(seriesVector);
m_seriesNames.push_back(seriesName);
}

ExcelLines::AddLine(const ExcelLine& excelLine)
{
m_linesVector.push_back(excelLine);
}

ExcelCell::ExcelCell(int row,int col,std::string value):m_row(row),m_col(col),m_str(value)
{

}

void ExcelRange::AddVector(int startRow,int startCol,int cArgs...)
{
if (m_vectorOfVectors.size() > 0)
{
    ::MessageBox(NULL, "This range is already populated in the object.Create one more range object.", "Notice", 0x10000);
    return;
}
m_startRow = startRow;
m_startCol = startCol;
va_list marker;
va_start(marker, cArgs);
int tempValue;
int tempValueprev;
tempValue = 0;
tempValueprev = 0;
for (int i = 0; i < cArgs;i++)
{
    StringVectorType hj;
    hj = va_arg(marker, StringVectorType);
    tempValue = hj.size();
    if ( (tempValueprev > 0) && (tempValue != tempValueprev) )
    {
      m_vectorOfVectors.clear();
      ::MessageBox(NULL, "All columns should be of equal length for ExcelRange::AddVector", "Notice", 0x10000);
      return;
    }
    tempValueprev = tempValue;
    m_vectorOfVectors.push_back(hj);
}
}

void ExcelRange::AddCell(const ExcelCell& cell)
{
m_cellsVector.push_back(cell);
}

ExcelWorkBook::SaveAsHTML(const std::string fileName)
{
VARIANT parm[2];
parm[0].vt = VT_BSTR;

const char *ansistr = fileName.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
parm[0].bstrVal = ::SysAllocString(unicodestr);

parm[1].vt = VT_I4;
int xlHtml = 44;
parm[1].intVal = xlHtml;

AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, parm[1], parm[0]);

}

ExcelWorkBook::ShowLines(const ExcelLines& excelLines)
{

int i;
IDispatch *pXlSheet;//(NULL);
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
    pXlSheet = result.pdispVal;
}
IDispatch *pShapes;//(NULL);
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Shapes", 0);
    pShapes = result.pdispVal;
}
float screenXWidth;
float screenYHeight;
screenXWidth = 900;
screenYHeight = 400;

float lowestX;
float highestX;
float lowestY;
float highestY;
float XOffest;
float YOffest;
float scalingX;
float scalingY;
lowestX = 999999;
lowestY = 999999;
highestX = 0;
highestY = 0;
for(i=0;i<excelLines.m_linesVector.size();i++)
{
ExcelLine excelLine = excelLines.m_linesVector[i];
if ( excelLine.m_startX < lowestX )
lowestX = excelLine.m_startX;
if ( excelLine.m_endX < lowestX )
lowestX = excelLine.m_endX;

if ( excelLine.m_startY < lowestY )
lowestY = excelLine.m_startY;
if ( excelLine.m_endY < lowestY )
lowestY = excelLine.m_endY;

    if (excelLine.m_startX > highestX )
highestX = excelLine.m_startX;
    if (excelLine.m_endX > highestX )
highestX = excelLine.m_endX;

    if (excelLine.m_startY > highestY )
highestY = excelLine.m_startY;
    if (excelLine.m_endY > highestY )
highestY = excelLine.m_endY;

}
float currXWidth = highestX - lowestX;
scalingX = screenXWidth/currXWidth;
float currYHeight = highestY - lowestY;
scalingY = screenYHeight/currYHeight;
std::cout << "lowestX=" << lowestX << std::endl;
std::cout << "lowestY=" << lowestY << std::endl;
std::cout << "highestX=" << highestX << std::endl;
std::cout << "highestY=" << highestY << std::endl;
std::cout << "scalingX=" << scalingX << std::endl;
std::cout << "scalingY=" << scalingY << std::endl;

for(i=0;i<excelLines.m_linesVector.size();i++)
{
    ExcelLine excelLine = excelLines.m_linesVector[i];
    VARIANT parm[4];
    parm[0].vt = VT_R4; parm[0].fltVal = (excelLine.m_startX-lowestX)*scalingX;
    parm[1].vt = VT_R4; parm[1].fltVal = (excelLine.m_startY-lowestY)*scalingY;
    parm[2].vt = VT_R4; parm[2].fltVal = (excelLine.m_endX-lowestX)*scalingX;
    parm[3].vt = VT_R4; parm[3].fltVal = (excelLine.m_endY-lowestY)*scalingY;
std::cout << "Original : " << excelLine.m_startX << "," << excelLine.m_startY << "," << excelLine.m_endX << "," << excelLine.m_endY << std::endl;
    std::cout << "Transaformed : " << parm[0].fltVal << "," << parm[1].fltVal << "," << parm[2].fltVal << "," << parm[3].fltVal << std::endl << std::endl;

    AutoWrap(DISPATCH_METHOD, NULL, pShapes, L"AddLine", 4, parm[3], parm[2], parm[1], parm[0]);


}

}
void ExcelWorkBook::ShowRange(const ExcelRange& range,IDispatch *pXlSheet)
{
int i;
int j;
IDispatch *pXlRange;
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Cells", 0);
    pXlRange = result.pdispVal;
}

int totalRows(0);
int totalCols(0);

totalCols= range.m_vectorOfVectors.size();
if (totalCols > 0)
    totalRows = range.m_vectorOfVectors[0].size();

if (totalRows > 0)
{
    VARIANT arr;
    arr.vt = VT_ARRAY | VT_VARIANT;
    {
      // std::cout << "totalRows = " << totalRows << " totalCols = " << totalCols << std::endl;
      SAFEARRAYBOUND sab[2];
      sab[0].lLbound = 1; sab[0].cElements = totalRows+2;
      sab[1].lLbound = 1; sab[1].cElements = totalCols+2;
      arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
    }
    for (i=0; i<range.m_vectorOfVectors.size(); i++)
    {
      ExcelRange::StringVectorType stringVector;
      stringVector = range.m_vectorOfVectors[i];
      for (j=0;j < stringVector.size();j++)
      {
        std::string str = stringVector[j];
        // std::cout << "i=" << i << " j = " << j << "   " << str << std::endl;
        VARIANT tmp;
        tmp.vt = VT_BSTR;
        const char *ansistr = str.c_str();
        int a = lstrlenA(ansistr)+1;
        BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
        MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
        tmp.bstrVal = ::SysAllocString(unicodestr);
        long indices[] = {j+1,i+1};
        SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
      }//for end j
    }//for end i
    IDispatch *pXlUpperLeftCell;//(NULL);
    {
      VARIANT parm[2];
      parm[0].vt = VT_I4; parm[0].lVal = range.m_startRow;
      parm[1].vt = VT_I4; parm[1].lVal = range.m_startCol;
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);
      pXlUpperLeftCell = result.pdispVal;
    }

    IDispatch *pXlBottomRightCell;//(NULL);
    {
      VARIANT parm[2];
      parm[0].vt = VT_I4; parm[0].lVal = range.m_startRow+totalRows;
      parm[1].vt = VT_I4; parm[1].lVal = range.m_startCol+totalCols;
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);
      pXlBottomRightCell = result.pdispVal;
    }
    IDispatch *pXlRange3;//(NULL);
    {
      VARIANT parm[2];
      parm[0].vt = VT_DISPATCH; parm[0].pdispVal = pXlUpperLeftCell;
      parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlBottomRightCell;

      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 2, parm[1],parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);

      pXlRange3 = result.pdispVal;
    }
    IDispatch *pXlRange2;//(NULL);
    {
      VARIANT parm;
      parm.vt = VT_BSTR;
      parm.bstrVal = ::SysAllocString(L"C5:D6");

      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
      VariantClear(&parm);

      pXlRange2 = result.pdispVal;
    }
    AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange3, L"Value", 1, arr);
    pXlRange2->Release();
    pXlRange3->Release();
    //pXlUpperLeftCell->Release();
    //pXlBottomRightCell->Release();
    VariantClear(&arr);


}//totalrows > 0

for (i=0; i<range.m_cellsVector.size(); i++)
{
    int row = range.m_cellsVector[i].m_row;
    int col = range.m_cellsVector[i].m_col;
    std::string str = range.m_cellsVector[i].m_str;


    IDispatch *pXlCell;
    {
      VARIANT parm[2];
      parm[0].vt = VT_I4; parm[0].lVal = row;
      parm[1].vt = VT_I4; parm[1].lVal = col;
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Item",2, parm[1], parm[0]);
      VariantClear(&parm[0]);
      VariantClear(&parm[1]);
      pXlCell = result.pdispVal;
    }

    {
      VARIANT parm;
      parm.vt = VT_BSTR;
      const char *ansistr = str.c_str();
      int a = lstrlenA(ansistr)+1;
      BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
      MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
      parm.bstrVal = ::SysAllocString(unicodestr);
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlCell, L"Value", 1, parm);
    }
    pXlCell->Release();
}

pXlRange->Release();
pXlSheet->Release();

}
ExcelWorkBook::ShowRange(const ExcelRange& range,const std::string sheetName)
{

IDispatch *pXlSheet;

if (sheetName == "")
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
    pXlSheet = result.pdispVal;
}
else
{

    VARIANT parm;
    parm.vt = VT_BSTR;
    const char *ansistr = sheetName.c_str();
    int a = lstrlenA(ansistr)+1;
    BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
    MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
    parm.bstrVal = ::SysAllocString(unicodestr);
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 1,parm);
    pXlSheet = result.pdispVal;
}
ShowRange(range,pXlSheet);

}

ExcelWorkBook::ExcelWorkBook(std::string fileName):pXlApp(NULL),pXlBooks(NULL),pXlBook(NULL)
{
if (pXlApp == NULL)
    InitializeApplication();
{
    VARIANT parm;
    parm.vt = VT_BSTR;
const char *ansistr = fileName.c_str();
    int a = lstrlenA(ansistr)+1;
    BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
    MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
    parm.bstrVal = ::SysAllocString(unicodestr);

    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open",1, parm);
    pXlBook = result.pdispVal;
}
{
          VARIANT result;
          VariantInit(&result);
          AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
          pXlSheets = result.pdispVal;
}
}

ExcelWorkBook::InitializeApplication()
{
std::string str("hello");
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);

}
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
    ::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);

}
// 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);
}
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
    pXlBooks = result.pdispVal;
}

}

ExcelWorkBook::ExcelWorkBook():pXlApp(NULL),pXlBooks(NULL),pXlBook(NULL)
{
if (pXlApp == NULL)
    InitializeApplication();

// Call Workbooks.Add() to get a new workbook...

{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
    pXlBook = result.pdispVal;
}
   {
        VARIANT result;
        VariantInit(&result);
        AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
        pXlSheets = result.pdispVal;
}
}

ExcelWorkBook::~ExcelWorkBook()
{
   ::MessageBox(NULL, "Click OK to Quit Excel.", "Notice", 0x10000);

   // 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...

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

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

void ErrHandler(HRESULT hr, EXCEPINFO excep)
{
    if(hr==DISP_E_EXCEPTION)
    {
        char errDesc[512];
        char errMsg[512];
        wcstombs(errDesc, excep.bstrDescription, 512);
        sprintf(errMsg, "Run-time error %d:\n\n %s",
                excep.scode & 0x0000FFFF, //Lower 16-bits of SCODE
                errDesc);                  //Text error description
        ::MessageBox(NULL, errMsg, "Server Error", MB_SETFOREGROUND |
                     MB_OK);
    }
    else
    {
        LPVOID lpMsgBuf;
        ::FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER |
                        FORMAT_MESSAGE_FROM_SYSTEM |
                        FORMAT_MESSAGE_IGNORE_INSERTS, NULL, hr,
                        MAKELANGID(LANG_NEUTRAL,
                        SUBLANG_DEFAULT),(LPTSTR) &lpMsgBuf,
                        0, NULL);
        ::MessageBox(NULL, (LPCTSTR)lpMsgBuf, "COM Error",
                     MB_OK | MB_SETFOREGROUND);
        ::LocalFree( lpMsgBuf );
    }

}

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...
std::cout << "Before GetIDsOfNames " << std::endl;
    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!
EXCEPINFO excep;
    std::cout << "Before pDisp->Invoke " << std::endl;
    hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, &excep, NULL);
    if(FAILED(hr)) {
    ErrHandler(hr, excep);
        sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
        MessageBox(NULL, buf, "AutoWrap()", 0x10010);
        _exit(0);
        return hr;
    }
std::cout << "after pDisp->Invoke " << std::endl;
    // End variable-argument section...
    va_end(marker);

    delete [] pArgs;

    return hr;
}

std::vector<std::string> GetStringVector(std::vector<double> inVector)
{
int i;
std::vector<std::string> outVector;

//for (i = 0; i < 10;i++)//inVector.size(); i++)
for (i = 0; i < inVector.size(); i++)
{
    char s[64];
    sprintf(s,"%*.*f",6,10,inVector[i]);
    std::string tmp(s);
    outVector.push_back(tmp);

}
return outVector;
}

IDispatch* GetRangePointer(int startRow,int startCol,int endRow,int endCol,IDispatch* pSheet)
{
IDispatch *pXlFullRange;
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pSheet, L"Cells", 0);
    pXlFullRange = result.pdispVal;
}
std::cout << "pXlFullRange = " << pXlFullRange << std::endl;
IDispatch *pXlUpperLeftCell;
{
    VARIANT parm[2];
    parm[0].vt = VT_I4; parm[0].lVal = startRow;
    parm[1].vt = VT_I4; parm[1].lVal = startCol;
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlFullRange, L"Item",2, parm[1], parm[0]);
    VariantClear(&parm[0]);
    VariantClear(&parm[1]);
    pXlUpperLeftCell = result.pdispVal;
}
std::cout << "pXlUpperLeftCell = " << pXlUpperLeftCell << std::endl;

IDispatch *pXlBottomRightCell;
{
    VARIANT parm[2];
    parm[0].vt = VT_I4; parm[0].lVal = endRow;
    parm[1].vt = VT_I4; parm[1].lVal = endCol;
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlFullRange, L"Item",2, parm[1], parm[0]);
    VariantClear(&parm[0]);
    VariantClear(&parm[1]);
    pXlBottomRightCell = result.pdispVal;
}
std::cout << "pXlBottomRightCell = " << pXlBottomRightCell << std::endl;
IDispatch *pXlRange;
{
    VARIANT parm[2];
    parm[0].vt = VT_DISPATCH; parm[0].pdispVal = pXlUpperLeftCell;
    parm[1].vt = VT_DISPATCH; parm[1].pdispVal = pXlBottomRightCell;

    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pSheet, L"Range", 2, parm[1],parm[0]);
    VariantClear(&parm[0]);
    VariantClear(&parm[1]);

    pXlRange = result.pdispVal;
}
std::cout << "pXlRange = " << pXlRange << std::endl;
pXlFullRange->Release();
return pXlRange;
}

BSTR GetBString(std::string string)
{
const char *ansistr = string.c_str();
int a = lstrlenA(ansistr)+1;
BSTR unicodestr = SysAllocStringLen(NULL, 2*a);
MultiByteToWideChar(CP_ACP, 0, ansistr, a, unicodestr, a);
return ::SysAllocString(unicodestr);
}

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

Luigi Ballabio-2

Re: Excel viewer for vectors

At 07:33 PM 10/2/03, amar singh wrote:
>On the top of my head , I remember there is an open defect in the
>termstructure::zerocoupon function for 0/0 division, but I don't know if
>it is related. I have used a temporary or dummy fix in my local copy .
>
>I wonder if Nando/Ligui can give a temporary fix for the implemenation to
>apply to our local copy, that will be great.

Hi Amar,
for the time being, something like adding

if (t == 0.0)
t = QL_EPSILON;

as the first thing in the method should probably work. Of course we'll have
to think of something better for the real fix...

Later,
Luigi