控件方式与OLE方式同时存在时报错了
用BCB操作EXCEL,网上都介绍两种方法,一是使用EXCEL控件,二是使用OLE。我需要做MDB转EXCEL,用的EXCEL控件方式能够把数据都转过来了,但是图片没能对齐,于是用OLE方式实现了图片对齐。
结果这两套代码能在一起通过编译,但运行了就报错,除非注释掉一套中的修改、写入EXCEL的代码。
怎么解决这个问题?
代码如下:
void __fastcall TForm1::Button2Click(TObject *Sender)
{
Variant vExcelApp, vPic, vSheet, vShapeRange;
vExcelApp = Variant::CreateObject("Excel.Application");
// 使其可视
vExcelApp.OlePropertySet("Visible", true);
// 打开一个Excel文档
vExcelApp.OlePropertyGet("workbooks").OleFunction("open", "d:\\2.xls");
// 获取图像总数
int nPicsCount = vExcelApp.OlePropertyGet("ActiveSheet")
.OlePropertyGet("Pictures").OlePropertyGet("Count");
vSheet = vExcelApp.OlePropertyGet("ActiveSheet");
// 下面遍历当前ActiveSheet中的图像,不要直接运行
// 请自己修改后再执行,代码已测试过
for(int i=0; i<nPicsCount; i++)
{
vPic = vSheet.OlePropertyGet("Pictures").OlePropertyGet("Item", i+1);
String strPicID = String("Picture ") + String(i+1);
vSheet.OlePropertyGet("Shapes")
.OleFunction("Item", strPicID.c_str())
.OleProcedure("Select");
vSheet.OlePropertyGet("Shapes")
.OleFunction("Item", strPicID.c_str())
.OleProcedure("Select");
vShapeRange = vExcelApp.OlePropertyGet("Selection").OlePropertyGet("ShapeRange");
vShapeRange.OleProcedure("IncrementLeft",2);
vShapeRange.OleProcedure("IncrementTop",2);
// 调整图像大小结束
}
vExcelApp.OleFunction("Quit");
}
//---------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
ExcelApplication1->Connect();
ExcelApplication1->set_Visible(0, true);
ExcelApplication1->set_DisplayAlerts(0,false);
ExcelWorkbook1->ConnectTo(ExcelApplication1->Workbooks->Open((WideString)"d:\\2.xls",
TNP, TNP, TNP, TNP,
TNP, TNP, TNP, TNP,
TNP, TNP, TNP, TNP, 0));
ExcelWorksheet1->ConnectTo(ExcelWorkbook1->Worksheets->get_Item(V(1)));
String ConnStr;
RangePtr R;
String SQL;
ExcelWorksheet1->Activate();
ADOConnection1->ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\111.mdb;Persist Security Info=False";
//ConnStr="ODBC;DSN=mm;UID='';PWD=''";
ConnStr="OLEDB;"+ADOConnection1->ConnectionString;
SQL="select * from steel";
R=ExcelWorksheet1->get_Range(V("a1"), V("a1"));
ExcelQueryTable1->ConnectTo(ExcelWorksheet1->QueryTables->Add(V(ConnStr), R, V(SQL)));
ExcelQueryTable1->Refresh();
ExcelWorksheet1->Rows->set_RowHeight(V(30));
ADOQuery1->SQL->Text="select * from steel";
ADOQuery1->Open();
int i,j,k;
String Col="a";
String newCol;
for(i=0;i<ADOQuery1->FieldCount;i++)
{
if(ADOQuery1->Fields->Fields[i]->FieldName == "图形")
{
newCol = Col+IntToStr(1);
R = ExcelWorksheet1->get_Range(V(newCol),V(newCol));
R = R->get_EntireColumn();
R->Insert(V(0));
R = ExcelWorksheet1->get_Range(V(newCol),V(newCol));
R->set_Value(V("图形"));
R->set_ColumnWidth(V(40));
break;
}
Col=GetNextColName(Col);
}
unsigned int DataHandle;
HPALETTE APalette;
unsigned short MyFormat;
Graphics::TBitmap *bmp=new Graphics::TBitmap();
RangePtr r;
Excel_2k::ShapesPtr ss;
Excel_2k::ShapePtr s;
ADOQuery1->First();
for(i = 0; i < ADOQuery1->RecordCount; i++)
{
Image1->Picture->Assign(ADOQuery1->FieldByName("图形"));//将字段内容转为图片
String strPos=Col+IntToStr(i+1);
r=ExcelWorksheet1->get_Range(V(strPos),V(strPos));
if(ADOQuery1->FieldByName("图形")->IsNull)
{
ADOQuery1->Next();
continue;
}
bmp->Width=Image1->Width;
bmp->Height=Image1->Height;
bmp->Canvas->StretchDraw(Rect(0,0,bmp->Width,bmp->Height),
Image1->Picture->Graphic);
bmp->SaveToClipboardFormat(MyFormat,DataHandle,APalette);
Clipboard()->SetAsHandle(MyFormat,DataHandle);
ExcelWorksheet1->Paste(V(LPDISPATCH(r)), TNP, 0);
ADOQuery1->Next();
}
delete bmp;
ADOQuery1->Close();
ExcelWorkbook1->Disconnect();
ExcelWorksheet1->SaveAs((WideString)"d:\\2.xls",
TNP, TNP, TNP, TNP,
TNP, TNP, TNP, TNP,
0 );
ExcelWorksheet1->Disconnect();
ExcelApplication1->Quit();
ExcelApplication1->Disconnect();
}
[解决办法]
为什么要两种方式一起使用呢?都用OLE代码来操作多方便。