/************************************************************************************
* C++ Buider 利用变体类Variant提供的4个方法向OLE服务程序提交操作命令 *
* Variant Variant::OlePropertyGet(属性名,参数。。。); // 获取对象属性 *
* Variant Variant::OlePropertySet(属性名,参数。。。); // 设置对象属性 *
* Variant Variant::OleFunction(函数名,参数。。。); // 运行对象的函数 *
* Variant Variant::OleProcedure(过程名,参数。。。); // 获取对象 *
*************************************************************************************/
#include <vcl.h>
#pragma link "Excel_2K_SRVR"
#include "Excel_2K_SRVR.h"
#pragma hdrstop
#include "UnitMain.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::btn_LoadExcelClick(TObject *Sender)
{
String strFileName = GetCurrentDir() + "//Import.xls";
if( !FileExists(strFileName)){
MessageBox(Handle,"Excel表文件Import.xls不存在,无法打开","信息提示",MB_OK);
return;
}
Variant ExcelApp; // 对象
Variant WorkBook; // 工作薄
Variant WorkSheet; // 工作表
try{
ExcelApp = Variant::CreateObject("Excel.Application");
}catch(...){
MessageBox(Handle,"无法启动Excel!","信息提示",MB_OK);
return;
}
ExcelApp.OlePropertySet("Visible",true); // 设置Excel为可视
ExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open",strFileName.c_str()); // 打开指定的Excel文件,文件最好设置一个sheet
WorkBook = ExcelApp.OlePropertyGet("ActiveWorkBook");
WorkSheet = WorkBook.OlePropertyGet("ActiveSheet"); // 获得当前默认的sheet
// 当前的Excel文件导入程序
int nRow = WorkSheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count");
int nCol = WorkSheet.OlePropertyGet("UsedRange").OlePropertyGet("Columns").OlePropertyGet("Count");
// 从Excel表回去数据
for(int i=5; i<=nRow; i++){
for(int j=1;j<=nCol;j++){
String Str;
Str=WorkSheet.OlePropertyGet("Cells",i,j).OlePropertyGet("Value");
StringGrid1->Cells[i-5][j-1]=Str;
}
}
WorkBook.OleProcedure("Close"); // 关闭表格
ExcelApp.OleFunction("Quit"); // 退出Excel
}
//---------------------------------------------------------------------------
void __fastcall TForm1::btn_SaveExcelClick(TObject *Sender)
{
String strFileName = GetCurrentDir() + "//Export.xls";
if( !FileExists(strFileName)){
MessageBox(Handle,"Excel表文件Import.xls不存在,无法打开","信息提示",MB_OK);
return;
}
Variant ExcelApp; // 对象
Variant WorkBook; // 工作薄
Variant WorkSheet; // 工作表
Variant Range; // 范围
Variant Borders; // 边框
try{
ExcelApp = Variant::CreateObject("Excel.Application");
}catch(...){
MessageBox(Handle,"无法启动Excel!","信息提示",MB_OK);
return;
}
ExcelApp.OlePropertySet("Visible",false); // 设置Excel为可视
ExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open",strFileName.c_str()); // 打开指定的Excel文件
WorkBook = ExcelApp.OlePropertyGet("ActiveWorkBook");
WorkSheet = WorkBook.OlePropertyGet("ActiveSheet"); // 获得当前默认的sheet
// 清空EXCEL表的数据
int nRow = WorkSheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count");
int nCol = WorkSheet.OlePropertyGet("UsedRange").OlePropertyGet("Columns").OlePropertyGet("Count");
for(int i=5; i<=nRow; i++)
WorkSheet.OlePropertyGet("Rows",i).OlePropertySet("Value","");
// 给Excel表赋值
for(int j=1; j<10; j++){
for(int i=1;i<10;i++){
String Str = IntToStr(i*j);
WorkSheet.OlePropertyGet("Cells",i+4,j).OlePropertySet("Value",Str.c_str());
}
}
String StrRange = "A"+IntToStr(nCol)+":I"+IntToStr(nRow); // 获取操作范围
Range = WorkSheet.OlePropertyGet("Range",StrRange.c_str());
Borders = Range.OlePropertyGet("Borders"); // 获取边框对象
//Borders = Range.OlePropertySet("linestyle",xlNone); // 去掉边框线
Borders.OlePropertySet("linestyle",xlContinuous); // 画表格边框
Borders.OlePropertySet("weight",xlThin);
Borders.OlePropertySet("Colorindex",xlAutomatic);
WorkBook.OleProcedure("Save"); // 保存表格
WorkBook.OleProcedure("Close"); // 关闭表格
ExcelApp.OleFunction("Quit"); // 退出Excel
char StrCurDir[MAX_PATH];
GetCurrentDirectory(MAX_PATH,StrCurDir);
char exeStr[] = "Export.xls";
SHELLEXECUTEINFO info;
memset(&info,0,sizeof(info));
info.cbSize = (BYTE)"Open";
info.lpFile = exeStr;
info.lpParameters = NULL;
info.fMask = SEE_MASK_NOCLOSEPROCESS;
info.nShow = SW_SHOWDEFAULT;
if(!ShellExecuteEx(&info))
MessageBox(Handle,"打开Excel文件Export.xls失败","信息提示",MB_OK);
SetCurrentDirectory(StrCurDir);
}
//---------------------------------------------------------------------------
void __fastcall TForm1::btn_CloseClick(TObject *Sender)
{
Close();
}
//---------------------------------------------------------------------------