Use C++ to show memory use in an Excel Pivot table
(apologies for the formatting: The MSDN blog infrastructure has changed and some tools aren't working yet)
The CComDispatchDriver class is very useful, but it doesn’t work too well with Excel.
It makes it easier to call many Automation interfaces (IDispatch). PropertyGet and PropertyPut, Invoke Method
You can see the typedef of CComDispatchDriver in
typedef CComQIPtr<IDispatch, &__uuidof(IDispatch)> CComDispatchDriver;
in c:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\atlmfc\include\atlcomcli.h
In the same file:
//specialization for IDispatch
template <>
class CComPtr :
public CComPtrBase
You can see various helper methods to call Invoke, like “Invoke1”, “Invoke2”
However, it doesn’t quite help for some Excel interfaces.
For example, consider setting the value of a cell :
Cells[2][3].Value = “hi”
This is a property get with 2 parameters to get an object representing the cell value, then a property put for that value.
In the sample below I created a subclass of CComDispatchDriver which has a few helper methods specifically to Office automation types. It also has a local cache of the mapping of a Dispatch Name to its ID.
I like to use Excel Pivot tables, so I wanted to create one with some data. The code below looks for a running Visual Studio instance and examines its memory, sending the results to an Excel Pivot table
The data shows the kind of memory being used and files with their size.
The code creates 4 pivot tables, one for memory region state (like Reserved or Committed) , region protection attributes (like WriteCopy, Execute), region Type (Image, Private) and filenames
The sample can run as either 32 or 64 bit, demonstrating that IDispatch with Exel works fine with 64 or 32 bit apps.
Using the Excel Macro Recorder helps to figure out what types to call.
<code>
#include <windows.h>
#include <psapi.h>
#include <atlbase.h>
#include <atlcom.h>
#include <atlsafe.h>
#include <atlstr.h>
#include <vector>
#include <memory>
#include <functional>
#include <map>
#include <shlobj.h>
#include <shellapi.h>
#define IfFailRet(expr) { hr = (expr); if(FAILED(hr)) return (hr); }
#define IfNullFail(expr) { if (!expr) return (E_FAIL); }
/*
File->New->project->C++->Windows->Win32->Win32 Project. In the wizard, say Empty Project.
Right click on the Solution->Source Files and add a new file "CppExcel.cpp" and paste in this content
when i don't know how to manipulate excel, i do this:
View->Macro->Record a macro
then edit the macro
or i look at MSDN documentation:
Excel: https://msdn.microsoft.com/en-us/library/office/ff194068.aspx
Word: https://msdn.microsoft.com/en-us/library/office/ff837519.aspx
or open a VS developer command prompt and run oleview on "C:\Program Files (x86)\Microsoft Office\Office16\Excel.exe"
or open oleview, navigate to TypeLibraries->Microsoft Word 16.0 Object Library
*/
using namespace std;
class MyCComDispatchDriver :
public CComDispatchDriver
{
public:
MyCComDispatchDriver() : CComDispatchDriver() {}
MyCComDispatchDriver(LPDISPATCH lpDispatch) : CComDispatchDriver(lpDispatch) { }
map<CString, DISPID> _mapDispId;
HRESULT __cdecl CallMember(CString memberName, int dispFlags, CComVariant *pResult, UINT nParams, ...)
{
HRESULT hr = S_OK;
DISPID dispId;
auto findRes = _mapDispId.find(memberName);
if (findRes == _mapDispId.end())
{
hr = GetIDOfName(memberName, &dispId);
if (FAILED(hr))
{
MessageBox(0, memberName, L"Member not found", 0);
return hr;
}
_mapDispId[memberName] = dispId;
}
else
{
dispId = findRes->second;
}
va_list args;
va_start(args, nParams);
vector<CComVariant> argvec;
for (UINT i = 0; i < nParams; i++)
{
auto var = va_arg(args, CComVariant);
argvec.push_back(var);
}
reverse(argvec.begin(), argvec.end());
DISPPARAMS dispparams = { argvec.data(), NULL, nParams, 0 };
if (dispFlags == DISPATCH_PROPERTYPUT)
{
static DISPID dispidNamed = DISPID_PROPERTYPUT;
dispparams.cNamedArgs = 1;
dispparams.rgdispidNamedArgs = &dispidNamed;
}
EXCEPINFO excepinfo;
UINT argError = 0;
hr = p->Invoke(dispId,
IID_NULL,
LOCALE_USER_DEFAULT,
dispFlags,
&dispparams,
pResult,
&excepinfo,
&argError
);
if (FAILED(hr))
{
MessageBox(0, excepinfo.bstrSource, excepinfo.bstrDescription, 0);
}
return hr;
}
};
class ExcelSheet
{
MyCComDispatchDriver _oExcel;
MyCComDispatchDriver _oActiveSheet;
MyCComDispatchDriver _oActiveWorkBook;
MyCComDispatchDriver _oCells;
public:
ExcelSheet()
{
initExcel();
}
HRESULT initExcel()
{
HRESULT hr = S_OK;
// create an instance of Excel.
IfFailRet(_oExcel.CoCreateInstance(
L"Excel.Application",
nullptr,
CLSCTX_LOCAL_SERVER//its a separate process, rather than in in-process DLL
));
CComVariant cvtWrkBooks;
IfFailRet(_oExcel.CallMember(
L"Workbooks",
DISPATCH_PROPERTYGET,
&cvtWrkBooks,
0));
CComVariant cvtActiveWrkBook;
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtWrkBooks))).CallMember(
L"Add",
DISPATCH_METHOD,
&cvtActiveWrkBook,
0
));
_oActiveWorkBook = V_DISPATCH(&cvtActiveWrkBook);
CComVariant cvtActiveSheet;
IfFailRet(_oExcel.CallMember(
L"ActiveSheet",
DISPATCH_PROPERTYGET,
&cvtActiveSheet,
0));
_oActiveSheet = V_DISPATCH(&cvtActiveSheet);
// now get the "Cells" property
CComVariant cvtCells;
IfFailRet(_oExcel.CallMember(
L"Cells",
DISPATCH_PROPERTYGET,
&cvtCells,
0));
_oCells = V_DISPATCH(&cvtCells);
return hr;
}
HRESULT SetCellValue(int row, int col, CComVariant cvtValue)
{
HRESULT hr = S_OK;
// get a ref to the cell
CComVariant cvtResCell;
IfFailRet(_oCells.CallMember(
L"Item",
DISPATCH_PROPERTYGET,
&cvtResCell,
2,
CComVariant(row + 1),
CComVariant(col + 1)));
// set the value of the cell ref
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtResCell))).CallMember(
L"Value",
DISPATCH_PROPERTYPUT,
nullptr,
1,
cvtValue));
return hr;
}
HRESULT doExcel()
{
HRESULT hr = S_OK;
DWORD procIds[1024];
DWORD cbNeeded;
// we need some data to put in Excel
// so we'll find the Visual Studio Process "devenv.exe"
// Get a list of running processes
EnumProcesses(procIds, sizeof(procIds), &cbNeeded);
int nProcs = cbNeeded / sizeof(DWORD);
int pidTarget = 0;
for (int i = 0; i < nProcs; i++)
{
auto pid = procIds[i];
// declare "hProcess" to be a handle from OpenProcess() with a dtor that calls CloseHandle()
unique_ptr<void, BOOL(WINAPI *)(HANDLE)> hProcess(
OpenProcess(PROCESS_QUERY_INFORMATION | PROCESS_VM_READ, 0, pid),
&::CloseHandle);
HMODULE hModule;
if (hProcess.get() != nullptr)
{
// now look at the modules for the process
DWORD dw;
if (EnumProcessModules(hProcess.get(), &hModule, sizeof(hModule), &dw))
{
CString buffer;
GetModuleBaseName(hProcess.get(), hModule, buffer.GetBuffer(MAX_PATH), MAX_PATH);
buffer.ReleaseBuffer(); //set buffer size
if (_wcsicmp(buffer, L"devenv.exe") == 0)
{
pidTarget = procIds[i];
break;
}
}
}
}
//int pid = GetCurrentProcessId(); // use current process
int pid = pidTarget;
unique_ptr<remove_pointer<HANDLE>::type, BOOL(WINAPI *)(HANDLE)> hProcess(
OpenProcess(PROCESS_QUERY_INFORMATION | PROCESS_VM_READ, 0, pid),
[](HANDLE h) {return ::CloseHandle(h); });
// set excel column headings
static wchar_t *colnames[] = { L"BaseAddr",L"AllocBase",L"Size",L"Size10",L"State",L"Protect",L"Type",L"FileName" };
int nCols = 8;
for (int col = 0; col < nCols; col++)
{
SetCellValue(0, col, CComVariant(colnames[col]));
}
// now get memory information about the process
MEMORY_BASIC_INFORMATION mbi;
LPVOID address = 0;
int row = 1;
#if defined(_AMD64_)
WCHAR *fmtHex = L"'%016llx";
#else // '_X86_'
WCHAR *fmtHex = L"'%08x";
#endif
PVOID prioraddr = 0;
while (true)
{
SIZE_T result = VirtualQueryEx(hProcess.get(), address, &mbi, sizeof(mbi));
if (result == 0)
{
int errr = GetLastError();
break;
}
prioraddr = mbi.BaseAddress;
for (int col = 0; col < nCols; col++)
{
CString str;
switch (col)
{
case 0:
str.AppendFormat(fmtHex, mbi.BaseAddress);
break;
case 1:
str.AppendFormat(fmtHex, mbi.AllocationBase);
break;
case 2:
str.AppendFormat(fmtHex, mbi.RegionSize);
break;
case 3:
#if defined(_AMD64_)
str.AppendFormat(L"%llu", mbi.RegionSize);
#else // '_X86_'
str.AppendFormat(L"%u", mbi.RegionSize);
#endif
break;
case 4:
str.Append(MemStateString(mbi.State));
break;
case 5:
str.Append(AllocationProtectString(mbi.Protect));
break;
case 6:
str.Append(MemTypeString(mbi.Type));
break;
case 7:
if (address != 0 && (mbi.State & MEM_FREE) == 0)
{
str.GetBuffer(MAX_PATH);
GetModuleFileNameEx(hProcess.get(), (HMODULE)mbi.AllocationBase, str.GetBuffer(), str.GetAllocLength());
str.ReleaseBuffer();
str.MakeLower();
}
break;
default:
break;
}
SetCellValue(row, col, CComVariant(str));
}
address = (LPBYTE)address + mbi.RegionSize;
row++;
}
/*
Columns("F:F").ColumnWidth = 12.07
Columns("D:D").Select
Range("D63").Activate
Selection.NumberFormat = "#,##0"
*/
for (int i = 0; i < nCols; i++)
{
CComVariant cvtColumnRange;
IfFailRet(_oActiveSheet.CallMember(
L"Columns",
DISPATCH_PROPERTYGET,
&cvtColumnRange,
1,
CComVariant(i + 1)));
MyCComDispatchDriver oColumn(V_DISPATCH(&cvtColumnRange));
if (i == 3)
{
IfFailRet(oColumn.CallMember(
L"NumberFormat",
DISPATCH_PROPERTYPUT,
nullptr,
1,
CComVariant(L"#,##0")));
}
IfFailRet(oColumn.CallMember(
L"AutoFit",
DISPATCH_METHOD,
nullptr,
0));
}
//ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$310"), , xlYes).Name = "MemoryTable"
CComVariant cvtResCell;
IfFailRet(_oCells.CallMember(
L"Item",
DISPATCH_PROPERTYGET,
&cvtResCell,
2,
CComVariant(1),
CComVariant(1)));
MyCComDispatchDriver oCell(V_DISPATCH(&cvtResCell));
CComVariant cvtCurRegion;
IfFailRet(oCell.CallMember(
L"CurrentRegion",
DISPATCH_PROPERTYGET,
&cvtCurRegion,
0
));
CComVariant cvtListObjects;
IfFailRet(_oActiveSheet.CallMember(
L"ListObjects",
DISPATCH_PROPERTYGET,
&cvtListObjects,
0
));
CComVariant cvtTable;
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtListObjects))).CallMember(
L"Add",
DISPATCH_METHOD,
&cvtTable,
4,
CComVariant(1), //xlSrcRange == 1
CComVariant(V_DISPATCH(&cvtCurRegion)),
CComVariant(), // LinkSource
CComVariant(1) //xlYes == 1 //has headers
));
MyCComDispatchDriver oTable(V_DISPATCH(&cvtTable));
IfFailRet(oTable.CallMember(
L"Name",
DISPATCH_PROPERTYPUT,
nullptr,
1,
CComVariant(L"MemoryTable")
));
CComVariant cvtSheets;
IfFailRet(_oExcel.CallMember(
L"Sheets",
DISPATCH_PROPERTYGET,
&cvtSheets,
0
));
CComVariant cvtSheet2;
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtSheets)).CallMember(
L"Add",
DISPATCH_METHOD,
&cvtSheet2,
0
)));
CComVariant cvtPivotCaches;
IfFailRet(_oActiveWorkBook.CallMember(
L"PivotCaches",
DISPATCH_METHOD,
&cvtPivotCaches,
0
));
// lets create a pivot table for cols 4-7
for (int pivnum = 4; pivnum < 8; pivnum++)
{
CComVariant cvtCellPivotDestination;
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtSheet2)).CallMember(
L"Cells",
DISPATCH_PROPERTYGET,
&cvtCellPivotDestination,
2,
CComVariant(2),
CComVariant(1 + 3 * (pivnum - 4))
)));
CComVariant cvtPivotCache;
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtPivotCaches))).CallMember(
L"Create",
DISPATCH_METHOD,
&cvtPivotCache,
2,
CComVariant(1), // xlDatabase == 1
CComVariant(oTable.p)
));
CString pivTableName;
pivTableName.AppendFormat(L"Pivot%s", colnames[pivnum]);
CComVariant cvtPivotTable;
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtPivotCache))).CallMember(
L"CreatePivotTable",
DISPATCH_METHOD,
&cvtPivotTable,
2,
CComVariant(V_DISPATCH(&cvtCellPivotDestination)), // TableDestination
CComVariant(pivTableName)
));
MyCComDispatchDriver oPivotTable(V_DISPATCH(&cvtPivotTable));
// add a pivot field from the memory "State" column
CComVariant cvtPivFieldState;
IfFailRet(oPivotTable.CallMember(
L"PivotFields",
DISPATCH_METHOD,
&cvtPivFieldState,
1,
CComVariant(colnames[pivnum])
));
MyCComDispatchDriver oPivotFieldState(V_DISPATCH(&cvtPivFieldState));
IfFailRet(oPivotFieldState.CallMember(
L"Orientation",
DISPATCH_PROPERTYPUT,
nullptr,
1,
CComVariant(1) // xlRowField == 1
));
IfFailRet(oPivotFieldState.CallMember(
L"Position",
DISPATCH_PROPERTYPUT,
nullptr,
1,
CComVariant(1)
));
// add a pivot field for the Size10 column
CComVariant cvtPivFieldSize10;
IfFailRet(oPivotTable.CallMember(
L"PivotFields",
DISPATCH_METHOD,
&cvtPivFieldSize10,
1,
CComVariant(L"Size10")
));
MyCComDispatchDriver oPivotFieldSize10(V_DISPATCH(&cvtPivFieldSize10));
CComVariant cvtDataField;
IfFailRet(oPivotTable.CallMember(
L"AddDataField",
DISPATCH_METHOD,
&cvtDataField,
2,
CComVariant(V_DISPATCH(&cvtPivFieldSize10)),
CComVariant(L"Sum of Size10")
));
// set the numeric format for the region of the pivot table
CComVariant cvtPivotTableRegion;
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtCellPivotDestination))).CallMember(
L"CurrentRegion",
DISPATCH_PROPERTYGET,
&cvtPivotTableRegion,
0
));
IfFailRet((MyCComDispatchDriver(V_DISPATCH(&cvtPivotTableRegion))).CallMember(
L"NumberFormat",
DISPATCH_PROPERTYPUT,
nullptr,
1,
CComVariant(L"#,##0")));
IfFailRet(oPivotFieldState.CallMember(
L"AutoSort",
DISPATCH_METHOD,
nullptr,
2,
CComVariant(2), // xlDescending == 2
CComVariant(L"Sum of Size10")
));
}
/*
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R306C8", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Size10"), "Sum of Size10", xlSum
Application.Goto Reference:="Macro1"
Columns("C:C").Select
Selection.NumberFormat = "#,##0"
ActiveSheet.PivotTables("PivotTableName").PivotFields("State").AutoSort _
xlDescending, "Sum of Size10"
Application.Goto Reference:="Macro1"
*/
return hr;
}
CString AllocationProtectString(DWORD protectFlags)
{
CString result;
if (protectFlags & PAGE_NOACCESS)
{
result.AppendFormat(L"NOACCESS");
}
if (protectFlags & PAGE_READONLY)
{
result.AppendFormat(L"READONLY");
}
if (protectFlags & PAGE_READWRITE)
{
result.AppendFormat(L"READWRITE");
}
if (protectFlags & PAGE_WRITECOPY)
{
result.AppendFormat(L"WRITECOPY");
}
if (protectFlags & PAGE_EXECUTE)
{
result.AppendFormat(L"EXECUTE");
}
if (protectFlags & PAGE_EXECUTE_READ)
{
result.AppendFormat(L"EXECUTE_READ");
}
if (protectFlags & PAGE_EXECUTE_READWRITE)
{
result.AppendFormat(L"EXECUTE_READWRITE");
}
if (protectFlags & PAGE_EXECUTE_WRITECOPY)
{
result.AppendFormat(L"EXECUTE_WRITECOPY");
}
if (protectFlags & PAGE_GUARD)
{
result.AppendFormat(L"GUARD");
}
if (protectFlags & PAGE_NOCACHE)
{
result.AppendFormat(L"NOCACHE");
}
if (protectFlags & PAGE_WRITECOMBINE)
{
result.AppendFormat(L"WRITECOMBINE");
}
return result;
}
CString MemTypeString(DWORD dwType)
{
CString result;
if (dwType & MEM_IMAGE)
{
result.Append(L"IMAGE");
}
if (dwType & MEM_MAPPED)
{
result.Append(L"MAPPED");
}
if (dwType & MEM_PRIVATE)
{
result.Append(L"PRIVATE");
}
return result;
}
CString MemStateString(DWORD dwState)
{
CString result;
if (dwState & MEM_COMMIT)
{
result.Append(L"COMMIT");
}
if (dwState & MEM_RESERVE)
{
result.Append(L"RESERVE");
}
if (dwState & MEM_FREE)
{
result.Append(L"FREE");
}
return result;
}
void MakeVisible()
{
// make excel visible
_oExcel.PutPropertyByName(L"Visible", &CComVariant(true));
}
HRESULT Save()
{
HRESULT hr = S_OK;
// lets invoke the SaveAs method: https://msdn.microsoft.com/en-us/library/office/ff841185.aspx
// first we calculate the file name
PWSTR pFolder;
SHGetKnownFolderPath(
FOLDERID_Documents,
NULL, //flags
NULL, //token
&pFolder
); // C:\Users\calvinh\Desktop
CString pathOutput(pFolder);
CoTaskMemFree(pFolder);
pathOutput.Append(L"\\temp.xlsx");
DeleteFile(pathOutput); // delete if it exists
IfFailRet(_oActiveSheet.CallMember(
L"Saveas",
DISPATCH_METHOD,
nullptr,
1,
CComVariant(pathOutput)));
return hr;
}
};
int __stdcall WinMain(
_In_ HINSTANCE hInstance,
_In_opt_ HINSTANCE hPrevInstance,
_In_ LPSTR lpCmdLine,
_In_ int nShowCmd
)
{
HRESULT hr = S_OK;
// initialize COM
CoInitializeEx(0, COINIT_APARTMENTTHREADED);
char *txt = "test";
CAtlString foo;
CA2W pW(txt);
foo = pW;
ExcelSheet excelSheet;
IfFailRet(excelSheet.doExcel());
excelSheet.MakeVisible();
excelSheet.Save();
//Range("E13").Select
//ActiveWorkbook.SaveAs Filename : = "C:\Users\CalvinH\Documents\Book1.xlsx", _
//FileFormat : = xlOpenXMLWorkbook, CreateBackup : = False
return hr;
}
</code>