将DBGrid里的数据导入EXCEL的,分享一下:
void __fastcall Tsearch::DBGridToExcel(TDBGrid *AGrid, AnsiString Title) //从DBGrid导出到Excel
{
if (AGrid->DataSource->DataSet->IsEmpty())
{
ShowMessage("没有数据需要导出");
}
else
{
AGrid->DataSource->DataSet->DisableControls();
TBookmark bmkDataSet = AGrid->DataSource->DataSet->GetBookmark();
try
{
try
{
Variant vExcel = CreateOleObject("Excel.Application");
vExcel.OlePropertySet("Visible",false);
Variant vWorkbooks = vExcel.OlePropertyGet("Workbooks");
Variant vWorkbook = vWorkbooks.OleFunction("Add");
Variant vSheets = vWorkbook.OlePropertyGet("Sheets");
Variant vCells = vExcel.OlePropertyGet("Cells");
int k = 0;
for(int i = 0; i < AGrid->Columns->Count; i++)
{ //本循环输出标题
if (AGrid->Columns->Items[i]->Visible)
{
k++;
vCells.OlePropertyGet("Cells",1,k).OlePropertySet("Value",AGrid->Columns->Items[i]->Title->Caption.c_str() );
//vSheets.OlePropertyGet("Columns", k).OlePropertyGet("ColumnWidth").OlePropertySet("Value",
// AGrid->Fields[k - 1]->DisplayWidth);
}
}
AGrid->DataSource->DataSet->First();
Variant vText=VarArrayCreate(OPENARRAY(int,
(1, AGrid->DataSource->DataSet->RecordCount,
1, AGrid->Columns->Count)), varVariant);
int iRow=1;
while (!AGrid->DataSource->DataSet->Eof)
{
int iCol = 0;
for (int j=1;j<=AGrid->Columns->Count;j++)
{
if (AGrid->Columns->Items[j-1]->Visible)
{
iCol++;
//vText.GetElement(iRow,iCol).ChangeType(varString);
if(AGrid->Columns->Items[j-1]->Field->DataType==ftString) //如果是字符原样输出,利用LOTUS '符
vText.PutElement("'"+AGrid->Columns->Items[j-1]->Field->DisplayText,iRow,iCol);
else
vText.PutElement(AGrid->Columns->Items[j-1]->Field->DisplayText,iRow,iCol);
//vCells.OlePropertyGet("Cells",iRow,iCol).OlePropertySet("NumberFormatLocal","@");
}
}
iRow ++;
Application->ProcessMessages();
AGrid->DataSource->DataSet->Next();
}
vCells.OlePropertyGet("Range", vCells.OlePropertyGet("Item",2,1),
vCells.OlePropertyGet("Item",AGrid->DataSource->DataSet->RecordCount+1,AGrid->Columns->Count)).OlePropertySet("Value",vText);
vExcel.OlePropertySet("Visible", true);
}
catch(Exception &E)
{
ShowMessage(E.Message+"/n可能是系统没有安装Excel");
//Application->MessageBox("系统没有安装Excel", Application->Title.c_str(), MB_ICONINFORMATION);
}
}
__finally
{
if(bmkDataSet)
{
AGrid->DataSource->DataSet->GotoBookmark(bmkDataSet);
AGrid->DataSource->DataSet->FreeBookmark(bmkDataSet);
}
AGrid->DataSource->DataSet->EnableControls();
}
}
}
把查询到的数据库记录导出到excel文件里面
#include <FileCtrl.hpp>
#pragma hdrstop
#include<Comobj.hpp>
#include<Utilcls.h>
void __fastcall TForm1::BitBtn1Click(TObject *Sender)
{
AnsiString SQLTxt,str;
SQLTxt = "select * from ovf_dun_result order by id";
Query1->Close();
Query1->SQL->Clear();
Query1->SQL->Add(SQLTxt);
Query1->Open();
if(Query1->RecordCount == 0) //无数据退出
return;
Variant ex,newxls,cellms; //ole万能变量,定义excel对象使用
try
{
ex=CreateOleObject("Excel.Application"); //启动Excel
}
catch(...)
{
MessageDlg("无法启动Excel,可能尚未安装或文件已经损坏!",mtError,TMsgDlgButtons()<<mbYes,0);
return;
}
newxls=ex.OlePropertyGet("ActiveWorkBook");//创建工作簿对象
ex.OlePropertyGet("WorkBooks").OleFunction("Add");//添加一个工作薄
cellms=ex.OlePropertyGet("ActiveSheet");//创建工作区
cellms.OlePropertyGet("Cells",1,1).OlePropertySet("Value","催收方式");
cellms.OlePropertyGet("Cells",1,2).OlePropertySet("Value","代码");
cellms.OlePropertyGet("Cells",1,3).OlePropertySet("Value","备注");
ex.OlePropertySet("Visible",(Variant)true); //使Excel可见
Query1->First();
for(int i=0;i<Query1->RecordCount;i++)//按列灌入纪录
{
for(int j=0;j<Query1->FieldCount;j++) //行的值
{
str = Query1->Fields->Fields[j]->AsString;
cellms.OlePropertyGet("Cells",i+2, j+1).OlePropertySet("Value", str.c_str());
}
Query1->Next();
}
}
//---------------------------------------------------------------------------