C++如何将数据写入Excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20261034/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write data into an Excel file in C++
提问by Emma
I am programming with Microsoft Visual studio 2012 C++. My professor asks me to put the result of my program into an excel file instead of a text file. Because it is easier for others to understand. Is there some interface I can use in my C++ program to directly generate an excel file and put the data in it?
我正在使用 Microsoft Visual Studio 2012 C++ 进行编程。我的教授要求我将程序的结果放入一个 excel 文件而不是文本文件中。因为别人更容易理解。我的C++程序中是否有一些接口可以直接生成excel文件并将数据放入其中？
采纳答案by Jo?o Augusto
If you don't want to use a third party library or automation, and you need some kind of formatting (colors, fonts, etc...) you can simply create a open office xml. http://en.wikipedia.org/wiki/Microsoft_Office_2003_XML_formats
如果您不想使用第三方库或自动化，并且您需要某种格式（颜色、字体等），您可以简单地创建一个开放式办公室 xml。 http://en.wikipedia.org/wiki/Microsoft_Office_2003_XML_formats
回答by Bathsheba
Would your professor accept a csv file? This is a Character Separated Value; often a comma is used as the delimiter although Excel will accept tab characters too. Excel can load these things automatically and splits the input into separate rows and columns. I suspect that's what the professor is after.
你的教授会接受 csv 文件吗？这是一个字符分隔值；尽管 Excel 也接受制表符，但通常使用逗号作为分隔符。Excel 可以自动加载这些内容并将输入拆分为单独的行和列。我怀疑这就是教授所追求的。
Otherwise, using the Component Object Model (COM) for Excel is another route. But that's far more involved.
否则，使用 Excel 的组件对象模型 (COM) 是另一条途径。但这要复杂得多。
回答by Brecht Sanders
There is a portable C/C++ library called XLSX I/O that can write to .xlsx files, without even having Excel installed. See: https://sourceforge.net/projects/xlsxio/
有一个名为 XLSX I/O 的可移植 C/C++ 库，它可以写入 .xlsx 文件，甚至无需安装 Excel。请参阅：https: //sourceforge.net/projects/xlsxio/
回答by Nicolay77
I have been using this library in Linux:
我一直在 Linux 中使用这个库：
https://github.com/jmcnamara/libxlsxwriter.git
https://github.com/jmcnamara/libxlsxwriter.git
It works well, and of course, it is fast.
它运行良好，当然，速度也很快。
The 'use csv' advice stops working as soon as you need more than one sheet in the file, as it is my case.
一旦您需要文件中的多张纸，“使用 csv”建议就会停止工作，就像我的情况一样。
回答by MSDN.WhiteKnight
On Windows you can use ODBC, which allow working with Excel sheets as regular database tables. It only deals with data though, not formatting, diagrams etc. Standard ODBC driver supports only xls files; to create xlsx file Microsoft Access 2010+ Database Engine Redistributable must be installed.
在 Windows 上，您可以使用ODBC，它允许将 Excel 工作表用作常规数据库表。但它只处理数据，不处理格式、图表等。标准 ODBC 驱动程序仅支持 xls 文件；要创建 xlsx 文件，必须安装 Microsoft Access 2010+ Database Engine Redistributable。
Example:
例子：
#include <stdio.h>
#include <tchar.h>
#include <locale.h>
#include <Windows.h>
#include <sqlext.h>
WCHAR szDSN[] = L"Driver={Microsoft Excel Driver (*.xls)};DSN='';CREATE_DB=\"C:\test\newfile.xls\";DBQ=C:\test\newfile.xls;READONLY=0;";
BOOL ExecuteSql(HDBC hDbc, LPWSTR query){
RETCODE rc;
HSTMT hStmt;
WCHAR bufstate[10]=L"";
WCHAR buferr[1024]=L"";
SQLINTEGER i;
SQLSMALLINT cch;
BOOL result;
wprintf(L">%s\n", query);
/* Prepare SQL query */
rc = SQLAllocStmt(hDbc,&hStmt);
if(!SQL_SUCCEEDED(rc)){
wprintf(L"SQLAllocStmt failed\n");
return FALSE;
}
rc = SQLPrepare(hStmt, query, SQL_NTS);
if(!SQL_SUCCEEDED(rc)){
wprintf(L"SQLPrepare failed\n");
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return FALSE;
}
/* Excecute the query */
rc = SQLExecute(hStmt);
if (SQL_SUCCEEDED(rc)) {
wprintf(L"SQL Success\n");
result = TRUE;
}
else{
SQLGetDiagRec(SQL_HANDLE_STMT,hStmt,1,bufstate,&i,buferr,sizeof(buferr)/sizeof(buferr[0]),&cch);
wprintf(L"SQL Error. Code: %d; Message: %s\n",i,buferr);
result = FALSE;
}
SQLFreeHandle(SQL_HANDLE_STMT,hStmt);
return result;
}
int _tmain(int argc, _TCHAR* argv[])
{
setlocale(LC_ALL,"Russian");
HENV hEnv;
HDBC hDbc;
/* ODBC API return status */
RETCODE rc;
int iConnStrLength2Ptr;
WCHAR szConnStrOut[256];
/* Allocate an environment handle */
rc = SQLAllocEnv(&hEnv);
/* Allocate a connection handle */
rc = SQLAllocConnect(hEnv, &hDbc);
/* Connect to the database */
rc = SQLDriverConnect(hDbc, NULL, (WCHAR*)szDSN,
SQL_NTS, (WCHAR*)szConnStrOut,
255, (SQLSMALLINT*)&iConnStrLength2Ptr, SQL_DRIVER_NOPROMPT);
if (SQL_SUCCEEDED(rc))
{
wprintf(L"Successfully connected to database. Data source name: \n %s\n",
szConnStrOut);
ExecuteSql(hDbc,L"CREATE TABLE [Test] ([Name] TEXT, [Surname] TEXT)");
ExecuteSql(hDbc,L"INSERT INTO [Test] VALUES ('John','Smith')");
}
else
{
wprintf(L"Couldn't connect to %s.\n",szDSN);
}
/* Disconnect and free up allocated handles */
SQLDisconnect(hDbc);
SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
getchar();
return 0;
}
回答by siddse7en
I am late to the party but I tried few of the above solutions and this is what seems to be working well for me. I have taken inspiration from other sites too.
我参加聚会迟到了，但我尝试了一些上述解决方案，这似乎对我来说效果很好。我也从其他网站获得了灵感。
First piece was to use MS Component Object Model (COM). Refer this paper - Excel C++
第一部分是使用 MS 组件对象模型 (COM)。参考这篇论文 - Excel C++
#import "C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\mso.dll" \
rename_namespace("Office2003") \
rename("RGB","RGBmso") \
rename("DocumentProperties","DocumentPropertiesmso")
#import "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.olb" \
rename_namespace("VBE6")
#pragma warning (disable:4278)
#import "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" \
exclude("IFont", "IPicture") \
rename("RGB","RGBxl") \
rename("DialogBox", "DlgBoxxl")
Next create Excel pointers for Excel::_ApplicationPtr, Excel::_WorkbookPtr, and Excel::_WorksheetPtr
接下来为 Excel::_ApplicationPtr、Excel::_WorkbookPtr 和 Excel::_WorksheetPtr 创建 Excel 指针
Initialize your dll using AfxInitExtensionModule()
使用 AfxInitExtensionModule() 初始化您的 dll
Create a pointer to Excel Range - Excel::RangePtr. This is what is used to access data at cell level.
创建指向 Excel 范围的指针 - Excel::RangePtr。这是用于在单元级别访问数据的内容。
Expose APIs to your client code for reading and writing. This is a good guide to do that https://github.com/jmcnamara/libxlsxwriterCSpreadSheet
将 API 公开给您的客户端代码以进行读取和写入。这是一个很好的指南 https://github.com/jmcnamara/libxlsxwriter CSpreadSheet
For performance POV, I was able to write ~33k records in 155 seconds.
对于性能 POV，我能够在 155 秒内写入约 33k 条记录。
回答by rodrigo
Modern EXCEL versions can read a variety of formats, not only the native EXCEL format, and some of them are easier to produce than others.
现代 EXCEL 版本可以读取多种格式，不仅是原生 EXCEL 格式，而且其中一些格式比其他格式更容易制作。
You can produce native EXCEL files using automation, but that is difficult, cumbersome and fragile (sometimes it stops working without a reason).
您可以使用自动化生成原生 EXCEL 文件，但这很困难、繁琐且脆弱（有时它会无故停止工作）。
If you simply want to fill a few cells the easiest way is to write a CSV file.
如果您只是想填充几个单元格，最简单的方法是编写一个 CSV 文件。
If you want more control on the format or layout of your sheets you can use the Excel 2003 XML format. Just create a template of the file as you like, and use it to create your own: it is plain XML, relatively simple to understand.
如果您想要更多地控制工作表的格式或布局，您可以使用 Excel 2003 XML 格式。只需根据您的喜好创建文件模板，然后使用它来创建您自己的模板：它是纯 XML，比较容易理解。
