1. The require files
When you start to program in VC, you must have the lib files of Excel. The next table will show that:
Office Version | Requirement files | Other files | Default Dir |
Office 97 | Excel8.olb | Excel.exe | C:/program files/MS Office/office |
Office 2000 | Excel9.olb | Excel.exe | C:/program files/MS Office/office |
Office 2002 | Excel.exe |
| C:/program files/MS Office/office10 |
Office XP | Excel.exe |
| C:/program files/MS Office/office10 |
Before you start work, you must be sure that corresponding files are there.
2. The Excel Object model
Before you start to work, another thing must be known, it is the Excel object model. It tells us that what are included in Excel? Let us go.
2.1. Basic model
Excel program consists of eight main parts; these are application object, workbooks object, work book object, work sheets object, work sheet object, range object, charts, chart. Next picture describes the relationship of main objects in Excel:
Notice: In fact, there are many other objects, but we often use above objects. You can find the MSDN/office development/office XXXX/excel for getting others.
2.2. Application Object
Application object is defined in Excel type library as _Application class. Application is the Excel itself, the main functions are:
l The setting and options of Excel application level
l Some methods which return to top object
We can get Workbooks/workbook object from _Application object like this:
_Application app;
app.get_workbooks ();
2.3. Workbooks object
Workbooks is the aggregate of all open books. It is container object, the element is workbook object.
The main functions are:
l Return the workbook object by index.
l Add a new empty workbook.
l Open a file, and create a new workbook for this file.
Example:
Workbooks books = app.get_workbooks();
Workbook newBook = books.add(votp);
newBook = books.Open(“.//1.xls”,...);
newBook = books.get_Item(ColVariant((short)1));
2.4. Workbook Object
Workbook object is a work book. It includes work sheet and chart. The main functions are:
l Activate a workbook
l Return a worksheets or charts
l Return the active sheet
l Save to file(XLS)
Example:
newBook.Activate();
WorkSheets sheets = newBook.get_WorkSheets();
newBook.get_Charts();
newBook.get_ActiveChart();
newBook.get_ActiveSheet();
2.5. Worksheets object
Worksheets is a aggregate object too. Every element is worksheet object. In fact, there is a Sheets object, it is aggregate object too, but the element maybe a worksheet object or a chart object.
The main functions are:
l Add new work sheet
l Get work sheet by index
Example:
Worksheet sheet = sheets.add(vopt,vopt,vopt,COleVariant((short)1));
sheet = sheets.get_Item(index);
2.6. Worksheet object
WorkSheet object is a work sheet of Excel. It is the member of Worksheets and sheets.
The main functions are:
l All operation on work sheet, like password.
l Return the Range object by cell area.
l Activate itself
Example:
sheet. Protect();
sheet.put_Name(“My create sheet”);
Ranget oRng =sheet.get_Range(COleVariant(“A1:B3”),vopt);
sheet.Activate();
2.7. Range object
Range object is a cell, or a row, or a column, or a area (it maybe a cell or some continuous cells), or a 3D area.
The main functions are:
l Get and set the cells value
l Get and set the cells formula
l Offset
l Union
l Font, autofit, and so on…
Example:
oRng.get_Value();
oRng.put_Value(COleVariant("Date"));
oRange = oRange.get_Resize(COleVariant((long)20),
COleVariant((long)1));
oRange.put_Formula(COleVariant("=C2*0.07"));
2.8. Charts
Charts is a aggregate object, it includes all charts in workbook, but it doesn’t contain embedded charts.
The main functions are:
l Get chart by index
l Add a new chart to workbook
l Print chart
Example:
Charts charts = newBook.get_Charts();
Charts.get_Item(index);
Chart newChart = charts.add(vopt,vopt,COleVariant((short)1));
2.9. Chart
Chart represents chart, it can be a embedded chart or a single chart.
The main functions:
l Set the basic attributes, e.g., name, title, active.
l Set the chart type
l Set the chart data source
Example:
newChart.put_Name("My chart");
newChart.put_ChartType((long)xlLineMarkers);
Range oRang;
oRang = newSheet.get_Range(COleVariant("C2:D21"), vOpt);
newChart.SetSourceData(oRang,COleVariant((short)2));
2.10. Chart type
3. The step of creating
Now let us start to create a project and write program for creating an Excel sheet and an Excel chart.
We describe that in two steps. The first step is how to import type libraries and what type libraries are imported into project, and the second step is how to code. The second will be described in next section.
3.1. How and what
What libraries are imported into? Different office version has different type libraries, see above form.
There are some difference of how to import between VC6.0 and VC7.0.
3.1.1. VC6.0
1. Create a MFC exe project
2. Select Menu “View->Class Wizard”
3. Select option card “Automation->Add Class->from type library”
4. Select an excel9.olb/excel8.olb/excel.exe file, which often locates under dir C:/Program files/Office/.
5. Select specified classes, e.g. _Application, Workbooks, _Workbook, Worksheets, _Worksheet, Range, then click OK, and a file named excel9.h/excel8.h will be created. That file includes the definition of above classes.
3.1.2. VC7.0
1. Create a MFC EXE project, single document, and container
2. Select menu “Project->Class Wizard”
3. Select “Class in type library”
4. Click “Open” button
5. Set the source of class as “File”, and select the file.
6. Select the interfaces that you want to add your project from left list and insert them into right list
7. Set the import file name (excel.h), then click the “Complete” button.
来源:(http://blog.sina.com.cn/s/blog_4fb3d92a0100h7m2.html) - VC操作excel表格(一)_PSTI_新浪博客