#pragma once
#include <QString>
#include <QVariant>
#include <QObject>
#include <QAxObject>
//class QAxObject;
class QExcel : public QObject
{
public:
QExcel(QString xlsFilePath, int iOfficType,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);
QVariant GetSheetValue();
bool readAll(QList<QList<QVariant> > &res);
void castVariant2ListListVariant(const QVariant &var, QList<QList<QVariant> > &res);
/**************************************************************************/
/* 单元格 */
/**************************************************************************/
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();
int GetOfficeOkType();
private:
QAxObject * m_pExcel;
QAxObject * m_pWorkBooks;
QAxObject * m_pWorkBook;
QAxObject * m_pSheets;
QAxObject * m_pSheet;
int m_pOfficeType;
};
#include <QFile>
#include <QStringList>
#include <QDebug>
#include "QExcel.h"
#include<windows.h>
#include "TransLogWid/LogNotify.h"
QExcel::QExcel(QString xlsFilePath,int iofficeType, QObject *parent)
{
m_pOfficeType = EN_OFFICE_TYPE_Unknown;
m_pExcel = NULL;
m_pWorkBooks = NULL;
m_pWorkBook = NULL;
m_pSheets = NULL;
m_pSheet = NULL;
//ket.Application Excel.Application
if (EN_OFFICE_TYPE_WIN == iofficeType)
{
m_pExcel = new QAxObject("Excel.Application", parent); //先尝试用windows office打开
m_pWorkBooks = m_pExcel->querySubObject("WorkBooks");
if (NULL != m_pWorkBooks)
{
m_pOfficeType = EN_OFFICE_TYPE_WIN;
}
else
{
CLogNotify::GetInstance().ShowMsgToHeartWid("打开windows office excel 失败!!!");
}
}
else if (EN_OFFICE_TYPE_WPS == iofficeType)
{
m_pExcel = new QAxObject("ket.Application", parent); //先尝试用wps office打开
m_pWorkBooks = m_pExcel->querySubObject("WorkBooks");
if (NULL != m_pWorkBooks)
{
m_pOfficeType = EN_OFFICE_TYPE_WPS;
}
else
{
CLogNotify::GetInstance().ShowMsgToHeartWid("打开wps office excel 失败!!!");
}
}
if (NULL == m_pWorkBooks)
{
CLogNotify::GetInstance().ShowMsgToHeartWid("打开excel 失败!!!");
return;
}
//CLogNotify::GetInstance().ShowMsgToHeartWid("打开excel 成功!!!");
QFile file(xlsFilePath);
if (file.exists())
{
m_pWorkBooks->dynamicCall("Open(const QString&)", xlsFilePath);
m_pWorkBook = m_pExcel->querySubObject("ActiveWorkBook");
m_pSheets = m_pWorkBook->querySubObject("WorkSheets");
}
}
QExcel::~QExcel()
{
close();
}
void QExcel::close()
{
m_pExcel->dynamicCall("Quit()");
delete m_pSheet;
delete m_pSheets;
delete m_pWorkBook;
delete m_pWorkBooks;
delete m_pExcel;
m_pExcel = 0;
m_pWorkBooks = 0;
m_pWorkBook = 0;
m_pSheets = 0;
m_pSheet = 0;
}
int QExcel::GetOfficeOkType()
{
return m_pOfficeType;
}
QAxObject *QExcel::getWorkBooks()
{
return m_pWorkBooks;
}
QAxObject *QExcel::getWorkBook()
{
return m_pWorkBook;
}
QAxObject *QExcel::getWorkSheets()
{
return m_pSheets;
}
QAxObject *QExcel::getWorkSheet()
{
return m_pSheet;
}
void QExcel::selectSheet(const QString& sheetName)
{
m_pSheet = m_pSheets->querySubObject("Item(const QString&)", sheetName);
}
void QExcel::deleteSheet(const QString& sheetName)
{
QAxObject * a = m_pSheets->querySubObject("Item(const QString&)", sheetName);
a->dynamicCall("delete");
}
void QExcel::deleteSheet(int sheetIndex)
{
QAxObject * a = m_pSheets->querySubObject("Item(int)", sheetIndex);
a->dynamicCall("delete");
}
void QExcel::selectSheet(int sheetIndex)
{
m_pSheet = m_pSheets->querySubObject("Item(int)", sheetIndex);
}
void QExcel::setCellString(int row, int column, const QString& value)
{
QAxObject *range = m_pSheet->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 = m_pSheet->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 = m_pSheet->querySubObject("Range(const QString&)", cell);
range = range->querySubObject("Font");
range->setProperty("Size", size);
}
void QExcel::mergeCells(const QString& cell)
{
QAxObject *range = m_pSheet->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 = m_pSheet->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 = m_pSheet->querySubObject("Cells(int,int)", row, column);
return range->dynamicCall("Value");// range->property("Value");
}
void QExcel::save()
{
m_pWorkBook->dynamicCall("Save()");
}
int QExcel::getSheetsCount()
{
return m_pSheets->property("Count").toInt();
}
QString QExcel::getSheetName()
{
return m_pSheet->property("Name").toString();
}
QString QExcel::getSheetName(int sheetIndex)
{
QAxObject * a = m_pSheets->querySubObject("Item(int)", sheetIndex);
return a->property("Name").toString();
}
QVariant QExcel::GetSheetValue()
{
QAxObject *usedRange = m_pSheet->querySubObject("UsedRange");
return usedRange->dynamicCall("Value2");
}
bool QExcel::readAll(QList<QList<QVariant> > &res)
{
QVariant var;
if (m_pSheet != NULL && !m_pSheet->isNull())
{
QAxObject *usedRange = m_pSheet->querySubObject("UsedRange");
if (NULL == usedRange || usedRange->isNull())
{
return false;
}
var = usedRange->dynamicCall("Value");
delete usedRange;
}
;
castVariant2ListListVariant(var, res);
return true;
}
void QExcel::castVariant2ListListVariant(const QVariant &var, QList<QList<QVariant> > &res)
{
QVariantList varRows = var.toList();
if (varRows.isEmpty())
{
return;
}
const int rowCount = varRows.size();
QVariantList rowData;
for (int i = 0; i < rowCount; ++i)
{
rowData = varRows[i].toList();
res.push_back(rowData);
}
}
void QExcel::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn)
{
QAxObject *usedRange = m_pSheet->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 = m_pSheet->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 = m_pSheet->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 = m_pSheet->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 = m_pSheet->querySubObject("Rows(const QString &)", rowsName);
rows->dynamicCall("AutoFit()");
}
void QExcel::insertSheet(QString sheetName)
{
m_pSheets->querySubObject("Add()");
QAxObject * a = m_pSheets->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 = m_pSheet->querySubObject("Range(const QString&)", cell);
range->dynamicCall("ClearContents()");
}
void QExcel::clearCell(const QString& cell)
{
QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
range->dynamicCall("ClearContents()");
}
int QExcel::getUsedRowsCount()
{
QAxObject *usedRange = m_pSheet->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 = m_pSheet->querySubObject("Range(const QString&)", cell);
range->dynamicCall("SetValue(const QString&)", value);
}
void QExcel::setCellFontSize(const QString &cell, int size)
{
QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
range = range->querySubObject("Font");
range->setProperty("Size", size);
}
void QExcel::setCellTextCenter(const QString &cell)
{
QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
range->setProperty("HorizontalAlignment", -4108);//xlCenter
}
void QExcel::setCellFontBold(const QString &cell, bool isBold)
{
QAxObject *range = m_pSheet->querySubObject("Range(const QString&)", cell);
range = range->querySubObject("Font");
range->setProperty("Bold", isBold);
}
void QExcel::setCellTextWrap(const QString &cell, bool isWrap)
{
QAxObject *range = m_pSheet->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 = m_pSheet->querySubObject("Rows(const QString &)", rowsName);
r->setProperty("RowHeight", height);
}