c++ 操作 xlsx

操作xlsx的免费的C++类库很少。收费的有libxl

免费只找到一个并试验成功编译并执行创建xlsx的免费类库libopc。（注意我这里不考虑他的开源协议，我开发的不是商业软件）

官网：https://libopc.codeplex.com/

编译过程：

1.到他的官网上下载源码

最新版libopc 0.0.3， libopc-20526

2.解压源码后发现，他是用了第三方的库

libxml2:xml gnome的开源项目 版本 2.7.2

zlib:压缩解压 版本 1.2.5

版本都比较旧

更新libxml2: 2.9.2

更新zlib:1.28.

zlib更新比较容易，没有遇到麻烦。但是通过警告信息学习到点历史知识。

int ZEXPORT inflate(strm, flush) 
z_streamp strm;
int flush;

{

... 函数体

}

函数居然可以这么写，从十多年前的大学教材谭浩强的C语言至今第一次发现。编译器给出警告。涨知识了。

libxml2：2.9.2 需要libiconv的支持（默认）。

编译的时候遇到的问题：

1.使用libopc的xml的项目文件原封不动的编译，能通过。由于是lib可能没有连接，因此编写测试程序时出现好几个未定义的标识符。

其中有一个 _xmlBufContent...

2.参照libxml2 2.9.2 里面的vs2010的项目文件同步到xml的项目文件编译ok，最后编写测试程序也不出现错误。

3. libiconv，在他的官网上下载不能使用visul c++编译

https://codeload.github.com/winlibs/libiconv/zip/master，这是第三方制作的可以被visual C++编译，包括最新版的2013

4.将libiconv添加到libopc项目方案中。进行编译即可。

5.有的个别文件提示，需要将此文件保存成UNICODE否则内容丢失的警告的处理。

首先，Ctrl+A全选，编辑-高级-设置选定内容的格式

然后, 查找 \n 替换成 \n， 注意查找对话框中要将查找选项中的使用正则表达式勾选上。完成替换。

网上说这样就可以了。但是我重新编译仍然出现警告信息。

使用nodepadd++,出现提示文件。编码为UTF8 无格式。全选，剪切，设置为UTF8编码，粘贴后保存后重新编译警告信息消失。

6.使用libiconv转换编码遇到的问题

iconv_t conveter=iconv_open("UTF-8","GBK"); //GB2312 GBK  //使用那个都可以
char szInstr[32] = {"中国"};
char szOutStr[128] = {0};
size_t in = strlen(szInstr); //参数需要字节为单位的字符串长度。这里不用修改
size_t ol(128);  //注意此处必须赋值为目标缓冲区的长度，他的值会减小标识剩余缓冲区的长度。由于这个问题好几次转换失败，最后调试才搞明白。
const char* sz1 = &szInstr[0];
char* sz2 = &szOutStr[0];
iconv(conveter,(const char **)&sz1,(size_t *)&in,(char **)&sz2,(size_t *)&ol);
 iconv_close(conveter);

char szOutStr[128] = {0};
::WideCharToMultiByte(CP_UTF8, 0, L"中国", -1, szOutStr, 128, NULL, NULL);

贴出使用windows函数的转换函数，要是char*的"中国"得转换两边才可以。先转WideChar然后MultiByte

7.创建xlsx的代码(改写http://wp.libpf.com/?p=670),下面的代码是测试代码，不能用在实际代码中。

#include "stdafx.h"
#include <string>

using namespace std;

#include <opc/opc.h>

#pragma comment(lib, "zlib.lib")
#pragma comment(lib, "xml.lib")
#pragma comment(lib, "plib.lib")
#pragma comment(lib, "mce.lib")
#pragma comment(lib, "opc.lib")
#pragma comment(lib, "libiconv.lib")
#pragma comment(lib, "Ws2_32.lib")   //由于libxml2(xml)支持网络因此需要加入它否则提示连接错误

//将列序号转换为名称，例如1->A
std::string intToColumn(int k) {
  std::string s;
  int p;
  while (k > 0) {
    p = (k - 1) % 26;
    k = (k - p) / 26;
    s.push_back(65 + p);
  }
  std::reverse(s.begin(), s.end());
  return s;
} // intToColumn


void writef(opcContainerOutputStream* stream, const char *s, ...)
{
va_list ap;
va_start(ap, s);
char buf[1024];
int len = vsnprintf_s(buf, sizeof(buf), s, ap);
opcContainerWriteOutputStream(stream, (const opc_uint8_t *)buf, len);
va_end(ap);
}


void writes(opcContainerOutputStream* stream, const char *s)
{
int const len = strlen(s);
opcContainerWriteOutputStream(stream, (const opc_uint8_t *)s, len);
}


opcPart create_docProps_app_xml(opcContainer* c)
{
opcPart ret = opcPartFind(c, _X("docProps/app.xml"), _X("application/vnd.openxmlformats-officedocument.extended-properties+xml"), 0);
if (OPC_PART_INVALID == ret && OPC_PART_INVALID != (ret = opcPartCreate(c, _X("docProps/app.xml"), _X("application/vnd.openxmlformats-officedocument.extended-properties+xml"), 0)))
{
//adding content
opcContainerOutputStream *out = opcContainerCreateOutputStream(c, ret, OPC_COMPRESSIONOPTION_NORMAL);
if (NULL != out)
{
writes(out, "<Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\" xmlns:vt=\"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes\">\n");
writes(out, "  <Application>Performance Table</Application>\n");
writes(out, "  <AppVersion>00.0001</AppVersion>\n");
writes(out, "  <HeadingPairs>\n");
writes(out, "    <vt:vector size=\"2\" baseType=\"variant\">\n");
writes(out, "      <vt:variant>\n");
writes(out, "        <vt:lpstr>Worksheets</vt:lpstr>\n");
writes(out, "      </vt:variant>\n");
writes(out, "      <vt:variant>\n");
writes(out, "  <vt:i4>4</vt:i4>\n");
writes(out, "      </vt:variant>\n");
writes(out, "    </vt:vector>\n");
writes(out, "  </HeadingPairs>\n");
writes(out, "  <TitlesOfParts>\n");
writes(out, "    <vt:vector size=\"4\" baseType=\"lpstr\">\n");
writes(out, "      <vt:lpstr>sheet1</vt:lpstr>\n");
writes(out, "      <vt:lpstr>sheet2</vt:lpstr>\n");
writes(out, "      <vt:lpstr>sheet3</vt:lpstr>\n");
writes(out, "      <vt:lpstr>sheet4</vt:lpstr>\n");
writes(out, "    </vt:vector>\n");
writes(out, "  </TitlesOfParts>\n");
writes(out, "  <Company>Test S.p.A.</Company>\n");
writes(out, "</Properties>\n");
writes(out, "");
opcContainerCloseOutputStream(out);
}
// adding relations
}
return ret;
} // Xlsx::create_docProps_app_xml


opcPart create_docProps_core_xml(opcContainer* c)
{
opcPart ret = opcPartFind(c, _X("docProps/core.xml"), _X("application/vnd.openxmlformats-package.core-properties+xml"), 0);
if (OPC_PART_INVALID == ret && OPC_PART_INVALID != (ret = opcPartCreate(c, _X("docProps/core.xml"), _X("application/vnd.openxmlformats-package.core-properties+xml"), 0)))
{
//adding content
opcContainerOutputStream *out = opcContainerCreateOutputStream(c, ret, OPC_COMPRESSIONOPTION_NORMAL);
if (NULL != out)
{
writes(out, "<cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">\n");
writes(out, "  <dc:title>Financial Plan</dc:title>\n");
writes(out, "  <dc:subject>Test title</dc:subject>\n");
writes(out, "  <dc:creator>Test Author</dc:creator>\n");
writes(out, "  <cp:lastModifiedBy>Test Author</cp:lastModifiedBy>\n");
writes(out, "  <dcterms:created xsi:type=\"dcterms:W3CDTF\">2015-08-07T07:05:47Z</dcterms:created>\n");
writes(out, "  <dcterms:modified xsi:type=\"dcterms:W3CDTF\">2015-08-07T07:05:47Z</dcterms:modified>\n");
writes(out, "</cp:coreProperties>\n");
writes(out, "");
opcContainerCloseOutputStream(out);
}
// adding relations
}
return ret;
} // Xlsx::create_docProps_core_xml


opcPart create_xl_styles_xml(opcContainer* c)
{
opcPart ret = opcPartFind(c, _X("xl/styles.xml"), _X("application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"), 0);
if (OPC_PART_INVALID == ret && OPC_PART_INVALID != (ret = opcPartCreate(c, _X("xl/styles.xml"), _X("application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"), 0)))
{
//adding content
opcContainerOutputStream *out = opcContainerCreateOutputStream(c, ret, OPC_COMPRESSIONOPTION_NORMAL);
if (NULL != out)
{
writes(out, "<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" xmlns:x14ac=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac\" mc:Ignorable=\"x14ac\">\n");
writes(out, "  <numFmts count=\"1\">\n");
writes(out, "    <numFmt numFmtId=\"164\" formatCode=\"yyyy/mm/dd\"/>\n");
writes(out, "  </numFmts>\n");
writes(out, "  <fonts count=\"2\" x14ac:knownFonts=\"1\">\n");
writes(out, "    <font>\n");
writes(out, "      <sz val=\"11\"/>\n");
writes(out, "      <color theme=\"1\"/>\n");
writes(out, "      <name val=\"Calibri\"/>\n");
writes(out, "      <family val=\"2\"/>\n");
writes(out, "      <scheme val=\"minor\"/>\n");
writes(out, "    </font>\n");
writes(out, "    <font>\n");
writes(out, "      <b/>\n");
writes(out, "      <sz val=\"11\"/>\n");
writes(out, "      <color theme=\"1\"/>\n");
writes(out, "      <name val=\"Calibri\"/>\n");
writes(out, "      <family val=\"2\"/>\n");
writes(out, "      <scheme val=\"minor\"/>\n");
writes(out, "    </font>\n");
writes(out, "  </fonts>\n");
writes(out, "  <fills count=\"2\">\n");
writes(out, "    <fill>\n");
writes(out, "      <patternFill patternType=\"none\"/>\n");
writes(out, "    </fill>\n");
writes(out, "    <fill>\n");
writes(out, "      <patternFill patternType=\"gray125\"/>\n");
writes(out, "    </fill>\n");
writes(out, "  </fills>\n");
writes(out, "  <borders count=\"1\">\n");
writes(out, "    <border>\n");
writes(out, "      <left/>\n");
writes(out, "      <right/>\n");
writes(out, "      <top/>\n");
writes(out, "      <bottom/>\n");
writes(out, "      <diagonal/>\n");
writes(out, "    </border>\n");
writes(out, "  </borders>\n");
writes(out, "  <cellStyleXfs count=\"1\">\n");
writes(out, "    <xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/>\n");
writes(out, "  </cellStyleXfs>\n");
writes(out, "  <cellXfs count=\"5\">\n");
writes(out, "    <xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/>\n");
writes(out, "    <xf numFmtId=\"0\" fontId=\"1\" fillId=\"0\" borderId=\"0\" xfId=\"0\" applyFont=\"1\"/>\n");
writes(out, "    <xf numFmtId=\"164\" fontId=\"1\" fillId=\"0\" borderId=\"0\" xfId=\"0\" applyNumberFormat=\"1\" applyFont=\"1\"/>\n");
writes(out, "    <xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\" applyFont=\"1\"/>\n");
writes(out, "    <xf numFmtId=\"2\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\" applyNumberFormat=\"1\" applyFont=\"1\"/>\n");
writes(out, "  </cellXfs>\n");
writes(out, "  <cellStyles count=\"1\">\n");
writes(out, "    <cellStyle name=\"Normal\" xfId=\"0\" builtinId=\"0\"/>\n");
writes(out, "  </cellStyles>\n");
writes(out, "  <dxfs count=\"0\"/>\n");
writes(out, "  <tableStyles count=\"0\" defaultTableStyle=\"TableStyleMedium2\" defaultPivotStyle=\"PivotStyleLight16\"/>\n");
writes(out, "  <extLst>\n");
writes(out, "    <ext xmlns:x14=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main\" uri=\"{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}\">\n");
writes(out, "      <x14:slicerStyles defaultSlicerStyle=\"SlicerStyleLight1\"/>\n");
writes(out, "    </ext>\n");
writes(out, "    <ext xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" uri=\"{9260A510-F301-46a8-8635-F512D64BE5F5}\">\n");
writes(out, "      <x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\"/>\n");
writes(out, "    </ext>\n");
writes(out, "  </extLst>\n");
writes(out, "</styleSheet>\n");
writes(out, "");
opcContainerCloseOutputStream(out);
}
// adding relations
}
return ret;
} // Xlsx::create_xl_styles_xml


opcPart create_xl_worksheets_sheet_xml(opcContainer* c, int i)
{
char temppath[1024];


//vsnprintf(temparray, _countof(temparray), "xl/worksheets/sheet%d.xml", i);


sprintf_s(temppath,"xl/worksheets/sheet%d.xml", i); 


opcPart ret = opcPartFind(c, _X(temppath), _X("application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"), 0);
if (OPC_PART_INVALID == ret && OPC_PART_INVALID != (ret = opcPartCreate(c, _X(temppath), _X("application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"), 0)))
{
//adding content
opcContainerOutputStream *out = opcContainerCreateOutputStream(c, ret, OPC_COMPRESSIONOPTION_NORMAL);
if (NULL != out) {
writes(out, "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" xmlns:x14ac=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac\" mc:Ignorable=\"x14ac\">\n");


writes(out, "  <sheetViews>\n");
if (i == 1) {
writes(out, "    <sheetView tabSelected=\"1\" workbookViewId=\"0\">\n");
} else {
writes(out, "    <sheetView tabSelected=\"0\" workbookViewId=\"0\">\n");
}

writes(out, "    </sheetView>\n");
writes(out, "  </sheetViews>\n");


writes(out, "  <cols>\n");
writef(out,"    <col min=\"%d\" max=\"%d\" width=\"%d\" customWidth=\"1\"/>\n", 68, 168, 88); 
writes(out, "  </cols>\n");


writes(out, "  <sheetData>\n");


writef(out, "    <row r=\"%d\" spans=\"1:3\">\n", 2);

//这是用excel 2010创建后查看到的内容
//<c r="A1"> A1单元格 常规 默认
//<v>301415926</v>
//</c>
//<c r="B1" s="1"> B1 单元格 数值 小数位数 7位
//<v>3.1415926000000001</v>
//</c>
//<c r="C1" s="2"> C1 单元格 货币
//<v>12.5</v>
//</c>
//<c r="D1" s="3"> D1 单元格 会计专用
//<v>13.5</v>
//</c>
//<c r="E1" s="4"> E1 单元格 日期
//<v>42217</v>
//</c>
//<c r="F1" s="5"> F1 单元格 时间
//<v>0.60663194444444446</v>
//</c>
//<c r="G1" s="6"> G1 单元格 百分比
//<v>0.45</v>
//</c>
//<c r="H1" s="7"> H1 单元格 分数
//<v>0.25</v>
//</c>
//<c r="I1" s="8"> I1 单元格 科学记数
//<v>10000</v>
//</c>
//<c r="J1" s="9" t="s"> J1 单元格 文本 保存到sharedStrings.xml文件中。
//<v>0</v>
//</c>
//<c r="K1" s="10"> K1 单元格 特殊
//<v>130021</v>
//</c>
//<c r="L1" s="9" t="s"> L1 单元格 自定义 @
//<v>1</v>
//</c>


writef(out, "      <c r=\"%s%d\">\n", intToColumn(1).c_str(), 2);
writef(out, "        <v>%d</v>\n", 100);
writes(out, "      </c>\n");

writef(out, "      <c r=\"%s%d\" s=\"1\">\n", intToColumn(2).c_str(), 2);
writef(out, "        <v>3.1415926</v>\n", 100);
writes(out, "      </c>\n");


writef(out, "      <c r=\"%s%d\" s=\"1\" t=\"inlineStr\">\n", intToColumn(3).c_str(), 2); //这是内嵌字符串, 否则一般情况下他单独保存到一个单独的文件中(sharestring.xml)这里保存的是标识


iconv_t conveter=iconv_open("UTF-8","GBK"); //GB2312 GBK
 
char szInstr[32] = {"中国"};
char szOutStr[128] = {0};


size_t in = strlen(szInstr);
size_t ol(128);


const char* sz1 = &szInstr[0];
char* sz2 = &szOutStr[0];
 
iconv(conveter,(const char **)&sz1,(size_t *)&in,(char **)&sz2,(size_t *)&ol);
iconv_close(conveter);

writef(out, "        <is><t>%s</t></is>\n", szOutStr);
writes(out, "      </c>\n");

writes(out, "    </row>\n");


writes(out, "  </sheetData>\n");
writes(out, "</worksheet>\n");
writes(out, "");
opcContainerCloseOutputStream(out);
}
}
return ret;
} // Xlsx::create_xl_worksheets_sheet_xml


opcPart create_xl_workbook_xml(opcContainer* c)
{
opcPart ret = opcPartFind(c, _X("xl/workbook.xml"), _X("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"), 0);
if (OPC_PART_INVALID == ret && OPC_PART_INVALID != (ret = opcPartCreate(c, _X("xl/workbook.xml"), _X("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"), 0)))
{
//adding content
opcContainerOutputStream *out = opcContainerCreateOutputStream(c, ret, OPC_COMPRESSIONOPTION_NORMAL);
if (NULL != out) {
writes(out, "<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" mc:Ignorable=\"x15\">\n");
writes(out, "  <sheets>\n");


writes(out, "    <sheet name=\"sheet1\" sheetId=\"1\" r:id=\"rId1\"/>\n");
writes(out, "    <sheet name=\"sheet2\" sheetId=\"2\" r:id=\"rId2\"/>\n");
writes(out, "    <sheet name=\"sheet3\" sheetId=\"3\" r:id=\"rId3\"/>\n");
writes(out, "    <sheet name=\"sheet4\" sheetId=\"4\" r:id=\"rId4\"/>\n");


writes(out, "  </sheets>\n");
writes(out, "</workbook>\n");
writes(out, "");
opcContainerCloseOutputStream(out);
}

opcRelationAdd(c, ret, _X("rId1"), create_xl_worksheets_sheet_xml(c,1), _X("http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"));
opcRelationAdd(c, ret, _X("rId2"), create_xl_worksheets_sheet_xml(c,2), _X("http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"));
opcRelationAdd(c, ret, _X("rId3"), create_xl_worksheets_sheet_xml(c,3), _X("http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"));
opcRelationAdd(c, ret, _X("rId4"), create_xl_worksheets_sheet_xml(c,4), _X("http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"));


opcRelationAdd(c, ret, _X("rId5"), create_xl_styles_xml(c), _X("http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"));
}
return ret;
} // Xlsx::create_xl_workbook_xml


int _tmain(int argc, _TCHAR* argv[])
{
if(opcInitLibrary() == OPC_ERROR_NONE)
{
opcContainer* c=opcContainerOpen(_X("d:\\Command.xlsx"), OPC_OPEN_WRITE_ONLY, NULL, NULL);
if (nullptr != c)
{
opcExtensionRegister(c, _X("rels"), _X("application/vnd.openxmlformats-package.relationships+xml"));
opcExtensionRegister(c, _X("xml"), _X("application/xml"));
opcRelationAdd(c, OPC_PART_INVALID, _X("rId1"), create_xl_workbook_xml(c), _X("http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"));
opcRelationAdd(c, OPC_PART_INVALID, _X("rId2"), create_docProps_core_xml(c), _X("http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"));
opcRelationAdd(c, OPC_PART_INVALID, _X("rId3"), create_docProps_app_xml(c), _X("http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"));
opcContainerClose(c, OPC_CLOSE_NOW);
}


opcFreeLibrary();
}


return 0;
}

8.环境说明

操作系统 windows 7 旗舰版 64 visual studio 2013 update 2010 sp1

开发环境 visual studio 2012(实际编译环境)

这里给需要的兄弟提个醒，lipopc肯定能用来创建xlsx。这是我成功的经验。

假如你要是完完整整的功能，例如创建、写、读你一点工作都不想做。libopc不适合你，还是使用ole吧，ole也没啥不好，除了需要环境和速度外。

我是专用libopc完成特定的功能，不是xlsx的全部。当然libopc能实现全部需要自己去研究。比方说你不知道如何写数据或者啥格式你可以使用excel 创建一个xlsx解压后看他的样式和数据的xml，你照写即可。