TabEditor-Qt/mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QFile>
#include <QTextStream>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QTableWidget>
#include <QFileDialog>
#include <QProgressDialog>
#include <QMessageBox>
#include <QInputDialog>
#include <QSqlRecord>
#include <QtDebug>

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    setAcceptDrops(true);

    connect(ui->actionOpen, &QAction::triggered, this, &MainWindow::onOpenButtonClicked);
    connect(ui->actionSave, &QAction::triggered, this, &MainWindow::onSaveButtonClicked);
    connect(ui->actionSaveAs, &QAction::triggered, this, &MainWindow::onSaveAsButtonClicked);

    QIcon openIcon = QApplication::style()->standardIcon(QStyle::SP_DialogOpenButton);
    QIcon saveIcon = QApplication::style()->standardIcon(QStyle::SP_DialogSaveButton);
    QIcon saveAsIcon = QApplication::style()->standardIcon(QStyle::SP_DialogSaveButton);

    ui->actionOpen->setIcon(openIcon);
    ui->actionSave->setIcon(saveIcon);
    ui->actionSave->setIcon(saveAsIcon);

    ui->actionOpen->setShortcut(QKeySequence(Qt::CTRL + Qt::Key_N));
    ui->actionSave->setShortcut(QKeySequence(Qt::CTRL + Qt::Key_S));

    QMenu* convertMenu = menuBar()->addMenu(tr("转换(&C)"));

    QAction* textToSqliteAction = convertMenu->addAction(tr("txt 转 SQLite"));
    connect(textToSqliteAction, &QAction::triggered, this, &MainWindow::convertTextToSqlite);

    QAction* sqliteToTextAction = convertMenu->addAction(tr("SQLite 转 txt"));
    connect(sqliteToTextAction, &QAction::triggered, this, &MainWindow::convertSqliteToText);

    QIcon convertIcon = QApplication::style()->standardIcon(QStyle::SP_ArrowRight);
    textToSqliteAction->setIcon(convertIcon);
    sqliteToTextAction->setIcon(convertIcon);
}


MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::showProgressDialog(const QString& labelText, int minimum, int maximum)
{
    progressDialog = new QProgressDialog(labelText, "Cancel", minimum, maximum, this);
    progressDialog->setWindowModality(Qt::WindowModal);
    progressDialog->setAutoClose(false);
    progressDialog->setAutoReset(false);
    progressDialog->setValue(minimum);
    progressDialog->show();
}


void MainWindow::loadTextFile(const QString& fileName)
{
    QFile file(fileName);

    if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        qDebug() << "Failed to open file:" << fileName;
        return;
    }

    QTextStream in(&file);

    // 自动检测并设置正确的文本编码
    in.setAutoDetectUnicode(true);
    in.setCodec("UTF-16LE");

    int tableRow = 0;
    int tableCol = 0;
    QStringList rows;

    // 逐行读取文本数据
    while (!in.atEnd())
    {
        QString line = in.readLine();
        if (!line.isEmpty())
        {
            rows.append(line);
            tableRow++;

            if (tableRow == 2)
            {
                QStringList columnValues = line.split("\t", QString::SkipEmptyParts);
                tableCol = columnValues.count();
            }
        }
    }

    file.close();

    showProgressDialog("Converting...", 0, tableRow);

    // 将数据显示在表格中
    QTableWidget* tableWidget = ui->tableWidget;

    // 设置表格的行数和列数
    tableWidget->setRowCount(tableRow);
    tableWidget->setColumnCount(tableCol);

    tableWidget->blockSignals(true); // 阻止信号发射

    for (int i = 0; i < tableRow; ++i)
    {
        QStringList row = rows.at(i).split("\t");

        if (i == 1)
        {
            // 获取第二行的列名-设置表格的列标题
            tableWidget->setHorizontalHeaderLabels(row);
        }

        for (int j = 0; j < tableCol; ++j)
        {
            QTableWidgetItem* item = new QTableWidgetItem(row.at(j));
            tableWidget->setItem(i, j, item);
        }

        progressDialog->setValue(i);
        QApplication::processEvents();
        if (progressDialog->wasCanceled())
        {
            tableWidget->clear();
            break;
        }
    }

    progressDialog->hide();
    tableWidget->blockSignals(false); // 恢复信号发射
}


void MainWindow::onOpenButtonClicked()
{
    openFilePath = QFileDialog::getOpenFileName(this, "Open Text File", "", "Text Files (*.txt)");
    if (!openFilePath.isEmpty())
    {
        loadTextFile(openFilePath);
    }
}

void MainWindow::onSaveButtonClicked()
{
    if (!openFilePath.isEmpty())
    {
        QFile file(openFilePath);
        if (file.open(QIODevice::WriteOnly))
        {
            // 写入UTF-16带有BOM的标记
            QByteArray bom;
            bom.append((char)0xFF);
            bom.append((char)0xFE);
            file.write(bom);

            QTextStream out(&file);
            out.setCodec("UTF-16LE");//Qt6 use: out.setEncoding(QStringConverter::Utf16LE);

            QTableWidget* tableWidget = ui->tableWidget;
            int rowCount = tableWidget->rowCount();
            int columnCount = tableWidget->columnCount();

            for (int i = 0; i < rowCount; ++i)
            {
                for (int j = 0; j < columnCount; ++j)
                {
                    QTableWidgetItem* item = tableWidget->item(i, j);
                    if (item != nullptr)
                    {
                        QString text = item->text();
                        out << text;
                    }

                    if (j != columnCount - 1)
                    {
                        out << "\t"; // 制表符分隔
                    }
                }

                out << "\r\n"; // 换行，注意使用\r\n表示换行符
            }

            file.close();
        }
    }
}

void MainWindow::onSaveAsButtonClicked()
{
    QString fileName = QFileDialog::getSaveFileName(this, "Save Text File", "", "Text Files (*.txt)");
    if (!fileName.isEmpty())
    {
        QFile file(fileName);
        if (file.open(QIODevice::WriteOnly))
        {
            // 写入UTF-16带有BOM的标记
            QByteArray bom;
            bom.append((char)0xFF);
            bom.append((char)0xFE);
            file.write(bom);

            QTextStream out(&file);
            out.setCodec("UTF-16LE");//Qt6 use: out.setEncoding(QStringConverter::Utf16LE);

            QTableWidget* tableWidget = ui->tableWidget;
            int rowCount = tableWidget->rowCount();
            int columnCount = tableWidget->columnCount();

            for (int i = 0; i < rowCount; ++i)
            {
                for (int j = 0; j < columnCount; ++j)
                {
                    QTableWidgetItem* item = tableWidget->item(i, j);
                    if (item != nullptr)
                    {
                        QString text = item->text();
                        out << text;
                    }

                    if (j != columnCount - 1)
                    {
                        out << "\t"; // 制表符分隔
                    }
                }

                out << "\r\n"; // 换行，注意使用\r\n表示换行符
            }

            file.close();
            QMessageBox::information(this, "保存完成", "文件已成功保存。");
        }
    }
}

void MainWindow::dragEnterEvent(QDragEnterEvent *event)
{
    if (event->mimeData()->hasUrls())
    {
        event->acceptProposedAction();
    }
}

void MainWindow::dropEvent(QDropEvent *event)
{
    const QMimeData* mimeData = event->mimeData();
    if (mimeData->hasUrls())
    {
        QList<QUrl> urlList = mimeData->urls();
        if (urlList.length() == 1)
        {
            openFilePath = urlList.first().toLocalFile();
            if (!openFilePath.isEmpty())
            {
                loadTextFile(openFilePath);
            }
        }
    }
}

void MainWindow::convertTextToSqlite()
{
    // 选择要读取的文本文件
    QString textFilePath = QFileDialog::getOpenFileName(this, tr("Select Text File"), "", tr("Text Files (*.txt)"));
    if (textFilePath.isEmpty())
        return;

    // 选择要保存的SQLite文件
    QString sqliteFilePath = QFileDialog::getSaveFileName(this, tr("Save SQLite File"), "", tr("SQLite Files (*.sqlite)"));
    if (sqliteFilePath.isEmpty())
        return;

    // 打开文本文件并读取内容
    QFile textFile(textFilePath);
    if (!textFile.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        QMessageBox::critical(this, tr("Error"), tr("Failed to open text file."));
        return;
    }

    QTextStream textStream(&textFile);

    // 跳过第一行
    if (!textStream.atEnd())
    {
        textStream.readLine();
    }

    // 读取第二行作为列名
    QStringList columnNames;
    if (!textStream.atEnd())
    {
        QString secondLine = textStream.readLine();

        // 处理列名，将空格替换为下划线，将点号替换为下划线
        QString processedLine = secondLine.replace(" ", "_").replace(".", "_");

        // 将处理后的列名拆分为多个列名（以制表符为分隔符）
        columnNames = processedLine.split('\t', Qt::SkipEmptyParts);
    }

    QList<QStringList> data;
    textStream.seek(0);
    // 读取数据行
    while (!textStream.atEnd())
    {
        QString line = textStream.readLine();
        QStringList values = line.split("\t");

        // 截断行内容，保证不超过列名的个数
        if (values.size() > columnNames.size())
        {
            values = values.mid(0, columnNames.size());
        }

        data.append(values);
    }

    textFile.close();

    showProgressDialog("Converting...", 0, data.size());

    // 打开SQLite数据库
    QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName(sqliteFilePath);
    if (!database.open())
    {
        QMessageBox::critical(this, tr("Error"), tr("Failed to open SQLite database."));
        return;
    }

    // 创建表并插入数据
    QSqlQuery query(database);
    QString createTableQuery = "CREATE TABLE IF NOT EXISTS ref_list (";
    for (const QString& columnName : columnNames)
    {
        createTableQuery += "\"" + columnName + "\" TEXT, ";
    }
    createTableQuery.chop(2); // 移除最后的逗号和空格
    createTableQuery += ");";

    if (!query.exec(createTableQuery))
    {
        QMessageBox::critical(this, tr("Error"), tr("Failed to create table: %1").arg(query.lastError().text()));
        database.close();
        return;
    }

    database.transaction();

    QString insertQuery = "INSERT INTO ref_list VALUES (";
    for (int i = 0; i < columnNames.size(); ++i)
    {
        insertQuery += "?, ";
    }
    insertQuery.chop(2); // 移除最后的逗号和空格
    insertQuery += ");";

    int progress = 0;
    for (const QStringList& row : data)
    {
        if (row.size() != columnNames.size())
        {
            qDebug() << "Mismatched value count in row: " << row;
            continue; // Skip this row and proceed to the next row
        }

        query.prepare(insertQuery);
        for (int i = 0; i < row.size(); ++i)
        {
            query.addBindValue(row[i]);
        }

        if (!query.exec())
        {
            QMessageBox::critical(this, tr("Error"), tr("Failed to insert row: %1").arg(query.lastError().text()));
            database.rollback();
            database.close();
            return;
        }

        ++progress;
        progressDialog->setValue(progress);
        QApplication::processEvents();
        if (progressDialog->wasCanceled())
        {
            break;
        }
    }

    progressDialog->hide();
    database.commit();
    database.close();

    QMessageBox::information(this, tr("Conversion Complete"), tr("Text to SQLite conversion completed successfully."));
}

void MainWindow::convertSqliteToText()
{
    // Select the SQLite file
    QString sqliteFilePath = QFileDialog::getOpenFileName(this, tr("Select SQLite File"), "", tr("SQLite Files (*.sqlite)"));
    if (sqliteFilePath.isEmpty())
        return;

    // Select the text file to save
    QString textFilePath = QFileDialog::getSaveFileName(this, tr("Save Text File"), "", tr("Text Files (*.txt)"));
    if (textFilePath.isEmpty())
        return;

    // Open the SQLite database
    QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
    database.setDatabaseName(sqliteFilePath);
    if (!database.open())
    {
        QMessageBox::critical(this, tr("Error"), tr("Failed to open SQLite database."));
        return;
    }

    // Retrieve the table name from the user
    QStringList tables = database.tables();
    if (tables.isEmpty())
    {
        QMessageBox::information(this, tr("No Tables"), tr("The SQLite database does not contain any tables."));
        database.close();
        return;
    }

    // Select the table to export
    bool ok;
    QString selectedTable = QInputDialog::getItem(this, tr("Select Table"), tr("Select a table to export:"), tables, 0, false, &ok);
    if (!ok || selectedTable.isEmpty())
    {
        database.close();
        return;
    }

    // Execute a query to retrieve all data from the selected table
    QSqlQuery query;
    query.prepare(QString("SELECT * FROM %1").arg(selectedTable));
    if (!query.exec())
    {
        QMessageBox::critical(this, tr("Error"), tr("Failed to execute query: %1").arg(query.lastError().text()));
        database.close();
        return;
    }

    showProgressDialog("Converting...", 0, query.size());

    // Open the text file to write data
    QFile textFile(textFilePath);
    if (!textFile.open(QIODevice::WriteOnly))
    {
        QMessageBox::critical(this, tr("Error"), tr("Failed to open text file."));
        database.close();
        return;
    }

    // Write the BOM (Byte Order Mark) for UTF-16LE encoding
    QByteArray bom;
    bom.append((char)0xFF);
    bom.append((char)0xFE);
    textFile.write(bom);

    QTextStream textStream(&textFile);
    textStream.setCodec("UTF-16LE");

    // Write the data to the text file
    int progress = 0;
    while (query.next())
    {
        QSqlRecord record = query.record();
        for (int i = 0; i < record.count(); ++i)
        {
            QVariant value = record.value(i);
            textStream << value.toString();
            if (i < record.count() - 1)
                textStream << "\t";
        }
        textStream << "\r\n";

        ++progress;
        progressDialog->setValue(progress);
        QApplication::processEvents();
        if (progressDialog->wasCanceled())
        {
            break;
        }
    }

    progressDialog->hide();
    textFile.close();
    database.close();

    QMessageBox::information(this, tr("Conversion Complete"), tr("SQLite to Text conversion completed successfully."));
}