配置xxx.pro
引入Active Qt库
QT += axcontainer
引入objbase.h，解决返回QAxObject为空的问题
1 #include <objbase.h>
2
3 //在需要创建QAxObject()上方调用如下代码
4 CoInitializeEx(NULL, COINIT_MULTITHREADED);
5 m_pExcel = new(std::nothrow) QAxObject();
因为QAxObject默认是在单线程下使用的，因此如果不用上门代码申明多线程, 会导致获取的excel的QAxObject都是NULL
保存或者打开excel的路径需要统一用""
用QDir::toNativeSeparators(m_strPath)进行转换m_pWorkbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(m_strPath));
SaveAs必须在所有操作结束后调用，否则不会保存到excel中
可以放到close前调用即可
1 m_pWorkbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(m_strPath));
2 m_pWorkbook->dynamicCall("Close()");
3 m_pExcel->dynamicCall("Quit()");
4 delete m_pExcel;
5 m_pExcel = NULL;
Demo
1 #ifndef EXCELOPERATOR_H
2 #define EXCELOPERATOR_H
3
4 #include <QObject>
5 #include <ActiveQt/QAxObject>
6 #include <QDebug>
7 #include <QDir>
8
9 class ExcelOperator : public QObject
10 {
11 Q_OBJECT
12 public:
13 explicit ExcelOperator(QObject *parent = nullptr);
14 ~ExcelOperator();
15 //打开文件
16 bool open(QString path);
17 //关闭文件
18 bool close();
19 //获取工作表数量
20 int getSheetsCount();
21 //根据名称创建工作表
22 QAxObject* addSheet(QString name);
23 //根据名称删除工作表
24 bool delSheet(QString name);
25 //根据编号删除工作表
26 bool delSheet(int index);
27 //根据名称获取工作表
28 QAxObject* getSheet(QString name);
29 //根据编号获取工作表
30 QAxObject* getSheet(int index);
31 //获取行对象
32 QAxObject* getRows(QAxObject* pSheet);
33 //获取行数
34 int getRowsCount(QAxObject* pSheet);
35 //获取列对象
36 QAxObject* getColumns(QAxObject* pSheet);
37 //获取列数
38 int getColumnsCount(QAxObject* pSheet);
39 //根据行列值获取单元格值, 如: 3行，5列
40 QString getCell(QAxObject* pSheet, int row, int column);
41 //根据行列编号获取单元格值, 如: "F6"
42 QString getCell(QAxObject* pSheet, QString number);
43 //根据行列值设置单元格值
44 bool setCell(QAxObject* pSheet, int row, int column, QString value);
45 //根据行列编号设置单元格值
46 bool setCell(QAxObject* pSheet, QString number, QString value);
47
48
49 signals:
50
51 public slots:
52 private:
53 QAxObject* m_pExcel;
54 QAxObject* m_pWorksheets;
55 QAxObject* m_pWorkbook;
56 QString m_strPath;
57 };
58
59 #endif // EXCELOPERATOR_H
1 #include "exceloperator.h"
2 #include <objbase.h>
3
4 ExcelOperator::ExcelOperator(QObject *parent) : QObject(parent)
5 , m_pExcel(NULL)
6 , m_pWorksheets(NULL)
7 , m_pWorkbook(NULL)
8 {
9
10 }
11
12 ExcelOperator::~ExcelOperator()
13 {
14 close();
15 }
16
17 bool ExcelOperator::open(QString path)
18 {
19 m_strPath = path;
20 QAxObject *pWorkbooks = NULL;
21 CoInitializeEx(NULL, COINIT_MULTITHREADED);
22 m_pExcel = new(std::nothrow) QAxObject();
23 if (NULL == m_pExcel) {
24 qCritical()<<"创建Excel对象失败...";
25 return false;
26 }
27 try {
28 m_pExcel->setControl("Excel.Application");
29 m_pExcel->dynamicCall("SetVisible(bool)", false); //true 表示操作文件时可见，false表示为不可见
30 m_pExcel->setProperty("DisplayAlerts", false);
31 pWorkbooks = m_pExcel->querySubObject("WorkBooks");
32 pWorkbooks->dynamicCall("Add");
33 m_pWorkbook = m_pExcel->querySubObject("ActiveWorkBook");
34 qDebug()<<"excel path: "<<m_strPath;
35
36 // 获取打开的excel文件中所有的工作sheet
37 m_pWorksheets = m_pWorkbook->querySubObject("WorkSheets");
38 } catch (...) {
39 qCritical()<<"打开文件失败...";
40 return false;
41 }
42
43 return true;
44 }
45
46 bool ExcelOperator::close()
47 {
48 qDebug()<<"excel close...";
49 if (m_pExcel)
50 {
51 qDebug()<<"closing...";
52 m_pWorkbook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(m_strPath));
53 m_pWorkbook->dynamicCall("Close()");
54 m_pExcel->dynamicCall("Quit()");
55 delete m_pExcel;
56 m_pExcel = NULL;
57 }
58 return true;
59 }
60
61 int ExcelOperator::getSheetsCount()
62 {
63 int count = 0;
64 count = m_pWorksheets->property("Count").toInt();
65 return count;
66 }
67
68
69 QAxObject* ExcelOperator::addSheet(QString name)
70 {
71 QAxObject *pWorkSheet = NULL;
72 try {
73 int count = m_pWorksheets->property("Count").toInt(); //获取工作表数目
74 QAxObject *pLastSheet = m_pWorksheets->querySubObject("Item(int)", count);
75 pWorkSheet = m_pWorksheets->querySubObject("Add(QVariant)", pLastSheet->asVariant());
76 pLastSheet->dynamicCall("Move(QVariant)", pWorkSheet->asVariant());
77 pWorkSheet->setProperty("Name", name); //设置工作表名称
78 } catch (...) {
79 qCritical()<<"创建sheet失败...";
80 }
81 return pWorkSheet;
82 }
83
84 bool ExcelOperator::delSheet(QString name)
85 {
86 try {
87 QAxObject *pFirstSheet = m_pWorksheets->querySubObject("Item(QString)", name);
88 pFirstSheet->dynamicCall("delete");
89 } catch (...) {
90 qCritical()<<"删除sheet失败...";
91 return false;
92 }
93 return true;
94 }
95
96 bool ExcelOperator::delSheet(int index)
97 {
98 try {
99 QAxObject *pFirstSheet = m_pWorksheets->querySubObject("Item(int)", index);
100 pFirstSheet->dynamicCall("delete");
101 } catch (...) {
102 qCritical()<<"删除sheet失败...";
103 return false;
104 }
105 return true;
106 }
107
108 QAxObject* ExcelOperator::getSheet(QString name)
109 {
110 QAxObject* pWorkSheet = NULL;
111 try {
112 pWorkSheet = m_pWorksheets->querySubObject("Item(QString)", name);
113 } catch (...) {
114 qCritical()<<"获取sheet失败...";
115 }
116 return pWorkSheet;
117 }
118
119 QAxObject* ExcelOperator::getSheet(int index)
120 {
121 QAxObject* pWorkSheet = NULL;
122 try {
123 pWorkSheet = m_pWorksheets->querySubObject("Item(int)", index);
124 } catch (...) {
125 qCritical()<<"获取sheet失败...";
126 }
127 return pWorkSheet;
128 }
129
130 QAxObject* ExcelOperator::getRows(QAxObject* pSheet)
131 {
132 QAxObject* pRows = NULL;
133 try {
134 pRows = pSheet->querySubObject("Rows");
135 } catch (...) {
136 qCritical()<<"获取行失败...";
137 }
138 return pRows;
139 }
140
141 int ExcelOperator::getRowsCount(QAxObject* pSheet)
142 {
143 int rows = 0;
144 try {
145 QAxObject* pRows = getRows(pSheet);
146 rows = pRows->property("Count").toInt();
147 } catch (...) {
148 qCritical()<<"获取行数失败...";
149 }
150 return rows;
151 }
152
153 QAxObject* ExcelOperator::getColumns(QAxObject* pSheet)
154 {
155 QAxObject* pColumns = NULL;
156 try {
157 pColumns = pSheet->querySubObject("Columns");
158 } catch (...) {
159 qCritical()<<"获取列失败...";
160 }
161 return pColumns;
162 }
163
164 int ExcelOperator::getColumnsCount(QAxObject* pSheet)
165 {
166 int columns = 0;
167 try {
168 QAxObject* pColumns = getColumns(pSheet);
169 columns = pColumns->property("Count").toInt();
170 } catch (...) {
171 qCritical()<<"获取列数失败...";
172 }
173 return columns;
174 }
175
176 QString ExcelOperator::getCell(QAxObject* pSheet, int row, int column)
177 {
178 QString strCell = "";
179 try {
180 QAxObject* pCell = pSheet->querySubObject("Cells(int, int)", row, column);
181 strCell = pCell->property("Value").toString();
182 } catch (...) {
183 qCritical()<<"获取单元格信息失败...";
184 }
185
186 return strCell;
187 }
188
189 QString ExcelOperator::getCell(QAxObject* pSheet, QString number)
190 {
191 QString strCell = "";
192 try {
193 QAxObject* pCell = pSheet->querySubObject("Range(QString)", number);
194 strCell = pCell->property("Value").toString();
195 } catch (...) {
196 qCritical()<<"获取单元格信息失败...";
197 }
198
199 return strCell;
200 }
201
202 bool ExcelOperator::setCell(QAxObject* pSheet, int row, int column, QString value)
203 {
204 try {
205 QAxObject* pCell = pSheet->querySubObject("Cells(int, int)", row, column);
206 pCell->setProperty("Value", value);
207 } catch (...) {
208 qCritical()<<"写入单元格信息失败...";
209 return false;
210 }
211 return true;
212 }
213
214 bool ExcelOperator::setCell(QAxObject* pSheet, QString number, QString value)
215 {
216 try {
217 QAxObject* pCell = pSheet->querySubObject("Range(QString)", number);
218 pCell->setProperty("Value", value);
219 } catch (...) {
220 qCritical()<<"写入单元格信息失败...";
221 return false;
222 }
223 return true;
224 }