Using OLE to control Excel tables in C + + Builder

The author in the actual work often use Excel table to do the data report, most forms of data to read from the database, so I used C++builder to do a report program, convenient a lot, now share it to c++builder enthusiasts, even if the rich C + + Builder's documentation, do something.

First, Save the Excel report file in a specified directory, preferably in the subdirectory of the executable program, as a template file. You can set the report title and table first. This is saved in the TRPT subdirectory.

And then build a report directory, as the target folder of reports, storing the report of the completed data, can be directly operated by the user.

First make sure that you have Office installed on your machine. Here a Office2000 for example.

To create a new project in C++builder, put a two-button Savebutton and Readbutton on the form Form1 to save the data to the Excel table and display the Excel table that you just saved.

In the Click event of the Savebutton button, place the data from the database into the specified Excel table and copy the file to the list. The report directory is displayed in the Click event of the Readbutto button, which is convenient for the user to modify and save separately.

Define several variables in the Form1.h header file:

private:
Variant Ex,Wb,Sheet,ERange,EBorders;

and include the following statement in the file header:

#include "Excel_2K_SRVR.h"
#include

Add in Form1.cpp file header

#pragma link "Excel_2K_SRVR"

The main code is as follows:

void __fastcall TForm1:: Savebuttonclick (TObject *sender)
{
Try
{
savebutton->enabled = false;
readbutton->enabled = false;//makes two buttons invalid
file://the full directory name of the report file Cardsend.xls
Ansistring excelfilename = Getcurrentdir () + "\\trpt\\table.xls";
if (! FileExists (Excelfilename))
{
Application->messagebox (the report template file does not exist and cannot be opened!) ",
"Error", mb_iconstop| MB_OK);
Return
}
file://to build Excel OLE Object ex
Try
{
Ex = Variant::createobject ("Excel.Application");
}
catch (...)
{
Application->messagebox ("Unable to start Excel", "Error", mb_iconstop| MB_OK);
Return
}
file://set Excel to Invisible
Ex.olepropertyset ("Visible", false);
file://opens 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.olepropertyget ("ActiveWorkbook");
Sheet = Wb.olepropertyget ("ActiveSheet");//Get current default Sheet
file://empty the Excel table, this is to empty the loop to line No. 300. It is enough for a general form.
Ansistring strrowtemp;
Ansistring Strrange;
int icols,irows;//record number of columns and rows
/* starts at the third line and ends at line No. 300. The first row is the table title, and the second row is a subtitle or a tab date. */
for (irows=3;irows<300;irows++)
{file://assumes only 6 columns.
for (icols = 1;icols < 7; icols++)
{
file://Empty Line
Sheet.olepropertyget ("Cells", Irows,icols). Olepropertyset ("Value", "");
}
file://Remove Table border
Strrange = "A" +inttostr (irows) + ": F" +inttostr (irows);//Get operating range
Erange = Sheet.olepropertyget ("Range", Strrange.c_str ());
eborders = Erange.olepropertyget ("Borders");//Get Border Object
Eborders.olepropertyset ("LineStyle", xlnone);
}
Ansistring strptrdate; FILE://holds the current date as a tabulation date
DateSeparator = '-';
Shortdateformat = "yyyy/m/d";//set to Year/month/day format
Strptrdate = Datetostr (date ());//Take current 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 = stryear+ "year" +strmonth+ "month" +strday+ "Day";
Ansistring strdata = "report title";//Report title
file://the report title to the first column in the first row. Before that, you should set the title format of the report file.
Sheet.olepropertyget ("Cells", 1, 1). Olepropertyset ("Value"),
Strdata.c_str ());
file://the tab date to the right of the second row of the table.
Sheet.olepropertyget ("Cells", 2,5). Olepropertyset ("Value"),
Strptrdate.c_str ());
Irows = 3;//Place the column name of the table in the third line
Sheet.olepropertyget ("Cells", irows,1). Olepropertyset ("Value", "Column name 1");
Sheet.olepropertyget ("Cells", irows,2). Olepropertyset ("Value", "Column 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://Draw a table border and take a range between a3:f3
Strrange = "A" +inttostr (irows) + ": F" +inttostr (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++;
file://data from the database (abbreviated), assuming that the dataset is placed in the Query1.
Query1->open ()//Open Data set
FILE://Cycle Fetch number
while (! query1->eof)
{
file://the data from the field to the rows in the Excel table
for (icols=1;icols<7;icols++)
{
Strrowtemp = query1->fields->fields[icols-1]->asstring;
Sheet.olepropertyget ("Cells", Irows,icols). Olepropertyset ("Value"),
Strrowtemp.c_str ());
}
file://Draw the table border of the row
Strrange = "A" +inttostr (irows) + ": F" +inttostr (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 Table
Wb.oleprocedure ("close");
Ex.olefunction ("Quit"); exit Excel
file://define target filename
Ansistring Destinationfile =
Getcurrentdir () + "\\report\\table.xls";
file://Copy the Excel form file that you just modified Table.xls to the table directory
if (! CopyFile (Excelfilename.c_str (), Destinationfile.c_str (), false)
{
Application->messagebox ("Copy file operation failed, Excel file may be in use!") ",
"Error", mb_iconstop| MB_OK);
Return
}
Application->messagebox ("Save the Report successfully!") \ n can press \ ' Open Excel file \ '
button for report work "," Prompt ", mb_iconinformation| MB_OK);
Savebutton->enabled = true;
Readbutton->enabled=true;
}//try End
catch (...)
{
Application->messagebox ("Operation Excel table failed!") ",
"Error", mb_iconstop| MB_OK);
Wb.oleprocedure ("close");
Ex.olefunction ("Quit");
Savebutton->enabled = true;
Readbutton->enabled=false;
}
}