OpenXLSX是一个C++库，用于读取、写入、创建和修改.xlsx格式的Microsoft Excel文件，源码地址：https://github.com/troldal/OpenXLSX ，License为BSD-3-Clause，可在Windows、Linux、MaCOS平台上使用。最新发布版本为v0.3.2，发布版本已严重过时，使用master分支编译源码。
1. OpenXLSX依赖的第三方库：PugiXML、Zippy和Boost.Nowide，这些库均为头文件，已包含在代码库中，无需单独下载和构建。
2. OpenXLSX的重点是速度，而不是内存占用。
3. OpenXLSX使用CMake作为构建系统，OpenXLSX可以生成动态库或静态库，默认为静态库。
4. OpenXLSX中的所有字符串操作和使用都使用C++ std::string，它与编码无关，但可以用于UTF-8编码。此外，Excel内部使用UTF-8编码。
Windows10上通过CMake编译源码，shell脚本内容如下：build.sh存放在OpenXLSX根目录下
#! /bin/bash
if [ $HOSTTYPE != "x86_64" ]; then
echo_error "only support x86_64 architecture: current architecture: $HOSTTYPE"
fi
if [[ "$OSTYPE" != "msys" ]] && [[ "$OSTYPE" != "cygwin" ]]; then
echo_error "unsupported platform: $OSTYPE"
exit -1
fi
result_judgment()
{
rc=$?
if [[ ${rc} != 0 ]]; then
echo -e "\033[0;31mError: there are some errors in the above operation, please check: ${rc}\033[0m"
exit ${rc}
fi
}
echo_error()
{
echo -e "\033[0;31mError: $1\033[0m"
}
if [ $# != 1 ]; then
echo_error "requires a parameter: Release or Debug\n For example: $0 Debug"
exit -1
fi
if [ $1 != "Release" ] && [ $1 != "Debug" ]; then
echo_error "the parameter can only be Release or Debug"
exit -1
fi
mkdir -p build && cd build
cmake \
-G"Visual Studio 17 2022" -A x64 \
-DCMAKE_BUILD_TYPE=$1 \
-DCMAKE_CONFIGURATION_TYPES=$1 \
-DCMAKE_INSTALL_PREFIX=../install/$1 \
..
result_judgment
cmake --build . --target install --config $1
result_judgment
cd ../
mkdir -p install/$1/include/external
cp -a OpenXLSX/external/pugixml install/$1/include/external
echo -e "\033[0;32mbuild completed\033[0m"1. Debug库，执行：./build.sh Debug
2. Release库，执行：./build.sh Release
3. 注：因为在XLXmlParser.hpp中include了<external/pugixml/pugixml.hpp>，因此需要将external中的pugixml目录拷贝到install的include目录下
解析excel文件的测试代码如下：
int test_openxlsx_parse()
{
constexpr char file_name[]{ "../../../testdata/测试.xlsx" };
try {
OpenXLSX::XLDocument doc;
doc.open(gbk_to_utf8(file_name));
auto workbook = doc.workbook();
auto worksheet_names = workbook.worksheetNames();
if (worksheet_names.size() == 0) {
std::cerr << "Error: no worksheet: " << file_name << std::endl;
return -1;
}
std::cout << "worksheet names: ";
for (const auto& name : worksheet_names) {
std::cout << utf8_to_gbk(name) << ", ";
}
std::cout << std::endl;
auto worksheet_name = worksheet_names[0];
auto worksheet = workbook.worksheet(worksheet_name);
if (worksheet.rowCount() == 0) {
std::cerr << "Error: excel table is empty: " << file_name << std::endl;
return -1;
}
auto range = worksheet.range();
auto rows = range.numRows();
auto cols = range.numColumns();
std::cout << "file name: " << file_name << ", work sheet name: " << utf8_to_gbk(worksheet_name) << ", rows: " << rows << ", cols: " << cols << std::endl;
std::string str{};
for (auto row = 1; row <= rows; ++row) {
for (auto col = 1; col <= cols; ++col) {
auto cell = worksheet.cell(row, col);
if (cell.value().type() == OpenXLSX::XLValueType::String) {
str = cell.value().get<std::string>();
std::cout << utf8_to_gbk(str) << "\t";
}
else if (cell.value().type() == OpenXLSX::XLValueType::Integer) {
std::cout << cell.value().get<int64_t>() << "\t";
}
else if (cell.value().type() == OpenXLSX::XLValueType::Float) {
std::cout << cell.value().get<float>() << "\t";
}
}
std::cout << std::endl;
}
const std::vector<std::string> header_row{ "代码", "-200大", "序号", "库存(T)", "名称" };
for (auto col = 1; col <= cols; ++col) {
auto cell = worksheet.cell(1, col);
for (auto i = 0; i < header_row.size(); ++i) {
if (cell.value().type() == OpenXLSX::XLValueType::String) {
if (utf8_to_gbk(cell.value().get<std::string>()) == header_row[i]) {
std::cout << "第 " << col << " 列表示:" << header_row[i] << std::endl;
break;
}
}
}
}
doc.close();
}
catch (const std::exception& e) {
std::cerr << "Error: " << e.what() << std::endl;
return -1;
}
return 0;
}excel文件测试.xlsx内容如下：
执行结果如下图所示：
创建excel文件测试代码如下：
int test_openxlsx_create()
{
constexpr char file_name[]{ "../../../testdata/创建.xlsx" };
try {
OpenXLSX::XLDocument doc;
doc.create(gbk_to_utf8(file_name), OpenXLSX::XLForceOverwrite);
auto workbook = doc.workbook();
constexpr char sheet_name[]{ "信息" };
workbook.addWorksheet(gbk_to_utf8(sheet_name));
constexpr char default_sheet[]{ "Sheet1" };
if (workbook.worksheetExists(default_sheet)) {
workbook.deleteSheet(default_sheet);
}
const std::vector<std::string> header_row{ "序号","名称", "代码", "-200大", "库存(T)" };
using info = std::tuple<int, std::string, std::string, float, float>;
const std::vector<info> infos{
{1, "铁矿", "DAV382", 232.1, 10},
{2, "Tiger", "234SCS", 2334, 18.5},
{3, "香蕉B", "2349CX", 233.5, -192.4},
{4, "Apple号", "232398", 8.48, 23} };
auto worksheet = workbook.worksheet(gbk_to_utf8(sheet_name));
int col = 1;
for (const auto& value : header_row) {
worksheet.cell(1, col).value() = gbk_to_utf8(value);
++col;
}
auto float_to_string = [](float value) {
std::ostringstream oss;
oss << value;
return oss.str();
};
int row = 2;
for (const auto& [num, str1, str2, f1, f2] : infos) {
worksheet.cell(row, 1).value() = num;
worksheet.cell(row, 2).value() = gbk_to_utf8(str1);
worksheet.cell(row, 3).value() = gbk_to_utf8(str2);
worksheet.cell(row, 4).value() = f1; // 注:直接赋值f1,在excel表中显示与原始值有差异;使用float_to_string(f1)又会导致再次解析时此数据类型为string而不是float
worksheet.cell(row, 5).value() = f2;
++row;
}
doc.save();
doc.close();
}
catch (const std::exception& e) {
std::cerr << "Error: " << e.what() << std::endl;
return -1;
}
return 0;
}运行程序，生成的excel文件创建.xlsx内容如下：
辅助函数内容如下：
inline std::string gbk_to_utf8(const std::string& str)
{
// gbk to wchar
auto len = ::MultiByteToWideChar(CP_ACP, 0, str.c_str(), -1, nullptr, 0);
std::wstring wstr(len, 0);
::MultiByteToWideChar(CP_ACP, 0, str.c_str(), -1, &wstr[0], len);
// wchar to utf8
len = ::WideCharToMultiByte(CP_UTF8, 0, wstr.c_str(), -1, nullptr, 0, nullptr, nullptr);
std::string u8str(len, 0);
::WideCharToMultiByte(CP_UTF8, 0, wstr.c_str(), -1, &u8str[0], len, nullptr, nullptr);
u8str.pop_back(); // remove '\0'
return u8str;
}
inline std::string utf8_to_gbk(const std::string& u8str)
{
// utf8 to wchar
auto len = ::MultiByteToWideChar(CP_UTF8, 0, u8str.c_str(), -1, nullptr, 0);
std::wstring wstr(len, 0);
::MultiByteToWideChar(CP_UTF8, 0, u8str.c_str(), -1, &wstr[0], len);
// wchar to gbk
len = ::WideCharToMultiByte(CP_ACP, 0, wstr.c_str(), -1, nullptr, 0, nullptr, nullptr);
std::string str(len, 0);
::WideCharToMultiByte(CP_ACP, 0, wstr.c_str(), -1, &str[0], len, nullptr, nullptr);
str.pop_back(); // remove '\0'
return str;
}注：调用OpenXLSX接口时
1. 行、列索引从1开始
2. 中文文件名或excel表中带有中文的需额外处理