Sample C++ Code: Excel reporting - richly formatted data and formulas

#include "stdafx.h"

 

#import <Pagos.Spreadsheet.COM.dll>

 

using namespace PagosSpreadSheet;

using namespace std;

 

//    Creates PSC Application interface

void CreatePSCInterface(IApplication **app)

{

      //    Initialize COM subsystem

      CoInitialize(NULL);

 

      //    Accessing com object

      HRESULT hr = CoCreateInstance(__uuidof(Application), NULL, CLSCTX_ALL, __uuidof(IApplication), static_cast<void**>(static_cast<void*>(app)));

 

      //    Checking for error

      if(!SUCCEEDED(hr))

      {

            LPVOID lpMsgBuf;

            FormatMessage(

                  FORMAT_MESSAGE_ALLOCATE_BUFFER |

                  FORMAT_MESSAGE_FROM_SYSTEM |

                  FORMAT_MESSAGE_IGNORE_INSERTS,

                  NULL,

                  hr,

                  0, // Default language

                  (LPTSTR) &lpMsgBuf,

                  0,

                  NULL

                  );         

            wcout << ((wchar_t *)lpMsgBuf);

            LocalFree( lpMsgBuf );

            cout << "Error while creating com object." << endl;

            system("PAUSE");

            exit(-1);

      }

}

 

int main(int argc, char* argv[])

{

     

      try

      {

            long start = GetTickCount();

 

            IApplication *app = NULL;

 

            //Create an instance of the application

            CreatePSCInterface(&app);

 

            //Add a workbook

            IWorkbook *wb = app->Workbooks->Add();

 

            //Load a worksheet from added workbook

            IWorksheet *ws = wb->Worksheets->Item["Sheet1"];

 

            //Prepare worksheet data

            const char *headers[] = { "Employee", "Volume Q1", "Sales Q1", "Volume Q2", "Sales Q2", "Volume Q3", "Sales Q3", "Volume Q4", "Sales Q4" };

 

            struct RowData{

                  const char * name;

                  float data[8];

            }

            data[] =    {

                                   { "Helen", 5, 7906.25F, 40, 63250, 69, 109106.25F, 31, 49018.75F },

                                   { "Fred", 32, 45640, 80, 114100, 45, 64181.25, 87, 124083.75F },

                                   { "John", 60, 107355, 65, 116301.25F, 90, 161032.5F, 50, 89462.5F },

                                   { "Jane", 40, 54770, 1, 1369.25F, 22, 30123.5F, 65, 89001.25F },

                                   { "Total", 137, 215671.25F, 186, 295020.5F, 226, 364443.5F, 233, 351566.25F }

                             };

 

            const char *bottom[] = { "Total", "=SUM(B2:B5)", "=SUM(C2:C5)", "=SUM(D2:D5)", "=SUM(E2:E5)", "=SUM(F2:F5)", "=SUM(G2:G5)", "=SUM(H2:H5)", "=SUM(I2:I5)" };

 

            int row;

            int column;

 

            //Bind values to cells

            for (row = 0; row < 7; row++)

            {

                  for (column = 0; column < 9; column++)

                  {

                        if(row == 0)

                             ws->GetCells(row+1, column+1)->Value = headers[column];

                        else if(row == 6)

                        {

                             if(column == 0)

                                   ws->GetCells(row+1, column+1)->Value = bottom[column];

                             else

                                   ws->GetCells(row+1, column+1)->Formula = bottom[column];

                        }

                        else

                        {

                             if(column == 0)

                                   ws->GetCells(row+1, column+1)->Value = data[row-1].name;

                             else

                                   ws->GetCells(row+1, column+1)->Value = data[row-1].data[column-1];

                        }

                  }

            }

 

            //Apply styles

            IRange *a1i6 = ws->Range["A1:I6"];

            a1i6->Font->Name = "Arial";

            a1i6->Font->Size = 8;       

            a1i6->HorizontalAlignment = HAlignCenter;

 

            IRange *a1i1 = ws->Range["A1:I1"];

            a1i1->Font->Bold = true;

            a1i1->Interior->Color = RGB(51, 204, 204);

 

            IRange *a6i6 = ws->Range["A6:I6"];

            a6i6->Font->Bold = true;

            a6i6->Interior->Color =  RGB(51, 204, 204);

 

            const char *numberFormat = "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)";

            IRange *c2c6 = ws->Range["C2:C6"];

            IRange *e2e6 = ws->Range["E2:E6"];

            IRange *g2g6 = ws->Range["G2:G6"];

            IRange *i2i6 = ws->Range["I2:I6"];

            c2c6->NumberFormat = numberFormat;

            e2e6->NumberFormat = numberFormat;

            g2g6->NumberFormat = numberFormat;

            i2i6->NumberFormat = numberFormat;

 

            //Save workbook

            wb->SaveAs("..\\Spreadsheets\\Temp\\EmployeeDataFormatFormula.xml");

 

            //Close workbook

            wb->Close();           

      }

      catch(_com_error &e)

      {

            cout << e.Description() << endl;

      }

 

      CoUninitialize();

      system("PAUSE");

      return 0;

}