3,882
社区成员




#include <iostream>
#include <sstream>
#include <fstream>
#include <time.h>
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "adoEOF") rename("BOF", "adoBOF")
const int writeRows = 50;
inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
std::string makeConnStr(std::string filename, bool header = true)
{
std::stringstream stream;
std::string hdr = header ? "YES" : "NO";
if(!filename.empty())
if(*--filename.end() == 'x')
stream << "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" << filename << ";Extended Properties=\"Excel 12.0 Xml;HDR=" << hdr << "\"";
else
stream << "Provider='Microsoft.JET.OLEDB.4.0';Data Source=" << filename << ";Extended Properties=\"Excel 8.0;HDR=" << hdr << "\"";
return stream.str();
}
void write(std::string filename)
{
if(FAILED(::CoInitialize(NULL))) return;
_ConnectionPtr pCon = NULL;
_CommandPtr pCmd = NULL;
_RecordsetPtr pRec = NULL;
try
{
_bstr_t connStr(makeConnStr(filename).c_str());
TESTHR(pCon.CreateInstance(__uuidof(Connection)));
TESTHR(pCon->Open(connStr, "", "", NULL));
TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->ActiveConnection = pCon;
pCmd->CommandText = "CREATE TABLE MySheet(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);
TESTHR(pRec.CreateInstance(__uuidof(Recordset)));
pRec->Open("SELECT * FROM MySheet", _variant_t((IDispatch*)pCon), adOpenKeyset, adLockOptimistic, adCmdText);
for(int i = 0; i < writeRows; ++i)
{
TESTHR(pRec->AddNew());
char str[11] = {0}; for(int j = 0; j < 10; ++j) str[j] = 'a' + (rand() % 26);
char mytest[] = "abcdefghigklmnopqrstuvwxyz1abcdefghigklmnopqrstuvwxyz2abcdefghigklmnopqrstuvwxyz3abcdefghigklmnopqrstuvwxyz4abcdefghigklmnopqrstuvwxyz5abcdefghigklmnopqrstuvwxyz6abcdefghigklmnopqrstuvwxyz7abcdefghigklmnopqrstuvwxyz8abcdefghigklmnopqrstuvwxyz9abcdefghigklmnopqrstuvwxyz10abcdefghigklmnopqrstuvwxyz11abcdefghigklmnopqrstuvwxyz12";
pRec->Fields->GetItem("A")->Value = _variant_t(i);
pRec->Fields->GetItem("B")->Value = _variant_t(str);
pRec->Fields->GetItem("C")->Value = _variant_t(i);
pRec->Fields->GetItem("D")->Value = _variant_t(i);
pRec->Fields->GetItem("E")->Value = _variant_t(i);
pRec->Fields->GetItem("F")->Value = _variant_t(i);
pRec->Fields->GetItem("G")->Value = _variant_t(i);
pRec->Fields->GetItem("H")->Value = _variant_t(i);
pRec->Fields->GetItem("I")->Value = _variant_t(i);
pRec->Fields->GetItem("J")->Value = _variant_t(str);
//pRec->Fields->GetItem("J")->Value = _variant_t(mytest);
}
TESTHR(pRec->Update());
TESTHR(pRec->Close());
}
catch(_com_error &e)
{
_bstr_t bstrDescription(e.Description());
CharToOem(bstrDescription, bstrDescription);
std::cout << bstrDescription << std::endl;
}
if(pCon != 0 && pCon->State == adStateOpen) pCon->Close();
::CoUninitialize();
}
int main()
{
write("data.xls"); // writing data.xls
system("pause");
}
_RecordsetPtr m_pRecordset;
char mytest[] = "abcdefghigklmnopqrstuvwxyz1abcdefghigklmnopqrstuvwxyz2abcdefghigklmnopqrstuvwxyz3abcdefghigklmnopqrstuvwxyz4abcdefghigklmnopqrstuvwxyz5abcdefghigklmnopqrstuvwxyz6abcdefghigklmnopqrstuvwxyz7abcdefghigklmnopqrstuvwxyz8abcdefghigklmnopqrstuvwxyz9abcdefghigklmnopqrstuvwxyz10abcdefghigklmnopqrstuvwxyz11abcdefghigklmnopqrstuvwxyz12";
try {
m_pConnection->Execute("CREATE TABLE test1111 ([0] MEMO,[1] MEMO,[2] CURRENCY,[3] MEMO,[4] MEMO,[5] MEMO,[6] CURRENCY)",NULL,adCmdText);
ConnectDB();
m_pRecordset.CreateInstance(__uuidof(Recordset));
m_pRecordset->Open("[test1111$]",m_pConnection.GetInterfacePtr(), // 获取库接库的IDispatch指针
adOpenStatic,//adOpenDynamic,adOpenKeyset
adLockPessimistic,
adCmdTable);
for (int i=0;i<10;i++)
{
m_pRecordset->AddNew();
m_pRecordset->Fields->GetItem("0")->Value = mytest;
}
for(int i = 0; i < writeRows; ++i)
{
TESTHR(pRec->AddNew());
char str[11] = {0}; for(int j = 0; j < 10; ++j) str[j] = 'a' + (rand() % 26);
char mytest[] = "abcdefghigklmnopqrstuvwxyz1abcdefghigklmnopqrstuvwxyz2abcdefghigklmnopqrstuvwxyz3abcdefghigklmnopqrstuvwxyz4abcdefghigklmnopqrstuvwxyz5abcdefghigklmnopqrstuvwxyz6abcdefghigklmnopqrstuvwxyz7abcdefghigklmnopqrstuvwxyz8abcdefghigklmnopqrstuvwxyz9abcdefghigklmnopqrstuvwxyz10abcdefghigklmnopqrstuvwxyz11abcdefghigklmnopqrstuvwxyz12";
pRec->Fields->GetItem("A")->Value = _variant_t(i);
pRec->Fields->GetItem("B")->Value = _variant_t(str);
pRec->Fields->GetItem("C")->Value = _variant_t(i);
pRec->Fields->GetItem("D")->Value = _variant_t(i);
pRec->Fields->GetItem("E")->Value = _variant_t(i);
pRec->Fields->GetItem("F")->Value = _variant_t(i);
pRec->Fields->GetItem("G")->Value = _variant_t(i);
pRec->Fields->GetItem("H")->Value = _variant_t(i);
pRec->Fields->GetItem("I")->Value = _variant_t(i);
// pRec->Fields->GetItem("J")->Value = _variant_t(str);
pRec->Fields->GetItem("J")->Value = _variant_t(mytest);
}