QT 操作excel 类封装

转自：http://blog.csdn.net/liliming1234/article/details/7054941

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();
}