QT 操作excel 类封装(转载)
pro file
- CONFIG += qaxcontainer
- QT += core
- QT -= gui
- TARGET = QExcel
- CONFIG += console
- CONFIG -= app_bundle
- TEMPLATE = app
- SOURCES += main.cpp \
- qexcel.cpp
- HEADERS += \
- qexcel.h
qexcel.h
- #ifndef QEXCEL_H
- #define QEXCEL_H
- #include <QString>
- #include <QVariant>
- class QAxObject;
- class QExcel : public QObject
- {
- public:
- QExcel(QString xlsFilePath, QObject *parent = 0);
- ~QExcel();
- public:
- QAxObject * getWorkBooks();
- QAxObject * getWorkBook();
- QAxObject * getWorkSheets();
- QAxObject * getWorkSheet();
- public:
- /**************************************************************************/
- /* 工作表 */
- /**************************************************************************/
- void selectSheet(const QString& sheetName);
- //sheetIndex 起始于 1
- void selectSheet(int sheetIndex);
- void deleteSheet(const QString& sheetName);
- void deleteSheet(int sheetIndex);
- void insertSheet(QString sheetName);
- int getSheetsCount();
- //在 selectSheet() 之后才可调用
- QString getSheetName();
- QString getSheetName(int sheetIndex);
- /**************************************************************************/
- /* 单元格 */
- /**************************************************************************/
- void setCellString(int row, int column, const QString& value);
- //cell 例如 "A7"
- void setCellString(const QString& cell, const QString& value);
- //range 例如 "A5:C7"
- void mergeCells(const QString& range);
- void mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn);
- QVariant getCellValue(int row, int column);
- void clearCell(int row, int column);
- void clearCell(const QString& cell);
- /**************************************************************************/
- /* 布局格式 */
- /**************************************************************************/
- void getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn);
- void setColumnWidth(int column, int width);
- void setRowHeight(int row, int height);
- 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 setAutoFitRow(int row);
- void mergeSerialSameCellsInAColumn(int column, int topRow);
- int getUsedRowsCount();
- 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 save();
- void close();
- private:
- QAxObject * excel;
- QAxObject * workBooks;
- QAxObject * workBook;
- QAxObject * sheets;
- QAxObject * sheet;
- };
- #endif
qexcel.cpp
- #include <QAxObject>
- #include <QFile>
- #include <QStringList>
- #include <QDebug>
- #include "qexcel.h"
- QExcel::QExcel(QString xlsFilePath, QObject *parent)
- {
- excel = 0;
- workBooks = 0;
- workBook = 0;
- sheets = 0;
- sheet = 0;
- excel = new QAxObject("Excel.Application", parent);
- workBooks = excel->querySubObject("Workbooks");
- QFile file(xlsFilePath);
- if (file.exists())
- {
- workBooks->dynamicCall("Open(const QString&)", xlsFilePath);
- workBook = excel->querySubObject("ActiveWorkBook");
- sheets = workBook->querySubObject("WorkSheets");
- }
- }
- QExcel::~QExcel()
- {
- close();
- }
- void QExcel::close()
- {
- excel->dynamicCall("Quit()");
- delete sheet;
- delete sheets;
- delete workBook;
- delete workBooks;
- delete excel;
- excel = 0;
- workBooks = 0;
- workBook = 0;
- sheets = 0;
- sheet = 0;
- }
- QAxObject *QExcel::getWorkBooks()
- {
- return workBooks;
- }
- QAxObject *QExcel::getWorkBook()
- {
- return workBook;
- }
- QAxObject *QExcel::getWorkSheets()
- {
- return sheets;
- }
- QAxObject *QExcel::getWorkSheet()
- {
- return sheet;
- }
- void QExcel::selectSheet(const QString& sheetName)
- {
- sheet = sheets->querySubObject("Item(const QString&)", sheetName);
- }
- void QExcel::deleteSheet(const QString& sheetName)
- {
- QAxObject * a = sheets->querySubObject("Item(const QString&)", sheetName);
- a->dynamicCall("delete");
- }
- void QExcel::deleteSheet(int sheetIndex)
- {
- QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex);
- a->dynamicCall("delete");
- }
- void QExcel::selectSheet(int sheetIndex)
- {
- sheet = sheets->querySubObject("Item(int)", sheetIndex);
- }
- 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::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::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::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::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);
- }
- QVariant QExcel::getCellValue(int row, int column)
- {
- QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column);
- return range->property("Value");
- }
- void QExcel::save()
- {
- workBook->dynamicCall("Save()");
- }
- 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::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::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::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::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::insertSheet(QString sheetName)
- {
- sheets->querySubObject("Add()");
- QAxObject * a = sheets->querySubObject("Item(int)", 1);
- a->setProperty("Name", sheetName);
- }
- 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;
- }
- }
- 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()");
- }
- 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;
- }
- void QExcel::setCellString(const QString& cell, const QString& value)
- {
- QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
- range->dynamicCall("SetValue(const QString&)", value);
- }
- 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(const QString &cell)
- {
- QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
- range->setProperty("HorizontalAlignment", -4108);//xlCenter
- }
- 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::setCellTextWrap(const QString &cell, bool isWrap)
- {
- QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
- range->setProperty("WrapText", isWrap);
- }
- 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);
- }
main.cpp
- #include <QtGui/QApplication>
- #include <QDebug>
-
- #include "qexcel.h"
-
- int main(int argc, char *argv[])
- {
- QApplication a(argc, argv);
-
- //打开文件,取得工作簿
- QExcel j("D:/test.xls");
- //取得工作表数量
- //qDebug()<<"SheetCount"<<j.getSheetsCount();
- //激活一张工作表
- //j.selectSheet(1);
- //j.selectSheet("JSheet2");
- //取得工作表名称
- //j.selectSheet(1);
- //j.getSheetName();
- //qDebug()<<"SheetName 1"<<j.getSheetName(1);
- //取得工作表已使用范围
- //int topLeftRow, topLeftColumn, bottomRightRow, bottomRightColumn;
- //j.getUsedRange(&topLeftRow, &topLeftColumn, &bottomRightRow, &bottomRightColumn);
- //读值
- //j.getCellValue(2, 2).toString();
- //删除工作表
- //j.selectSheet("Sheet1");
- //j.selectSheet(1);
- //j.deleteSheet();
- //j.save();
- //插入数据
- j.selectSheet("Sheet1");
- j.setCellString(1, 7, "addString");
- j.setCellString("A3", "abc");
- j.save();
- //合并单元格
- //j.selectSheet(2);
- //j.mergeCells("G1:H2");
- //j.mergeCells(4, 7, 5 ,8);
- //j.save();
- //设置列宽
- //j.selectSheet(1);
- //j.setColumnWidth(1, 20);
- //j.save();
- //设置粗体
- //j.selectSheet(1);
- //j.setCellFontBold(2, 2, true);
- //j.setCellFontBold("A2", true);
- //j.save();
- //设置文字大小
- //j.selectSheet(1);
- //j.setCellFontSize("B3", 20);
- //j.setCellFontSize(1, 2, 20);
- //j.save();
- //设置单元格文字居中
- //j.selectSheet(2);
- //j.setCellTextCenter(1, 2);
- //j.setCellTextCenter("A2");
- //j.save();
- //设置单元格文字自动折行
- //j.selectSheet(1);
- //j.setCellTextWrap(2,2,true);
- //j.setCellTextWrap("A2", true);
- //j.save();
- //设置一行自适应行高
- //j.selectSheet(1);
- //j.setAutoFitRow(2);
- //j.save();
- //新建工作表
- //j.insertSheet("abc");
- //j.save();
- //清除单元格内容
- //j.selectSheet(4);
- //j.clearCell(1,1);
- //j.clearCell("A2");
- //j.save();
- //合并一列中相同连续的单元格
- //j.selectSheet(1);
- //j.mergeSerialSameCellsInColumn(1, 2);
- //j.save();
- //获取一张工作表已用行数
- //j.selectSheet(1);
- //qDebug()<<j.getUsedRowsCount();
- //设置行高
- //j.selectSheet(1);
- //j.setRowHeight(2, 30);
- //j.save();
-
- j.close();
- qDebug()<<"App End";
- return a.exec();
- }
- #include <QtGui/QApplication>
- #include <QDebug>
- #include "qexcel.h"
- int main(int argc, char *argv[])
- {
- QApplication a(argc, argv);
- //打开文件,取得工作簿
- QExcel j("D:/test.xls");
- //取得工作表数量
- //qDebug()<<"SheetCount"<<j.getSheetsCount();
- //激活一张工作表
- //j.selectSheet(1);
- //j.selectSheet("JSheet2");
- //取得工作表名称
- //j.selectSheet(1);
- //j.getSheetName();
- //qDebug()<<"SheetName 1"<<j.getSheetName(1);
- //取得工作表已使用范围
- //int topLeftRow, topLeftColumn, bottomRightRow, bottomRightColumn;
- //j.getUsedRange(&topLeftRow, &topLeftColumn, &bottomRightRow, &bottomRightColumn);
- //读值
- //j.getCellValue(2, 2).toString();
- //删除工作表
- //j.selectSheet("Sheet1");
- //j.selectSheet(1);
- //j.deleteSheet();
- //j.save();
- //插入数据
- j.selectSheet("Sheet1");
- j.setCellString(1, 7, "addString");
- j.setCellString("A3", "abc");
- j.save();
- //合并单元格
- //j.selectSheet(2);
- //j.mergeCells("G1:H2");
- //j.mergeCells(4, 7, 5 ,8);
- //j.save();
- //设置列宽
- //j.selectSheet(1);
- //j.setColumnWidth(1, 20);
- //j.save();
- //设置粗体
- //j.selectSheet(1);
- //j.setCellFontBold(2, 2, true);
- //j.setCellFontBold("A2", true);
- //j.save();
- //设置文字大小
- //j.selectSheet(1);
- //j.setCellFontSize("B3", 20);
- //j.setCellFontSize(1, 2, 20);
- //j.save();
- //设置单元格文字居中
- //j.selectSheet(2);
- //j.setCellTextCenter(1, 2);
- //j.setCellTextCenter("A2");
- //j.save();
- //设置单元格文字自动折行
- //j.selectSheet(1);
- //j.setCellTextWrap(2,2,true);
- //j.setCellTextWrap("A2", true);
- //j.save();
- //设置一行自适应行高
- //j.selectSheet(1);
- //j.setAutoFitRow(2);
- //j.save();
- //新建工作表
- //j.insertSheet("abc");
- //j.save();
- //清除单元格内容
- //j.selectSheet(4);
- //j.clearCell(1,1);
- //j.clearCell("A2");
- //j.save();
- //合并一列中相同连续的单元格
- //j.selectSheet(1);
- //j.mergeSerialSameCellsInColumn(1, 2);
- //j.save();
- //获取一张工作表已用行数
- //j.selectSheet(1);
- //qDebug()<<j.getUsedRowsCount();
- //设置行高
- //j.selectSheet(1);
- //j.setRowHeight(2, 30);
- //j.save();
- j.close();
- qDebug()<<"App End";
- return a.exec();
- }