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
"+Source+"\r
"+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("
");
				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
",(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
"+Source+"\r
"+Description;
		::MessageBox(NULL,ErrorMessage.c_str(),"  ",MB_OK);
		return   FALSE;   
	}   
}


int main()
{
	CoInitialize(NULL);
//	ReadFromExcel();//  Excel    
	selectExcel();
	CoUninitialize( );
	system("pause");
	return 0;
}
////////////////mian.cpp////////////////////////////////////////////////