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

最新推荐文章于 2020-12-29 03:21:50 发布

philipzeng

最新推荐文章于 2020-12-29 03:21:50 发布

阅读量1.4k

点赞数

CC 4.0 BY-SA版权

文章标签： excel shapes exception charts null user

本文链接：https://blog.youkuaiyun.com/philipzeng/article/details/5665742

本文详细介绍了如何利用C++ Builder（CB）来实现向Excel表格中添加图表，包括设置图表类型、源数据以及调整图表位置。通过宏录制和枚举值的查找，演示了具体步骤，如设置图表为折线图，并删除系列，同时提供了InitExcelApplication、OpenExcel等实用函数的实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先使用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);