C++中使用xlnt库读写表格信息excel

简介

C++ 中用于读写表格的库比较多，但是有的收费、有的不好用。
网上的博客太多，海量教程中想找到适合自己的方法实在太难了。下面是我看过的一些比较优秀的教程，整理记录一下：

C++读写EXCEL文件方式比较。补充：读写csv也是可以的，但其实 写csv有点投机取巧，不算真正的excel。
开源项目QtXlsxWriter，用的是qmake（我不熟），而不是cmake，所以我放弃
开源项目OpenXLSX，需要C++17，我只有vs2015，不想装vs2019，故弃之
OpenXLSX读写教程，R语言
C++使用OLE高速读写EXCEL的源码，有代码，但是有点复杂，搞不懂
xlnt代码，xlnt详细文档
下面就介绍一下，如何用xlnt这个开源库，非常非常简单的实现excel的读写。

编译xlnt库

直接使用别人编译好的xlnt库

编译生成的东西就在build/installed 目录下，和其他库一样，我们配置包含头文件、库目录、链接库名字，将所需要的dll放在x64及exe所在目录下。参见此目录xlnt库：包含Debug和Release版本的32位和64位库文件。
有两个文件夹，包括xlnt和lib，xlnt中是包含文件头文件内容，lib中有64位和32位的库，每个库下面有debug和release版本的lib文件，以及dll文件。
按照C++一般配置库文件的方式就可以，VC++目录中添加包含文件目录../xlnt ,包含库目录添加到lib/x64,链接库目录填xlntd.lib,
注意xlnt与vs工程的版本和位数要一样（我的都是Debug x64），否则会出现无法链接库的问题。

读写例子测试

例子

将一组数据写入一个新的excel表格，

写某一列，其他雷同
首先创建表格wb，然后写入表单ws，最后保存表格wb
定位写入位置xlnt::cell_reference(int cols, int rows) ，写入值value（），可以是int float string

//====输出模型面数及四面体网格数
	std::string dest_filename = "d://output.xlsx";
	xlnt::workbook wb;
	xlnt::worksheet ws = wb.active_sheet();
	//ws.cell("A1").value(5);				// 写入数值
	//ws.cell("B2").value("string data");	// 写入字符串
	
	for (int i = 0; i < modelPointer.size(); i++)
	{
		PBDmodel* pbd = modelPointer[i];
		int tetSize = pbd->tets.size();
		int triSize = pbd->tris.size();
		int vertexSize = pbd->vertices.size();
		ws.cell(xlnt::cell_reference(1, i + 1)).value(pbd->modelname);
		ws.cell(xlnt::cell_reference(2, i+1)).value(triSize);
		ws.cell(xlnt::cell_reference(3, i+1)).value(tetSize);
		ws.cell(xlnt::cell_reference(4, i + 1)).value(vertexSize);
	}
	wb.save(dest_filename);

---

#include <iostream>
#include <xlnt/xlnt.hpp>
#include <vector>
#include <string>

int main()
{
    //Creating a 2 dimensional vector which we will write values to
    std::vector< std::vector<std::string> > wholeWorksheet;
    //Looping through each row (100 rows as per the second argument in the for loop)
    for (int outer = 0; outer < 100; outer++)
    {
        //Creating a fresh vector for a fresh row
    std::vector<std::string> singleRow;
    //Looping through each of the columns (100 as per the second argument in the for loop) in this particular row
    for(int inner = 0; inner < 100; inner++)
    {
        //Adding a single value in each cell of the row 
        std::string val = std::to_string(inner + 1);
        singleRow.push_back(val);            
    }
    //Adding the single row to the 2 dimensional vector
    wholeWorksheet.push_back(singleRow);
    std::clog << "Writing to row " << outer << " in the vector " << std::endl;
    }
    //Writing to the spread sheet
    //Creating the output workbook 
    std::clog << "Creating workbook" << std::endl;
    xlnt::workbook wbOut;
    //Setting the destination output file name
    std::string dest_filename = "output.xlsx";
    //Creating the output worksheet
    xlnt::worksheet wsOut = wbOut.active_sheet();
    //Giving the output worksheet a title/name
    wsOut.title("data");
    //We will now be looping through the 2 dimensional vector which we created above
    //In this case we have two iterators one for the outer loop (row) and one for the inner loop (column)
    std::clog << "Looping through vector and writing to spread sheet" << std::endl;
    for (int fOut = 0; fOut < wholeWorksheet.size(); fOut++)
    {
        std::clog << "Row" << fOut << std::endl;
        for (int fIn = 0; fIn < wholeWorksheet.at(fOut).size(); fIn++)
        {
            //Take notice of the difference between accessing the vector and accessing the work sheet
        //As you may already know Excel spread sheets start at row 1 and column 1 (not row 0 and column 0 like you would expect from a C++ vector) 
        //In short the xlnt cell reference starts at column 1 row 1 (hence the + 1s below) and the vector reference starts at row 0 and column 0
        wsOut.cell(xlnt::cell_reference(fIn + 1, fOut + 1)).value(wholeWorksheet.at(fOut).at(fIn));
        //Further clarification to avoid confusion
        //Cell reference arguments are (column number, row number); e.g. cell_reference(fIn + 1, fOut + 1)
        //Vector arguments are (row number, column number); e.g. wholeWorksheet.at(fOut).at(fIn)
    }
    }
    std::clog << "Finished writing spread sheet" << std::endl;
    wbOut.save(dest_filename); 
    return 0;
}

将一组数据写入一个已存在的表格

#include <iostream>
#include <xlnt/xlnt.hpp>
#include <vector>

int main()
{
    xlnt::workbook wb;
    wb.load("/home/timothymccallum/test.xlsx");
    auto ws = wb.active_sheet();
    std::clog << "Processing spread sheet" << std::endl;
    std::clog << "Creating a single vector which stores the whole spread sheet" << std::endl;
    std::vector< std::vector<std::string> > theWholeSpreadSheet;
    for (auto row : ws.rows(false)) 
    { 
        std::clog << "Creating a fresh vector for just this row in the spread sheet" << std::endl;
    std::vector<std::string> aSingleRow;
    for (auto cell : row) 
    { 
        std::clog << "Adding this cell to the row" << std::endl;
        aSingleRow.push_back(cell.to_string());
    }
    std::clog << "Adding this entire row to the vector which stores the whole spread sheet" << std::endl;
    theWholeSpreadSheet.push_back(aSingleRow);
    }
    std::clog << "Processing complete" << std::endl;
    std::clog << "Reading the vector and printing output to the screen" << std::endl;
    for (int rowInt = 0; rowInt < theWholeSpreadSheet.size(); rowInt++)
    {
        for (int colInt = 0; colInt < theWholeSpreadSheet.at(rowInt).size(); colInt++)
    {
        std::cout << theWholeSpreadSheet.at(rowInt).at(colInt) << std::endl;
        }
    }
    return 0;
}

读取表格

#include <iostream>
#include <xlnt/xlnt.hpp>

int main()
{
    xlnt::workbook wb;
    wb.load("/home/timothymccallum/test.xlsx");
    auto ws = wb.active_sheet();
    std::clog << "Processing spread sheet" << std::endl;
    for (auto row : ws.rows(false)) 
    { 
        for (auto cell : row) 
    { 
        std::clog << cell.to_string() << std::endl;
    }
    }
    std::clog << "Processing complete" << std::endl;
    return 0;
}