How to use VS to manipulate Excel使用MFC读写Excel

1. Build project with MFC Dialogue;

2. Open (ctrl + alt + x) class wizard:

3. Add class - From a type library

4. Choose From File:

5. Find the Road of excel.exe

5. Add some classes to the project:

6. Press F7 to test whether the code works;

7. Delete or comment the code in each of the added in files.

1 #import "C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" no_namespace

Or, you will face the problem when compiling.

8. If you still can not pass the process of compiling, in a class that I do not remember, change Dialog to _dialog, which will be very beneficial.

9. Now you will be able to use it to control Excel.

10. We can read Excel with code like this:

 1 ::CoInitialize(NULL);
 2     CApplication app;
 3     CWorkbooks books;
 4     CWorkbook book;
 5     CWorksheets sheets;
 6     CWorksheet sheet;
 7     CRange range;
 8     CRange iCell;
 9     LPDISPATCH lpDisp;
10     COleVariant vResult;
11     COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
12 
13     //if can not connect to Excel
14     if(!app.CreateDispatch("Excel.Application"))
15     {
16         AfxMessageBox("Can not start Excel server!");
17         return;
18     }
19 
20  
21 
22     //*****
23     //open an Excel file
24     books.AttachDispatch(app.get_Workbooks());
25 
26 
27     lpDisp = books.Open("C:\\Documents and Settings\\Administrator.PC-201202231058\\桌面\\excel\\excel\\in.xls",
28         covOptional, covOptional, covOptional, covOptional, covOptional,
29         covOptional, covOptional, covOptional, covOptional, covOptional,
30         covOptional, covOptional, covOptional, covOptional );
31 
32 
33     //*****
34     //get Workbook
35     book.AttachDispatch(lpDisp);
36 
37 
38     //*****
39     //get Worksheets
40     sheets.AttachDispatch(book.get_Worksheets());
41 
42  
43 
44     //*****
45     //get active sheet
46     //if this cell is still in editing, the program will wait
47     lpDisp=book.get_ActiveSheet();
48     sheet.AttachDispatch(lpDisp);
49 
50  
51 
52     //*****
53     //get the value of the first cell
54     range.AttachDispatch(sheet.get_Cells());
55     
56     // the number of 1 here can be changed to get values of other cells
57 range.AttachDispatch(range.get_Item(COleVariant((long)1),COleVariant((long)1)).pdispVal);
58     vResult =range.get_Value2();
59     CString str;
60     if(vResult.vt == VT_BSTR) //String
61     {
62     str=vResult.bstrVal;
63     }
64     else if (vResult.vt==VT_R8) //number
65     {
66     str.Format("%f",vResult.dblVal);
67     }
68 
69     else if(vResult.vt==VT_DATE) //time
70     {
71     SYSTEMTIME st;
72     //VariantTimeToSystemTime(&vResult.date, &st);
73     }
74     else if(vResult.vt==VT_EMPTY) //blank
75     {
76     str="";
77     }

11. To write a file, we can do like this:

 1     //Init COM
 2     ::CoInitialize(NULL);
 3 
 4     //Export to Excel
 5     CString sss,s1,s2,e1,e2,strSQL;
 6     CStringArray sa;
 7     
 8     
 9     CString fname,fname1, sheetname,s;
10 
11     
12     fname="C:\\Documents and Settings\\Administrator.PC-201202231058\\桌面\\excel\\excel\\out.xls";//得到要导出保存的路径及文件名
13 
14     //Define the objects
15     CApplication objApp;
16     CWorkbooks objBooks;
17     CWorkbook objBook;
18     CWorksheets objSheets;
19     CWorksheet objSheet;
20     CRange objRange,objRange1,objRange2;    
21 
22     COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
23     COleVariant covTrue((short)TRUE), covFalse((short)FALSE),\
24         varFormat((short)-4143),varCenter((short)-4108),varLeft((short)-4131),varText("TEXT",VT_BSTR),var,\
25         varRange1("A1",VT_BSTR),varRange2("D1",VT_BSTR);
26     
27     //Creat Excel
28     objApp.m_bAutoRelease=true;
29     if(!objApp.CreateDispatch("Excel.Application"))
30     {
31         AfxMessageBox("Failed to connect to Excel!");
32         return;
33     }
34     //get Workbooks
35     objBooks=objApp.get_Workbooks();
36 
37     //open Excel file
38     objBook.AttachDispatch(objBooks.Add(_variant_t("")));
39     objSheets=objBook.get_Sheets();
40 
41     
42     //Define as the first excel object
43     objSheet=objSheets.get_Item((_variant_t)short(1));
44     sheetname="sheetname";
45     objSheet.put_Name(sheetname);
46     objSheet.Activate();
47     objRange.AttachDispatch(objSheet.get_Cells(),true); 
48     
49     //Write to the cell A1
50     //If you wan to write to other cells, just change this flag 
51     s1.Format("A1");
52     e1=s1;
53     objRange1=objSheet.get_Range(_variant_t(s1),_variant_t(s1));
54 
55     //s is the thing to write
56     //if it is a string, s = "'111"
57     //if it is a number, s = "111"
58     s="111.11";
59 
60     //Write to cell
61     objRange1.put_FormulaR1C1(_variant_t(s));
62 
63         
64     //save
65     objBook.SaveAs(_variant_t(fname),varFormat,covOptional,covOptional,covOptional,covOptional,0,covOptional,covOptional,covOptional,covOptional,covOptional);
66 
67     objApp.Quit();
68     objRange.ReleaseDispatch();
69     objSheet.ReleaseDispatch();
70     objSheets.ReleaseDispatch();
71     objBook.ReleaseDispatch();
72     objBooks.ReleaseDispatch();

We need to use ::CoInitializeEx(NULL, COINIT_MULTITHREADED) instead of ::CoInitialize(NULL)

12. Do CoUninitialize(); when finishing.