将Excel导入tableWidget

#include "widget.h" #include "SendDateStruct.h" #include "ui_widget.h" #include<QAction> #include<QDialog> #include<QFileDialog> #include<QFile> #include<ActiveQt/QAxObject> #include<QDebug> #include<QMessageBox> #include<QTableWidget> #include<QString> #include<QDir> #include<QHBoxLayout> #include<qmath.h> Widget::Widget(QWidget *parent) : QWidget(parent) , ui(new Ui::Widget) { ui->setupUi(this); ui->tableWidget->setSelectionMode(QAbstractItemView::SingleSelection); ui->tableWidget->setSelectionBehavior(QAbstractItemView::SelectRows); connect(ui->tableWidget,&QTableWidget::itemClicked,this,&Widget::sendClickDate); //connect(this,&Widget::sig_StudentInfo,this,&Widget::getStruct); ui->pushButton_3->setFocus(); ui->pushButton_3->setDefault(1); } Widget::~Widget() { delete ui; } void Widget::sendClickDate(QTableWidgetItem *item) { QString currentName = ui->tableWidget->item(ui->tableWidget->currentRow(), 0)->text(); QString currentSex = ui->tableWidget->item(ui->tableWidget->currentRow(), 1)->text(); QString currentAge = ui->tableWidget->item(ui->tableWidget->currentRow(), 2)->text(); similarityInfo stu; QVariant varValue; stu.name=currentName; stu.Age=currentAge; stu.Sex=currentSex; varValue.setValue(stu); //emit sig_StudentInfo(varValue); qDebug() << currentName << currentSex << currentAge; } //void Widget::getStruct(QVariant varValue) //{ // similarityInfo stu1; // stu1 = varValue.value<similarityInfo>(); // qDebug()<<stu1.name<<stu1.Age<<stu1.Sex; //} void Widget::on_pushButton_clicked() { QString curPath=QDir::currentPath(); QString filter="表格文件(*.xlsx)"; QString fileName=QFileDialog::getOpenFileName(this,"打开",curPath,filter); if (fileName.isEmpty()) return; fileName=QDir::toNativeSeparators(fileName); ui->lineEdit->setText(fileName); QAxObject *myExcel=new QAxObject("Excel.application",this); myExcel->setProperty("DisplayAlerts",false); QAxObject *workBooks=myExcel->querySubObject("WorkBooks"); workBooks->dynamicCall("Open(const QString&)",fileName); QAxObject*workBook=myExcel->querySubObject("ActiveWorkBook"); QAxObject*mySheets=workBook->querySubObject("Sheets"); QAxObject*sheet=mySheets->querySubObject("Item(int)",1); //获取已经使用的单元格区域，并得到行列数 QAxObject*range=sheet->querySubObject("UsedRange"); QAxObject*rows=range->querySubObject("Rows"); QAxObject*colums=range->querySubObject("Columns"); int count_row=rows->dynamicCall("Count").toUInt(); int count_col=colums->dynamicCall("Count").toUInt(); //提取单元格中内容，存放到StringList中 QStringList tableString; QAxObject*cell; tableString.clear(); for(int i=1;i<=count_row;i++) { QString line; for(int j=1;j<=count_col;j++) { cell=range->querySubObject("Cells(int,int)",i,j); line+=cell->dynamicCall("Value").toString()+"\t"; } tableString.append(line); } //关闭工作簿、结束进程 workBook->dynamicCall("Close()"); myExcel->dynamicCall("Quit()"); //显示得到的数据 showTableString(tableString); } void Widget::showTableString(const QStringList table) { if(table.isEmpty()) return; //QTableWidget *tableWidget=ui->tableWidget; QStringList header=table.at(0).simplified().split(' ',Qt::SkipEmptyParts); ui->tableWidget->setRowCount(table.count()-1); ui->tableWidget->setColumnCount(header.count()); ui->tableWidget->setHorizontalHeaderLabels(header); for(int i=1;i<table.count();i++) { QStringList line=table.at(i).split('\t',Qt::KeepEmptyParts); for(int j=0;j<header.count();j++) { QTableWidgetItem*item=new QTableWidgetItem; item->setText(line.at(j)); item->setTextAlignment(Qt::AlignCenter); ui->tableWidget->setItem(i-1,j,item); //默认选中第一个 ui->tableWidget->selectRow(2); } QTableWidgetItem*item=ui->tableWidget->item(i-1,header.count()-1); qreal aver=item->text().toDouble(); item->setText(QString("%1").arg(aver,4,'f',1)); } } void Widget::on_pushButton_3_clicked() { QString currentName = ui->tableWidget->item(ui->tableWidget->currentRow(), 0)->text(); QString currentSex = ui->tableWidget->item(ui->tableWidget->currentRow(), 1)->text(); QString currentAge = ui->tableWidget->item(ui->tableWidget->currentRow(), 2)->text(); qDebug() << ui->tableWidget->currentRow()<<currentName << currentSex << currentAge; qDebug()<<ui->tableWidget->currentItem()<<ui->tableWidget->currentRow(); } void Widget::on_pushButton_2_clicked() { //获取保存路径 QString filepath=QFileDialog::getSaveFileName(this,tr("Save"),".",tr(" (*.xlsx)")); if(!filepath.isEmpty()){ QAxObject *excel = new QAxObject(this); //连接Excel控件 excel->setControl("Excel.Application"); //不显示窗体 excel->dynamicCall("SetVisible (bool Visible)","false"); //不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示 excel->setProperty("DisplayAlerts", false); //获取工作簿集合 QAxObject *workbooks = excel->querySubObject("WorkBooks"); //新建一个工作簿 workbooks->dynamicCall("Add"); //获取当前工作簿 QAxObject *workbook = excel->querySubObject("ActiveWorkBook"); //获取工作表集合 QAxObject *worksheets = workbook->querySubObject("Sheets"); //获取工作表集合的工作表1，即sheet1 QAxObject *worksheet = worksheets->querySubObject("Item(int)",1); //设置表头值 for(int i=1;i<ui->tableWidget->columnCount()+1;i++) { //设置设置某行某列 QAxObject *Range = worksheet->querySubObject("Cells(int,int)", 1, i); Range->dynamicCall("SetValue(const QString &)",ui->tableWidget->horizontalHeaderItem(i-1)->text()); } //设置表格数据 for(int i = 1;i<ui->tableWidget->rowCount()+1;i++) { for(int j = 1;j<ui->tableWidget->columnCount()+1;j++) { QAxObject *Range = worksheet->querySubObject("Cells(int,int)", i+1, j); Range->dynamicCall("SetValue(const QString &)",ui->tableWidget->item(i-1,j-1)->data(Qt::DisplayRole).toString()); } } workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(filepath));//保存至filepath workbook->dynamicCall("Close()");//关闭工作簿 excel->dynamicCall("Quit()");//关闭excel delete excel; excel=NULL; qDebug() << "\n导出成功啦！！！"; } }