MFC vs2012 Office2013 Read and write Excel files

Recently busy a small project (with classmates), where the customer requirements not only read and write txt, but also can read and write Excel file, here thought very simple, the result ... Less nonsense, the process is as follows:

Author Environment: Win7 64+VS2012+OFFICE2013+MFC.

Description: Read and write Excel files, usually including 1. ODBC database implementation; 2. By parsing The Excel table file, 3. through the ole/com of several implementation methods. This paper mainly studies the operation of Excel table by ole/com.

Steps:

1. Create a new MFC dialog box. Note Check the automation, otherwise you will not be able to add the required library, resulting in failure to start the service and other errors. Some blogs say they need to join

1 if (! AfxOleInit ()) 2 {3    AfxMessageBox (idp_ole_init_failed); 4     return FALSE; 5 }

The author of the experiment found that add not to join all no problem, if someone behind the problem can join.

2.Add a basic 7 class to the project engineering (excel as ole/com library plug-ins, defines the interface of various types of interaction, These interfaces are cross-language interfaces. vc You can import these interfaces and pass the   interface to excel because this article is only concerned about excel table data Read, the main focus on 7 _ application, workbooks, _workbook, Worksheets_worksheet range, Font< Span lang= "ZH-CN" >

VS2012 The steps for importing an interface to a ole/com component are: MFC classes in the class-and type-Library of the Project, Class Wizard , first select the path to the component you want to import, which is the Excel.ex The path where E is located, and then select the interface in the Excel type library that you want to import . The component path is generally C:\Program Files\Microsoft Office\office15\excel.exe, similar in format.

3. the "#import" C:\\Program Files\\Microsoft Office\\office12\\excel is required after the import. EXE "No_namespace" comment out, and then add the header file: #include <afxdisp.h> to the above 7 files.

4. If error C2059 double click Error C2059, change Variant dialogbox () to Variant _DialogBox () to compile again, pass!!

5. Read and write Excel.

*dlg.h defining variables in the header file of a dialog box

  1  //   2   CApplication app;     CWorkbook book;    4   Cworkbooks books;    5   Cworksheet sheet;   6   cworksheets sheets;     CRange range;     8   Cmyfont font;   9   CRange cols;  10  lpdispatch lpdisp; 

A. Write Excel, add code to the OnInitDialog function inside the *dlg.cpp:

1 //Export2COleVariant covoptional ((Long) disp_e_paramnotfound,vt_error); 3     if(!app. CreateDispatch (_t ("Excel.Application")))4     {   5          This->messagebox (_t ("Unable to create Excel app! ")); 6         returnTRUE; 7     }   8Books =app.get_workbooks ();9     //open Excel, where pathname is the path name of the Excel tableTen      OneBook =Books.  ADD (covoptional);  ASheets =book.get_worksheets ();  -Sheet = Sheets.get_item (COleVariant ( Short)1));//get two cells with coordinates (a,1) and (b,1) -Range = Sheet.get_range (COleVariant (_t ("A1")), COleVariant (_t ("B1")));//set the cell class tolerance to Hello Exce theRange.put_value2 (COleVariant (_t ("Hello Excel")));//Select an entire column and set the width to adaptive -cols =Range.get_entirecolumn ();  - cols.  AutoFit ();  -     //set font to bold +Font =Range.get_font ();  -Font.put_bold (COleVariant ( Short) ( TRUE));  +     //get the coordinates (c,2) cell ARange = Sheet.get_range (COleVariant (_t ("C2")), COleVariant (_t ("C2")));  at     //Set Formula "=rand () *100000" -Range.put_formula (COleVariant (_t ("=rand () *100000")));  -     //set number format to currency type -Range.put_numberformat (COleVariant (_t ("$0.00")));  -     //Select an entire column and set the width to adaptive -cols =Range.get_entirecolumn (); in cols. AutoFit ();  -     //Show Excel Table to app.put_visible (TRUE);  +App.put_usercontrol (TRUE);

The results are as follows

B. Import Excel, also add code in the location of *dlg:

1 //Import2COleVariant covoptional ((Long) disp_e_paramnotfound,vt_error); 3     if(!app. CreateDispatch (_t ("Excel.Application")))4     {   5          This->messagebox (_t ("Unable to create Excel app! ")); 6         returnTRUE; 7     }   8Books =app.get_workbooks ();9     //open Excel, where pathname is the path name of the Excel tableTenLpdisp = books. Open (_t ("d:\\vs2012\\projects\\testexcelapp2\\testexcelapp2\\ working thin 1.xlsx"), covoptional, Covoptional,covoptional,covoptional,covoptional,covoptional,covoptional,covoptional, covoptional,covoptional,covoptional,covoptional,covoptional,covoptional); One Book . AttachDispatch (LPDISP);  ASheets =book.get_worksheets (); -Sheet = Sheets.get_item (COleVariant ( Short)1));  -     //get a cell with coordinates (a,1) theRange = Sheet.get_range (COleVariant (_t ("A1")), COleVariant (_t ("A1")));  -     //get the contents of a cell - COleVariant rValue; -RValue =COleVariant (Range.get_value2 ()); +     //convert to wide character - Rvalue.changetype (VT_BSTR); +     //conversion format, and output A      This-MessageBox (CString (Rvalue.bstrval)); at book.put_saved (TRUE); -App. Quit ();

The effect is as follows:

Excel content:

Read the first column of the first row of elements:

I just need to read the reading and writing functions of Excel, so just do this experiment, you can use their own projects, so we share a bit. What questions can leave a message, mutual encouragement!

MFC vs2012 Office2013 Read and write Excel files