VC uses OLE to read and write Excel

To create a project a few days ago, you need to read the data in Excel. I found that there are two ways to query data on the Internet. First, use an Excel table as a database to read and write using ODBC. This operation is similar to an Access database operation. However, this method is less efficient. Another method is OLE technology (Object
Linking and Embedding, object connection and embedding ). Ole is an object-oriented technology that allows you to develop reusable software components (COM ). This article describes how to read and write Excel using OLE technology.

The environments used in this article are Windows XP, vs2008, and excel2007.

1) Click "project"-> "add class"-> "MFC class in typelib"-> "OK" in your created VC project"

2) A "add Class Wizard from Type Library" dialog box appears, select the "file" option, and select the file location: C: \ Program Files \ Microsoft Office \ office12 \ excel. EXE adds the _ application, _ workbook, _ worksheet, range, ranges, workbooks, and worksheets interfaces in the interface list to the right. Click "finish ".

3) Add a new C ++ class to the VC project and name it cexcel. Add the following header files to the Excel. h file:

# Include "capplication. H"

# Include "crange. H"

# Include "cranges. H"

# Include "cworkbook. H"

# Include "cworkbooks. H"

# Include "cworksheet. H"

# Include "cworksheets. H"

4) Open the above seven header files in sequence, and set # import "C: \ Program Files \ Microsoft Office \ office12 \ excel in each file. comment out the line EXE "no_namespace (otherwise, an error will be reported later ). In addition, set the 335th lines of variant in the crange. h file
Change the name of dialogbox in dialogbox (). For example, you can change it to exceldialogbox ().

Now the preparation is complete, and the rest is to encapsulate the Excel operations into a class. First, create an Excel server. Then, based on the functions provided in each header file, open the Excel file, obtain the workbook, obtain the worksheet, select a cell, and obtain the value of the cell. Or select a region (range) and output the values in the region to a two-dimensional array.

#include "CApplication.h"#include "CRange.h"#include "CRanges.h"#include "CWorkbook.h"#include "CWorkbooks.h"#include "CWorksheet.h"#include "CWorksheets.h"#pragma onceclass CExcel{public:CExcel(void);~CExcel(void);void OpenExcel(CString path);void OpenSheet(CString SheetName);CString GetCellValue(long row,long col);CString GetCellValueByName(CString rowName,CString colName);private:CApplication app;CWorkbooks books;CWorkbook book;CWorksheets sheets;CWorksheet sheet;CRange range;LPDISPATCH lpDisp;};

# Include "stdafx. H" # include "Excel. H" cexcel: cexcel (void) {If (! App. createdispatch (_ T ("Excel. application"), null) {afxmessagebox (_ T ("failed to start the Excel server! ");} Lpdisp = NULL;} cexcel ::~ Cexcel (void) {app. releasedispatch (); books. releasedispatch (); book. releasedispatch (); sheets. releasedispatch (); sheet. releasedispatch (); range. releasedispatch ();} void cexcel: openexcel (cstring path) {books. attachdispatch (App. get_workbooks (), 1); colevariant varpath (PATH); book. attachdispatch (books. add (varpath);} void cexcel: opensheet (cstring sheetname) {sheets. attachdispatch (book. get_sheets (), true); colevariant var2 (sheetname); sheet. attachdispatch (sheets. get_item (var2), true);} cstring cexcel: getcellvalue (long row, long col) {_ variant_t varrow (ROW); _ variant_t varcol (COL); colevariant value; range. attachdispatch (sheet. get_cells (), true); value = range. get_item (varrow, varcol); // The returned type is vt_dispatch. This is a pointer range. attachdispatch (value. pdispval, true); variant value2 = range. get_text (); cstring strvalue = value2.bstrval; return strvalue;} cstring cexcel: getcellvaluebyname (cstring rowname, cstring colname) {colevariant value; cstring strvalue; long row = 0, col = 0; long re_row = 0, re_col = 0; range. attachdispatch (sheet. get_cells (), true); For (ROW = 1, Col = 1; Col <range. get_column (); Col ++) {value = range. get_item (_ variant_t (ROW), _ variant_t (COL); // The returned type is vt_dispatch. This is a pointer range. attachdispatch (value. pdispval, true); variant value2 = range. get_text (); cstring strvalue = value2.bstrval; If (strvalue = colname) break;} re_col = Col; For (ROW = 1, row = 1; row <range. get_row (); row ++) {value = range. get_item (_ variant_t (ROW), _ variant_t (COL); // The returned type is vt_dispatch. This is a pointer range. attachdispatch (value. pdispval, true); variant value2 = range. get_text (); cstring strvalue = value2.bstrval; If (strvalue = rowname) break;} re_row = row; return getcellvalue (re_row, re_col );}