dbmanagerdlg.cpp
来自「利用OLEDB以数据库的方式打开Excell文件」· C++ 代码 · 共 1,946 行 · 第 1/4 页
CPP
1,946 行
dlgData.SetRecordset(&rs);
dlgData.DoModal();
// Make sure the CDynamicBulkSet is cleaned up properly
rs.Close();
}
void CDBManagerDlg::OnSelchangeListDb()
{
int iNowSel = m_ListCtrl.GetCurSel();
CString csNowSel,table_name;
m_ListCtrl.GetText( iNowSel, csNowSel );
field_info=csNowSel;
int Length = field_info.GetLength();
if( field_info[0] == 0x27 )
{
CString field_info_resualt=field_info.Right(Length-1);
Length = field_info_resualt.GetLength();
field_info_resualt=field_info_resualt.Left(Length-1);
table_name="["+field_info_resualt+"A1:IV65536]";
}
else
{
table_name="["+field_info+"]";
}
cs_table_name=table_name;
FetchColumnInfo( csNowSel,m_RsGridView );
/*
//if (csNowSel == "whole$")
m_biaoming=biaoming;
if (csNowSel == m_biaoming+"$")
else
FetchColumnInfo( csNowSel,m_RsGridView2 );
*/
//*********************************显示数据***********************************//
CString csSQL="select * from "+table_name;
if (m_pDynamicBulkSet)
{
if (m_pDynamicBulkSet->IsOpen())
m_pDynamicBulkSet->Close();
delete m_pDynamicBulkSet;
m_pDynamicBulkSet = 0;
}
/////////
CString m_sUpdateString;
m_sUpdateString.Format("UPDATE %s SET [话费分成佣金总额]=[200301月话费分成佣金] +[200302月话费分成佣金]+[200303月话费分成佣金] +[200304月话费分成佣金] +[200305月话费分成佣金] +[200306月话费分成佣金] +[200307月话费分成佣金] +[200308月话费分成佣金] +[200309月话费分成佣金] +[200310月话费分成佣金] +[200311月话费分成佣金] +[200312月话费分成佣金] +[200401月话费分成佣金] +[200402月话费分成佣金] +[200403月话费分成佣金] +[200404月话费分成佣金] +[200405月话费分成佣金] +[200406月话费分成佣金] +[200407月话费分成佣金] +[200408月话费分成佣金] +[200409月话费分成佣金] +[200410月话费分成佣金] +[200411月话费分成佣金] +[200412月话费分成佣金] +[200501月话费分成佣金] +[200502月话费分成佣金] +[200503月话费分成佣金] +[200504月话费分成佣金] +[200505月话费分成佣金] +[200506月话费分成佣金] +[200507月话费分成佣金] +[200508月话费分成佣金] +[200509月话费分成佣金] +[200510月话费分成佣金] +[200511月话费分成佣金] +[200512月话费分成佣金] +[200601月话费分成佣金] +[200602月话费分成佣金] +[200603月话费分成佣金] +[200604月话费分成佣金]",table_name);
m_Database.ExecuteSQL(m_sUpdateString);
m_sUpdateString.Format("UPDATE %s SET [使用费总额]=[200301月使用费] +[200302月使用费]+[200303月使用费] +[200304月使用费] +[200305月使用费] +[200306月使用费] +[200307月使用费] +[200308月使用费] +[200309月使用费] +[200310月使用费] +[200311月使用费] +[200312月使用费] +[200401月使用费] +[200402月使用费] +[200403月使用费] +[200404月使用费] +[200405月使用费] +[200406月使用费] +[200407月使用费] +[200408月使用费] +[200409月使用费] +[200410月使用费] +[200411月使用费] +[200412月使用费] +[200501月使用费] +[200502月使用费] +[200503月使用费] +[200504月使用费] +[200505月使用费] +[200506月使用费] +[200507月使用费] +[200508月使用费] +[200509月使用费] +[200510月使用费] +[200511月使用费] +[200512月使用费] +[200601月使用费] +[200602月使用费] +[200603月使用费] +[200604月使用费]",table_name);
m_Database.ExecuteSQL(m_sUpdateString);
m_pDynamicBulkSet = new CDynamicBulkSet( &m_Database );
////////////////
// Open the recordset, create the dialog and hand it the recordset
m_pDynamicBulkSet->Open(CRecordset::snapshot, csSQL,
CRecordset::readOnly | CRecordset::useMultiRowFetch);
CODBCFieldInfo info;
CStringArray csTotalContent;
int iTotalRows = 0;
int nColumns = m_pDynamicBulkSet->GetODBCFieldCount();
m_RsGridResult.SetColCount( nColumns );
long* rgLength;
LPSTR rgData;
CString strData,value;
while (!m_pDynamicBulkSet->IsEOF())
{
int nRowsFetched = m_pDynamicBulkSet->GetRowsFetched();
iTotalRows += nRowsFetched;
for (int nRow = 0; nRow < nRowsFetched; nRow++)
{
for (int nField = 0; nField < nColumns; nField++)
{
m_pDynamicBulkSet->GetODBCFieldInfo(nField, info);
if( nRow == 0 )
{
if ( !m_RsGridResult.SetCellText( 0, nField, info.m_strName))
{
return;
}
}
rgData = (LPSTR)m_pDynamicBulkSet->m_ppvData[nField];
rgLength = (long*)m_pDynamicBulkSet->m_ppvLengths[nField];
int nStatus = m_pDynamicBulkSet->GetRowStatus(nRow + 1);
// Get the string to display
if (nStatus == SQL_ROW_DELETED)
strData = _T("<DELETED>");
else if (nStatus == SQL_ROW_NOROW)
// Shouldn't get this since rows fetched is checked
strData = _T("<NO_ROW>");
else if (rgLength[nRow] == SQL_NULL_DATA)
strData = _T("<NULL>");
else
strData = &rgData[nRow * MAX_TEXT_LEN];
csTotalContent.Add( strData );
}
}
m_pDynamicBulkSet->MoveNext();
}
while( m_RsGridResult.GetCellText( 1, 1 ) != "" )
{
m_RsGridResult.DeleteRow( 1, 1 );
}
m_RsGridResult.SetRowCount( iTotalRows + 1 );
if( nColumns == 1 || iTotalRows == 1 )
{
m_RsGridResult.InsertRow( 0, 1 );
}
for (int nRow = 0; nRow < iTotalRows; nRow++)
{
for (int nFields = 0; nFields < nColumns; nFields++)
{
m_RsGridResult.SetCellText(nRow + 1, nFields, csTotalContent.GetAt( nFields + nRow * nColumns ) );
}
}
//****************************************************************************//
}
void CDBManagerDlg::OnButView1()
{
ASSERT(m_pDynamicBulkSet->IsOpen());
m_pDynamicBulkSet->MoveFirst();
ShowSQLData();
// Disable the correct controls
GetDlgItem(IDC_BUT_VIEW1)->EnableWindow(FALSE);
}
void CDBManagerDlg::OnButView2()
{
ASSERT(m_pDynamicBulkSet->IsOpen());
m_pDynamicBulkSet->MovePrev();
if (m_pDynamicBulkSet->IsBOF())
{
// Disable the correct controls
GetDlgItem(IDC_BUT_VIEW2)->EnableWindow(FALSE);
AfxMessageBox("已到数据结尾");
}
else
ShowSQLData();
}
void CDBManagerDlg::OnButView3()
{
ASSERT(m_pDynamicBulkSet->IsOpen());
m_pDynamicBulkSet->MoveNext();
if (m_pDynamicBulkSet->IsEOF())
{
// Disable the correct controls
GetDlgItem(IDC_BUT_VIEW3)->EnableWindow(FALSE);
AfxMessageBox("已到数据结尾");
}
else
ShowSQLData();
}
void CDBManagerDlg::OnButView4()
{
ASSERT(m_pDynamicBulkSet->IsOpen());
m_pDynamicBulkSet->MoveLast();
ShowSQLData();
// Disable the correct controls
GetDlgItem(IDC_BUT_VIEW4)->EnableWindow(FALSE);
}
BOOL CDBManagerDlg::ShowSQLData()
{
ASSERT(m_pDynamicBulkSet->IsOpen());
if (m_pDynamicBulkSet->IsEOF() && m_pDynamicBulkSet->IsBOF())
{
// Disable all the controls
GetDlgItem(IDC_BUT_VIEW1)->EnableWindow(FALSE);
GetDlgItem(IDC_BUT_VIEW2)->EnableWindow(FALSE);
GetDlgItem(IDC_BUT_VIEW3)->EnableWindow(FALSE);
GetDlgItem(IDC_BUT_VIEW4)->EnableWindow(FALSE);
// Put up a warning dialog and return
AfxMessageBox("此表没有数据");
return FALSE;
}
else
{
// Enable all the controls
GetDlgItem(IDC_BUT_VIEW1)->EnableWindow(TRUE);
GetDlgItem(IDC_BUT_VIEW2)->EnableWindow(TRUE);
GetDlgItem(IDC_BUT_VIEW3)->EnableWindow(TRUE);
GetDlgItem(IDC_BUT_VIEW4)->EnableWindow(TRUE);
}
/*
CODBCFieldInfo info;
int nColumns = m_pDynamicBulkSet->GetODBCFieldCount();
int nRowsFetched = m_pDynamicBulkSet->GetRowsFetched();
while( m_RsGridResult.GetCellText( 1, 1 ) != "" )
{
m_RsGridResult.DeleteRow( 1, 1 );
}
m_RsGridResult.SetColCount( nColumns );
m_RsGridResult.SetRowCount( nRowsFetched + 1 );
if( nColumns == 1 || nRowsFetched == 1 )
{
m_RsGridResult.InsertRow( 0, 1 );
}
long* rgLength;
LPSTR rgData;
CString strData;
// for each column, retrieve the field name and
// insert the field name in the header
for (int nField = 0; nField < nColumns; nField++)
{
m_pDynamicBulkSet->GetODBCFieldInfo(nField, info);
if ( !m_RsGridResult.SetCellText( 0, nField, info.m_strName))
{
return FALSE;
}
rgData = (LPSTR)m_pDynamicBulkSet->m_ppvData[nField];
rgLength = (long*)m_pDynamicBulkSet->m_ppvLengths[nField];
for (int nRow = 0; nRow < nRowsFetched; nRow++)
{
int nStatus = m_pDynamicBulkSet->GetRowStatus(nRow + 1);
// Get the string to display
if (nStatus == SQL_ROW_DELETED)
strData = _T("<DELETED>");
else if (nStatus == SQL_ROW_NOROW)
// Shouldn't get this since rows fetched is checked
strData = _T("<NO_ROW>");
else if (rgLength[nRow] == SQL_NULL_DATA)
strData = _T("<NULL>");
else
strData = &rgData[nRow * MAX_TEXT_LEN];
m_RsGridResult.SetCellText(nRow + 1, nField,
strData );
}
}
*/
CODBCFieldInfo info;
CStringArray csTotalContent;
int iTotalRows = 0;
int nColumns = m_pDynamicBulkSet->GetODBCFieldCount();
m_RsGridResult.SetColCount( nColumns );
long* rgLength;
LPSTR rgData;
CString strData;
while (!m_pDynamicBulkSet->IsEOF())
{
int nRowsFetched = m_pDynamicBulkSet->GetRowsFetched();
iTotalRows += nRowsFetched;
for (int nRow = 0; nRow < nRowsFetched; nRow++)
{
for (int nField = 0; nField < nColumns; nField++)
{
m_pDynamicBulkSet->GetODBCFieldInfo(nField, info);
if( nRow == 0 )
{
if ( !m_RsGridResult.SetCellText( 0, nField, info.m_strName))
{
return FALSE;
}
}
rgData = (LPSTR)m_pDynamicBulkSet->m_ppvData[nField];
rgLength = (long*)m_pDynamicBulkSet->m_ppvLengths[nField];
int nStatus = m_pDynamicBulkSet->GetRowStatus(nRow + 1);
// Get the string to display
if (nStatus == SQL_ROW_DELETED)
strData = _T("<DELETED>");
else if (nStatus == SQL_ROW_NOROW)
// Shouldn't get this since rows fetched is checked
strData = _T("<NO_ROW>");
else if (rgLength[nRow] == SQL_NULL_DATA)
strData = _T("<NULL>");
else
strData = &rgData[nRow * MAX_TEXT_LEN];
csTotalContent.Add( strData );
}
}
m_pDynamicBulkSet->MoveNext();
}
while( m_RsGridResult.GetCellText( 1, 1 ) != "" )
{
m_RsGridResult.DeleteRow( 1, 1 );
}
m_RsGridResult.SetRowCount( iTotalRows + 1 );
if( nColumns == 1 || iTotalRows == 1 )
{
m_RsGridResult.InsertRow( 0, 1 );
}
for (int nRow = 0; nRow < iTotalRows; nRow++)
{
for (int nFields = 0; nFields < nColumns; nFields++)
{
m_RsGridResult.SetCellText(nRow + 1, nFields, csTotalContent.GetAt( nFields + nRow * nColumns ) );
}
}
}
void CDBManagerDlg::OnExecute()
{
//save as data file
TCHAR szData[100];
CString cspath = theApp.m_csRootPath + "DBConfig.ini";
//Write SQL FieldName fieldtype FieldLength
CString csCustomerName;
GetPrivateProfileString( "Data", "CustomerName", "", szData, 100, cspath );
csCustomerName = szData;
CString csTempFilePath = csCustomerName + ".txt";
HANDLE hFileE = CreateFile( csTempFilePath,
GENERIC_WRITE, FILE_SHARE_READ,
NULL, CREATE_ALWAYS, FILE_ATTRIBUTE_NORMAL, NULL);
if (hFileE == INVALID_HANDLE_VALUE)
{
MessageBox( "不能创建数据文件!", "Error" );
return;
}
CFile myEFile( ( int )hFileE );
/////////
CODBCFieldInfo info;
int nColumns = m_pDynamicBulkSet->GetODBCFieldCount();
int nRowsFetched = m_pDynamicBulkSet->GetRowsFetched();
long* rgLength;
LPSTR rgData;
CString strData;
int iMaxArray[200];
// for each column, retrieve the field name and
// insert the field name in the header
for (int nField = 0; nField < nColumns; nField++)
{
m_pDynamicBulkSet->GetODBCFieldInfo(nField, info);
rgData = (LPSTR)m_pDynamicBulkSet->m_ppvData[nField];
rgLength = (long*)m_pDynamicBulkSet->m_ppvLengths[nField];
for (int nRow = 0; nRow < nRowsFetched; nRow++)
{
int nStatus = m_pDynamicBulkSet->GetRowStatus(nRow + 1);
// Get the string to display
if (nStatus == SQL_ROW_DELETED)
strData = _T("<DELETED>");
else if (nStatus == SQL_ROW_NOROW)
// Shouldn't get this since rows fetched is checked
strData = _T("<NO_ROW>");
else if (rgLength[nRow] == SQL_NULL_DATA)
strData = _T("<NULL>");
else
strData = &rgData[nRow * MAX_TEXT_LEN];
CString csTemp = strData;
csTemp.TrimLeft(" ");
csTemp.TrimRight( " ");
int iFieldLength = csTemp.GetLength();
if( iFieldLength > iMaxArray[nField] )
iMaxArray[nField] = iFieldLength;
m_csFinalData += strData;
m_csFinalData += "\x001";
}
}
while(!m_pDynamicBulkSet->IsEOF())
{
m_pDynamicBulkSet->MoveNext();
nColumns = m_pDynamicBulkSet->GetODBCFieldCount();
nRowsFetched = m_pDynamicBulkSet->GetRowsFetched();
// for each column, retrieve the field name and
// insert the field name in the header
for (int nField = 0; nField < nColumns; nField++)
{
m_pDynamicBulkSet->GetODBCFieldInfo(nField, info);
rgData = (LPSTR)m_pDynamicBulkSet->m_ppvData[nField];
rgLength = (long*)m_pDynamicBulkSet->m_ppvLengths[nField];
for (int nRow = 0; nRow < nRowsFetched; nRow++)
{
int nStatus = m_pDynamicBulkSet->GetRowStatus(nRow + 1);
// Get the string to display
if (nStatus == SQL_ROW_DELETED)
strData = _T("<DELETED>");
else if (nStatus == SQL_ROW_NOROW)
// Shouldn't get this since rows fetched is checked
strData = _T("<NO_ROW>");
else if (rgLength[nRow] == SQL_NULL_DATA)
strData = _T("<NULL>");
else
strData = &rgData[nRow * MAX_TEXT_LEN];
CString csTemp = strData;
csTemp.TrimLeft(" ");
csTemp.TrimRight( " ");
int iFieldLength = csTemp.GetLength();
if( iFieldLength > iMaxArray[nField] )
iMaxArray[nField] = iFieldLength;
m_csFinalData += strData;
m_csFinalData += "\x001";
}
}
}
for ( nField = 0; nField < nColumns; nField++)
{
CString csTemp;
csTemp.Format( "%d", iMaxArray[nField] );
m_csMaxFieldLength += csTemp;
m_csMaxFieldLength += ",";
}
WritePrivateProfileString( "Database", "SQL", m_csSQL, cspath );
WritePrivateProfileString( "Database", "FieldName", m_csFieldName, cspath );
WritePrivateProfileString( "Database", "FieldType", m_csFieldType, cspath );
WritePrivateProfileString( "Database", "FieldLength", m_csFieldLength, cspath );
WritePrivateProfileString( "Database", "MaxFieldLength", m_csMaxFieldLength, cspath );
/////////
myEFile.Write(m_csFinalData.GetBuffer(0),m_csFinalData.GetLength());
myEFile.Close();
}
void CDBManagerDlg::OpenSQLResult( CString csSQL )
{
if (m_pDynamicBulkSet)
{
if (m_pDynamicBulkSet->IsOpen())
m_pDynamicBulkSet->Close();
delete m_pDynamicBulkSet;
m_pDynamicBulkSet = 0;
}
m_pDynamicBulkSet = new CDynamicBulkSet( &m_Database );
// Open the recordset, create the dialog and hand it the recordset
m_pDynamicBulkSet->Open(CRecordset::snapshot, csSQL,
CRecordset::readOnly | CRecordset::useMultiRowFetch);
}
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?