在VC中徹底玩轉(zhuǎn)Excel

源創(chuàng)］在VC中徹底玩轉(zhuǎn)Excel如今Excel是越來(lái)越重要了，在我們自己開(kāi)發(fā)的程序中不免要和Excel打交道了。利用Automation技術(shù)，我們可以在不去了解數(shù)據(jù)庫(kù)的情況下玩轉(zhuǎn)Excel，而且你會(huì)發(fā)現(xiàn)一切竟如此輕松！好了，咱們開(kāi)始吧，我不喜歡用長(zhǎng)篇累牘的代碼來(lái)故弄玄虛，所以下面的代碼都是切中要害的片段，總體上是個(gè)連貫的過(guò)程，包括啟動(dòng)Excel，讀取數(shù)據(jù)，寫(xiě)入數(shù)據(jù)，以及最后的關(guān)閉Excel，其中還包括了很多人感興趣的合并單元格的處理。特別說(shuō)明以下代碼需要MFC的支持，而且工程中還要包含EXCEL2000的定義文件：EXCEL9.H，EXCEL9.CPPVC6.0操作Excel建立一個(gè)空的單文檔程序；加入Excel的庫(kù)文件：在vc++的view菜單里面選classwizard，然后選Automationtab，再選AddClass，選"Fromatypelibrary."，然后再選你需要的objectlibrary。(forthisexample,ifyouareautomatingExcel97,choosetheMicrosoftExcel8.0ObjectLibrary;thedefaultlocationisC:\ProgramFiles\MicrosoftOffice\Office\Excel8.olb).IfyouareautomatingMicrosoftExcel2000,chooseMicrosoftExcel9.0ObjectLibraryforwhichthedefaultlocationistheC:\ProgramFiles\MicrosoftOffice\Office\Excel9.olb.IfyouareautomatingMicrosoftExcel2002andMicrosoftOfficeExcel2003,theobjectlibraryisembeddedinthefileExcel.exe.ThedefaultlocationforExcel.exeinOffice2002isC:\programFiles\MicrosoftOffice\Office10\Excel.exe.ThedefaultlocationforExcel.exeinOffice2003isC:\programFiles\MicrosoftOffice\Office11\Excel.exe.在ListCtrl框中選中—Application,—Workbook,—Worksheet,Range,Sheets,Workbooks單擊OK,自動(dòng)生成EXCEL8.H和EXCEL8.CPP文件(或者EXCEL9.H和EXCEL9.CPP或者EXCEL.H和EXCEL.CPP)。初始化COM庫(kù)：在App類(lèi)的InitInstance中一定要先加上AfxOleInit();因?yàn)椴僮鱁xcel是屬于COM自動(dòng)化，需初始化COM庫(kù)。加入Excel的頭文件：文檔類(lèi)的CPP文件中包含頭文件#include"excel9.h"，是關(guān)于Excel中各類(lèi)的接口定義信息。如果excel9.h,excel9.cpp文件已經(jīng)生成創(chuàng)建project,將示例文檔中的excel9.h,excel9.cpp復(fù)制到工程目錄，選Project-->Addtoproject-->File-->excel9.h;Project-->Addtoproject-->File-->excel9.cpp;在App的Initinstance中加入AfxOleInit();在Dlg的頭文件中加入#include"excel9.h"代碼中加入調(diào)用COM接口代碼，編譯即可，//*****〃變量定義-Applicationapp;Workbooksbooks;_Workbookbook;Worksheetssheets;-Worksheetsheet;Rangerange;RangeiCell;LPDISPATCHIpDisp;COleVariantvResult;COleVariantcovTrue((short)TRUE),covFalse((short)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);//*****//初始化COM的動(dòng)態(tài)連接庫(kù)if(!AfxOleInit()){AfxMessageBox("無(wú)法初始化COM的動(dòng)態(tài)連接庫(kù)!”)；return;}//*****〃創(chuàng)建Excel2000服務(wù)器(啟動(dòng)Excel)if(!app.CreateDispatch("Excel.Application")){AfxMessageBox("無(wú)法啟動(dòng)Excel服務(wù)器!”)；return;}app.SetVisible(TRUE); 〃使Excel可見(jiàn)app.SetUserControl(TRUE); 〃允許其它用戶(hù)控制Excel//*****//打開(kāi)c:\X1.xlsbooks.AttachDispatch(app.GetWorkbooks());lpDisp=books.Open("C:\\\\1.xls”,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional);//*****//得到Workbookbook.AttachDispatch(lpDisp);//*****//得到Worksheetssheets.AttachDispatch(book.GetWorksheets());//*****//得到當(dāng)前活躍sheet〃如果有單元格正處于編輯狀態(tài)中，此操作不能返回，會(huì)一直等待lpDisp=book.GetActiveSheet();sheet.AttachDispatch(lpDisp);//*****〃讀取已經(jīng)使用區(qū)域的信息，包括已經(jīng)使用的行數(shù)、列數(shù)、起始行、起始列RangeusedRange;usedRange.AttachDispatch(sheet.GetUsedRange());range.AttachDispatch(usedRange.GetRows());longiRowNum=range.GetCount(); //已經(jīng)使用的行數(shù)range.AttachDispatch(usedRange.GetColumns());longiColNum=range.GetCount(); 〃已經(jīng)使用的列數(shù)longiStartRow=usedRange.GetRow(); 〃已使用區(qū)域的起始行，從1開(kāi)始longiStartCol=usedRange.GetColumn(); 〃已使用區(qū)域的起始列，從1開(kāi)始//*****〃讀取第一個(gè)單元格的值range.AttachDispatch(sheet.GetCells());range.AttachDispatch(range.GetItem(COleVariant((long)1),COleVariant((long)1)).pdispVal);COleVariantvResult=range.GetVdue();CStringstr;if(vResult.vt==VT_BSTR) 〃字符串{str=vResult.bstrVal;}elseif(vResult.vt==VT_R8)//8字節(jié)的數(shù)字{str.Format("%f”,vResult.dblVal);}elseif(vResult.vt==VT_DATE) 〃時(shí)間格式{SYSTEMTIMEst;VariantTimeToSystemTime(&vResult.date,&st);}elseif(vResult.vt==VT_EMPTY) 〃單元格空的{str="";}//*****〃讀取第一個(gè)單元格的對(duì)齊方式，數(shù)據(jù)類(lèi)型:VT_I4〃讀取水平對(duì)齊方式range.AttachDispatch(sheet.GetCells());iCell.AttachDispatch((range.GetItem(COleVariant(long(1)),COleVariant(long(1)))).pdispVal);vResult.lVal=0;vResult=iCell.GetHorizontalAlignment();if(vResult.lVal!=0){switch(vResult.lVal){case1: 〃默認(rèn)break;case-4108://居中break;case-4131:〃靠左break;case-4152:〃靠右break;}}//垂直對(duì)齊方式iCell.AttachDispatch((range.GetItem(COleVariant(long(1)),COleVariant(long(1)))).pdispVal);vResult.lVal=0;vResult=iCell.GetVerticalAlignment();if(vResult.lVal!=0){switch(vResult.lVal){case-4160://靠上break;case-4108://居中break;case-4107://靠下break;}}//*****〃設(shè)置第一個(gè)單元格的值"HI,EXCEL!”range.SetItem(COleVariant(1),COleVariant(1),COleVariant("HI,EXCEL!”));//*****〃設(shè)置第一個(gè)單元格字體顏色:紅色Fontfont;range.AttachDispatch(sheet.GetCells());range.AttachDispatch((range.GetItem(COleVariant(long(1)),COleVariant(long(1)))).pdispVal);font.SetColor(COleVariant((long)0xFF0000));//*****//合并單元格的處理//包括判斷第一個(gè)單元格是否為合并單元格，以及將第一個(gè)單元格進(jìn)行合并RangeunionRange;range.AttachDispatch(sheet.GetCells());unionRange.AttachDispatch(range.GetItem(COleVariant((long)1),COleVariant((long)1)).pdispVal);vResult=unionRange.GetMergeCells();if(vResult.boolVal==-1) 〃是合并的單元格{//合并單元格的行數(shù)range.AttachDispatch(unionRange.GetRows());longiUnionRowNum=range.GetCount();//合并單元格的列數(shù)range.AttachDispatch(unionRange.GetColumns());longiUnionColumnNum=range.GetCount();//合并區(qū)域的起始行，列l(wèi)ongiUnionStartRow=unionRange.GetRow(); //起始行，從1開(kāi)始longiUnionStartCol=unionRange.GetColumn(); //起始列，從1開(kāi)始}elseif(vResult.boolVal==0){//不是合并的單元格}//將第一個(gè)單元格合并成2行，3列range.AttachDispatch(sheet.GetCells());unionRange.AttachDispatch(range.GetItem(COleVariant((long)1),COleVariant((long)1)).pdispVal);unionRange.AttachDispatch(unionRange.GetResize(COleVariant((long)2),COleVariant((long)3)));unionRange.Merge(COleVriant((long)0)); //合并單元格//*****//將文件保存為2.xlsbook.SaveAs(COleVariant("C:\\\\2.xls"),covOptional,covOptional,\\covOptional,covOptional,covOptional,0,\\covOptional,covOptional,covOptional,covOptional);//*****〃關(guān)閉所有的book，退出Excelbook.Close(covOptional,COleVariant(OutFilename),covOptional);books.Close();app.Quit();關(guān)于excel.h和excel.cpp,要注意版本問(wèn)題.比如對(duì)excelxp,類(lèi)庫(kù)是直接包含在excel.exe中.因此你只要用加入類(lèi)(addclass)的方法，直接選中excel.exe，并選擇對(duì)話(huà)框中的常用的幾個(gè)類(lèi)(如Rang)就可以編程了.千萬(wàn)不要選所有的類(lèi),否則太大了.作者：unionsoft2003-11-2911:00:34)修改字體顏色的那段漏了一句，應(yīng)為：//*****〃設(shè)置第一個(gè)單元格字體顏色:紅色Fontfont;range.AttachDispatch(sheet.GetCells());range.AttachDispatch((range.GetItem(COleVariant(long(1)),COleVariant(long(1)))).pdispVal);font.AttachDispatch(range.GetFont());font.SetColor(COleVariant((long)0xFF0000));作者：zhengkuo2003-12-210:50:00)如果，程序既要安裝在2000的計(jì)算機(jī)上，也可能安裝在XP的機(jī)子上，有的用戶(hù)還用97,(指的都是office),可能會(huì)出現(xiàn)版本問(wèn)題 其中比較麻煩的是--在app.quit()后，仍舊存在excel進(jìn)程，如果這樣？怎么解決？作者：zhengkuo2003-12-210:51:50)請(qǐng)教高手，能否用vb控制excel做成dll，在arx中進(jìn)行調(diào)用，因?yàn)楫吘箆b與excel親切作者：unionsoft2004-1-3011:06:58)以下是引用zhengkuo在2003-12-210:50:00的發(fā)言：如果，程序既要安裝在2000的計(jì)算機(jī)上，也可能安裝在XP的機(jī)子上，有的用戶(hù)還用97,(指的都是office),可能會(huì)出現(xiàn)版本問(wèn)題 其中比較麻煩的是--在app.quit()后，仍舊存在excel進(jìn)程，如果這樣？怎么解決？兼容性問(wèn)題：office2002-office97是向下兼容的，只要你不使用office2002中的新特性，程序在這些office版本中都好用Excel程序不能退出的問(wèn)題：.不要使用#import導(dǎo)入類(lèi)型庫(kù)，如：#import"c:\\excel\\excel.olb”.程序結(jié)束時(shí)，確保所有IDispatch都釋放了，如：app.ReleaseDispatch();作者：easypower2004-5-1410:00:39)遢是不明白如何得到excel.cpp和excel.hMW^文件信青指教作者：jack19752004-6-2416:43:03)有版本問(wèn)題時(shí)，可以加一個(gè)判斷：1、首先通過(guò)exlApp得到版本，比如9.0，10.0，11.0,10.0以后的版本注意open函數(shù)的參數(shù)為15個(gè)，即最后在增加兩個(gè)covOptional，即可，另外，補(bǔ)充一下，判斷當(dāng)前是否有excel應(yīng)用程序在運(yùn)行，使之更舒服一些：::CLSIDFromProgID(L"Excel.Application”,&clsid);//fromregistryif(::GetActiveObject(clsid,NULL,&pUnk)==S_OK){VERIFY(pUnk->QueryInterface(IID_IDispatch,(void**)&pDisp)==S_OK);ExcelApp.AttachDispatch(pDisp);pUnk->Release();}else{if(!ExcelApp.CreateDispatch("Excel.Application")){AfxMessageBox("Excelprogramnotfound");return0;}}作者：unionsoft2004-12-1312:36:09)以下是引用zhmary在2004-12-1011:30:10的發(fā)言：請(qǐng)問(wèn)各位高手，從哪里得onclick=Cswf()height=22alt=Flash圖片src="skins/default/ubb/swf.gif"width=23border=0>BJExcel.cp...Excel.cpp和Excel.h是從Excel的類(lèi)型庫(kù)中獲取的，類(lèi)型庫(kù)類(lèi)似C++中的頭文件，包括接口，方法，屬性的定義；類(lèi)型庫(kù)在Excel的安裝目錄可以找到，Excel的版本不同，這個(gè)類(lèi)型庫(kù)也不一樣，如下所示：Excel95andprior:xl5en32.olbExcel97:excel8.olbExcel2000:excel9.olbExcel2002:excel.exe具體的獲取方法:.使用VC++新建立一個(gè)基于MFC的EXE工程.點(diǎn)擊菜單”查看"-->"建立類(lèi)向?qū)А保藭r(shí)會(huì)彈全"MFCClassWizard”對(duì)話(huà)框.點(diǎn)擊"AddClass"-->"Fromatypelibray"，指定Excel的typelibray，在Excel的安裝目錄下可以找到，^口:"D:\\MicrosoftOffice\\Office\\EXCEL9.OLB"4.在彈出的對(duì)話(huà)框中選擇所需的類(lèi)，按"確定”，Excel.cpp和Excel.h就產(chǎn)生了。作者：yfy20032004-12-2317:41:57)lpDisp=books.Open("C:\\\\1.xls”,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional);編譯顯示錯(cuò)誤：errorC2660:'Open':functiondoesnottake13parameters作者：xux46182005-4-68:48:51)請(qǐng)問(wèn)怎樣才能增加一個(gè)工作表？作者：夢(mèng)幻神話(huà)2005-4-615:50:40)Workbooks.AttachDispatch(ExcelApp.GetWorkbooks());Workbook.AttachDispatch(Workbooks.Open(FileName,covOptional,covOptional,covOptional,covOptional,covOptional, covOptional,covOptional, covOptional,covOptional,covOptional,covOptional,covOptional));Worksheets.AttachDispatch(Workbook.GetWorksheets());Worksheet.AttachDispatch(Worksheets.GetItem((COleVriant((long)1))));Range.AttachDispatch(Worksheet.GetCells());iCell.AttachDispatch(Range.GetItem(COleVariant((long)2),COleVariant((long)2)).pdispVal);vResult=iCell.GetMergeCells();if(vResult.boolVal==-1){AfxMessageBox("Yes");Range.AttachDispatch(iCell.GetRows());longrow_num=Range.GetCount();Range.AttachDispatch(iCell.GetColumns());longcol_num=Range.GetCount();CStringstr;str.Format("%dx%d",row_num,col_num);AfxMessageBox(str);}請(qǐng)教：為什么str得到的結(jié)果都是1X17(求合并單元格的原始行數(shù)和列數(shù))。謝謝。。。。。。。。。。作者：unionsoft2005-4-910:13:25)首先你的Cells(2,2)是否處于合并單元格中其次，你缺少了個(gè)關(guān)鍵語(yǔ)句：iCell.GetMergeArea())，你可以參考下面的語(yǔ)句RangeUnionRange;UnionRange.AttachDispatch(iCell.GetMergeArea());〃先要獲取合并區(qū)域range.AttachDispatch(UnionRange.GetRows());longiRowNum=range.GetCount(); 〃合并單元格行數(shù)range.AttachDispatch(UnionRange.GetColumns());longiColNum=Range.GetCount(); 〃合并單元格列數(shù)我就想在excel里實(shí)現(xiàn),第一行為標(biāo)題(居中)第二行為時(shí)間(左對(duì)齊),下面為9列數(shù)據(jù).最后一行為簽名(左對(duì)齊)怎么辦？回復(fù)更多評(píng)論#re:［轉(zhuǎn)］在VC中徹底玩轉(zhuǎn)Excel11-2617:24I好學(xué)者修改字體顏色的那段漏了一句，應(yīng)為：//*****〃設(shè)置第一個(gè)單元格字體顏色:紅色Fontfont;range.AttachDispatch(sheet.GetCells());range.AttachDispatch((range.GetItem(COleVariant(long(1)),COleVariant(long(1)))).pdispVal);font.AttachDispatch(range.GetFont());font.SetColor(COleVariant((long)0xFF0000));這樣改了還是不能用,Font是哪兒來(lái)的哦！回復(fù)更多評(píng)論re:［轉(zhuǎn)］在VC中徹底玩轉(zhuǎn)Excel10-1915:11I唐特Font和_Application,Workbooks等等這些東西一樣，是打開(kāi)excel.exe(excel9.olb)的時(shí)候添加的class回復(fù)更多評(píng)論re:［轉(zhuǎn)］在VC中徹底玩轉(zhuǎn)Excel01-1611:19|HU請(qǐng)教一個(gè)問(wèn)題，現(xiàn)在出現(xiàn)了OFFICE2007,在2000中做的程序，在2007下無(wú)法啟動(dòng)EXCEL服務(wù)，就是CreateDispatch失敗。但是，運(yùn)行的環(huán)境既有2007，也會(huì)有老版本的EXCEL，這種情況下，怎么辦？怎么讓程序更有通用性？謝謝?。。。?回復(fù)更多評(píng)論re:［轉(zhuǎn)］在VC中徹底玩轉(zhuǎn)Excel2008-01-2510:55|想飛的星期請(qǐng)問(wèn)怎么知道我createdispatch出來(lái)的excel已經(jīng)關(guān)閉了？？我還想每次重用同一個(gè)excel進(jìn)程，怎么辦？回復(fù)更多評(píng)論re:［轉(zhuǎn)］在VC中徹底玩轉(zhuǎn)Excel2008-03-2814:56|lamorak你好，請(qǐng)教一下，我在做數(shù)據(jù)導(dǎo)入到excel中的程序，將3個(gè)文件的數(shù)據(jù)導(dǎo)入沒(méi)有問(wèn)題，我為了做測(cè)試，將這3個(gè)文件復(fù)制了44次，就是145個(gè)文件的數(shù)據(jù)，總共有6702314行，200M的數(shù)據(jù)量，轉(zhuǎn)換到75M數(shù)據(jù)的時(shí)候，Excel就不給轉(zhuǎn)了，就彈出Excel中“value，range，GetIDsOfNames”函數(shù)出錯(cuò)，Excel大小有限制嗎？我這個(gè)差不多就是做9X3文件循環(huán)數(shù)據(jù)插入，第10個(gè)循環(huán)就錯(cuò)了。我每次轉(zhuǎn)一個(gè)文件后都加了Sleep(2000)，能幫忙解決一下嗎？回復(fù)更多評(píng)論本文來(lái)自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處/vicozo/archive/2009/04/12/4067804.aspxVC操作excel表格/圖表2010-07-3121:06VC操作excel表格（一）TherequirefilesWhenyoustarttoprograminVC,youmusthavethelibfilesofExcel.Thenexttablewillshowthat:OfficeVersionRequirementfilesOtherfilesDefaultDirOffice97Excel8.olbExcel.exeC:\programfiles\MSOffice\officeOffice2000Excel9.olbExcel.exeC:\programfiles\MSOffice\officeOffice2002Excel.exeC:\programfiles\MSOffice\office10OfficeXPExcel.exeC:\programfiles\MSOffice\office10Beforeyoustartwork,youmustbesurethatcorrespondingfilesarethere.TheExcelObjectmodelBeforeyoustarttowork,anotherthingmustbeknown,itistheExcelobjectmodel.IttellsusthatwhatareincludedinExcel?Letusgo.BasicmodelExcelprogramconsistsofeightmainparts;theseareapplicationobject,workbooksobject,workbookobject,worksheetsobject,worksheetobject,rangeobject,charts,chart.NextpicturedescribestherelationshipofmainobjectsinExcel:Notice:Infact,therearemanyotherobjects,butweoftenuseaboveobjects.YoucanfindtheMSDN\officedevelopment\officeXXXX\excelforgettingothers.ApplicationObjectApplicationobjectisdefinedinExceltypelibraryas_Applicationclass.ApplicationistheExcelitself,themainfunctionsare:l ThesettingandoptionsofExcelapplicationlevell SomemethodswhichreturntotopobjectWecangetWorkbooks/workbookobjectfrom_Applicationobjectlikethis:_Applicationapp;app.get_workbooks();WorkbooksobjectWorkbooksistheaggregateofallopenbooks.Itiscontainerobject,theelementisworkbookobject.Themainfunctionsare:l Returntheworkbookobjectbyindex.l Addanewemptyworkbook.l Openafile,andcreateanewworkbookforthisfile.Example:Workbooksbooks=app.get_workbooks();WorkbooknewBook=books.add(votp);newBook=books.Open(".\\1.xls”，...)；newBook=books.get_Item(ColVariant((short)1));WorkbookObjectWorkbookobjectisaworkbook.Itincludesworksheetandchart.Themainfunctionsare:l Activateaworkbookl Returnaworksheets or chartsl Returntheactivesheetl Savetofile(XLS)Example:newBook.Activate();WorkSheetssheets=newBook.get_WorkSheets();newBook.get_Charts();newBook.get_ActiveChart();newBook.get_ActiveSheet();WorksheetsobjectWorksheetsisaaggregateobjecttoo.Everyelementisworksheetobject.Infact,thereisaSheetsobject,itisaggregateobjecttoo,buttheelementmaybeaworksheetobjectorachartobject.Themainfunctionsare:l Addnewworksheetl GetworksheetbyindexExample:Worksheetsheet=sheets.add(vopt,vopt,vopt,COleVariant((short)1));sheet=sheets.get_Item(index);WorksheetobjectWorkSheetobjectisaworksheetofExcel.ItisthememberofWorksheetsandsheets.Themainfunctionsare:

l Alloperationonworksheet,likepassword.l ReturntheRangeobjectbycellarea.l ActivateitselfExample:sheet.Protect();sheet.put_Name("Mycreatesheet”)；RangetoRng二sheet.get_Range(COleVariant("A1:B3”)，vopt);sheet.Activate();RangeobjectRangeobjectisacell,orarow,oracolumn,oraarea(itmaybeorsomecontinuouscells),ora3Darea.Themainfunctionsare:l Getand set the cells valuel Getand set the cells formulal Offsetl Unionl Font,autofit,andso on…Example:oRng.get_Value();oRng.put_Value(COleVariant("Date"));oRange=oRange.get_Resize(COleVariant((long)20),COleVariant((long)1));oRange.put_Formula(COleVariant(〃二C2*0.07"));ChartsacellitChartsisaaggregateobject,itincludesallchartsinworkbook,butdoesn’tcontainembeddedcharts.acellitThemainfunctionsare:l Getchartbyindexl Addanewchartto workbookl PrintchartExample:Chartscharts=newBook.get_Charts();Charts.get_Item(index);ChartnewChart=charts.add(vopt,vopt,COleVariant((short)1));ChartChartrepresentschart,itcanbeaembeddedchartorasinglechart.Themainfunctions:l Set the basic attributes, e.g.,name,title,active.l Set the chart typel Set the chart datasourceExample:newChart.put_Name("Mychart");newChart.put_ChartType((long)xlLineMarkers);RangeoRang;oRang=newSheet.get_Range(COleVariant("C2:D21”),vOpt);newChart.SetSourceData(oRang,COleVariant((short)2));CharttypeThestepofcreatingNowletusstarttocreateaprojectandwriteprogramforcreatinganExcelsheetandanExcelchart.Wedescribethatintwosteps.Thefirststepishowtoimporttypelibrariesandwhattypelibrariesareimportedintoproject,andthesecondstepishowtocode.Thesecondwillbedescribedinnextsection.HowandwhatWhatlibrariesareimportedinto?Differentofficeversionhasdifferenttypelibraries,seeaboveform.TherearesomedifferenceofhowtoimportbetweenVC6.0andVC7.0.VC6.0CreateaMFCexeprojectSelectMenu“View->ClassWizard”Selectoptioncard“Automation->AddClass->fromtypelibrary”Selectanexcel9.olb/excel8.olb/excel.exefile,whichoftenlocatesunderdirC:\Programfiles\Office\.Selectspecifiedclasses,e.g._Application,Workbooks,_Workbook,Worksheets,_Worksheet,Range,thenclickOK,andafilenamedexcel9.h/excel8.hwillbecreated.Thatfileincludesthedefinitionofaboveclasses.VC7.0CreateaMFCEXEproject,singledocument,andcontainerSelect menu“Project->ClassWizard”Select “Classintypelibrary”Click "Open”buttonSetthesourceofclassas"File”，andselectthefile.SelecttheinterfacesthatyouwanttoaddyourprojectfromleftlistandinsertthemintorightlistSettheimportfilename(excel.h),thenclickthe"Complete”button.vc操作excel表格（二）ProgramwithcreateExcelfileWesupposethatallclassedaredefinedinexcel.h,sowecanusethoseclassesbyonlyincludingexcel.h.Weaddtwomenuitems,oneforcreatingspecifiedExcelsheetnamedID_NewSheet,theotherforcreatingchartnameID_NewChart.Thesteps:AddtwomenuitemsAddtwomessagemapfunctionsforabovetwomenuitemsIncludeexcel.hfileinthefileyoudefineabovetwomessagemapfunctions.ImplementthetwofunctionsProgramwithsheetThenextisawayofID_NewSheetmessagemapfunctionimplementation://Excelobjectmodel_Applicationapp;Workbooksbooks;_WorkbooknewBook;Worksheetssheets;_WorksheetoSheet,firstSheet;Chartscharts;_Chartchart,firstChart;Rangerange;RangeiCell;LPDISPATCHlpDisp;COleVariantvResult;COleVariantvOpt((long)DISP_E_PARAMNOTFOUND,VT_ERROR);//CreateExcelserver(startExcel)if(!app.CreateDispatch(〃Excel.Application〃))(AfxMessageBox("Can’tstartExcelserver!");return;}app.put_Visible(TRUE);//setExcelvisibleapp.put_UserControl(TRUE);//usercanoperateExcel//newabookbooks.AttachDispatch(app.get_Workbooks());newBook=books.Add(vOpt);//Getworksheetsandgetthefirstworksheetsheets=newBook.get_Worksheets();oSheet=sheets.get_Item(COleVariant((short)1));//***ADDDATATOTHEWORKSHEET//AddHeaderstoRow1oftheworksheetRangeoRange;oRange=oSheet.get_Range(COleVariant("A1"),vOpt);oRange.put_Value2(COleVariant(〃Date〃))；oRange=oSheet.get_Range(COleVariant("B1"),vOpt);oRange.put_Value2(COleVariant("Order#"));oRange=oSheet.get_Range(COleVariant("C1"),vOpt);oRange=oSheet.get_Range(COleVariant("D1"),vOpt);oRange.put_Value2(COleVariant(〃Tax〃))；//CreateasafearraythatisNUMROWSx3-//column1willcontaindatescolumn2willcontainstrings//andcolumn3willcontainnumbersCOleSafeArraysa;DWORDdwElements[2];dwElements[0]=20;//NumberofrowsdwElements[1]=3; //Numberofcolumnssa.Create(VT_VARIANT,2,dwElements);//Populatethesafearraywiththedatalongindex[2];longlRow;COleVariantvTemp;COleDateTimevDateTime;CStrings;for(lRow=0;lRow<=20-1;lRow++)(index[0]=lRow;//Fillthefirstcolumnwithdatesindex[1]=0;vDateTime.SetDate(1999,rand()%12,rand()%28);sa.PutElement(index,(COleVariant)vDateTime);//Fillthesecondcolumnwithstringsindex[1]=1;s.Format("ORDR%d",lRow+1000);vTemp=s;sa.PutElement(index,vTemp);//Fillthethirdcolumnwithnumbersindex[1]=2;vTemp=(long)rand();sa.PutElement(index,vTemp);}//Fillarange,startingatA2withthedatain//thesafearrayoRange=oSheet.get_Range(COleVariant("A2"),vOpt);oRange=oRange.get_Resize(COleVariant((short)20),COleVariant((short)3));oRange.put_Value2(sa);sa.Detach();//***ADDFORMULASTOTHEWORKSHEET//Fillthefourthcolumnwithaformulatocomputethe//salestax.Notethattheformulausesa"relative"http://cellreferencesothatitfillsproperly.oRange=oSheet.get_Range(COleVariant("D2"),vOpt);oRange=oRange.get_Resize(COleVariant((long)20),COleVariant((long)1));oRange.put_Formula(COleVariant(〃二C2*0.07"