首页 > 代码库 > QT 操作excel 类封装
QT 操作excel 类封装
1 # pro file 2 [plain] view plaincopy 3 CONFIG += qaxcontainer 4 5 QT += core 6 7 QT -= gui 8 9 TARGET = QExcel 10 CONFIG += console 11 CONFIG -= app_bundle 12 13 TEMPLATE = app 14 15 16 SOURCES += main.cpp \ 17 qexcel.cpp 18 19 HEADERS += \ 20 qexcel.h
1 // qexcel.h 2 3 #ifndef QEXCEL_H 4 #define QEXCEL_H 5 6 #include <QString> 7 #include <QVariant> 8 9 class QAxObject; 10 11 class QExcel : public QObject 12 { 13 public: 14 QExcel(QString xlsFilePath, QObject *parent = 0); 15 ~QExcel(); 16 17 public: 18 QAxObject * getWorkBooks(); 19 QAxObject * getWorkBook(); 20 QAxObject * getWorkSheets(); 21 QAxObject * getWorkSheet(); 22 23 public: 24 /**************************************************************************/ 25 /* 工作表 */ 26 /**************************************************************************/ 27 void selectSheet(const QString& sheetName); 28 //sheetIndex 起始于 1 29 void selectSheet(int sheetIndex); 30 void deleteSheet(const QString& sheetName); 31 void deleteSheet(int sheetIndex); 32 void insertSheet(QString sheetName); 33 int getSheetsCount(); 34 //在 selectSheet() 之后才可调用 35 QString getSheetName(); 36 QString getSheetName(int sheetIndex); 37 38 /**************************************************************************/ 39 /* 单元格 */ 40 /**************************************************************************/ 41 void setCellString(int row, int column, const QString& value); 42 //cell 例如 "A7" 43 void setCellString(const QString& cell, const QString& value); 44 //range 例如 "A5:C7" 45 void mergeCells(const QString& range); 46 void mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn); 47 QVariant getCellValue(int row, int column); 48 void clearCell(int row, int column); 49 void clearCell(const QString& cell); 50 51 /**************************************************************************/ 52 /* 布局格式 */ 53 /**************************************************************************/ 54 void getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn); 55 void setColumnWidth(int column, int width); 56 void setRowHeight(int row, int height); 57 void setCellTextCenter(int row, int column); 58 void setCellTextCenter(const QString& cell); 59 void setCellTextWrap(int row, int column, bool isWrap); 60 void setCellTextWrap(const QString& cell, bool isWrap); 61 void setAutoFitRow(int row); 62 void mergeSerialSameCellsInAColumn(int column, int topRow); 63 int getUsedRowsCount(); 64 void setCellFontBold(int row, int column, bool isBold); 65 void setCellFontBold(const QString& cell, bool isBold); 66 void setCellFontSize(int row, int column, int size); 67 void setCellFontSize(const QString& cell, int size); 68 69 /**************************************************************************/ 70 /* 文件 */ 71 /**************************************************************************/ 72 void save(); 73 void close(); 74 75 private: 76 QAxObject * excel; 77 QAxObject * workBooks; 78 QAxObject * workBook; 79 QAxObject * sheets; 80 QAxObject * sheet; 81 }; 82 83 #endif
1 // qexcel.cpp 2 3 #include <QAxObject> 4 #include <QFile> 5 #include <QStringList> 6 #include <QDebug> 7 8 #include "qexcel.h" 9 10 QExcel::QExcel(QString xlsFilePath, QObject *parent) 11 { 12 excel = 0; 13 workBooks = 0; 14 workBook = 0; 15 sheets = 0; 16 sheet = 0; 17 18 excel = new QAxObject("Excel.Application", parent); 19 workBooks = excel->querySubObject("Workbooks"); 20 QFile file(xlsFilePath); 21 if (file.exists()) 22 { 23 workBooks->dynamicCall("Open(const QString&)", xlsFilePath); 24 workBook = excel->querySubObject("ActiveWorkBook"); 25 sheets = workBook->querySubObject("WorkSheets"); 26 } 27 } 28 29 QExcel::~QExcel() 30 { 31 close(); 32 } 33 34 void QExcel::close() 35 { 36 excel->dynamicCall("Quit()"); 37 38 delete sheet; 39 delete sheets; 40 delete workBook; 41 delete workBooks; 42 delete excel; 43 44 excel = 0; 45 workBooks = 0; 46 workBook = 0; 47 sheets = 0; 48 sheet = 0; 49 } 50 51 QAxObject *QExcel::getWorkBooks() 52 { 53 return workBooks; 54 } 55 56 QAxObject *QExcel::getWorkBook() 57 { 58 return workBook; 59 } 60 61 QAxObject *QExcel::getWorkSheets() 62 { 63 return sheets; 64 } 65 66 QAxObject *QExcel::getWorkSheet() 67 { 68 return sheet; 69 } 70 71 void QExcel::selectSheet(const QString& sheetName) 72 { 73 sheet = sheets->querySubObject("Item(const QString&)", sheetName); 74 } 75 76 void QExcel::deleteSheet(const QString& sheetName) 77 { 78 QAxObject * a = sheets->querySubObject("Item(const QString&)", sheetName); 79 a->dynamicCall("delete"); 80 } 81 82 void QExcel::deleteSheet(int sheetIndex) 83 { 84 QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex); 85 a->dynamicCall("delete"); 86 } 87 88 void QExcel::selectSheet(int sheetIndex) 89 { 90 sheet = sheets->querySubObject("Item(int)", sheetIndex); 91 } 92 93 void QExcel::setCellString(int row, int column, const QString& value) 94 { 95 QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); 96 range->dynamicCall("SetValue(const QString&)", value); 97 } 98 99 void QExcel::setCellFontBold(int row, int column, bool isBold) 100 { 101 QString cell; 102 cell.append(QChar(column - 1 + ‘A‘)); 103 cell.append(QString::number(row)); 104 105 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 106 range = range->querySubObject("Font"); 107 range->setProperty("Bold", isBold); 108 } 109 110 void QExcel::setCellFontSize(int row, int column, int size) 111 { 112 QString cell; 113 cell.append(QChar(column - 1 + ‘A‘)); 114 cell.append(QString::number(row)); 115 116 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 117 range = range->querySubObject("Font"); 118 range->setProperty("Size", size); 119 } 120 121 void QExcel::mergeCells(const QString& cell) 122 { 123 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 124 range->setProperty("VerticalAlignment", -4108);//xlCenter 125 range->setProperty("WrapText", true); 126 range->setProperty("MergeCells", true); 127 } 128 129 void QExcel::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn) 130 { 131 QString cell; 132 cell.append(QChar(topLeftColumn - 1 + ‘A‘)); 133 cell.append(QString::number(topLeftRow)); 134 cell.append(":"); 135 cell.append(QChar(bottomRightColumn - 1 + ‘A‘)); 136 cell.append(QString::number(bottomRightRow)); 137 138 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 139 range->setProperty("VerticalAlignment", -4108);//xlCenter 140 range->setProperty("WrapText", true); 141 range->setProperty("MergeCells", true); 142 } 143 144 QVariant QExcel::getCellValue(int row, int column) 145 { 146 QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); 147 return range->property("Value"); 148 } 149 150 void QExcel::save() 151 { 152 workBook->dynamicCall("Save()"); 153 } 154 155 int QExcel::getSheetsCount() 156 { 157 return sheets->property("Count").toInt(); 158 } 159 160 QString QExcel::getSheetName() 161 { 162 return sheet->property("Name").toString(); 163 } 164 165 QString QExcel::getSheetName(int sheetIndex) 166 { 167 QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex); 168 return a->property("Name").toString(); 169 } 170 171 void QExcel::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn) 172 { 173 QAxObject *usedRange = sheet->querySubObject("UsedRange"); 174 *topLeftRow = usedRange->property("Row").toInt(); 175 *topLeftColumn = usedRange->property("Column").toInt(); 176 177 QAxObject *rows = usedRange->querySubObject("Rows"); 178 *bottomRightRow = *topLeftRow + rows->property("Count").toInt() - 1; 179 180 QAxObject *columns = usedRange->querySubObject("Columns"); 181 *bottomRightColumn = *topLeftColumn + columns->property("Count").toInt() - 1; 182 } 183 184 void QExcel::setColumnWidth(int column, int width) 185 { 186 QString columnName; 187 columnName.append(QChar(column - 1 + ‘A‘)); 188 columnName.append(":"); 189 columnName.append(QChar(column - 1 + ‘A‘)); 190 191 QAxObject * col = sheet->querySubObject("Columns(const QString&)", columnName); 192 col->setProperty("ColumnWidth", width); 193 } 194 195 void QExcel::setCellTextCenter(int row, int column) 196 { 197 QString cell; 198 cell.append(QChar(column - 1 + ‘A‘)); 199 cell.append(QString::number(row)); 200 201 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 202 range->setProperty("HorizontalAlignment", -4108);//xlCenter 203 } 204 205 void QExcel::setCellTextWrap(int row, int column, bool isWrap) 206 { 207 QString cell; 208 cell.append(QChar(column - 1 + ‘A‘)); 209 cell.append(QString::number(row)); 210 211 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 212 range->setProperty("WrapText", isWrap); 213 } 214 215 void QExcel::setAutoFitRow(int row) 216 { 217 QString rowsName; 218 rowsName.append(QString::number(row)); 219 rowsName.append(":"); 220 rowsName.append(QString::number(row)); 221 222 QAxObject * rows = sheet->querySubObject("Rows(const QString &)", rowsName); 223 rows->dynamicCall("AutoFit()"); 224 } 225 226 void QExcel::insertSheet(QString sheetName) 227 { 228 sheets->querySubObject("Add()"); 229 QAxObject * a = sheets->querySubObject("Item(int)", 1); 230 a->setProperty("Name", sheetName); 231 } 232 233 void QExcel::mergeSerialSameCellsInAColumn(int column, int topRow) 234 { 235 int a,b,c,rowsCount; 236 getUsedRange(&a, &b, &rowsCount, &c); 237 238 int aMergeStart = topRow, aMergeEnd = topRow + 1; 239 240 QString value; 241 while(aMergeEnd <= rowsCount) 242 { 243 value =http://www.mamicode.com/ getCellValue(aMergeStart, column).toString(); 244 while(value =http://www.mamicode.com/= getCellValue(aMergeEnd, column).toString()) 245 { 246 clearCell(aMergeEnd, column); 247 aMergeEnd++; 248 } 249 aMergeEnd--; 250 mergeCells(aMergeStart, column, aMergeEnd, column); 251 252 aMergeStart = aMergeEnd + 1; 253 aMergeEnd = aMergeStart + 1; 254 } 255 } 256 257 void QExcel::clearCell(int row, int column) 258 { 259 QString cell; 260 cell.append(QChar(column - 1 + ‘A‘)); 261 cell.append(QString::number(row)); 262 263 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 264 range->dynamicCall("ClearContents()"); 265 } 266 267 void QExcel::clearCell(const QString& cell) 268 { 269 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 270 range->dynamicCall("ClearContents()"); 271 } 272 273 int QExcel::getUsedRowsCount() 274 { 275 QAxObject *usedRange = sheet->querySubObject("UsedRange"); 276 int topRow = usedRange->property("Row").toInt(); 277 QAxObject *rows = usedRange->querySubObject("Rows"); 278 int bottomRow = topRow + rows->property("Count").toInt() - 1; 279 return bottomRow; 280 } 281 282 void QExcel::setCellString(const QString& cell, const QString& value) 283 { 284 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 285 range->dynamicCall("SetValue(const QString&)", value); 286 } 287 288 void QExcel::setCellFontSize(const QString &cell, int size) 289 { 290 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 291 range = range->querySubObject("Font"); 292 range->setProperty("Size", size); 293 } 294 295 void QExcel::setCellTextCenter(const QString &cell) 296 { 297 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 298 range->setProperty("HorizontalAlignment", -4108);//xlCenter 299 } 300 301 void QExcel::setCellFontBold(const QString &cell, bool isBold) 302 { 303 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 304 range = range->querySubObject("Font"); 305 range->setProperty("Bold", isBold); 306 } 307 308 void QExcel::setCellTextWrap(const QString &cell, bool isWrap) 309 { 310 QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); 311 range->setProperty("WrapText", isWrap); 312 } 313 314 void QExcel::setRowHeight(int row, int height) 315 { 316 QString rowsName; 317 rowsName.append(QString::number(row)); 318 rowsName.append(":"); 319 rowsName.append(QString::number(row)); 320 321 QAxObject * r = sheet->querySubObject("Rows(const QString &)", rowsName); 322 r->setProperty("RowHeight", height); 323 }
1 // main.cpp 2 3 #include <QtGui/QApplication> 4 #include <QDebug> 5 6 #include "qexcel.h" 7 8 int main(int argc, char *argv[]) 9 { 10 QApplication a(argc, argv); 11 12 //打开文件，取得工作簿 13 QExcel j("D:/test.xls"); 14 //取得工作表数量 15 //qDebug()<<"SheetCount"<<j.getSheetsCount(); 16 //激活一张工作表 17 //j.selectSheet(1); 18 //j.selectSheet("JSheet2"); 19 //取得工作表名称 20 //j.selectSheet(1); 21 //j.getSheetName(); 22 //qDebug()<<"SheetName 1"<<j.getSheetName(1); 23 //取得工作表已使用范围 24 //int topLeftRow, topLeftColumn, bottomRightRow, bottomRightColumn; 25 //j.getUsedRange(&topLeftRow, &topLeftColumn, &bottomRightRow, &bottomRightColumn); 26 //读值 27 //j.getCellValue(2, 2).toString(); 28 //删除工作表 29 //j.selectSheet("Sheet1"); 30 //j.selectSheet(1); 31 //j.deleteSheet(); 32 //j.save(); 33 //插入数据 34 j.selectSheet("Sheet1"); 35 j.setCellString(1, 7, "addString"); 36 j.setCellString("A3", "abc"); 37 j.save(); 38 //合并单元格 39 //j.selectSheet(2); 40 //j.mergeCells("G1:H2"); 41 //j.mergeCells(4, 7, 5 ,8); 42 //j.save(); 43 //设置列宽 44 //j.selectSheet(1); 45 //j.setColumnWidth(1, 20); 46 //j.save(); 47 //设置粗体 48 //j.selectSheet(1); 49 //j.setCellFontBold(2, 2, true); 50 //j.setCellFontBold("A2", true); 51 //j.save(); 52 //设置文字大小 53 //j.selectSheet(1); 54 //j.setCellFontSize("B3", 20); 55 //j.setCellFontSize(1, 2, 20); 56 //j.save(); 57 //设置单元格文字居中 58 //j.selectSheet(2); 59 //j.setCellTextCenter(1, 2); 60 //j.setCellTextCenter("A2"); 61 //j.save(); 62 //设置单元格文字自动折行 63 //j.selectSheet(1); 64 //j.setCellTextWrap(2,2,true); 65 //j.setCellTextWrap("A2", true); 66 //j.save(); 67 //设置一行自适应行高 68 //j.selectSheet(1); 69 //j.setAutoFitRow(2); 70 //j.save(); 71 //新建工作表 72 //j.insertSheet("abc"); 73 //j.save(); 74 //清除单元格内容 75 //j.selectSheet(4); 76 //j.clearCell(1,1); 77 //j.clearCell("A2"); 78 //j.save(); 79 //合并一列中相同连续的单元格 80 //j.selectSheet(1); 81 //j.mergeSerialSameCellsInColumn(1, 2); 82 //j.save(); 83 //获取一张工作表已用行数 84 //j.selectSheet(1); 85 //qDebug()<<j.getUsedRowsCount(); 86 //设置行高 87 //j.selectSheet(1); 88 //j.setRowHeight(2, 30); 89 //j.save(); 90 91 j.close(); 92 qDebug()<<"App End"; 93 return a.exec(); 94 }
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。