OLE operations with Excel (the most complete information) (04.2.19 Update) Author: ccrun, as reproduced please ensure the integrity of this document, and to indicate the source. Welcome to C Builder research, http://www.ccrun.com/doc/go.asp? Id = 529 with OLE OLE EXCEL (currently the most complete information) (04.2.19 update) This document is from the Internet, large Part is Ccrun (old demon) in Excel through recording macro-> to see macro code -> to CB code. This document is constantly updated. Welcome everyone to pay attention to the operation of Excel in the application, first You must include COMOBJ.HPP # include "COMOBJ.HPP" C Builde "COMOBJ.HPP" C Builde "COMOBJ.HPP" C Builder "COMOBJ.HPP" C Builder "COMOBJ.HPP" C Builder Packed the features of Excel automation objects in the following four OLECT CLASS functions, and the application can easily call. Set Object Properties: Void OlePropertySet; Get Object Properties: Variant Olepropertyget; Call Object: 1) Variant Olefunction (Function Name, Parameters ...); 2) Void OleProcedure (procedure name, the parameter ......); in the program macros can be used to save time: #define PG OlePropertyGet # define PS OlePropertySet # define FN OleFunction # define PR OleProcedure example: ExcelApp.OlePropertyGet ( "workbooks") OleFunction (. "Add"); writable as Excelapp.pg ("Workbooks"). Fn ("add"); using OLE to control Excel2000 in C Builder, you must master Excel2000's automation objects and Microsoft Word Visual Basic Help files About Excel Objects, methods and properties. Object is an Excel element, attribute is one of the features or operations of an object, and the method is an action that can be performed. First define the following variables: Variant Excelapp, Workbook1, Sheet1, Range1; 1, the objects commonly used in Excel are: Application, Workbooks, Worksheets, etc.
★ create application objects ★ Variant ExcelApp; ExcelApp = Variant :: CreateObject ( "Excel.Application"); or ExcelApp = CreateOleObject ( "Excel.Application"); ★ create a workbook object ★ Variant WorkBook1; WorkBook1 = ExcelApp.PG ( " ActiveWorkbook "); ★ Create a worksheet object ★ Variant Sheet1; Sheet1 = Workbook1.pg (" Activesheet "); ★ Create a zone object ★ Variant Range; Range = Sheet1.pg (" Range "," A1: A10 "); or Using Excel.exec (Propertyget ("Range") << ""). EXEC ("SELECT")); 2, commonly used attribute operations: ★ Make Excel programs ★ Excelapp.ps ("Visible "(VARIANT) FALSE); ★ New Excel file ★ ◎ New system template's workbook Excelapp.pg (" Workbooks "). Fn (" add ") // Default workbook Excelapp.pg (" Workbooks "). Fn ("Add", 1) // Single Worksheet Excelapp.pg ("Workbooks"). FN ("Add", 2) // Chart Excelapp.pg ("Workbooks"). FN ("Add", 3) / / Macro table Excelapp.pg ("Workbooks"). FN ("Add", 4) // International General Macro Table Excelapp.pg ("Workbooks"). FN ("Add", 5) // with the default excelapp .Pg ("Workbooks"). FN ("Add", 6) // Workbook and only one table or EXCELAPP XEC method Excel.exec ("Workbooks")). EXEC ("Add"); ◎ New ourselve created template's workbook Excelapp.pg ("Workbooks"). FN ("Add", "C : //Temp/Result.xlt "); ★ Open workbook ★ Excelapp.pg (" Workbooks "). FN (" Open "," path name .xls ") ★ Save workbook ★ Workbook1.fn (" SAVE "); // Save Workbook1.fn (" Saves "," File Name "); // Workbook Save As, Path Note" // "★ Exit Excel ★ Excelapp.fn (" Quit "); ExcelappPp =
Unassigned; or excelapp.exec ("quit")); ★ Operation worksheet ★ ◎ Select the first worksheet in the work table Workbook1.pg ("Sheets", 1) .pr ("SELECT"); Sheet1 = Workbook1.pg ("Activesheet"); ◎ Rename Worksheet Sheet1.ps ("Name", "New Name"); ◎ Total number of worksheets in the current workbook int nsheetcount = workbook1.pg ("Sheets" ) .Pg ("count"); ★ Operation line and column ★ ◎ How many rows and how many columns in the current worksheet: Sheet1.pg ("UsedRange"). PG ("ColumnS"). PG ("count") ; // Column number sheet1.pg ("UsedRange"). PG ("rows"). PG ("count"); // Route ◎ Settings Columns wide Excelapp.pg ("Column, 1) .ps (" ColumnWidth ", 22); or Range = Excelapp.pg (" Cells ", 1, 3); Range.ps (" ColumnWidth ", 22); ◎ Set line high ExceLapp.pg (" Rows ", 2) .ps ( "RowHeight", 25); or Range = Excelapp.pg ("Cells", 2, 1); Range.ps ("RowHeight", 25); ◎ Insert a line of Sheet1.pg in front of the worksheet ("Rows", 1) .PR ( "Insert"); ◎ delete a row ExcelApp.PG ( "rows", 2) .PR ( "delete"); // delete the line 2 // author: ccrun, as reproduced please ensure this The integrity of the document is indicated.
// Welcome to C Builder Study www.ccrun.com // Excerpted from: http://www.ccrun.com/doc/go.asp? Id = 529 ★ Operating cell ★ ◎ Set cell font sheet1.pg (" Cells ", 1, 1) .pg (" font "). PS (" Name "," Lishu "); // Font Sheet1.pg (" Cells ", 2, 3) .pg (" font "). PS ("SIZE", 28); // Size ◎ Set the selected area font range.pg ("cells"). PG ("font"). PS ("Size", 28); Range.pg ("Cells") .Pg ("font"). PS ("Color", RGB (0, 0, 255)); these parameters set: font name: "书" // Font Name Size: 12 // Font Size Color: RGB ( *, *, *) // Color underline: true / false // Underline italic: true / false // ◎ Set cell format is a decimal percentage sheet1.pg ("cells", 1, 1) .ps ("NumberformatLocal "," 0.00% "); ◎ Set the vertical alignment of the cell RANGE = Excelapp.pg (" cells ", 3, 4); // 1 = on the upper 2 = Case 3 = Under the alignment 4 = two ends Align 5 = Dispersion Align Range.ps ("VerticalAlignment", 2); ◎ Setting cell text is automatic wrap Range = Excelapp.pg ("Cells", 3, 4); Range.ps ("WrapText", True ★ Merge of cells ★ ◎ Range = Sheet1.pg ("Range", "A1: A2"); // A1 and A2 cell merge string strang = "a" INTOSTR (J) ":" "C" INTOSTR (J); / / For example: A1: C5 Range1 = Sheet1.pg ("Range", strroge.c_str ()); // You can use variable control cells to merge RANGE1.FN ("Merge", False); ★ Read and write cell ★ ◎ Specifies the cell Grid String Strign = "Abcdefg"; Sheet1.pg ("Cells", 3, 6) .ps ("Value", StrValue.c_STR ()); Sheet1.pg ("Cells", J, 1). PS ("
Value "," total record: " String (j-6)); or use Excel.exec (Propertyget (" Cells ") << 1 << 3) .EXEC (PropertySet (" Value ") << 15); ◎ Selected Area Units Range.pg ("Cells"). PS ("Value", 10); ◎ Selected Area Row Find value Range.pg ("Rows", 1) .ps ("Value", 1234) ◎ Workscript column assignment Sheet1.pg ("Column", 1) .ps ("Value", 1234); ◎ Read value: String Strign = Sheet1.pg ("cells", 3, 5) .pg ("Value"); ★ Window Properties ★ ◎ Display Attribute Excelapp.ps ("WindowsTate", 3); // Maximize Display 1 --------- Xlnormal // Normal Display 2 ----- ---- Xlminimized // Minimize Display 3 --------- XLmaximized // Maximize Shows ◎ Status Bar Properties Excelapp.ps ("STATUSBAR", "Hello, please wait.
querying! "); Excelapp.ps (" statusbar ", false); // Restore into default value ◎ Title properties: excelapp.ps (" CAPTION "," Query System "); 3, Operation Chart ★ Add Chart Variant Chart; Chart = Excelapp.exec ("Charts")). EXEC ("Add")); Excelapp.exec (PropertySet ("Visible") << true); Chart.exec (PropertySet ("TYPE") << 4100); ★ Scroll chart for (int nRotate = 5; nrotate <= 180; nrotate = 5) {chart.exec ("Rotation") << nrotate;} for (int nRotate = 175; nRotate> = 0; nrotate - = 5) {chart.exec ("Rotation") << nrotate);} In addition, to ensure that the program can run normally, you need to determine whether the target machine is installed in the program; try {eXcelapp = variant :: CREATEOBJECT ("Excel.Application");} catch (...) {showMessage ("Run Excel error, confirm installation installation"); return;} #include "compj.hpp" // ---------- -------------------------------------------------- -------------------- / / Sort by specified columns in the specified Excel file // strexcelfilename: Excel file name // ncol: The specified number // NSortStyle: 1: Ascending, 2: Descending Void SortexcelColumn (String Strexcelfilename, Int Ncol, int nSortStyle) {Variant vExcelApp, vWorkbook, vRange; vExcelApp = Variant :: CreateObject ( "Excel.Application"); vExcelApp.OlePropertySet ( "Visible", false);. vExcelApp.OlePropertyGet ( "WorkBooks") OleProcedure ( "Open" , strExcelFileName.c_str ()); vWorkbook = vExcelApp.OlePropertyGet ( "ActiveWorkbook"); vExcelApp.OlePropertyGet ( "Columns", nCol) .OleProcedure ( "Select");. vExcelApp.OlePropertyGet ( "ActiveSheet") OlePropertyGet ( "Cells "
