Yesterday Ccrun just wrote an article to export the contents of ListView to a Word document and an Excel document, and today's writing program happened to use the function of exporting the data in DBGrid to office, and then write a brother version of the function. Dbgrid2word and Dbgrid2excel, respectively, to export the data in DBGrid to Word and Excel documents. It should be noted that the data in the DBGrid is not in the code database, because the dataset may be filtered when it is opened, depending on how the user opens the DataSet, and in short, how much data is displayed in the DBGrid. For the sake of writing code very hard, please leave the source and original author information in the reprint. It's thank. :D
If you have good ideas, or bugs in your code, welcome to the discussion: info@ccrun.com
2005.10.13 v0.1
First edition release
//---------------------------------------------------------------------------
Export data from DBGrid to a Word document
v0.1 by Ccrun (old demon) 2005.10.13 1:40
//---------------------------------------------------------------------------
void __fastcall Dbgrid2word (TDBGrid *dbg, String strdocfile)
{
if (!dbg->datasource->dataset->active)//DataSet is returned without opening
Return
Variant Vwordapp, vTable, Vcell;
Try
{
Vwordapp = Variant::createobject ("Word.Application");
}
catch (...)
{
MessageBox (0, "error starting Word, may not have Word installed.",
"Dbgrid2word", MB_OK | MB_ICONERROR);
Vwordapp = unassigned;
Return
}
Hide Word interface
Vwordapp.olepropertyset ("Visible", false);
Create a new document
Vwordapp.olepropertyget ("Documents"). Olefunction ("Add");
//
Variant vselect = Vwordapp.olepropertyget ("Selection");
Set the font size
Vselect.olepropertyget ("Font"). Olepropertyset ("Size", dbg->font->size);
Vselect.olepropertyget ("Font"). Olepropertyset ("Name", Dbg->font->name.c_str ());
Number of rows to insert a table
int nRowCount (Dbg->datasource->dataset->recordcount + 1);
nRowCount = nRowCount < 2? 2:nrowcount;
Number of columns to insert a table
int Ncolcount (dbg->columns->count);
Ncolcount = Ncolcount < 1? 1:ncolcount;
Insert a table in a Word document that is essentially the same as the number of dbgrid rows
Vwordapp.olepropertyget ("ActiveDocument"). Olepropertyget ("Tables")
. Oleprocedure ("Add",
Vselect.olepropertyget ("Range"),
nRowCount,//Line Count
Ncolcount,//Number of columns
1,//Defaulttablebehavior:=wdword9tablebehavior
0); Autofitbehavior:=wdautofitfixed
Manipulate this table
vTable = Vwordapp.olepropertyget ("ActiveDocument").
Olefunction ("Range"). Olepropertyget ("Tables"). Olefunction ("Item", 1);
Set the width of a cell
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);
}
//----------------------------------------------------------------------------
Sorry, this hint again, in order to prevent irresponsible reprint, had to leave some information here.
Author: ccrun (old demon) info@ccrun.com
This paper studies from C++builder-http://www.ccrun.com/article/go.asp?i=635&d=g75jbn
//----------------------------------------------------------------------------
First write the column name to the Word table
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 from DBGrid to a Word table
Dbg->datasource->dataset->first ();
for (int i=0; i<nrowcount; i++)
{
6E 2E 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 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 over!"),
"Dbgrid2word", MB_OK | Mb_iconinformation);
}
//---------------------------------------------------------------------------
Export data from DBGrid to an Excel document
v0.1 by Ccrun (old demon) 2005.10.13 1:55
//---------------------------------------------------------------------------
void __fastcall Dbgrid2excel (TDBGrid *dbg, String strxlsfile)
{
if (!dbg->datasource->dataset->active)//DataSet is returned without opening
Return
Variant Vexcelapp, Vsheet;
Try
{
Vexcelapp = Variant::createobject ("Excel.Application");
}
catch (...)
{
MessageBox (0, "error starting Excel, may not have Excel installed.",
"Dbgrid2excel", MB_OK | MB_ICONERROR);
Return
}
Hide Excel Interface
Vexcelapp.olepropertyset ("Visible", false);
Create a new worksheet
Vexcelapp.olepropertyget ("Workbooks"). Olefunction ("Add", 1); Work table
Manipulate this worksheet
Vsheet = Vexcelapp.olepropertyget ("ActiveWorkbook")
. Olepropertyget ("Sheets", 1);
Set the font for an 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", "General");
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 a table
int Ncolcount (dbg->columns->count);
Ncolcount = Ncolcount < 1? 1:ncolcount;
Set the width of a cell
for (int i=0; i<ncolcount; i++)
{
int ncolwidth = dbg->columns->items[i]->width;
Vexcelapp.olepropertyget ("Columns", I + 1)
. Olepropertyset ("ColumnWidth", NCOLWIDTH/7);
}
//----------------------------------------------------------------------------
Sorry, this hint again, in order to prevent irresponsible reprint, had to leave some information here.
Author: ccrun (old demon) info@ccrun.com
This paper studies from C++builder-http://www.ccrun.com/article/go.asp?i=635&d=g75jbn
//----------------------------------------------------------------------------
First write column names to excel tables
for (int j=0; j<dbg->columns->count; j + +)
{
Row height of header row
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 a column name cell
Variant Vinter = Vsheet.olepropertyget (
"Cells", 1, J + 1). Olepropertyget ("Interior");
Vinter.olepropertyset ("ColorIndex", 15); Grey
Vinter.olepropertyset ("pattern", 1); Xlsolid
Vinter.olepropertyset ("Patterncolorindex",-4105); Xlautomatic
}
Write data from DBGrid to an Excel table
Dbg->datasource->dataset->first ();
for (int i=0; i<nrowcount; i++)
{
Row height of normal data row 16
Vexcelapp.olepropertyget ("Rows", I + 2). Olepropertyset ("RowHeight", 16);
6E 2E 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 an 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 over!"),
"Dbgrid2excel", MB_OK | Mb_iconinformation);
}
Test code
Dbgrid2word (DBGRID1, "C:\\ccrun\\123.doc");
Dbgrid2excel (DBGRID1, "C:\\ccrun\\123.xls");
