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);