Qt玩转Excel_互联网资讯__分享互联网新鲜有趣好玩的事物-云易优站长平台

Excel表格在日常工做中使会经常用的，有时候法式中需要生成测试陈述就需要用到Excel，在此封拆了一个类QExcel，便利后续利用。示例如下：

excel = new QExcel(); //新建对象excel->oPEn("C:\\Users\\Administrator\\Desktop\\www.xls"); //翻开一个Excel文件for (int i=1; i<=10; i++){ for (int j=1; j<=10; j++) { excel->setCellString(i,j,QString::number(i+j)); }}excel->setCellBackground(1,1,Qt::red);excel->setCellBackground(2,2,Qt::green);excel->setCellBackground(3,3,Qt::blue);excel->setCellBackground(4,4,Qt::yellow);excel->setCellBackground(5,5,Qt::gray);excel->save(); //保留文件//=================================================================留意：1、翻开文件和创建文件，二选一，即可利用所有供给的功用；2、操做完成之跋文得保留文件（挪用save()函数）和退出Excel（挪用quit()函数）3、行、列都是从1起头，填0行0列，法式瓦解//=================================================================excel->createFile("C:\\Users\\Administrator\\Desktop\\Test.xls"); //创建一个Excel文件for (int i=1; i<=10; i++){ for (int j=1; j<=10; j++) { excel->setCellString(i,j,QString::number(i+j)); }}excel->setCellBackground(1,1,Qt::red);excel->setCellBackground(2,2,Qt::green);excel->setCellBackground(3,3,Qt::blue);excel->setCellBackground(4,4,Qt::yellow);excel->setCellBackground(5,5,Qt::gray);excel->save(); //保留文件excel->quit(); //退出Excel

需在.PRo工程文件中添加axcontainer库

QT +=

头文件

QT开发交换君羊：714620761

#ifndef QEXCEL_H#define QEXCEL_H#include <QString>#include <QColor>#include <QVariant>#include <QAxObject>#include <QFile>#include <QStringList>#include <QDebug>class QAxObject;class QExcel : public QObject{public: QExcel(QObject *parent = nullptr); ~QExcel();public: QAxObject * getWorkBooks(); QAxObject * getWorkBook(); QAxObject * getWorkSheets(); QAxObject * getWorkSheet();public: /***************************************************************************/ /* 文件操做 */ /**************************************************************************/ void open(QString FileName); //翻开文件 void createFile(QString FileName); //创建文件 void save(); //保留Excel文件 void quit(); //退出Excel /**************************************************************************/ /* 工做表操做 */ /*************************************************************************/ void selectSheet(const QString& sheetName); //按照名字选择工做表 void selectSheet(int sheetIndex); //按照下标索引选择工做表，下标从1起头 void deleteSheet(const QString& sheetName); //按照名字删除工做表 void deleteSheet(int sheetIndex); //按照下标索引删除工做表，下标从1起头 void insertSheet(QString sheetName); //新建工做表 int getSheetsCount(); //获取工做表数量 QString getSheetName(); //获取当前活泼工做表的名字 QString getSheetName(int sheetIndex); //按照下标索引获取工做表名字 /***************************************************************************/ /* 单位格操做 */ /**************************************************************************/ void mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn); //按照行列编号合并单位格 void mergeCells(const QString& range); //按照字母编号合并单位格，例如(A5:C7) void setCellString(int row, int column, const QString& value); //按照行列编号设置单位格数据，例如(1,1,"xxx") void setCellString(const QString& cell, const QString& value); //按照字母编号设置单位格数据，例如("A5","xxx") bool setRowData(int row,int colum_start,int column_end,QVariant vData); //批量写入一行数据 QString getRange_fromColumn(int column); //按照列值计算出字母值 QVariant getCellValue(int row, int column); //按照行列编号获取单位格数值 QVariant getCellValue(const QString& cell); //按照字母编号获取单位格数值 void clearCell(int row, int column); //按照行列编号清空单位格 void clearCell(const QString& cell); //按照字母编号清空单位格 void setCellFontBold(int row, int column, bool isBold); //按照行列编号设置单位格字体能否加粗 void setCellFontBold(const QString& cell, bool isBold); //按照字母编号设置单位格字体能否加粗 void setCellFontSize(int row, int column, int size); //按照行列编号设置单位格字体大小 void setCellFontSize(const QString& cell, int size); //按照字母编号设置单位格字体大小 void setCellTextCenter(int row, int column); //按照行列编号设置单位格内容居中 void setCellTextCenter(const QString& cell); //按照字母编号设置单位格内容居中 void setCellTextWrap(int row, int column, bool isWrap); //按照行列编号设置单位格内容能否换行 void setCellTextWrap(const QString& cell, bool isWrap); //按照字母编号设置单位格内容能否换行 void setCellBackground(int row, int column, QColor color); //按照行列编号设置单位格布景色 void setCellBackground(const QString& cell, QColor color); //按照字母编号设置单位格布景色 void setCellFontColor(int row, int column, QColor color); //按照行列编号设置单位格字体颜色 void setCellFontColor(const QString& cell, QColor color); //按照字母编号设置单位格字体颜色 void setCellBorderColor(int row, int column, QColor color); //按照行列编号设置单位格边框颜色 void setCellBorderColor(const QString& cell, QColor color); //按照字母编号设置单位格边框颜色 void getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn); //获得工做表已利用范畴 void setColumnWidth(int column, int width); //设置列宽 void setRowHeight(int row, int height); //设置行高 void setAutoFitRow(int row); //设置自适应行高 void mergeSerialSameCellsInAColumn(int column, int topRow); //合并一列中不异持续的单位格 int getUsedRowsCount(); //获取总行数 int getUseDColumnCount(); //获取总列数private: QAxObject * excel; //Excel指针 QAxObject * workBooks; //工做簿集合 QAxObject * workBook; //工做簿 QAxObject * sheets; //工做表集合 QAxObject * sheet; //工做表};#endif

源文件

#include "qexcel.h"QExcel::QExcel(QObject *parent):QObject(parent){ excel = nullptr; workBooks = nullptr; workBook = nullptr; sheets = nullptr; sheet = nullptr;}QExcel::~QExcel(){}/***************************************************************************//* 文件操做 *//**************************************************************************/void QExcel::open(QString FileName){ if(excel==nullptr) { excel = new QAxObject(); excel->setControl("Excel.Application"); //毗连Excel控件 excel->setProperty("DisplayAlerts", false); //制止显示警告 workBooks = excel->querySubObject("Workbooks"); //获取工做簿集合 QFile file(FileName); if(file.exists()) { workBooks->dynamicCall("Open(const QString&)", FileName); //翻开指定文件 workBook = excel->querySubObject("ActiveWorkBook"); //获取活泼工做簿 sheets = workBook->querySubObject("WorkSheets"); //获取工做表集合 sheet = workBook->querySubObject("ActiveSheet"); //获取活泼工做表 } } else { workBooks = excel->querySubObject("Workbooks"); //获取工做簿集合 QFile file(FileName); if(file.exists()) { workBooks->dynamicCall("Open(const QString&)", FileName); //翻开指定文件 workBook = excel->querySubObject("ActiveWorkBook"); //获取活泼工做簿 sheets = workBook->querySubObject("WorkSheets"); //获取工做表集合 sheet = workBook->querySubObject("ActiveSheet"); //获取活泼工做表 } }}void QExcel::createFile(QString FileName){ if(excel==nullptr) { excel = new QAxObject(); excel->setControl("Excel.Application"); //毗连Excel控件 excel->setProperty("DisplayAlerts", false); //制止显示警告 workBooks = excel->querySubObject("Workbooks"); //获取工做簿集合 QFile file(FileName); if(!file.exists()) { workBooks->dynamicCall("Add"); //新建文件 workBook=excel->querySubObject("ActiveWorkBook"); //获取活泼工做簿 workBook->dynamicCall("SaveAs(const QString&)",FileName); //按指定文件名保留文件 sheets = workBook->querySubObject("WorkSheets"); //获取工做表集合 sheet = workBook->querySubObject("ActiveSheet"); //获取活泼工做表 } } else { workBooks = excel->querySubObject("Workbooks"); //获取工做簿集合 QFile file(FileName); if(!file.exists()) { workBooks->dynamicCall("Add"); //新建文件 workBook=excel->querySubObject("ActiveWorkBook"); //获取活泼工做簿 workBook->dynamicCall("SaveAs(const QString&)",FileName); //按指定文件名保留文件 sheets = workBook->querySubObject("WorkSheets"); //获取工做表集合 sheet = workBook->querySubObject("ActiveSheet"); //获取活泼工做表 } }}void QExcel::save() //保留Excel文件{ workBook->dynamicCall("Save()"); qDebug()<<"文件已保留";}void QExcel::quit() //退出Excel{ excel->dynamicCall("Quit()"); delete sheet; delete sheets; delete workBook; delete workBooks; delete excel; excel = nullptr; workBooks = nullptr; workBook = nullptr; sheets = nullptr; sheet = nullptr; qDebug()<<"退出Excel";}/**************************************************************************//* 工做表操做 *//*************************************************************************/void QExcel::selectSheet(const QString& sheetName) //按照名字选择工做表{ sheet = sheets->querySubObject("Item(const QString&)", sheetName);}void QExcel::selectSheet(int sheetIndex) //按照下标索引选择工做表，下标从1起头{ sheet = sheets->querySubObject("Item(int)", sheetIndex);}void QExcel::deleteSheet(const QString& sheetName) //按照名字删除工做表{ QAxObject * a = sheets->querySubObject("Item(const QString&)", sheetName); a->dynamicCall("delete");}void QExcel::deleteSheet(int sheetIndex) //按照下标索引删除工做表，下标从1起头{ QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex); a->dynamicCall("delete");}void QExcel::insertSheet(QString sheetName) //新建工做表{ sheets->querySubObject("Add()"); QAxObject * a = sheets->querySubObject("Item(int)", 1); a->setProperty("Name", sheetName);}int QExcel::getSheetsCount() //获取工做表数量{ return sheets->property("Count").toInt();}QString QExcel::getSheetName() //获取当前活泼工做表的名字{ return sheet->property("Name").toString();}QString QExcel::getSheetName(int sheetIndex) //按照下标索引获取工做表名字{ QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex); return a->property("Name").toString();}/***************************************************************************//* 单位格操做 *//**************************************************************************/void QExcel::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn) //按照行列编号合并单位格{ QString cell; cell.append(QChar(topLeftColumn - 1 + 'A')); cell.append(QString::number(topLeftRow)); cell.append(":"); cell.append(QChar(bottomRightColumn - 1 + 'A')); cell.append(QString::number(bottomRightRow)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("VerticalAlignment", -4108);//xlCenter range->setProperty("WrapText", true); range->setProperty("MergeCells", true);}void QExcel::mergeCells(const QString& cell) //按照字母编号合并单位格{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("VerticalAlignment", -4108);//xlCenter range->setProperty("WrapText", true); range->setProperty("MergeCells", true);}void QExcel::setCellString(int row, int column, const QString& value) //按照行列编号设置单位格数据{ QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); range->dynamicCall("SetValue(const QString&)", value);}void QExcel::setCellString(const QString& cell, const QString& value) //按照字母编号设置单位格数据{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->dynamicCall("SetValue(const QString&)", value);}bool QExcel::setRowData(int row,int colum_start,int column_end,QVariant vData) //批量写入一行数据{ bool op = false; QString start,end; start=getRange_fromColumn(colum_start); end=getRange_fromColumn(column_end); QVariant qstrRange = start+QString::number(row,10)+":"+end+QString::number(row,10); QAxObject *range = sheet->querySubObject("Range(const QString&)", qstrRange); if ( range ) { range->dynamicCall("SetValue(const QVariant&)",QVariant(vData)); //修改单位格的数据 op = true; } else { op = false; } delete range; return op;}QString QExcel::getRange_fromColumn(int column) //按照列值计算出字母值{ if(column <= 0) //列值必需大于等于1 return ""; QString ABC="ABCDEFGHIJKLMNOPQRSTUVWXYZ"; QString result=""; QVector<int> pos; if(column>=1 && column<=26) { result += ABC[column-1]; return result; } else { int high = column; int low; int last_high; while(high>0) { last_high=high; high = high / 26; low = last_high % 26; if(low==0 && high!=0) { low=26; high=high-1; } pos.push_front(low); } for(int i=0; i<pos.size(); i++) { result += ABC[pos[i]-1]; } } return result;}QVariant QExcel::getCellValue(int row, int column) //按照行列编号获取单位格数值{ QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); return range->property("Value");}QVariant QExcel::getCellValue(const QString& cell) //按照字母编号获取单位格数值{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); return range->property("Value");}void QExcel::clearCell(int row, int column) //按照行列编号清空单位格{ QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->dynamicCall("ClearContents()");}void QExcel::clearCell(const QString& cell) //按照字母编号清空单位格{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->dynamicCall("ClearContents()");}void QExcel::setCellFontBold(int row, int column, bool isBold) //按照行列编号设置单位格字体能否加粗{ QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Bold", isBold);}void QExcel::setCellFontBold(const QString &cell, bool isBold) //按照字母编号设置单位格字体能否加粗{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Bold", isBold);}void QExcel::setCellFontSize(int row, int column, int size) //按照行列编号设置单位格字体大小{ QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Size", size);}void QExcel::setCellFontSize(const QString &cell, int size) //按照字母编号设置单位格字体大小{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range = range->querySubObject("Font"); range->setProperty("Size", size);}void QExcel::setCellTextCenter(int row, int column) //按照行列编号设置单位格内容居中{ QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("HorizontalAlignment", -4108);//xlCenter}void QExcel::setCellTextCenter(const QString &cell) //按照字母编号设置单位格内容居中{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("HorizontalAlignment", -4108);//xlCenter}void QExcel::setCellTextWrap(int row, int column, bool isWrap) //按照行列编号设置单位格内容能否换行{ QString cell; cell.append(QChar(column - 1 + 'A')); cell.append(QString::number(row)); QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("WrapText", isWrap);}void QExcel::setCellTextWrap(const QString &cell, bool isWrap) //按照字母编号设置单位格内容能否换行{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); range->setProperty("WrapText", isWrap);}void QExcel::setCellBackground(int row, int column, QColor color) //按照行列编号设置单位格布景颜色{ QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); QAxObject *interior =range->querySubObject("Interior"); interior->setProperty("Color", color);}void QExcel::setCellBackground(const QString& cell, QColor color) //按照字母编号设置单位格布景颜色{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); QAxObject *interior =range->querySubObject("Interior"); interior->setProperty("Color", color);}void QExcel::setCellFontColor(int row, int column, QColor color) //按照行列编号设置单位格字体颜色{ QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); QAxObject *font =range->querySubObject("Font"); font->setProperty("Color", color);}void QExcel::setCellFontColor(const QString& cell, QColor color) //按照字母编号设置单位格字体颜色{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); QAxObject *font =range->querySubObject("Font"); font->setProperty("Color", color);}void QExcel::setCellBorderColor(int row, int column, QColor color) //按照行列编号设置单位格边框颜色{ QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column); QAxObject* border = range->querySubObject("Borders"); border->setProperty("Color", color);}void QExcel::setCellBorderColor(const QString& cell, QColor color) //按照字母编号设置单位格边框颜色{ QAxObject *range = sheet->querySubObject("Range(const QString&)", cell); QAxObject* border = range->querySubObject("Borders"); border->setProperty("Color", color);}void QExcel::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn) //获得工做表已利用范畴{ QAxObject *usedRange = sheet->querySubObject("UsedRange"); *topLeftRow = usedRange->property("Row").toInt(); *topLeftColumn = usedRange->property("Column").toInt(); QAxObject *rows = usedRange->querySubObject("Rows"); *bottomRightRow = *topLeftRow + rows->property("Count").toInt() - 1; QAxObject *columns = usedRange->querySubObject("Columns"); *bottomRightColumn = *topLeftColumn + columns->property("Count").toInt() - 1;}void QExcel::setColumnWidth(int column, int width) //设置列宽{ QString columnName; columnName.append(QChar(column - 1 + 'A')); columnName.append(":"); columnName.append(QChar(column - 1 + 'A')); QAxObject * col = sheet->querySubObject("Columns(const QString&)", columnName); col->setProperty("ColumnWidth", width);}void QExcel::setRowHeight(int row, int height) //设置行高{ QString rowsName; rowsName.append(QString::number(row)); rowsName.append(":"); rowsName.append(QString::number(row)); QAxObject * r = sheet->querySubObject("Rows(const QString &)", rowsName); r->setProperty("RowHeight", height);}void QExcel::setAutoFitRow(int row) //设置自适应行高{ QString rowsName; rowsName.append(QString::number(row)); rowsName.append(":"); rowsName.append(QString::number(row)); QAxObject * rows = sheet->querySubObject("Rows(const QString &)", rowsName); rows->dynamicCall("AutoFit()");}void QExcel::mergeSerialSameCellsInAColumn(int column, int topRow) //合并一列中不异持续的单位格{ int a,b,c,rowsCount; getUsedRange(&a, &b, &rowsCount, &c); int aMergeStart = topRow, aMergeEnd = topRow + 1; QString value; while(aMergeEnd <= rowsCount) { value = getCellValue(aMergeStart, column).toString(); while(value == getCellValue(aMergeEnd, column).toString()) { clearCell(aMergeEnd, column); aMergeEnd++; } aMergeEnd--; mergeCells(aMergeStart, column, aMergeEnd, column); aMergeStart = aMergeEnd + 1; aMergeEnd = aMergeStart + 1; }}int QExcel::getUsedRowsCount() //获取总行数{ QAxObject *usedRange = sheet->querySubObject("UsedRange"); int topRow = usedRange->property("Row").toInt(); QAxObject *rows = usedRange->querySubObject("Rows"); int bottomRow = topRow + rows->property("Count").toInt() - 1; return bottomRow;}int QExcel::getUsedColumnCount() //获取总列数{ QAxObject *usedRange = sheet->querySubObject("UsedRange"); int leftColumn = usedRange->property("Column").toInt(); QAxObject *columns = usedRange->querySubObject("Columns"); int rightColumn = leftColumn + columns->property("Count").toInt() - 1; return rightColumn;}QAxObject *QExcel::getWorkBooks(){ return workBooks;}QAxObject *QExcel::getWorkBook(){ return workBook;}QAxObject *QExcel::getWorkSheets(){ return sheets;}QAxObject *QExcel::getWorkSheet(){ return sheet;}