Qt QAxObject 批量写入 Excel

// 计算行的Range名称
QString excelHCalc(int n) {
    QString h;
    while (n > 0) {
        h.prepend(static_cast<char>((n % 26) + 'A' - 1));
        n /= 26;
    }
    return h;
}

// 初始化
QAxObject *excel = new QAxObject();
QAxObject *workBooks = new QAxObject();
QAxObject *workBook = new QAxObject();
QAxObject *workSheets = new QAxObject();
QAxObject *workSheet = new QAxObject();
QAxObject *workRange;

// 配置
if (excel->setControl("Excel.Application")) {   // 使用office
    qDebug() << "使用Office";
} else if (excel->setControl("ket.Application")) {  // 使用wps  
    qDebug() << "使用WPS";
} else {
    qWaring() << "未安装Office或WPS";
    QMessageBox::warning(this, tr("错误"), tr("未安装Office或WPS"));
    return;
}
excel->setProperty("Visible", true);    // 设置为可见
workBooks = excel->querySubObject("WorkBooks"); // 操作表
workBooks->dynamicCall("Add");  // 新建
if (excel->setProperty("Caption", "Qt Excel")) {    // 打开为 Excel
    qDebug() << R"(Qt Excel)";
} else if (!excel->setProperty("Caption", "DataToExcel")) { // 使用wps  
    qDebug() << R"(DataToExcel)";
} else {
    qWaring() << "错误的Caption";
    QMessageBox::warning(this, tr("错误"), tr("错误的Caption"));
    return;
}
workBook = excel->querySubObject("ActiveWorkBook");
workSheets = workBook->querySubObject("Sheets");
workSheet = workSheets->querySubObject("Item(int)", 1);
workSheet->setProperty("Name", "Data");  // 设置工作表名称

// 数据
QVariantList table; // 表格
QVariantList tableLine; // 表格行

// 测试
for (int i = 0; i < 100; ++i) { // 列
    tableLine << QStringLiteral("test%1").arg(i);
}
for (int i = 0; i < 100; ++i) { // 行
    table << QVariant(tableLine);   // 每次添加一行
}

// 获取最宽列宽
int maxLineLength = 0;
int lineLength = 0;
for (const auto &line : table) {
    lineLength = line.toList().size();
    if (maxLineLength < lineLength) {
        maxLineLength = lineLength;
    }
}

// 一次性写入
QString range = QStringLiteral("A1:%1%2").arg(excelHCalc(maxLineLength)).arg(table.size());
workRange = workSheet->querySubObject("Range(const QString&)", range);  //
workRange->setProperty("Value2", QVariant(table));

// 保存
QString filePath = QString("./datas/export/");
QString fileName = QString("data_%1.xls").arg(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
QDir dir;
if (!dir.exists(filePath)) {    // 如果文件夹不存在
    dir.mkpath(filePath);       //   则创建文件夹
}
workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(filePath + fileName)); // 保存

// 关闭
//excel->dynamicCall("Quit(void)");

// 删除 不然进程会进后台不会自动关闭
delete workRange;   workRange = Q_NULLPTR;
delete workSheet;   workSheet = Q_NULLPTR;
delete workSheets;  workSheets = Q_NULLPTR;
delete workBook;    workBook = Q_NULLPTR;
delete workBooks;   workBooks = Q_NULLPTR;
delete excel;   excel = Q_NULLPTR;