|
在实际的工作中,我们经常要对一些表格数据进行处理,如果先把这些表格数据通过Excel处理,再导入到AutoCAD,
' y0 h2 z5 a. q' G 那绝对会起到事半功倍的效果。随着Automation编程技术的出现,我们可以很方便地实现这一点了,下面是个! B$ E- r$ X/ Z! a/ ~" v. [
ObjectARX的例子,其功能是动态关联Excel,然后读取其数据,并将这些数据打印在文本域中。8 c# f7 O1 J" }1 O$ [, P
//动态从Excel读取数据' W5 C5 g* v7 b/ j
int DynamicReadFromExcel()- {5 P9 ]5 j& m8 o" h2 M. I
{2 {, |8 L6 {1 k
//常用变量定义/ z. C; p# p: \( v* n
_Application app;
3 a. w1 i+ ?4 N Workbooks books;, h2 I' {3 I5 h$ a1 F. S( W, m
_Workbook book;
, z) n5 p. m4 ?5 Y! D3 t Worksheets sheets;
# r# u- e6 u' B" F8 h _Worksheet sheet;4 E; h2 X$ g$ ~
Range range;
- h' w7 p g; w7 t3 k Range iCell; s+ C0 {! i; F/ U
LPDISPATCH lpDisp;; O2 d; g- f- R% q, x+ o
COleVariant
" ?( O' S, [/ O- o) K covTrue((short)TRUE),1 _8 g% i2 D) Z9 V2 m; T. k
covFalse((short)FALSE),8 o$ a& w7 P2 ?4 c
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
8 S% l8 t# p# R& ~+ g6 x COleVariant vResult; L$ a1 b+ |: _6 Q: X
//采用MFC方式初始化COM库,程序结束时COM库会自动释放
1 G6 ~& \* o( `! V5 B' W( ` if(!AfxOleInit())5 k7 h) S- X0 D' w" I# e& ?
{
( c, Z6 g5 Z+ b: R MessageBox(NULL,"初始化COM支持库失败!\n无法控制Excel!", \; k" v$ W x+ D6 w& g9 Z! j/ r
"TrueTable",MB_IConERROR | MB_OK);
0 ?: I, p' \5 p1 _) { return RTERROR;4 e$ h+ A s, g- ~( w0 l' c0 d
}% ?) _1 u5 B- W' ]# ]
//关联已经运行的Excel实例
& I* z! J) A3 W. h' V CLSID clsid;
, N2 U+ p8 k( }; O! v3 y CLSIDFromProgID(L"Excel.Application", &clsid);
$ f ^: F* M5 v- N; V IUnknown *pUnk = NULL;* ?0 F% k `7 H `
IDispatch *pRunDisp = NULL;
& T2 g2 v/ K# c for(long i=1;i<=5;i++) //做5次尝试
* }, {9 n; V8 ^1 I {
: T7 l( s' e9 N5 @% D5 Z HRESULT hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);8 F: ~' P: a% l j- q
if(SUCCEEDED(hr)). M+ ?/ v3 g3 n
{1 B |1 e) X0 [5 w l' A3 u
hr = pUnk->QueryInterface(IID_IDispatch, (void **)&pRunDisp);
' \% O% h2 p; f9 |" E/ a( F* \6 e, C break;
& C s! x7 W0 r5 B$ F }
- z; T& g8 h% Y4 W ::Sleep(10);
H& d7 \& W! |8 B }2 @& T# O% o1 w0 \' e: _
if (!pRunDisp)3 i6 c! d$ x# C1 u/ {& @
{4 D% }! j% u& y
::MessageBox(NULL, "没有发现Excel!", "TrueTable", MB_ICONHAND);$ u1 }5 S+ t1 q6 A, e
return RTERROR;
0 p4 [8 T: r& M! \# t5 x: a }
2 y' N' ?, P# H! j) I* e- L. a if (pUnk) pUnk->Release();. W& _# F5 t6 b1 O& Q! U$ v+ W
//关联Excel9 b/ {* T2 L' n& O4 c( i+ s- I
app.AttachDispatch (pRunDisp);
+ f+ F* S0 E8 r2 _7 O% O6 O //得到当前活跃sheet5 b+ _, ^3 y3 m7 d5 w
//如果有单元格正处于编辑状态中,此操作不能返回,会一直等待7 z! U0 X3 P @0 k1 Q/ c5 ~
lpDisp=app.GetActiveSheet();
3 v2 }4 @' f& P" f' A$ v1 N if(lpDisp==NULL)7 r% P, q; u5 W
{
# K% h2 l4 c2 f5 r* F0 z/ K MessageBox(NULL, "没有发现有效的表格!", \2 \ Y# Z( P' }6 X1 T
"TrueTable",MB_IConERROR | MB_OK);
3 o9 s& R' A: p" J% P4 f app.ReleaseDispatch ();, u9 U1 V1 Q$ g; x& j* J
return RTERROR;
9 w# v5 i+ O; v- X, ? }
% M" T& D s1 \, l/ @9 Q sheet.AttachDispatch(lpDisp);) \5 K5 y, ~% B
//已经使用的行数:
7 s$ k7 o, A6 H7 B8 _0 O5 {: ] long row_num;
4 \- s/ d8 C. O( J( Z2 G range.AttachDispatch(sheet.GetUsedRange());
7 e2 `) |8 C3 g/ I range.AttachDispatch(range.GetRows());8 m9 `' p2 P; o$ p* q( k
row_num=range.GetCount();
( q, Z$ I8 f. U4 y' f6 l. x; c2 M //已经使用的列数:3 q+ c- u4 }7 j$ l3 x
long col_num;
7 c* G* T/ ~. S" |( B. f- _ z range.AttachDispatch(sheet.GetUsedRange());
: X D1 w1 E3 w8 X range.AttachDispatch(range.GetColumns());
% W& U' Z: Q# Z& ]/ _( r9 k col_num=range.GetCount();! {1 s. e1 d% P5 s' q* E
//已经使用区域的起始行、列:( Q* E7 [% }' L4 l) \5 M
range.AttachDispatch(sheet.GetUsedRange());# h% X5 c3 b/ S2 V4 n
long StartRow=range.GetRow(); //起始行5 x0 w0 s- D+ V
long StartCol=range.GetColumn(); //起始列
% z2 J& n; q$ ^; d- K4 ~ //读取sheet名, j7 T6 v/ T3 l/ ?4 q' M) ]
CString SheetName=sheet.GetName(); e, n' e' D+ q5 P& l3 a
//ads_printf("\n%s",SheetName);
7 @, r5 D% a/ x1 t E' l if(col_num<2 && row_num<2) //此sheet为空4 N; X) n# A" z
{
; h9 Y( C9 H4 a3 A+ m) O- f MessageBox(NULL,"\n当前表格没有数据!", \
+ K6 b# B0 z2 h: D* C+ T/ F, l V "TrueTable",MB_IConERROR | MB_OK);0 N; H7 W! L, J
app.ReleaseDispatch ();' }% {7 E- c3 X7 n: N: Y
return RTERROR;
" y9 V' P! v% B: ?6 |4 ^ }( X" V' M# |6 f. B K/ V
else
* c4 A$ g4 r6 K; K {
2 A& f; T- L5 @ ads_printf("\n表格%s共%d行,%d列",SheetName,row_num,col_num);
$ V: p7 ?3 G+ {4 z }
8 \9 q! r, g$ i" q1 h( ] //得到全部Cells,此时,range是cells的集合
3 j9 n$ N n7 t7 A% m: t8 d* M range.AttachDispatch(sheet.GetCells());8 k/ p% \+ n. q( _7 L/ H$ c
//读写数据了( d: _6 q' r1 G8 A
CString cstr;
! V' ^# l4 D& V6 Z; v8 ?2 X ads_printf("\n");( ^3 `& ^/ I1 W' Q; n
for(long i=StartRow;i<STARTROW+ROW_NUM;I++) {
/ ?: }; W9 x% K' ]" e" V for(long j=StartCol;j<STARTCOL+COL_NUM;J++) {
$ J( ?% F5 W3 H+ | //读取单元格文本
7 i+ n3 U/ o; D" r8 z4 c iCell.AttachDispatch(range.GetItem (COleVariant(i),COleVariant(j)).pdispVal );
: }5 a" E. }9 j vResult =iCell.GetText();
. y4 V* E1 r, c- ?0 E9 B- L9 ? cstr=vResult.bstrVal;$ t% [5 [3 E+ M7 }& A6 }; V% W1 L
//写单元格文本
0 P$ R* L2 R& ?# i5 P ads_printf("%s ",(LPTSTR)cstr);/ f2 a0 S9 S6 }# J) Z( B
}- u2 }! m$ {6 `7 `
ads_printf("\n");0 `: P+ w( f/ i' P* F: Z
}' L0 e* l4 }# i! Q7 Q: e
//释放Dispatch& a$ v' {$ I9 F% i! b
iCell.ReleaseDispatch ();
' v" E k" ]3 w! {2 R range.ReleaseDispatch ();8 {* u1 s0 t: ?0 I7 Z& m- D& {
sheet.ReleaseDispatch ();9 ^* j& J" s; n2 q+ @) B3 u, |
sheets.ReleaseDispatch ();4 ^% x8 |2 t& D3 i8 }/ K
book.ReleaseDispatch ();, m* r! a( v. \. `
books.ReleaseDispatch ();
+ |; S" _: T+ e0 s$ x, n1 J0 b app.ReleaseDispatch ();
! j; D! f: t/ B/ d0 ` return RTNORM;% T# `+ s" \# \" n C% m
}8 L& [$ _" E- s
如果要输出到Excel的话,关键函数就是:: ~- P, y. |; u0 t5 J, v# D
iCell.SetItem(COleVariant(i),COleVariant(j),COleVariant(cstr)); |
|