Export the content in DBGrid to the Excel file

//---------------------------------------------------------------------------
// Export the content in DBGrid to the Word Document
//---------------------------------------------------------------------------
Void _ fastcall dbgrid2word (TDBGrid * dbg, string strdocfile)
{
If (! Dbg-> datasource-> dataset-> active) // return if the dataset is not opened.
Return;
Variant vwordapp, vtable, vcell;
Try
{
Vwordapp = variant: Createobject ("word. application ");
}
Catch (...)
{
MessageBox (0, "An error occurred while starting word. It may be that word is not installed .",
"Dbgrid2word", mb_ OK | mb_iconerror );
Vwordapp = unassigned;
Return;
}
// Hide the word Interface
Vwordapp. olepropertyset ("visible", false );
// Create a document
Vwordapp. olepropertyget ("events"). olefunction ("add ");
//
Variant vselect = vwordapp. olepropertyget ("selection ");
// Set the font and size
Vselect. olepropertyget ("font"). olepropertyset ("size", dbg-> font-> size );
Vselect. olepropertyget ("font"). olepropertyset ("name", dbg-> font-> name. c_str ());
// Number of rows to insert into the table
Int nrowcount (DBG-> datasource-> dataset-> recordcount + 1 );
Nrowcount = nrowcount <2? 2: nrowcount;
// Number of columns to insert into the table
Int ncolcount (DBG-> Columns-> count );
Ncolcount = ncolcount <1? 1: ncolcount;
// Insert a table with the same number of rows and columns as DBGrid in the Word document.
Vwordapp. olepropertyget ("activedocument"). olepropertyget ("tables ")
. Oleprocedure ("add ",
Vselect. olepropertyget ("range "),
Nrowcount, // number of rows
Ncolcount, // Number of Columns
1, // defaulttablebehavior: = wdword9tablebehavior
0); // autofitbehavior: = wdautofitfixed
// Operate on this table
Vtable = vwordapp. olepropertyget ("activedocument ").
Olefunction ("range"). olepropertyget ("tables"). olefunction ("item", 1 );
// Set the cell width
For (INT I = 0; I <ncolcount; I ++)
{
Int ncolwidth = dbg-> Columns-> items [I]-> width;
Vtable. olepropertyget ("columns"). olefunction ("item", I + 1)
. Olepropertyset ("preferredwidthtype", 3); // wdpreferredwidthpoints
Vtable. olepropertyget ("columns"). olefunction ("item", I + 1)
. Olepropertyset ("preferredwidth", ncolwidth );
}
// Write the column name to the Word Table first
For (Int J = 0; j <dbg-> Columns-> count; j ++)
{
Vcell = vtable. olefunction ("cell", 1, J + 1 );
Vcell. olepropertyset ("range", dbg-> Columns-> items [J]-> fieldname. c_str ());
// Column name cell background color // wdcolorgray125
Vcell. olepropertyget ("Shading ")
. Olepropertyset ("backgroundpatterncolor", 14737632 );
}
// Write data in DBGrid to the Word Table
Dbg-> datasource-> dataset-> first ();
For (INT I = 0; I <nrowcount; I ++)
{
// 63 63 72 75 6e 2E 63 6f 6d
For (Int J = 0; j <dbg-> Columns-> count; j ++)
{
Vcell = vtable. olefunction ("cell", I + 2, J + 1 );
Vcell. olepropertyset ("range ",
Dbg-> datasource-> dataset-> fieldbyname (
Dbg-> Columns-> items [J]-> fieldname)-> asstring. c_str ());
}
Dbg-> datasource-> dataset-> next ();
}
// Save the Word document and exit
Vwordapp. olepropertyget ("activedocument ")
. Oleprocedure ("saveas", strdocfile. c_str ());
Vwordapp. olepropertyget ("activedocument"). oleprocedure ("close ");
Application-> processmessages ();
Vwordapp. oleprocedure ("quit ");
Application-> processmessages ();
Vwordapp = unassigned;
// End of work
MessageBox (0, "dbgrid2word conversion ends! ",
"Dbgrid2word", mb_ OK | mb_iconinformation );
}
//---------------------------------------------------------------------------
// Export the content in DBGrid to the Excel file
//---------------------------------------------------------------------------
Void _ fastcall dbgrid2excel (TDBGrid * dbg, string strxlsfile)
{
If (! Dbg-> datasource-> dataset-> active) // return if the dataset is not opened.
Return;
Variant vexcelapp, vsheet;
Try
{
Vexcelapp = variant: Createobject ("Excel. application ");
}
Catch (...)
{
MessageBox (0, "An error occurred while starting excel. Excel may not be installed .",
"Dbgrid2excel", mb_ OK | mb_iconerror );
Return;
}
// Hide the Excel page
Vexcelapp. olepropertyset ("visible", false );
// Create a worksheet
Vexcelapp. olepropertyget ("workbooks"). olefunction ("add", 1); // Worksheet
// Operate on this worksheet
Vsheet = vexcelapp. olepropertyget ("activeworkbook ")
. Olepropertyget ("sheets", 1 );
// Set the font of the Excel document
Vsheet. oleprocedure ("select ");
Vsheet. olepropertyget ("cells"). oleprocedure ("select ");
Vexcelapp. olepropertyget ("selection"). olepropertyget ("font ")
. Olepropertyset ("size", dbg-> font-> size );
Vexcelapp. olepropertyget ("selection"). olepropertyget ("font ")
. Olepropertyset ("name", dbg-> font-> name. c_str ());
Vexcelapp. olepropertyget ("selection"). olepropertyget ("font ")
. Olepropertyset ("fontstyle", "regular ");
Vsheet. olepropertyget ("cells", 1, 1). oleprocedure ("select ");
// Number of rows in the table
Int nrowcount (DBG-> datasource-> dataset-> recordcount + 1 );
Nrowcount = nrowcount <2? 2: nrowcount;
// Number of columns in the table
Int ncolcount (DBG-> Columns-> count );
Ncolcount = ncolcount <1? 1: ncolcount;
// Set the cell width
For (INT I = 0; I <ncolcount; I ++)
{
Int ncolwidth = dbg-> Columns-> items [I]-> width;
Vexcelapp. olepropertyget ("columns", I + 1)
. Olepropertyset ("columnwidth", ncolwidth/7 );
}
// Write the column name to the Excel table first
For (Int J = 0; j <dbg-> Columns-> count; j ++)
{
// The Row Height of the title line
Vexcelapp. olepropertyget ("rows", 1). olepropertyset ("rowheight", 20 );
//
Vsheet. olepropertyget ("cells", 1, J + 1)
. Olepropertyset ("value ",
Dbg-> Columns-> items [J]-> fieldname. c_str ());
// Set the background color of the column name cell
Variant Vinter = vsheet. olepropertyget (
"Cells", 1, J + 1). olepropertyget ("Interior ");
Vinter. olepropertyset ("colorindex", 15); // gray
Vinter. olepropertyset ("pattern", 1); // xlsolid
Vinter. olepropertyset ("patterncolorindex",-4105); // xlautomatic
}
// Write data in DBGrid to an Excel table
Dbg-> datasource-> dataset-> first ();
For (INT I = 0; I <nrowcount; I ++)
{
// The Row Height of normal data rows is 16
Vexcelapp. olepropertyget ("rows", I + 2). olepropertyset ("rowheight", 16 );
// 63 63 72 75 6e 2E 63 6f 6d
For (Int J = 0; j <dbg-> Columns-> count; j ++)
{
Vsheet. olepropertyget ("cells", I + 2, J + 1)
. Olepropertyset ("value ",
Dbg-> datasource-> dataset-> fieldbyname (
Dbg-> Columns-> items [J]-> fieldname)-> asstring. c_str ());
}
Dbg-> datasource-> dataset-> next ();
}
// Save the Excel document and exit
Vexcelapp. olepropertyget ("activeworkbook ")
. Olefunction ("saveas", strxlsfile. c_str ());
Vexcelapp. olefunction ("quit ");
Vsheet = unassigned;
Vexcelapp = unassigned;
// End of work
MessageBox (0, "dbgrid2excel conversion ends! ",
"Dbgrid2excel", mb_ OK | mb_iconinformation );
}
//---------------------------------------------------------------------------