QT4 Qtablewidget导入、导出Excel

#include "mainwindow.h" #include "ui_mainwindow.h" #include "Qdir" #include "QFileDialog" #include <QStringList> #include <QTableWidget> #include <QDebug> #include <QHeaderView> #include <QFile> MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow) { ui->setupUi(this); } MainWindow::~MainWindow() { delete ui; } #include <QMessageBox> void MainWindow::on_pushButton_clicked() { QString strFile = QFileDialog::getOpenFileName(this,QString("选择Excel文件"),"",tr("Exel file(*.xls *.xlsx)")); if (strFile.isEmpty()){ return; } //excel_list中已经获取的excel中所有数据，接下来要把数据打印到tablewidget中去 ui->tableWidget->setShowGrid(true); //设置显示格子线 ui->tableWidget->horizontalHeader()->setStretchLastSection(true); //使表格自适应行列宽度 // ui->tableWidget->horizontalHeader()->setStyleSheet("QHeaderView::section{background:gray;}"); //设置表头颜色 ui->tableWidget->horizontalHeader()->setMinimumHeight(50); // ui->tableWidget->verticalHeader()->setStyleSheet("QHeaderView::section{background:gray;}"); ui->tableWidget->verticalHeader()->setStretchLastSection(true); //使表格自适应行列宽度 ui->tableWidget->verticalHeader()->setMinimumWidth(50); ui->tableWidget->verticalHeader()->setDefaultSectionSize(50); ui->tableWidget->setAlternatingRowColors(true); ui->tableWidget->setPalette(QPalette(Qt::gray)); // 设置隔行变色的颜色 gray灰色 QAxObject excel("Excel.Application"); //加载Excel驱动 excel.setProperty("Visible", false);//不显示Excel界面，如果为true会看到启动的Excel界面 QAxObject *work_books = excel.querySubObject("WorkBooks"); work_books->dynamicCall("Open (const QString&)", strFile); //打开指定文件 QAxObject *work_book = excel.querySubObject("ActiveWorkBook"); QAxObject *work_sheets = work_book->querySubObject("Sheets"); //获取工作表 QString ExcelName; static int row_count = 0,column_count = 0; int sheet_count = work_sheets->property("Count").toInt(); //获取工作表数目,如下图，有 3 页 if(sheet_count > 0) { QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", 1); //设置为 获取第一页 数据 QAxObject *used_range = work_sheet->querySubObject("UsedRange"); QAxObject *rows = used_range->querySubObject("Rows"); row_count = rows->property("Count").toInt(); //获取行数 QAxObject *column = used_range->querySubObject("Columns"); column_count = column->property("Count").toInt(); //获取列数 ui->tableWidget->setColumnCount(column_count); //获取列数据 ui->tableWidget->setRowCount(row_count); //获取行数据 //获取第一行第一列数据 ExcelName = work_sheet->querySubObject("Cells(int,int)", 1,1)->property("Value").toString(); QTableWidgetItem* item; //获取表格中需要的数据，此处是从第三行第二列获取数据，具体原因看下图理解，根据自己的需求获取信息 for (int i =1; i <= row_count; i++) { for (int j = 1; j <= column_count;j++) { QString txt = work_sheet->querySubObject("Cells(int,int)", i,j)->property("Value").toString(); //获取单元格内容 item = new QTableWidgetItem(txt); ui->tableWidget->setItem(i-1,j-1,item); arr.append(txt); } array.append(arr); arr.clear(); //将每行数据存储到array后，清空arr，避免下次循环时数据累计；arr.appeng()是添加不是赋值 } work_book->dynamicCall("Close(Boolean)", false); //关闭文件 excel.dynamicCall("Quit(void)"); //退出 work_book->dynamicCall("Close()"); excel.dynamicCall("Quit()"); } } void MainWindow::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()); // qDebug()<<ui->tableWidget->horizontalHeaderItem(i-1)->text(); // } //设置表格数据 for(int i = 1;i<ui->tableWidget->rowCount()+1;i++) { qDebug()<<ui->tableWidget->rowCount(); for(int j = 1;j<ui->tableWidget->columnCount()+1;j++) { QAxObject *Range = worksheet->querySubObject("Cells(int,int)", i, j); Range->dynamicCall("SetValue(const QString &)",ui->tableWidget->item(i-1,j-1)->text()); } } workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(filepath));//保存至filepath workbook->dynamicCall("Close()");//关闭工作簿 excel->dynamicCall("Quit()");//关闭excel delete excel; excel=NULL; qDebug() << "\n导出成功啦！！！"; } }