VC操作Excel文件編程相關(guān)內(nèi)容總結(jié)

1、VC操作Excel文件編程相關(guān)內(nèi) 容總結(jié)VC操作Excel文件編程相關(guān)內(nèi)容總結(jié)最近在做個(gè)數(shù)據(jù)庫(kù)程序，因?yàn)橛行?shù)據(jù)用戶要求 導(dǎo)出到Excel文件顯示（需要報(bào)給其他單位）。所以查閱了一下相關(guān)的VC的Excel編程操作, 總結(jié)一下吧：（所有資料來源于網(wǎng)絡(luò)） 利用VC操作Excel的方法至少有兩種1 .利用ODBC把Excel文件當(dāng)成數(shù)據(jù)庫(kù)文件, 來進(jìn)行讀、寫、修改等操作，網(wǎng)上有人編寫了 c Spreadsheet類，提供支持。2 .利用 Automation（OLD Automation） 方法。將Excel當(dāng)成組件服務(wù)器，利用VBAo 又分為基于MFC的和SDK兩種。主要研究了一下第二種基于MFC

2、的OLE編 程方法。一、Excel的對(duì)象模型在對(duì)Excel編程之前首先要了解微軟ExceI 對(duì)象（Object）模型。（Parent-Child 系）圖1 Office應(yīng)用程序?qū)ο竽Ｐ?Excel) From msdn其中：Application：代表應(yīng)用程序本身。即Excel應(yīng)用程序Workbooks： > Workbook 的集合, 代表了工作薄。Worksheets：是 Worksheet 的集合, 是Workbook的子對(duì)象。Range：是 Worksheet的子對(duì)象，可以理解為Sheet中一定范圍的單元格。Shapes：是Worksheet的子對(duì)象，用于存儲(chǔ)圖片等信息的單元格。

3、二、VC操作Excel的初始化過程 工、導(dǎo)入Excel庫(kù)文件。首先打開Class Wizard,點(diǎn)擊“Add cl ass"按鈕下的"From a type Library”導(dǎo) 入office安裝目錄下的“excel.exe"(適用 于 Excel2003),然后選擇需要用的一些 類，比如一Application, Workbook, Wo rkbooks,Worksheets,_WroksheetzRange,Shapes等。點(diǎn)擊確定后，系統(tǒng)會(huì)在你 的程序目錄下生成excel.h和excel.cpp兩 個(gè)文件，在要使用這些導(dǎo)入的類時(shí)加入#加：11 de “Exc

4、el.h”即可。2、初始化Application。首先，初始化COM組件view plaincopy to clipboardprint? if(!AfxOleInit()<AfxMessageBox("無法初始化COM的 動(dòng)態(tài)連接庫(kù)”)；return FALSE;if(!AfxOleInit() AfxMessageBox("無法初始化COM的 動(dòng)態(tài)連接庫(kù)”)；return FALSE;然后，創(chuàng)建Excel服務(wù)器(啟動(dòng)Excel)定義app的全局或成員變量_Applicatio n app;view plaincopy to clipboardprint?if (l

5、app.CreateDispatchC'Excel.Appli cation11)AfxMessageBox("無法啟動(dòng)Excel月艮務(wù)器”力return FALSE;if (!app.CreateDispatch("Excel.Appli cation11)<AfxMessageBox("無法啟動(dòng)Excel月艮務(wù)>");return FALSE;?3、設(shè)置Excel的狀態(tài)view plaincopy to clipboardprint? app.SetVisible(bVisble); / 使 Excel 可見app.SetUser

6、Control(bControl); / 允 許其他用戶控制Excelapp.SetVisible(bVisble); / 使 Excel可見app.SetUserControl(bControl); 允許其他用戶控制Excel三、VC對(duì)Excel的操作定義變量view plaincopy to clipboardprint?Workbooks books;_Workbook book;Worksheets sheets;_Worksheet sheet;LPDISPATCH IpDisp;Range range;COIeVariant covOptional(long)DISPE PARAM

7、NOTFOUND, VT ERROR)；Workbooks books;_Workbook book;Worksheets sheets;_Worksheet sheet;LPDISPATCH IpDisp;Range range;COIeVariant covOptional(long) DISP_E_PARAMNOTFOUND, VT_ERR OR)；1、打開已有的Excel文件 view plaincopy to clipboard print?books.AttachDispatch(app.GetWo rkbooks();/或者也可以/ books = app.GetWorkbook

8、s 0;IpDisp = books.Open(vvD: Wl.xls ”, covOptionalz covOptional, covOp tional, covOptionalz covOptional, c ovOptional, covOptionalf covOption alf covOptional, covOptionalzcovOptional, covOptional, covOption al, covOptional);books.AttachDispatch(app.GetW orkbooks();11或者也可以/ books = app.GetWorkbooks()

9、;IpDisp = books.Open(" D:l.xl s", covOptional, covOptional, covO ptional, covOptionalz covOptional, covOptional, covOptional, covOption al, covOptional, covOptional, covOptional, covOptional, covOpti onalf covOptional);2.、新建一個(gè)xls文件，并寫入數(shù)據(jù)COIeVariant covOptional(long) DISP_E_PARAMNOTFOUND, V

10、T_ERR OR)；/ Get the Workbooks collecti on so that you can add a new wor kbookbooks.AttachDispatch(app.GetWorkbooks();/ 得至ij Workbooksbook = books. Add(covOptiona I);/ 得至(J Workbook/ Get the Worksheets collectio n so that you can get the IDispatc h for the first Worksheetsheets = book.GetWorksheets()

11、;/ 得至! Worksheetssheet = sheets.GetItem(COIeVariant(short)l); / 得到 Worksheet/分別向每個(gè)單元格添加數(shù)據(jù)/ Excel2000 用函數(shù) SetValue() 即可，Excel2003 用函數(shù) SetVa肉e2()/range = sheet.GetRange(COIeV ariant(,Al,)z covOptional); / 獲取Al Rangerange.SetValue2( COIeVariant(vlDate");添加數(shù)據(jù)range = shee 匕 GetRange(COIeVa riantC

12、9;Bl11), covOptional); / 獲取 Bl Rangerange.SetValue2(COIeVariant(lvO rder");/ 添加數(shù)據(jù)range = sheet.GetRange(COIeVa riant(IICl,)z covOptional); / 獲取 Cl Rangerange.SetValue2(COIeVariant(llA mount");/ 添加數(shù)據(jù)range = sheet.GetRange(COIeVa riant(,Dl,)A covOptional); / 獲取 DI Rangerange.SetValue2(COIeVa

13、riant(lvT/添加數(shù)ax");據(jù)/向單元格中添加公式range = shee 匕 GetRange(COIeVar iant("D211), covOptional); / 獲得D2 Rangerange = range.GetResize(COIeVa riant(long)NUMROWS)f COIeVarian t(long)l); /重新設(shè)置D2的大小range.SetFormula(COIeVariant("給D= C2*0b07,1);2： D21設(shè)置公式/設(shè)置單元格的格式range = sheet.GetRange(COIeV ariantCA

14、l11), COIeVariantfDl11); / / 獲得 A1:D1 的 RangeoFont = range.GetFont 0;/獲得Range的字體oFont.SetBold(COIeVariant(short)TRUE);/設(shè)置是否粗體oFont.SetColor(COIeVariant(lon g)RGB(255, 0, 0);/設(shè)置字體顏色oFont.SetName(COIeVariant(11/設(shè)置字體類型range = range.GetEntireColumn0；/獲得全部的單元格range.AutoFit0；/自動(dòng)適合尺寸3、合并單元格/思路：先獲取AL Cl的Ran

15、ge范圍，然后重新定義此范圍，最后合并/2.直接獲得A1:C2的Range范圍，直接合并。結(jié)果和第一種方法一樣Range unionRange;unionRange = sheet.GetRange(COIeVariantCAl11), COIeVariantC'X 1");vResult = unionRange.GetMerg eCells();unionRange = unionRange.GetResize(COIeVanant(long)2)f COIeV ariant(long)3);unionRange.Merge(COIeVariant(long)O);/合并

16、單元格unionRange.SetRowHeight(COIeVariant(short)30);/設(shè)置單元格的高度unionRange.SetHorizontalAlignment(COIeVariant(long)-4108);/水平居中對(duì)齊 式，其他沒試)4、向單元格中插入圖片（支持MP、JPG 格Shapes shapes = sheet.GetSh apes(); /從Sheet對(duì)象上獲得一個(gè) Shapes range = sheet.GetRange(COIeV ariant(lvB16vv)zCOIeVariant(vlJ22”)；/獲得Range對(duì)象，用來插入圖片rgMyRge

17、l = range;shapes.AddPictureCD:Testl.jpg “ ，false , true ,(float)range .G etLeftO.dblVal, (float)range .GetTo p().dblValz /從本地添加一個(gè)圖片(float)range .G etWidth().dblValz (float)range .Get Height().dblVal);ShapeRange sRange = shapes. GetRange(_variant_t(long(l);sRange.SetHeight(float(30); sRange.SetWidth(

18、float(30);5、將已建的文件另存為COIeVariant covOptional(long)D ISP_E_PARAMNOTFOUND, VT_ERRO R)；book.SaveAs(COIeVariant("D:3. xls'covOptionalcovOptional,covOptional,covOptional, covOptional,Oz covOptional,covOptional, covOptionalzcovOptionalzco vOptional);6、關(guān)閉Excel服務(wù)COIeVariant covOptional(long)DISP E PARAMNOTFOUND, VT ERRObook.SetSaved(TRUE);/將Workbook的保存狀態(tài)設(shè)置為已保存,即不讓系/釋統(tǒng)提示是否人工保存range.ReleaseDispatch(); 釋 放Range對(duì)象sheet.ReleaseDispa