|
在实际的工作中,我们经常要对一些表格数据进行处理,如果先把这些表格数据通过Excel处理,再导入到AutoCAD,
6 k" _7 X/ j3 F, D3 A3 E7 ]9 x 那绝对会起到事半功倍的效果。随着Automation编程技术的出现,我们可以很方便地实现这一点了,下面是个
9 u" G9 |9 `. r/ N2 l ObjectARX的例子,其功能是动态关联Excel,然后读取其数据,并将这些数据打印在文本域中。
5 c+ w( A* `( D //动态从Excel读取数据
9 M* E% `% T% j$ I5 ~4 X# f3 R int DynamicReadFromExcel()2 {/ o7 c$ u& d/ X
{
4 b- u( E G9 _2 t( z; P. B //常用变量定义
+ e& ^4 V ?2 E( U% j _Application app;
' Y. I: S7 f& f Workbooks books;
; s1 h( J3 V' B' V. F" @$ H, b _Workbook book;
( T: I" U" z5 }0 w- Z9 B% a$ ?; z Worksheets sheets;) [0 D. o' b W) b
_Worksheet sheet;
7 b/ z+ m( g" l; [ Range range;) x5 i: p* E! C: B$ i# u, v
Range iCell;, a$ \+ }, b0 l
LPDISPATCH lpDisp;
( A! L( j9 I' ^6 N7 R6 h' _9 k COleVariant$ k+ Q7 y8 R9 ]2 a9 n" S
covTrue((short)TRUE),7 d' p3 z2 d. J0 y5 n& L! U
covFalse((short)FALSE),, B9 Z6 ` l. A4 U8 N7 A
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);9 \: W" K" `' U! x7 M
COleVariant vResult;
# `. |) U; a& N9 ~' b( q8 i# z //采用MFC方式初始化COM库,程序结束时COM库会自动释放 F) b' X F9 D G
if(!AfxOleInit())2 G n7 T, ?/ Q0 q. e
{
: o3 j, W# _7 a1 u* Q3 M MessageBox(NULL,"初始化COM支持库失败!\n无法控制Excel!", \
4 S3 b' S) N/ W3 l. ? "TrueTable",MB_IConERROR | MB_OK);* Q8 S ^' b6 w: p8 d v0 [9 T
return RTERROR;0 ?- h. e. j5 n. M$ T
}# o% Q+ s/ s& {, H9 N' y9 f
//关联已经运行的Excel实例 a7 u8 z, x* z- W6 Y+ H0 B
CLSID clsid;
: A: V: W. [0 Z6 u# a CLSIDFromProgID(L"Excel.Application", &clsid);
4 G) ~. q3 L: A% j$ h9 A IUnknown *pUnk = NULL;
# b# f6 [, d4 w. |* D I IDispatch *pRunDisp = NULL;
( J/ h+ S% e/ |/ [1 s/ h- M for(long i=1;i<=5;i++) //做5次尝试
1 Q4 M& A3 Y8 q6 f, e: c! a {
+ [ l3 \( x( [, x1 ?% {1 s HRESULT hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);
- Z4 T% e9 x/ @# B1 ^. v if(SUCCEEDED(hr))
5 n2 E+ j- v+ O: F" I {, ]3 P+ | ^# B, @
hr = pUnk->QueryInterface(IID_IDispatch, (void **)&pRunDisp);
# J- O) N D( A, M) H break;
' l6 t' ^# n T | m }8 {( [: G9 H: K
::Sleep(10);) T' T& E% e5 o. ?$ O
}* j+ ^+ L8 l5 d6 k
if (!pRunDisp)
% x( z2 y. n5 h5 W! w3 n) `$ M0 X- } {6 h( J) \5 y6 ]* H" |$ n
::MessageBox(NULL, "没有发现Excel!", "TrueTable", MB_ICONHAND);
! K! ]! c6 p: Q9 j( Y3 P' t3 P return RTERROR;, k% a8 P& B* C! s T
}
7 J2 g! {( j% |3 z8 z& R) k+ b if (pUnk) pUnk->Release();. A. U0 a5 d' y' i' y; ]& o
//关联Excel
% ]" O9 g& `7 m2 {* \+ y app.AttachDispatch (pRunDisp);
& w' @) h& x) [" Z3 E //得到当前活跃sheet
0 v# g# g6 T6 l //如果有单元格正处于编辑状态中,此操作不能返回,会一直等待9 v7 j( [6 q& Y* J3 Y
lpDisp=app.GetActiveSheet();
# y. x5 ^' H9 A# P9 H# E8 [$ h7 w if(lpDisp==NULL)
" H$ Y9 t& D0 O& u2 I {
# W9 X0 B+ N4 {3 Y: e, G MessageBox(NULL, "没有发现有效的表格!", \
' J8 [, O5 g: ~9 \ "TrueTable",MB_IConERROR | MB_OK);4 `3 c3 T% W: C5 A+ I5 R
app.ReleaseDispatch ();
* ?! z$ @0 ~; N: o0 }) r return RTERROR;
- P% X2 x6 G$ g }+ m1 _3 p: E: K* r3 Z. t
sheet.AttachDispatch(lpDisp);
, c( z: y1 `# ^0 ^; ~. [& ` //已经使用的行数:
! z/ H, ~9 b% A long row_num;; v2 ?8 \+ R) ]9 c, y8 k# D0 n
range.AttachDispatch(sheet.GetUsedRange());
2 ~2 `1 V- A# z3 ` range.AttachDispatch(range.GetRows());
' H* y. H6 a$ A row_num=range.GetCount();3 L& D0 n/ A$ s( K4 ~: G3 d3 v
//已经使用的列数: {, F8 b# S7 q) T# y o' Z8 M
long col_num;" F# C o6 X. q7 E4 x4 V8 n2 G
range.AttachDispatch(sheet.GetUsedRange());
2 x; L, |7 n. a9 i0 i6 M range.AttachDispatch(range.GetColumns());
3 g* w0 M1 |) N col_num=range.GetCount();7 q7 S+ A5 d3 n: q$ j' D8 B1 Z c- S+ D
//已经使用区域的起始行、列:
( g9 m( U& V7 V& ~* Z* Y range.AttachDispatch(sheet.GetUsedRange());
0 q! Q" u. U3 ~; o( g$ t+ V3 N: { long StartRow=range.GetRow(); //起始行$ P8 W& H: B( ~" ~! {" _, d0 L( f% C
long StartCol=range.GetColumn(); //起始列
+ x, ]5 c' f1 e; } //读取sheet名$ \5 \. O9 i6 r2 j
CString SheetName=sheet.GetName();! p' ^* p# B0 \$ w
//ads_printf("\n%s",SheetName);5 n! D8 f9 r L/ N% v4 b
if(col_num<2 && row_num<2) //此sheet为空- Q' ~: x3 Y# z; C* A
{$ C2 Y, L _" @; _
MessageBox(NULL,"\n当前表格没有数据!", \ i) M- W1 p3 x8 R
"TrueTable",MB_IConERROR | MB_OK);
- z8 J" X2 W: \+ q2 G { app.ReleaseDispatch ();" _; e$ M3 [/ n( y/ e; s
return RTERROR;
9 ^1 Z( @5 c3 _/ I% l6 W; n }
& f. j6 F3 g2 @9 K/ `- p) V else. W- B$ x1 n/ S7 S' c' Z# [: r2 h
{! l- p2 M) W5 L1 z/ [
ads_printf("\n表格%s共%d行,%d列",SheetName,row_num,col_num);
: K' a) e. T4 W5 H; o' N8 [' ?) f; E }
7 f$ ~4 p e d9 F% J) Y8 l7 K //得到全部Cells,此时,range是cells的集合
) n. o9 z: @: |) T0 @ range.AttachDispatch(sheet.GetCells());# d0 \' H) Q! c
//读写数据了
# d% l( L3 B* }5 E CString cstr;( n. x9 i+ s! w! A
ads_printf("\n");
8 T1 F2 e: K7 {5 s: d" s for(long i=StartRow;i<STARTROW+ROW_NUM;I++) {
) M# U# X; c( I. s: ^ for(long j=StartCol;j<STARTCOL+COL_NUM;J++) {& {' V8 S' d! V; f
//读取单元格文本/ `! c. Y; n" p3 f3 x2 P
iCell.AttachDispatch(range.GetItem (COleVariant(i),COleVariant(j)).pdispVal );) \( b; I# h4 Y! _! |5 l- @* ~" L4 A
vResult =iCell.GetText();
+ W$ {# i" r8 W' t2 h) i cstr=vResult.bstrVal;
' r" ~- z4 [" A( F9 i% f3 V //写单元格文本% {# H2 ~: S+ Z: x
ads_printf("%s ",(LPTSTR)cstr);0 }0 g) I7 \' M
}9 o+ @4 k/ \0 p: Z: i4 J3 [5 r
ads_printf("\n");+ M( z% J$ h! f0 m
}
' J% v* U" H& D4 u$ B //释放Dispatch
$ o9 f6 b8 g) \- n% s* ^0 ? iCell.ReleaseDispatch ();% Y8 L& q0 p8 t; [$ u7 ~. \: V
range.ReleaseDispatch ();5 D% C# h3 `- i# ^" Z- r
sheet.ReleaseDispatch ();5 Q) w& z7 x7 R) V# ?* p
sheets.ReleaseDispatch ();/ k/ S. v/ G6 O6 G4 w
book.ReleaseDispatch ();$ A" u5 e$ k4 |0 |8 K
books.ReleaseDispatch ();' y7 }6 C3 ~( v l" v. [5 x
app.ReleaseDispatch ();5 f7 j, m7 j8 X1 A
return RTNORM;: r1 S( q* ]3 }
}
( J _ J6 `, w1 D1 ~+ ~ 如果要输出到Excel的话,关键函数就是:
! H! m1 q9 Y5 _6 j3 M0 z+ a iCell.SetItem(COleVariant(i),COleVariant(j),COleVariant(cstr)); |
|