CDatabase *CCachedDBConnections::GetConnection(const CString &strConnect, BOOL bAutoOpen) { CCacheMap::CPair *pKeyValPair = m_DBCache.PLookup(strConnect); CDatabase *pDB = NULL; if (pKeyValPair != NULL) { pDB = pKeyValPair->value; } if (NULL == pDB) { try { pDB = new CDatabase; } catch (...) { pDB = NULL; } } if (pDB != NULL && !pDB->IsOpen() && bAutoOpen) { ATLVERIFY(pDB->OpenEx(strConnect, CDatabase::useCursorLib | CDatabase::noOdbcDialog)); } if (NULL == pKeyValPair) { m_DBCache[strConnect] = pDB; } return pDB; }
bool COleDBConnectionProp::Connect(CDatabase& database, bool bShowDialog) { CWaitCursor cursor; if(m_strDSN.IsEmpty()) { ShowError("Empty ODBC Source"); return false; } if(m_strLoginName.IsEmpty()) { ShowError("Empty Login Name"); return false; } CString ConnectString = "DSN=" + m_strDSN + ";"; if(!m_strDatabaseName.IsEmpty()) { ConnectString += "DATABASE=" + m_strDatabaseName + ";"; } ConnectString += "UID=" + m_strLoginName + ";" + "PWD=" + m_strPassword + ";" ; try { DWORD dwOptions = CDatabase::useCursorLib; if(!bShowDialog) dwOptions |= CDatabase::noOdbcDialog; if(database.IsOpen()) database.Close(); if(!database.OpenEx(ConnectString,dwOptions)) { return false; } } catch( CDBException* pExc) { pExc->ReportError(); pExc->Delete(); return false; } return true; }
int CMyDatabase::OpenConnection( CDatabase &m_Database,int dbms,int Index ) { char drivername[255] = {0}; int result = -1; StationIndex = Index; try { m_Database.SetLoginTimeout(2); m_Database.OpenEx(m_strConnect, CDatabase::noOdbcDialog); } catch( CDBException *e) { result = -1; char Message[255] = {0}; sprintf(Message,"CMyDatabase::OpenConnection(%s)",e->m_strError); WriteLog(Message,""); e->Delete(); } return result; }
int CMyDatabase::OpenConnection( CDatabase &m_Database ) { char drivername[255] = {0}; int result = 0; char Message[255] = {0}; try { m_Database.SetLoginTimeout(2); m_Database.OpenEx(m_strConnect, CDatabase::noOdbcDialog); result = 1; } catch( CDBException *e) { sprintf(Message,"CMyDatabase::OpenConnection(%s)",e->m_strError); printf( "%s\n",Message); WriteLog(Message,""); e->Delete(); } return result; }
BOOL C51JobWebPost::CreatExcelDb(CString dbpath,CString tableName,CString &strError) { if (_access(dbpath,0)==0) //存在 { strError = "数据库已存在"; return TRUE; } CDatabase database; CString sDriver; CString sExcelFile; CString sSql; //检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)" sDriver = GetExcelDriver(); if (sDriver.IsEmpty()) { // 没有发现Excel驱动 strError = "请先安装Excel软件才能使用导出功能"; return FALSE; } // 创建进行存取的字符串 sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, dbpath); // 创建数据库 (既Excel表格文件) if( database.OpenEx(sSql,CDatabase::noOdbcDialog) ) { CString strTemp; strTemp.Format("账号 TEXT,密码 TEXT,推荐人 TEXT,邮箱 TEXT,网址 TEXT,时间 TEXT"); // 创建表结构 sSql = "CREATE TABLE " + tableName + " ( " + strTemp + " ) "; database.ExecuteSQL(sSql); return TRUE; } else { strError = "创建EXCEL数据库失败"; return FALSE; } }
////////////////////////////////////////////////////////////////////////////// // 函数:void CListCtrlToExcel(CListCtrl* pList, CString strTitle) // 参数: // pList 需要导出的List控件指针 // strTitle 导出的数据表标题 // 说明: // 导出CListCtrl控件的全部数据到Excel文件。Excel文件名由用户通过“另存为” // 对话框输入指定。创建名为strTitle的工作表,将List控件内的所有数据(包括 // 列名和数据项)以文本的形式保存到Excel工作表中。保持行列关系。 // ////////////////////////////////////////////////////////////////////////////// void CListCtrlToExcel(CListCtrl* pList, CString strTitle) { CString warningStr; if(pList->GetItemCount()>0) { CDatabase database; CString sDriver; CString sExcelFile; CString sSql; CString tableName = strTitle; //检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)" sDriver = GetExcelDriver(); if(sDriver.IsEmpty()) { //没有发现Excel驱动 AfxMessageBox("没有安装Excel!\n请先安装Excel软件才能使用导出功能!"); return; } //默认文件名 if(!GetDefaultXlsFileName(sExcelFile)) return; //创建进行存取的字符串 sSql.Format("DRIVER={%s};DSN='';FIRSTORWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver,sExcelFile,sExcelFile); //创建数据库(即Excel表格文件) if(database.OpenEx(sSql,CDatabase::noOdbcDialog)) { //创建表结构 int i; LVCOLUMN columnData; CString columnName; int columnNum=0; CString strH,strV; sSql=""; strH=""; columnData.mask = LVCF_TEXT; columnData.cchTextMax = 100; columnData.pszText = columnName.GetBuffer(100); for(i=0;pList->GetColumn(i,&columnData);i++) { if(i!=0) { sSql=sSql+", "; strH=strH+", "; } sSql = sSql + " " + columnData.pszText + " TEXT"; strH = strH + " " + columnData.pszText + " "; } columnName.ReleaseBuffer(); columnNum = i; sSql = "CREATE TABLE " + tableName + " ( " + sSql + " ) "; database.ExecuteSQL(sSql); //插入数据项 int nItemIndex; for(nItemIndex=0;nItemIndex<pList->GetItemCount();nItemIndex++) { strV = ""; for(i=0;i<columnNum;i++) { if(i!=0) { strV = strV + ", "; } strV = strV + " '" + pList->GetItemText(nItemIndex,i) + "' "; } sSql = "INSERT INTO " + tableName + " (" + strH + ")" + "VALUES(" + strV + ")"; database.ExecuteSQL(sSql); } } //关闭数据库 database.Close(); warningStr.Format("导出文件保存于%s中!",sExcelFile); AfxMessageBox(warningStr); ShellExecute(NULL,"open",sExcelFile,NULL,NULL,SW_SHOW); //最后才显示文件名为strFile的数据表 } else { //没有数据 MessageBox(NULL,"没有数据,不能导出!","提示",MB_OK|MB_ICONWARNING|MB_TOPMOST); } }
void CList2Excel::ExportListToExcel(CListCtrl* pList, CString strTitle,CString strExcFile) { CString warningStr; if (pList->GetItemCount ()>0) { CDatabase database; CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动 CString sExcelFile = strExcFile; // 要建立的Excel文件 CString sSql; CString strField = "",strField2 = ""; int m; int n = pList->GetItemCount(); // 创建进行存取的字符串 sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile); // 创建数据库 (既Excel表格文件) if( database.OpenEx(sSql,CDatabase::noOdbcDialog) ) { // 创建表结构 int i; LVCOLUMN columnData; CString columnName; int columnNum = 0; CString strH; CString strV; sSql = ""; strH = ""; columnData.mask = LVCF_TEXT; columnData.cchTextMax =256; columnData.pszText = columnName.GetBuffer (256); for(i=0;pList->GetColumn(i,&columnData);i++) { strField = strField + "[" + columnData.pszText +"]" + " char(255), "; strField2 = strField2 + "[" + columnData.pszText +"], "; } columnName.ReleaseBuffer (); m = i; // 创建表结构 sSql.Format("CREATE TABLE [%s] (",strTitle); strField.Delete(strField.GetLength()-2, 2); strField += ")"; sSql += strField; database.ExecuteSQL(sSql); strField2.Delete(strField2.GetLength()-2, 2); //Add By HuangXiao Ke;把导出时进度信息显示出来,耦合性太大,如果别的程序要用这个累,把这些无关代码去掉 CD_Progress* pDlg = (CD_Progress*)g_dlgQueryManager.GetDialog(IDD_DIALOG_PROGRESS); pDlg->ShowWindow(SW_SHOW); pDlg->m_cProgress.SetRange(0,n); pDlg->m_cProgress.SetPos(0); CString str; // 插入数值 for (i=0; i<n; i++) { pDlg->m_cProgress.SetPos(i); sSql=""; sSql.Format("INSERT INTO [%s] (",strTitle); sSql += strField2; sSql += ") VALUES ("; for (int j = 0; j <m ; j++) { str = ""; str.Format("'%s', ", pList->GetItemText(i,j)); sSql = sSql + str; } sSql.Delete(sSql.GetLength()-2, 2); sSql += ")"; //OutputDebugString(sSql); database.ExecuteSQL(sSql); } pDlg->ShowWindow(SW_HIDE); } // 关闭数据库 database.Close(); warningStr.Format("导出文件保存于%s.xls!",sExcelFile); AfxMessageBox(warningStr); } }
void CDlgBDSelectRezTL::OnBnClickedButtonExportExcel() { CDatabase database; CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager CString sExcelFile = "Тележки1067.xls"; // Filename and path for the file to be created if( iNewProc ) sExcelFile = "Тележки1067arx.xls"; CString sSql; CString strColListInsert = ""; CString strColListCreate = ""; int iColCount = 0; LVITEM Item; HDITEM hItem; enum { sizeOfBuffer = 256 }; TCHAR lpBuffer[sizeOfBuffer]; bool fFound = false; hItem.mask = HDI_TEXT; hItem.pszText = lpBuffer; hItem.cchTextMax = sizeOfBuffer; CHeaderCtrl *hcHeader = m_List.GetHeaderCtrl(); iColCount = hcHeader->GetItemCount(); CString strTitle = ""; for( int i = 0; i < iColCount; i++ ) { if( i ) { strColListInsert += ", "; strColListCreate += ", "; } if( hcHeader->GetItem(i, &hItem) ) { strTitle = hItem.pszText; strTitle.Remove('.'); strColListInsert = strColListInsert + "[" + strTitle + "]"; strColListCreate = strColListCreate + "[" + strTitle + "] TEXT"; } } // Build the creation string for access without DSN DeleteFile(".\\" + sExcelFile); if( !CopyFile(".\\html\\" + sExcelFile, ".\\" + sExcelFile, 1) ) { // Создам шаблон sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\".\\html\\%s\";DBQ=.\\html\\%s", sDriver, sExcelFile, sExcelFile); database.OpenEx(sSql,CDatabase::noOdbcDialog); sSql = "CREATE TABLE [Тележки 1067] (" + strColListCreate + ")"; database.ExecuteSQL(sSql); database.Close(); CopyFile(".\\html\\" + sExcelFile, ".\\" + sExcelFile, 1); } sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\".\\%s\";DBQ=%s", sDriver, sExcelFile, sExcelFile); // Create the database (i.e. Excel sheet) if( database.OpenEx(sSql,CDatabase::noOdbcDialog) ) { /* TRY { sSql = "CREATE TABLE [Тележки 1067] (" + strColListCreate + ")"; database.ExecuteSQL(sSql); } CATCH_ALL(e) { } END_CATCH_ALL */ for( int i = 0; i < m_List.GetItemCount(); i++ ) { CString strLine = ""; for( int j = 0; j < iColCount; j++ ) { if( j ) strLine += ", "; strLine += "'" + m_List.GetItemText(i, j) + "'"; } //sSql = "INSERT INTO [Тележки 1067] (" + strColListInsert + ") VALUES ( " + strLine + " )"; sSql = "INSERT INTO [Тележки 1067] VALUES ( " + strLine + " )"; database.ExecuteSQL(sSql); } } database.Close(); CString str = "excel.exe .\\" + sExcelFile; ShellExecute(NULL, "open", "excel.exe", sExcelFile, NULL, SW_SHOWNORMAL); }
/////////////////////////////////////////////////////////////////////////////// // void GetExcelDriver(CListCtrl* pList, CString strTitle) // 参数: // pList 需要导出的List控件指针 // strTitle 导出的数据表标题 // 说明: // 导出CListCtrl控件的全部数据到Excel文件。Excel文件名由用户通过“另存为” // 对话框输入指定。创建名为strTitle的工作表,将List控件内的所有数据(包括 // 列名和数据项)以文本的形式保存到Excel工作表中。保持行列关系。 // /////////////////////////////////////////////////////////////////////////////// void CFunc::ExportListToExcel(CListCtrl* pList[], CString strTitle[], int number) { CString warningStr; bool l_bStart = true; for (int index = 0; index < number; index++)//判断交易分析是否完成 { if (pList[index]->GetItemCount () == 0 || pList[index]->GetItemCount () < 0) { l_bStart = false; } } if (l_bStart) //遍历每一行的循环 { CDatabase database; CString sDriver; CString sExcelFile; CString sSql; CString tableName; // 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)" sDriver = GetExcelDriver(); if (sDriver.IsEmpty()) { // 没有发现Excel驱动 AfxMessageBox("You've not installed Excel!\nPlease install Excel first!"); return; } ///默认文件名 if (!GetDefaultXlsFileName(sExcelFile)) return; // 创建进行存取的字符串 sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile); database.OpenEx(sSql,CDatabase::noOdbcDialog); // 创建数据库 (既Excel表格文件) for (int tableIndex = 0; tableIndex < number; tableIndex++)//创建多个Excel表的循环 { tableName = strTitle[tableIndex]; // 创建表结构 int i; LVCOLUMN columnData; CString columnName; int columnNum = 0; CString strH; CString strV; sSql = ""; strH = ""; columnData.mask = LVCF_TEXT; columnData.cchTextMax =100; columnData.pszText = columnName.GetBuffer (100); for(i=1;pList[tableIndex]->GetColumn(i,&columnData);i++)//第一列没有数据应跳过 { if (i!=1) { sSql = sSql + ", " ; strH = strH + ", " ; } sSql = sSql + " " + columnData.pszText +" TEXT";//数据暂时全部是以文本类型写入Excel strH = strH + " " + columnData.pszText +" "; } columnName.ReleaseBuffer (); columnNum = i; sSql = "CREATE TABLE " + tableName + " ( " + sSql + " ) "; database.ExecuteSQL(sSql); // 插入数据项 int nItemIndex; for (nItemIndex=0;nItemIndex < pList[tableIndex]->GetItemCount(); nItemIndex++) { strV = ""; for(i=1;i<columnNum;i++)//第一列没有数据应跳过 { if (i!=1) { strV = strV + ", " ; } strV = strV + " '" + pList[tableIndex]->GetItemText(nItemIndex,i) +"' "; } sSql = "INSERT INTO "+ tableName +" ("+ strH + ")" +" VALUES("+ strV + ")"; database.ExecuteSQL(sSql); sSql.Empty(); } }//end for创建多个Excel表的循环 // 关闭数据库 database.Close(); warningStr.Format("Excel file is saved as %s!",sExcelFile); AfxMessageBox(warningStr); }//end for遍历每一行的循环 }
BOOL CEdRptExport::ExportDBData(ofstream &htm, ofstream &txt) { CDatabase db; BOOL bRet = FALSE; CString csHeadFile = m_pDoc->m_szHeadRight; if (m_pDoc->m_szODBCLink.IsEmpty()) { ShowMessage(m_pDoc->GetHWnd(), "没有给定数据库指针,也没有指定链接字!"); return FALSE; } if (!db.OpenEx(m_pDoc->m_szODBCLink, CDatabase::openReadOnly | CDatabase::noOdbcDialog)) { ShowMessage(m_pDoc->GetHWnd(), "无法打开数据库连接 %s", m_pDoc->m_szODBCLink); return FALSE; } try { UINT nRecCount = m_pDoc->GetRecordCount(db); CString szSQL = m_pDoc->m_szSQL; if (nRecCount > (int)m_pDoc->m_nMaxRecordNum) { CString str; str.Format(_T("共有 %d 条记录,但模版设计最大记录数为 %d,\n是否只 %d 条记录?\n" "注意:如何选否,会给数据库很大压力,甚至取消操作!"), nRecCount, m_pDoc->m_nMaxRecordNum, m_pDoc->m_nMaxRecordNum); UINT ulSelect = IDNO; if (m_pDoc->GetHWnd()) ulSelect = MessageBox(m_pDoc->GetHWnd(), str, _T("EdFc"), MB_YESNOCANCEL); if (ulSelect == IDNO) ; else if (ulSelect == IDCANCEL) return FALSE; else // IDYES { csHeadFile.Format(_T(" %d条记录未导出"), nRecCount - m_pDoc->m_nMaxRecordNum); nRecCount = m_pDoc->m_nMaxRecordNum; CString csDBDrv = getDBDriver(m_pDoc->m_szODBCLink); csDBDrv.MakeLower(); if (csDBDrv.Find("oracle") >= 0) { CString szTmp = szSQL; szTmp.MakeLower(); int nFind = szTmp.Find(" where "); if (nFind > 0) { szTmp.Format(" ROWNUM <= %d AND ", m_pDoc->m_nMaxRecordNum); szSQL.Insert(nFind + (int)_tcslen(" where "), (LPCSTR)szTmp); } else { szTmp.Format(" WHERE ROWNUM <= %d ", m_pDoc->m_nMaxRecordNum); szSQL += szTmp; } } else if (csDBDrv.Find("mysql") >= 0) { CString szTmp; szTmp.Format(" Limit 0, %d ", m_pDoc->m_nMaxRecordNum); szSQL += szTmp; } // else if (csDBDrv.IsEmpty()) // ShowMessage(m_pDoc->GetHWnd(), "报表打印组件无法判断该数据库的类型。"); else { CString str; str.Format(_T("[ %s ] 数据库无法使用修改SQL的方法限制记录个数,而打开大数据集可能会出现异常\n,是否先修改查询条件,再进行打印?"), csDBDrv); if (m_pDoc->GetHWnd() && MessageBox(m_pDoc->GetHWnd(), str,_T("BSRpter"), MB_YESNO) == IDYES) return FALSE; } } } CRecordset rc(&db); rc.Open(CRecordset::forwardOnly, szSQL); UINT nFieldCount = rc.GetODBCFieldCount(); CString szTitle = m_pDoc->m_szTitle; TxtToHtml(szTitle); CString szHeadRight = m_pDoc->m_szHeadRight; TxtToHtml(szHeadRight); htm << "<html><head><title>" << (LPCTSTR)szTitle << "</title></head>" << endl; htm << "<body>" << endl << "<table width=100%>" << endl << "<tr>" << endl << "\t<td width=20%> </td>" << endl << "\t<td width=60% align=center><font size=+3>" << (LPCTSTR)szTitle << "</font></td>" << endl << "\t<td width=20% align=right>" << (LPCTSTR)csHeadFile << "</td>" << endl << "</tr>" << endl << "</table>" << endl; htm << "<table border=1 width=100%>" << endl << "<tr>" << endl; int nWidth = 0; for (UINT col = 0; col < nFieldCount; col++) nWidth += m_pDoc->m_ColFmt[col].width; CString str; for (col = 0; col < nFieldCount; col++) { str = m_pDoc->m_ColFmt[col].name; if (str.IsEmpty()) { CODBCFieldInfo fi; rc.GetODBCFieldInfo(col, fi); str = fi.m_strName; } txt << "\"" << (LPCTSTR)str << "\""; TxtToHtml(str); htm << "\t<td width=" << m_pDoc->m_ColFmt[col].width * 100 / nWidth << "% align=center>" << (LPCTSTR)str << "</td>" << endl; if (col == nFieldCount - 1) { txt << endl; htm << "</tr>" << endl; } else txt << ','; } if (m_pDoc->GetHWnd()) ::SendMessage(m_pDoc->GetHWnd(), WM_CREATEPROGRESS, nRecCount, 0); CDBVariant varValue; CString strHtml; UINT nCount = 0; for(UINT row = 0; row < nRecCount; row ++) { htm << "<tr>" << endl; for (UINT col = 0; col < nFieldCount; col ++) { strHtml = _T(""); rc.GetFieldValue(col, varValue); if (varValue.m_dwType == DBVT_BINARY) { str = GetPicFile(varValue.m_pbinary, nCount++); if (str.IsEmpty()) //error continue; m_ZipArc.AddFileToZip(str); DeleteFile(str); str = str.Right(str.GetLength() - str.ReverseFind('\\') - 1); strHtml.Format("<A href=%s>%s</A>", str, str); } else str = m_pDoc->GetVarStr(&varValue, m_pDoc->m_ColFmt[col].format); varValue.Clear(); txt << "\"" << (LPCTSTR)str << "\""; TxtToHtml(str); if (strHtml.IsEmpty()) strHtml = str; htm << "\t<td align=center>" << (LPCTSTR)strHtml << "</td>" << endl; if (col == nFieldCount - 1) { txt << endl; htm << "</tr>" << endl; } else txt << ','; } if (m_pDoc->GetHWnd()) ::SendMessage(m_pDoc->GetHWnd(), WM_STEPPROGRESS, nRecCount, row); rc.MoveNext(); } rc.Close(); CString szFootLeft = m_pDoc->m_szFootLeft; TxtToHtml(szFootLeft); CString szFootRight = m_pDoc->m_szFootRight; if (szFootRight.IsEmpty()) szFootRight = CTime::GetCurrentTime().Format("%m/%d/%y %H:%M:%S"); TxtToHtml(szFootRight); htm << "</table>" << endl << "<p>" << endl << "<table width=100%>" << endl << "<tr>" << endl << "\t<td width=50% align=left>" << (LPCTSTR)szFootLeft << "</td>" << endl << "\t<td width=50% align=right>" << (LPCTSTR)szFootRight << "</td>" << endl << "</table>" << endl << "<p><hr>" << endl << "<b>FmtFile Infomation</b><br>" << endl << "<ul>" << endl << "\t<li>ODBC: " << (LPCTSTR)m_pDoc->m_szODBCLink << "<br>" << endl << "\t<li>SQL : " << (LPCTSTR)m_pDoc->m_szSQL << "</ul>" << endl << "</body>" << endl << "</html>" << endl; if (m_pDoc->GetHWnd()) ::SendMessage(m_pDoc->GetHWnd(), WM_DESTROYPROGRESS, 0, 0); bRet = TRUE; } catch(CException *edb) { static TCHAR lpszError[MAX_PATH]; CString csError; if (edb->GetErrorMessage(lpszError, MAX_PATH)) csError = lpszError; if (csError.GetLength() <= 0) csError = _T("无法获得数据!"); ShowMessage(m_pDoc->GetHWnd(), csError); edb->Delete(); bRet = FALSE; } catch(...) { ShowMessage(m_pDoc->GetHWnd(), _T("无法获得数据!")); bRet = FALSE; } db.Close(); return bRet; }
BOOL CEdRptDoc::GetDataFromDB() { CDatabase db; BOOL bRet = FALSE; try { if (m_szODBCLink.IsEmpty()) { ShowMessage(m_hWnd, "没有给定数据库指针,也没有指定链接字!"); return FALSE; } if (!db.OpenEx(m_szODBCLink, CDatabase::openReadOnly | CDatabase::noOdbcDialog)) { ShowMessage(m_hWnd, "无法打开数据库连接 %s", m_szODBCLink); return FALSE; } m_nRecCount = GetRecordCount(db); CRecordset rc(&db); CString szSQL = m_szSQL; CString csHeadRight = m_szHeadRight; if (m_nRecCount > (int)m_nMaxRecordNum) { CString str; str.Format(_T("共有 %d 条记录,但模版设计最大记录数为 %d,\n是否只 %d 条记录?\n" "注意:如何选否,会给数据库很大压力,甚至取消操作!"), m_nRecCount, m_nMaxRecordNum, m_nMaxRecordNum); UINT ulSelect = IDNO; if (m_hWnd) ulSelect = MessageBox(m_hWnd, str, _T("BSRpter"), MB_YESNOCANCEL); if (ulSelect == IDNO) ; else if (ulSelect == IDCANCEL) return FALSE; else // IDYES { csHeadRight.Format(_T(" %d条记录未打印"), m_nRecCount - m_nMaxRecordNum); m_nRecCount = m_nMaxRecordNum; CString csDBDrv = getDBDriver(m_szODBCLink); csDBDrv.MakeLower(); if (csDBDrv.Find("oracle") >= 0) { CString szTmp = szSQL; szTmp.MakeLower(); int nFind = szTmp.Find(" where "); if (nFind > 0) { szTmp.Format("ROWNUM <= %d AND ", m_nMaxRecordNum); szSQL.Insert(nFind + (int)_tcslen(" where "), (LPCSTR)szTmp); } else { szTmp.Format(" WHERE ROWNUM <= %d ", m_nMaxRecordNum); szSQL += szTmp; } } else if (csDBDrv.Find("mysql") >= 0) { CString szTmp; szTmp.Format(" Limit 0, %d ", m_nMaxRecordNum); szSQL += szTmp; } else { CString str; str.Format(_T("[ %s ] 数据库无法使用修改SQL的方法限制记录个数," "而打开大数据集可能会出现异常\n,是否先修改查询条件,再进行打印?") , csDBDrv); if (m_hWnd != NULL && MessageBox(m_hWnd, str,_T("EdFc"), MB_YESNO) == IDYES) return FALSE; } } } rc.Open(CRecordset::forwardOnly, szSQL); m_nFieldCount = rc.GetODBCFieldCount(); while (m_ColFmt.GetSize() > m_nFieldCount && m_nFieldCount > 1) m_ColFmt.RemoveAt(m_ColFmt.GetSize() - 1); COLFMT cf; while (m_ColFmt.GetSize() < m_nFieldCount) m_ColFmt.Add(cf); m_Grid.DeleteAllItems(); int nRowCount = m_nRecCount + (m_bFootSum ? 2 : 1); m_Grid.SetRowCount(nRowCount); m_Grid.SetColumnCount(m_nFieldCount); // set ColumName; GV_ITEM Item; Item.mask = GVIF_TEXT|GVIF_FORMAT; Item.row = 0; Item.nFormat = DT_CENTER|DT_VCENTER|DT_SINGLELINE|DT_END_ELLIPSIS|DT_NOPREFIX; for (Item.col = 0; Item.col < m_nFieldCount; Item.col++) { m_Grid.SetColumnWidth(Item.col, m_ColFmt[Item.col].width); Item.szText = m_ColFmt[Item.col].name; if (m_ColFmt[Item.col].name.IsEmpty()) { CODBCFieldInfo fi; rc.GetODBCFieldInfo(Item.col, fi); Item.szText = fi.m_strName; } m_Grid.SetItem(&Item); } if (m_hWnd != NULL && m_nRecCount > 0) ::SendMessage(m_hWnd, WM_CREATEPROGRESS, m_nRecCount, 0); CDBVariant varValue; Item.mask = GVIF_TEXT|GVIF_FORMAT; Item.nFormat = DT_CENTER|DT_VCENTER|DT_SINGLELINE|DT_END_ELLIPSIS|DT_NOPREFIX; for(int row = 0; row < m_nRecCount; row ++) { for (int col = 0; col < m_nFieldCount; col ++) { rc.GetFieldValue(col, varValue); Item.row = row + 1; Item.col = col; Item.szText = GetVarStr(&varValue, m_ColFmt[col].format); m_Grid.SetItem(&Item); varValue.Clear(); } if (m_hWnd != NULL) ::SendMessage(m_hWnd, WM_STEPPROGRESS, m_nRecCount, row); rc.MoveNext(); } if (m_bFootSum) GetStaticData(rc); rc.Close(); if (m_hWnd != NULL) ::SendMessage(m_hWnd, WM_DESTROYPROGRESS, 0, 0); bRet = TRUE; } catch(CException *edb) { static TCHAR lpszError[MAX_PATH]; CString csError; if (edb->GetErrorMessage(lpszError, MAX_PATH)) csError = lpszError; if (csError.GetLength() <= 0) csError = _T("无法获得数据!"); ShowMessage(m_hWnd, csError); edb->Delete(); } catch(...) { ShowMessage(m_hWnd, _T("无法获得数据!")); } return bRet; }
UINT CNav_ExportCustomsDlg::ExportData(LPVOID p) { HRESULT hRes; Excel::_ApplicationPtr appExcel; hRes = appExcel.CreateInstance( _T("Excel.Application")); CNav_ExportCustomsDlg *Dialog; Dialog = (CNav_ExportCustomsDlg*)p; if(Dialog != NULL) { CString sStart, sEnd; Dialog->m_EndDate.GetWindowText(sEnd); Dialog->m_StartDate.GetWindowText(sStart); CString sGTD; Dialog->m_EdGTDNUMBER.GetWindowText(sGTD); COleDateTime datStart,datEnd, cDate; datStart.ParseDateTime(sStart); datEnd.ParseDateTime(sEnd); CString sConnect; CString sServer, sDatabase; sServer = sReadFromIni(_T("DB"),_T("SERVER"),_T("svbyminssq3")); //sWriteToIni(_T("DB"),_T("SERVER"),sServer); sDatabase = sReadFromIni(_T("DB"),_T("DATABASE"),_T("SHATE-M-8")); //sWriteToIni(_T("DB"),_T("DATABASE"),sDatabase); Excel::WorkbooksPtr ExcelBooks; Excel::_WorkbookPtr ExcelBook; Excel::_WorksheetPtr ExcelSheet; Excel::RangePtr range; VARIANT bTRUE; bTRUE.vt = 11; bTRUE.boolVal = TRUE; appExcel->Visible[0] = FALSE; ExcelBook= appExcel->Workbooks->Add(); ExcelSheet = ExcelBook->Worksheets->Item[1]; sConnect.Format(_T("DRIVER=SQL Server;SERVER=%s;UID=;WSID=%s;Trusted_Connection=Yes;DATABASE=%s;LANGUAGE=русский"),sServer,GetWinUserName(),sDatabase); CDatabase* dBase; dBase = NULL; try { dBase = new(CDatabase); dBase->SetQueryTimeout(600); dBase->OpenEx(sConnect,CDatabase::noOdbcDialog); sConnect.Format(_T("EXEC [sp_setapprole] '%s', '%s', 'none', 0, 0"),_T("$ndo$shadow"),_T("FF5EC4E40F67BD4EDF3D04F8B84364DAD0")); dBase->ExecuteSQL(sConnect); } catch(CDBException *exsept) { appExcel->Visible[0] = TRUE; Dialog->m_stState.SetWindowTextW(exsept->m_strError); exsept->Delete(); if(dBase != NULL) { if(dBase->IsOpen()) { dBase->Close(); } delete(dBase); } dBase = NULL; Dialog->m_BtOK.ShowWindow(1); Dialog->SecondThread = NULL; return 0; } CString sSQL; try { CRecordset Query(dBase); int iField; CDBVariant dbValue; Dialog->m_stState.SetWindowTextW(_T("Формирование")); sGTD = sGTD + _T("'"); sSQL = _T("select distinct SIH.[Posting Date],(SIL.[TTN Series]+SIL.[TTN Number]) AS TTN,SIL.[Item No_ 2], SIH.[Bill-to Name] "); sSQL = sSQL + _T(" from [")+sDatabase; sSQL = sSQL + _T("$Sales Invoice Header] as SIH join ["); sSQL = sSQL + sDatabase; sSQL = sSQL + _T("$Sales Invoice Line] as SIL on SIL.[Document No_] = SIH.[No_] and SIL.[No_] is not null and SIL.[No_] <> '' and SIL.[TTN Series] <> '' and SIL.[TTN Number] <> '' join ["); sSQL = sSQL + sDatabase; sSQL = sSQL + _T("$Custom Declaration Relation] as CDR on CDR.[Item No_] = SIL.[No_] and CDR.[Document Type] = 5 and CDR.[CD No_] = '") + sGTD; sSQL = sSQL + _T(" where [Sales Process Type Code] = 'Б/Н_ДОСТАВКА' and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) >= '")+ datStart.Format(_T("%Y%m%d"))+_T("'"); sSQL = sSQL + _T(" and Left(CONVERT ( nchar , SIH.[Posting Date], 112),8) <= '")+ datEnd.Format(_T("%Y%m%d"))+_T("'"); //sSQL = sSQL + _T(" and CDR.[CD No_] = '") + sGTD; CString sDat; int iRow; iRow = 1; sDat = _T("Дата учета продажи"); ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString(); sDat = _T("Номер ТТН, ТН"); ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString(); sDat = _T("Код проданного товара (код товара2)"); ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString(); sDat = _T("Клиент"); ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString(); Query.Open(CRecordset::snapshot,sSQL, CRecordset::readOnly); while(!Query.IsEOF()) { iRow++; if((iRow -2) % 100 == 0) { sSQL.Format(_T("Обработанно %d"),iRow-2); Dialog->m_stState.SetWindowTextW(sSQL); } iField = 0; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,2] = sDat.AllocSysString(); iField = 1; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,3] = sDat.AllocSysString(); iField = 2; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,4] = sDat.AllocSysString(); iField = 3; Query.GetFieldValue(iField, dbValue); sDat = GetValue(&dbValue); ExcelSheet->Cells->Item[iRow,5] = sDat.AllocSysString(); Query.MoveNext(); } Query.Close(); } catch(CDBException *exsept) { /*appExcel->Visible[0] = TRUE; appExcel = NULL;*/ appExcel->Visible[0] = TRUE; Dialog->m_stState.SetWindowTextW(exsept->m_strError); Dialog->m_EdError.SetWindowTextW(sSQL); exsept->Delete(); if(dBase != NULL) { if(dBase->IsOpen()) { dBase->Close(); } delete(dBase); } Dialog->m_BtOK.ShowWindow(1); Dialog->SecondThread = NULL; dBase = NULL; return 0; } Dialog->m_stState.SetWindowTextW(_T("Выполненно")); Dialog->m_BtOK.ShowWindow(1); } appExcel->Visible[0] = TRUE; Dialog->SecondThread = NULL; return 1; }
void fmserver (void) { struct fmparam param[1]; // MessageBox (NULL, "Hello", "Message", MB_OK); init_param(param); /* test CDatabase db; db.OpenEx (_T("DSN=droits"), 0); CRecordsetClients rs; rs.Open (AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM clients", 0); rs.MoveFirst(); for (;;) { if (rs.IsEOF()) break; MessageBox (NULL, rs.m_login, "Login", MB_OK); rs.MoveNext(); } rs.Close(); */ WSADATA wsda; SOCKET sListen, sClient; struct hostent * hp; char buf[10000]; int len; SOCKADDR_IN addr, remote_addr; int ret; int iAddrLen; WSAStartup (MAKEWORD(1,1), &wsda); hp = gethostbyname ("localhost"); /*sListen = socket (hp->h_addrtype, SOCK_STREAM, 0);*/ sListen = socket (AF_INET, SOCK_STREAM, 0 /*IPPROTO_IP*/); if (sListen == SOCKET_ERROR) { MessageBox (NULL, "Error creating socket", "Error", MB_OK); exit(0); } memset (&addr, 0, sizeof(addr)); addr.sin_family = AF_INET; addr.sin_port = htons(6030); addr.sin_addr.s_addr = htonl (INADDR_ANY); ret = bind (sListen, (struct sockaddr *)&addr, sizeof(addr)); if (ret == SOCKET_ERROR) { MessageBox (NULL, "Error bind", "Error", MB_OK); exit(0); } ret = listen (sListen, 10); if (ret == SOCKET_ERROR) { sprintf (buf, "Error listen: %d", WSAGetLastError()); MessageBox (NULL, buf, "Error listen", MB_OK); exit(0); } for (;;) { iAddrLen = sizeof(remote_addr); sClient = accept (sListen, (struct sockaddr *) &remote_addr, &iAddrLen); if (sClient == SOCKET_ERROR) { MessageBox (NULL, "Error accept", "Error", MB_OK); exit(0); } // MessageBox (NULL, "Accepted", "server", MB_OK); /* ret = recv (sClient, buf, sizeof(buf), 0); if (ret == SOCKET_ERROR) { MessageBox (NULL, "Error recv", "Error", MB_OK); exit(0); } buf[ret] = 0; MessageBox (NULL, buf, "Received", MB_OK); */ /* input (sClient, buf); MessageBox (NULL, buf, "received", MB_OK); */ char dirfax[100]; char basedroits[100]; char basefax[100]; char faxdir[100]; char language[100]; char login[100]; char pass[100]; strcpy (basedroits, "droits"); strcpy (basefax, "AT05"); strcpy (faxdir, "L:\\AT05"); for (;;) { input (sClient, buf); if (status == SOCKET_ERROR) break; // MessageBox (NULL, buf, "commande", MB_OK); if (!strcmp (buf, "BASEFAX")) { input (sClient, dirfax); input (sClient, language); if (*dirfax) { lire_fichier (dirfax, "baserights.txt", basedroits); lire_fichier (dirfax, "basefax.txt", basefax); lire_fichier (dirfax, "faxdir.txt", faxdir); } output (sClient, "ID\tIdentifiant\t30\n"); output (sClient, "CNX\tDate\t20\n"); output (sClient, "TIME\tDurée\t5\n"); output (sClient, "PGENB\tPages\t5\n"); output (sClient, "HGN\tStatus\t5\n"); output (sClient, "FILE\tFichier\t15\n"); output (sClient, "\n"); } else if (!strcmp (buf, "USER")) { input (sClient, login); } else if (!strcmp (buf, "PASS")) { input (sClient, pass); output (sClient, "LOGIN\n1\n"); } else if (!strcmp (buf, "LISTREC")) { char param[100]; char strsince[100]; input (sClient, param); input (sClient, strsince); CDatabase db; char dsn[100]; sprintf (dsn, "DSN=%s", basefax); db.OpenEx (_T(dsn), 0); CRecordsetRec rs; rs.Open (AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM REC", 0); rs.MoveFirst(); // MessageBox (NULL, "Reading base", "trace", MB_OK); for (;;) { if (rs.IsEOF()) break; /*MessageBox (NULL, rs.m_ID, "id", MB_OK);*/ sprintf (buf, "<MESSAGE><BASE>FAXBT</BASE><ID>%s</ID><CNX>%s</CNX><TIME>%lf</TIME><PGENB>%lf</PGENB><HGN>%lf</HGN><FILE>%s</FILE></MESSAGE>\n", LPCSTR(rs.m_ID), LPCSTR(rs.m_CNX), rs.m_TIME, rs.m_PGENB, rs.m_HGN, LPCSTR(rs.m_FILE)); /*MessageBox (NULL, buf, "Message", MB_OK);*/ output (sClient, buf); rs.MoveNext(); } output (sClient, "\n"); rs.Close(); } else if (!strcmp (buf, "END")) { // MessageBox (NULL, buf, "END", MB_OK); break; } else { // MessageBox (NULL, buf, "autre commande", MB_OK); } } closesocket (sClient); } /* for (;;) { input (commande); MessageBox (NULL, commande, "Commande", MB_OK); } */ }