Use OLE to control the Excel table in C ++ Builder

zhaozj2021-02-16  94

The author often uses Excel to do a data report in actual work. Most of the data should be read from the database so that I use C Builder to make a report program, which is very convenient, now share it to C. Builder enthusiasts, even if you do something about rich C Builder documentation. First save the Excel report file to a specified directory, preferably in the subdirectory of the executable program, as a template file. You can set the report title, head, and so on. Here is saved in the TRPT subdirectory. Then built a Report directory, as a report target folder, stored in the report, can be done directly by the user. First determine that there is Office in your machine. Here an office2000 is an example. Create a new project in C Builder, put a two buttons SaveButton and ReadButton on the form Form1, which are used to save data to the Excel table and display the excel table just saved. In the click event of the SaveButton button, put the data taken from the database into the specified Excel table and copy the file to the Report directory. Display the report file in the Report directory in the restbutto button, which is convenient for users to modify and save. Define several variables in the Form1.h header:

PRIVATE: VARIANT EX, WB, Sheet, EregE, EBORDERS

And contain the following statement in the file head:

#include "excel_2k_srvr.h" #include

Add to Form1.cpp

#pragma link "excel_2k_srvr"

The main code is as follows:

Void __fastcall tform1 :: savebutton-> enabled = false; readbutton-> enabled = false; // Make two buttons invalid file: // Report the full directory name of the file cardsend.xls ANSIString Excelfilename = getCurrentDir () "// trpt // Table.xls"; if (! Fileexists (excelfilename)) {Application-> MessageBox ("Report Template file does not exist, unable to open!", "Error", MB_ICONSTOP | MB_OK ); return;} file: // Establish an Excel OLE object EXTRY {EX = Variant :: CreateObject ("excel.application");} catch (...) {Application-> MessageBox ("Unable to start Excel", " Error ", MB_ICONSTOP | MB_OK); return;} file: // Sets Excel to invisible ex. op.OlePropertySet (" Visible ", false); file: // open the specified Excel report file. It is best to set only one sheet in the report file. EX.OLEPROPERTYGET ("Workbooks"). Oleprocedure ("open", excelfilename.c_str ()); WB = ex. oc propertyget ("ActiveWorkbook"); Sheet = Wb.OlePropertyget ("ActiveSheet"); // Get the current default SHEETFILE: / / Clear the Excel table, here is used to empty to the 300th line. It is enough for a general form. Ansistring StrrowTemp; Ansistring StrRrange; Int iCols, IROWS; // Remarks Columns and Row / * Starting from the third row to 300. The first line is a table title, the second line is a subtitle or a table of tabular. * / for (IROWS = 3; IROWS <300; IROWS ) {file: // Suppose is only 6 columns.

For (Icols = 1; ICOLS <7; ICOLS ) {file: // Clear line Sheet.olePropertyget ("cells", iRows, Icols) .olePropertySet ("Value", "");} file: // Remove the table Border StrRrange = "a" INTOSTR (IROWS) ": f" INTOSTR (IROWS); // Get Operation Operating Range Erange = SHEEPROPERTYGET ("Range", StrRange.c_str ()); eBorders = Erange.olePropertyget (" Borders "); // Get border object eBorders.olePropertySet (" lineestyle ", xlnone);} ansistring strptrdate; file: // stores the current date as a tabular dateparetor = '-'; shortdateformat =" YYY / M / D "; // Set as annual / month / day format strptrdate = datetostr (Date ()); // Take the previous date ANSISTRING Stryear = strptrdate.substring (1, 4); strptrdate = strptrdate.substring (6, strptrdate.length) ) -5); Ansistring strMonth = strptrdate.substring (1, strptrdate.pos ("-") - 1); Ansistring strday = strptrdate.substring (strptrdate.pos ("-") 1, strptrdate.length () - Strptrdate.pos ("-")); strptrdate = strPtrdate = strPrYear "year" strMonth "month" strday "day"; ANSISSIING STRDATA = "Report Title"; // Report Title File: // Place the report title in the first Line first column. Prior to this, the title format of the report file should be set. Sheet.olePropertyget ("Cells", 1, 1) .olePropertySet ("Value", strdata.c_str ()); file: // Place the Table Date on the right side of the table.

Sheet.olePropertyget ("Cells", 2, 5) .olePropertySet ("Value", strptrdate.c_str ()); IROWS = 3; // Place the table in the third line list Sheet.olePropertyget ("Cells", IROWS , 1) .olePropertySet ("Value", "Column Name 1"); Sheet.olePropertyget ("Cells", IROWS, 2) .olePropertySet ("Value", "Colum Name 2"); Sheet.olePropertyget ("Cells" , IROWS, 3) .OLEPROPERTYSET ("Value", "Column Name 3"); Sheet.olePropertyget ("Cells", IROWS, 4) .olePropertySet ("Value", "Column Name 4"); Sheet.olePropertyget (" Cells ", IROWS, 5) .OLEPROPERTYSET (" Value "," Column Name 5 "); Sheet.olePropertyget (" Cells ", IROWS, 6) .OLEPROPERTYSET (" Value "," Column Name 6 "); file: / / Paint table Border, score between A3: F3 StrRrange = "a" INTOSTR (IROWS) ": f" INTOSTR (IROWS); ERANGE = Sheet.olePropertyget ("Range", StrRrange.c_str ()) ; EBorders = ERange.OlePropertyGet ( "Borders"); EBorders.OlePropertySet ( "linestyle", xlContinuous); EBorders.OlePropertySet ( "weight", xlThin); EBorders.OlePropertySet ( "colorindex", xlAutomatic); iRows ; file: / / Take data from the database (omitted), assuming that the data set is placed in Query1.

Query1-> Open (); // Open Data Set File: // Take the WHILE (! Query1-> EOF) {file: // Loop Take the field of the field to the EXCEL table corresponding to the ranks in the ranks, for (iCOLS = 1 Icols <7; icols ) {strrowTemp = query1-> fields-> fields [icols-1] -> asstring; sheet.olePropertyget ("cells", iRows, icols) .olepropertyset ("value", strrowtemp.c_str () );} file: // draws the table border strRrange = "a" INTOSTR (IROWS); ERANGE = SHEET.OLEPROPERTYGET ("RANGE", STRRANGE.C_STR ()) ; EBorders = ERange.OlePropertyGet ( "Borders"); EBorders.OlePropertySet ( "linestyle", xlContinuous); EBorders.OlePropertySet ( "weight", xlThin); EBorders.OlePropertySet ( "colorindex", xlAutomatic); iRows ; Query1-> Next ();} // while end wb.oleprocedure ("save"); // save the form wb.oleprocedure ("close"); turn off the form ex. oolefunction ("quit"); exit an Excelfile: / / Define the target file Ansistring DestinationFile = getCurrentDir () "//report/table.xls"; file: // copy the excel table file that just modified to the Report directory if (! CopyFile (ExcelfileName.c_STR (), DestinationFile .c_str (), false) {Application-> MessageBox ("Copy file operation failed, Excel files may be in use!", "Error", MB_ICONSTOP | MB_OK; Return;} Application-> MessageBox ("successfully completed report Save! / N can be opened by / ' Excel file / 'button for report work "," Tips ", MB_ICONITIONOMATION | MB_OK); SaveButton -> Enabled = true; readbutton -> enabled = true;} // TRY End Catch (...) {Application-> MessageBox "Operation Excel form failed! "," Wrong ", MB_ICONSTOP | MB_OK); WB.OLEPROCEDURE (" close "); ex. oolefunction (" quit "); savebutton -> enabled = true; readbutton -> enabled = false;}} to this, complete report data Write work. If you want to perform the completed Excel table, you can click "Open Excel Table File Button" to make modifications, save, print, etc. Operation. Readbutton's clicking event is implemented as follows:

转载请注明原文地址:https://www.9cbs.com/read-11582.html

New Post(0)