导出EXCEL做个记录

void exportExcel::table2ExcelByHtml(ReportTableView table, QString title, QString condition)
{
model = (ReportSqlQueryModel)table->getSourceModel();
columns = model->getColumns();
QString location = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
qDebug()<<location<<" :location";
// location = location.replace("/","\");
qDebug()<<location<<" :location";
QString fileName = “”;
fileName = QFileDialog::getSaveFileName(nullptr, QObject::tr(“另存为…”),location+"/"+title,QObject::tr(“EXCEL files (.xls);;EXCEL files (.xlsx);;HTML-Files (*.txt);;”));

bool succ;

if(fileName != "")
{
     QAxObject *excel = new QAxObject("ket.Application"); //Excel.Application
    if(!excel)
    {
        QMessageBox::warning(nullptr,"提示","连接Excel组件失败！");
        return;
    }

    excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体

    excel->setProperty("DisplayAlerts",false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
    QAxObject *workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
    if(!workbooks)
    {

        excel = new QAxObject("Excel.Application"); //ket.Application
        workbooks = excel->querySubObject("WorkBooks");
        if(!workbooks)
        {
            QMessageBox::warning(nullptr,"提示","打开WorkBooks失败！");
            return;
        }

    }

    workbooks->dynamicCall("Add");//新建一个工作簿
    QAxObject *workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
    QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);
    int i,j;
    int colcount = model->columnCount(); //获取QTableview列数
    int rowscount = model->rowCount(); //获取QTableview行数
    if(rowscount  == 0 )
    {
        QMessageBox::warning(nullptr,"提示","报表无数据！");
        return;
    }

    QDialog dia(table);
    _gressBar = new QProgressDialog(&dia);
    dia.setFixedSize(_gressBar->size());
    dia.setModal(true);
    _gressBar->setCancelButtonText(tr("取消"));
    _gressBar->setWindowTitle(QStringLiteral("导出Excel"));  //设置进度对话框标题
    _gressBar->setLabelText(QStringLiteral("正在生成Excel文件..."));//设置进度对话框显示文本
    _gressBar->setModal(true);
    _gressBar->setRange(1,rowscount);

    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",colcount,rowscount);

    worksheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);//调整行高
    QString cellTitle;
    cellTitle.append("A1:");
    if(colcount < 26)
        cellTitle.append(QChar(colcount - 1 + 'A'));
    else
    {
        cellTitle.append(QChar('A'));
        cellTitle.append(QChar(colcount - 27 + 'A'));
    }
    cellTitle.append(QString::number(1));
    QAxObject *_range = worksheet->querySubObject("Range(const QString&)" , cellTitle);

    _range->setProperty("WrapText",true);
    _range->setProperty("MergeCells", true);
    _range->setProperty("HorizontalAlignment", -4108);//xlCenter
    _range->setProperty("VerticalAlignment", -4108);//xlCenter

    cell=worksheet->querySubObject("Cells(int,int)", 1,1); //标题
    cell->dynamicCall("SetValue(const QString&)", title );
    cell->querySubObject("Font")->setProperty("Bold", true); //设置标题加粗
    cell->querySubObject("Font")->setProperty("Size", 20); //标题大小

    cellTitle = "A2:";
    if(colcount < 26)
        cellTitle.append(QChar(colcount - 1 + 'A'));
    else
    {
        cellTitle.append(QChar('A'));
        cellTitle.append(QChar(colcount - 27 + 'A'));
    }
    cellTitle.append(QString::number(2));
    _range = worksheet->querySubObject("Range(const QString&)" , cellTitle); 

    _range->setProperty("WrapText",true);
    _range->setProperty("MergeCells", true); //合并_range
    cell=worksheet->querySubObject("Cells(int,int)", 2,1); //标题
    cell->dynamicCall("SetValue(const QString&)", condition );
    cell->querySubObject("Font")->setProperty("Bold", true); //设置标题加粗
    cell->querySubObject("Font")->setProperty("Size", 12); //标题大小



    //列标题
    for(i = 0 ;i<colcount; i++)
    {
        QString columnName;
        if(i < 26)
        {
            columnName.append(QChar(i + 'A'));
            columnName.append(":");
            columnName.append(QChar(i + 'A'));
        }
        else
        {
            columnName.append(QChar('A'));
            columnName.append(QChar(i-26 + 'A'));
            columnName.append(":");
            columnName.append(QChar('A'));
            columnName.append(QChar(i-26 + 'A'));
        }

        QAxObject *col = worksheet->querySubObject("Columns(const QString&)", columnName);
        col->setProperty("ColumnWidth", table->columnWidth(i)/6);
        if(columns.at(i)->getColumnType() == Column::TEXT) //自定义列属性
        {
            col->setProperty("NumberFormatLocal","@");  //如果列的属性是文本就设置某列为文本
        }

        cell=worksheet->querySubObject("Cells(int,int)", 3, i+1);
        columnName=model->headerData(i,Qt::Horizontal,Qt::DisplayRole).toString();
        cell->dynamicCall("SetValue(const QString&)", columnName);
        cell->querySubObject("Font")->setProperty("Bold", true);
        cell->querySubObject("Interior")->setProperty("Color",QColor(191, 191, 191));
        cell->setProperty("HorizontalAlignment", -4108);//xlCenter
        cell->setProperty("VerticalAlignment", -4108);//xlCenter
    }
 
    //获取表格数据
    for(i = 0; i < rowscount ; i ++)
    {
        QList<QVariant> row;
        for(j = 0 ;j < colcount ;j ++)
        {
            QModelIndex index = model->index(i, j);
            QString strdata= model->data(index).toString();
            row.append(strdata);
            //worksheet->querySubObject("Cells(int,int)", i+4, j+1)->dynamicCall("SetValue(const QString&)", strdata);
        }

        _gressBar->setValue(i+1);
        QCoreApplication::processEvents();

        if(_gressBar->wasCanceled())
        {
            return;
        }
        QString from = "A" + QString::number(i + 4);
        QString to = cellTitle.split(":").at(1).at(0) + QString::number(i + 4);
        _range = worksheet->querySubObject("Range(const QString&,const QString&)",from,to);
        QVariant da(row);
        _range->dynamicCall("SetValue(const QVariant&)",da); //一行一行插入，全部一次性插入测试不成功
    }
   
    //画框线
    QString lrange;
    lrange.append("A2:");
    if(colcount <= 26)
        lrange.append(colcount - 1 + 'A');
    else
    {
        lrange.append('A');
        lrange.append(colcount - 27 + 'A');
    }
    lrange.append(QString::number(model->rowCount() + 2));
    _range = worksheet->querySubObject("Range(const QString&)", lrange);
    _range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
    _range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
     //调整数据区行高
    QString rowsName;
    rowsName.append("2:");
    rowsName.append(QString::number(model->rowCount() + 2));
    _range = worksheet->querySubObject("Range(const QString&)", rowsName);
    _range->setProperty("RowHeight", 20);
    workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至fileName
     workbook->dynamicCall("Close()");//关闭工作簿
    excel->dynamicCall("Quit()");//关闭excel
    delete excel;
    excel=NULL;
    if (QMessageBox::question(NULL,"完成","文件已经导出，是否现在打开？",QMessageBox::Yes|QMessageBox::No)==QMessageBox::Yes)
    {
        QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(fileName)));
    }
}

}

int exportExcel::tableViewExport2Excel(ReportTableView *table, QString title, BaseReport *currentReport)
{
setFormat();
// QString location = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation); //获取我的文档地址
// location += “/” + title; //地址再加上文件名

model = (ReportSqlQueryModel*)table->getSourceModel();
columns = model->getColumns();
report = currentReport;
ColumnSettings::getSettingsFromDb(report->getDbReportName(),PrintDocument::g_userID,columns);
ColumnSettings::sortByOrder(columns);
QString filepath = QFileDialog::getSaveFileName(table, QObject::tr("另存为..."),QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation)+"/"+title,QObject::tr("EXCEL files (*.xls);;EXCEL files (*.xlsx);;HTML-Files (*.txt);;"));

if (filepath != "")
{
    int row = model->rowCount();
    int col = model->columnCount();
    QList<QString> list;
    QString HeaderData;
    for(int i = 0; i< col ;i++)
    {
        if(columns[i]->isDisplay())
            HeaderData.append(model->headerData(i,Qt::Horizontal,Qt::DisplayRole).toString()+"\t");
    }

    list.push_back(HeaderData);
    for(int i=0; i<row; i++)
    {
        QString rowStr = "";
        for(int j=0 ;j<col ;j++)
        {
            if(columns[j]->isDisplay())
            {
                QModelIndex index = model->index(i,j);
                rowStr += model->data(index).toString() + "\t";
            }
        }
        list.push_back(rowStr);
    }
    QTextEdit textEdit;
    for(int i = 0; i<list.size();i++)
    {
        textEdit.append(list.at(i));
    }
    QFile file(filepath);
    if(file.open(QFile::WriteOnly|QIODevice::Text))
    {
        QTextStream ts(&file);
        qDebug()<<"GB2312";
        ts.setCodec("GB2312");
        ts << textEdit.document()->toPlainText();
        file.close();
        if (QMessageBox::question(NULL,"完成","文件已经导出，是否现在打开？",QMessageBox::Yes|QMessageBox::No)==QMessageBox::Yes)
        {
                QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(filepath)));
        }

        return 1;
    }else
    {
        return 0 ;
    }
}

}