Skip to content
Navigation Menu
{{ message }}
forked from francescoyang/stroage
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathexcelengine.cpp
More file actions
436 lines (373 loc) · 10.8 KB
/
Copy pathexcelengine.cpp
File metadata and controls
436 lines (373 loc) · 10.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
#include "excelengine.h"
ExcelEngine::ExcelEngine(QObject *parent) :
QObject(parent)
{
pExcel = NULL;
pWorkbooks = NULL;
pWorkbook = NULL;
pWorksheet = NULL;
sXlsFile = "";
nRowCount = 0;
nColumnCount = 0;
nStartRow = 0;
nStartColumn = 0;
bIsOpen = false;
bIsValid = false;
bIsANewFile = false;
bIsSaveAlready = false;
HRESULT r = OleInitialize(0);
if (r != S_OK && r != S_FALSE)
{
qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
}
// connect(this, SIGNAL(signals_saveExecl(QMap<int,QTableWidget *>)),this, SLOT(saveExecl(QMap<int,QTableWidget *>)));
}
ExcelEngine::ExcelEngine(QString xlsFile)
{
pExcel = NULL;
pWorkbooks = NULL;
pWorkbook = NULL;
pWorksheet = NULL;
sXlsFile = xlsFile;
nRowCount = 0;
nColumnCount = 0;
nStartRow = 0;
nStartColumn = 0;
bIsOpen = false;
bIsValid = false;
bIsANewFile = false;
bIsSaveAlready = false;
HRESULT r = OleInitialize(0);
if (r != S_OK && r != S_FALSE)
{
qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r);
}
}
ExcelEngine::~ExcelEngine()
{
if ( bIsOpen )
{
//析构前，先保存数据，然后关闭workbook
Close();
}
OleUninitialize();
}
/**
*@brief 打开sXlsFile指定的excel报表
*@return true : 打开成功
* false: 打开失败
*/
bool ExcelEngine::Open(UINT nSheet, bool visible)
{
if ( bIsOpen )
{
//return bIsOpen;
Close();
}
nCurrSheet = nSheet;
bIsVisible = visible;
if ( NULL == pExcel )
{
pExcel = new QAxObject("Excel.Application");
if ( pExcel )
{
bIsValid = true;
}
else
{
bIsValid = false;
bIsOpen = false;
return bIsOpen;
}
pExcel->dynamicCall("SetVisible(bool)", bIsVisible);
}
if ( !bIsValid )
{
bIsOpen = false;
return bIsOpen;
}
if ( sXlsFile.isEmpty() )
{
bIsOpen = false;
return bIsOpen;
}
/*如果指向的文件不存在，则需要新建一个*/
QFile f(sXlsFile);
if (!f.exists())
{
bIsANewFile = true;
}
else
{
bIsANewFile = false;
}
if (!bIsANewFile)
{
pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿
pWorkbook = pWorkbooks->querySubObject("Open(QString, QVariant)",sXlsFile,QVariant(0)); //打开xls对应的工作簿
}
else
{
pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿
pWorkbooks->dynamicCall("Add"); //添加一个新的工作薄
pWorkbook = pExcel->querySubObject("ActiveWorkBook"); //新建一个xls
}
pWorksheet = pWorkbook->querySubObject("WorkSheets(int)", nCurrSheet);//打开第一个sheet
//至此已打开，开始获取相应属性
QAxObject *usedrange = pWorksheet->querySubObject("UsedRange");//获取该sheet的使用范围对象
QAxObject *rows = usedrange->querySubObject("Rows");
QAxObject *columns = usedrange->querySubObject("Columns");
//因为excel可以从任意行列填数据而不一定是从0,0开始，因此要获取首行列下标
nStartRow = usedrange->property("Row").toInt(); //第一行的起始位置
nStartColumn = usedrange->property("Column").toInt(); //第一列的起始位置
nRowCount = rows->property("Count").toInt(); //获取行数
nColumnCount = columns->property("Count").toInt(); //获取列数
bIsOpen = true;
return bIsOpen;
}
/**
*@brief Open()的重载函数
*/
bool ExcelEngine::Open(QString xlsFile, UINT nSheet, bool visible)
{
sXlsFile = xlsFile;
nCurrSheet = nSheet;
bIsVisible = visible;
return Open(nCurrSheet,bIsVisible);
}
void ExcelEngine::saveExecl(QMap<int,QTableWidget *> tmp)
{
if(!Open("test.xls",1,false))
qDebug() << "open xls error";
qDebug() << "ExcelEngine::saveExecl";
if(!SaveDataFrTable(tmp[1])){
qDebug() << "output execl error";
}
this->Close();
// if(!excelengine->Open("test.xls",1,false))
// qDebug() << "open xls error";
}
/**
*@brief 保存表格数据，把数据写入文件
*/
void ExcelEngine::Save()
{
if ( pWorkbook )
{
if (bIsSaveAlready)
{
return ;
}
// qDebug() << "save as" << sXlsFile;
// pWorkbook->dynamicCall("SaveAs (const QString&,int,const QString&,const QString&,bool,bool)",
// sXlsFile,56,QString(""),QString(""),false,false);
if (!bIsANewFile)
{
qDebug() << "Save is new file " << sXlsFile;
pWorkbook->dynamicCall("Save()");
}
else /*如果该文档是新建出来的，则使用另存为COM接口*/
{
qDebug() << "save as" << sXlsFile;
// pWorkbook->dynamicCall("SaveAs (const QString&)",
// "d:/code/svn/acanoe_brower/test.xls");
pWorkbook->dynamicCall("SaveAs (const QString&,int,const QString&,const QString&,bool,bool)",
sXlsFile,56,QString(""),QString(""),false,false);
}
bIsSaveAlready = true;
}
}
/**
*@brief 关闭前先保存数据，然后关闭当前Excel COM对象，并释放内存
*/
void ExcelEngine::Close()
{
//关闭前先保存数据
Save();
if ( pExcel && pWorkbook )
{
pWorkbook->dynamicCall("Close(bool)", true);
pExcel->dynamicCall("Quit()");
delete pExcel;
pExcel = NULL;
bIsOpen = false;
bIsValid = false;
bIsANewFile = false;
bIsSaveAlready = true;
}
}
/**
*@brief 把tableWidget中的数据保存到excel中
*@param tableWidget : 指向GUI中的tablewidget指针
*@return 保存成功与否 true : 成功
* false: 失败
*/
bool ExcelEngine::SaveDataFrTable(QTableWidget *tableWidget)
{
if ( NULL == tableWidget )
{
return false;
}
if ( !bIsOpen )
{
return false;
}
int tableR = tableWidget->rowCount();
int tableC = tableWidget->columnCount();
//获取表头写做第一行
for (int i=0; i<tableC; i++)
{
if ( tableWidget->horizontalHeaderItem(i) != NULL )
{
this->SetCellData(1,i+1,tableWidget->horizontalHeaderItem(i)->text());
}
}
//写数据
for (int i=0; i<tableR; i++)
{
for (int j=0; j<tableC; j++)
{
if ( tableWidget->item(i,j) != NULL )
{
this->SetCellData(i+2,j+1,tableWidget->item(i,j)->text());
}
}
}
qDebug() << " ExcelEngine::SaveDataFrTable " << sXlsFile;
//保存
Save();
return true;
}
/**
*@brief 从指定的xls文件中把数据导入到tableWidget中
*@param tableWidget : 执行要导入到的tablewidget指针
*@return 导入成功与否 true : 成功
* false: 失败
*/
bool ExcelEngine::ReadDataToTable(QTableWidget *tableWidget)
{
if ( NULL == tableWidget )
{
return false;
}
//先把table的内容清空
int tableColumn = tableWidget->columnCount();
tableWidget->clear();
for (int n=0; n<tableColumn; n++)
{
tableWidget->removeColumn(0);
}
int rowcnt = nStartRow + nRowCount;
int columncnt = nStartColumn + nColumnCount;
//获取excel中的第一行数据作为表头
QStringList headerList;
for (int n = nStartColumn; n<columncnt; n++ )
{
QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)",nStartRow, n);
if ( cell )
{
headerList<<cell->dynamicCall("Value2()").toString();
}
}
//重新创建表头
tableWidget->setColumnCount(nColumnCount);
tableWidget->setHorizontalHeaderLabels(headerList);
//插入新数据
for (int i = nStartRow+1, r = 0; i < rowcnt; i++, r++ ) //行
{
tableWidget->insertRow(r); //插入新行
for (int j = nStartColumn, c = 0; j < columncnt; j++, c++ ) //列
{
QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)", i, j );//获取单元格
//在r新行中添加子项数据
if ( cell )
{
tableWidget->setItem(r,c,new QTableWidgetItem(cell->dynamicCall("Value2()").toString()));
}
}
}
return true;
}
/**
*@brief 获取指定单元格的数据
*@param row : 单元格的行号
*@param column : 单元格的列号
*@return [row,column]单元格对应的数据
*/
QVariant ExcelEngine::GetCellData(UINT row, UINT column)
{
QVariant data;
QAxObject *cell = pWorksheet->querySubObject("Cells(int,int)",row,column);//获取单元格对象
if ( cell )
{
data = cell->dynamicCall("Value2()");
}
return data;
}
/**
*@brief 修改指定单元格的数据
*@param row : 单元格的行号
*@param column : 单元格指定的列号
*@param data : 单元格要修改为的新数据
*@return 修改是否成功 true : 成功
* false: 失败
*/
bool ExcelEngine::SetCellData(UINT row, UINT column, QVariant data)
{
bool op = false;
QAxObject *cell = pWorksheet->querySubObject("Cells(int,int)",row,column);//获取单元格对象
if ( cell )
{
QString strData = data.toString(); //excel 居然只能插入字符串和整型，浮点型无法插入
cell->dynamicCall("SetValue(const QVariant&)",strData); //修改单元格的数据
op = true;
}
else
{
op = false;
}
return op;
}
/**
*@brief 清空除报表之外的数据
*/
void ExcelEngine::Clear()
{
sXlsFile = "";
nRowCount = 0;
nColumnCount = 0;
nStartRow = 0;
nStartColumn = 0;
}
/**
*@brief 判断excel是否已被打开
*@return true : 已打开
* false: 未打开
*/
bool ExcelEngine::IsOpen()
{
return bIsOpen;
}
/**
*@brief 判断excel COM对象是否调用成功，excel是否可用
*@return true : 可用
* false: 不可用
*/
bool ExcelEngine::IsValid()
{
return bIsValid;
}
/**
*@brief 获取excel的行数
*/
UINT ExcelEngine::GetRowCount()const
{
return nRowCount;
}
/**
*@brief 获取excel的列数
*/
UINT ExcelEngine::GetColumnCount()const
{
return nColumnCount;
}
You can’t perform that action at this time.