chnidlg.cpp

读取数据库数据显示到把列表控件

// chniDlg.cpp : implementation file
//

#include "stdafx.h"
#include "chni.h"
#include "chniDlg.h"
#include <io.h>
#include <odbcinst.h>
#include <afxdb.h>

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CChniDlg dialog

CChniDlg::CChniDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CChniDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CChniDlg)
	m_edit_pathvalue = _T("");
	m_edit_select = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
    nSel=9;
    record=1;
	bAccessFlag=false;
}

void CChniDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CChniDlg)
	DDX_Control(pDX, IDC_STATIC_PAGESHOW, m_pageshow);
	DDX_Control(pDX, IDC_STATIC_PAGENUM, m_pagenum);
	DDX_Control(pDX, IDC_STATIC_record, m_record);
	DDX_Control(pDX, IDC_COMBOBOXEX_SELECT, m_combo_value);
	DDX_Control(pDX, IDC_STATIC_value, m_static_value);
	DDX_Control(pDX, IDC_EDIT_PATH, m_edit_path);
	DDX_Control(pDX, IDC_LIST_VALUE, m_list_value);
	DDX_Text(pDX, IDC_EDIT_PATH, m_edit_pathvalue);
	DDX_Text(pDX, IDC_EDIT_SELECT, m_edit_select);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CChniDlg, CDialog)
	//{{AFX_MSG_MAP(CChniDlg)
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BUTTON_PATH, OnButtonPath)
	ON_BN_CLICKED(IDC_BUTTON_CONNECT, OnButtonConnect)
	ON_BN_CLICKED(IDC_BUTTON_select, OnBUTTONselect)
	ON_BN_CLICKED(IDC_BUTTON_LOAD, OnButtonLoad)
	ON_CBN_SELCHANGE(IDC_COMBOBOXEX_SELECT, OnSelchangeComboboxexSelect)
	ON_BN_CLICKED(IDC_BUTTON_NEXTPAGE, OnButtonNextpage)
	ON_BN_CLICKED(IDC_BUTTON_PREVPAGE, OnButtonPrevpage)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CChniDlg message handlers

BOOL CChniDlg::OnInitDialog()
{
	CDialog::OnInitDialog();
	::SendMessage(m_list_value.m_hWnd, LVM_SETEXTENDEDLISTVIEWSTYLE,LVS_EX_FULLROWSELECT, LVS_EX_FULLROWSELECT);
	//////////为列表控件添加列//////////
//	m_list_value.InsertColumn(0,"排序号",LVCFMT_LEFT,80);
	m_list_value.InsertColumn(1,"地区 ",LVCFMT_LEFT,80);
	m_list_value.InsertColumn(2,"电话",LVCFMT_LEFT,120);
	m_list_value.InsertColumn(3,"性别",LVCFMT_LEFT,80);
	m_list_value.InsertColumn(4,"年龄",LVCFMT_LEFT,60);



	//////////读取数据库中的信息添加到列表控件///////////
	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	
	// TODO: Add extra initialization here
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CChniDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CChniDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

BOOL CChniDlg::PreTranslateMessage(MSG* pMsg) 
{
	// TODO: Add your specialized code here and/or call the base class
	if(pMsg->message == WM_KEYDOWN){         
        switch(pMsg->wParam){
		case VK_RETURN://截获回车 
			return TRUE;
        case VK_ESCAPE://截获ESC 
			return TRUE;
			break;
		}
	}	
	return CDialog::PreTranslateMessage(pMsg);
}

void CChniDlg::OnButtonPath() 
{
	// TODO: Add your control notification handler code here
	char  szFilter[] = "MDB Files (*.chni)|*.chni|All Files (*.*)|*.*||";
	CFileDialog dlg( TRUE,"MDB",NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,szFilter );
	if(dlg.DoModal() == IDOK)
	{
		CString strPathName = dlg.GetPathName();
		strFileName = dlg.GetFileName();
		strFileTitle=dlg.GetFileTitle();
		strfileext=dlg.GetFileExt();
		m_edit_pathvalue= strPathName;
		UpdateData(FALSE);	    
	}	
}

void CChniDlg::OnButtonConnect() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	if (m_edit_pathvalue== "") 
	{
		MessageBox(_T("数据库目录不能为空!"),"中网互联",MB_OK| MB_ICONQUESTION);
		return;
	}
	  CString str;
	  str="chni";
  
	if (strfileext!=str)
	{
		MessageBox(_T("打开的扩展名不对!"),"中网互联",MB_OK| MB_ICONQUESTION);
		return;
	}
	if (!DBConnect(strFileName)) 
	{
		m_static_value.SetWindowText(_T("失败"));
		bAccessFlag = false;
		return;
	}
	else
	{
		m_static_value.SetWindowText(_T("成功"));
		bAccessFlag = true;
		m_edit_path.EnableWindow(FALSE);
	}	
}

void CChniDlg::OnBUTTONselect() 
{
	  m_list_value.DeleteAllItems();//删除列表显示的数据
		UpdateData();

     if (m_edit_select=="")
	{
		MessageBox(_T("查询数据不能为空!"),"中网互联",MB_OK| MB_ICONQUESTION);
		return;
	}
	 if (bAccessFlag==false )
	{
		MessageBox(_T("选择的数据不能为空!"),"中网互联",MB_OK| MB_ICONQUESTION);
		return;
	}
	 if (nSel==9)
	{
		MessageBox(_T("查询标志数据库没选择!"),"中网互联",MB_OK| MB_ICONQUESTION);
		return;
	}

    // Selectmark(m_edit_select);
	 record=0;
	 try
	 {	Dateselect();}
     	catch(...)
	{
		AfxMessageBox("数据库读取失败");
		return;
	}
	CString str,str1;
//	str1.Format(" %s ",);

//	m_pagenum.SetWindowText(str1);

	str.Format ("%d",record);
	m_record.SetWindowText(str);
	// TODO: Add your control notification handler code here
	
}
///////////////连接数据库
bool CChniDlg::DBConnect(CString strDataSource)
{
	HRESULT hr;
	hr = pConnection.CreateInstance(__uuidof(Connection));
	if (SUCCEEDED(hr)) 
	{
		CString strConnection;
		strConnection.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s",strDataSource);
		try
		{	
			pConnection->CursorLocation = adUseClient;
			pConnection->Open((_bstr_t)strConnection,"","",NULL);  
			return TRUE;
		}
		catch(...)
		{
			return FALSE;
		}
	}	
	else
	{
		return FALSE;
		
	}
}
////////////////////////查询数据.////////////////////////////////
void CChniDlg::Dateselect()
{
		_RecordsetPtr	pRecordset;
		_variant_t varea,vtel,vsex,vage;
		CString vareaint,mark;
		mark=m_edit_select;
		int nItem,nrecord;
		nrecord=0;
        CString sSql,str,str1;
	str1=strFileName;
	CString  strsex[3]={{"女"},{"男"},{"不定"}};
	CString  strarea[6]={{""},{"长沙"},{"星沙"},{"宁乡"},{"浏阳"},{"望城"}};

//	CString  strselectdate[4]={{""},{"长沙"},{"星沙"},{"宁乡"},{"浏阳"},{"望城"}};
        if (nSel==0)
	
		{str=" age like '%" +m_edit_select +"%' ";	
			goto select;
		}

        if (nSel==1)
		{   
			for (int j=0;j<3;j++)
			{    
				if (strsex[j]==m_edit_select)
				{
				     nrecord=j;
					mark.Format("%d",nrecord);
	            	str=" sex like '%" +mark +"%' ";
					goto select;
				}           
				
			}

		}

        if (nSel==2)
        {      
			for (int j=0;j<7;j++)
			{   
				if (strarea[j]==m_edit_select)
				{    nrecord=j;     
			    mark.Format("%d",nrecord);			
		    	str="area like '%" +mark +"%' ";
				goto select;
				}
			}

		}			
        if (nSel==3)
        { str="tel like '%" +m_edit_select+"%' ";
			goto select;
		}
       
	//	{sSql="SELECT * FROM chni01 WHERE tel like '%" +mark +"%'  ";}
select:        sSql="select * FROM " + strFileTitle+"  where  "+ str+" ";
	
        
		pRecordset.CreateInstance(__uuidof(Recordset));
		pRecordset->Open((LPCTSTR)sSql,_variant_t(pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);
	    while(!pRecordset->adoEOF)
		{	
		  		 record++;		
		//	if (record<1000)
			{varea =pRecordset->GetCollect("area");          
			vtel =pRecordset->GetCollect("tel");
			vsex = pRecordset->GetCollect("sex");
			vage =pRecordset->GetCollect("age");

	        //  for  (nrecord=0;nrecord<10;nrecord++)
			//  {
             nItem=m_list_value.InsertItem(record,(_bstr_t)strarea[((int)varea.lVal)]);
		//	m_list_value.SetItem(nItem,2,1,(_bstr_t)vsex,NULL,0,0,0);
              m_list_value.SetItem(nItem,2,1,(_bstr_t)strsex[((int)vsex.lVal)],NULL,0,0,0);
              m_list_value.SetItem(nItem,1,1,(_bstr_t)vtel,NULL,0,0,0);
              m_list_value.SetItem(nItem,3,1,(_bstr_t)vage,NULL,0,0,0);

			 // }
		   pRecordset->MoveNext();}
		  //   else
		//	 {break;
		//	 }
		}
}

void CChniDlg::OnButtonLoad() 
{
	// TODO: Add your control notification handler code here
    ExportListToExcel();
}

void CChniDlg::OnSelchangeComboboxexSelect() 
{
 
   
    nSel=(int)m_combo_value.GetCurSel();   

	// TODO: Add your control notification handler code here
	
}

void CChniDlg::OnButtonNextpage() 
{
	// TODO: Add your control notification handler code here
	
}

void CChniDlg::OnButtonPrevpage() 
{
	// TODO: Add your control notification handler code here
	
}
CString CChniDlg::GetExcelDriver()
{
    char szBuf[2001];
    WORD cbBufMax = 2000;
    WORD cbBufOut;
    char *pszBuf = szBuf;
    CString sDriver;
	
    // 获取已安装驱动的名称(涵数在odbcinst.h里)
    if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
        return "";
    
    // 检索已安装的驱动是否有Excel...
    do
    {
        if (strstr(pszBuf, "Excel") != 0)
        {
            //发现 !
            sDriver = CString(pszBuf);
            break;
        }
        pszBuf = strchr(pszBuf, '\0') + 1;
    }
    while (pszBuf[1] != '\0');
	
    return sDriver;
}

//		自动创建目录
///////////////////////////////////////////////////////////////////////////////
BOOL CChniDlg::MakeSurePathExists( CString &Path,
						bool FilenameIncluded)
{
	int Pos=0;
	while((Pos=Path.Find('\\',Pos+1))!=-1)
		CreateDirectory(Path.Left(Pos),NULL);
	if(!FilenameIncluded) 	CreateDirectory(Path,NULL);
	//	return ((!FilenameIncluded)?!_access(Path,0):
	//	!_access(Path.Left(Path.ReverseFind('\\')),0));
	
	return !_access(Path,0);
}

//获得默认的文件名
BOOL CChniDlg::GetDefaultXlsFileName(CString& sExcelFile)
{
	///默认文件名：yyyymmddhhmmss.xls
	CString timeStr;
	CTime day;
	day=CTime::GetCurrentTime();
	int filenameday,filenamemonth,filenameyear,filehour,filemin,filesec;
	filenameday=day.GetDay();//dd
	filenamemonth=day.GetMonth();//mm月份
	filenameyear=day.GetYear();//yyyy
	filehour=day.GetHour();//hh
	filemin=day.GetMinute();//mm分钟
	filesec=day.GetSecond();//ss
	timeStr.Format("%04d%02d%02d%02d%02d%02d",filenameyear,filenamemonth,filenameday,filehour,filemin,filesec);
	
	sExcelFile =  timeStr + ".xls";
	// prompt the user (with all document templates)
	CFileDialog dlgFile(FALSE,".xls",sExcelFile);
	CString title;
	CString strFilter;
	
	title = "导出";
	strFilter = "Excel文件(*.xls)";
	strFilter += (TCHAR)'\0';   // next string please
	strFilter += _T("*.xls");
	strFilter += (TCHAR)'\0';   // last string
	dlgFile.m_ofn.nMaxCustFilter++;
	dlgFile.m_ofn.nFilterIndex = 1;
	// append the "*.*" all files filter
	CString allFilter;
	VERIFY(allFilter.LoadString(AFX_IDS_ALLFILTER));
	strFilter += allFilter;
	strFilter += (TCHAR)'\0';   // next string please
	strFilter += _T("*.*");
	strFilter += (TCHAR)'\0';   // last string
	dlgFile.m_ofn.nMaxCustFilter++;
	
	dlgFile.m_ofn.lpstrFilter = strFilter;
	dlgFile.m_ofn.lpstrTitle = title;
	
	if (dlgFile.DoModal()==IDCANCEL)
		return FALSE; // open cancelled
	sExcelFile.ReleaseBuffer();
	if (MakeSurePathExists(sExcelFile,true)) {
		if(!DeleteFile(sExcelFile)) {    // delete the file
			AfxMessageBox("覆盖文件时出错！");
			return FALSE;
		}
	}
	return TRUE;
}


///////////////////////////////////////////////////////////////////////////////
void CChniDlg::ExportListToExcel()
{
	CString warningStr;
	if (m_list_value.GetItemCount ()>0) {	
		CDatabase database;
		CString sDriver;
		CString sExcelFile; 
		CString sSql;
		CString tableName = "CHNI";
		
		// 检索是否安装有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='';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 =100;
			columnData.pszText = columnName.GetBuffer (100);
			for(i=0;m_list_value.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<m_list_value.GetItemCount ();nItemIndex++){
				strV = "";
				for(i=0;i<columnNum;i++)
				{
					if (i!=0)
					{
						strV = strV + ", " ;
					}
					strV = strV + " '" + m_list_value.GetItemText(nItemIndex,i) +"' ";
				}
				
				sSql = "INSERT INTO "+ tableName 
					+" ("+ strH + ")"
					+" VALUES("+ strV + ")";
				database.ExecuteSQL(sSql);
			}
			
		}      
		
		// 关闭数据库
		database.Close();
			
		warningStr.Format("导出文件保存于%s!",sExcelFile);
		AfxMessageBox(warningStr);
	}
	else
	{AfxMessageBox("没有导出的数据");}
}