(1)下载QtXlsxWriter
Github上提供的项目下载地址:https://github.com/dbzhang800/QtXlsxWriter
及帮助文档连接:http://qtxlsx.debao.me
(2)下载Perl编译器(这是必须的)地址:Download & Install Perl - ActiveState下载后安装
1.编译QtXlsx模块
修改ZipReader::init()中的QList 改为QVector,并添加#include <QVector.h>
双击qtxlsx.pro打开
选中src,右键重新编译,就会生成相应文件
把include 下的QtXlsx文件夹拷贝到 E:\Qt\5.7\msvc2013\include
把lib文件夹下的Qt5Xlsxd.prl 、Qt5Xlsx.prl、Qt5Xlsx.lib、Qt5Xlsxd.lib 拷贝到E:\Qt\5.7\msvc2013\lib
把lib文件夹下的Qt5Xlsx.dll、Qt5Xlsxd.dll 拷贝到E:\Qt\5.7\msvc2013\bin下
把mkspecs/modules的qt_lib_xlsx.pri拷贝到E:\Qt\5.7\msvc2013\mkspecs\modules下
大体完成了,新建一个QT工程
在属性配置如下图
测试程序如下:
//Qxlsx文件的读
QXlsx::Document xlsx(fileName);
QXlsx::Workbook *workBook = xlsx.workbook();
QXlsx::Worksheet *workSheet = static_cast<QXlsx::Worksheet*>(workBook->sheet(0));
int rowCount = workSheet->dimension().rowCount();
int columnCount = workSheet->dimension().columnCount();
int tRowStart = xlsx.dimension().firstRow();
int tColStart = xlsx.dimension().firstColumn();
for (int i = tRowStart+1; i <= rowCount; i++)
{
QXlsx::Cell *pCellDid = workSheet->cellAt(i, 1);
QString sDid = pCellDid->value().toString();
QXlsx::Cell *pCellkey = workSheet->cellAt(i, 2);
QString sKey = pCellkey->value().toString();
QXlsx::Cell *pCellMac = workSheet->cellAt(i, 3);
QString sMac = pCellMac->value().toString();
QXlsx::Cell *pModel = workSheet->cellAt(i, 4);
QString sModel = pModel->value().toString();
}
转载于:QtXlsxWriter编译和配置(VS2013+Qt5.6.0)_wokaowokaowokao12345的专栏-CSDN博客
2.QtXlsx免编译配置
把G:\QtXlsxWriter-master\src下的xlsx拷贝到新建的工程中
修改如下内容:
//#include <private/qzipwriter_p.h>
#include <QtGui\5.7.1\QtGui\private\qzipwriter_p.h>
//#include <private/qzipreader_p.h>
#include <QtGui\5.7.1\QtGui\private\qzipreader_p.h>
//添加头文件#include<qvector.h>
把QList替换成QVector
QVector<QZipReader::FileInfo> allFiles = m_reader->fileInfoList();
把xlsx下的所有头文件、cpp文件填加到工程中,如图所示
转载于 QtXlsxWriter 免编译 配置VS2013工程_wokaowokaowokao12345的专栏-CSDN博客
3.QAxObject 读写excel
QAxObject* excel = new QAxObject("Excel.Application");
excel->setProperty("Visible", false);
QAxObject* workbooks = excel->querySubObject("WorkBooks");
workbooks->dynamicCall("Open (const QString&)", QString("c:\\副本1.xls")); //filename
QAxObject* workbook = excel->querySubObject("ActiveWorkBook");
QAxObject* worksheets = workbook->querySubObject("WorkSheets");
QAxObject* worksheet = workbook->querySubObject("Worksheets(int)", 1); //worksheet number
QAxObject* usedrange = worksheet->querySubObject("UsedRange");
QAxObject* rows = usedrange->querySubObject("Rows");
QAxObject* columns = usedrange->querySubObject("Columns");
int intRowStart = usedrange->property("Row").toInt();
int intColStart = usedrange->property("Column").toInt();
int intCols = columns->property("Count").toInt();
int intRows = rows->property("Count").toInt();
QAxObject * cell;
for (int i = intRowStart; i < intRowStart + intRows; i++)
{
for (int j = intColStart; j < intColStart + intCols; j++)
{
cell = excel->querySubObject("Cells(Int, Int)", i, j );
QVariant cellValue = cell->dynamicCall("value");
cout << "Content " << cellValue.toString().toStdString() << endl; //value of the cell
}
}
excel->setProperty("DisplayAlerts", 0);
workbook->dynamicCall("Save(void)");
workbook->dynamicCall("Close (Boolean)", false);
excel->setProperty("DisplayAlerts",1);
delete excel;
//readExcel.h
#ifndef READEXCEL_H
#define READEXCEL_H
#include <map>
#include <string>
#include <vector>
#include <iostream>
#include <QAxObject>
#include <QString>
#include <QFile>
class Position
{
public:
Position() {
m_row = 0;
m_col = 0;
}
Position(int row, int col) {
m_row = row;
m_col = col;
}
public:
int m_row;
int m_col;
public:
bool operator<(const Position & ct) const // 两个const是必需的。
{
if (m_row < ct.m_row) {
return true;
} else if (m_row == ct.m_row) {
if ( m_col < ct.m_col ) {
return true;
}
}
return false;
}
};
class ReadExcel
{
public:
ReadExcel();
~ReadExcel();
public:
//打开excel文件
bool openExcel(const QString& filename);
//获取 指定单元格的数据
std::string getCellData(const int& row, const int& col);
//获取 行数,列数
void getInfo(int& row, int& col) const;
private:
void getALLfromExcel();
private:
int m_row; //行
int m_col; //列
QString m_filename;
QAxObject* m_excel;
std::map<Position, std::string> m_mapdata;
Position p;
};
#endif // READEXCEL_H
//readExcel.cpp
#include "readexcel.h"
#include <QDebug>
#include <iostream>
ReadExcel::ReadExcel()
:m_row(0), m_col(0), m_filename("")
{
m_excel = new QAxObject("Excel.Application");
}
ReadExcel::~ReadExcel()
{
if (!m_mapdata.empty()) {
m_mapdata.clear();
}
delete m_excel;
}
//
bool ReadExcel::openExcel(const QString &filename)
{
if (filename.isEmpty()) {
m_row = 0;
m_col = 0;
return false;
}
QFile file(filename);
if (!file.exists()){
m_row = 0;
m_col = 0;
return false;
};
if (!m_mapdata.empty()) {
m_mapdata.clear();
}
m_filename = filename;
try {
getALLfromExcel();
} catch (...) {
return false;
}
return true;
}
void ReadExcel::getInfo(int &row, int &col) const
{
row = m_row;
col = m_col;
}
std::string ReadExcel::getCellData(const int &row, const int &col)
{
if (row >= 1 && row <= m_row && col >= 1 && col <= m_col) {
p.m_row = row;
p.m_col = col;
return m_mapdata[p];
} else {
return NULL;
}
}
void ReadExcel::getALLfromExcel()
{
m_excel->setProperty("Visible", 0);
QAxObject* workbooks = m_excel->querySubObject("WorkBooks");
workbooks->dynamicCall("Open (const QString&)", m_filename);
QAxObject* workbook = m_excel->querySubObject("ActiveWorkBook");
QAxObject* worksheets = workbook->querySubObject("WorkSheets");
QAxObject* worksheet = workbook->querySubObject("Worksheets(int)", 1); //worksheet number
QAxObject* usedrange = worksheet->querySubObject("UsedRange");
QAxObject* rows = usedrange->querySubObject("Rows");
QAxObject* columns = usedrange->querySubObject("Columns");
int intRowStart = usedrange->property("Row").toInt();
int intColStart = usedrange->property("Column").toInt();
int intCols = columns->property("Count").toInt();
int intRows = rows->property("Count").toInt();
m_row = intRows;
m_col = intCols;
QAxObject * cell;
for (int i = intRowStart; i < intRowStart + intRows; i++)
{
for (int j = intColStart; j < intColStart + intCols; j++)
{
Position pos(i, j);
cell = m_excel->querySubObject("Cells(Int, Int)", i, j );
QVariant cellValue = cell->dynamicCall("value");
m_mapdata.insert(std::pair<Position, std::string>(pos, cellValue.toString().toStdString()));
}
}
m_excel->setProperty("DisplayAlerts", 0);
workbook->dynamicCall("Save(void)");
workbook->dynamicCall("Close (Boolean)", false);
m_excel->setProperty("DisplayAlerts",1);
}
已经编译好的32位qtxlsx库的地址(include下的头文件拷贝下源码的头文件):qtXlsx已经编译好的库-QT其他资源-CSDN下载
已经编译好的64位qtxlsx库的地址:qtxlsx64位动态库.7z-互联网文档类资源-CSDN下载