作者：乔山办公网日期：
返回目录：excel表格制作
PB中操作Excel的技巧集
eole=CREATEOBJECT("Excel.application")
2．添加新工作簿
eole.Workbooks.add
3．设置第3个工作表为激活工作表
eole.Worksheets("sheet3").Activate
4．打开指定工作簿
eole.Workbooks.Open("c:\temp\ll.xls")
5．显示Excel窗口
eole.visible=True
6．更改Excel标题栏
eole.Caption=″VFP应用程序调用Microsoft Excel″
7．给单元格赋值
eole.cells(1,4).value=XM(XM为数据库字段名)
8．设置指定列的宽度(单位：字符个数)
eole.ActiveSheet.Columns(1).ColumnWidth=5
9．设置指定行的高度(单位：磅)
eole.ActiveSheet.Rows(1).RowHeight=1/0.035 (设定行高为1厘米，1磅=0.035厘米)
10．在第18行之前插入分页符
eole.Worksheets(″Sheet1″).Rows(18).PageBreak=1
11．在第4列之前删除分页符
eole.ActiveSheet.Columns(4).PageBreak=0
12．指定边框线宽度(Borders参数如下)
ole.ActiveSheet.Range(″b3:d3″).Borders(2).Weight=3
13．设置四个边框线条的类型
eole.ActiveSheet.Range(″b3:d3″).Borders(2).LineStyle=1
(其中Borders参数：1－左、2－右、3－顶、4－底、5－斜、6－斜/；LineStyle值：1与7－细实、2－细虚、4－点虚、9－双e799bee5baa6e79fa5e98193e78988e69d83336细实线)
14．设置页眉
eole.ActiveSheet.PageSetup.CenterHeader=″报表1″
15．设置页脚
eole.ActiveSheet.PageSetup.CenterFooter=″第＆P页″
16．设置页眉到顶端边距为2厘米
eole.ActiveSheet.PageSetup.HeaderMargin=2/0.035
17．设置页脚到底边距为3厘米
eole.ActiveSheet.PageSetup.FooterMargin=3/0.035
下面是一个完整可用的例子，我从我的代码里面拿出来的，希望对你有帮助！
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;//记录列数e79fa5e98193e59b9ee7ad94365和行数
//从第三行开始，到第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");
}
}
在Project-Options-Packages里度UnCheck掉"Build with runtime packages"。问
在Project-Options-Linker里UnCheck掉"Use dynamic RTL"。
在Project-Options-Compile里按一下"Release"按钮。答
只要这三个就行回了，重新答编译就可以了。
下面是一个完整可用的例子，我从我的代码里面拿出来的，希望对你有帮助！
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("复制文7a686964616fe78988e69d83361件操作失败,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");
}
}