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

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

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

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

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;
}
           

//得到驱动
CString GetExcelDriver()
{
    char szBuf[2001];
    WORD cbBufMax = 2000;
    WORD cbBufOut;
    char *pszBuf = szBuf;
    CString sDriver;

    // 获取已安装驱动的名称(涵数在odbcinst.h里)
    if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
        return "";
    
    // 检索已安装的驱动是否有Excel...
    do
    {
        if (strstr(pszBuf,"Excel") != 0)
        {
            //发现 !
            sDriver = CString(pszBuf);
            break;
        }
        pszBuf = strchr(pszBuf, '\0') + 1;
    }
    while (pszBuf[1] != '\0');

    return sDriver;
}
           

//读取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