DBgrid2Excel
经常看到有网友发帖子询问如何将DBGrid中的内容导出到Excel或Word文档中,于是笔者花了点时间写了以下两个函数,分别实现将DBGrid 中数据导出到Word和Excel文档。需要注意的是DBGrid中的数据并不代表数据库中所有的数据,因为数据集在打开的时候有可能进行了筛选,取决于 使用者如何打开这个数据集,总之就是DBGrid中显示多少数据,就导出多少。
一、将DBGrid中的内容导出到Word文档
//---------------------------------------------------------------------------
// 将DBGrid中的数据导出到Word文档
//---------------------------------------------------------------------------
void __fastcall DBGrid2Word(TDBGrid *dbg, String strDocFile)
{
if(!dbg->DataSource->DataSet->Active) // 数据集没有打开就返回
return;
Variant vWordApp, vTable, vCell;
try
{
vWordApp = Variant::CreateObject("Word.Application");
}
catch(...)
{
MessageBox(0, "启动 Word 出错, 可能是没有安装Word.","DBGrid2Word", MB_OK | MB_ICONERROR);
vWordApp = Unassigned;
return;
}
// 隐藏Word界面
vWordApp.OlePropertySet("Visible", false);
// 新建一个文档
vWordApp.OlePropertyGet("Documents").OleFunction("Add");
Variant vSelect = vWordApp.OlePropertyGet("Selection");
// 设置一下字体,大小
vSelect.OlePropertyGet("Font").OlePropertySet("Size", dbg->Font->Size);
vSelect.OlePropertyGet("Font").OlePropertySet("Name", dbg->Font->Name.c_str());
// 要插入表格的行数
int nRowCount(dbg->DataSource->DataSet->RecordCount + 1);
nRowCount = nRowCount < 2? 2: nRowCount;
// 要插入表格的列数
int nColCount(dbg->Columns->Count);
nColCount = nColCount < 1? 1: nColCount;
// 在Word文档中插入与DBGrid行数列数基本相同的一个表格
vWordApp.OlePropertyGet("ActiveDocument").OlePropertyGet("Tables").OleProcedure("Add",
vSelect.OlePropertyGet("Range"),
nRowCount, // 行数
nColCount, // 列数
1, // DefaultTableBehavior:=wdWord9TableBehavior
0); // AutoFitBehavior:=wdAutoFitFixed
// 操作这个表格
vTable = vWordApp.OlePropertyGet("ActiveDocument").
OleFunction("Range").OlePropertyGet("Tables").OleFunction("Item", 1);
// 设置单元格的宽度
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);
}
// 先将列名写入Word表格
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());
// 列名单元格背景颜色 // wdColorGray125
vCell.OlePropertyGet("Shading").OlePropertySet("BackgroundPatternColor", 14737632);
}
// 将DBGrid中的数据写入Word表格
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();
}
// 保存Word文档并退出
vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("SaveAs", strDocFile.c_str());
vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("Close");
Application->ProcessMessages();
vWordApp.OleProcedure("Quit");
Application->ProcessMessages();
vWordApp = Unassigned;
// 工作结束
MessageBox(0, "DBGrid2Word 转换结束!","DBGrid2Word", MB_OK | MB_ICONINFORMATION);
}
void __fastcall DBGrid2Word(TDBGrid *dbg, String strDocFile)
{
if(!dbg->DataSource->DataSet->Active) // 数据集没有打开就返回
return;
Variant vWordApp, vTable, vCell;
try
{
vWordApp = Variant::CreateObject("Word.Application");
}
catch(...)
{
MessageBox(0, "启动 Word 出错, 可能是没有安装Word.","DBGrid2Word", MB_OK | MB_ICONERROR);
vWordApp = Unassigned;
return;
}
// 隐藏Word界面
vWordApp.OlePropertySet("Visible", false);
// 新建一个文档
vWordApp.OlePropertyGet("Documents").OleFunction("Add");
Variant vSelect = vWordApp.OlePropertyGet("Selection");
// 设置一下字体,大小
vSelect.OlePropertyGet("Font").OlePropertySet("Size", dbg->Font->Size);
vSelect.OlePropertyGet("Font").OlePropertySet("Name", dbg->Font->Name.c_str());
// 要插入表格的行数
int nRowCount(dbg->DataSource->DataSet->RecordCount + 1);
nRowCount = nRowCount < 2? 2: nRowCount;
// 要插入表格的列数
int nColCount(dbg->Columns->Count);
nColCount = nColCount < 1? 1: nColCount;
// 在Word文档中插入与DBGrid行数列数基本相同的一个表格
vWordApp.OlePropertyGet("ActiveDocument").OlePropertyGet("Tables").OleProcedure("Add",
vSelect.OlePropertyGet("Range"),
nRowCount, // 行数
nColCount, // 列数
1, // DefaultTableBehavior:=wdWord9TableBehavior
0); // AutoFitBehavior:=wdAutoFitFixed
// 操作这个表格
vTable = vWordApp.OlePropertyGet("ActiveDocument").
OleFunction("Range").OlePropertyGet("Tables").OleFunction("Item", 1);
// 设置单元格的宽度
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);
}
// 先将列名写入Word表格
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());
// 列名单元格背景颜色 // wdColorGray125
vCell.OlePropertyGet("Shading").OlePropertySet("BackgroundPatternColor", 14737632);
}
// 将DBGrid中的数据写入Word表格
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();
}
// 保存Word文档并退出
vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("SaveAs", strDocFile.c_str());
vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("Close");
Application->ProcessMessages();
vWordApp.OleProcedure("Quit");
Application->ProcessMessages();
vWordApp = Unassigned;
// 工作结束
MessageBox(0, "DBGrid2Word 转换结束!","DBGrid2Word", MB_OK | MB_ICONINFORMATION);
}
二、将DBGrid中的内容导出到Excel文档
//*****************************************************************************
//*****************************************************************************
//模块名称 : 打印模块
//函数名称 : DBGrid2Excel
//函数功能 : 执行打印功能
//输入数值 : dgGrid, XlsPath+XlsFile
//输出数值 : xls文件
//返回数值 :
//*****************************************************************************
//注意事项 :
//*****************************************************************************
void TfsForm::DBGrid2Excel(TDBGrid *dbg, String strXlsFile)
{
//判断是否已经存在,如果存在删除
if(FileExists(strXlsFile) != false && DeleteFile(strXlsFile) == false)
{
MessageBox(0, "导出途中请勿\n打开目标文件\n否则导出失败","Warning", MB_OK | MB_ICONERROR);
return;
}
//定义对象
int nIndex,yPos, xPos;
AnsiString Cell;
Variant ExcelMain = Unassigned;
Variant ExcelBook = Unassigned;
Variant ExcelPage = Unassigned;
//创建对象
try
{
ExcelMain = Variant::CreateObject("Excel.Application");
}
catch(...)
{
MessageBox(0, "启动 Excel 出错, 可能是没有安装Excel.","DBGrid2Excel", MB_OK | MB_ICONERROR);
return;
}
ExcelMain.OlePropertySet("Visible", False);
// 新建一个工作表
ExcelBook = ExcelMain.OlePropertyGet("Workbooks").OleFunction("Add", 1); // 工作表
ExcelPage = ExcelMain.OlePropertyGet("ActiveWorkBook").OlePropertyGet("Sheets",1);
//设置标题
yPos = 1;
xPos = 1;
for(nIndex = 0; nIndex < dbg->Columns->Count; nIndex += 1)
{
Cell = dbg->Columns->Items[nIndex]->Title->Caption;
ExcelMain.OlePropertyGet("Columns", xPos).OlePropertySet("ColumnWidth", dbg->Columns->Items[nIndex]->Width / 6 + 1);
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 3); //居中
ExcelPage.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("Value", Cell.c_str());
ExcelPage.OlePropertyGet("Cells", yPos, xPos).OlePropertyGet("Font").OlePropertySet("Color", RGB(0, 0, 255));
xPos += 1;
}
//查询数据
// while(qrAlarm->ControlsDisabled() == false) { qrAlarm->DisableControls(); }
for(dbg->DataSource->DataSet->First(), yPos = 2; dbg->DataSource->DataSet->Eof == false; dbg->DataSource->DataSet->Next(), yPos += 1)
{
for(nIndex = 0, xPos = 1; nIndex < dbg->Columns->Count; nIndex += 1)
{
if(dbg->Columns->Items[nIndex]->Alignment == taLeftJustify)
{
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 2); //居左
}
if(dbg->Columns->Items[nIndex]->Alignment == taCenter)
{
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 3); //居中
}
if(dbg->Columns->Items[nIndex]->Alignment == taRightJustify)
{
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 4); //居右
}
ExcelPage.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("Value", dbg->DataSource->DataSet->FieldByName(dbg->Columns->Items[nIndex]->FieldName)->AsString.c_str());
xPos += 1;
}
}
// 保存Excel文档并退出
ExcelBook.OleProcedure("SaveAs",strXlsFile.c_str());
ExcelBook.OleFunction("Close");
ExcelMain.OleFunction("Quit");
ExcelMain = Unassigned;
ExcelBook = Unassigned;
ExcelPage = Unassigned;
// 工作结束
MessageBox(0, "DBGrid2Excel 转换结束!","DBGrid2Excel", MB_OK | MB_ICONINFORMATION);
return;
}
//*****************************************************************************
//模块名称 : 打印模块
//函数名称 : DBGrid2Excel
//函数功能 : 执行打印功能
//输入数值 : dgGrid, XlsPath+XlsFile
//输出数值 : xls文件
//返回数值 :
//*****************************************************************************
//注意事项 :
//*****************************************************************************
void TfsForm::DBGrid2Excel(TDBGrid *dbg, String strXlsFile)
{
//判断是否已经存在,如果存在删除
if(FileExists(strXlsFile) != false && DeleteFile(strXlsFile) == false)
{
MessageBox(0, "导出途中请勿\n打开目标文件\n否则导出失败","Warning", MB_OK | MB_ICONERROR);
return;
}
//定义对象
int nIndex,yPos, xPos;
AnsiString Cell;
Variant ExcelMain = Unassigned;
Variant ExcelBook = Unassigned;
Variant ExcelPage = Unassigned;
//创建对象
try
{
ExcelMain = Variant::CreateObject("Excel.Application");
}
catch(...)
{
MessageBox(0, "启动 Excel 出错, 可能是没有安装Excel.","DBGrid2Excel", MB_OK | MB_ICONERROR);
return;
}
ExcelMain.OlePropertySet("Visible", False);
// 新建一个工作表
ExcelBook = ExcelMain.OlePropertyGet("Workbooks").OleFunction("Add", 1); // 工作表
ExcelPage = ExcelMain.OlePropertyGet("ActiveWorkBook").OlePropertyGet("Sheets",1);
//设置标题
yPos = 1;
xPos = 1;
for(nIndex = 0; nIndex < dbg->Columns->Count; nIndex += 1)
{
Cell = dbg->Columns->Items[nIndex]->Title->Caption;
ExcelMain.OlePropertyGet("Columns", xPos).OlePropertySet("ColumnWidth", dbg->Columns->Items[nIndex]->Width / 6 + 1);
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 3); //居中
ExcelPage.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("Value", Cell.c_str());
ExcelPage.OlePropertyGet("Cells", yPos, xPos).OlePropertyGet("Font").OlePropertySet("Color", RGB(0, 0, 255));
xPos += 1;
}
//查询数据
// while(qrAlarm->ControlsDisabled() == false) { qrAlarm->DisableControls(); }
for(dbg->DataSource->DataSet->First(), yPos = 2; dbg->DataSource->DataSet->Eof == false; dbg->DataSource->DataSet->Next(), yPos += 1)
{
for(nIndex = 0, xPos = 1; nIndex < dbg->Columns->Count; nIndex += 1)
{
if(dbg->Columns->Items[nIndex]->Alignment == taLeftJustify)
{
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 2); //居左
}
if(dbg->Columns->Items[nIndex]->Alignment == taCenter)
{
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 3); //居中
}
if(dbg->Columns->Items[nIndex]->Alignment == taRightJustify)
{
ExcelMain.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("HorizontalAlignment", 4); //居右
}
ExcelPage.OlePropertyGet("Cells", yPos, xPos).OlePropertySet("Value", dbg->DataSource->DataSet->FieldByName(dbg->Columns->Items[nIndex]->FieldName)->AsString.c_str());
xPos += 1;
}
}
// 保存Excel文档并退出
ExcelBook.OleProcedure("SaveAs",strXlsFile.c_str());
ExcelBook.OleFunction("Close");
ExcelMain.OleFunction("Quit");
ExcelMain = Unassigned;
ExcelBook = Unassigned;
ExcelPage = Unassigned;
// 工作结束
MessageBox(0, "DBGrid2Excel 转换结束!","DBGrid2Excel", MB_OK | MB_ICONINFORMATION);
return;
}
三、EXCLE2DB:
c++ builder EXCLE2DB 仅仅是一个框架,具体数据格式问题,和过滤检错问题,还要加强。
//*****************************************************************************
//modelname: 从EXCEL文件导入到数据库
//functionname : Excel2ADOQuery
//function : 从EXCEL文件导入到数据库
//input : adoqr, tableName, XlsPath+XlsFil
//output :
//return :
//*****************************************************************************
//attention :
//*****************************************************************************
DWORD WINAPI Excel2ADOQuery(TADOQuery *adoqr,String tableName,String strXlsFile)
{
AnsiString query;
if(FileExists(strXlsFile) == false)
{
MessageBox(0, "请确定EXCLE文件是否存在\n导入途中请勿\n打开EXCLE文件\n否则导入失败","Warning", MB_OK | MB_ICONERROR);
return -1;
}
//定义对象
Variant ExcelMain = Unassigned;
Variant Wb = Unassigned;
Variant Sheet = Unassigned;
//创建对象
try
{
ExcelMain = Variant::CreateObject("Excel.Application");
}
catch(...)
{
MessageBox(0, "启动 Excel 出错, 可能是没有安装Excel.","DBGrid2Excel", MB_OK | MB_ICONERROR);
return -1;
}
//设置Excel为不可见
ExcelMain.OlePropertySet("Visible",false);
ExcelMain.OlePropertyGet("WorkBooks").OleProcedure("Open",strXlsFile.c_str());
Wb = ExcelMain.OlePropertyGet("ActiveWorkBook");
Sheet = Wb.OlePropertyGet("ActiveSheet"); //获得当前默认的Sheet
// 从excel导入数据
//得到工作表的行数和列数
int iCols,iRows; //记录列数和行数
iRows =(int) Sheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count");
iCols =(int) Sheet.OlePropertyGet("UsedRange").OlePropertyGet("Columns").OlePropertyGet("Count");
for(int row=2;row<=iRows;row++)
{
adoqr->Append();
for (int col=1;col<iCols;col++)
{//得到单元格的值
adoqr->Fields->Fields[col]->AsString = Sheet.OlePropertyGet("Cells",row,col).OlePropertyGet("Value");
/*
AnsiString str("");
str=Sheet.OlePropertyGet("Cells",row,col).OlePropertyGet("Value");
ShowMessage(str);
*/
}
}
//更新数据库
adoqr->UpdateBatch();
adoqr->Close();
//退出Excel文档
Wb.OleFunction("Close");
ExcelMain.OleFunction("Quit");
ExcelMain = Unassigned;
Wb = Unassigned;
Sheet = Unassigned;
return 0;
}
//*****************************************************************************
//modelname: 从EXCEL文件导入到数据库
//functionname : Excel2ADOQuery
//function : 从EXCEL文件导入到数据库
//input : adoqr, tableName, XlsPath+XlsFil
//output :
//return :
//*****************************************************************************
//attention :
//*****************************************************************************
DWORD WINAPI Excel2ADOQuery(TADOQuery *adoqr,String tableName,String strXlsFile)
{
AnsiString query;
if(FileExists(strXlsFile) == false)
{
MessageBox(0, "请确定EXCLE文件是否存在\n导入途中请勿\n打开EXCLE文件\n否则导入失败","Warning", MB_OK | MB_ICONERROR);
return -1;
}
//定义对象
Variant ExcelMain = Unassigned;
Variant Wb = Unassigned;
Variant Sheet = Unassigned;
//创建对象
try
{
ExcelMain = Variant::CreateObject("Excel.Application");
}
catch(...)
{
MessageBox(0, "启动 Excel 出错, 可能是没有安装Excel.","DBGrid2Excel", MB_OK | MB_ICONERROR);
return -1;
}
//设置Excel为不可见
ExcelMain.OlePropertySet("Visible",false);
ExcelMain.OlePropertyGet("WorkBooks").OleProcedure("Open",strXlsFile.c_str());
Wb = ExcelMain.OlePropertyGet("ActiveWorkBook");
Sheet = Wb.OlePropertyGet("ActiveSheet"); //获得当前默认的Sheet
// 从excel导入数据
//得到工作表的行数和列数
int iCols,iRows; //记录列数和行数
iRows =(int) Sheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count");
iCols =(int) Sheet.OlePropertyGet("UsedRange").OlePropertyGet("Columns").OlePropertyGet("Count");
for(int row=2;row<=iRows;row++)
{
adoqr->Append();
for (int col=1;col<iCols;col++)
{//得到单元格的值
adoqr->Fields->Fields[col]->AsString = Sheet.OlePropertyGet("Cells",row,col).OlePropertyGet("Value");
/*
AnsiString str("");
str=Sheet.OlePropertyGet("Cells",row,col).OlePropertyGet("Value");
ShowMessage(str);
*/
}
}
//更新数据库
adoqr->UpdateBatch();
adoqr->Close();
//退出Excel文档
Wb.OleFunction("Close");
ExcelMain.OleFunction("Quit");
ExcelMain = Unassigned;
Wb = Unassigned;
Sheet = Unassigned;
return 0;
}
转载:http://hi.baidu.com/msingle/blog/item/185f2b0f711fe32e6059f39a.html
http://hi.baidu.com/msingle/blog/item/2a274e9bd89d53bcc9eaf4a7.html
/*点滴记录程序员的成长历程*/
//--加油吧,年经的人们
//--梦想在召唤!