dbmanagerdlg.cpp
来自「利用OLEDB以数据库的方式打开Excell文件」· C++ 代码 · 共 1,946 行 · 第 1/4 页
CPP
1,946 行
if ( !m_RsGridResult.SetCellText( 0, nField, info.m_strName))
{
return ;
}
rgData = (LPSTR)TempSet->m_ppvData[nField];
rgLength = (long*)TempSet->m_ppvLengths[nField];
for (int nRow = 0; nRow < nRowsFetched; nRow++)
{
int nStatus = TempSet->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];
if( nField == 0 )
Array1.Add(strData);
else
Array2.Add(strData);
}
}
for( int ii = 0; ii < nRowsFetched; ii++ )
{
// m_stempString.Format(" UPDATE [whole$A1:IV65536] SET [whole$A1:IV65536].[结算类型]= '%s' WHERE [whole$A1:IV65536].[用户号码]= %s", Array2.GetAt(ii), Array1.GetAt(ii) );
UpdateData();
m_stempString.Format("UPDATE %s SET %s.%s='%s' WHERE %s.[UIM卡号]=%s",ListTable1,ListTable1,ListField1,Array2.GetAt(ii),ListTable1,Array1.GetAt(ii));
m_Database.ExecuteSQL(m_stempString);
}
TempSet->MoveNext();
}
}// for (int i=0; i<nIndex; i++) ----end
//////////////////////////////////////////
m_ListCorrespond.DeleteAllItems();
}
void CDBManagerDlg::OnClickLISTCorrespond(NMHDR* pNMHDR, LRESULT* pResult)
{
// TODO: Add your control notification handler code here
NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
m_item=pNMListView->iItem;
*pResult = 0;
}
void CDBManagerDlg::OnSelchangeLISTCorrespond()
{
int iRow = m_RsGridView.GetCurrentRow();
int iRow2 = m_RsGridView2.GetCurrentRow();
CString m_csListCurrent = m_RsGridView.GetCellText(iRow,0);
CString m_csListCurrent2 = m_RsGridView2.GetCellText(iRow2,0);
}
void CDBManagerDlg::OnDELETEList()
{
m_ListCorrespond.DeleteItem(m_item);
}
void CDBManagerDlg::OnRButtonDBClickRsgridctrlResult(long nFlags, long lRow, long lCol)
{
// TODO: Add your control notification handler code here
m_biaoming=biaoming;
if (cs_table_name=="["+m_biaoming+"$]")
{
CCompany companyDlg;
companyDlg.m_company_pCDataBase=&m_Database;
lRow=m_RsGridResult.GetCurrentRow();
companyDlg.m_cs_company_name=m_RsGridResult.GetCellDisString(lRow,10);
companyDlg.DoModal();
}
}
void CDBManagerDlg::OnButtonHelp()
{
// TODO: Add your control notification handler code here
BOOL dwResult;
// dwResult = (DWORD)::ShellExecute( m_hWnd, "open", "F:\\songrf_new\\New00\\new_1\\Debug\\config\\Help.doc", "", "", SW_SHOWMAXIMIZED );
dwResult = (DWORD)::ShellExecute( m_hWnd, "open", "Debug\\config\\Help.doc", "", "", SW_SHOWMAXIMIZED );
if ( dwResult <= 32 )
{
MessageBox( " 打开帮助文件失败 ", "错误", MB_ICONSTOP );
}
}
void CDBManagerDlg::OnSelchangeListDb2()
{
int iNowSel = m_ListCtrl2.GetCurSel();
CString csNowSel,table_name;
m_ListCtrl2.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_RsGridView2 );
//*********************************显示数据***********************************//
CString csSQL="select * from "+table_name;
if (m_pDynamicBulkSet)
{
if (m_pDynamicBulkSet->IsOpen())
m_pDynamicBulkSet->Close();
m_pDynamicBulkSet = 0;
delete m_pDynamicBulkSet;
}
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;
// 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;
}
while (!m_pDynamicBulkSet->IsEOF())
{
int nRowsFetched = m_pDynamicBulkSet->GetRowsFetched();
if( nField == 0 )
iTotalRows += nRowsFetched;
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];
csTotalContent.Add( strData );
}
m_pDynamicBulkSet->MoveNext();
}
m_pDynamicBulkSet->MoveFirst();
}
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 nFields = 0; nFields < nColumns; nFields++)
{
for (int nRow = 0; nRow < iTotalRows; nRow++)
{
m_RsGridResult.SetCellText(nRow + 1, nFields, csTotalContent.GetAt( nRow + nFields * iTotalRows ) );
}
}
*/
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;
}
}
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::OnButton2()
{
m_ListCorrespond.DeleteAllItems();
}
void CDBManagerDlg::OnButtonQuery()
{
// CQUERY QueryDlg;
// QueryDlg.m_QueryDataBase = &m_Database;
// CString pdateSQL="UPDATE [whole$] SET [临时总和字段] = 3";
// m_Database.ExecuteSQL(pdateSQL);
// TODO: Add your control notification handler code here
CQUERY queryDlg;
queryDlg.m_query_pCDataBase = &m_Database;
queryDlg.m_QueryDataBase = &m_Database;
queryDlg.DoModal();
}
void CDBManagerDlg::OnUpdatetotal()
{
CString table_name;
table_name=biaoming;
table_name = "["+ table_name + "$A1:IV65536]" ;
/////////////////////////
CString csSQL="select * from "+table_name;
if (m_pDynamicBulkSet)
{
if (m_pDynamicBulkSet->IsOpen())
m_pDynamicBulkSet->Close();
delete m_pDynamicBulkSet;
m_pDynamicBulkSet = 0;
}
CString m_sSelString = "";
CString m_sSelPart = "";
CString m_sUpdateString = "";
CString m_sUpdateString2 = "";
CString m_sPartString = "" ;
CString m_sPartString2 = "";
CString m_tempTableName;
CODBCFieldInfo info;
CStringArray csTotalContent;
m_pDynamicBulkSet = new CDynamicBulkSet( &m_Database );
m_pDynamicBulkSet->Open(CRecordset::snapshot, csSQL,
CRecordset::readOnly | CRecordset::useMultiRowFetch);
int nColumns = m_pDynamicBulkSet->GetODBCFieldCount();
//m_RsGridResult.SetColCount( nColumns );
m_sUpdateString.Format("UPDATE %s SET ",table_name);
m_sUpdateString2.Format("UPDATE %s SET ",table_name);
m_sSelString.Format("SELECT ");
for (int nField = 13; nField < nColumns; nField++)
{
if((nField==15)||(nField==17))
{
}
else
{
m_pDynamicBulkSet->GetODBCFieldInfo(nField, info);
m_tempTableName =info.m_strName;
m_tempTableName = "[" + m_tempTableName + "]" ;
if (nField==13)
{
m_sSelPart.Format(" SUM(%s)",m_tempTableName);
m_sPartString.Format( "%s = 0",m_tempTableName);
// m_sPartString2.Format( " %s = ( SELECT SUM(%s) FROM %s)",m_tempTableName,m_tempTableName,table_name);
}
else
{
m_sSelPart.Format(", SUM(%s)",m_tempTableName);
m_sPartString.Format( ", %s = 0",m_tempTableName);
// m_sPartString2.Format( ", %s = ( SELECT SUM(%s) FROM %s)",m_tempTableName,m_tempTableName,table_name);
}
m_sSelString += m_sSelPart;
m_sUpdateString +=m_sPartString;
// m_sUpdateString2 +=m_sPartString2;
}
}
CString m_sSelPart2;
m_sSelPart2.Format(" From %s", table_name);
m_sSelString += m_sSelPart2;
m_sUpdateString += " where [序号] = 10000 ";
// m_sUpdateString2 += " where [序号] = 10000 ";
// MessageBox(m_sUpdateString,"dd");
m_Database.ExecuteSQL(m_sUpdateString);
// m_Database.ExecuteSQL(m_sUpdateString2);
//将查询的SUM结果 放到数组中
CDynamicBulkSet * TempSet;
CStringArray Array1;
CODBCFieldInfo info2;
TempSet = new CDynamicBulkSet( &m_Database );
TempSet->Open(CRecordset::snapshot, m_sSelString,CRecordset::readOnly | CRecordset::useMultiRowFetch);
//CStringArray Array1, Array2;
while(!TempSet->IsEOF())
{
int nColumns2 = TempSet->GetODBCFieldCount();
int nRowsFetched2 = TempSet->GetRowsFetched();
long* rgLength2;
LPSTR rgData2;
CString strData2;
// for each column, retrieve the field name and
// insert the field name in the header
for (int nField2 = 0; nField2 < nColumns2; nField2++)
{
TempSet->GetODBCFieldInfo(nField2, info2);
//if ( !m_RsGridResult.SetCellText( 0, nField2, info2.m_strName))
//{
// return ;
// }
rgData2 = (LPSTR)TempSet->m_ppvData[nField2];
rgLength2 = (long*)TempSet->m_ppvLengths[nField2];
// for (int nRow2 = 0; nRow2 < nRowsFetched2; nRow2++)
// {
int nRow2 = 0;
int nStatus2 = TempSet->GetRowStatus(nRow2 +1 );
// Get the string to display
if (nStatus2 == SQL_ROW_DELETED)
strData2 = _T("<DELETED>");
else if (nStatus2 == SQL_ROW_NOROW)
// Shouldn't get this since rows fetched is checked
strData2 = _T("<NO_ROW>");
else if (rgLength2[nRow2] == SQL_NULL_DATA)
strData2 = _T("<NULL>");
else
strData2 = &rgData2[nRow2 * MAX_TEXT_LEN];
Array1.Add(strData2);
// }
}
TempSet->MoveNext();
}
//end
//依次取出数组中的值。
int ii=0;
for (int nField2 = 13; nField2 < nColumns; nField2++)
{
if((nField2==15)||(nField2==17))
{
}
else
{
m_pDynamicBulkSet->GetODBCFieldInfo(nField2, info);
m_tempTableName =info.m_strName;
m_tempTableName = "[" + m_tempTableName + "]" ;
if (nField2==13)
{
m_sPartString2.Format( "%s=%s",m_tempTableName,Array1.GetAt(ii));
}
else
{
m_sPartString2.Format( ",%s=%s",m_tempTableName,Array1.GetAt(ii));
}
ii++;
m_sUpdateString2 +=m_sPartString2;
}
}
m_sUpdateString2 += " where [序号] = 10000 ";
// MessageBox(m_sUpdateString2,"dd");
m_Database.ExecuteSQL(m_sUpdateString2);
///////////////////////
// CString csSQL="select * from "+table_name;
if (m_pDynamicBulkSet)
{
if (m_pDynamicBulkSet->IsOpen())
m_pDynamicBulkSet->Close();
delete m_pDynamicBulkSet;
m_pDynamicBulkSet = 0;
}
m_pDynamicBulkSet = new CDynamicBulkSet( &m_Database );
m_pDynamicBulkSet->Open(CRecordset::snapshot, csSQL,
CRecordset::readOnly | CRecordset::useMultiRowFetch);
ShowSQLData();
MessageBox(" 更新总表合计完成!","执行结果");
}
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?