ODBCRWExcelDlg.cpp
上传用户:fdpbxz
上传日期:2022-07-13
资源大小:33k
文件大小:12k
- // ODBCRWExcelDlg.cpp : implementation file
- //
- #include "stdafx.h"
- #include "ODBCRWExcel.h"
- #include "ODBCRWExcelDlg.h"
- #ifdef _DEBUG
- #define new DEBUG_NEW
- #undef THIS_FILE
- static char THIS_FILE[] = __FILE__;
- #endif
- /////////////////////////////////////////////////////////////////////////////
- // CAboutDlg dialog used for App About
- class CAboutDlg : public CDialog
- {
- public:
- CAboutDlg();
- // Dialog Data
- //{{AFX_DATA(CAboutDlg)
- enum { IDD = IDD_ABOUTBOX };
- //}}AFX_DATA
- // ClassWizard generated virtual function overrides
- //{{AFX_VIRTUAL(CAboutDlg)
- protected:
- virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support
- //}}AFX_VIRTUAL
- // Implementation
- protected:
- //{{AFX_MSG(CAboutDlg)
- //}}AFX_MSG
- DECLARE_MESSAGE_MAP()
- };
- CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
- {
- //{{AFX_DATA_INIT(CAboutDlg)
- //}}AFX_DATA_INIT
- }
- void CAboutDlg::DoDataExchange(CDataExchange* pDX)
- {
- CDialog::DoDataExchange(pDX);
- //{{AFX_DATA_MAP(CAboutDlg)
- //}}AFX_DATA_MAP
- }
- BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
- //{{AFX_MSG_MAP(CAboutDlg)
- // No message handlers
- //}}AFX_MSG_MAP
- END_MESSAGE_MAP()
- /////////////////////////////////////////////////////////////////////////////
- // CODBCRWExcelDlg dialog
- CODBCRWExcelDlg::CODBCRWExcelDlg(CWnd* pParent /*=NULL*/)
- : CDialog(CODBCRWExcelDlg::IDD, pParent)
- {
- //{{AFX_DATA_INIT(CODBCRWExcelDlg)
- m_csName = _T("");
- m_csAge = _T("");
- //}}AFX_DATA_INIT
- // Note that LoadIcon does not require a subsequent DestroyIcon in Win32
- m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
- m_csSex = _T("");
- m_csDatebaseFile = _T("");
- }
- void CODBCRWExcelDlg::DoDataExchange(CDataExchange* pDX)
- {
- CDialog::DoDataExchange(pDX);
- //{{AFX_DATA_MAP(CODBCRWExcelDlg)
- DDX_Control(pDX, IDC_COMBO_SEX, m_comboSex);
- DDX_Control(pDX, IDC_INFO_LIST, m_ListCtrlInfo);
- DDX_Text(pDX, IDC_EDIT_NAME, m_csName);
- DDX_Text(pDX, IDC_EDIT_AGE, m_csAge);
- //}}AFX_DATA_MAP
- }
- BEGIN_MESSAGE_MAP(CODBCRWExcelDlg, CDialog)
- //{{AFX_MSG_MAP(CODBCRWExcelDlg)
- ON_WM_SYSCOMMAND()
- ON_WM_PAINT()
- ON_WM_QUERYDRAGICON()
- ON_BN_CLICKED(IDC_BUTTON_READ_EXCEL, OnButtonReadExcel)
- ON_BN_CLICKED(IDC_BUTTON_WRITE_EXCEL, OnButtonWriteExcel)
- ON_BN_CLICKED(IDC_BUTTON_QUIT, OnButtonQuit)
- ON_BN_CLICKED(IDC_BUTTON_CLEAR_LIST, OnButtonClearList)
- ON_NOTIFY(NM_CLICK, IDC_INFO_LIST, OnClickInfoList)
- //}}AFX_MSG_MAP
- END_MESSAGE_MAP()
- /////////////////////////////////////////////////////////////////////////////
- // CODBCRWExcelDlg message handlers
- BOOL CODBCRWExcelDlg::OnInitDialog()
- {
- CDialog::OnInitDialog();
- // Add "About..." menu item to system menu.
- // IDM_ABOUTBOX must be in the system command range.
- ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
- ASSERT(IDM_ABOUTBOX < 0xF000);
- CMenu* pSysMenu = GetSystemMenu(FALSE);
- if (pSysMenu != NULL)
- {
- CString strAboutMenu;
- strAboutMenu.LoadString(IDS_ABOUTBOX);
- if (!strAboutMenu.IsEmpty())
- {
- pSysMenu->AppendMenu(MF_SEPARATOR);
- pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
- }
- }
- // 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
- //初始化ListCtrl
- m_ListCtrlInfo.SetExtendedStyle( LVS_EX_GRIDLINES );
- m_ListCtrlInfo.SetExtendedStyle( m_ListCtrlInfo.GetExtendedStyle() |
- LVS_EX_FULLROWSELECT );
- m_ListCtrlInfo.InsertColumn(0, "姓名", LVCFMT_CENTER, 185);
- m_ListCtrlInfo.InsertColumn(1, "性别", LVCFMT_CENTER, 120);
- m_ListCtrlInfo.InsertColumn(2, "年龄", LVCFMT_CENTER, 120);
- // 初始化下拉列表框
- m_comboSex.AddString("男");
- m_comboSex.AddString("女");
- m_comboSex.SetCurSel(0);
- // 若数据库表没有被创建,则将"读取"按钮和"列表"控件置为无效状态
- if (!IsCreatedTable())
- {
- GetDlgItem(IDC_BUTTON_READ_EXCEL)->EnableWindow(FALSE);
- GetDlgItem(IDC_INFO_LIST)->EnableWindow(FALSE);
- }
- return TRUE; // return TRUE unless you set the focus to a control
- }
- void CODBCRWExcelDlg::OnSysCommand(UINT nID, LPARAM lParam)
- {
- if ((nID & 0xFFF0) == IDM_ABOUTBOX)
- {
- CAboutDlg dlgAbout;
- dlgAbout.DoModal();
- }
- else
- {
- CDialog::OnSysCommand(nID, lParam);
- }
- }
- // 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 CODBCRWExcelDlg::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 CODBCRWExcelDlg::OnQueryDragIcon()
- {
- return (HCURSOR) m_hIcon;
- }
- void CODBCRWExcelDlg::OnButtonReadExcel()
- {
- // TODO: Add your control notification handler code here
- CDatabase database;
- CString csSql = _T("");
- CString csName = _T("");
- CString csSex = _T("");
- CString csAge = _T("");
- CString csDriver = _T("");
- CString csDsn = _T("");
-
- // 获取主程序所在路径
- // 获取数据库文件
- m_csDatebaseFile = GetDatabaseFile();
- // 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)"
- csDriver = GetExcelDriver();
- if (csDriver.IsEmpty())
- {
- // 没有发现Excel驱动
- AfxMessageBox("没有安装Excel驱动!");
- return;
- }
-
- // 创建进行存取的字符串
- csDsn.Format(_T("ODBC;DRIVER={%s};DSN='';DBQ=%s"), csDriver, m_csDatebaseFile);
- TRY
- {
- // 打开数据库
- database.Open(NULL, false, false, csDsn);
-
- CRecordset rs(&database);
- // 设置读取的查询语句.
- csSql = "SELECT Name, Sex, Age "
- "FROM ClassmateInfo " ;
- "ORDER BY Name ";
-
- // 执行查询语句
- rs.Open(CRecordset::forwardOnly, csSql, CRecordset::readOnly);
- // 清空列表
- if (m_ListCtrlInfo.GetItemCount() !=0)
- {
- m_ListCtrlInfo.DeleteAllItems();
- }
- // 获取查询结果
- while (!rs.IsEOF())
- {
- // 读取Excel内部数值
- rs.GetFieldValue("Name", csName);
- rs.GetFieldValue("Sex", csSex);
- rs.GetFieldValue("Age", csAge);
- csAge = csAge.Mid(0,csAge.ReverseFind('.'));
- // 显示记录的内容
- m_ListCtrlInfo.InsertItem(0, _T(""));
- m_ListCtrlInfo.SetItemText(0, 0, csName);
- m_ListCtrlInfo.SetItemText(0, 1, csSex);
- m_ListCtrlInfo.SetItemText(0, 2, csAge);
- // 移到下一条记录
- rs.MoveNext();
- }
-
- // 显示读Excel文件的信息
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("Excel文件读取成功!"));
- // 关闭数据库
- database.Close();
- }
- CATCH(CDBException, e)
- {
- // 数据库操作产生异常
- AfxMessageBox("数据库错误: " + e->m_strError);
- }
- END_CATCH;
- }
- void CODBCRWExcelDlg::OnButtonWriteExcel()
- {
- // TODO: Add your control notification handler code here
- UpdateData();
- // 判决姓名是否为空
- if (m_csName.IsEmpty())
- {
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("姓名不能为空!"));
- return;
- }
-
- // 判决年龄是否为空
- if (m_csAge.IsEmpty())
- {
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("年龄不能为空!"));
- return;
- }
- CDatabase database;
- CString csDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动
- CString csSql = _T("");
- // 获取数据库文件
- m_csDatebaseFile = GetDatabaseFile();
- // 创建进行存取的字符串
- csSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB="%s";DBQ=%s",
- csDriver, m_csDatebaseFile, m_csDatebaseFile);
- TRY
- {
- // 创建数据库 (Excel表格文件)
- if ( database.OpenEx(csSql,CDatabase::noOdbcDialog) )
- {
- // 判断表ClassmateInfo是否已经被创建,若否,则创建。
- if (!IsCreatedTable())
- {
- // 创建表结构(姓名、性别和年龄)
- csSql = "CREATE TABLE ClassmateInfo (Name TEXT,Sex TEXT,Age NUMBER)";
- database.ExecuteSQL(csSql);
- }
- // 写入ini文件
- ::WritePrivateProfileString( "CreateTable", "IsCreated", "TRUE", "./CreateTable.ini" );
- // 获取性别
- int nCurSelSex = m_comboSex.GetCurSel();
- m_comboSex.GetLBText(nCurSelSex, m_csSex);
- // 插入数值
- csSql = "INSERT INTO ClassmateInfo (Name,Sex,Age) VALUES ('"+m_csName+"','"+m_csSex+"','"+m_csAge+"')";
- database.ExecuteSQL(csSql);
- }
- // 关闭数据库
- database.Close();
- // 在列表控件中显示记录
- OnButtonReadExcel();
- // 显示写Excel文件的信息
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("Excel文件写入成功!"));
- // 清空编辑框
- m_csName = _T("");
- m_csAge = _T("");
- UpdateData();
- GetDlgItem(IDC_BUTTON_READ_EXCEL)->EnableWindow(TRUE);
- GetDlgItem(IDC_INFO_LIST)->EnableWindow(TRUE);
- }
- CATCH_ALL(e)
- {
- AfxMessageBox("异常发生!");
- }
- END_CATCH_ALL;
- }
- CString CODBCRWExcelDlg::GetExcelDriver()
- {
- char szBuf[2001];
- WORD cbBufMax = 2000;
- WORD cbBufOut;
- char *pszBuf = szBuf;
- CString csDriver = _T("");
- // 获取已安装驱动的名称
- if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))
- {
- return _T("");
- }
-
- // 检索已安装的驱动是否有Excel
- do
- {
- if (strstr(pszBuf, "Excel") != 0)
- {
- // 发现驱动
- csDriver = CString(pszBuf);
- break;
- }
- pszBuf = strchr(pszBuf, ' ') + 1;
- }
- while (pszBuf[1] != ' ');
- return csDriver;
- }
- BOOL CODBCRWExcelDlg::IsCreatedTable()
- {
- // 读取CreateTable.ini文件
- CString csCreateInfo = _T("");
- ::GetPrivateProfileString(_T("CreateTable"), _T("IsCreated"), _T(""),
- csCreateInfo.GetBuffer(MAX_PATH),
- MAX_PATH,
- _T("./CreateTable.ini"));
-
- if (csCreateInfo.Compare(_T("TRUE")) != 0)
- {
- return FALSE;
- }
- return TRUE;
- }
- void CODBCRWExcelDlg::OnButtonQuit()
- {
- // TODO: Add your control notification handler code here
- CDialog::OnCancel();
- }
- void CODBCRWExcelDlg::OnButtonClearList()
- {
- // TODO: Add your control notification handler code here
- // 判断列表是否为空
- int nCount = m_ListCtrlInfo.GetItemCount();
- if (nCount == 0)
- {
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("列表本来就是空的,点击清除没有意义!"));
- return;
- }
- // 清空列表
- m_ListCtrlInfo.DeleteAllItems();
- // 显示列表已经被清空的信息
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("列表已经被清空!"));
- }
- void CODBCRWExcelDlg::OnClickInfoList(NMHDR* pNMHDR, LRESULT* pResult)
- {
- // TODO: Add your control notification handler code here
- // 判断列表是否为空
- if (m_ListCtrlInfo.GetItemCount() == 0)
- {
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("当前列表中没有数据!"));
- return;
- }
- // 取得第一条记录的位置
- POSITION pos = m_ListCtrlInfo.GetFirstSelectedItemPosition();
- if ( NULL == pos )
- {
- // 若没有选择记录,则给出提示
- GetDlgItem(IDC_STATIC_OPERATION_INFO)->SetWindowText(_T("你还没有选择一条记录,请选择!"));
- return;
- }
- while (pos)
- {
- // 取得被选择记录的索引
- int nIndexInFieldList = m_ListCtrlInfo.GetNextSelectedItem(pos);
- // 高亮被的选择记录
- m_ListCtrlInfo.SetFocus();
-
- // 取得记录中的子项
- m_csName = m_ListCtrlInfo.GetItemText(nIndexInFieldList,0);
- m_csSex = m_ListCtrlInfo.GetItemText(nIndexInFieldList,1);
- m_csAge = m_ListCtrlInfo.GetItemText(nIndexInFieldList,2);
- }
-
- m_comboSex.SetWindowText(m_csSex);
- UpdateData(FALSE);
- *pResult = 0;
- }
- CString CODBCRWExcelDlg::GetDatabaseFile()
- {
- CString csPath = _T("");
- // 获取主程序所在路径
- GetModuleFileName(NULL,csPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH);
- csPath.ReleaseBuffer ();
- int nPos = 0;
- nPos = csPath.ReverseFind ('\');
- csPath = csPath.Left (nPos);
- CString csDatebaseFile = csPath + "\PersonalInfo.xls"; // 要创建的Excel文件
- return csDatebaseFile;
- }