Partager via


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.

image

 

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