关于BCB中，Excel的一些操作（存底）

本文介绍如何使用Excel应用程序接口实现Excel文件的操作与自动化处理，包括复制内容及边框样式、按日期创建新的工作表、文件复制及重命名等功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

将一个Excel或sheet的部分内容复制到另一个Excel或sheet，并设置边框

//---------------------------------------------------------------------------

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Variant ex,wb1,wb2,sh1,sh2,ERange,EBorders;

AnsiString temp1;

AnsiString temp2;

AnsiString strRange;

int r = 1,icol = 1, irow = 1, c = 1;

ex=CreateOleObject("Excel.Application");

ex.OlePropertySet("Visible",true);

wb2=ex.OlePropertyGet("Workbooks").OleFunction("Open","c:\\abc.xls") ;

wb1=ex.OlePropertyGet("Workbooks").OleFunction("open","c:\\123.xls");

sh2=wb2.OlePropertyGet("ActiveSheet");

sh1=wb1.OlePropertyGet("ActiveSheet");

temp2=sh2.OlePropertyGet("Cells",7,6).OlePropertyGet("Value");

sh1.OlePropertyGet("Cells",7,6).OlePropertySet("Value",temp2.c_str());

temp1=sh1.OlePropertyGet("Cells",7,6).OlePropertyGet("Value");

Edit1->Text= temp1;

while(irow-20)//for(irow=5;irow<9;irow++)

{

for(icol=1,c=1;icol<10;icol++)

{

temp2=sh2.OlePropertyGet("Cells",r,c++).OlePropertyGet("Value");

sh1.OlePropertyGet("Cells",irow,icol).OlePropertySet("Value",temp2.c_str());

}

strRange = "A"+IntToStr(irow)+":I"+IntToStr(irow);

ERange = sh1.OlePropertyGet("Range",strRange.c_str());

EBorders = ERange.OlePropertyGet("Borders");

EBorders.OlePropertySet("linestyle",xlContinuous);

EBorders.OlePropertySet("weight",xlThin);

EBorders.OlePropertySet("colorindex",xlAutomatic);

irow++;

r++;

}

//---------------------------------------------------------------------------

选择某一个sheet，明重新命名

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Variant ex,wb,newxls,sh;

try

{

ex=CreateOleObject("Excel.Application");//启动Excel

ex.OlePropertySet("Visible",(Variant)true);//使Excel启动后可见

ex.OlePropertyGet("WorkBooks").OleProcedure("ADD",3);//新建一新工作薄(加上这一句，会有两个Excel窗口，同时关闭)

ex.OlePropertySet("Windowstate",1);//Excel启动后窗体状态：1(xlNormal)正常显示(Excel上次关闭时是什么状态，启动后就是什么状态)，2(xlMinimized)最小化(不是缩小到任务栏)，3(xlMaximized)最大化

// newxls=(ex.OlePropertyGet("Workbooks")).OleFunction("Add");//①//使用ExcelApp的Exec方法新建一有3个工作表的默认工作薄

newxls=(ex.OlePropertyGet("Workbooks")).OleFunction("Add",1);//创建有单个工作表的工作簿

newxls=ex.OlePropertyGet("workbooks").OleFunction("open", "c:\\123.xls");//打开已存在的文件，使用时可将上面关于新建①的那行屏蔽掉

//sh=newxls.OlePropertyGet("ActiveSheet");

}

catch(...)

{

ShowMessage("启动Excel出错，可能沒有安裝Excel");

return;

}

sh.OlePropertyGet("Application").OlePropertySet("DisplayAlerts",true); //打开Excel的警告提示，如提示保存等

newxls.OlePropertyGet("Sheets", 2).OleProcedure("Select");//选择第二工作表

//sh = newxls.OlePropertyGet("ActiveSheet");//选择第二工作表

sh.OlePropertySet("Name", "我们的家园");//重命名当前工作表

int nSheetCount=newxls.OlePropertyGet("Sheets").OlePropertyGet("Count");//取得工作表总数

Edit1->Text=nSheetCount;

}

//---------------------------------------------------------------------------

将Excel文件从一个地方复制到另一个地方，并重新命名

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Variant ex,wb,sh;

ex = CreateOleObject("Excel.Application");

ex.OlePropertySet("Visible",true);

wb = ex.OlePropertyGet("WorkBooks").OleFunction("Open","c:\\123.xls");

sh = wb.OlePropertyGet("activesheet");

//sh.OlePropertyGet("Columns",1).OlePropertySet("Value","123456789") ;

sh.OlePropertyGet("Range","A1:A9").OlePropertySet("Value","19") ;

//wb.OleProcedure("SaveAs","c:\\report\\456.xls");

CopyFile("c:\\123456.xls","c:\\report\\1456.xls",true); //将123456.xls复制到report文件夹中并命名为1456.xls

CopyFile("c:\\123456.xls","c:\\report\\2456.xls",false);

AnsiString FN = GetCurrentDir()+"\\report\\456.xls";// C:\Program Files\Borland\CBuilder5\Projects\report\456.xls

Edit1->Text = FN;

if(!FileExists(FN))

{

Application->MessageBox("报表模版文件不存在","错误",MB_ICONSTOP|MB_OK);

return;

}

Application->MessageBox("成功完成","提示",MB_ICONINFORMATION|MB_OK);

CopyFile("c:\\123456.xls",FN.c_str(),false);

}

//---------------------------------------------------------------------------

输入当前时间和日期

//---------------------------------------------------------------------------

void __fastcall TForm1::Button1Click(TObject *Sender)

{

AnsiString WDate,ctime,strPtrDate;

//DateSeparator = '-';

//ShortDateFormat = "yyyy/m/d";

WDate = DateToStr(Date());

ctime = TimeToStr(Time());

AnsiString strYear = strPtrDate.SubString(1,4);

strPtrDate = strPtrDate.SubString(6,strPtrDate.Length()-5);

AnsiString strMonth = strPtrDate.SubString(1,strPtrDate.Pos("-")-1);

AnsiString strDay = strPtrDate.SubString(strPtrDate.Pos("-")+1, strPtrDate.Length()- strPtrDate.Pos("-"));

strPtrDate = strYear+"年"+strMonth+"月"+strDay+"日";

Edit3->Text=strPtrDate;

Edit1->Text = WDate;

Edit2->Text = ctime;

}

//---------------------------------------------------------------------------

方法一

//---------------------------------------------------------------------------

#include <vcl.h>

#pragma hdrstop

#include "sheet_datename_simple.h"

//---------------------------------------------------------------------------

#pragma package(smart_init)

#pragma resource "*.dfm"

#include <Utilcls.h>

#include "Excel_2K_SRVR.h"

#include "ComObj.hpp"

TForm1 *Form1;

//---------------------------------------------------------------------------

__fastcall TForm1::TForm1(TComponent* Owner)

: TForm(Owner)

{

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Variant ex,wb,sh;

try

{

ex = CreateOleObject("Excel.Application");

}

catch(...)

{

Application->MessageBox("无法启动Excel","错误",MB_ICONSTOP|MB_OK);

return;

}

ex.OlePropertySet("Visible",false);

AnsiString NDate;

DateSeparator = '-';

NDate = DateToStr(Date());

AnsiString DateYear = NDate.SubString(1,4);

NDate = NDate.SubString(6,NDate.Length()-5);

AnsiString DateMonth = NDate.SubString(1,NDate.Pos("-")-1);

AnsiString DateDay = NDate.SubString(NDate.Pos("-")+1,NDate.Length()-NDate.Pos("-"));

NDate = DateMonth+"月"+DateDay+"日";//sheet的名字

AnsiString TDate = DateMonth+"月"+DateDay+"日报表";//标题

AnsiString Direc = "c:\\"+DateMonth+"月.xls";//路径

if(FileExists(Direc))

{

wb = ex.OlePropertyGet("workbooks").OleFunction("open",Direc.c_str());

sh = wb.OlePropertyGet("Activesheet");

AnsiString Na = sh.OlePropertyGet("Name");

if(Na!=NDate)

{

Variant bef1,aft1;

int count = wb.OlePropertyGet("sheets").OlePropertyGet("count");

aft1=wb.OlePropertyGet("sheets",count);

wb.OlePropertyGet("sheets").OleProcedure("Add",bef1.NoParam(),aft1);

sh = wb.OlePropertyGet("Activesheet");

sh.OlePropertySet("Name",NDate.c_str());

sh.OlePropertyGet("Cells",1,1).OlePropertySet("Value",TDate.c_str());

sh.OlePropertyGet("Cells",2,1).OlePropertySet("Value","列名1");

sh.OlePropertyGet("Cells",2,2).OlePropertySet("Value","列名2");

sh.OlePropertyGet("Cells",2,3).OlePropertySet("Value","列名3");

sh.OlePropertyGet("Cells",2,4).OlePropertySet("Value","列名4");

sh.OlePropertyGet("Cells",2,5).OlePropertySet("Value","列名5");

sh.OlePropertyGet("Cells",2,6).OlePropertySet("Value","列名6");

sh.OlePropertyGet("Cells",2,7).OlePropertySet("Value","列名7");

}

else

{

wb = ex.OlePropertyGet("workbooks").OleFunction("Add",1);

wb.OleFunction("SaveAs",Direc.c_str());