Qt操作Excelクラス

#ifndef QEXCEL_H  
#define QEXCEL_H  

#include   
#include   

class QAxObject;

class ExcelEngine final : public QObject
{
public:
	ExcelEngine(QString strPath, QObject *parent = 0);
	~ExcelEngine();

public:

	/*****************************************************/
	/*                                                                     */
	/****************************************************/
	bool selectSheet(const QString& sheetName);
	bool selectSheet(int sheetIndex);

	void deleteSheet(const QString& sheetName);
	void deleteSheet(int sheetIndex);

	//      
	bool insertSheet(QString sheetName, bool bAtLast = true);

	//   ／     
	bool moveSheetForward(int nSheetIndex);
	bool moveSheetBack(int nSheetIndex);

	//       
	int getSheetsCount();

	//        (  selectSheet()       )
	QString getSheetName();
	QString getSheetName(int sheetIndex);

	/*****************************************************/
	/*                                                                     */
	/****************************************************/

	//        
	void setCell(int row, int column, const QString& value);
	void setCell(const QString& cell, const QString& value);

	void setCell(int row, int column, const int& value);
	void setCell(const QString& cell, const int& value);
	
	void setCell(int row, int column, const float& value);
	void setCell(const QString& cell, const float& value);

	//           ,  : 3 ，5 
	QString getCell(int row, int column);
	//            ,  : "F6"
	QString getCell(const QString& strCell);

	QVariant getCellValue(int row, int column);
	QVariant getCellValue(const QString& strCell);

	//     . range    "A5:C7"
	void mergeCells(const QString& range);
	void mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn);

	//       
	void clearCell(int row, int column);
	void clearCell(const QString& cell);

	
	//     
	int getRowsCount();
	int getColumnsCount();

	//           
	bool insertEmptyRow(int nInsertRow = 1);
	bool insertEmptyColumn(int nInsertCol = 1);

	//      
	void deleteRow(int nRowNum);
	bool deleteColumn(int nColumnNum);

	//    /    
	bool copyRowThenInsert(int nCopyRow, int nInsertRow);
	bool copyColumnThenInsert(int nCopyCol, int nInsertCol);

	//    /    
	bool copyRowThenPaste(int nCopyRow, int nInsertRow);
	bool copyColumnThenPaste(int nCopyCol, int nInsertCol);

	//         : copyBlockThenPaste("A1:D6", "A8");
	bool copyBlockThenInsert(const QString& strCellCopy, const QString& strCellPast);
	bool copyBlockThenPaste(const QString& strCellCopy, const QString& strCellPast);

	bool multiSelectCopy(const QString &strCellCopy, const QString &strCellPaste);

	//      ,              ,   strCellResult
	bool  sumFunc(const QString& strCell, const QString& strCellResult);
	bool  sumFunc(const QStringList& strCells, const QString& strCellResult);

	//           
	bool setCellFormula(const QString& strCell, const QString& strFormula);

	/*****************************************************/
	/*                                                                      */
	/****************************************************/
	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 saveAs(const QString& strPath);
	void close();

private:
	QAxObject * getWorkBooks();
	QAxObject * getWorkBook();
	QAxObject * getWorkSheets();
	QAxObject * getWorkSheet();

private:
	QAxObject * m_pExcel = nullptr;

	QAxObject * m_pWorkBooks = nullptr;
	QAxObject * m_pWorkBook = nullptr;

	QAxObject * m_pWorkSheets = nullptr;
	QAxObject * m_pWorkSheet = nullptr;

	QAxObject* m_pAxObj = nullptr;
};

#endif  

#include "ExcelEngine.h"


#include   
#include   
//#include 
#include   
#include   

//     
#define DEL_OBJ(X) { if (X) { delete X; X=nullptr;} }

#include 
ExcelEngine::ExcelEngine(QString strPath, QObject *parent)
{
	m_pExcel = new QAxObject("Excel.Application", parent);
	m_pExcel->setProperty("Visible", true);	//     
	m_pWorkBooks = m_pExcel->querySubObject("Workbooks");

	QFile file(strPath);
	if (file.exists())
	{
		m_pWorkBooks->dynamicCall("Open(const QString&)", strPath);
		m_pWorkBook = m_pExcel->querySubObject("ActiveWorkBook");
		m_pWorkSheets = m_pWorkBook->querySubObject("WorkSheets");
	}
	else
	{
		//	m_pWorkBooks->dynamicCall("Add");
		//	m_pWorkBook = m_pExcel->querySubObject("ActiveWorkBook");
		//	m_pWorkBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(strPath));
		//	m_pWorkSheets = m_pWorkBook->querySubObject("WorkSheets");
		return;
	}
	selectSheet(1);
}

ExcelEngine::~ExcelEngine()
{
	close();
}

void ExcelEngine::close()
{
	if (m_pExcel)
	{
		m_pWorkBook->dynamicCall("Close()");
		m_pExcel->dynamicCall("Quit()");//  excel
	}

	DEL_OBJ(m_pWorkSheet);
	DEL_OBJ(m_pWorkSheets);
	DEL_OBJ(m_pWorkBook);
	DEL_OBJ(m_pWorkBooks);

	DEL_OBJ(m_pExcel);
}

QAxObject *ExcelEngine::getWorkBooks()
{
	return m_pWorkBooks;
}

QAxObject *ExcelEngine::getWorkBook()
{
	return m_pWorkBook;
}

QAxObject *ExcelEngine::getWorkSheets()
{
	return m_pWorkSheets;
}

QAxObject *ExcelEngine::getWorkSheet()
{
	if (!m_pWorkSheet)
		return nullptr;

	return m_pWorkSheet;
}


/*****************************************************/
/*                                                                     */
/****************************************************/

bool ExcelEngine::selectSheet(const QString& sheetName)
{
	if (!m_pWorkSheets)
		return false;

	m_pWorkSheet = m_pWorkSheets->querySubObject("Item(const QString&)", sheetName);
	return true;
}


bool ExcelEngine::selectSheet(int sheetIndex)
{
	if (!m_pWorkSheets || sheetIndex < 1 || sheetIndex > getSheetsCount())
		return false;

	m_pWorkSheet = m_pWorkSheets->querySubObject("Item(int)", sheetIndex);
	return true;
}

void ExcelEngine::deleteSheet(const QString& sheetName)
{
	if (!m_pWorkSheets)
		return;

	m_pAxObj = m_pWorkSheets->querySubObject("Item(const QString&)", sheetName);
	if(m_pAxObj)
		m_pAxObj->dynamicCall("delete");
}

void ExcelEngine::deleteSheet(int sheetIndex)
{
	if (!m_pWorkSheets)
		return;

	m_pAxObj = m_pWorkSheets->querySubObject("Item(int)", sheetIndex);
	if (m_pAxObj)
		m_pAxObj->dynamicCall("delete");
}


bool ExcelEngine::insertSheet(QString sheetName, bool bAtLast /*= true*/)
{
	if (!m_pWorkSheets)
		return false;

	if (!bAtLast)
	{
		int nSheetCount = getSheetsCount();
		QAxObject* pLastSheet = m_pWorkSheets->querySubObject("Item(int)", nSheetCount);
		m_pWorkSheets->querySubObject("Add(QVariant)", pLastSheet->asVariant()); //             ；
		m_pWorkSheet = m_pWorkSheets->querySubObject("Item(int)", nSheetCount);
		pLastSheet->dynamicCall("Move(QVariant)", m_pWorkSheet->asVariant());

		nSheetCount = getSheetsCount();
		pLastSheet = m_pWorkSheets->querySubObject("Item(int)", nSheetCount);
		pLastSheet->setProperty("Name", sheetName);  //       
	}
	else
	{
		m_pWorkSheets->querySubObject("Add()"); //       
		m_pAxObj = m_pWorkSheets->querySubObject("Item(int)", 1);  //           1
		if (m_pAxObj)
			m_pAxObj->setProperty("Name", sheetName);
	}
	return true;
}


bool ExcelEngine::moveSheetForward(int nSheetIndex)
{
	if (!m_pWorkBooks)
		return false;

	if (nSheetIndex < 2) return true; //      

	if (selectSheet(nSheetIndex))
	{
		m_pAxObj = m_pWorkSheets->querySubObject("Item(int)", nSheetIndex - 1);
		m_pWorkSheet->dynamicCall("Move(QVariant)", m_pAxObj->asVariant());
	}

	return true;
}

bool ExcelEngine::moveSheetBack(int nSheetIndex)
{
	int nCount = getSheetsCount();
	if (nSheetIndex > nCount)
		return  true;

	moveSheetForward(nSheetIndex + 1);
	return true;
}


int ExcelEngine::getSheetsCount()
{
	if (!m_pWorkSheets)
		return 0;
	return m_pWorkSheets->property("Count").toInt();
}

QString ExcelEngine::getSheetName()
{
	if (!m_pWorkSheet)
		return " ";

	return m_pWorkSheet->property("Name").toString();
}

QString ExcelEngine::getSheetName(int sheetIndex)
{
	if (!m_pWorkSheets)
		return "";

	m_pAxObj = m_pWorkSheets->querySubObject("Item(int)", sheetIndex);
	return m_pAxObj->property("Name").toString();
}



/*****************************************************/
/*                                                                     */
/****************************************************/

void ExcelEngine::setCell(int row, int column, const QString& value)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Cells(int,int)", row, column);
	m_pAxObj->dynamicCall("SetValue(const QString&)", value);
}

void ExcelEngine::setCell(const QString& cell, const QString& value)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->dynamicCall("SetValue(const QString&)", value);
}


void ExcelEngine::setCell(int row, int column, const int& value)
{
	if (!m_pWorkSheet) 
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Cells(int,int)", row, column);
	m_pAxObj->dynamicCall("SetValue(const int&)", value);
}

void ExcelEngine::setCell(const QString& cell, const int& value)
{
	if (!m_pWorkSheet) 
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&, const QString&,)", cell);
	m_pAxObj->dynamicCall("SetValue(const int&)", value);
}


void ExcelEngine::setCell(int row, int column, const float& value)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Cells(int,int)", row, column);
	m_pAxObj->dynamicCall("SetValue(const float&)", value);
}

void ExcelEngine::setCell(const QString& cell, const float& value)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&, const QString&,)", cell);
	m_pAxObj->dynamicCall("SetValue(const float&)", value);
}

void ExcelEngine::mergeCells(const QString& cell)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->setProperty("VerticalAlignment", -4108);//xlCenter  
	m_pAxObj->setProperty("WrapText", true);
	m_pAxObj->setProperty("MergeCells", true);
}

void ExcelEngine::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn)
{
	if (!m_pWorkSheet)
		return;

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

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->setProperty("VerticalAlignment", -4108);//xlCenter  
	m_pAxObj->setProperty("WrapText", true);
	m_pAxObj->setProperty("MergeCells", true);
}


void ExcelEngine::clearCell(int row, int column)
{
	if (!m_pWorkSheet)
		return;

	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->dynamicCall("ClearContents()");
}

void ExcelEngine::clearCell(const QString& cell)
{
	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->dynamicCall("ClearContents()");
}


int ExcelEngine::getRowsCount()
{
	if (!m_pWorkSheet)
		return 0;

	int rows = 0;
	try {
		QAxObject * pUsedrange = m_pWorkSheet->querySubObject("UsedRange");
		QAxObject* pRows = pUsedrange->querySubObject("Rows");
		rows = pRows->property("Count").toInt();
	}
	catch (...) {
		qCritical() << "      ...";
	}
	return rows;
}


int ExcelEngine::getColumnsCount()
{
	if (!m_pWorkSheet)
		return 0;

	int columns = 0;
	try {
		QAxObject * pUsedrange = m_pWorkSheet->querySubObject("UsedRange");//   sheet       
		QAxObject* pColumns = pUsedrange->querySubObject("Columns");
		columns = pColumns->property("Count").toInt();
	}
	catch (...) {
		qCritical() << "      ...";
	}
	return columns;
}

bool ExcelEngine::insertEmptyRow(int nInsertRow /*= 1*/)
{
	if (m_pWorkSheet || nInsertRow < 1) return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Rows(int)", nInsertRow);
	m_pAxObj->dynamicCall("Insert()");
	return true;
}

bool ExcelEngine::insertEmptyColumn(int nInsertCol /*= 1*/)
{
	if (m_pWorkSheet || nInsertCol < 1)
		return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Columns(int)", nInsertCol);
	m_pAxObj->dynamicCall("Insert()");
	return true;
}


void ExcelEngine::deleteRow(int nRowNum)
{
	if (!m_pWorkSheet || nRowNum < 1)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Rows(int)", nRowNum);//      
	if (m_pAxObj)
		m_pAxObj->dynamicCall("Delete()"); //     
}

bool ExcelEngine::deleteColumn(int nColumnNum)
{
	if (!m_pWorkSheet || nColumnNum < 1)
		return false;

	m_pAxObj = m_pWorkSheet->querySubObject("Columns(int)", nColumnNum);
	if (m_pAxObj)
		m_pAxObj->dynamicCall("Delete()");
	return true;
}
bool ExcelEngine::copyRowThenInsert(int nCopyRow, int nInsertRow)
{
	if (!m_pWorkSheet || nCopyRow < 1 || nInsertRow < 1)
		return false;

	m_pAxObj = m_pWorkSheet->querySubObject("Rows(int)", nCopyRow);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Copy()");
	m_pAxObj = m_pWorkSheet->querySubObject("Rows(int)", nInsertRow);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Insert()");
	return true;
}

bool ExcelEngine::copyColumnThenInsert(int nCopyCol, int nInsertCol)
{
	if (!m_pWorkSheet || nCopyCol < 1 || nInsertCol < 1)
		return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Columns(int)", nCopyCol);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Copy()");

	m_pAxObj = m_pWorkSheet->querySubObject("Columns(int)", nInsertCol);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Insert()");

	return true;
}

bool ExcelEngine::copyRowThenPaste(int nCopyRow, int nInsertRow)
{
	if (!m_pWorkSheet || nCopyRow < 1 || nInsertRow < 1) 
		return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Rows(int)", nCopyRow);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Copy()");

	m_pAxObj = m_pWorkSheet->querySubObject("Rows(int)", nInsertRow);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("PasteSpecial()");
	return true;
}

bool ExcelEngine::copyColumnThenPaste(int nCopyCol, int nInsertCol)
{
	if (!m_pWorkSheet || nCopyCol < 1 || nInsertCol < 1) 
		return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Columns(int)", nCopyCol);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Copy()");

	m_pAxObj = m_pWorkSheet->querySubObject("Columns(int)", nInsertCol);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("PasteSpecial()");

	return true;
}

bool ExcelEngine::copyBlockThenInsert(const QString& strCellCopy, const QString& strCellPast)
{
	if (!m_pWorkSheet) 
		return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Range(QVariant&,QVariant&)", strCellCopy);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Copy()");

	m_pAxObj = m_pWorkSheet->querySubObject("Range(QVariant&,QVariant&)", strCellPast);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Insert()");

	return true;
}

bool ExcelEngine::copyBlockThenPaste(const QString& strCellCopy, const QString& strCellPast)
{
	if (!m_pWorkSheet) 
		return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Range(QVariant&,QVariant&)", strCellCopy);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Copy()");

	m_pAxObj = m_pWorkSheet->querySubObject("Range(QVariant&,QVariant&)", strCellPast);
	m_pAxObj->dynamicCall("Select()");
	m_pAxObj->dynamicCall("PasteSpecial()");

	return true;
}

bool ExcelEngine::multiSelectCopy(const QString &strCellCopy, const QString &strCellPaste)
{
	if (!m_pWorkSheet) 
		return false;
	m_pAxObj = m_pWorkSheet->querySubObject("Range(QVariant&,QVariant&)", strCellCopy);
	m_pAxObj->dynamicCall("Select()");

	m_pAxObj->dynamicCall("Copy()");

	m_pAxObj = m_pWorkSheet->querySubObject("Range(QVariant&,QVariant&)", strCellPaste);//->dynamicCall("Select()");
	m_pAxObj->dynamicCall("Paste()");
	return true;
}

QString ExcelEngine::getCell(int row, int column)
{
	if (!m_pWorkSheet)
		return "";

	QString strValue = "";
	try {
		m_pAxObj = m_pWorkSheet->querySubObject("Cells(int, int)", row, column);
		strValue = m_pAxObj->property("Value").toString();
	}
	catch (...) {
		qCritical() << "         ...";
	}

	return strValue;
}

QString ExcelEngine::getCell(const QString& strCell)
{
	if (!m_pWorkSheet)
		return "";

	QString strValue = "";
	try {
		m_pAxObj = m_pWorkSheet->querySubObject("Range(QString)", strCell);
		strValue = m_pAxObj->property("Value").toString();
	}
	catch (...) {
		qCritical() << "         ...";
	}

	return strValue;
}


QVariant ExcelEngine::getCellValue(int row, int column)
{
	if (!m_pWorkSheet)
		return 0;

	m_pAxObj = m_pWorkSheet->querySubObject("Cells(int,int)", row, column);
	return m_pAxObj->property("Value");
}

QVariant ExcelEngine::getCellValue(const QString& strCell)
{
	if (!m_pWorkSheet)
		return false;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", strCell);
	return m_pAxObj->property("Value");
}

bool ExcelEngine::sumFunc(const QString& strCell, const QString& strCellResult)
{
	if (!m_pWorkSheet)
		return false;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", strCellResult);
	QString strSum = "=SUM(" + strCell + ")";
	m_pAxObj->dynamicCall("SetValue(const QString&)", strSum);
	return true;
}

bool ExcelEngine::sumFunc(const QStringList& strCells, const QString& strCellResult)
{
	if (!m_pWorkSheet)
		return false;

	QString strSum = "=SUM(";
	for (int i = 0; i < strCells.size(); i++)
	{
		strSum += strCells.at(i) + ",";
	}
	strSum += ")";

	//    m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", strCellResult);
	//    m_pAxObj->dynamicCall("SetValue(const QString&)",strSum);

	setCellFormula(strCellResult, strSum);
	return true;
}

bool ExcelEngine::setCellFormula(const QString& strCell, const QString& strFormula)
{
	if (!m_pWorkSheet)
		return false;

	m_pAxObj = m_pWorkSheet->querySubObject("Columns(const QString&)", strCell);
	m_pAxObj->setProperty("SetValue(const QString&)", strFormula);
	return true;
}

/*****************************************************/
/*                                                                    */
/*****************************************************/

void ExcelEngine::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn)
{
	if (!m_pWorkSheet)
		return;

	QAxObject *usedRange = m_pWorkSheet->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 ExcelEngine::setColumnWidth(int column, int width)
{
	if (!m_pWorkSheet)
		return;

	QString columnName;
	columnName.append(QChar(column - 1 + 'A'));
	columnName.append(":");
	columnName.append(QChar(column - 1 + 'A'));

	m_pAxObj = m_pWorkSheet->querySubObject("Columns(const QString&)", columnName);
	m_pAxObj->setProperty("ColumnWidth", width);
}

void ExcelEngine::setRowHeight(int row, int height)
{
	if (!m_pWorkSheet)
		return;

	QString rowsName;
	rowsName.append(QString::number(row));
	rowsName.append(":");
	rowsName.append(QString::number(row));

	m_pAxObj = m_pWorkSheet->querySubObject("Rows(const QString &)", rowsName);
	m_pAxObj->setProperty("RowHeight", height);
}

void ExcelEngine::setCellTextCenter(int row, int column)
{
	if (!m_pWorkSheet)
		return;

	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->setProperty("HorizontalAlignment", -4108);//xlCenter  
}

void ExcelEngine::setCellTextCenter(const QString &cell)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->setProperty("HorizontalAlignment", -4108);//xlCenter  
}

void ExcelEngine::setCellTextWrap(int row, int column, bool isWrap)
{
	if (!m_pWorkSheet)
		return;

	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->setProperty("WrapText", isWrap);
}

void ExcelEngine::setCellTextWrap(const QString &cell, bool isWrap)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj->setProperty("WrapText", isWrap);
}

void ExcelEngine::setAutoFitRow(int row)
{
	if (!m_pWorkSheet)
		return;

	QString rowsName;
	rowsName.append(QString::number(row));
	rowsName.append(":");
	rowsName.append(QString::number(row));

	QAxObject * rows = m_pWorkSheet->querySubObject("Rows(const QString &)", rowsName);
	rows->dynamicCall("AutoFit()");
}

void ExcelEngine::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 ExcelEngine::getUsedRowsCount()
{
	if (!m_pWorkSheet)
		return 0;

	QAxObject *usedRange = m_pWorkSheet->querySubObject("UsedRange");
	int topRow = usedRange->property("Row").toInt();
	QAxObject *rows = usedRange->querySubObject("Rows");
	int bottomRow = topRow + rows->property("Count").toInt() - 1;
	return bottomRow;
}

void ExcelEngine::setCellFontBold(int row, int column, bool isBold)
{
	if (!m_pWorkSheet)
		return;

	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	QAxObject *m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj = m_pAxObj->querySubObject("Font");
	m_pAxObj->setProperty("Bold", isBold);
}

void ExcelEngine::setCellFontBold(const QString &cell, bool isBold)
{
	if (!m_pWorkSheet)
		return;
	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj = m_pAxObj->querySubObject("Font");
	m_pAxObj->setProperty("Bold", isBold);
}

void ExcelEngine::setCellFontSize(int row, int column, int size)
{
	if (!m_pWorkSheet)
		return;

	QString cell;
	cell.append(QChar(column - 1 + 'A'));
	cell.append(QString::number(row));

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj = m_pAxObj->querySubObject("Font");
	m_pAxObj->setProperty("Size", size);
}

void ExcelEngine::setCellFontSize(const QString &cell, int size)
{
	if (!m_pWorkSheet)
		return;

	m_pAxObj = m_pWorkSheet->querySubObject("Range(const QString&)", cell);
	m_pAxObj = m_pAxObj->querySubObject("Font");
	m_pAxObj->setProperty("Size", size);
}

/*****************************************************/
/*                                                                         */
/*****************************************************/
void ExcelEngine::save()
{
	if (m_pWorkBook)
		m_pWorkBook->dynamicCall("Save()");
}

void ExcelEngine::saveAs(const QString& strPath)
{
	//QString filepath=QFileDialog::getSaveFileName(nullptr, tr("Save orbit"),".", tr("Microsoft Office 2010 (*.xlsx)"));//      
	//    filepath，      QDir::toNativeSeparators     "/"   "\"，        。
	m_pWorkBook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(strPath));
}