Operations on Excel tables using VC

For more information, see the original website:

Http://www.cnblogs.com/xianyunhe/archive/2011/09/25/2190485.html

There are many ways to Operate Excel tables through VC, such as through ODBC database, through parsing Excel table files, through OLE/COM. This article mainly studies how to operate Excel tables through OLE/COM.

 

The application environment of the source code in this article is described as follows:

Windows XP SP3

Microsoft Visual maxcompute 2010

Microsoft Office Excel 2007

 

1. Added support for OLE/COM.

First, the application must add support for OLE/COM to import the OLE/COM component.

This article uses the MFC Dialog Box program. By selecting the automation option in the Project Creation wizard, you can automatically add the corresponding header file and Ole library initialization code for the program.

By viewing the source code, you can know that the header files added to many classes of OLE/COM are added to the stdafx. h header files.

# Include <afxdisp. h> // MFC automation class

At the same time, the initialization code of OLE/COM is added to the initinstance function of the application class, as shown below:

// Initialize the OLE Library

If (! Afxoleinit ())

{

Afxmessagebox (idp_ole_init_failed );

Return false;

}
 

2. Import and encapsulate interfaces in Excel

As an OLE/COM library plug-in, Excel defines various interactive interfaces, which are cross-language interfaces. VC can import these interfaces and use them to perform Excel operations.

As this article only focuses on reading data from Excel tables, it focuses on several interfaces such as _ application, workbooks, _ workbook, worksheets, _ worksheet, and range. The attributes and methods of various interfaces of Excel can be queried through office development of msdn.

The procedure for importing OLE/COM components to vs2010 is as follows: Project-> Class Wizard-> Add class-> MFC class from typelib. select the region of the imported group, that is, the path of excel.exe, and then select

The interface in the Excel database to import.

After the interface is imported, vs2010 automatically creates an implementation class for the imported interface to implement interface attributes and methods. Because the Standard C ++ has no attribute accessors, only one or two access functions can be added for Attribute access, you can add get _ and put _ prefixes before the attribute name to perform read/write operations on the attribute. That is, the VC automatically encapsulates the C ++ class interface.

 

The class and header file corresponding to the interface imported in this article are described as follows:

 

Excel Interface	Import class	Header file	Description
_ Application	Capplicaton	Application. h	Excel application.
Workbooks	Cworkbooks	Workbooks. h	The workbook container contains all workbooks opened by the Excel application.
_ Workbook	Cworkbook	Workbook. h	A single workbook.
Worksheets	Cworksheets	Worksheets. h	The container of the sheet table in a single workbook, including all sheet in the workbook.
_ Worksheet	Cworksheet	Worksheet. h	A single sheet.
Range	Crange	Range. h	You can operate a certain number of cells on one or more cells.

 

3. Import the entire Excel database type

The corresponding class of the interface only encapsulates the attributes and methods of the interface, while the data types in Excel, such as enumeration types, are not usable. Therefore, to facilitate Excel operations, you also need to import the Excel data type.

By viewing the header file corresponding to the import interface, you can find that all header files of the import interface will have this line:

# Import "d :\\ Program Files \ Microsoft Office \ office12 \ excel. EXE" no_namespace

This line of code imports the entire Excel database type to the project.

The import code automatically generated by vs2010 has the following problems:

(1) If multiple interfaces are imported, each header file will import the type library once. If multiple header files are referenced, the Type Library will be repeatedly imported.

(2) Some Types in the Excel Type Library will conflict with some types in the MFC class library.

(3) some types of Excel-type databases are related to some other office and VB libraries. If you do not import them, these types cannot be used ..

The solution to the above three problems is as follows:

(1) only import the Excel Type Library in the header file corresponding to the _ application interface.

(2) Rename the conflicting type.

(3) Before importing an Excel database, import the relevant libraries of office and VB.

The code for the modified Import Type Library is as follows:

 

/* Import the Office Type Library */

# Import "C :\\ Program Files \ common files \ Microsoft shared \ office12 \ MSO. dll "\

Rename ("RGB", "msorgb ")\

Rename ("documentproperties", "msodocumentproperties ")

Using namespace office;

 

/* Import the VB Type Library */

# Import "C: \ Program Files \ common files \ Microsoft shared \ VBA \ vba6 \ vbe6ext. olb"

Using namespace vbide;

 

/* Import an Excel database */

# Import "d :\\ Program Files \ Microsoft Office \ office12 \ excel. EXE "\

Rename ("dialogbox", "exceldialogbox ")\

Rename ("RGB", "excelrgb ")\

Rename ("copyfile", "excelcopyfile ")\

Rename ("replacetext", "excelreplacetext ")\

No_auto_exclude

Using namespace Excel;

 

After the program is compiled, three files, MSO. tlh, vbe6ext. tlh, and Excel. tlh, are generated in the debug or release directory. By opening the file, we can see that the namespace of the three files is office, vbide, and excel. After the entire Type Library of Excel is imported, all types in Excel can be used.

 

4. Excel operations

The main steps to Operate Excel are as follows:

(1) Create an Excel application.

(2) Get the workbook container.

(3) open a workbook or create a workbook.

(4) Get the worksheet container in the workbook.

(5) open a worksheet or create a worksheet.

(6) read and write the cells in the worksheet through range.

(7) Save the Excel file.

(8) release resources.

 

5. Batch Processing of EXCEL tables

VC uses OLE/COM to Operate Excel through the component technology between processes. Therefore, every time you read and write cells in an Excel file, you must switch between processes. When the data volume is large and one cell is read, the main time is spent in process switching. Therefore, reading multiple cells can effectively improve the running efficiency of the program.

Read and Write operations on multiple cells can be completed through the following two member functions in crange.

Variant get_value2 ();

Void put_value2 (variant & newvalue );

If newvalue is input as a two-dimensional array, the values of multiple cells can be written to excel at a time.

For more information about how to implement 2D data in variant, see

Http://www.cnblogs.com/xianyunhe/archive/2011/09/13/2174703.html

Of course, before performing operations on the crange class, you must set the cells corresponding to the crange class.

 

6. save an Excel table

(1) If you want to save the opened workbook, you can use the SAVE Function of the cworkbook class to save the workbook. The original file will be overwritten.

(2) Use the saveas function of the cworkbook class to create a new workbook or save it.

Saveas has many parameters. Among them, 1st parameters are set to the path of the file to be saved; 2nd parameters are set to the file format, you can view the enumerated type xlfileformat in msdn to understand the Excel file format. After testing, in the testing environment used in this article, the file format of excel2003 is xlexcel8, and the file format of excel2007 is xlexcel4.

 

7. Obtain the current Excel version

You can use the get_version function of capplication to obtain the Excel version. The master version number of excel2007 is 12, and the master version number of excel2003 is 11.

 

8. sample source code

The main code is as follows:

View code example (lvs_report | lvs_ex_fullrowselect); capplication excelapp; cworkbooks books; cworkbook book; cworksheets sheets; cworksheet sheet; crange range; lpdispatch lpdisp = NULL; // create an Excel server (start Excel) if (! Excelapp. createdispatch (_ T ("Excel. application"), null) {afxmessagebox (_ T ("failed to start the Excel server! "); Return-1;}/* determine the current Excel version */cstring strexcelversion = excelapp. get_version (); int istart = 0; strexcelversion = strexcelversion. tokenize (_ T (". "), istart); If (_ T (" 11 ") = strexcelversion) {afxmessagebox (_ T (" the current Excel version is 2003. ");} Else if (_ T (" 12 ") = strexcelversion) {afxmessagebox (_ T (" the current Excel version is 2007. ");} Else {afxmessagebox (_ T (" the current Excel version is another version. ");} Excelapp. put_visible (true); excelapp. put_usercontrol (false);/* Get the workbook container */books. attachdispatch (excelapp. get_workbooks ();/* open a workbook. If it does not exist, add a workbook */cstring strbookpath = _ T ("C: \ tmp.xls "); try {/* open a workbook */lpdisp = books. open (strbookpath, vtmissing, and vtmissing); book. attachdispatch (lpdisp);} catch (...) {/* Add a new workbook */lpdisp = books. add (vtmissing); book. attachdispatch (lpdisp);}/* Get the sheet container in the workbook */sheets. attachdispatch (book. get_sheets ();/* open a sheet. If it does not exist, add a new sheet */cstring strsheetname = _ T ("newsheet "); try {/* open an existing sheet */lpdisp = sheets. get_item (_ variant_t (strsheetname); sheet. attachdispatch (lpdisp);} catch (...) {/* Create a new sheet */lpdisp = sheets. add (vtmissing, vtmissing, _ variant_t (long) 1), vtmissing); sheet. attachdispatch (lpdisp); sheet. put_name (strsheetname);} system ("pause");/* write multiple cells to the sheet. The size is 10*10 */lpdisp = sheet. get_range (_ variant_t ("A1"), _ variant_t ("j10"); range. attachdispatch (lpdisp); vartype VT = vt_i4;/* array element type, long */safearraybound sabwrite [2]; /* defines the dimension of the array and the starting value of the lower mark */sabwrite [0]. celements = 10; sabwrite [0]. llbound = 0; sabwrite [1]. celements = 10; sabwrite [1]. llbound = 0; colesafearray olesawrite; olesawrite. create (Vt, sizeof (sabwrite)/sizeof (safearraybound), sabwrite);/* indirectly assigns values to elements of a two-dimensional array by pointing to an array pointer */long (* parray) [2] = NULL; olesawrite. accessdata (void **) & parray); memset (parray, 0, sabwrite [0]. celements * sabwrite [1]. celements * sizeof (long);/* release pointer to array */olesawrite. unaccessdata (); parray = NULL;/* assign values to elements of a two-dimensional array one by one */long index [2] = {0, 0}; long lfirstlbound = 0; long lfirstubound = 0; long lsecondlbound = 0; long lsecondubound = 0; olesawrite. getlbound (1, & lfirstlbound); olesawrite. getubound (1, & lfirstubound); olesawrite. getlbound (2, & lsecondlbound); olesawrite. getubound (2, & lsecondubound); For (long I = lfirstlbound; I <= lfirstubound; I ++) {index [0] = I; for (long J = lsecondlbound; j <= lsecondubound; j ++) {index [1] = J; long lelement = I * sabwrite [1]. celements + J; olesawrite. putelement (index, & lelement) ;}/ * converts the colesawritefearray variable to variant and writes it to the Excel table */variant varwrite = (variant) olesawrite; range. put_value2 (varwrite); System ("pause");/* select the format of the file to be saved Based on the file suffix */cstring strsaveasname = _ T ("C: \ new.xlsx "); cstring strsuffix = strsaveasname. mid (strsaveasname. reversefind (_ T ('. '); xlfileformat newfileformat = xlopenxmlworkbook; If (0 = strsuffix. comparenocase (_ T (". xls ") {newfileformat = xlexcel8;} book. saveas (_ variant_t (strsaveasname), _ variant_t (long) newfileformat), vtmissing, 0, vtmissing, vtmissing ); system ("pause");/* read the values of multiple cells in the Excel table, and display */variant varread = range in listctrl. get_value2 (); colesafearray olesaread (varread); variant varitem; cstring stritem; lfirstlbound = 0; lfirstubound = 0; lsecondlbound = 0; lsecondubound = 0; olesaread. getlbound (1, & lfirstlbound); olesaread. getubound (1, & lfirstubound); olesaread. getlbound (2, & lsecondlbound); olesaread. getubound (2, & lsecondubound); memset (index, 0, 2 * sizeof (long); m_listctrl.insertcolumn (0, _ T (""), 0,100 ); for (long J = lsecondlbound; j <= lsecondubound; j ++) {cstring strcolname = _ T (""); strcolname. format (_ T ("% d"), J); m_listctrl.insertcolumn (J, strcolname, 0,100) ;}for (long I = lfirstlbound; I <= lfirstubound; I ++) {cstring strrowname = _ T (""); strrowname. format (_ T ("% d"), I); m_listctrl.insertitem (I-1, strrowname); index [0] = I; for (long J = lsecondlbound; j <= lsecondubound; j ++) {index [1] = J; olesaread. getelement (index, & varitem); Switch (varitem. vt) {Case vt_r8: {stritem. format (_ T ("% d"), (INT) varitem. dblval);} case vt_bstr: {stritem = varitem. bstrval;} case vt_i4: {stritem. format (_ T ("% lD"), (INT) varitem. lval);} default: {}} m_listctrl.setitemtext (I-1, J, stritem) ;}/ * release resources */sheet. releasedispatch (); sheets. releasedispatch (); book. releasedispatch (); books. releasedispatch (); excelapp. quit (); excelapp. releasedispatch ();

The download link of the sample source code project file is as follows:

Http://files.cnblogs.com/xianyunhe/ReadWriteExcel.rar

 

References

(1) http://www.cnblogs.com/xianyunhe/archive/2011/09/13/2174703.html

(2) http://hfp0601.blog.163.com/blog/static/228483522011031104718762/

(3) http://www.cppblog.com/sleepwom/archive/2009/10/03/97804.html

(4) http://www.rondebruin.nl/saveas.htm