QT笔记——QtXlsx操作Execl 之 读写文件

如果我们想用QtXlsx 来读写 我们的Execl

说明一下几个常用的函数：
保存文件的两种形式

//保存为 Book1.xlsx
xlsx.save(); 
//保存为 你好.xlsx
xlsx.saveAs(QStringLiteral("你好.xlsx"))
//保存为 lion_cxq.xls
xlsx.saveAs("lion_cxq.xls")

操作Exexl的常用函数

//写入单元数据
xlsx.write(i + 1, j + 1, itemStr);
//读取单元数据
cellStr = xlsx.read(i, j).toString();
//设置文件路径
QXlsx::Document xlsx(path);
//获取工作薄的指针
QXlsx::Workbook *workBook = xlsx.workbook();
//获取第0 个工作簿
QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
//获取 sheet 的 行数
int row = workSheet->dimension().rowCount();
//获取第几个单元的 指针 
QXlsx::Cell *cell = workSheet->cellAt(i, j);
//获取 该单元的数值
QString value = cell->value().toString();

运行结果：

内容：

ui图

.h文件

#pragma once

#include <QtWidgets/QWidget>
#include "ui_QtXlsxTest.h"
#include "xlsxdocument.h"
#include "xlsxworkbook.h"
#include "xlsxworksheet.h"
#include "xlsxcellrange.h"
#include "xlsxcell.h"
#include <QDebug>
#include <QMessageBox>
#include <QVariant>


class QtXlsxTest : public QWidget
{
	Q_OBJECT

public:
	enum TypeNumber
	{
		Type_QString,
		Type_Int,
	};
public:
	QtXlsxTest(QWidget* parent = Q_NULLPTR);

	void setCellValueToItemValue(int i,int j, QVariant str, TypeNumber type);
public slots:
	void on_outBtn_clicked();

	void on_inBtn_clicked();

private:
	Ui::QtXlsxTestClass ui;

	QStringList m_Vlist;
	QStringList m_Hlist;
};

.cpp文件

#include "QtXlsxTest.h"

QtXlsxTest::QtXlsxTest(QWidget* parent)
	: QWidget(parent)
{
	ui.setupUi(this);
	
	m_Hlist << QStringLiteral("班级") << QStringLiteral("姓名") << QStringLiteral("年龄") << QStringLiteral("分数");
	m_Vlist << QStringLiteral("1") << QStringLiteral("2") << QStringLiteral("3") << QStringLiteral("4");
	
	ui.tableWidget2->setRowCount(m_Vlist.size());
	ui.tableWidget2->setColumnCount(m_Hlist.size());
	ui.tableWidget2->setHorizontalHeaderLabels(m_Hlist);
	ui.tableWidget2->setVerticalHeaderLabels(m_Vlist);
}

void QtXlsxTest::setCellValueToItemValue(int row, int col, QVariant str, TypeNumber type)
{
	int tableRowCount = ui.tableWidget2->rowCount();
	int tableColumnCount = ui.tableWidget2->columnCount();
	if (row > tableRowCount || col > tableColumnCount)
	{
		QMessageBox::warning(nullptr, QStringLiteral("警告"), QStringLiteral("插入数值错误"));
		return;
	}

	QTableWidgetItem* item = new QTableWidgetItem();
	item->setData(Qt::UserRole, QVariant::fromValue(str));
	if (type == Type_QString)
	{
		QString userData = item->data(Qt::UserRole).value<QString>();
		item->setText(userData);
	}
	else if (type == Type_Int)
	{
		int userData = item->data(Qt::UserRole).toInt();
		item->setText(QString::number(userData));
	}

	ui.tableWidget2->setItem(row, col, item);
}

void QtXlsxTest::on_outBtn_clicked()
{
	//tablewidget写出到.xlsx
	int row = ui.tableWidget->rowCount();
	int column = ui.tableWidget->columnCount();

	QXlsx::Document xlsx;
	QString itemStr;
	for (int i = 0; i < row; ++i)
	{
		for (int j = 0; j < column; ++j)
		{
			QTableWidgetItem* item = ui.tableWidget->item(i, j);
			if (item == NULL)
				itemStr = "";
			else
				itemStr = item->text();

			//因为execl  起始位置为（1,1）
			xlsx.write(i + 1, j + 1, itemStr);
		}
	}

	//保存为 Book1.xlsx
	xlsx.save(); 

	if (xlsx.saveAs(QStringLiteral("你好.xlsx")))
		QMessageBox::information(nullptr, QStringLiteral("信息"), QStringLiteral("保存成功"));

	if(xlsx.saveAs("lion_cxq.xls"))
		QMessageBox::information(nullptr, QStringLiteral("信息"), QStringLiteral("保存成功"));
}

void QtXlsxTest::on_inBtn_clicked()
{
	ui.tableWidget2->clearContents();
	ui.tableWidget2->setRowCount(0);

	//读取.xlsx 到 tablewidget
	QString path = QString("1.xlsx");
	//选择需要读取的execl 的文件 路径
	QXlsx::Document xlsx(path);
	//获取工作薄 的 指针
	QXlsx::Workbook* workBook = xlsx.workbook();

	if (workBook == nullptr)
	{
		QMessageBox::warning(nullptr, QStringLiteral("警告"), QStringLiteral("工作簿为空指针"));
		return;
	}

	//获取sheet的数量
	int sheetCount = workBook->sheetCount();
	if (sheetCount < 0)
	{
		QMessageBox::warning(nullptr, QStringLiteral("警告"), QStringLiteral("工作簿小于0个"));
		return;
	}
	qDebug() << QStringLiteral("工作薄数量") << sheetCount;

	//将第0 个 工作薄 转化为 
	QXlsx::Worksheet* workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
	if (workSheet == nullptr)
	{
		QMessageBox::warning(nullptr, QStringLiteral("警告"), QStringLiteral("工作sheet为空指针"));
		return;
	}

	int rowCount = workSheet->dimension().rowCount();
	int columnCount = workSheet->dimension().columnCount();
	qDebug() << QStringLiteral("工作sheet的行数：") << rowCount << QStringLiteral("工作sheet的列数") << columnCount;

	if (rowCount <= 0 || columnCount <= 0)
	{
		QMessageBox::warning(nullptr, QStringLiteral("警告"), QStringLiteral("工作sheet的行列为空"));
		return;
	}

	ui.tableWidget2->setRowCount(rowCount);
	ui.tableWidget2->setVerticalHeaderLabels(m_Vlist);

	QString cellStr;
	//获取execl 的 值
	for (int i = 1; i <= rowCount; ++i)
	{
		for (int j = 1; j <= columnCount; ++j)
		{
			QXlsx::Cell* cell = workSheet->cellAt(i, j);
			if (cell == nullptr)
				cellStr = "";
			else
				cellStr = cell->value().toString();

			//将值插入到tablewidget 中
			setCellValueToItemValue(i - 1,j - 1,cellStr, Type_QString);

		}
	}

}

参考博客：
QtXlsx读写数据