.hpp
#pragma once
#include "include_cpp/libxl.h"
#include <string>
#include <sstream>
#include <uf_ui.h>
#pragma comment(lib,"include_cpp/VM_excel_64.lib")
using namespace std;
using namespace libxl;
class TYW_Excel
{
public:
TYW_Excel();
~TYW_Excel();
//输出报错的文件信息和报错文件路径
void OutError(string message, string failPath);
//打开Excel表格
bool OpenExcelBook(string excelPath);
//打开Excel表格页
bool OpenExcelSheet(int page);
//打开Excel表格页
bool OpenExcelSheet(const char* pageName);
//读取Excel单元格数据
string ReadExcelCellData(int row, int col);
//查找指定内容属于单元格哪一行
int FindDataOfRow(const char* cellData/*in 输入查找内容*/, int col/*in 输入列*/);
//查找指定内容属于单元格哪一列
int FindDataOfCol(const char* cellData/*in 输入查找内容*/, int row/*in 输入行*/);
// 获取从指定列开始的一行的数据
std::vector<string> GetExeclRowData(int col);
// 获取从指定行开始的一列的数据
std::vector<string> GetExeclColData(int row);
int m_lastRow; //最大行数
int m_lastCol; //最大列数
//打印数据
template<typename T>
TYW_Excel& myPrint(T data)
{
UF_UI_open_listing_window();
stringstream buf;
buf << data << " ";
string str = buf.str();
if (str.find("\n ") != -1)
{
str = str.replace(str.find("\n "), 2, "\n");
}
UF_UI_write_listing_window(str.c_str());
return *this;
}
private:
Book* m_book;
Sheet* m_sheet;
};
.cpp
#include "TYW_Excel.h"
TYW_Excel::TYW_Excel()
{
m_book = xlCreateBook();
//m_book->setKey("TommoT", "windows-2421220b07c2e10a6eb96768a2p7r6gc");
m_book->setKey("Halil Kural", "windows-2723210a07c4e90162b26966a8jcdboe");
if (!m_book)
{
uc1601("创建实例失败", 1);
}
m_sheet = NULL;
m_lastRow = 0; //最大行数
m_lastCol = 0; //最大列数
}
TYW_Excel::~TYW_Excel()
{
if (m_book)
{
m_book->release();
m_book = NULL;
m_sheet = NULL;
}
}
//输出报错的文件信息和报错文件路径
void TYW_Excel::OutError(string message, string failPath)
{
string failMessage = message + ":*" + failPath;
uc1601((char*)failMessage.c_str(), 1);
}
//打开Excel表格
bool TYW_Excel::OpenExcelBook(string excelPath)
{
if (!m_book)
{
uc1601("m_book 为 NULL", 1);
return false;
}
if (!m_book->load(excelPath.c_str()))
{
OutError("EXCEL表格不存在", excelPath);
return false;
}
//myPrint(excelPath).myPrint("表格路径\n");
return true;
}
//打开Excel表格页
bool TYW_Excel::OpenExcelSheet(int page)
{
if (!m_book)
{
uc1601("m_book 为 NULL", 1);
return false;
}
m_sheet = m_book->getSheet(page);
if (!m_sheet)
{
uc1601("获取表失败", 1);
return false;
}
m_lastRow = m_sheet->lastRow(); //最大行数
m_lastCol = m_sheet->lastCol(); //最大列数
return true;
}
//打开Excel表格页
bool TYW_Excel::OpenExcelSheet(const char* pageName)
{
if (!m_book)
{
uc1601("m_book 为 NULL", 1);
return false;
}
bool isFindSheet = false;
int sheetNum = m_book->sheetCount();
//myPrint(sheetNum).myPrint("页数量\n");
for (int i = 0; i < sheetNum; i++)
{
Sheet* sheet01 = m_book->getSheet(i);
const char* sheetName = sheet01->name();
//myPrint(sheetName).myPrint("遍历表格页名称\n");
if (strcmp(sheetName, pageName) == 0)
{
m_sheet = sheet01;
isFindSheet = true;
break;
}
}
if (!isFindSheet)
{
string msgErr = "未找到表格页名称:*";
msgErr.append(pageName);
uc1601((char*)msgErr.c_str(), 1);
return isFindSheet;
}
if (!isFindSheet)
{
string msgErr = "未找到表格页名称:*";
msgErr.append(pageName);
uc1601((char*)msgErr.c_str(), 1);
return isFindSheet;
}
m_lastRow = m_sheet->lastRow(); //最大行数
m_lastCol = m_sheet->lastCol(); //最大列数
return isFindSheet;
}
//读取Excel单元格数据
string TYW_Excel::ReadExcelCellData(int row, int col)
{
if (!m_sheet)
{
uc1601("m_sheet 为 NULL", 1);
return false;
}
ostringstream oss;
CellType CellFormat = m_sheet->cellType(row, col);
if (CellFormat == CELLTYPE_EMPTY)
{
oss << "";
}
else if (CellFormat == CELLTYPE_NUMBER)
{
oss << m_sheet->readNum(row, col);
}
else if (CellFormat == CELLTYPE_STRING)
{
oss << m_sheet->readStr(row, col);
}
else if(CellFormat == CELLTYPE_BLANK)
{
oss << "";
}
else if(CellFormat == CELLTYPE_BOOLEAN)
{
oss << m_sheet->readBool(row, col)) ? ("True") : ("False");
}
else if(CellFormat == CELLTYPE_ERROR)
{
oss << "";
}
return oss.str();
}
//查找指定内容属于单元格哪一行
int TYW_Excel::FindDataOfRow(const char* cellData/*in 输入查找内容*/, int col/*in 输入列*/)
{
int row = -1;
for (int i = 0; i < m_lastRow; i++)
{
string readData = ReadExcelCellData(i, col);
if (!strcmp(readData.c_str(), cellData))
{
row = i;
break;
}
}
return row;
}
//查找指定内容属于单元格哪一列
int TYW_Excel::FindDataOfCol(const char* cellData/*in 输入查找内容*/, int row/*in 输入行*/)
{
int col = -1;
for (int i = 0; i < m_lastCol; i++)
{
string readData = ReadExcelCellData(row, i);
if (!strcmp(readData.c_str(), cellData))
{
col = i;
break;
}
}
return col;
}
// 获取从指定列开始的一行的数据
std::vector<string> GetExeclRowData(int col)
{
std::vector<string> vecStr;
std::string str;
for(int i = 0; i < m_lastRow; ++i)
{
str = m_sheet.ReadExcelCellData(i, col);
if(strcmp(str.c_str(), ""))
{
vecStr.push_back(str);
}
}
return vecStr;
}
// 获取从指定行开始的一列的数据
std::vector<string> GetExeclColData(int row)
{
std::vector<string> vecStr;
std::string str;
for(int i = 0; i < m_lastCol; ++i)
{
str = m_sheet.ReadExcelCellData(row, i);
if(strcmp(str.c_str(), ""))
{
vecStr.push_back(str);
}
}
return vecStr;
}