c++ 读写Excel及数据导入SQLServer

来源：互联网发布：龙腾管家软件下载编辑：程序博客网时间：2024/05/23 13:56

C++操作Excel ,网上的资料还是比较多的，写这篇文章也是分享给初学者一些经验。

本人觉得CSpreadSheet.h这个类封装的还不错。下面我就如何使用这个类介绍一下，

[cpp]

////////////////main.cpp/////////////////////////////////////////////////////

#include <string>

#include<afxdb.h>

#include<odbcinst.h>

#include "CSpreadSheet.h"

using std::string;

#pragma warning(disable:4146)

#pragma warning(disable:4786)

#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF")

//插入到数据库

bool InsertExcel(CString str1,CString str2)

{

try

{

CDatabase m_db;

if (!m_db.IsOpen())

{

m_db.OpenEx("Dsn=MyDatabase;uid=Administrator;trusted_connection=Yes;app=Microsoft? Visual Studio? 2005;wsid=LIYU\SQLEXPRESS;database=MyDdatabase",0);

}

CString sql("insert into Students(myname,age) values('"+ str1+"','"+str2+"')");

m_db.ExecuteSQL(sql);

if(m_db.IsOpen())

{

m_db.Close();

}

return true;

}

catch(_com_error e)

{

string ErrorMessage("数据库连接关闭失败:"),Description,Source;

Description=e.Description();

Source=e.Source();

ErrorMessage+=e.ErrorMessage();

ErrorMessage=ErrorMessage+"\r\n"+Source+"\r\n"+Description;

::MessageBox(NULL,ErrorMessage.c_str(),"错误",MB_OK);

return false;

}

//获取路径

CString GetAddr()

{

CString sFile,sPath;

//获取主程序所在路径,存在sPath中

GetModuleFileName(NULL,sPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH);

sPath.ReleaseBuffer ();

int nPos;

nPos=sPath.ReverseFind ('\\');

sPath=sPath.Left (nPos);

sFile = sPath + "\\Demo.xls";

return sFile;

}

[cpp]

//得到驱动

CString GetExcelDriver()

{

char szBuf[2001];

WORD cbBufMax = 2000;

WORD cbBufOut;

char *pszBuf = szBuf;

CString sDriver;

// 获取已安装驱动的名称(涵数在odbcinst.h里)

if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))

return "";

// 检索已安装的驱动是否有Excel...

{

if (strstr(pszBuf,"Excel") != 0)

{

//发现 !

sDriver = CString(pszBuf);

break;

}

pszBuf = strchr(pszBuf, '\0') + 1;

}

while (pszBuf[1] != '\0');

return sDriver;

}

[cpp]

//读取Excel

void ReadFromExcel()

{

TRY

{

CString str=GetAddr();

if(str.IsEmpty())

::MessageBox(NULL,"无法获取当前路径",NULL,MB_OK);

else

{

CSpreadSheet SS(str,"Students");

CStringArray Rows, Column;

CString strContents = "";

CString sItem[3]={"0"};

for (int i = 2; i < SS.GetTotalRows()+1; i++)

{

// 读取一行

SS.ReadRow(Rows, i);

strContents.Empty();

for (int j = 0; j < Rows.GetSize(); j++)

{

strContents = Rows.GetAt(j);

sItem[j]=strContents;

printf("%s\t",sItem[j]);

}

printf("\n");

if(!InsertExcel(sItem[1],sItem[2]))

{

::MessageBox(NULL,"导入数据出错","错误",MB_OK);

return;

}

CATCH(CDBException, e)

{

// 数据库操作产生异常时...

AfxMessageBox("数据库错误: " + e->m_strError);

}

END_CATCH;

}

//写Excel

void WriteFromExcel(int num,CString str1,CString str2,CString str3)

{

CString path=GetAddr();

if(path.IsEmpty())

::MessageBox(NULL,"获取路径错误",NULL,MB_OK);

else

{

// 新建Excel文件名及路径，TestSheet为内部表名

CSpreadSheet SS(path,"StudentsOut");

CStringArray sampleArray, testRow;

SS.BeginTransaction();

// 加入标题

sampleArray.RemoveAll();

sampleArray.Add("ID");

sampleArray.Add("myname");

sampleArray.Add("age");

SS.AddHeaders(sampleArray);

testRow.Add(str1);

testRow.Add(str2);

testRow.Add(str3);

SS.AddRow(testRow,num,true);

SS.Commit();

}

//查询

bool selectExcel()

{

_ConnectionPtr m_pConnection; //connection object's pointer

_CommandPtr m_pCommand; //command object's pointer

_ParameterPtr m_pParameter; //Parameter object's pointer

_RecordsetPtr m_pRecordset;

HRESULT hr;

try

{

// 创建连接对象

hr=m_pConnection.CreateInstance(__uuidof(Connection));

m_pRecordset.CreateInstance(__uuidof(Recordset));

if(!SUCCEEDED(hr)) return FALSE;

// 连接数据库

m_pConnection->ConnectionString="File Name=LinkDatabase.udl";

m_pConnection->ConnectionTimeout=20;//等待连接的时间为20s

hr=m_pConnection->Open("","","",adModeUnknown);

if(!SUCCEEDED(hr)) return FALSE;

// 查询数据库

_variant_t RecordsAffected;

std::string sql= "select * from Students";

char * str=(char*)sql.c_str();

m_pRecordset=m_pConnection->Execute(str,&RecordsAffected,adCmdText);

//m_pRecordset-> Open(str, _variant_t((IDispatch *)m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);

int i=2;

// 遍历查询结果

while (!m_pRecordset->adoEOF)

{

printf("%s\t",(char*)(_bstr_t)m_pRecordset->GetCollect("ID"));

printf("%s\t",(char*)(_bstr_t)m_pRecordset->GetCollect("myname"));

printf("%s\n",(char*)(_bstr_t)m_pRecordset->GetCollect("age"));

CString str1=(CString)m_pRecordset->GetCollect("ID");

CString str2=(CString)m_pRecordset->GetCollect("myname");

CString str3=(CString)m_pRecordset->GetCollect("age");

WriteFromExcel(i++,str1,str2,str3);

m_pRecordset->MoveNext();

}

m_pRecordset->Close();

// 关闭数据库连接

if(m_pConnection!=NULL)

{

m_pConnection->Close();

m_pConnection = NULL ;

}

catch(_com_error e)

{

string ErrorMessage("数据库连接关闭失败:"),Description,Source;

Description=e.Description();

Source=e.Source();

ErrorMessage+=e.ErrorMessage();

ErrorMessage=ErrorMessage+"\r\n"+Source+"\r\n"+Description;

::MessageBox(NULL,ErrorMessage.c_str(),"错误",MB_OK);

return FALSE;

}

int main()

{

CoInitialize(NULL);

// ReadFromExcel();//读取Excel到数据库

selectExcel();

CoUninitialize( );

system("pause");

return 0;

}

////////////////mian.cpp////////////////////////////////////////////////

这段代码对我起了参考性的作用，解决了我困惑了3天的问题，找到了Excel和SQL操作的方向。配合CSpreadSheet的帮助基本对EXCEL操作没有问题。

0 0