Chinaunix首页 | 论坛 | 博客
  • 博客访问: 778810
  • 博文数量: 230
  • 博客积分: 6330
  • 博客等级: 准将
  • 技术积分: 2188
  • 用 户 组: 普通用户
  • 注册时间: 2009-07-10 15:55
个人简介

脚踏实地

文章分类

全部博文(230)

文章存档

2017年(1)

2016年(7)

2015年(10)

2014年(32)

2013年(24)

2012年(33)

2011年(50)

2010年(30)

2009年(43)

分类: WINDOWS

2011-01-11 21:58:43

关于生成excel - chart: 来源:
#include   "excel8.h "
void   CChartView::OnInsertChart()  
{
        CChartCntrItem*   pItem   =   NULL;
        //获取与视图相关的文档
        CChartDoc*   pDoc   =   GetDocument();
        //创造一个新项(COleClientItem),代表新插入的OLE对象
        pItem   =   new   CChartCntrItem(pDoc);

        //插入一个Excel图表
        CLSID   clsid;
        ::CLSIDFromProgID(L "Excel.Chart ",&clsid);
        pItem-> CreateNewItem(clsid);

        //为新增的图标准备显示区域
CRect   rectView;
        this-> GetClientRect(&rectView);
        //得到OLE对象(EXCEL图表)的调度接口,以便对之进行自动化控制
LPDISPATCH   lpDisp;
        lpDisp   =   pItem-> GetIDispatch();
        COleVariant   vOpt(DISP_E_PARAMNOTFOUND,   VT_ERROR);
_Workbook   oBook;
        oBook.AttachDispatch(lpDisp);

        //获取工作表和图表
        Worksheets   oSheets   =   oBook.GetWorksheets();
        //获取工作簿的第一张工作表
_Worksheet   oSheet   =   oSheets.GetItem(COleVariant((short)1));
BeginWaitCursor();
Charts   oCharts   =   oBook.GetCharts();
//在自动化过程隐藏图表
oSheet.SetVisible(FALSE);
//获取工作簿的图表
_Chart   oChart   =   oCharts.GetItem(COleVariant((short)1));
        //清除工作表上的所有数据
        Range   oRng   =   oSheet.GetCells();
        oRng.Clear();
        //设置第一列标题
oRng   =   oSheet.GetRange(COleVariant( "A1 "),   vOpt);
        oRng.SetValue(COleVariant( "月份 "));
//设置第二列标题
        oRng   =   oSheet.GetRange(COleVariant( "B1 "),   vOpt);
        oRng.SetValue(COleVariant( "销售额 "));
//设置第一列的数据:月份
CString   tmp,rg;
//设置A2到A13的单元内容
for(int   i=1;i <=12;i++)
{
tmp.Format( "%d月份 ",i);
rg.Format( "A%d:A%d ",i+1,i+1);
oRng   =   oSheet.GetRange(COleVariant(rg),   vOpt);
oRng.SetValue(COleVariant(tmp));
}
//设置第二列数据
oRng   =   oSheet.GetRange(COleVariant( "B2:b13 "),   vOpt);
        //设置每个单元格的内容为=RAND()*10000
oRng.SetFormula(COleVariant( "=RAND()*10000 "));

        //设置图表类型为三维圆饼图xl3DPieExploded
oChart.SetChartType((long)70);  
//设置数据源区域
        oRng   =   oSheet.GetRange(COleVariant( "A1:B13 "),   vOpt);
        //数据序列产生在列xlColumns=2
oChart.SetSourceData(oRng,  
COleVariant((short)2));  

        //加入图标标题
        oChart.SetHasTitle(TRUE);
        ChartTitle   oChartTtl   =   oChart.GetChartTitle();
        oChartTtl.SetText( " <-销售收入分月统计图-> ");
oChartTtl.SetShadow(TRUE);
        //不使用图例
oChart.SetHasLegend(FALSE);
//设置图表区的背景颜色
        PlotArea   oPlot   =   oChart.GetPlotArea();
        Interior   oInt   =   oPlot.GetInterior();
        oInt.SetColorIndex(COleVariant((short)19));
//设置数据序列的边框颜色及宽度
Series   oSeries   =  
oChart.SeriesCollection(COleVariant((short)1));
        Border   oBorder   =   oSeries.GetBorder();
        oBorder.SetColorIndex(COleVariant((short)21));
        oBorder.SetWeight(COleVariant((long)-4138));
//在所有的自动化完成之后,显示图表
oSheet.SetVisible(TRUE);
EndWaitCursor();
}
 
 
 
=======================
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.
See next pictures:
 
Picture 1:
 

Picture 2:
 

Picture 3:
 
Picture 4:
 
Picture 5:
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.
 
 
See next pictures:
 
Picture 6:
 

Picture 7:
 
Picture 8
 

Picture 9:
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
    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 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;
    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();
 
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/windcsn/archive/2004/12/15/216724.aspx
阅读(1850) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~