Sample C++ Code: Excel reporting - batch processing sample

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

 

            //Open quote template workbook

            app->GetWorkbooks()->Open("..\\Spreadsheets\\AutoInsurance\\AutoInsuranceQuote.xml");

            //Open workbook that contains customer list for quotes

            app->GetWorkbooks()->Open("..\\Spreadsheets\\AutoInsurance\\AutoInsuranceQuoteData.xls");

 

            //Load workbooks

            IWorkbook *wbTemplate = app->Workbooks->Item["AutoInsuranceQuote.xml"];

            IWorkbook *wbData = app->Workbooks->Item["AutoInsuranceQuoteData.xls"];

           

            //Load worksheets

            IWorksheet *wsTemplate = wbTemplate->Worksheets->Item["Sheet1"];

            IWorksheet *wsData = wbData->Worksheets->Item["Data"];

 

            //Get count of customers

            int rowCount = wsData->Cells->Height;

            int rowIndex;

           

            VARIANT value;

            value.bstrVal = L"Example";

           

            //Prepare and save a quote workbook for each customer

            for (rowIndex = 1; rowIndex < rowCount; rowIndex++)

            {

                  //Set quote values from customer information

                  wbTemplate->GetNames()->Item["Name"]->Range->Value = wsData->GetCells(rowIndex, 1)->Value;

                  wbTemplate->GetNames()->Item["Adress"]->Range->Value = wsData->GetCells(rowIndex, 2)->Value;

                  wbTemplate->GetNames()->Item["CityStateZIP"]->Range->Value = wsData->GetCells(rowIndex, 3)->Value;

                  wbTemplate->GetNames()->Item["yearlypremium"]->Range->Value = wsData->GetCells(rowIndex, 4)->Value;

                  wbTemplate->GetNames()->Item["monthlypremium"]->Range->Value = wsData->GetCells(rowIndex, 5)->Value;

                  wsTemplate->Range["B21"]->Value = wsData->GetCells(rowIndex, 6)->Value;

                  wsTemplate->Range["B22"]->Value = wsData->GetCells(rowIndex, 7)->Value;

                  wsTemplate->Range["B23"]->Value = wsData->GetCells(rowIndex, 8)->Value;

                  wsTemplate->Range["B24"]->Value = wsData->GetCells(rowIndex, 9)->Value;

                  wsTemplate->Range["B25"]->Value = wsData->GetCells(rowIndex, 10)->Value;

                  wsTemplate->Range["B26"]->Value = wsData->GetCells(rowIndex, 11)->Value;

                  value = wsData->GetCells(rowIndex, 12)->Value;

                  wsTemplate->Range["D21"]->Value = value;

                  value = wsData->GetCells(rowIndex, 13)->Value;

                  wsTemplate->Range["D22"]->Value = value;

                  value = wsData->GetCells(rowIndex, 14)->Value;

                  wsTemplate->Range["D23"]->Value = value;

                  wsTemplate->Range["B31"]->Value = wsData->GetCells(rowIndex, 15)->Value;

                  wsTemplate->Range["B32"]->Value = wsData->GetCells(rowIndex, 16)->Value;

                  wsTemplate->Range["B33"]->Value = wsData->GetCells(rowIndex, 17)->Value;

                  wsTemplate->Range["B34"]->Value = wsData->GetCells(rowIndex, 18)->Value;

                  wsTemplate->Range["E31"]->Value = wsData->GetCells(rowIndex, 19)->Value;

                  wsTemplate->Range["E32"]->Value = wsData->GetCells(rowIndex, 20)->Value;

                  wsTemplate->Range["E33"]->Value = wsData->GetCells(rowIndex, 21)->Value;

 

                  //Save quote workbook

                  stringstream ss;

                  ss << "..\\Spreadsheets\\AutoInsurance\\Quotes\\Quote";

                  ss << rowIndex;

                  ss << ".xml";

                  wbTemplate->SaveAs(ss.str().c_str());

            }

 

            //Close workbooks

            wbData->Close();

            wbTemplate->Close();

 

            //Calculate and display runtime

            long end = GetTickCount();

            long runTime = end - start;

            cout << "Runtime : " << runTime / 1000.0f << " seconds" << endl;

      }

      catch(_com_error &e)

      {

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

      }

 

      CoUninitialize();

      system("PAUSE");

      return 0;

}