解析、创建Excel文件的开源库OpenXLSX介绍
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/bashif [ $HOSTTYPE != "x86_64" ]; thenecho_error "only support x86_64 architecture: current architecture: $HOSTTYPE"
fiif [[ "$OSTYPE" != "msys" ]] && [[ "$OSTYPE" != "cygwin" ]]; thenecho_error "unsupported platform: $OSTYPE"exit -1
firesult_judgment()
{rc=$?if [[ ${rc} != 0 ]]; thenecho -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 ]; thenecho_error "requires a parameter: Release or Debug\n For example: $0 Debug"exit -1
fiif [ $1 != "Release" ] && [ $1 != "Debug" ]; thenecho_error "the parameter can only be Release or Debug"exit -1
fimkdir -p build && cd buildcmake \-G"Visual Studio 17 2022" -A x64 \-DCMAKE_BUILD_TYPE=$1 \-DCMAKE_CONFIGURATION_TYPES=$1 \-DCMAKE_INSTALL_PREFIX=../install/$1 \..
result_judgmentcmake --build . --target install --config $1
result_judgment
cd ../mkdir -p install/$1/include/external
cp -a OpenXLSX/external/pugixml install/$1/include/externalecho -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而不是floatworksheet.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 wcharauto 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 utf8len = ::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 wcharauto 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 gbklen = ::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表中带有中文的需额外处理
GitHub：https://github.com/fengbingchun/Messy_Test