最近项目需要根据不同的数据自动生成excel表格展示给客户看,我采用的是先用excel生成一个带格式的excel表格模板,然后每一次只需要复制这个模板到指定位置,然后更新里面的数据。这样让复杂度降低了一大半,不需要考虑格式(字体加粗、对齐方式、表格边框等等)的问题,只要在模板里把格式定好就行。
先汇总一下遇到的问题,解决方法:
- 不能按照处理数据的方式操作excel第一行。操作excel文件时,记录指针无法移动到excel表格第一行,因为ado把表格第一行认为是字段名,从第二行开始认为是数据。最简单解决方法:把表格模板第一行空下,如果能接受的话。。。这样表格中所有的内容都可以当做数据,用记录指针处理。
- 用Move()方法移动记录指针时,结果不是自己想要的位置。原因:如果上一次操作完成了,没有把记录指针移动到起始位置并保存更新,记录指针会从上一次指针位置开始移动。解决方法:每次更新数据前加一句pRec->MoveFirst(); ,然后再更新数据:pRec->Update();
pRec->MoveFirst(); pRec->Update();
- 待续...
记录一些步骤和知识点:
首先我们生成一个自己想要格式的excel模板。以后操作这个模板就行,方便了太多太多。例如:
这个模板需要第一行空着,原因上文说过。
接下来就是利用ado操作excel文件,填入数据。直接上代码吧。
导入ado环境:
#import "C:/Program Files/Common Files/System/ado/msado15.dll" no_namespace rename("EOF","adoEOF")
先上异常处理机制,下文每一步都要在try里操作
try
{
...
}
catch (_com_error& e)
{
printf(_T("Error:%s\n"), e.ErrorMessage());
printf(_T("Desc:%s\n"), (char*)(e.Description()));
}
ado初始化:
CoInitialize(NULL);
创建一个连接字符串(针对xls):
_ConnectionPtr pCon;
CString ConnectionString;
//打开excel
/*"HDR=Yes;" 表示工作表的第一行是表头,没有数据。 "HDR=No;"与之相反。
"IMEX=1;"告诉驱动程序始终将"intermixed"数据类型(numbers, dates, strings等等)作为文本型读取。
注意:该选项可能引起Excel工作表写权限的修改。如果想写入数据,创建新表等必须使其为0*/
ConnectionString = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
ConnectionString += "D:\\测试excel\\demo.xls"; //excel file name
ConnectionString += _T(";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"");
BSTR resultsString = ConnectionString.AllocSysString();
pCon.CreateInstance(__uuidof(Connection));
pCon->Open(resultsString, "", "", NULL);
假如是.xls,那么连接字符串:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=data.xlsx;Extended Properties="Excel 12.0 Xml"(没试过,参考自:https://www.cnblogs.com/lkpp/p/ReadandWriteExcelFilesCppviaADO.html)。
下面这步本文不需要!本文直接在模板里已经弄好表格字段名等。如果有需求可以看看。创建表格、字段名、字段类型。
//ALTER TABLE test ADD COLUMN hello TEXT
/*_CommandPtr pCmd;
TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->ActiveConnection = pCon;
pCmd->CommandText = "CREATE TABLE Sheet1(A int, B varchar, C int, D int, E int, F int, G int, H int, I int, J varchar)";
pCmd->Execute(NULL, NULL, adCmdText);*/
/*_CommandPtr pCmd;
TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->ActiveConnection = pCon;
pCmd->CommandText = "ALTER TABLE [Sheet4$] ADD asd varchar(10)";
pCmd->Execute(NULL, NULL, adCmdText);*/
创建Recordset并填入数据。
_RecordsetPtr pRec;
pRec.CreateInstance(__uuidof(Recordset));
pRec->Open("SELECT * FROM [Sheet1$]", _variant_t((IDispatch*)pCon),
adOpenDynamic, adLockOptimistic, adCmdText);
update(pRec, 0, 0, "表1 山西省大同市植被覆盖度统计表");
update(pRec, 1, 0, "2000年");
update(pRec, 3, 2, 123.45);
update(pRec, 3, 3, 100.45);
update(pRec, 3, 4, 20.45);
update(pRec, 3, 5, 123.45+100.45+20.45);
void update(_RecordsetPtr pRec, int row, int column, string value)
{
pRec->MoveFirst(); pRec->Update();
pRec->Move(row);
_variant_t t = _variant_t(long(column));
pRec->PutCollect(&t, _variant_t(value.c_str()));
pRec->MoveFirst(); pRec->Update();
}
void update(_RecordsetPtr pRec, int row, int column, double value)
{
value = int((value + 0.005)*100.00) / 100.00;
pRec->MoveFirst(); pRec->Update();
pRec->Move(row);
_variant_t t = _variant_t(long(column));
pRec->PutCollect(&t, _variant_t(value));
pRec->MoveFirst(); pRec->Update();
}
void update(_RecordsetPtr pRec, int row, int column, int value)
{
pRec->MoveFirst(); pRec->Update();
pRec->Move(row);
_variant_t t = _variant_t(long(column));
pRec->PutCollect(&t, _variant_t(value));
pRec->MoveFirst(); pRec->Update();
}
这里参数adOpenDynamic网上的一些解释:
adopenkeyset是静态的cursor,即使有其他程式新增或删除了记录,cursor依然不会被更新,程式无法看到其它程式所新增或删除的记录; adopendynamic是动态的cursor,如果有其他程式新增或删除了的记录,可以反映到cursor之中,程式可以动态看到其他程式新增及删除的记录。例如:多人共用数据库时...
函数update是我自己编写的更新字段函数,参数pRec, row, column, value分别代表_RecordsetPtr,要更新的字段行数,要更新的字段列数,要更新的数据值。这里也可以根据字段所在列的名字更新数据,就不介绍了。
关闭连接:
pRec->Close();
pCon->Close();
结果:
贴上查询excel数据的代码,本文没用到,也没深入研究。
/*pRec.CreateInstance(__uuidof(Recordset));
pRec->Open("SELECT * FROM [MySheet$]", _variant_t((IDispatch*)pCon),
adOpenStatic, adLockOptimistic, adCmdText);
while (!pRec->adoEOF)
{
for (long i = 0; i < pRec->Fields->GetCount(); ++i)
{
if (i > 0) cout << ";";
_variant_t v = pRec->Fields->GetItem(i)->Value;
if (v.vt == VT_R8)
cout << v.dblVal;
if (v.vt == VT_BSTR)
cout << (char*)(_bstr_t)v.bstrVal;
}
cout << std::endl;
pRec->MoveNext();
}*/
小的总结:ado是把表格文件当做数据库表来处理的,数据库能干的这里都能干,但是excel表格一些操作它不太方便干,比如对格式的操作,加粗对齐字体颜色等等。以后对excel操作可以考虑选取别的方法。