38.QT-QAxObject快速写入EXCEL示例

参考链接:

 

1. qaxobject介绍

在qt中,有个自带的qaxobject类,可以直接操作excel

除此之外,当我们操作某个文件夹下的excel的时候,都会在该文件夹下出现一个隐藏文件~$xx.xlsx,如下图所示:

此时,如果我们再用qaxobject去操作这个文件时,该文件是只读的,无法保存成功的,所以我们使用qaxobject时,需要提前判断一次.

 

2.示例

首先在项目下创建一个模板templatepath.xlsx文件

 

3.效果如下

4.写代码

#include <qapplication>
#include "widget.h"
#include <qtgui>
#include <qaxobject>
#include <qstandardpaths>
#include <qfiledialog>
#include <qfiledialog>

 
/*excel操作*/
enum excel_columntype{
    columna = 1,
    columnb = 2,
    columnc = 3,
    columnd = 4,
    columne = 5,
    columnf = 6,
    columng = 7,
    columnh = 8,
    columni = 9
};

 
qstring saveas()
{
    qstring file;
    qstring filter;
 

    //如果版本低于qt5,则需要将:
    //  qstandardpaths::writablelocation(qstandardpaths::desktoplocation),
    //改为:qdesktopservices::storagelocation(qdesktopservices::desktoplocation),
    file = qfiledialog::getsavefilename (
     null,                               //父组件
    "另存为",                              //标题
     qstandardpaths::writablelocation(qstandardpaths::desktoplocation),                 //设置路径, .表示当前路径,./表示更目录
     "excel(*.xlsx)",     //过滤器
     &filter  );

    return file;
}

//设置excel中单个内容的数据
void  excel_setcell(qaxobject *worksheet,excel_columntype column,int row,qcolor color,qstring text)
{
  qaxobject *cell = worksheet->querysubobject("cells(int,int)", row, column);
  cell->setproperty("value", text);
  qaxobject *font = cell->querysubobject("font");
  font->setproperty("color", color);
}


//把qvariant转为qlist<qlist<qvariant> >,用于快速读出的
void castvariant2listlistvariant(const qvariant &var, qlist<qlist<qvariant> > &res)
{
    qvariantlist varrows = var.tolist();
    if(varrows.isempty())
    {
        return;
    }

    const int rowcount = varrows.size();
    qvariantlist rowdata;

    for(int i=0;i<rowcount;++i)
    {
        rowdata = varrows[i].tolist();
        res.push_back(rowdata);
    }
}

//把qlist<qlist<qvariant> > 转为qvariant,用于快速写入的
void castlistlistvariant2variant(const qlist<qlist<qvariant> > &cells, qvariant &res)
{
    qvariantlist vars;
    const int rows = cells.size();
    for(int i=0;i<rows;++i)
    {
        vars.append(qvariant(cells[i]));
    }
    res = qvariant(vars);
}

int main(int argc, char *argv[])
{
    qapplication a(argc, argv);
    qstring templatepath = "./template.xlsx";
    qfileinfo info(templatepath);

    if(!info.exists())
    {
            qdebug()<<"template.xlsx is null";
            return 0;
    }

    templatepath = info.absolutefilepath();                   //获取模板的绝地路径
    templatepath = qdir::tonativeseparators(templatepath);   //转换一下路径,让windows能够识别

    qstring excelfile = qdir::tonativeseparators(saveas());  //打开文件保存对话框,找到要保存的位置

    if(excelfile=="")
            return  0;

    qfile::copy(templatepath, excelfile);                   //将模板文件复制到要保存的位置去

    info.setfile(excelfile);
    info.setfile(info.dir().path()+"/~$"+info.filename());

    if(info.exists())          //判断一下,有没有"~$xxx.xlsx"文件存在,是不是为只读
    {
        qdebug()<<"报表属性为只读,请检查文件是否已打开!";
         return   0;
    }

 
    qaxobject *excel = new qaxobject();//建立excel操作对象
    excel->setcontrol("excel.application");//连接excel控件
    excel->setproperty("visible", true);//显示窗体看效果
    excel->setproperty("displayalerts", false);//显示警告看效果
    qaxobject *workbooks = excel->querysubobject("workbooks");

    qaxobject* workbook = workbooks->querysubobject("open(const qstring&)",qdir::tonativeseparators(excelfile) ); //打开

    excel->setproperty("caption", "qt excel");      //标题为qt excel
    qaxobject *work_book = excel->querysubobject("activeworkbook");

    qaxobject *worksheet = work_book->querysubobject("sheets(int)",1);     //获取表单1

    excel_setcell(worksheet,columnb,2,qcolor(74,51,255),"12345");     //设置b2单元格内容为12345

    excel_setcell(worksheet,columnb,3,qcolor(255,255,0),"b3");     //设置b3单元格内容

    excel_setcell(worksheet,columnb,4,qcolor(255,0,0),"b4");     //设置b4单元格内容

 

    /*批量一次性设置a6~i106所在内容*/
    qaxobject *user_range = worksheet->querysubobject("range(const qstring&)","a6:i106");

    qlist<qlist<qvariant> > datas;
    for(int i=1;i<101;i++)
    {
        qlist<qvariant> rows;
        for(int j=1;j<10;j++)
        {
            rows.append(i*j);
        }
        datas.append(rows);
    }

    qvariant var;
    castlistlistvariant2variant(datas,var);

    user_range->setproperty("value", var);

 
    workbook->dynamiccall("save()" );


    // workbook->dynamiccall("close()");  //关闭文件
    // excel->dynamiccall("quit()");//关闭excel

    return 0;

}

 

未完待续,下章学习qtxlsx库操作excel: