Export and import of Qt Tablewidget table data
Preliminary preparation:
pro file added
QT + = axcontainer
Add #include
There is also #include written on the Internet
Different versions may have different code writing methods, so test it yourself.
1. Import xls/xlsx file data into TableWidget table
We right-click the import button, go to the slot, and add the clicked code
QString strData;
void MainWindow::on_pushButton_17_clicked()
{<!-- -->
//First we need to create a file selection dialog box
QString curPash =QDir::currentPath(); //Get the current path
QString dlgTitle="Select table file";
//Files in both xls and xlsx formats are acceptable. xlsx is compatible with xls. Please note that two semicolons are required after each type.
QString filter="Table files (*.xls *.xlsx);;xls files (*.xls);;xlsx files (*.xlsx);;All files (*.*)";
//Create file selection dialog
QStringList fileList = QFileDialog::getOpenFileNames(this,dlgTitle,curPash,filter);
if(fileList.count()<1)
return;
for(int i = 0;i<fileList.count();i + + )
{<!-- -->
//Save file address
strData = fileList.at(i);
}
//Connect the Excel control
QAxObject excel("Excel.Application");
//Do not display any warning information
excel.setProperty("Visible",false);
//Get the workbook collection
QAxObject *workbooks = excel.querySubObject("WorkBooks");
workbooks->dynamicCall("Open (const QString & amp;)",str);
//Get the active workbook
QAxObject *workbook = excel.querySubObject("ActiveWorkBook");
//Get worksheet 1 of the worksheet collection, that is, sheet1
QAxObject *worksheet = workbook->querySubObject("Sheets(int)",1);
QAxObject *range; //Get the value of cell
QString strVal="hull";
QStringList header;
//Set the initial table rows and columns to 0
ui->tableWidget->setRowCount(0); //Set the number of rows 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 the total number. Many people initially thought this function was the total number, causing the program to often crash.
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 value of cell
strVal = range->dynamicCall("Value2()").toString();
header<<strVal;
//Set table header
}
else
{<!-- -->
probar->setValue( + + count);
range = worksheet->querySubObject("Cells(int,int)",i,j); //Get the value of cell
strVal = range->dynamicCall("Value2()").toString();
ui->tableWidget->setItem(i-2,j-1,new QTableWidgetItem(strVal));
}
}
if(i==1)
{<!-- -->
ui->tableWidget->setHorizontalHeaderLabels(header);
}
}
ui->tableWidget->setRowCount(ui->tableWidget->rowCount()-1);
}
}
2. Export tableWidget table data to xls/xlsx file
We right-click the import button, go to the slot, and add the clicked code
void MainWindow::on_pushButton_23_clicked()
{<!-- -->
//Get the save path
QString filepath=QFileDialog::getSaveFileName(this,tr("Save"),".",tr(" (*.xlsx)"));
if(!filepath.isEmpty()){<!-- -->
QAxObject *excel = new QAxObject(this);
//Connect the Excel control
excel->setControl("Excel.Application");
//Do not display the form
excel->dynamicCall("SetVisible (bool Visible)","false");
//Do not display any warning message. If it is true, a prompt similar to "The file has been modified, do you want to save it?" 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 worksheet 1 of the worksheet collection, that is, sheet1
QAxObject *worksheet = worksheets->querySubObject("Item(int)",1);
//Set header value
for(int i=1;i<ui->tableWidget->columnCount() + 1;i + + )
{<!-- -->
//Set a row and a column
QAxObject *Range = worksheet->querySubObject("Cells(int,int)", 1, i);
Range->dynamicCall("SetValue(const QString & amp;)",ui->tableWidget->horizontalHeaderItem(i-1)->text());
}
//Set 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 & amp;)",ui->tableWidget->item(i-1,j-1)->data(Qt::DisplayRole).toString());
}
}
workbook->dynamicCall("SaveAs(const QString & amp;)",QDir::toNativeSeparators(filepath));//Save to filepath
workbook->dynamicCall("Close()");//Close the workbook
excel->dynamicCall("Quit()");//Close excel
delete excel;
excel=NULL;
qDebug() << "\
Export successful!!!";
}
}
3 How to save data into CSV file in Qt
3.1 csv file
The csv file is the abbreviation of Comma-Separated Values (CSV) file. The file stores tabular data (numbers and text) in plain text form. Each field is separated by commas and carriage returns are used for line breaks. Due to the use of plain text records, csv files can be easily recognized by text processing tools, excel and other tools.
3.2 Export csv file in Qt
It is very convenient to open and save csv files in Qt. It can be operated directly in the form of ordinary text. It is still very simple to use QTextStream for standardized reading and writing.
#include <QFileDialog>
#include <QTextStream>
#include <iostream>
//Display all contents of the selected table
void MainWindow::on_pushButton_clicked()
{<!-- -->
/
Initialization: Is it best to perform it in the constructor?
for(int row = 0; row < 4; row + + )
{<!-- -->
for(int col = 0; col < 3; col + + )
{<!-- -->
if(ui->tableWidget->item(row,col)==nullptr || ui->tableWidget->item(row,col)->text().isEmpty())
{<!-- -->
ui->tableWidget->setItem(row,col,new QTableWidgetItem);
//...
}
}
}
/
Method 1: Get the created csv file name
QString fileName = QFileDialog::getSaveFileName(this, tr("Excel file"), qApp->applicationDirPath (),
tr("Files (*.csv)"));
if (fileName.isEmpty())
return;
//Open .csv file
QFile file(fileName);
if(!file.open(QIODevice::WriteOnly | QIODevice::Text))
{<!-- -->
std::cerr << "Cannot open file for writing: "
<< qPrintable(file.errorString()) << std::endl;
return;
}
QTextStream out( & amp;file);
/
//Method 2: Open the specified .csv file
// QFile file("200000.csv");
// if(!file.open(QIODevice::WriteOnly | QIODevice::Text))
// {<!-- -->
// qDebug()<<"Cannot open file for writing";
// return;
// }
// QTextStream out( & amp;file);
Create header
out << tr("information,") << tr("number,") <<"\
";
QString string;
//write content
for(int i = 0; i < 4; i + + )
{<!-- -->
// QString string = ui->tableWidget->item(i, col)->text();
// out << "test" << "," << i << "\
";
for(int col = 0; col < 3; col + + )
{<!-- -->
string="";
string = ui->tableWidget->item(i, col)->text();
The following statements can be accessed directly without executing ui->tableWidget->setItem(row,col,new QTableWidgetItem);
/// But it is not known whether there are other side effects.
//string = ui->tableWidget->model()->index(i, col).data().toString();
out << string << ","; // write to file
//out << "\
";
}
out << "\
";
}
//Close file
file.close();
}
#include <QFileDialog>
#include <QDateTime>
#include <QTextStream>
void MainWindow::on_pushButton_clicked()
{<!-- -->
//1.Select the exported csv file saving path
QString csvFile = QFileDialog::getExistingDirectory(this);
if(csvFile.isEmpty())
return;
//2. The file name uses the system timestamp to generate a unique file
QDateTime current_date_time =QDateTime::currentDateTime();
QString current_date =current_date_time.toString("yyyy_MM_dd_hh_mm_ss");
//csvFile + = tr("/%1_DTUConfigInfo_export_%2.csv").arg(username).arg(current_date);
csvFile + = tr("/DTUConfigInfo_export_%2.csv").arg(current_date);
//3. Use QFile to open the .csv file. If it does not exist, a new file will be automatically created.
QFile file(csvFile);
if (file.exists())
{<!-- -->
//If the file exists, the operation to be performed will be empty here because the file cannot exist.
}
file.open( QIODevice::ReadWrite | QIODevice::Text );
statusBar()->showMessage(tr("Exporting data..."));
QTextStream out( & amp;file);
//4. Get data and create the first row
out<<tr("UID,")<<tr("sysID,")<<tr("UsrID,")<<tr("MeterNum,")<<tr("CMD,\
");/ /header
//Other data can be added in this way
//5. After writing the data, you need to close the file
file.close();
}
Four Summary
Import and export of csv files in Qt
CSV
1 Introduction:
Full name: Comma Separated Values.
It is the English abbreviation of “comma separated values”, usually a plain text file, usually opened with wordWPS or Notepad.
2. Rules:
(1) Leave no space at the beginning and use row units.
(2) It may or may not contain column names. If column names are included, they will be placed on the first line of the file.
(3) One row of data spans across rows, and there are no blank rows.
(4) Use half-width commas as separators, and even if the column is empty, its existence must be expressed.
(5) If there is a half-width comma (i.e.,) in the column content, use half-width quotation marks (i.e. “”) to enclose the field value. If there is a half-width comma (i.e.,) in the content, use half-width quotation marks (i.e. “”) to enclose the field value.
(6) If there are half-width quotation marks (i.e. “) in the column content, they should be replaced with half-width double quotation marks (“”) to escape, and the field value should be enclosed in half-width quotation marks (i.e. “”).
(7) When reading and writing files, the operation rules of quotation marks and commas are reciprocal.
(8) The internal code format is not limited and can be ASCII, Unicode or other.
(9) Special characters are not supported