QXlsx读写数据库

最近写读写xlsx文件的工具,用了Qt自带的比较卡,操作也不舒服,最后选择用了QXlsx

QXlsx源码地址:github  https://github.com/dbzhang800/QtXlsxWriter

QXlsx官网连接: Documentation: http://qtxlsx.debao.me 

 主要用到了sqlite3 跟 QXlsx:

以下是记录使用过程:

1. pro文件中导入QXlsx源码,这里没有使用动态库,直接将代码编译到程序了
    添加:
        include(xlsx/qtxlsx.pri)
    
    如下图结构:

将对头文件加入即可

#include "xlsxdocument.h"
#include "xlsxworkbook.h"
#include "xlsxworksheet.h"

using namespace QXlsx;

读取xlsx写入数据库:

/* 导入处理 */
void xlsxProcTask::inportXlsx(const QString &xlsxName) {
  QStringList list = threadSqlite.getTableInfo("test");
  QXlsx::Document doc(xlsxName);

  int sheetCount = doc.sheetNames().count();
  int row, coulmn;
  QXlsx::CellRange range;
  QStringList rowListData; /* 记录一行数据 */
  QStringList headList;    /* 记录表头 */
  QList<int> delList;      /* 记录删除列 */
  QString tableName, valueData, tmpName, tmpValue;

  threadSqlite.openTransaction();
  for (int cnt = 0; cnt < sheetCount; cnt++) {
    rowListData.clear();
    tableName.clear();
    valueData.clear();
    delList.clear();
    headList.clear();
    tmpName.clear();
    tmpValue.clear();
    tableName = QString("INSERT INTO test (");
    valueData = QString(" VALUES ( ");
    doc.selectSheet(doc.sheetNames().at(cnt));

    range = doc.dimension();
    row = range.lastRow();
    coulmn = range.lastColumn();

    /* 表头查询 */
    for (int cCnt = 1; cCnt <= coulmn; cCnt++) {
      headList.append(doc.read(1, cCnt).toString());
    }

    /* 表头通过数据库过滤 */
    for (int cCnt = headList.count() - 1; cCnt >= 0; cCnt--) {
      if (-1 == list.indexOf(headList.at(cCnt))) {
        delList.append(cCnt);
        headList.removeAt(cCnt);
        continue;
      }
    }

    /* 写sql语句 表项 */
    for (int cCnt = 0; cCnt < headList.count(); cCnt++) {
      if (cCnt == 0) {
        tableName += QString(" %1 ").arg(headList.at(cCnt));
      } else {
        tableName += QString(" , %1 ").arg(headList.at(cCnt));
      }
    }
    tableName += QString(" ) ");

    /* 按照行写入 */
    for (int rCnt = 2; rCnt <= row; rCnt++) {
      tmpName = tableName;
      tmpValue = valueData;
      rowListData.clear();

      /* 列处理一个单元格 */
      for (int cCoumn = 1; cCoumn <= coulmn; cCoumn++) {
        if (0 != delList.count()) {/*  */
          if (-1 != delList.indexOf(cCoumn)) {
            continue;
          }
        }
        rowListData.append(doc.read(rCnt, cCoumn).toString());
      }

      for (int cCnt = 0; cCnt < rowListData.count(); cCnt++) {
        if (cCnt == 0) {
          tmpValue += QString("'%1'").arg(procString(rowListData.at(cCnt)));
        } else {
          tmpValue += QString(", '%1' ").arg(procString(rowListData.at(cCnt)));
        }
      }

      tmpValue += QString(")");
      tmpName += tmpValue;

      threadSqlite.write(tmpName);
    }
  }

  threadSqlite.closeTransaction();

  emit importFinished();
}

数据库操作函数:

.h

#ifndef OPERASQLITE_H
#define OPERASQLITE_H

#include <QObject>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <QSqlError>
#include <QSqlRecord>
#include <QtSql>

static QString clearSeq = QString("delete from ");
class operaSqlite : QObject {
  Q_OBJECT
public:
  operaSqlite();
  ~operaSqlite();
  void setContain(QString);
  void openTransaction();
  void closeTransaction();
  void write(const QString &);
  bool openSqlite(QString myDb, QString &error);
  bool closeSqlite();
  QStringList getTableInfo(QString tabName);
  void clearSqlite(QString &tableName);
  bool isOpen();
  QSqlDatabase getDb();

private:
  QSqlDatabase sqliteDb;
  QString sqlContain;
  QString contions;
};

#endif // OPERASQLITE_H

.cpp
#include "operasqlite.h"

operaSqlite::operaSqlite() {}

void operaSqlite::setContain(QString contain) { sqlContain = contain; }

operaSqlite::~operaSqlite() { closeSqlite(); }

bool operaSqlite::openSqlite(QString myDb, QString &error) {
  bool bRet = false;
  do {
    if (myDb.isEmpty()) {
      break;
    }

    if (QSqlDatabase::contains(sqlContain)) {
      sqliteDb = QSqlDatabase::database(sqlContain);
      if (false == sqliteDb.isOpen()) {
        sqliteDb.open();
      } else {
        error = QString("数据库打开错误:%1").arg(sqliteDb.lastError().text());
      }
    } else {
      sqliteDb = QSqlDatabase::addDatabase("QSQLITE", sqlContain);
      sqliteDb.setDatabaseName(myDb);
      if (false == sqliteDb.open("root", "Smaller")) {
        error = QString("数据库打开错误:%1").arg(sqliteDb.lastError().text());
        break;
      }
    }
    bRet = true;
  } while (0);

  return bRet;
}

bool operaSqlite::closeSqlite() {
  bool bRet = false;
  QSqlDatabase db;
  do {
    if (QSqlDatabase::contains(sqlContain)) {
      db = QSqlDatabase::database(sqlContain);
      if (true == db.isOpen()) {
        db.close();
      }
    }
    bRet = true;
  } while (0);
  return bRet;
}

QStringList operaSqlite::getTableInfo(QString tabName) {
  QStringList nameList;
  QSqlQuery query(sqliteDb);
  QString getTableInfo = "PRAGMA table_info(" + tabName + ")";

  query.prepare(getTableInfo);

  if (query.exec()) {
    while (query.next()) {
      nameList.append(
          query.value(query.record().indexOf(QString("name"))).toString());
    }
  } else {
    qDebug() << query.lastError();
  }

  return nameList;
}

void operaSqlite::openTransaction() { sqliteDb.transaction(); }
void operaSqlite::closeTransaction() { sqliteDb.commit(); }

void operaSqlite::write(const QString &data) {
  QSqlQuery query(sqliteDb);
  if (false == query.exec(data)) {
    qDebug() << query.lastError().text();
  }
}

void operaSqlite::clearSqlite(QString &tableName) {
  QString execSql = clearSeq + tableName;
  do {
    QSqlQuery query(sqliteDb);

    if (false == query.exec(execSql)) {
      qDebug() << query.lastError().text();
      break;
    }

    execSql = QString("select * from sqlite_sequence");
    if (false == query.exec(execSql)) {
      qDebug() << query.lastError().text();
      break;
    }

    execSql = QString("update sqlite_sequence set seq=0 where name='%1'")
                  .arg(tableName);
    if (false == query.exec(execSql)) {
      qDebug() << query.lastError().text();
      break;
    }
  } while (0);
}

bool operaSqlite::isOpen() { return sqliteDb.isOpen(); }

QSqlDatabase operaSqlite::getDb() { return sqliteDb; }

写出xlsx函数:

/* 写表格处理 */
bool xlsxProcTask::writeXlsx(const QString &sql, const QStringList &head,
                             const QString &xlsxName) {
  bool bRet = false;
  QString error;
  operaSqlite sqlWriteXlsx;
  sqlWriteXlsx.setContain("79288789");
  sqlWriteXlsx.openSqlite("wx.db", error);
  QString saveFileName;
  do {
    if (sql.isEmpty())
      break;

    Document xlsx;
    Format formatXls;
    formatXls.setFontSize(11);
    formatXls.setHorizontalAlignment(QXlsx::Format::AlignHCenter);

    /* 写入head */
    for (int cnt = 0; cnt < head.count(); cnt++) {
      xlsx.write(1, cnt + 1, head.at(cnt), formatXls);
    }

    /* 查询数据库 */
    QSqlQuery query(sqlWriteXlsx.getDb());
    if (false == query.exec(sql)) {
      break;
    }

    int row = 2;/* 第一行是表头, 表格处理默认是从(1,1)起始 */
    while (query.next()) {
      for (int i = 0; i < head.count(); i++) { /* 写入一行 */

        xlsx.write(row, i + 1,
                   query.value(query.record().indexOf(head.at(i))).toString());
      }
      row++; /* 多行写入,查询多少行就写入多少 */
    }

    /* 保存文件 */
    saveFileName += (xlsxParam.savePath + "/" + xlsxName + QString(".xlsx"));

    CellRange range = xlsx.dimension();
    row = range.lastRow();
    if (row == 1)/* 1行主要是空表格 直接不保存了 */
      break;

    /* 有数据的表格保存 */
    xlsx.saveAs(saveFileName);
    bRet = true;
  } while (0);

  if (sqlWriteXlsx.isOpen()) {
    sqlWriteXlsx.closeSqlite();
  }
  return bRet;
}

文中使用的结构体(自用的定义自己可以随便换 对应的处理换掉就行了):

enum {
  noUse = 0,
  fiveSplit = 1,
  fiveGet,
  sixSplit,
  sixGet,
  fiveUserGet,
  sixUserGet
};

typedef struct {
  qint8 classType; 
  QStringList classList;
} sectorClass_t;

typedef struct {
  bool isDel; /* true 删除,false 不操作 */
  QStringList delCoumnList;
} delColumn_t;

typedef struct {
  bool isAdd;               /* true 删除,false 不操作 */
  QString coumnName;        /* 列名 */
  QStringList AddCoumnList; /* 计算公式 */
} calculation_t;

typedef struct {
  QString xlsxName;         /* 表格名称 */
  QString savePath;         /* 保存路径 */
  QString sheetName;        /* sheet名称 */
  QString tabName;          /* 数据库表名 */
  sectorClass_t sector;     /* 提取信息 */
  delColumn_t delColumn;    /* 待删除信息 */
  calculation_t calulation; /* 添加信息 */
  QXlsx::Document *doc;
} xlsxParam_t;