qt 批量写入excel数据(已封装好),按列写入

此方法需电脑安装office 或者wps ,原理为调用office软件的接口。若想更快的提升速度,可以只打开一次QAxObject* excel = new QAxObject(),然后进行其他操作。

  1. 函数参数为QVector 数据和要保存文件的文件名(全路径文件名),Qvector 数据写入一列,第一行为整列数据的表头,其余行依次对数据进行排列写入。
bool exportToExcel(const QVector<float> ch1,const QString fileName)
{
    QString filepath = QString(fileName);
    if (!filepath.isEmpty())
    {
        QAxObject* excel = new QAxObject();
        if (excel->setControl("Excel.Application")) {   // 使用office
            qDebug() << "使用Office";
        } else if (excel->setControl("ket.Application")) {  // 使用wps
            qDebug() << "使用WPS";
        } else {
            qDebug() << "未安装Office或WPS";
            //            QMessageBox::warning(this, tr("错误"), tr("未安装Office或WPS"));
            return false;
        }
        //连接Excel控件
        //excel->dynamicCall("SetVisible (bool Visible)", "true");
        //不显示窗体
        excel->dynamicCall("SetVisible (bool Visible)", "false");
        //不显示窗体
        excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改,是否保存”的提示

        QAxObject* workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
        //workbooks->dynamicCall("Add");//新建一个工作簿
        workbooks->dynamicCall("Add");//新建一个工作簿

        //4) 打开已存在的工作簿:
        //workbooks->dynamicCall("Open(const QString&)", QString("c:/test.xls"));
        workbooks->dynamicCall("Open(const QString&)", filepath);
        QAxObject* workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿


        QAxObject* worksheets = workbook->querySubObject("Sheets");//获取工作表集合
        QAxObject* worksheet = worksheets->querySubObject("Item(int)", 1);//获取工作表集合的工作表1,即sheet1


        QAxObject* used_range = worksheet->querySubObject("UsedRange");     //获得利用的范围
        QAxObject* rows_1 = used_range->querySubObject("Rows");
        QAxObject* columns = used_range->querySubObject("Columns");

        int row_start = used_range->property("Row").toInt();          //获得开始行
        int row_end = rows_1->property("Count").toInt();

        int column_start = used_range->property("Column").toInt();     //获得开始列
        int column_end = columns->property("Count").toInt();
        int columnCount = 0;
        columnCount = column_start + column_end;
        if (1 == row_start&& row_end ==1)
        {
            columnCount = 1;
        }

        qDebug()<<QString().sprintf("********row_start = %d, row_end = %d, column_start = %d, column_end = %d********",row_start,row_end, column_start, column_end);

        QAxObject* cellA, * cellB, * cellC, * cellD;

        //设置标题
        int cellrow = 1;
        cellB = worksheet->querySubObject("Cells(int,int)", 1, columnCount /*column_end+2*/);
        if(columnCount ==1)
        {
            cellB->dynamicCall("SetValue(const QVariant&)", QVariant("Fibre Length (m THF)"));
        }
        else {
            QString time_t = QDateTime::currentDateTime().toString("yyyy/M/d hh:mm:ss");
            cellB->dynamicCall("SetValue(const QVariant&)", QVariant(time_t));
        }


        cellrow++;

        qint64 start_time =QDateTime::currentMSecsSinceEpoch();
        //        int rows = ch1.size();

        //        for (int i = 0; i < rows; i++) {

        //            //获取单元格
        //            cellB = worksheet->querySubObject("Cells(int, int", i+2, columnCount /*column_end+2*/);
        //            //储存一个字符串数据至表格
        //            cellB->dynamicCall("setValue(const QVariant&", QVariant(ch1[i]));

        //            cellrow++;
        //            if(i %100 == 0)
        //            {
        //               qDebug()<<QString("write row = %1\n").arg(cellrow);
        //            }

        //        }
        QString ColName="";

        convertToColName2(columnCount,ColName);

        QString str =QString("%1%2:%1%3").arg(ColName).arg(2).arg(ch1.size()+1);
        qDebug()<<str;
        QVariantList vars;//行数据
        const int m_rows = ch1.size();//行数
        for(int i=0;i< m_rows;++i)
        {
            QList<QVariant> m_column_num;//每列数据
            m_column_num.append(QString::number(ch1[i],'f',4));
            vars.append(QVariant(m_column_num));
        }
        QAxObject *rangeAx = worksheet->querySubObject("Range(const QString&)", str);
        rangeAx->dynamicCall("Value", QVariant(vars));


        qDebug()<<QDateTime::currentMSecsSinceEpoch()-start_time;

        workbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(filepath));//保存至filepath,注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\",不然一定保存不了。
        workbook->dynamicCall("Close()");//关闭工作簿

        excel->dynamicCall("Quit()");//关闭excel
        delete excel;
        excel = NULL;
    }
    return true;
}
  1. 内部使用函数,用来计算excel列数跟字母的对应关系。
void convertToColName2(int data, QString &res)
{
    int a = data;
    do {
        a--;
        res = char(a % 26 + 'A') + res;
        a /= 26;
    } while (a > 0);

}
  1. 导出的数据如图所示。

在这里插入图片描述
默认的第一行标题为如下图所示,可进行更改为任意想要的标题。在这里插入图片描述

  • 3
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
以下是使用Qt实现Modbus TCP写入的示例代码: ```c++ #include <QtNetwork> #include <QtEndian> #include <QDebug> class ModbusTcpClient : public QObject { Q_OBJECT public: ModbusTcpClient(QObject *parent = nullptr) : QObject(parent) , m_socket(new QTcpSocket(this)) { connect(m_socket, &QTcpSocket::connected, this, &ModbusTcpClient::onSocketConnected); connect(m_socket, &QTcpSocket::disconnected, this, &ModbusTcpClient::onSocketDisconnected); connect(m_socket, &QTcpSocket::readyRead, this, &ModbusTcpClient::onSocketReadyRead); connect(m_socket, QOverload<QAbstractSocket::SocketError>::of(&QAbstractSocket::error), this, &ModbusTcpClient::onSocketError); } bool connectToHost(const QString &host, quint16 port) { m_socket->connectToHost(host, port); if (!m_socket->waitForConnected(5000)) { qWarning() << "Failed to connect to host:" << host << "port:" << port << "error:" << m_socket->errorString(); return false; } return true; } bool writeCoil(quint16 address, bool value) { quint8 buffer[12] = { 0 }; quint16 tid = 0x0001; // transaction id quint16 pid = 0x0000; // protocol id quint16 len = 0x0006; // pdu length quint8 uid = 0x01; // unit id quint16 fcode = 0x0005; // function code quint16 addr = qToBigEndian(address); // coil address quint16 val = qToBigEndian(value ? 0xFF00 : 0x0000); // coil value qToBigEndian(tid, buffer); // write transaction id qToBigEndian(pid, buffer + 2); // write protocol id qToBigEndian(len, buffer + 4); // write pdu length buffer[6] = uid; // write unit id qToBigEndian(fcode, buffer + 7); // write function code qToBigEndian(addr, buffer + 9); // write coil address qToBigEndian(val, buffer + 11); // write coil value qint64 bytesWritten = m_socket->write(reinterpret_cast<const char *>(buffer), sizeof(buffer)); if (bytesWritten != sizeof(buffer)) { qWarning() << "Failed to write to socket:" << m_socket->errorString(); return false; } if (!m_socket->waitForBytesWritten(5000)) { qWarning() << "Failed to wait for bytes written:" << m_socket->errorString(); return false; } return true; } signals: void connected(); void disconnected(); void error(QAbstractSocket::SocketError error); void dataReceived(const QByteArray &data); private slots: void onSocketConnected() { emit connected(); } void onSocketDisconnected() { emit disconnected(); } void onSocketError(QAbstractSocket::SocketError error) { emit error(error); } void onSocketReadyRead() { QByteArray data = m_socket->readAll(); emit dataReceived(data); } private: QTcpSocket *m_socket; }; ``` 使用示例: ```c++ ModbusTcpClient client; if (client.connectToHost("192.168.1.100", 502)) { if (client.writeCoil(0x0001, true)) { qDebug() << "Write coil success."; } else { qDebug() << "Write coil failed."; } } else { qDebug() << "Failed to connect to host."; } ``` 在上面的示例中,`ModbusTcpClient`类封装了Modbus TCP客户端。`connectToHost`方法用于连接到Modbus TCP服务器。`writeCoil`方法用于写入线圈的值。线圈地址和值通过参数传递。如果写入成功,则返回`true`,否则返回`false`。在`onSocketReadyRead`槽中,当从服务器收到数据时,会发出`dataReceived`信号。使用时,只需实例化`ModbusTcpClient`类,然后调用`connectToHost`和`writeCoil`方法即可。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值