【C++常用API】Libxl读写excel表

.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;
}