odbc写excel

最新推荐文章于 2018-11-06 18:31:41 发布

ctmgqj8805

最新推荐文章于 2018-11-06 18:31:41 发布

阅读量97

点赞数

#pragma once
#include
#include

class __declspec(dllexport) CRWExcel
{
public:
CRWExcel(void);
CRWExcel(CString File, CString SheetOrSeparator, bool Backup = true);
CString getExcelDriver();
BOOL open(const CString &dsn);
bool readRows(CString &sql,CStringArray &RowValues);
bool readTitles(CStringArray &RowValues);
bool executeSQL(CString &sql);
public:
~CRWExcel(void);
private:
CDatabase *m_pDatabase;
CRecordset *m_pRecordset;
CString m_sDsn;
CString m_sSheetName;// = SheetOrSeparator;
CString m_sFile;
CString m_sSql;

int m_dTotalColumns;//total columns
};

#include "StdAfx.h"
#include "RWExcel.h"
#include
using namespace std;
CRWExcel::CRWExcel(CString sFile, CString SheetOrSeparator, bool Backup /* = true */)
:m_sFile(sFile),m_pRecordset(NULL)
{
m_pDatabase = new CDatabase;

m_sDsn.Format(L"DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", getExcelDriver(), sFile, sFile);
if(!open(m_sDsn))
{
  AfxMessageBox(L"打開失敗");
  m_pDatabase->Close();
  m_pDatabase = NULL;
}
if (m_pDatabase)
{
  m_pRecordset = new CRecordset(m_pDatabase);
}
}

CRWExcel::CRWExcel(void)
{
}

CRWExcel::~CRWExcel(void)
{
if (m_pRecordset != NULL)
{
  m_pRecordset->Close();
  delete m_pRecordset;
  m_pRecordset = NULL;
}
if (m_pDatabase != NULL)
{
  m_pDatabase->Close();
  delete m_pDatabase;
  m_pDatabase = NULL;
}
}
CString CRWExcel::getExcelDriver()
{
wchar_t szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
wchar_t *pszBuf = szBuf;
CString sDirver;
// Get the names of the installed drivers ("odbcinst.h" has to be included )
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
{
  sDirver = L"";
}

// Search for the driver...
do
{
  if( wcsstr( pszBuf, L"Excel" ) != 0 )
  {
   // Found !
   sDirver= CString( pszBuf );
   break;
  }
  pszBuf = wcschr( pszBuf, '\0' ) + 1;
}
while( pszBuf[1] != '\0' );
return sDirver;
}

BOOL CRWExcel::open(const CString &dsn)
{
return m_pDatabase->OpenEx(dsn, CDatabase::noOdbcDialog);
}
bool CRWExcel::executeSQL(CString &sql)
{
try
{
  // 能夠後退 [3/12/2009 f3219162]
  // pRecordset->Open(CRecordset::snapshot, sql, CRecordset::readOnly);
  // 不能夠后退
  //pRecordset->Open(CRecordset::forwardOnly, sql, CRecordset::readOnly);
  m_pRecordset->Open(CRecordset::snapshot, sql, CRecordset::readOnly);
}
catch(...)
{
  return false;
}
return true;
}
bool CRWExcel::readRows(CString &sql,CStringArray &RowValues)
{
ASSERT(m_pDatabase!=NULL);
ASSERT(m_pRecordset!=NULL);
//- 得到列數
int nColumns =m_pRecordset->m_nResultCols;
while (!m_pRecordset->IsEOF())
{

  wstring wstr(L"");
  for (int i=0;i  {
   CString sAge1;
   m_pRecordset->GetFieldValue(i,sAge1);
   wstr +=L" ";
   wstr +=sAge1.GetBuffer();
   wstr +=L" ";
  }
  CString sAge(wstr.c_str());
  RowValues.Add(sAge);
  m_pRecordset->MoveNext();
}
m_pRecordset->MoveFirst();
return true;
}
bool CRWExcel::readTitles(CStringArray &RowValues)
{
ASSERT(m_pDatabase!=NULL);
ASSERT(m_pRecordset!=NULL);
int nColumns =m_pRecordset->m_nResultCols;
if(m_pRecordset->IsEOF())
{
  return false;
}
wstring wstr(L"");
for (int i=0;i {
  CString sAge1;
  m_pRecordset->GetFieldValue(i,sAge1);
  wstr +=L" ";
  wstr += m_pRecordset->m_rgODBCFieldInfos[i].m_strName;
  wstr +=L" ";
}
CString sAge(wstr.c_str());
RowValues.Add(sAge);
return true;
}