VC操作excel表格

VC操作excel表格(一)

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

Applicationobject is defined in Excel type library as _Application class. Application is the Excel itself, the main functions are:

lThe setting and options of Excel application level

lSome 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:

lReturn the workbook object by index.

lAdd a new empty workbook.

lOpen 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:

lActivate a workbook

lReturn a worksheets or charts

lReturn the active sheet

lSave 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:

lAdd new work sheet

lGet 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:

lAll operation on work sheet, like password.

lReturn the Range object by cell area.

lActivate 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:

lGet and set the cells value

lGet and set the cells formula

lOffset

lUnion

lFont, 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:

lGet chart by index

lAdd a new chart to workbook

lPrint 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:

lSet the basic attributes, e.g., name, title, active.

lSet the chart type

lSet 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, andcontainer

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.

vc操作excel表格(二)

4.Program with create Excel file

We suppose that all classed are defined in excel.h, so we can use those classes by only including excel.h.

We add two menu items, one for creating specified Excel sheet named ID_NewSheet, the other for creating chart name ID_NewChart.

The steps:

1.Add two menu items

2.Add two message map functions for above two menu items

3.Include excel.h file in the file you define above two message map functions.

4.Implement the two functions

4.1.Program with sheet

The next is a way of ID_NewSheet message map function implementation:

//Excel object model

_Application app;

Workbooks books;

_Workbook newBook;

Worksheets sheets;

_Worksheet oSheet,firstSheet;

Charts charts;

_Chart chart,firstChart;

Range range;

Range iCell;

LPDISPATCH lpDisp;

COleVariant vResult;

COleVariant vOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

//Create Excel server (start Excel)

if(!app.CreateDispatch("Excel.Application"))

{

AfxMessageBox("Can’t start Excel server! ");

return;

}

app.put_Visible(TRUE);//set Excel visible

app.put_UserControl(TRUE);//user can operate Excel

//new a book

books.AttachDispatch(app.get_Workbooks());

newBook = books.Add(vOpt);

//Get worksheets and get the first worksheet

sheets = newBook.get_Worksheets();

oSheet = sheets.get_Item(COleVariant((short)1));

//*** ADD DATA TO THE WORKSHEET

//Add Headers to Row 1 of the worksheet

Range oRange;

oRange = oSheet.get_Range(COleVariant("A1"), vOpt);

oRange.put_Value2(COleVariant("Date"));

oRange = oSheet.get_Range(COleVariant("B1"), vOpt);

oRange.put_Value2(COleVariant("Order #"));

oRange = oSheet.get_Range(COleVariant("C1"), vOpt);

oRange.put_Value2(COleVariant("Amount"));

oRange = oSheet.get_Range(COleVariant("D1"), vOpt);

oRange.put_Value2(COleVariant("Tax"));

//Create a safe array that is NUMROWS x 3 --

//column 1 will contain dates column 2 will contain strings

//and column 3 will contain numbers

COleSafeArray sa;

DWORD dwElements[2];

dwElements[0]= 20;//Number of rows

dwElements[1]= 3;//Number of columns

sa.Create(VT_VARIANT, 2, dwElements);

//Populate the safe array with the data

longindex[2];

longlRow;

COleVariant vTemp;

COleDateTime vDateTime;

CString s;

for(lRow=0;lRow<=20-1;lRow++)

{

index[0] = lRow;

//Fill the first column with dates

index[1] = 0;

vDateTime.SetDate(1999, rand()%12, rand()%28);

sa.PutElement(index, (COleVariant)vDateTime);

//Fill the second column with strings

index[1] = 1;

s.Format("ORDR%d", lRow+1000);

vTemp = s;

sa.PutElement(index, vTemp);

//Fill the third column with numbers

index[1] = 2;

vTemp = (long)rand();

sa.PutElement(index, vTemp);

}

//Fill a range, starting at A2 with the data in

//the safe array

oRange = oSheet.get_Range(COleVariant("A2"), vOpt);

oRange = oRange.get_Resize(COleVariant((short)20),

COleVariant((short)3));

oRange.put_Value2(sa);

sa.Detach();

//*** ADD FORMULAS TO THE WORKSHEET

//Fill the fourth column with a formula to compute the

//sales tax. Note that the formula uses a "relative"

//cell reference so that it fills properly.

oRange = oSheet.get_Range(COleVariant("D2"), vOpt);

oRange = oRange.get_Resize(COleVariant((long)20),

COleVariant((long)1));

oRange.put_Formula(COleVariant("=C2*0.07"));

//*** FORMAT THE WORKSHEET

oRange = oSheet.get_Range(COleVariant("A1"), COleVariant("D1"));

/*Font oFont = oRange.get_Font();

oFont.SetBold(COleVariant((short)TRUE));//Apply Bold to Headers*/

oRange = oRange.get_EntireColumn();

oRange.AutoFit();//AutoFit the columns 1:4

//Make Excel visible and give the user control

oSheet.put_Name("My New Sheet");

newBook.SaveAs(COleVariant("C://mynew.xls"),vOpt,vOpt,

vOpt,vOpt,vOpt,0,

vOpt,vOpt,vOpt,vOpt,vOpt);

newBook.Close (vOpt,COleVariant("C://mynew.xls"/*OutFilename*/),vOpt);

books.Close();

app.Quit();

4.2.Program with chart

The next is a way of ID_NewChart message map function implementation:

//New create Excel object model

_Application app;

_Workbook newBook;

Workbooks books;

Worksheets sheets;

_Worksheet newSheet;

Chartscharts;

_Chart newChart;

COleVariant vOpt((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

//Create Excel server (start Excel)

if(!app.CreateDispatch("Excel.Application"))

{

AfxMessageBox("Can’t start Excel server!");

return;

}

//app.put_Visible(true);//set Excel visible

app.put_UserControl(TRUE);//user can operate Excel

books = app.get_Workbooks();

newBook=books.Open("C://mynew.xls",vOpt, vOpt, vOpt, vOpt, vOpt,

vOpt, vOpt, vOpt, vOpt, vOpt,vOpt, vOpt,vOpt,vOpt);

//newBook = books.Add(vOpt);

sheets = newBook.get_Sheets();

newSheet = sheets.get_Item(COleVariant((short)1));

charts = newBook.get_Charts();

CString tip;

//for(;m_chartType<100;m_chartType++)

{

try

{

if(charts.get_Count()>0)

charts.Delete();

newChart = charts.Add(vOpt,vOpt,COleVariant((short)1));

newChart.put_Name("My chart");

newChart.put_ChartType((long)xlLineMarkers);

tip.Format("C://mynewchart%d.xls",xlLine);

//MessageBox(tip);

Range oRang;

oRang = newSheet.get_Range(COleVariant("C2:D21"), vOpt);

newChart.SetSourceData(oRang,COleVariant((short)2));

newChart.put_HasTitle(true);

ChartTitle oChartTtl = newChart.get_ChartTitle();

oChartTtl.put_Text("My sample xy-scatter chart");

newChart.put_HasLegend(false);

newBook.SaveAs(COleVariant(tip),vOpt,vOpt,

vOpt,vOpt,vOpt,0,

vOpt,vOpt,vOpt,vOpt,vOpt);

}

catch(...)

{

//newBook.Close(vOpt,COleVariant(tip/*OutFilename*/),vOpt);

//app.Quit();

//continue;

}

}

//newBook.Close (vOpt,COleVariant(tip/*OutFilename*/),vOpt);

books.Close();

app.Quit();