“This is the 23rd day of my participation in the August Gwen Challenge. For details, see: August Gwen Challenge” juejin.cn/post/698796…

@[TOC]


QT built-in TableWidget controls can achieve the function of table display, just start to learn TableWidget, just create a table in advance, set the data, but the real software will not let us set the data format. Let’s take a look at how to import XLS files and automatically generate tables.

! [insert picture description here] (img – blog. Csdnimg. Cn / 20210207144… =700x) the method I use is relatively slow to import/export, and there is another one on the web that is faster and can be written again.

Preparation:

Add QT += axContainer to pro files

Add #include<ActiveQt/QAxObject> to the header file

There’s also #include

Version is different, the code may be written differently, test yourself.

XLS/XLSX files import data into the TableWidget table

! [insert picture description here] (img – blog. Csdnimg. Cn / 20210207145… =600x) we right click the import button, go to the slot, and add the clicked code

QString strData;
void MainWindow::on_pushButton_17_clicked(a)
{
	// First we need to create a file selection dialog box
    QString curPash =QDir::currentPath(a);// Get the current path
    QString dlgTitle="Select form file";
    XLSX is compatible with XLS. Note that each type should be followed by two semicolons
    QString filter="(*.xls *.xlsx); XLS files (*. XLS);; XLSX file (*. XLSX);; All files (*.*)";
    // Create a file selection dialog box
    QStringList fileList = QFileDialog::getOpenFileNames(this,dlgTitle,curPash,filter);
    if(fileList.count()"1)
        return;
    for(int i = 0; i<fileList.count(a); i++) {// Save the file address
        strData = fileList.at(i);
    }
    // Connect Excel controls
    QAxObject excel("Excel.Application");
    // Do not display any warning messages
    excel.setProperty("Visible".false);
    // Get the workbook collection
    QAxObject *workbooks = excel.querySubObject("WorkBooks");
    workbooks->dynamicCall("Open (const QString&)",str);
    // Get the activity workbook
    QAxObject *workbook = excel.querySubObject("ActiveWorkBook");
    // get sheet1 of the worksheet collection, sheet1
    QAxObject *worksheet = workbook->querySubObject("Sheets(int)".1);
    QAxObject *range; // Get the cell value
    QString strVal="hull";
    QStringList header;
    // Set the initial table rows and columns to 0
    ui->tableWidget->setRowCount(0); // Set the number of lines to 0
    ui->tableWidget->setColumnCount(0); // Set the number of columns to 0

    int count =0;
    for(int i = 1; i<row; i++) {// Note that the function in setRowCount is not append, but total
        ui->tableWidget->setRowCount(ui->tableWidget->rowCount() +1);
        for(int j = 1; j<column; j++) {if(i == 1)
            {
                ui->tableWidget->setColumnCount(ui->tableWidget->columnCount() +1);
                range = worksheet->querySubObject("Cells(int,int)",i,j); // Get the cell value
                strVal = range->dynamicCall("Value2()").toString(a); header<<strVal;// Set the table header
            }
            else
            {
                probar->setValue(++count);
                range = worksheet->querySubObject("Cells(int,int)",i,j); // Get the cell value
                strVal = range->dynamicCall("Value2()").toString(a); ui->tableWidget->setItem(i2 -,j- 1.new QTableWidgetItem(strVal)); }}if(i == 1)
        {
            ui->tableWidget->setHorizontalHeaderLabels(header);
        }
    }
    ui->tableWidget->setRowCount(ui->tableWidget->rowCount(a)- 1); }}Copy the code

The worksheet in this code is the one at the bottom left of the XLS file:


TableWidget Export table data to XLS/XLSX files

! [insert picture description here] (img – blog. Csdnimg. Cn / 20210207162… =600x) we right click the import button, go to the slot, and add the clicked code

void MainWindow::on_pushButton_23_clicked(a)
{
    // Get the save path
       QString filepath=QFileDialog::getSaveFileName(this.tr("Save"),".".tr(" (*.xlsx)"));
       if(! filepath.isEmpty()){
           QAxObject *excel = new QAxObject(this);
           // Connect Excel controls
           excel->setControl("Excel.Application");
           // Do not display the form
           excel->dynamicCall("SetVisible (bool Visible)"."false");
           // Do not display any warning messages. If true, a message like "File has been modified, save or not" will appear when closing
           excel->setProperty("DisplayAlerts".false);
           // Get the workbook collection
           QAxObject *workbooks = excel->querySubObject("WorkBooks");
           // Create a new workbook
           workbooks->dynamicCall("Add");
           // Get the current workbook
           QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
           // Get the worksheet collection
           QAxObject *worksheets = workbook->querySubObject("Sheets");
           // get sheet1 of the worksheet collection, sheet1
           QAxObject *worksheet = worksheets->querySubObject("Item(int)".1);
           // Set the header value
           for(int i=1; i<ui->tableWidget->columnCount() +1; i++) {// set sets a row and a column
               QAxObject *Range = worksheet->querySubObject("Cells(int,int)".1, i);
               Range->dynamicCall("SetValue(const QString &)",ui->tableWidget->horizontalHeaderItem(i- 1) - >text());
           }
           // Set the table data
           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));// Save to filepath
           workbook->dynamicCall("Close()");// Close the workbook
           excel->dynamicCall("Quit()");/ / close excel
           delete excel;
           excel=NULL;
           qDebug() < <"\n Export successful!!"; }}Copy the code