#include "qexcel.h"
QExcel::QExcel(QString name) :
excel(nullptr),
workBooks(nullptr),
workBook(nullptr),
sheets(nullptr),
sheet(nullptr)
{
CoInitializeEx(NULL, COINIT_MULTITHREADED);
excel = new QAxObject(this);
excel->setControl("Excel.Application");
excel->dynamicCall("SetVisible (bool Visible)", "false");
excel->setProperty("DisplayAlerts", false);
workBooks = excel->querySubObject("Workbooks");
NewExcel(name);
if(!name.isEmpty())
workBooks->dynamicCall("Open(const QString&)", name);
else
workBooks->dynamicCall("Add");
workBook = excel->querySubObject("ActiveWorkBook");
sheets = workBook->querySubObject("WorkSheets");
}
QExcel::~QExcel()
{
close();
}
void QExcel::NewExcel(QString name)
{
QString fileName = QDir::toNativeSeparators(name);
QFile file(fileName);
if(!file.exists())
{
workBooks->dynamicCall("Add");
workBook = excel->querySubObject("ActiveWorkBook");
saveAs(fileName);
}
}
void QExcel::writeTitle(const QString& module, const QStringList& titles)
{
freeSheet();
sheets->querySubObject("Add()");
sheet = sheets->querySubObject("Item(int)", 1);
sheet->setProperty("Name", module);
for (int column =0; column < titles.size(); ++column)
{
QAxObject* cell = sheet->querySubObject("Cells(int,int)", 1, column + 1);
cell->dynamicCall("SetValue(const QString&)", titles.at(column));
delete cell;
cell = nullptr;
}
}
void QExcel::writeLineTest(int row, QVariantList pValues, quint32 valCnt)
{
for (quint32 column = 0; column < valCnt; ++column)
{
QAxObject* cell = sheet->querySubObject("Cells(int,int)", row, column + 1);
cell->dynamicCall("SetValue(const QString&)", pValues[column]);
delete cell;
cell = nullptr;
}
}
QVariant QExcel::readLine(int row, int column)
{
QVariant data;
QAxObject* range = sheet->querySubObject("Cells(int,int)", row, column);
// return range->property("value");
if (range)
{
data = range->dynamicCall("Value2()");
}
return data;
}
void QExcel::close()
{
//关闭excel
excel->dynamicCall("Quit()");
delete sheet;
delete sheets;
delete workBook;
delete workBooks;
delete excel;
excel = nullptr;
workBooks = nullptr;
workBook = nullptr;
sheets = nullptr;
sheet = nullptr;
CoUninitialize();
}
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::copyRangeToRange(QString resource, QString target)
{
if (sheet)
{
QAxObject* resRange = sheet->querySubObject("Range(const QString&)", resource);
resRange->dynamicCall("Copy");//复制指令区域内容到剪贴板
QAxObject* tarRange = sheet->querySubObject("Range(const QString&)", target);//选中目标区域
tarRange->dynamicCall("PasteSpecial");
}
}
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()");
}
void QExcel::saveAs(const QString& filePath)
{
workBook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(filePath));
workBook->dynamicCall("Close(Boolean)", true);
}
int QExcel::getSheetsCount()
{
return sheets->property("Count").toInt();
}
bool QExcel::isExistSheet(QString sheetName)
{
int sheetnum = getSheetsCount();
for(int i = 1;i <= sheetnum;i++)
{
selectSheet(i);
if(getSheetName() == sheetName)
{
return true;
}
}
return false;
}
QString QExcel::getSheetName()
{
if(sheet)
return sheet->property("Name").toString();
else
return "";
}
QString QExcel::getSheetName(int sheetIndex)
{
QAxObject* a = sheets->querySubObject("Item(int)", sheetIndex);
return a->property("Name").toString();
}
bool QExcel::readSheet(QString sheetName, QVariantList& datas)
{
selectSheet(sheetName);
if (sheet == nullptr)
return false;
if (QAxObject* usedRange = sheet->querySubObject("UsedRange"))
{
QVariantList varRows = usedRange->dynamicCall("Value").toList();
datas = varRows;
}
return true;
}
bool QExcel::readSheet(QString sheetName, int row, int column, QVariantList &datas)
{
selectSheet(sheetName);
if (sheet == nullptr)
return false;
if (QAxObject* usedRange = sheet->querySubObject("UsedRange"))
{
QVariantList varRows = usedRange->dynamicCall("Value").toList();
datas = varRows;
}
for(int i = 0;i < row;i++)
{
datas.removeAt(0);
}
for(int i = 0;i < datas.length();i++)
{
QVariantList listvalue = datas.at(i).value<QVariantList>();
for(int j = 0;j < column;j++)
{
listvalue.removeAt(0);
}
datas.replace(i, listvalue);
}
return true;
}
bool QExcel::writeSheet(QString sheetName, int startRow, int startCol, QVariantList& datas)
{
selectSheet(sheetName);
if (sheet == nullptr)
return false;
for (auto iter : datas)
{
QList<QVariant> rowDatas = iter.toL
