1、VC2010對Excel的操作1. 創建新的C+工程創建基于對話框的MFC程序2. 添加庫、添加Excel類庫在工程名上右鍵，選擇“添加”“類”（或者點擊菜單欄的“項目”->“添加類”），選擇“TypeLib中的MFC類”（MFC Class From TypeLib）類來源選“注冊表”，在可用的類型庫中選擇“Microsoft Excel 11.0 Object Library<1.5>”在接口列表框中選擇需要的類，在此，我們選擇_Application,_Workbook,Worksheet,Range,Workbooks,Worksheets這六個就可以了。可以看到，六

2、個類被添加了進來。3. 修改頭文件分別將加進來的六個頭文件上面的“#import "C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE" no_namespace”注釋掉。4. 添加頭文件在stdAfx.h頭文件中添加加進來的這幾個頭文件#include "CApplication.h"#include "CRange.h"#include "CWorkbook.h"#include "CWorkbooks.h"#include "CWor

3、ksheet.h"#include "CWorksheets.h"5. 修改錯誤編譯，會出現兩個錯誤：crange.h(335): warning C4003: “DialogBoxW”宏的實參不足crange.h(335): error C2059: 語法錯誤:“,”雙擊錯誤提示，定位在錯誤行，VARIANT DialogBox()VARIANT result;InvokeHelper(0xf5, DISPATCH_METHOD, VT_VARIANT, (void*)&result, NULL);return result;將該函數名“DialogBo

4、x()”前面加“_”下劃線，即“_DialogBox()”，這樣就可以編譯成功了。6. 在對話框中添加一個編輯框，并為其關聯一CEdit類型變量m_Path，添加“打開”按鈕，實現打開一已經存在的Excel文件。并將路徑顯示在編輯框中。實現代碼如下。void CExportToExcelDlg:OnBnClickedButtonOpen()CFileDialog file(TRUE,NULL,NULL,OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,_T("EXCEL文件t(*.xls;*.xlsx)|*.xls;*.xlsx|"),AfxGetM

5、ainWnd();if(file.DoModal()=IDOK)CString strPath=file.GetPathName();m_Path.SetWindowTextW(strPath);CApplication app;CWorkbook book;CWorkbooks books;if (!app.CreateDispatch(_T("Excel.Application")MessageBox(_T("Error!Creat Excel Application Server Faile!");exit(1);/books.AttachDisp

6、atch(app.get_Workbooks(),true);/book.AttachDispatch(books.Add(_variant_t(strPath);books = app.get_Workbooks();book = books.Add(_variant_t(strPath);app.put_Visible(true);/結尾，釋放book.ReleaseDispatch(); books.ReleaseDispatch(); app.ReleaseDispatch();app.Quit();7. 在對話框中添加“寫入”按鈕，實現新建一Excel文件（存在則覆蓋），并向文件中寫

7、入數據。實現代碼如下。void CExportToExcelDlg:OnBnClickedButtonWrite()CString strFile = _T("D:WriteToExcelTest.xlsx");COleVariant covTrue(short)TRUE), covFalse(short)FALSE), covOptional(long)DISP_E_PARAMNOTFOUND, VT_ERROR); CApplication app;CWorkbook book;CWorkbooks books;CWorksheet sheet;CWorksheets

8、sheets;CRange range;CFont font;if (!app.CreateDispatch(_T("Excel.Application")MessageBox(_T("Error!Creat Excel Application Server Faile!");books = app.get_Workbooks();/books.AttachDispatch(app.get_Workbooks();可代替上面一行book = books.Add(covOptional);/book.AttachDispatch(books.Add(cov

9、Optional),true); 可代替上面一行sheets=book.get_Worksheets();/sheets.AttachDispatch(book.get_Worksheets(),true); 可代替上面一行sheet = sheets.get_Item(COleVariant(short)1);/sheet.AttachDispatch(sheets.get_Item(_variant_t("sheet1"),true); 可代替上面一行/下面兩行，是向A1中寫入"Yeah!I can write data to excel!"rang

10、e = sheet.get_Range(COleVariant(_T("A1"),COleVariant(_T("A1"); range.put_Value2(COleVariant(_T("Yeah!I can write data to excel!");/下面是向第二行的前十個單元格中輸入1到10，十個數字for(long i=1;i<11;i+)range.put_Item(_variant_t(long)2),_variant_t(long)i),_variant_t(long)i);/設置列寬range = shee

11、t.get_Range(COleVariant(_T("A1"),COleVariant(_T("J1");range.put_ColumnWidth(_variant_t(long)5);/顯示表格app.put_Visible(TRUE);/保存book.SaveCopyAs(COleVariant(strFile); book.put_Saved(true);/結尾，釋放book.ReleaseDispatch(); books.ReleaseDispatch(); app.ReleaseDispatch();app.Quit(); 8. 在對話框

12、中添加列表控件，并關聯變量m_Grid，并設置顯示為報表樣式。在對話框中添加“寫入列表”按鈕，實現將對話框中已有的表寫入到Excel中。實現代碼如下。在初始化函數中，先初始化列表。/設置列表視圖的擴展風格m_Grid.SetExtendedStyle(LVS_EX_FLATSB/扁平風格顯示滾動條|LVS_EX_FULLROWSELECT/允許整行選中|LVS_EX_HEADERDRAGDROP/允許整列拖動|LVS_EX_ONECLICKACTIVATE/單擊選中項|LVS_EX_GRIDLINES);/畫出網格線/設置表頭m_Grid.InsertColumn(0,_T("編號&

13、quot;),LVCFMT_LEFT,100,0);m_Grid.InsertColumn(1,_T("姓名"),LVCFMT_LEFT,100,1);m_Grid.InsertColumn(2,_T("所屬部門"),LVCFMT_LEFT,100,2);/向列表中插入數據int count = 0;m_Grid.InsertItem(count,_T("001");m_Grid.SetItemText(count,1,_T("張一");m_Grid.SetItemText(count+,2,_T("銷售

14、部");m_Grid.InsertItem(count,_T("002");m_Grid.SetItemText(count,1,_T("列二");m_Grid.SetItemText(count+,2,_T("研發部");m_Grid.InsertItem(count,_T("003");m_Grid.SetItemText(count,1,_T("宇三");m_Grid.SetItemText(count+,2,_T("采購部");m_Grid.InsertIt

15、em(count,_T("004");m_Grid.SetItemText(count,1,_T("宙四");m_Grid.SetItemText(count,2,_T("宣傳部");再編寫按鈕的響應函數void CExportToExcelDlg:OnBnClickedButtonWritelist()/ TODO: 在此添加控件通知處理程序代碼CString strFile = _T("D:WriteListToExcelTest.xlsx");COleVariant covTrue(short)TRUE),

16、covFalse(short)FALSE), covOptional(long)DISP_E_PARAMNOTFOUND, VT_ERROR); CApplication app;CWorkbook book;CWorkbooks books;CWorksheet sheet;CWorksheets sheets;CRange range;if (!app.CreateDispatch(_T("Excel.Application")MessageBox(_T("Error!Creat Excel Application Server Faile!");e

17、xit(1);books = app.get_Workbooks();book = books.Add(covOptional);sheets = book.get_Worksheets();sheet = sheets.get_Item(COleVariant(short)1);/得到全部Cells range.AttachDispatch(sheet.get_Cells(); CString sText=_T("編號"),_T("姓名"),_T("所屬部門");for (int setnum=0;setnum<m_Grid.

18、GetItemCount()+1;setnum+)for (int num=0;num<3;num+)if (!setnum)range.put_Item(_variant_t(long)(setnum+1),_variant_t(long)(num+1),_variant_t(sTextnum);elserange.put_Item(_variant_t(long)(setnum+1),_variant_t(long)(num+1),_variant_t(m_Grid.GetItemText(setnum-1,num);/保存book.SaveCopyAs(COleVariant(strFil