转载:https://www.cnblogs.com/nxopen2018/p/12197604.html
qtExcelApi.h
#pragma once
#include <ActiveQt/QAxObject>
#include <QDir>
using namespace std;
class qtExcelApi
{
public:
qtExcelApi(void);
~qtExcelApi(void);
/**
* @brief 新建Excel
*/
void CreateExcel();
/**
* @brief 打开Excel
* @param[in] filePath 路径
* @param[in] type 打开时是否显示EXCEL
* @return BOOL 打开成功&失败
*/
bool OpenFile(const char* filePath, bool type);
/**
* @brief 关闭Excel
*/
void CloseExcel();
/**
* @brief 保存Excel
*/
void Save(const char* savePath);
/**
* @brief 获取所有的工作表数量
* @return int 数量
*/
int GetSheetCount();
/**
* @brief 获取单元格数据
* @param[in] row 行
* @param[in] column 列
* @return string 内容
*/
string GetRangeData(const int row, const int column);
/**
* @brief 读取整个sheet
* @return string 内容
*/
vector<string> GetUsedRange();
/**
* @brief 读取sheet中的一个范围
* @return string 内容
*/
vector<string> GetScopeRange(const char* A1, const char* A5);
/**
* @brief 当前sheet单元格写入内容
* @param[in] row 行
* @param[in] column 列
* @param[in] Data 内容
*/
void SetRangeData(const int row, const int column, const char* Data);
/**
* @brief 获得当前sheet使用的行数
* @return int 数量
*/
int GetRowNum();
/**
* @brief 获得当前sheet使用的列数
* @return int 数量
*/
int GetColumnNum();
/**
* @brief 设置当前工作表
* @param[in] id 第几个sheet,从1开始
* @return BOOL 设置成功&失败
*/
bool SetCurrentSheetByNum(const int& id);
private:
//Excel应用程序
QAxObject *ExcelApp;
//Excel工作簿
QAxObject *ExcelBooks;
QAxObject *ExcelBook;
//Excel工作表
QAxObject *ExcelSheets;
QAxObject *ExcelSheet;
//Excel单元格
QAxObject *ExcelRange;
};
qtExcelApi.cpp
#include "qtExcelApi.h"
qtExcelApi::qtExcelApi(void)
{
}
qtExcelApi::~qtExcelApi()
{
}
//打开Excel
void qtExcelApi::CreateExcel()
{
//连接excel
ExcelApp = new QAxObject("Excel.Application");
//是否可视化excel
ExcelApp->dynamicCall("SetVisible(bool Visible)", true);
//是否弹出警告窗口
ExcelApp->setProperty("DisplayAlerts", false);
//获取工作簿集合
ExcelBooks = ExcelApp->querySubObject("WorkBooks");
//新建一个工作簿
ExcelBooks->dynamicCall("Add");
//获取当前工作簿
ExcelBook = ExcelApp->querySubObject("ActiveWorkBook");
//获取工作表格集合
ExcelSheets = ExcelBook->querySubObject("Sheets");
//获取当前工作表格1,即sheet1
ExcelSheet = ExcelSheets->querySubObject("Item(int)", 1);
}
//打开Excel
bool qtExcelApi::OpenFile(const char* filePath, bool type)
{
//连接excel
ExcelApp = new QAxObject("Excel.Application");
//是否可视化excel
ExcelApp->dynamicCall("SetVisible(bool Visible)", type);
//是否弹出警告窗口
ExcelApp->setProperty("DisplayAlerts", false);
//获取工作簿集合
ExcelBooks = ExcelApp->querySubObject("WorkBooks");
//打开一个工作簿
ExcelBooks->dynamicCall("Open(const QString&)", QDir::toNativeSeparators(filePath));
//获取当前工作簿
ExcelBook = ExcelApp->querySubObject("ActiveWorkBook");
//获取工作表格集合
ExcelSheets = ExcelBook->querySubObject("Sheets");
//获取当前工作表格1,即sheet1
ExcelSheet = ExcelSheets->querySubObject("Item(int)", 1);
return true;
}
//关闭Excel
void qtExcelApi::CloseExcel()
{
if (ExcelApp != NULL)
{
ExcelApp->dynamicCall("Quit()");
delete ExcelApp;
ExcelApp = NULL;
}
}
//保存Excel
void qtExcelApi::Save(const char* savePath)
{
ExcelBook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(savePath));
}
//获取单元格数据
string qtExcelApi::GetRangeData(const int row, const int column)
{
QVariant ret;
QByteArray cdata;
if (this->ExcelSheet != NULL && !this->ExcelSheet->isNull())
{
QAxObject* range = this->ExcelSheet->querySubObject("Cells(int, int)", row, column);
ret = range->dynamicCall("Value()");
QString qs = ret.toString();
cdata = qs.toLocal8Bit();
delete range;
}
return string(cdata);;
}
//读取整个sheet
vector<string> qtExcelApi::GetUsedRange()
{
//读取当前工作表所有数据
QAxObject *usedRange = ExcelSheet->querySubObject("UsedRange");
QVariant var = usedRange->dynamicCall("Value()");
QVariantList varRows = var.toList();
const int rowCount = varRows.size();
QVariantList rowData;
QByteArray cdata;
vector<string> vecUsedRange;
for (int i = 0; i < rowCount; ++i)
{
rowData = varRows[i].toList();
QString qs = rowData[0].toString();
cdata = qs.toLocal8Bit();
vecUsedRange.push_back(string(cdata));
}
delete usedRange;
return vecUsedRange;
}
//读取sheet中的一个范围
vector<string> qtExcelApi::GetScopeRange(const char* A1, const char* A5)
{
QVariantList params;
params << A1 << A5; //A1至A5的数据
//读取当前工作表所有数据
QAxObject *cell = ExcelSheet->querySubObject("Range(QVariant,QVariant)", params);
QVariant var = cell->dynamicCall("Value2()");
QVariantList varRows = var.toList();
const int rowCount = varRows.size();
QVariantList rowData;
QByteArray cdata;
vector<string> vecUsedRange;
for (int i = 0; i < rowCount; ++i)
{
rowData = varRows[i].toList();
QString qs = rowData[0].toString();
cdata = qs.toLocal8Bit();
vecUsedRange.push_back(string(cdata));
}
delete cell;
return vecUsedRange;
}
//当前sheet单元格写入内容
void qtExcelApi::SetRangeData(const int row, const int column, const char* Data)
{
QAxObject *cell = ExcelSheet->querySubObject("Cells(int,int)", row, column);
cell->setProperty("Value", Data);
}
//获取所有的工作表数量
int qtExcelApi::GetSheetCount()
{
int intCount = ExcelSheets->property("Count").toInt();
return intCount;
}
//获得当前sheet使用的行数
int qtExcelApi::GetRowNum()
{
QAxObject *usedRange = ExcelSheet->querySubObject("UsedRange");
QAxObject *rows = usedRange->querySubObject("Rows");
int nRows = rows->property("Count").toInt();
return nRows;
}
//获得当前sheet使用的列数
int qtExcelApi::GetColumnNum()
{
QAxObject *usedRange = ExcelSheet->querySubObject("UsedRange");
QAxObject *columns = usedRange->querySubObject("Columns");
int nCols = columns->property("Count").toInt();
return nCols;
}
//设置当前工作表
bool qtExcelApi::SetCurrentSheetByNum(const int& id)
{
ExcelSheet = ExcelBook->querySubObject("Worksheets(int)", 2);
return true;
}