在C++ Builder里如何使用ExcelOLEObject.有例子最好

Answer 1

下面是一个完整可用的例子，我从我的代码里面拿出来的，希望对你有帮助！

void __fastcall TBaoGaoDaoChuBiaoForm::Button5Click(TObject *Sender)
{
AnsiString ff[52]={"A","B","C","D","E","F","G",
"H","I","J","K","L","M","N",
"O","P","Q","R","S","T","U",
"V","W","X","Y","Z","AA","AB",
"AC","AD","AE","AF","AG","AH",
"AI","AJ","AK","AL","AM","AN",
"AO","AP","AQ","AR","AS","AT",
"AU","AV","AW","AX","AY","AZ"
};
MessageBox(NULL,"准备将数据库中的数据导出为Excel文件\n导出时间视您的机器配置情况而异\n在此期间，你的系统可能会暂时处于无响应状态，请耐心等候\n单击确定开始导出操作","提示",64+MB_TOPMOST);
int i;
TDateTime DT;
unsigned short Year,Month,Day;
AnsiString ntime,nYear,nMonth,nDay,t1,t2;
ntime=DateTimePicker1->Date.DateString();
DT=StrToDateTime(ntime);
DT.DecodeDate(&Year,&Month,&Day);
nYear = IntToStr(Year);
if(Month<10)
nMonth = "0"+IntToStr(Month);
else
nMonth =IntToStr(Month);
if(Day<10)
nDay = "0"+IntToStr(Day);
else
nDay = IntToStr(Day);
t1=nYear+"年"+nMonth+"月"+nDay+"日";

ntime=DateTimePicker2->Date.DateString();
DT=StrToDateTime(ntime);
DT.DecodeDate(&Year,&Month,&Day);
nYear = IntToStr(Year);
if(Month<10)
nMonth = "0"+IntToStr(Month);
else
nMonth =IntToStr(Month);
if(Day<10)
nDay = "0"+IntToStr(Day);
else
nDay = IntToStr(Day);
t2=nYear+"年"+nMonth+"月"+nDay+"日";

int n;
AnsiString mysql,ss[100];
mysql="select * from BaoGaokGuanLi Where 检测时间 between '"+t1+"' and '"+t2+"'";
n=ListBox2->Items->Count;
for(i=0;i<n;i++)
ss[i]=ListBox2->Items->Strings[i];
try
{
Button5->Enabled = false;
Button6->Enabled = false;//使两个按钮无效
//file://取报表文件CardSend.xls的完整目录名
AnsiString ExcelFileName = GetCurrentDir()+"\\trpt\\YiChuBaoGao.xls";
if(!FileExists(ExcelFileName))
{
Application->MessageBox("报表模板文件不存在,无法打开！","错误",MB_ICONSTOP|MB_OK);
return;
}
//file://建立Excel的Ole对象Ex
try
{
Ex = Variant::CreateObject("Excel.Application");
}
catch(...)
{
Application->MessageBox("无法启动Excel","错误",MB_ICONSTOP|MB_OK);
return;
}
//file://设置Excel为不可见
Ex.OlePropertySet("Visible",false);
//file://打开指定的Excel报表文件。报表文件中最好设定只有一个Sheet。
Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",ExcelFileName.c_str());
Wb = Ex.OlePropertyGet("ActiveWorkBook");
Sheet = Wb.OlePropertyGet("ActiveSheet");//获得当前默认的Sheet
//file://清空Excel表，这里是用循环清空到第300行。对于一般的表格已经足够了。
AnsiString strRowTemp;
AnsiString strRange;
int iCols,iRows;//记录列数和行数

//从第三行开始，到第300行止。一般第一行是表标题，第二行是副标题或者制表日期。
for(iRows=1;iRows<1500;iRows++)
{ //file://假设只有6列。
for (iCols = 1;iCols < n+1; iCols++)
{
//file://清空行
Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value","");
}
//file://去掉表格边框
strRange = "A"+IntToStr(iRows)+":"+ff[n-1]+IntToStr(iRows);//获取操作范围
ERange = Sheet.OlePropertyGet("Range",strRange.c_str());
EBorders = ERange.OlePropertyGet("Borders");//获取边框对象
EBorders.OlePropertySet("linestyle",xlNone);
}

AnsiString strPtrDate,strYear,strMonth,strDay; //file://存放当前日期，作为制表日期
DateSeparator = '-';
ShortDateFormat = "yyyy/m/d";//设置为年/月/日格式
strPtrDate = DateToStr(Date());//取当前日期
if(strPtrDate.SubString(3,1)=="-")
{
strYear = "20"+strPtrDate.SubString(1,2);
strPtrDate = strPtrDate.SubString(6,strPtrDate.Length()-3);
strMonth = strPtrDate.SubString(1,strPtrDate.Pos("-")-1);
strDay =strPtrDate.SubString(strPtrDate.Pos("-")+1,strPtrDate.Length()-strPtrDate.Pos("-"));
}
else
{
strYear = strPtrDate.SubString(1,4);
strPtrDate = strPtrDate.SubString(6,strPtrDate.Length()-5);
strMonth = strPtrDate.SubString(1,strPtrDate.Pos("-")-1);
strDay =strPtrDate.SubString(strPtrDate.Pos("-")+1,strPtrDate.Length()-strPtrDate.Pos("-"));
}strPtrDate = strYear+"年"+strMonth+"月"+strDay+"日";
AnsiString strData = "库存报告清单";//报表标题
//file://将报表标题置于第一行第一列。在此之前，应将报表文件的标题格式设定好。
Sheet.OlePropertyGet("Cells",1,1).OlePropertySet("Value",strData.c_str());
//file://将制表日期置于表格第二行的右侧。
Sheet.OlePropertyGet("Cells",2,n).OlePropertySet("Value",strPtrDate.c_str());
iRows = 3;//在第三行放置表格的列名
for(i=0;i<n;i++)
Sheet.OlePropertyGet("Cells",iRows,i+1).OlePropertySet("Value",ss[i].c_str());
//file://画表格边框，在A3：F3之间取范围
strRange = "A"+IntToStr(iRows)+":"+ff[n-1]+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://从数据库中取数据(略)，假设数据集放入Query1中。
ADOQuery1->Close();
ADOQuery1->SQL->Clear();
ADOQuery1->SQL->Add(mysql);
ADOQuery1->Open();//打开数据集
//file://循环取数
while(!ADOQuery1->Eof)
{
//file://循环取字段的数据放到Excel表对应的行列中
for(iCols=1;iCols<n+1;iCols++)
{
strRowTemp = ADOQuery1->FieldByName(ss[iCols-1])->AsString;
Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value",strRowTemp.c_str());
}
//file://画该行的表格边框
strRange = "A"+IntToStr(iRows)+":"+ff[n-1]+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++;
ADOQuery1->Next();
}//while结束
Wb.OleProcedure("Save");//保存表格
Wb.OleProcedure("Close");//关闭表格
Ex.OleFunction("Quit");//退出Excel
//file://定义目标文件名
AnsiString DestinationFile = GetCurrentDir()+"\\report\\YiChuBaoGao.xls";
//file://将刚刚修改的Excel表格文件table.xls拷贝到report目录下
if(!CopyFile(ExcelFileName.c_str(),DestinationFile.c_str(),false))
{
Application->MessageBox("复制文件操作失败,Excel文件可能正在使用中！","错误",MB_ICONSTOP|MB_OK);
return;
}

Application->MessageBox("成功完成报表保存！\n可以按\'查看Excel数据表\'按钮进行报表工作","提示",MB_ICONINFORMATION|MB_OK);
Button6->Enabled = true;
Button5->Enabled=true;
}//try结束
catch(...)
{
Application->MessageBox("操作Excel表格失败！","错误",MB_ICONSTOP|MB_OK);
Wb.OleProcedure("Close");
Ex.OleFunction("Quit");
Button5->Enabled = true;
Button6->Enabled=false;
}
}
//---------------------------------------------------------------------------
void __fastcall TBaoGaoDaoChuBiaoForm::Button6Click(TObject *Sender)
{
try
{
//file://指定report目录下的报表文件用于用户操作
AnsiString ExcelFileName = GetCurrentDir()+"\\report\\YiChuBaoGao.xls";
if(!FileExists(ExcelFileName))
{
Application->MessageBox("Excel表文件不存在,无法打开！","错误",MB_ICONSTOP|MB_OK);
return;
}
try
{
Ex = Variant::CreateObject("Excel.Application");
}
catch(...)
{
Application->MessageBox("无法启动Excel","错误",MB_ICONSTOP|MB_OK);
return;
}
//file://使Excel可见
Ex.OlePropertySet("Visible",true);
//file://打开Excel表格文件Table.xls
Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",ExcelFileName.c_str());
}
catch(...)
{
Application->MessageBox("操作Excel表格错误！","错误",MB_ICONSTOP|MB_OK);
Ex.OleFunction("Quit");
}
}