cdatainputdlg.cpp

可以读入txt文本中的项 然后写入EXCEL 大大缓解了添加数据的繁琐。

// CDataInputDlg.cpp : implementation file
//

#include "stdafx.h"
#include "CDataInput.h"
#include "CDataInputDlg.h"
#include "CommonDialog.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()

/////////////////////////////////////////////////////////////////////////////
// CCDataInputDlg dialog

CCDataInputDlg::CCDataInputDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CCDataInputDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CCDataInputDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CCDataInputDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CCDataInputDlg)
		// NOTE: the ClassWizard will add DDX and DDV calls here
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CCDataInputDlg, CDialog)
	//{{AFX_MSG_MAP(CCDataInputDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_Store, OnStore)
	ON_BN_CLICKED(IDC_Show, OnShow)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CCDataInputDlg message handlers

BOOL CCDataInputDlg::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
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CCDataInputDlg::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 CCDataInputDlg::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 CCDataInputDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

CString CCDataInputDlg::COpenFile(CString m_Filter)
{ 
	CRect m_Rect;
	UINT m_ID;
	CCommonDialog1 m_Dlg;
	m_Dlg.Create(NULL,WS_BORDER,m_Rect,this,m_ID,NULL,FALSE,NULL);
	m_Dlg.SetFilter(m_Filter);
	m_Dlg.ShowOpen();

	return m_Dlg.GetFileName();
}

void CCDataInputDlg::CReadFile()
{
	CString m_ReadContent;
	int m_DataLength;
	int m_CurrentLength;
	m_Index=0;

	CString m_DataFileName=COpenFile("文本文件 (*.txt;*.ini)|*.txt;*ini|所有文件 (*.*)|*.*");
	while (m_DataFileName!="")
	{

		m_DataFile.Open(m_DataFileName,CFile::modeRead,NULL);                //打开文件
		m_DataLength=m_DataFile.GetLength();
		m_CurrentLength=0;


		while (m_CurrentLength<m_DataLength) 
		{

			m_DataFile.ReadString(m_ReadContent);                            //连续读取文件内容
			m_CurrentLength=m_CurrentLength+m_ReadContent.GetLength()+1;

			if (m_ReadContent!="" && m_Index==0)
			{
				CreateToExcel(m_ReadContent);
			}
			else if(m_ReadContent!="")
			{
				WriteToExcel(m_ReadContent);
			}
			m_Index++;	
		}

		m_DataFile.Close();
		m_DataFileName=COpenFile("文本文件 (*.txt;*.ini)|*.txt;*ini|所有文件 (*.*)|*.*");
	}
	AfxMessageBox("没有选择文件!");

	// 关闭数据库
	database.Close();

}

//创建Excel文件
void CCDataInputDlg::CreateToExcel(CString m_Content)
{

	CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)";      // Excel安装驱动
	CString m_ExcelFile="Record.xls";
   
	TRY
	{
		// 创建进行存取的字符串
		sSql.Format("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
			sDriver, m_ExcelFile, m_ExcelFile);
		
		// 创建数据库 (即Excel表格文件)
		if(database.OpenEx(sSql,CDatabase::noOdbcDialog) )
		{
			int m_Douhao=0;
			int j=0;
			int m_Mid=0;

			//分析表头
			for (int i=0;i<m_Content.GetLength();i=m_Douhao+1)
			{
				m_Douhao=m_Content.Find(",",i);
				if (m_Douhao!=-1)
				{
					if (j==0)
					{
						m_Contents[j]=m_Content.Mid(i,m_Douhao-m_Mid);
					} 
					else
					{	
						m_Contents[j]=m_Content.Mid(i,m_Douhao-m_Mid-1);
					}

					j++;
					m_Mid=m_Douhao;
				}
				else
				{
					m_Contents[j]=m_Content.Right(m_Content.GetLength()-m_Mid-1);
					break;
				}

			}


			// 创建表结构()
			sSql="CREATE TABLE Record (";
			for (i=0;i<j;i++)
			{
				sSql+=m_Contents[i]+" TEXT,";
			}
			sSql+=m_Contents[j]+" TEXT)";
			database.ExecuteSQL(sSql);	
		}      

	}
	CATCH_ALL(e)
	{
		TRACE1("Excel驱动没有安装: %s",sDriver);
	}
	END_CATCH_ALL;

}

//写入Excel文件
void CCDataInputDlg::WriteToExcel(CString m_Content)
{
	
	TRY
	{
		int m_Douhao=0;
		int j=0;
		int m_Mid=0;

		//分析内容
		for (int i=0;i<m_Content.GetLength();i=m_Douhao+1)
		{
			m_Douhao=m_Content.Find(",",i);
			if (m_Douhao!=-1)
			{
				if (j==0)
				{
					m_ContentsIndex[j]=m_Content.Mid(i,m_Douhao-m_Mid);
				} 
				else
				{	
					m_ContentsIndex[j]=m_Content.Mid(i,m_Douhao-m_Mid-1);
				}
				
				j++;
				m_Mid=m_Douhao;
			}
			else
			{
				m_ContentsIndex[j]=m_Content.Right(m_Content.GetLength()-m_Mid-1);
				break;
			}
			
		}

		// 插入数值
		CString sSqlA,sSqlB;
		for (i=0;i<j;i++)
		{
			sSqlA+=m_Contents[i]+",";
			sSqlB+="\'"+m_ContentsIndex[i]+"\',";
		}
		sSqlA+=m_Contents[j];
		sSqlB+="\'"+m_ContentsIndex[j]+"\'";
		sSql="INSERT INTO Record ("+sSqlA+") VALUES ("+sSqlB+")";
		database.ExecuteSQL(sSql);

	}
	CATCH_ALL(e)
	{
		TRACE1("Excel文件:%s没有写入！",m_DataFile.GetFileName());
	}
	END_CATCH_ALL;
}

void CCDataInputDlg::CShowExcel()
{
		CString sItem1, sItem2;
		CString sDriver= "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动;
		CString sDsn;

		CString m_ExcleName=COpenFile("Excel 文件(*.xls)|*.xls|所有文件 (*.*)|*.*");

		// 创建进行存取的字符串
		sDsn.Format("ODBC;DRIVER={%s};DSN='''';DBQ=%s", sDriver, m_ExcleName);
		
		TRY
		{
			// 打开数据库(即Excel文件)
			database.Open(NULL, false, false, sDsn);

			CRecordset recset(&database);

			// 设置读取的查询语句.
			sSql.Format("SELECT count(*) FROM %s" , database) ;
			//select * from [Sheet1$]

			// 执行查询语句
			recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
			
			// 获取查询结果
			while (!recset.IsEOF())
			{
				//读取Excel内部数值
				recset.GetFieldValue("Name ", sItem1);
				recset.GetFieldValue("Age", sItem2);
				
				// 移到下一行
				recset.MoveNext();
			}
			
			// 关闭数据库
			database.Close();
			
		}
		CATCH(CDBException, e)
		{
			// 数据库操作产生异常时...
			AfxMessageBox("数据库错误: " + e->m_strError);
		}
		END_CATCH;

}

void CCDataInputDlg::OnStore() 
{
	// TODO: Add your control notification handler code here
	CReadFile();
}

void CCDataInputDlg::OnShow() 
{
	// TODO: Add your control notification handler code here
	CShowExcel();
}