How to Operate Excel using VC

All materials are from the Internet

There are at least two ways to Operate Excel using VC

1. using ODBC to read, write, and modify Excel files as database files. Someone has compiled the cspreadsheet class online to provide support.

2. Use the automation (old automation) method. Take excel as a component server and use VBA. It can be divided into two types: MFC-based and SDK-based.

I mainly studied the second MFC-based OLE programming method.

I. Excel Object Model

Before programming excel, you must first understand the Microsoft Excel object model. (Parent-child relationship)

 

Figure 1 Office Application Object Model (Excel) from msdn

Where:

Application: represents the application itself. Excel application

Workbooks: a collection of workbooks, representing the work thin.

Worksheets: a collection of worksheet and a child object of a workbook.

Range: A child object of the worksheet. It can be understood as a specific range of cells in the sheet.

Shapes: A child object of worksheet. It is a cell used to store information such as images.

Ii. VC Excel initialization process

1. Import an Excel file.

First, Open Class Wizard and click "from a Type Library" under the "add class" button to import "excel.exe" under the Office installation directory (applies to excel2003 ), then select the required classes, such as _ application, _ workbook, workbooks, worksheets, _ wroksheet, range, and shapes. Click "OK". The system will generate two files, Excel. h and Excel. cpp, under your program directory. To use these imported classes, add # include
"Excel. H.

2. initialize the application.

First, initialize the COM component.

View plaincopy to clipboardprint?
If (! Afxoleinit ())
{
Afxmessagebox ("the dynamic Connection Library of COM cannot be initialized ");
Return false;
}
If (! Afxoleinit ())
{
Afxmessagebox ("the dynamic Connection Library of COM cannot be initialized ");
Return false;
}
 

Then, create an Excel server (start Excel)

Define the global or member Variable _ application app of the app;

View plaincopy to clipboardprint?
If (! App. createdispatch ("Excel. application "))
{
Afxmessagebox ("unable to start Excel server ");
Return false;
}
If (! App. createdispatch ("Excel. application "))
{
Afxmessagebox ("unable to start Excel server ");
Return false;
}
 
3. Set the Excel status

View plaincopy to clipboardprint?
App. setvisible (bvisble); // make Excel visible
App. setusercontrol (bcontrol); // allow other users to control Excel
App. setvisible (bvisble); // make Excel visible
App. setusercontrol (bcontrol); // allow other users to control Excel

Iii. Operations on Excel by VC

Define Variables

View plaincopyto clipboardprint?
Workbooks books;
_ Workbook;
Worksheets sheets;
_ Worksheet sheet;
Lpdispatch lpdisp;
Range;
Colevariant covoptional (long) disp_e_paramnotfound, vt_error );
Workbooks books;
_ Workbook;
Worksheets sheets;
_ Worksheet sheet;
Lpdispatch lpdisp;
Range;
Colevariantcovoptional (long) disp_e_paramnotfound, vt_error );

1. Open an existing Excel File

View plaincopy to clipboardprint?
Books. attachdispatch (App. getworkbooks ());
// Or you can
// Books = app. getworkbooks ();
Lpdisp = books. Open ("d :\\\ 1.xls", covoptional, kernel, kernel, covoptional, covoptional, kernel, covoptional,

Covoptional, covoptional );
Books. attachdispatch (App. getworkbooks ());
// Or you can
// Books = app. getworkbooks ();
Lpdisp = books. Open ("d :\\\ 1.xls", covoptional, kernel, kernel, covoptional, covoptional, kernel, covoptional,
Covoptional, covoptional );

 

2. Create a New. xls file and write data

Colevariant covoptional (long) disp_e_paramnotfound, vt_error );

// Get the workbooks collection so that you can add a new workbook
Books. attachdispatch (App. getworkbooks (); // get workbooks
Book = books. Add (covoptional); // get the workbook
 
// Get the worksheets collection so that you canget the idispatch for the first Worksheet
Sheets = book. getworksheets (); // obtain the worksheets
Sheet = sheets. getitem (colevariant (short) 1); // get the worksheet

// Add data to each cell separately
// Use the setvalue () function for excel2000, and use the setvalue2 () function for excel2003 ()
//

Range = sheet. getrange (colevariant ("A1"), covoptional); // GET A1 range
Range. setvalue2 (colevariant ("date"); // Add data
 
Range = sheet. getrange (colevariant ("B1"), covoptional); // get B1 range
Range. setvalue2 (colevariant ("order"); // Add data

Range = sheet. getrange (colevariant ("C1"), covoptional); // get C1 range
Range. setvalue2 (colevariant ("amount"); // Add data

Range = sheet. getrange (colevariant ("d1"), covoptional); // get the D1 range
Range. setvalue2 (colevariant ("tax"); // Add data

// Add a formula to the cell

Range = sheet. getrange (colevariant ("D2"), covoptional); // get D2 range
Range = range. getresize (colevariant (long) numrows), colevariant (long) 1); // reset the D2 size.
Range. setformula (colevariant ("= c2 * 0.07"); // set the formula for D2: D21

// Set the cell format

Range = sheet. getrange (colevariant ("A1"), colevariant ("d1"); // obtain the range of A1: d1
Ofont = range. getfont (); // obtain the font of the range.
Ofont. setbold (colevariant (short) True); // set whether to bold
Ofont. setcolor (colevariant (long) RGB (255,); // set the font color

Ofont. setname (colevariant (""); // set the font type

Range = range. getentirecolumn (); // obtain all cells
Range. autofit (); // auto fit

3. Merge Cells

// Train of thought: 1. first obtain the Range of A1: C1, then redefine this range, and finally merge
// 2. directly obtain the range of A1: C2 and merge them directly. The result is the same as the first method.
Range unionrange;
Unionrange = sheet. getrange (colevariant ("A1"), colevariant ("C1 "));
Vresult = unionrange. getmergecells ();
Unionrange = unionrange. getresize (colevariant (long) 2), colevariant (long) 3 ));
Unionrange. Merge (colevariant (long) 0); // merge Cells
Unionrange. setrowheight (colevariant (short) 30); // you can specify the height of a cell.
Unionrange. sethorizontalalignment (colevariant (long)-4108); // align horizontally

4. Insert images into cells (BMP and jpg formats are supported, but other formats are not supported)

Shapes shapes = sheet. getshapes (); // obtain a shapes from the sheet object

Range = sheet. getrange (colevariant ("F8"), colevariant ("j22"); // get the range object, used to insert an image

Rgmyrge1 = range;
Shapes. addpicture ("D: \ test1.jpg", false, true,
(Float) range. getleft (). dblval, (float) range. gettop (). dblval, // Add an image locally
(Float) range. getwidth (). dblval, (float) range. getheight (). dblval );

Shaperange srange = shapes. getrange (_ variant_t (long (1 )));

Srange. setheight (float (30 ));
Srange. setwidth (float (30 ));

5. Save the created. xls file

Colevariantcovoptional (long) disp_e_paramnotfound, vt_error );
Book. saveas (colevariant ("D: \ 3.xls"), covoptional, covoptional,
Covoptional, 0,
Covoptional, covoptional );

6. Close the Excel Service

Colevariantcovoptional (long) disp_e_paramnotfound, vt_error );
Book. setsaved (true); // set the Save status of the workbook to saved, so that the system does not prompt whether the workbook is manually saved.
Range. releasedispatch (); // release the range object
Sheet. releasedispatch (); // release the sheet object
Sheets. releasedispatch (); // release the sheets object
 
Book. releasedispatch (); // release the workbook object
Books. releasedispatch (); // release the workbooks object

Book. Close (covoptional, covoptional, covoptional); // close a workbook object
Books. Close (); // close the workbooks object.

App. Quit (); // exit _ Application
App. releasedispatch (); // releas_application