CB中向excel插入表并操作表详解-philbert

首先使用excel的宏录制功能，将想做的事情录为宏，然后编辑该宏，就能看到实际步骤，将实际步骤翻译到CB中即可。例如如下宏：

    Charts.Add

    ActiveChart.ChartType = xlLine

    ActiveChart.SetSourceData Source:=Sheets("User Score CDF Chart").Range( _

        "B20:F45636"), PlotBy:=xlColumns

    ActiveChart.SeriesCollection(1).Delete

    ActiveChart.Location Where:=xlLocationAsObject, Name:= _

        "User Score CDF Chart"

其中有些变量既不是属性也不是函数，而是枚举，使用VB脚本编辑器中的查看定义，可以查看该值具体数值。例如

xlLine值为4，xlColumns为1.

//首先定义了几个通用函数

bool __stdcall InitExcelApplication(Variant &m_App)

{

    try

    {

        m_App = GetActiveOleObject("Excel.Application");

        return true;

    }

    catch(...)

    {

        try

        {

            m_App = Variant::CreateObject("Excel.Application");

            return true;

        }

        catch(Exception &E)

        {

            m_App = Null;

            return false;

        }

        catch(...)

        {

            m_App = Null;

            return false;

        }

    }

}

bool __stdcall GetWorksheetByName(Variant &m_Workbook, const AnsiString& SheetName, Variant &Worksheet)

{

    try

    {

        Variant Worksheets = m_Workbook.OlePropertyGet("sheets");

        int SheetCount = Worksheets.OlePropertyGet("count");

        //遍历所有sheet

        for(int i=1; i <= SheetCount; i++)

        {

            Variant Sheet = Worksheets.OlePropertyGet("item", i);

            if (Sheet.OlePropertyGet("name") == SheetName)

            {

                Worksheet = Sheet;

                return true;

            }

        }

        return false;

    }

    catch(Exception &E)

    {

        return false;

    }

    catch(...)

    {

        return false;

    }

}

bool __stdcall OpenExcel(Variant &m_App, Variant &m_Workbook, const AnsiString &FileName)

{

    if(!FileExists(FileName))

    {

        return false;

    }

    try

    {

        m_App.OlePropertyGet("WorkBooks").OleProcedure("Open", FileName.c_str());

        m_Workbook = m_App.OlePropertyGet("ActiveWorkbook");

        return true;

    }

    catch(Exception &E)

    {

        m_Workbook = Null;

        return false;

    }

    catch(...)

    {

        m_Workbook = Null;

        return false;

    }

}

//在一个sheet中插入一个图表，图表的数据取自excel文件中

    Variant hApp;

    InitExcelApplication(hApp);

    Variant hWorkbook；

    OpenExcel(hApp,hWorkbook,FileName);

    AnsiString SheetName="User Chart";//希望图表插入到的sheet名字

    Variant Worksheet；

    GetWorksheetByName(hWorkbook,SheetName,Worksheet);

    Variant    Charts = hApp.OlePropertyGet("Charts");

    Charts.OleFunction("Add");

    Charts = hApp.OlePropertyGet("ActiveChart");

    Charts.OlePropertySet("ChartType",4);

    Variant Range = Worksheet.OlePropertyGet("Range","B20:F45636");

    Charts.OleProcedure("SetSourceData",Range,2);

    Variant Series = Charts.OlePropertyGet("SeriesCollection",1);

    Series.OleProcedure("Delete");

    Charts.OleProcedure("Location",2,"User Chart");

    Variant Shapes = Worksheet.OlePropertyGet("Shapes");

    Variant Shapes1 = Shapes.OleFunction("Item",2);

    Shapes1.OlePropertySet("Top",0);

    Shapes1.OlePropertySet("Left",0);