How do you read figures from a text file and write them in tabular form in an excel spreadsheet in c plus plus?

Answer 1

The following code is based upon Microsoft KB216686: How to automate Excel from C++ without using MFC or #import.

The code creates some random data as a table, writing it to a text file in CSV format. This data is then read from the file, printed to std::cout (for verification) before being output to an excel worksheet.

#include<iostream>

#include<fstream>

#include<sstream>

#include<array>

#include<random>

#include<ctime>

#include<ole2.h>

template<const size_t R, const size_t C>

class table_t

{

std::array<std::array<double, C>, R> data = {};

public:

table_t () {}

table_t (const table_t& rhs): data {rhs.data} {}

table_t (table_t&& rhs): data (std::move (rhs.data)) {}

table_t& operator= (const table_t& rhs) { data = rhs.data; }

table_t& operator= (table_t&& rhs) { data = std::move (rhs.data); }

std::array<double,C>& operator [] (const size_t row) { return data[row]; }

const std::array<double,C>& operator [] (const size_t row) const { return data[row]; }

size_t rows () const { return R; }

size_t columns () const { return C; }

std::wstring get_excel_range() const

{

std::wstringstream ss;

ss << L"A1:";

size_t cols=C;

while (cols)

{

ss << static_cast<char>((cols-1)%26+L'A');

cols /= 26;

}

ss << R;

return ss.str();

}

};

template<const size_t R, const size_t C>

std::ostream& operator<< (std::ostream& os, const table_t<R,C>& table)

{

for (size_t row=0; row<R; ++row)

{

for (size_t col=0; col<C; ++col)

{

os << table[row][col];

if (col<C-1)

os << ", ";

}

os << std::endl;

}

return os;

}

// forward declarations...

void create_data (const std::string&, const size_t, const size_t);

template<const size_t R, const size_t C>

table_t<R,C> read_data (const std::string&);

template<const size_t R, const size_t C>

int write_to_excel (const table_t<R,C>&);

HRESULT AutoWrap (int, VARIANT*, IDispatch*, LPOLESTR, int ...);

int main()

{

const size_t rows=10;

const size_t cols=5;

const std::string textfile {"sample.txt"};

// create sample data...

create_data (textfile, rows, cols);

// read and print the sample data...

table_t<rows, cols> table = read_data<rows, cols> (textfile);

std::cout << "Sample data:\n\n";

std::cout << table << std::endl;

// output data to excel...

return write_to_excel (table);

}

// Function to create a sample CSV data file with random data.

void create_data (const std::string& filename, const size_t rows, const size_t cols)

{

std::default_random_engine generator ((unsigned) time (0));

std::uniform_int_distribution<unsigned> distribution (0, 10000);

std::ofstream os (filename);

if (!os.bad())

{

for (size_t row=0; row<rows; ++row)

{

for (size_t col=0; col<cols; ++col)

{

double d = distribution (generator);

d /= 100;

os << d;

if (col!=cols-1)

os << ", ";

}

os << '\n';

}

}

os.close();

}

// Function to read a CSV file and return a table of doubles

template<const size_t R, const size_t C>

table_t<R,C> read_data (const std::string& filename)

{

table_t<R,C> table;

size_t row=0;

std::ifstream is (filename);

std::string line;

while (std::getline (is, line))

{

size_t col=0;

std::stringstream ss1;

ss1 << line;

std::string value;

while (std::getline (ss1, value, ','))

{

std::stringstream ss2;

ss2 << value;

double d;

ss2 >> d;

table[row][col] = d;

++col;

}

++row;

}

is.close();

return table;

}

template<const size_t R, const size_t C>

int write_to_excel (const table_t<R,C>& table)

{

// Initialize COM for this thread...

CoInitialize (nullptr);

// Get CLSID for our server...

CLSID clsid;

HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

if (FAILED(hr))

{

::MessageBox (nullptr, "CLSIDFromProgID() failed", "Error", 0x10010);

return -1;

}

// Start server and get IDispatch...

IDispatch *pXlApp;

hr = CoCreateInstance (clsid, nullptr, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);

if(FAILED(hr))

{

::MessageBox (nullptr, "Excel not registered properly", "Error", 0x10010);

return -2;

}

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

}

// Get Workbooks collection

IDispatch *pXlBooks;

{

VARIANT result;

VariantInit(&result);

AutoWrap (DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);

pXlBooks = result.pdispVal;

}

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

IDispatch *pXlBook;

{

VARIANT result;

VariantInit(&result);

AutoWrap (DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);

pXlBook = result.pdispVal;

}

// Create a safearray of variants...

VARIANT arr;

arr.vt = VT_ARRAY | VT_VARIANT;

{

SAFEARRAYBOUND sab[2];

sab[0].lLbound = 1; sab[0].cElements = table.rows();

sab[1].lLbound = 1; sab[1].cElements = table.columns();

arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);

}

// Fill safearray with values from the table...

for (size_t row=0; row<table.rows(); ++row)

{

for (size_t col=0; col<table.columns(); ++col)

{

// Create value

VARIANT tmp;

tmp.vt = VT_R8;

tmp.dblVal = table[row][col];

// Add to safearray...

long indices[] = {row+1,col+1};

SafeArrayPutElement (arr.parray, indices, (void *)&tmp);

}

}

// Get ActiveSheet object

IDispatch *pXlSheet;

{

VARIANT result;

VariantInit(&result);

AutoWrap (DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);

pXlSheet = result.pdispVal;

}

// Get Range object...

IDispatch *pXlRange;

{

VARIANT parm;

parm.vt = VT_BSTR;

parm.bstrVal = ::SysAllocString (table.get_excel_range().c_str());

VARIANT result;

VariantInit (&result);

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

VariantClear (&parm);

pXlRange = result.pdispVal;

}

// Set range with our safearray...

AutoWrap (DISPATCH_PROPERTYPUT, nullptr, pXlRange, L"Value", 1, arr);

// Wait for user...

::MessageBox (nullptr, "All done.", "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, nullptr, pXlBook, L"Saved", 1, x);

}

// Tell Excel to quit (i.e. App.Quit)

AutoWrap (DISPATCH_METHOD, nullptr, pXlApp, L"Quit", 0);

// Release references...

pXlRange->Release();

pXlSheet->Release();

pXlBook->Release();

pXlBooks->Release();

pXlApp->Release();

VariantClear(&arr);

// Uninitialize COM for this thread...

CoUninitialize();

return 0;

}

// AutoWrap() - Automation helper function...

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 (nullptr, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);

_exit (0);

}

// Variables used...

DISPPARAMS dp = {nullptr, nullptr, 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, nullptr, nullptr);

// Get DISPID for name passed...

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 (nullptr, 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!

hr = pDisp->Invoke (dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, nullptr, nullptr);

if (FAILED (hr))

{

sprintf (buf, "IDispatch::Invoke("%s"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);

MessageBox (nullptr, buf, "AutoWrap()", 0x10010);

_exit (0);

return hr;

}

// End variable-argument section...

va_end(marker);

delete [] pArgs;

return hr;

}