Calling Excel from C++ in 2019

C++ is fast and efficient, but its use with MS Office COM-based APIs has lost traction over the years. On the other hand, C++ is great for the heavy lifting of data science applications (I like how easy it is to use the Cuda API) and Excel is one of the best tools for data exploration. So why does Visual Studio 2019 not come with a project template supporting this union out of the box?

As a co-worker and I discovered, it might not be as straight forward as in Visual Studio 2015, but it's still do-able. Let's try an example with a console application.

The first thing you want to do is make sure your application uses the Common Language Runtime (CLR). This can be found by right clicking on the project and selecting Properties.

From there, it's time to add references. Start with Excel's COM-based library.

After all the references have been added, the reference list should appear as follows:

With that complete, now it's time to code. The following sample is intended to get started with the Excel API; it creates a file and renames the worksheet before saving.

// ExcelCppTest.cpp

int main()
{
    Microsoft::Office::Interop::Excel::Application^ app
        = gcnew Microsoft::Office::Interop::Excel::ApplicationClass();
    app->Visible = false;

    Microsoft::Office::Interop::Excel::Workbook^ workbook
        = app->Workbooks->Add(System::Type::Missing);

    Microsoft::Office::Interop::Excel::Worksheet^ worksheet
        = safe_cast<Microsoft::Office::Interop::Excel::Worksheet^>(app->ActiveSheet);

    worksheet->Name = "Test";

    workbook->SaveAs(System::Environment::CurrentDirectory + "\\Test.xlsx",
        System::Type::Missing, System::Type::Missing, System::Type::Missing,
        false, false,
        Microsoft::Office::Interop::Excel::XlSaveAsAccessMode::xlShared,
        false, false,
        System::Type::Missing, System::Type::Missing, System::Type::Missing
    );

    workbook->Close(false, System::Type::Missing, System::Type::Missing);

    System::Runtime::InteropServices::Marshal::ReleaseComObject(workbook);

    app->Quit();

    System::Runtime::InteropServices::Marshal::ReleaseComObject(app);

    System::Console::WriteLine("Test Complete.  Press any key to exit");
    System::Console::ReadKey();
    return 0;
}

The above code successfully generates an Excel file "Test.xlsx" in the project folder. Now you're ready to report on some real analysis!