QT学习|QXlsx读写Excel表格 QT学习|qt

QXlsx读写Excel表格 1.下载qtxlsxwriter 下载链接
2.将src文件夹下的xlsx放到你的项目中
3.项目文件.pro中添加 include(../xlsx/qtxlsx.pri)
4.qtxlsx.pri中添加DEFINES += XLSX_NO_LIB
创建读取xlsx示例：

文章图片

void MainWindow::on_pushButtoncreate_clicked() { QXlsx::Document xlsx; xlsx.write("A1","Hello Qt1!"); xlsx.write("A2","Hello Qt2!"); xlsx.write("A3","Hello Qt3!"); xlsx.write("A4","Hello Qt4!"); xlsx.write("A5","Hello Qt5!"); xlsx.saveAs("Test.xlsx"); }void MainWindow::on_pushButton_read_clicked() { QXlsx::Document xlsx("Test.xlsx"); xlsx.selectSheet(xlsx.sheetNames()[0]); QXlsx::CellRange range = xlsx.dimension(); for(int i=1; i <= range.rowCount(); ++i){ if(xlsx.cellAt(i,1)) //若单元格没有内容返回空指针，忽略容易崩溃 ui->plainTextEdit->appendPlainText(xlsx.cellAt(i,1)->value().toString()); } }

示例

文章图片

MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent) , ui(new Ui::MainWindow) { ui->setupUi(this); _tableView = new QTableView(this); _model = new QStandardItemModel(this); _tableView->setModel(_model); ui->widget_tableview->layout()->addWidget(_tableView); }void MainWindow::on_pushButton_read_clicked() { xlsxToTable(_tableView); //显示 _tableView->show(); }void MainWindow::on_pushButtoncreate_clicked() { tableToXlsx(_tableView); }//表格转table void MainWindow::xlsxToTable(QTableView* table) { QStandardItemModel* model = static_cast(table->model()); //获取表格，选择表单，获取大小 QXlsx::Document xlsx(QFileDialog::getOpenFileName(this,"select excel","","*.xlsx")); xlsx.selectSheet(xlsx.sheetNames().at(0)); QXlsx::CellRange range = xlsx.dimension(); //设置model大小 model->setColumnCount(range.columnCount()); model->setRowCount(range.rowCount()-1); //去掉表头一行//插入表头 for(int i=0; i< model->columnCount(); ++i) if(xlsx.cellAt(1,i+1)) model->setHeaderData(i,Qt::Horizontal,xlsx.cellAt(1,i+1)->value().toString()); //插入内容 for(int row=0; row< model->rowCount(); ++row) for(int col=0; col< model->columnCount(); ++col) if(xlsx.cellAt(row+2,col+1)) model->setData(model->index(row,col),xlsx.cellAt(row+2,col+1)->value().toString()); //合并单元格 QList mergedCells =xlsx.currentWorksheet()->mergedCells(); for(auto cell : mergedCells){ table->setSpan(cell.firstRow()-2,cell.firstColumn()-1,cell.rowCount(),cell.columnCount()); }}//table转表格 void MainWindow::tableToXlsx(QTableView *table) { QStandardItemModel* model = static_cast(table->model()); QXlsx::Document xlsx; //写入表头 for(int i=0; i< model->columnCount(); ++i) xlsx.write(QString('A'+i)+"1",model->headerData(i,Qt::Horizontal)); //写入内容 for(int row=0; row< model->rowCount(); ++row) for(int col=0; col< model->columnCount(); ++col) xlsx.write(QString('A'+col)+QString::number(row+2),model->data(model->index(row,col))); //保存 xlsx.saveAs(QFileDialog::getSaveFileName(this,"select excel","","*.xlsx")); }

添加读取不同表单

文章图片

void MainWindow::on_comboBox_sheet_activated(int index) { if(_model->rowCount()>0)_model->clear(); changeSheet(_tableView,_xlsx,index); }void MainWindow::xlsxToTable(QTableView* table) { //获取表格，选择表单，获取大小 if(_xlsx)delete _xlsx; _xlsx = new QXlsx::Document(QFileDialog::getOpenFileName(this,"select excel","","*.xlsx")); QStringList sheets = _xlsx->sheetNames(); for(auto sheet:sheets) ui->comboBox_sheet->addItem(sheet); changeSheet(_tableView,_xlsx); }void MainWindow::changeSheet(QTableView* table , QXlsx::Document* xlsx , int id) { QStandardItemModel* model = static_cast(table->model()); xlsx->selectSheet(xlsx->sheetNames().at(id)); QXlsx::CellRange range = xlsx->dimension(); //设置model大小 model->setColumnCount(range.columnCount()); model->setRowCount(range.rowCount()-1); //去掉表头一行//插入表头 for(int i=0; i< model->columnCount(); ++i) if(xlsx->cellAt(1,i+1)) model->setHeaderData(i,Qt::Horizontal,xlsx->cellAt(1,i+1)->value().toString()); //插入内容 for(int row=0; row< model->rowCount(); ++row) for(int col=0; col< model->columnCount(); ++col) if(xlsx->cellAt(row+2,col+1)) model->setData(model->index(row,col),xlsx->cellAt(row+2,col+1)->value().toString()); //合并单元格 QList mergedCells =xlsx->currentWorksheet()->mergedCells(); for(auto cell : mergedCells){ table->setSpan(cell.firstRow()-2,cell.firstColumn()-1,cell.rowCount(),cell.columnCount()); } }

【QT学习|QXlsx读写Excel表格】载入时解除合并单元格

//查找合并单元格 QList mergedCells =xlsx->currentWorksheet()->mergedCells(); //插入内容,拆分合并单元格 for(int row=0; row< model->rowCount(); ++row) for(int col=0; col< model->columnCount(); ++col) if(xlsx->cellAt(row+2,col+1)){ bool contain =false; if(!mergedCells.isEmpty()){ for(auto cell : mergedCells){ if( (row+2 >= cell.firstRow() && row+2 <= cell.lastRow()) && (col+1 >= cell.firstColumn() && col+1 <= cell.lastColumn())){ model->setData(model->index(row,col),xlsx->cellAt(cell.firstRow(),cell.firstColumn())->value().toString()); contain=true; break; } } } if(!contain)model->setData(model->index(row,col),xlsx->cellAt(row+2,col+1)->value().toString()); }