VS2010 MFC Excel（3）

2.2.      Application ObjectApplication object is defined in Excel type library as _Application class. Application is the Excel itself, the main functions are:         The setting and options of Excel application level         Some methods which return to top objectWe can get Workbooks/workbook object from _Application object like this:</span></strong>_Application app;app.get_workbooks ();<strong><span style="font-size:24px;">2.3.      Workbooks objectWorkbooks is the aggregate of all open books. It is Container object, the element is workbook object.The main functions are:         Return the workbook object by index.         Add a new empty workbook.         Open a file, and create a new workbook for this file.Example:</span></strong>Workbooks books = app.get_workbooks();Workbook newBook = books.add(votp);newBook = books.Open(“.//1.xls”,...);newBook = books.get_Item(ColVariant((short)1));<span style="font-size:24px;"><strong>2.4.      Workbook ObjectWorkbook object is a work book. It includes work sheet and chart. The main functions are:         Activate a workbook         Return a worksheets or charts         Return the active sheet         Save to file(XLS)Example:</strong></span>newBook.Activate();WorkSheets sheets = newBook.get_WorkSheets();newBook.get_Charts();newBook.get_ActiveChart();newBook.get_ActiveSheet();<span style="font-size:24px;"><strong>2.5.      Worksheets objectWorksheets 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:         Add new work sheet         Get work sheet by indexExample:</strong></span>Worksheet sheet = sheets.add(vopt,vopt,vopt,COleVariant((short)1));sheet = sheets.get_Item(index);<span style="font-size:24px;"><strong>2.6.      Worksheet objectWorkSheet object is a work sheet of Excel. It is the member of Worksheets and sheets.The main functions are:         All operation on work sheet, like password.         Return the Range object by cell area.         Activate itselfExample:</strong></span>sheet. Protect();sheet.put_Name(“My create sheet”);Ranget oRng =sheet.get_Range(COleVariant(“A1:B3”),vopt);sheet.Activate();<span style="font-size:24px;"><strong>2.7.      Range objectRange 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:         Get and set the cells value         Get and set the cells formula         Offset         Union         Font, autofit, and so on…Example:</strong></span>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"));<span style="font-size:24px;"><strong>2.8.      ChartsCharts is a aggregate object, it includes all charts in workbook, but it doesn’t contain embedded charts.The main functions are:         Get chart by index         Add a new chart to workbook         Print chartExample:</strong></span>Charts charts = newBook.get_Charts();Charts.get_Item(index);Chart newChart = charts.add(vopt,vopt,COleVariant((short)1));<span style="font-size:24px;"><strong>2.9.      ChartChart represents chart, it can be a embedded chart or a single chart.The main functions:         Set the basic attributes, e.g., name, title, active.         Set the chart type         Set the chart data sourceExample:</strong></span>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)); <span style="font-size:24px;"><strong>2.10. Chart type 3.  The step of creatingNow 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 whatWhat 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.01.    Create a MFC exe project2.    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.01.    Create a MFC EXE project, single document, and container2.    Select menu “Project->Class Wizard”3.    Select “Class in type library”4.    Click “Open” button5.    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 list7.    Set the import file name (excel.h), then click the “Complete” button.  4.  Program with create Excel fileWe 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 items2.    Add two message map functions for above two menu items3.    Include excel.h file in the file you define above two message map functions.4.    Implement the two functions4.1.      Program with sheetThe next is a way of ID_NewSheet message map function implementation://Excel object model</strong></span>    _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);//2代表维数，第一个参数代表数据类型    //Populate the safe array with the data    long index[2];    long lRow;    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 chartThe 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;    Charts  charts;    _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();