C++操作Excel，xlnt库的使用

1、xlnt是用于在内存中操作Excel表格和.xlsx文件中读取/写入Excel表格的C++库。

2、IDE：Visual Studio 2015。

     2.1、工程环境：

      》dll文件

      》lib库文件

      》头文件

     2.2设置项目属性

             右击属性--》配置属性--》VC++目录--》包含目录

      链接器--》输入--》附加依赖项，加入：$(SolutionDir)lib\xlntd.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib uuid.lib odbc32.lib odbccp32.lib

3、main.cpp

// TestXlnt.cpp : 
//

#include "stdafx.h"
#include <Windows.h>
#include <xlnt/xlnt.hpp>

//Unicode 转 utf-8
bool WStringToString(const std::wstring &wstr, std::string &str)
{
	int nLen = (int)wstr.length();
	int nDesSize = WideCharToMultiByte(CP_UTF8, 0, (LPCWSTR)wstr.c_str(), nLen, NULL, 0, NULL, NULL);
	str.resize(nDesSize, '\0');

	int nResult = WideCharToMultiByte(CP_UTF8, 0, (LPCWSTR)wstr.c_str(), nLen, (LPSTR)str.c_str(), nDesSize, NULL, NULL);

	if (nResult == 0)
	{
		//DWORD dwErrorCode = GetLastError();
		return FALSE;
	}

	return TRUE;
}

int main()
{
	const char Signal_0Name[][16] = { "id","chID","PTCPCBTem"," PTCOverHeat",  "PTCOverCrnt","PTCOverVol"," PTCRlngCtr" ,"PTCActuCrnt","PTCActuPwr", "Ptc_work_statu","PTCUnderVol","PTCIGBTSts"," PTCHVLckSts","PTCIGBTOverHeat" ,"PTCChksm" };
	const char Signal_1Name[][16] = { "id","chID","PTCSecRlngCtr", "PTCActuVol", " PTCINNTCSts","PTCOUTNTCSts", "PTCIGBTNTCSts" ,"PTCIGBTTem","PTCInptTem", "PTCOtptTem","PTCSecChksm" };

	try
	{
		xlnt::workbook wb;
		xlnt::worksheet sheetMain = wb.active_sheet();
		xlnt::worksheet sheet1 = wb.copy_sheet(sheetMain);
		xlnt::worksheet sheet2 = wb.copy_sheet(sheet1);

		//----------------sheetmain----------------------------------------------
		//常规操作
		sheetMain.cell("A1").value(5);
		sheetMain.cell("B2").value("string data");
		sheetMain.cell("C3").formula("=RAND()");
		//中文处理
		std::string strDes;
		WStringToString(L"测试", strDes);
		sheetMain.cell(5, 5).value(strDes);

		sheetMain.title("main");

		//----------------sheet1----------------------------------------------
		//行批量写入
		for (int i = 0; i<15; i++)
		{
			sheet1.cell(i + 1, 1).value(Signal_0Name[i]);
		}
		//常规操作
		sheet1.cell("A2").value(5);
		sheet1.cell("B3").value("string data");
		sheet1.cell("C4").formula("=RAND()");

		//合并单元格
		sheet1.merge_cells("C3:C4");
		sheet1.freeze_panes("B2");

		sheet1.title("sheet1");

		//----------------sheet2----------------------------------------------
		//列批量写入
		for (int i = 0; i<11; i++)
		{
			sheet2.cell(1, i + 1).value(Signal_1Name[i]);
		}
		sheet2.cell("C1").value("C1");
		sheet2.cell("B1").value("B1");
		sheet2.title("sheet2");
		sheet2.freeze_panes("B2");

		wb.save("test.xlsx");
	}
	catch (std::exception e)
	{
		std::string s = e.what();
	}

    return 0;
}

4、生成文件

sheetmain

sheet1

sheet2

 

xlnt支持设置字体，文件加密等功能，具体请移步官方文档。