将一个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());
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");
}
wb.OleProcedure("Save"); //保存表格
wb.OleProcedure("Close"); //关闭表格
ex.OleFunction("Quit"); //退出Excel
}
//---------------------------------------------------------------------------
方法二:
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "sheet_name_date_1.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");//启动Excel
ex.OlePropertySet("Visible",(Variant)false); //使Excel启动后可见
}
catch(...)
{
Application->MessageBox("无法启动Excel!","错误",MB_ICONSTOP|MB_OK);
}
AnsiString WDate;
DateSeparator = '-';
ShortDateFormat = "yyyy/m/d";
WDate = DateToStr(Date()); //取当前日期
AnsiString DateYear = WDate.SubString(1,4);
WDate = WDate.SubString(6,WDate.Length()-5);
AnsiString DateMonth = WDate.SubString(1,WDate.Pos("-")-1);
AnsiString DateDay = WDate.SubString(WDate.Pos("-")+1,WDate.Length()-WDate.Pos("-"));
WDate = DateMonth + "月" + DateDay + "日"; //sheet的名字
AnsiString EDate = DateMonth+"月"+DateDay+"日报表"; //报表标题
AnsiString Direc = "c:\\" + DateMonth +"月.xls"; //报表路径
if(DateDay=="1")
{
if(FileExists(Direc))
{
wb = ex.OlePropertyGet("workbooks").OleFunction("open",Direc.c_str());//打开本月报表工作簿
sh = wb.OlePropertyGet("Activesheet");
return;
}
else
{
wb=ex.OlePropertyGet("workbooks").OleFunction("Add",1); //建立新的报表工作簿
wb.OleFunction("SaveAs",Direc.c_str());
sh = wb.OlePropertyGet("Activesheet");
sh.OlePropertySet("Name",WDate.c_str());
sh.OlePropertyGet("cells",1,1).OlePropertySet("Value",EDate.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
{
if(FileExists(Direc))
{
wb = ex.OlePropertyGet("workbooks").OleFunction("open",Direc.c_str());//打开本月报表工作簿
sh = wb.OlePropertyGet("Activesheet");
AnsiString EName = sh.OlePropertyGet("Name");
if(EName!=WDate)
{ Variant bef1,aft1;
int icount = wb.OlePropertyGet("sheets").OlePropertyGet("count");
aft1 = wb.OlePropertyGet("sheets",icount);
wb.OlePropertyGet("sheets").OleProcedure("Add",bef1.NoParam(),aft1);
sh = wb.OlePropertyGet("Activesheet");
sh.OlePropertySet("Name",WDate.c_str());
sh.OlePropertyGet("cells",1,1).OlePropertySet("Value",EDate.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
return;
}
else
{
wb=ex.OlePropertyGet("workbooks").OleFunction("Add",1); //建立新的报表工作簿
wb.OleFunction("SaveAs",Direc.c_str());
sh = wb.OlePropertyGet("Activesheet");
sh.OlePropertySet("Name",WDate.c_str());
sh.OlePropertyGet("cells",1,1).OlePropertySet("Value",EDate.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");
}
}
wb.OleProcedure("Save"); //保存表格
wb.OleProcedure("Close"); //关闭表格
ex.OleFunction("Quit"); //退出Excel
}
//---------------------------------------------------------------------------
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "sheet_name_date.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;
ex = CreateOleObject("Excel.Application");//启动Excel
ex.OlePropertySet("Visible",(Variant)true); //使Excel启动后可见
AnsiString WDate;
DateSeparator = '-';
ShortDateFormat = "yyyy/m/d";
WDate = DateToStr(Date()); //取当前日期
AnsiString DateYear = WDate.SubString(1,4);
WDate = WDate.SubString(6,WDate.Length()-5);
AnsiString DateMonth = WDate.SubString(1,WDate.Pos("-")-1);
AnsiString DateDay = WDate.SubString(WDate.Pos("-")+1,WDate.Length()-WDate.Pos("-"));
WDate = DateMonth + "月" + DateDay + "日";
AnsiString EDate = DateMonth+"月"+DateDay+"报表"; //报表标题
AnsiString Direc = "c:\\" + DateMonth +".xls"; //报表路径
if(DateDay=="1")
{
wb=ex.OlePropertyGet("workbooks").OleFunction("Add",1); //建立新的报表工作簿
wb.OleFunction("SaveAs",Direc.c_str());
}
else
{
try
{
wb = ex.OleFunction("Open",Direc.c_str()); //打开本月报表工作簿
}
catch(...)
{
wb=ex.OlePropertyGet("workbooks").OleFunction("Add",1); //建立新的报表工作簿
wb.OleFunction("SaveAs",Direc.c_str());
}
}
int icount = wb.OlePropertyGet("sheets").OlePropertyGet("count");
if(icount==1)
sh = wb.OlePropertyGet("Activesheet");
else
{
Variant bef1;
wb.OlePropertyGet("sheets").OleProcedure("Add",bef1.NoParam(),icount);
sh = wb.OlePropertyGet("Activesheet");
}
AnsiString EName = sh.OlePropertyGet("Name");
if(EName!=WDate)
{
sh.OlePropertySet("Name",WDate.c_str());
sh.OlePropertyGet("cells",1,1).OlePropertySet("Value",EDate.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
return;
}
//---------------------------------------------------------------------------