Basic Excel operations using VC

Recently, due to work requirements, I had to operate Excel, including basic data input and output, font settings, color settings, cell merging, and fill color settings, of course, it also includes the generation of charts and the operations of online and offline operations. After a long time, the project was finally completed. Now I will record the learning operations in Excel and share them with you as my own summary.

1Excel Object Model (someone calls it a hierarchical structure)

Open an Excel worksheet, click "Tools"> "macro"> "Visual Basic Editor" to open the VB editor and open the help document, the "Microsoft Excel Object Model" under "Microsoft Excel Visual Basic Reference" shows the complete Excel hierarchy. Is it a bit similar to the inheritance chart of MFC? Using the help documentation, we can find some required knowledge. The following describes some classes:

_ Application: indicates the entire Excel application, which contains a workbook set.

Workbooks: A Workbook set that contains N workbooks)

_ Workbook: a workbook that contains a worksheet (sheets) set.

Worksheets: a worksheet set, which contains N worksheets.

_ Worksheet: Worksheet, Which is sheet1, sheet2, and sheet3 we see in Excel. It is the basic unit for us to operate Excel.

Range: This is a set of cells. We know that excel is composed of cells. You can use range to operate cells.

Font: used to set the font, color, font size, and bold size of cells.

Interior: Set the background color

Boards: Set the borders of all cells in the area. To set the borders of a group of areas, use rang-> borderaround.

The following uses a specific example to illustrate how to use MFC to Operate Excel.

2, Excel library insertion

In our MFC project, press Ctrl + W to open the MFC class wizard dialog box and click "add class... "->" from a type library... ", find the Excel library you are using. I used" Excel "under the directory c:/program files/Microsoft Office/office11. EXE file, select "all files" for the file type during search, and then add the classes we need. Generally, the first six classes listed above are required, and other classes are added when needed. I don't think it is a good way to add all. I think it is messy, and the generated excel. cpp file will be very large. I recommend that you browse these classes so that you can better understand the classes to be added when performing some operations. After adding the required classes, we can perform some basic operations.

3. Com supports database initialization.

Generally, the initialization and COM library operations are added to the initinstance () of the app, and the initialization code is added before the domodal () call:

If (coinitialize (null )! = 0) <br/>{< br/> afxmessagebox ("failed to initialize the com support library! "); <Br/> exit (1); <br/>}

Add couninitialize () before return; close the con library.

4Code to demonstrate some basic operations

First, do not forget to include the header file "Excel. H". If you use_ Variant_t(), The header file "comdef. H" and "comutil. H" must be included; otherwise, an error occurs:

"Error c2065: '_ variant_t': Undeclared identifier".

The following code includes some basic operations:

// Variable definition <br/> _ application app; <br/> workbooks books; <br/> _ workbook book; <br/> worksheets sheets; <br/> _ worksheet sheet; <br/> range; <br/> lpdispatch lpdisp; <br/> colevariant vresult; </P> <p> cstring STR = ""; </P> <p> colevariant <br/> covtrue (short) True ), <br/> covfalse (short) False), <br/> covoptional (long) disp_e_paramnotfound, vt_error ); </P> <p> // create an Excel 2003 Server (start Excel) <br/> If (! App. createdispatch ("Excel. Application", null) <br/>{< br/> afxmessagebox ("create Excel service failure! "); <Br/> return; <br/>}</P> <p> // The app next to it when it is set to false. quit (); Comment to open <br/> // otherwise, Excel. the EXE process will always exist and one more process will be opened each time you perform the operation <br/> app. setvisible (true); <br/> books. attachdispatch (App. getworkbooks (), true); </P> <p>/* <br/> * open a workbook. <Br/> * Excel 2000 only requires 13 parameters, and Excel 2003 requires 15 parameters <br/> */<br/> lpdisp = books. open ("E: // test.xls", <br/> covoptional, covoptional, kernel, kernel, <br/> covoptional, covoptional, <br/> kernel, covoptional, <br/> covoptional, covoptional); <br/> assert (lpdisp); </P> <p> book. attachdispatch (lpdisp); </P> <p> // obtain the worksheets <br/> sheets. attachdispat CH (book. getworksheets (), true); </P> <p> // obtain the worksheet <br/> Sheet. attachdispatch (sheets. getitem (_ variant_t (short) (1); </P> <p> // obtain all cells <br/> range. attachdispatch (sheet. getcells (), true); </P> <p> // write string data to cell A1, just like the operation matrix, with 1st rows and 1st columns <br/> range. setitem (_ variant_t (long) 1), _ variant_t (long) 1), _ variant_t ("Hello word! "); </P> <p> // write time data to cell A2 <br/> range. attachdispatch (sheet. getrange (_ variant_t ("A2"), _ variant_t ("A2"), true); <br/> range. setvalue2 (_ variant_t ("2011/02/15"); </P> <p> // to cell a3 ~ Write floating point data in A6 <br/> range. attachdispatch (sheet. getrange (_ variant_t ("A3"), _ variant_t ("A6"), true); <br/> range. setvalue2 (_ variant_t (double) 3.14); </P> <p> // set the column width of a cell to 12 <br/> range. attachdispatch (sheet. getrange (_ variant_t ("A1"), _ variant_t ("A1"), true); <br/> range. setcolumnwidth (_ variant_t (long) 12); </P> <p> // center all cells <br/> range. attachdispatch (sheet. getcells (), true); <br/> range. sethorizontalalignment (_ v Ariant_t (long)-4108); //-4108: Center,-4131: left,-4152: Right <br/> range. setverticalalignment (_ variant_t (long)-4108); //-4108: Center,-4160: Back to Top,-4107: down </P> <p> // reads the data of a cell, with 4th rows and 1st columns <br/> range. attachdispatch (range. getitem (_ variant_t (long) (4), _ variant_t (long) (1 ))). pdispval); <br/> vresult = range. getvalue (covoptional); </P> <p> switch (vresult. vt) <br/>{< br/> case vt_bstr: // string <br/> STR = vresult. bstrval; <br/> brea K; <br/> case vt_r8: // 8-byte number <br/> Str. format ("% F", vresult. dblval); <br/> break; <br/> case vt_date: // time format <br/> systemtime st; <br/> varianttimetosystemtime (vresult. date, & St); <br/> break; <br/> case vt_empty: // empty cell <br/> STR = ""; <br/> break; <br/>}< br/> // MessageBox (STR); </P> <p> font ft; // Insert the font class in the Excel class library, the following is similar to </P> <p> range. attachdispatch (sheet. getrange (_ variant_t ("A3"), _ variant_t ("A5"), true); <br/> Ft. attachdispatch (range. getfont (); <br/> ft. setname (_ variant_t (" 文 "); // font <br/> ft. setsize (_ variant_t (long) 12); // font size <br/> // ft. setcolorindex (_ variant_t (long) 3); // color of the word: Red <br/> ft. setcolor (_ variant_t (long) RGB (255, 0, 0); <br/> ft. setbold (_ variant_t (long) 1); // 1: bold, 0: not bold </P> <p> interior it; // set the background color </P> <p> range. attachdispatch (sheet. getrange (_ variant_t ("C3"), _ variant_t ("E6"), true); <Br/> it. attachdispatch (range. getinterior (); <br/> it. setcolorindex (_ variant_t (long) 20); // set the background color to light blue </P> <p> borders; // first set the border of all cells in the area <br/> borders = range. getborders (); <br/> borders. setcolorindex (_ variant_t (long) 1); <br/> borders. setlinestyle (_ variant_t (long) 1); <br/> borders. setweight (_ variant_t (long) 2); </P> <p> // then set the outer border <br/> // linestyle = line type (1 ~ 13) Weight = line width colorindex = line color (-4105 is automatic, 1 is black) <br/> range. borderaround (_ variant_t (long) 9), _ variant_t (long) 1), _ variant_t (long) 1), vtmissing ); </P> <p> range. attachdispatch (sheet. getrange (_ variant_t ("C8"), _ variant_t ("D9"), true); <br/> // merge cells <br/> range. merge (_ variant_t (long) 0); </P> <p> book. save (); // Save the Excel content <br/> // app. setdisplayalerts (false); // The No dialog box is displayed, asking whether to save <br/> // app. quit (); // exit </P> <p> // release the object <br/> range. releasedispatch (); <br/> Sheet. releasedispatch (); <br/> sheets. releasedispatch (); <br/> book. releasedispatch (); <br/> books. releasedispatch (); <br/> app. releasedispatch ();

 

The above are some basic Excel operations. If you have any questions, I hope you can point them out!