#include "stdafx.h"
#include "odbcexcel.h"
/*************************************************************************************************
2009-11-2 修改了整型数据显示不一致的问题,如5454,读取时是5454.0,在excel表格显示是5454
**************************************************************************************************/
CString CODBCExcel::GetExcelDriver()
{
TCHAR szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
TCHAR *pszBuf = szBuf;
CString strExcelDriver;
// Get the names of the installed drivers ("odbcinst.h" has to be included )
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
{
strExcelDriver = "";
}
// Search for the driver...
do
{
if( _tcsstr( pszBuf, _T("Excel") ) != 0 )
{
// Found !
strExcelDriver = CString( pszBuf );
break;
}
pszBuf = _tcschr( pszBuf, _T('\0')) + 1;
}
while( pszBuf[1] != '\0' );
return strExcelDriver;
}
//////////////////////////////////////////////////////////////////////////
CODBCExcel::CODBCExcel()
{
}
CODBCExcel::~CODBCExcel()
{
m_db.Close();
for (map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.begin(); iter != m_Sheets.end();)
{
delete iter->second; iter = m_Sheets.erase(iter);
}
}
bool CODBCExcel::Open(const CString& strFileName)
{
if (m_db.IsOpen()) return true;
CString strDSN;
strDSN.Format(_T("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s"),
GetExcelDriver(), strFileName, strFileName);
return m_db.OpenEx(strDSN, CDatabase::noOdbcDialog) == TRUE;
}
bool CODBCExcel::Save()
{
CString strSQL;
for (map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.begin(); iter != m_Sheets.end();++iter)
{
iter->second->Save();
}
return true;
}
void CODBCExcel::Close()
{
m_db.Close();
}
CODBCExcelSheet* CODBCExcel::GetWorkSheet(const CString& strSheetName)
{
CString strSQL;
strSQL.Format(_T("SELECT * FROM [%s$A1:IV65536]"), strSheetName);
CODBCExcelSheet* sheet = 0;
bool bNew = false;
map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.find(strSheetName);
if (iter != m_Sheets.end())
{
sheet = iter->second;
if (CODBCExcelSheet::Add == sheet->m_nFlag || CODBCExcelSheet::Update == sheet->m_nFlag)
return sheet;
}
else
{
sheet = new CODBCExcelSheet(*this, strSheetName);
bNew = true;
}
try
{
sheet->m_recordset.Open(CRecordset::forwardOnly, strSQL, CRecordset::readOnly);
}
catch(...)
{
if (sheet) sheet->m_recordset.Close();
if (!bNew) return sheet;
delete sheet; return 0;
}
sheet->Init();
m_Sheets.insert(make_pair(strSheetName, sheet));
return sheet;
}
CODBCExcelSheet* CODBCExcel::AddWorkSheet(const CString &strSheetName, const CStringArray& ColHeaders)
{
CODBCExcelSheet* sheet = GetWorkSheet(strSheetName);
if (!sheet)
{
sheet = new CODBCExcelSheet(*this, strSheetName);
if (!sheet) return 0;
sheet->m_nFlag = CODBCExcelSheet::Add;
sheet->m_ColHeaders.Copy(ColHeaders);
m_Sheets.insert(make_pair(strSheetName, sheet));
}
else
{
sheet->m_dwRows = 0;
sheet->m_nFlag = CODBCExcelSheet::Update;
sheet->m_ColHeaders.RemoveAll();
sheet->m_ColHeaders.Copy(ColHeaders);
}
sheet->m_dwCols = ColHeaders.GetCount();
return sheet;
}
void CODBCExcel::DeleteWorkSheet(const CString& strSheetName)
{
map<CString,CODBCExcelSheet*>::iterator iter = m_Sheets.find(strSheetName);
if (iter != m_Sheets.end())
{
iter->second->m_nFlag = CODBCExcelSheet::Delete;
iter->second->ResetCells();
}
else
{
CString strSQL;
strSQL.Format (_T("DROP TABLE [%s$A1:IV65536]"), strSheetName);
try
{
m_db.ExecuteSQL(strSQL);
}
catch (CDBException* e)
{
return ;
}
}
}
//////////////////////////////////////////////////////////////////////////
CODBCExcelSheet::CODBCExcelSheet(CODBCExcel& Excel, const CString& strName):
m_strName(strName),
m_Excel(Excel),
m_recordset(&Excel.m_db)
{
m_dwCols = m_dwRows = 0;
}
bool CODBCExcelSheet::Init()
{
DWORD dwCol;
m_ColHeaders.RemoveAll();
for (dwCol = 0; dwCol < m_recordset.m_nResultCols; ++dwCol)
{
m_recordset.m_rgODBCFieldInfos[dwCol].m_strName.Trim();
m_ColHeaders.Add(m_recordset.m_rgODBCFieldInfos[dwCol].m_strName);
}
m_dwCols = m_recordset.m_nResultCols;
m_nFlag = CODBCExcelSheet::Exist;
CString strText;
for (DWORD dwRow = 0;!m_recordset.IsEOF();)
{
try
{
CDBVariant dbVal;
for (dwCol = 0; dwCol < m_recordset.m_nResultCols; ++dwCol)
{
m_recordset.GetFieldValue(dwCol, dbVal);
Cell(dwRow, dwCol)->Set(dbVal);
/*m_recordset.GetFieldValue(dwCol, strText);
Cell(dwRow, dwCol)->Set(strText); */
}
++dwRow; m_recordset.MoveNext();
}
catch (CDBException* e)
{
m_recordset.Close();
return false;
}
}
m_recordset.Close();
UpdateRowCount();
return true;
}
void CODBCExcelSheet::UpdateRowCount()
{
for (;!m_Cells.empty();)
{
vector<CODBCExcelCell>& vecCol = m_Cells.back();
vector<CODBCExcelCell>::iterator iter;
for (iter = vecCol.begin(); iter != vecCol.end(); ++iter)
{
if (!(*iter).m_strVal.IsEmpty()) break;
}
if (iter == vecCol.end())
{
m_Cells.pop_back();
}
else
{
break;
}
}
m_dwRows = m_Cells.size();
}
bool CODBCExcelSheet::Save()
{
CString strSQL;
switch(m_nFlag)
{
case Update:
{
strSQL.Format(_T("DROP TABLE [%s$A1:IV65536]"), m_strName);
try
{
m_Excel.m_db.ExecuteSQL(strSQL);
}
catch (CDBException* e)
{
return false;
}
//$A1:IV65536
strSQL.Format(_T("CREATE TABLE [%s$A1:IV65536] ("), m_strName);
for (int i = 0; i < m_dwCols; ++i)
{
strSQL += _T("[") + m_ColHeaders.GetAt(i) +_T("]") + _T(" char(255), ");
}
strSQL.Delete(strSQL.GetLength()-2, 2);
strSQL += _T(")");
try
{
m_Excel.m_db.ExecuteSQL(strSQL);
}
catch (CDBException* e)
{
return false;
}
UpdateCells();
}
break;
case Add:
{
strSQL.Format(_T("CREATE TABLE [%s] ("), m_strName);
for (int i = 0; i < m_dwCols; ++i)
{
strSQL += _T("[") + m_ColHeaders.GetAt(i) +_T("]") + _T(" char(255), ");
}
strSQL.Delete(strSQL.GetLength()-2, 2);
strSQL += _T(")");
try
{
m_Excel.m_db.ExecuteSQL(strSQL);
}
catch (CDBException* e)
{
return false;
}
UpdateCells();
}
break;
case Delete:
{
strSQL.Format (_T("DROP TABLE [%s$A1:IV65536]"), m_strName);
try
{
m_Excel.m_db.ExecuteSQL(strSQL);
}
catch (CDBException* e)
{
return false;
}
}
break;
}
return true;
}
bool CODBCExcelSheet::UpdateCells()
{
CString strSQL, strSQLCol;
for (DWORD dwRow = 0, dwCol; dwRow < m_dwRows; ++dwRow)
{
strSQL.Format(_T("INSERT INTO [%s$A1:IV%d] ("), m_strName, dwRow+1);
for (dwCol = 0; dwCol < m_dwCols; ++dwCol)
{
strSQLCol.Format(_T("[%s], "), m_ColHeaders.GetAt(dwCol));
strSQL += strSQLCol;
}
strSQL.Delete(strSQL.GetLength()-2, 2);
strSQL += _T(") VALUES (");
for (dwCol = 0; dwCol < m_dwCols; ++dwCol)
{
strSQLCol.Format(_T("'%s', "), Cell(dwRow, dwCol)->GetText());
strSQL += strSQLCol;
}
strSQL.Delete(strSQL.GetLength()-2, 2);
strSQL += _T(")");
try
{
m_Excel.m_db.ExecuteSQL(strSQL);
}
catch (CDBException* e)
{
return false;
}
}
return true;
}
void CODBCExcelSheet::ResetCells()
{
for (DWORD dwRow = 0; dwRow < m_dwRows; ++dwRow)
{
for (DWORD dwCol = 0; dwCol < m_dwCols; ++dwCol)
{
m_Cells[dwRow][dwCol].Empty();
}
}
}
//rowIndex >= 1
