OLE program development and utilization (developed Excel)

OLE program development (developed Excel) 1. First open the class wizard (MFC ClassWizard) Select from A Type Library in the Add Class button ... Find the file Excel9.olb in the Office directory and open. Second, select the class (the object in Excel) in the class box in the CONFIRM CLASS (the object in Excel) and then add _Application, Workbooks, _Workbook, Worksheets, _Worksheet, and Range classes after pressing the OK button. Third, add the header file #include #include "Excel9.h" four, the code is as follows:

void CParameterApp :: CreateRepTemp () {_Application ExcelApp; Workbooks wbsMyBooks; _Workbook wbMyBook; Worksheets wssMysheets; _Worksheet wsMysheet; Range rgMyRge; CString strSqlStmt; CString strRepTemplate = "c: // // Report Report .xlt";

/// cfilefind filefind; int ifileexist = filefind.findfile ((lpctstr) strrepTemplate);

/// if (coinitialize (null)! = 0) {AFXMessageBox ("Initialization COM Support Library Failed!"); Exit (1);}

ColeException * E = new ColeException; try {if (! "ExceLapp.createdispatch (" Excel.Application.9 ", E)).

Catch (ColedispatChexception * e) {CString CSTR;

If (! e-> m_strsource.isempty ()) cstr = e-> m_strsource "-"; if (! e-> m_strdescription.isempty ()) cstr = e-> m_strdescription; else cstr = "Unknown Error "

AfxMessageBox (cstr, mb_ok, (e-> m_strhelpfile.isempty ())? 0: E-> m_dwhelpContext);

E-> delete ();} excelapp.setcaption (_T ("FARAD 200D Report Template Settings")); File: // Get Workbooks WBSMYBOOKS.ATTCHDISPATCH (ExceLapp.getWorkBooks (), true); if (ifileexist) {WBMYBOOK. AttachDispatch (wbsMyBooks.Add (_variant_t ((CString) strRepTemplate))); file: //wbMyBook.SetSaved (true); file: //ExcelApp.GetSaveAsFilename (vtMissing, vtMissing, vtMissing, vtMissing, _variant_t ( "vtMissing")) File: //wbmybook.save (); file: // ("xlshared") file: //wbmybook.saveas (_VARIANT_T ("D: // My Worksheet .xls"), _ variant_t ("xladdin") , vtMissing, vtMissing, vtMissing, vtMissing, _variant_t (long (1)), _ variant_t ( "xlUserResolution"), vtMissing, vtMissing, vtMissing); file: // run the macro (CString) ExcelApp.Run (_variant_t ( "auto_open") , vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing , VTMissing, VTMissing, VTMissing, VTMissing, VTMissing; Excelapp .SetVisible (TRUE); Excelapp.ReleaseDispatch ();

}

Else {CSTRING STRSQL = _T ("SELECT Report Name from Report Settings Table"); PrepareRepname (strsql);

Wbmybook.attachdispatch (wbsmybooks.add (vtmissing));

File: // Get Worksheets WSSMYSHEETS.ATTACHDISPATCH (WBMybook.getWorksheets () () (), true);

file: // get sheet1 // wsMysheet.AttachDispatch (wssMysheets.GetItem (_variant_t ( "sheet1")), true); wsMysheet.AttachDispatch (wssMysheets.GetItem (_variant_t ((long) 1)), true); wsMysheet.SetName (_T ("ai"));

File: // Get all Cells, at this time, RGMYRGE is a collection of Cells RgmyRge.attachdispatch (WSMYSHEET.GETCELLS (), true); file: // Setting the value of the value strsqlstmt = prepareSQL ("ai history table"); // Get inquiry statements setTemplatedata (& RGMYRGE, STRSQLSTMT); file: // Get all columns RGMYRGE.attachdispatch (WSMYSHEET.GETCOLUMNS (); file: // Settings Column Width RgmyRge.SetColumnWidth (_VARIANT_T (long) 15)) File: // Set alignment RGMYRGE.SETHORIZONTALALIGNMENT (_variant_t (byte (3)))); // 3: Home File: // Get Sheet2 WSMysheet.attachDispatch (WSSMYSHEETS.GETITEM (_VARIANT_T (long) 2), true ); Wsmysheet.setname (_T ("counter");

File: // Get all Cells, at this time, RGMYRGE is a collection of Cells RgmyRge.attachDispatch (wsmysheet.getcells (), true);

File: // Setting the value of the unit strsqlstmt = prepareSQL ("Counter Historical Table"); // Get Query Strategy SetTemPlatedata (& RGMYRGE, STRSQLSTMT);

File: // Get all columns RGMYRGE.AttachDispatch (wsmysheet.getColumns (), true); file: // Set column width RGMYRGE.SETCOLUMNWIDTH (_VARIANT_T (long) 15); file: // Set alignment RGMYRGE. SETHORIZONTALALIGNMENT (_VARIANT_T (Byte (2))); // 2: Left alignment

file: // get sheet3 wsMysheet.AttachDispatch (wssMysheets.GetItem (_variant_t ((long) 3)), true); wsMysheet.SetName (m_strRepNameArray [0]); wsMysheet.Activate (); // wsMysheet.SetVisible ((long 0); // Make Sheet3 unacceptable Excelapp.SetVisible (TRUE);

File: // Add all report forms

AddSheet (wssMysheets, wsMysheet); file: // run the macro ExcelApp.Run (_variant_t ((CString) "macro 2"), vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing); wbMyBook.SetSaved (true); ExcelApp.SetVisible ( True); File: // Save File WBMYBOOK.SAVECOPYAS (_VARIANT_T (CSTRING) StrrewTemplate);

file: // release the object if (m_strRepNameArray.GetSize ()> 0) m_strRepNameArray.RemoveAll (); rgMyRge.ReleaseDispatch (); wsMysheet.ReleaseDispatch (); wssMysheets.ReleaseDispatch (); wbMyBook.ReleaseDispatch (); wbsMyBooks.ReleaseDispatch ( ); Excelapp.releaseDispatch ();} couid cparameterapp :: setTemplatedata (Range * prgmyRge, CString strsql) {char prefixion [34] [3] = {"c", "d", "e", "F", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r "," S "," t "," u "," v "," w "," x "," y "," z "," AA "," AB "," AC "," AD ", "AE", "AF", "AG", "AH", "AI", "AJ",}

CODBCDYNAMIC ODBCDYNAMIC (_T ("SA"), "SA", ""; // Dynamically Connect Data Source TRY {// Execute Query ODBCDYNAMIC.EXECUTESQL (STRSQL); if (odbcdynamic.m_berror) {AFXMessageBox ("Sorry, no This data ", MB_OK); return;} // Display query result set int IRecordnum = odbcdynamic.m_odbcRecordArray.getsize (); // Current record number // Add result set record for (int IRecord = 0; Irecord getSize (); // Num is the number of rows of the table and the product of the number of columns (int J = 0; J getat (j); strcolname = podbcrecord-> m_strcolname; // column name podbcRecord-> m_pvar-> getStringValue (szvalue); // Value File: // Excel Row Range counting is based on 1 ylik (j setItem (_variant_t (long) 1), _ variant_t ((long) (j 1)), _ variant_t (strcolname)); if (0 <= Strlen (szvalue)) {INT Irow = J / (Num / IRecordnum) 2; // Line INT ICOL = J% (Num / IRecordnum) 1; // Column number IF ((IROW> = 2) && (Icol> = 3)) { Char Val [8]; char postfixion [8]; _itoa (irow, postfixion, 10); strcpy (val, prefixion [iCOL-3]); strcat (val, postfixion); prgmyRGE-> setItem (_variant_t ((long) IROW), _ variant_t ((long) iCol), _ variant_t (val));

} Else {prgmyRGE-> setITEM (_variant_t (long) iRow, _ variant_t (long) iCol), _ variant_t (szvalue));}}} Break;}}}} Break;}}}}} Break;}} catch (cuthException * PE) {PE-> ReportError () PE-> delete ();

return;} _ Worksheet CParameterApp :: AddSheet (Worksheets & worksheets, _Worksheet & worksheet) {file: // add all forms _Worksheet worksheettemp; CString strSelRepName; int nCount = m_strRepNameArray.GetSize ();

for (int i = 1 / * 0 * /; i

} Return WorksheetTemp;