|
개발 목적: MS Access MDB 데이터를 ADO로 Spread에 검색할 수 있도록 뿌려주고, 데이터를 Excel로 Import
또는 Export 하려함
개발 환경: WinXP, Borland5.0, Far Point Spread, ChartFX
문제점: 디버그 모드에서 실행시 (borland에서 컴파일후 실행시)에는 오류가 발생하지 않지만,
실행 파일을 직접 실행시 다음과 같은 오류 메시지가 나오고 정상 작동하지 않음.
메시지 : Invalid valiant operation.
문제의 함수 :
다음의 OpenFileXSLClick 내의 GetExcelSheetList함수 실행후 이상해짐
void __fastcall TDasLoaderLogAnalysis_Main::OpenFileXSLClick(
TObject *Sender)
{
AnsiString str = "";
OpenDialog2->Title="Import MS Office Excel";
if(OpenDialog2->Execute()){
TVariant varr,varg;
SAFEARRAYBOUND rgsabound[1];
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = 4;
bool y , z;
long lg = 0, count=0;
short x, listcount,handle;
VariantClear(&varr);
VariantInit(&varr);
VariantClear(&varg);
VariantInit(&varg);
varg.vt = VT_BSTR;
varr.vt = VT_ARRAY | VT_BSTR;
varr.parray = SafeArrayCreate(VT_BSTR,1,rgsabound);
// Check if file is an Excel file and set result to x
x = vaSpread1->IsExcelFile(WideString(DasLoaderLogAnalysis_Main->OpenDialog2->FileName));
// If file is Excel file, tell user, import sheet
// list, and set result to y
if (x==1)
{
//ShowMessage("File is an Excel file.");
y = vaSpread1->GetExcelSheetList(WideString( DasLoaderLogAnalysis_Main->OpenDialog2->FileName), varr, &listcount, WideString(""), &handle, TRUE);
// If received sheet list, tell user, import file,
// and set result to z
if (y)
{
//ShowMessage("Got sheet list.");
//HRESULT hresult =
SafeArrayGetElement(varr.parray, &lg, &varg.bstrVal);
VariantInit(&varr);
VariantInit(&varg);
z = vaSpread1->ImportExcelSheet(handle, 0);
// Tell user result based on T/F value of z
if (z) {
//ShowMessage("Import complete.");
TabSheet1->Caption = "Excel Data";
PageControl1->ActivePage = TabSheet1;
vaSpread1->MaxCols = 8;
vaSpread1->Col = 1;
if(vaSpread1->MaxRows > 0){
while(++count < vaSpread1->MaxRows){
vaSpread1->Row = count;
AnsiString RowValue = vaSpread1->Value;
if( RowValue == ""){
break;
}
}
vaSpread1->MaxRows = --count;
Export->Enabled = false;
Import->Enabled = true;
Delete->Enabled = true;
TabSheet2->TabVisible = false;
TabSheet3->TabVisible = false;
TabSheet4->TabVisible = false;
TabSheet5->TabVisible = false;
}
}
else
ShowMessage("Import did not succeed.");
}
else
// Tell user cannot obtain sheet list
ShowMessage("Cannot return information for Excel file.");
}
else
// Tell user file is not Excel file or is locked
ShowMessage("File is not an Excel file or is locked and cannot be imported.");
}
}
//---------------------------------------------------------------------------
위 코드 수행후
문제의 메시지는 다음 코드 실행시 발생됨:
//---------------------------------------------------------------------------
// Query의 결과를 Spread Header에(Title) 뿌려 주는 함수 ReadField 함수로 각각의 cell의 값을 저장함
int __fastcall TDasLoaderLogAnalysis_Main::FQuery(TvaSpread *Spread,AnsiString Query)
{
FieldsBr = Unassigned;
RecordSetDB = Unassigned;
VariantClear(RecordSetDB);
VariantInit(RecordSetDB);
try{
if (RecordSetDB.IsEmpty())
{
RecordSetDB=Variant::CreateObject("ADODB.Recordset");
}
else
{
RecordSetDB=GetActiveOleObject("ADODB.Recordset");
}
if (RecordSetDB.IsEmpty())
{
ShowMessage("Unable to find ADODB-Recordset.");
RecordSetDB = Unassigned;
return 0;
}
}
catch(EOleSysError &err){
Application->MessageBox("Could not create Recordset object",err.Message.c_str(),MB_OK);
Application->Terminate();
RecordSetDB = Unassigned;
return 0;
}
if(RecordSetDB.IsNull()){
ShowMessage("Could not connect to ADO");
Application->Terminate();
RecordSetDB = Unassigned;
return 0;
}
RecordSetDB.Exec(PropertySet("CacheSize") << 50);
RecordSetDB.Exec(PropertySet("Source") << Query);
RecordSetDB.Exec(PropertySet("ActiveConnection") << ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + MDB));
RecordSetDB.Exec(PropertySet("CursorType") << adOpenKeyset);
RecordSetDB.Exec(PropertySet("LockType") << adLockOptimistic);
try{
RecordSetDB.Exec(Function("Open"));
}
catch(EOleSysError &err){
Application->MessageBox(("Could not open - " + Query).c_str(),err.Message.c_str(),MB_OK);
RecordSetDB = Unassigned;
return 0;
}
Variant Fields = RecordSetDB.Exec(PropertyGet("Fields"));
Variant Count = Fields.Exec(PropertyGet("Count"));
Variant RC = RecordSetDB.Exec(Function("RecordCount"));
int RecordCount=RC;
if(!RecordCount){
return 2;
}
if(RecordSetDB.Exec(PropertyGet("EOF"))){
ShowMessage("No correct records");
RecordSetDB = Unassigned;
Fields = Unassigned;
Count = Unassigned;
RC = Unassigned;
return 0;
}
FieldsBr=RecordSetDB.Exec(PropertyGet("Fields"));
int count=FieldsBr.Exec(PropertyGet("Count"));
for(int inum=0;inum < count; ++inum)
{
Variant Field=FieldsBr.Exec(Function("Item") << inum);
AnsiString Text1;
Text1 = Field.Exec(PropertyGet("Name")); //테이블의 필드명을 Column Header에 뿌려준다
Spread->Row = 0;
Spread->Col = inum+1;
Spread->Text = Text1;
}
Fields = Unassigned;
Count = Unassigned;
RC = Unassigned;
return 1;
}
//---------------------------------------------------------------------------
Borland와 VC++간에 Valiant 선언이 달라 이같은 문제가 발생되리라 생각됨.
ado 를 사용하면서 FarPoint Spread에 Excel Import 해보신 분 조언 부탁합니다.
|
