useexceldlg.cpp

用VC写的日记笔小程序,采用的序列化存储.

// UseExcelDlg.cpp : implementation file
//

#include "stdafx.h"
#include "UseExcel.h"
#include "UseExcelDlg.h"
#include "AdoManager.h"
#include <Afxdlgs.h>
//#include "RecordSet.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif
#define szIniFileName ".\\config.ini"
#define DBERR_EXCEL   -1
#define DBERR_SQLSERVER   -2
CAdoManager m_cAdoConnExcel;
CAdoManager m_cAdoConnSQL;
CString	m_strFilePath="";
//数据库参数
char m_szDbServer_Sedi[255];
char m_szDbUserName_Sedi[255];
char m_szDbPwd_Sedi[255];
char m_szDbName_Sedi[255];
char m_szDbServer_Star[255];
char m_szDbUserName_Star[255];
char m_szDbPwd_Star[255];
char m_szDbName_Star[255];

int  OnOpenDB(int iSelect);
void OnCloseDB();
//CUseExcelDlg dlg;
/////////////////////////////////////////////////////////////////////////////
// CUseExcelDlg dialog

CUseExcelDlg::CUseExcelDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CUseExcelDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CUseExcelDlg)
	m_strInfo = _T("");
	m_iSelect = 1;
	m_strPath = _T("");
	m_strQiangpiao = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CUseExcelDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CUseExcelDlg)
	DDX_Control(pDX, IDC_LIST1, m_list);
	DDX_Text(pDX, IDC_STATIC1, m_strInfo);
	DDX_Radio(pDX, IDC_RADIO_SEDIMIKEY, m_iSelect);
	DDX_Text(pDX, IDC_EDIT_FILEPATH, m_strPath);
	DDX_Text(pDX, IDC_EDIT_QIANGPIAO, m_strQiangpiao);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CUseExcelDlg, CDialog)
	//{{AFX_MSG_MAP(CUseExcelDlg)
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_BTN_TESTGETDATE, OnBtnTestgetdate)
	ON_BN_CLICKED(IDC_BTN_OPENFILE, OnBtnOpenfile)
	ON_BN_CLICKED(IDC_RADIO_SEDIMIKEY, OnRadioSedimikey)
	ON_BN_CLICKED(IDC_RADIO_STARFACETOFACE, OnRadioStarfacetoface)
	ON_BN_CLICKED(IDC_QIANGPIAO, OnQiangpiao)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CUseExcelDlg message handlers
BOOL CUseExcelDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// 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

	Init();
	CTime t=CTime::GetCurrentTime();
	m_iDay=t.GetDay()-1;//为了防止周一启动，这周不做
	CString strInfo;
	strInfo="在这里显示移动信息";
	m_lListIndex=m_list.AddString(strInfo);
	m_list.AddString("");
	m_list.SetCurSel(m_lListIndex);
	m_strInfo="请点击开始启动线程...";
	UpdateData(FALSE);
	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 CUseExcelDlg::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 CUseExcelDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CUseExcelDlg::OnOK() 
{
//	if(OnOpenDB()==FALSE)
//		return ;
	UpdateData(TRUE);
	m_strFilePath=m_strPath;
	if(m_strFilePath==""){
		MessageBox("请选择Excel文件名");
		return;
	}

//	AfxBeginThread(ProssMsgThread,this);
//	CString strInfo;
//	m_lListIndex=m_list.AddString("线程已启动...");
//	m_list.SetCurSel(m_lListIndex);
//	m_strInfo="线程已启动...\n每周移动一次数据，每十分钟检查一次时间，如果今天是周一，则开始从EXCEL表向SQL Server数据库中移动数据，否则，什么也不做";
	if(m_iSelect==0){
		m_lListIndex=m_list.AddString("塞迪米祺的数据");
		m_list.SetCurSel(m_lListIndex);
	}
	else{
		m_lListIndex=m_list.AddString("明星面对面的数据");
		m_list.SetCurSel(m_lListIndex);
	}
	OnBeginMove();
	UpdateData(FALSE);
//	((CButton*)GetDlgItem(IDOK))->EnableWindow(FALSE);
//	OnMoveData();
	//CDialog::OnOK();
}

UINT ProssMsgThread(LPVOID lParam)
{
	CUseExcelDlg* pExcel=(CUseExcelDlg*)lParam;
	if(!::AfxOleInit())
	{
		AfxMessageBox("Ole库初使化失败");
	}
	while(1)
	{
		CTime t=CTime::GetCurrentTime();
		int iDay=t.GetDay();
		int iDayOfWeek=t.GetDayOfWeek();
		int iHour=t.GetHour();
//		if(iDay != pExcel->m_iDay && iDayOfWeek==2)//每周第二天(周一)之间移动数据,
//		{
			CString strInfo;
			strInfo.Format("今天时间为：%04d-%02d-%02d  %02d:%02d   周 %d ,移动数据,每周一移动数据",t.GetYear(),t.GetMonth(),t.GetDay(),t.GetHour(),t.GetMinute(),iDayOfWeek-1==0 ? 7 : iDayOfWeek-1);
			pExcel->m_lListIndex=pExcel->m_list.AddString(strInfo);
			pExcel->m_lListIndex=pExcel->m_list.AddString("开始移动数据，祝你成功！");
			pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
			int iDBRet=OnOpenDB(pExcel->m_iSelect);
			if(iDBRet==DBERR_EXCEL){
				pExcel->m_lListIndex=pExcel->m_list.AddString("打开excel库失败");
				pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
				return FALSE;
			}
			else if(iDBRet==DBERR_SQLSERVER){
				pExcel->m_lListIndex=pExcel->m_list.AddString("打开SQL Server数据库失败");
				pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
				return FALSE;
			}
			else{
				pExcel->m_lListIndex=pExcel->m_list.AddString("成功打开数据库！");
				pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
			}
			BOOL bRet = TRUE;
			if(pExcel->m_iSelect == 0)
				bRet=pExcel->OnMoveData_Sedi();
			else bRet=pExcel->OnMoveData_Star();
			if(bRet==FALSE){ 
				pExcel->m_lListIndex=pExcel->m_list.AddString("移动数据失败！");
				pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
			}
			else{
				CString strInfo1;
				strInfo1.Format("时间为：%04d-%02d-%02d  %02d:%02d ",t.GetYear(),t.GetMonth(),t.GetDay(),t.GetHour(),t.GetMinute());
				pExcel->m_list.AddString(strInfo1);
				pExcel->m_lListIndex=pExcel->m_list.AddString("恭喜你，移动数据成功！");
				pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
				pExcel->m_iDay=iDay;
			}
			OnCloseDB();
			pExcel->m_lListIndex=pExcel->m_list.AddString("成功关闭数据库！");
			pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
				
/*		}
		else{			
			if(iDay!=pExcel->m_iDay && iDayOfWeek!=2){
				CString strInfo;
				strInfo.Format("今天时间为：%04d-%02d-%02d  %02d:%02d   周 %d ,不移动数据,每周一移动数据",t.GetYear(),t.GetMonth(),t.GetDay(),t.GetHour(),t.GetMinute(),iDayOfWeek-1==0 ? 7 : iDayOfWeek-1);
				pExcel->m_lListIndex=pExcel->m_list.AddString(strInfo);
				pExcel->m_list.SetCurSel(pExcel->m_lListIndex);
			}		
			pExcel->m_iDay=iDay;
		}
		Sleep(6000);//每十分钟检查一次时间
*/	}
	
	return TRUE;
}

BOOL CUseExcelDlg::Init()
{
//	GetPrivateProfileString("EXCEL","EXCEL_FILENAME","DEMO",m_szFileName,sizeof(m_szFileName),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_SEDIMIKEY","DB_SVRNAME","127.0.0.1",m_szDbServer_Sedi,sizeof(m_szDbServer_Sedi),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_SEDIMIKEY","DB_NAME","SediMikey",m_szDbName_Sedi,sizeof(m_szDbName_Sedi),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_SEDIMIKEY","DB_USERNAME","SA",m_szDbUserName_Sedi,sizeof(m_szDbUserName_Sedi),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_SEDIMIKEY","DB_PWD","",m_szDbPwd_Sedi,sizeof(m_szDbPwd_Sedi),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_STARFTOF","DB_SVRNAME","127.0.0.1",m_szDbServer_Star,sizeof(m_szDbServer_Star),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_STARFTOF","DB_NAME","SediMikey",m_szDbName_Star,sizeof(m_szDbName_Star),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_STARFTOF","DB_USERNAME","SA",m_szDbUserName_Star,sizeof(m_szDbUserName_Star),szIniFileName);
	GetPrivateProfileString("DATABASEINFO_STARFTOF","DB_PWD","",m_szDbPwd_Star,sizeof(m_szDbPwd_Star),szIniFileName);
	return TRUE;
}

BOOL CUseExcelDlg::OnMoveData_Sedi()
{
	char szSql[2048];
	CString strInfo;
	memset(szSql,0,sizeof(szSql));
	struct sTBName{ //保存EXCEL表名和SQL Server数据库的表名
		char szFrom[100];
		char szTo[100];
	};
	sTBName sTBName[6];
	memset(sTBName,0,sizeof(sTBName));
	strcpy(sTBName[0].szFrom,"[IT$]");	strcpy(sTBName[0].szTo,"t_q_computer");
	strcpy(sTBName[1].szFrom,"[SM$]");	strcpy(sTBName[1].szTo,"t_q_DigitalMarket");
	strcpy(sTBName[2].szFrom,"[JL$]");	strcpy(sTBName[2].szTo,"t_q_ITManager");
	strcpy(sTBName[3].szFrom,"[RJ$]");	strcpy(sTBName[3].szTo,"t_q_HotSoftware");
	strcpy(sTBName[4].szFrom,"[QB$]");	strcpy(sTBName[4].szTo,"t_q_SoccerDr");
	strcpy(sTBName[5].szFrom,"[XH$]");	strcpy(sTBName[5].szTo,"t_q_joke");

//	if(OnOpenDB()==FALSE) return FALSE;//打开数据库
	
	m_cAdoConnSQL.BeginTrans();
	for(int i=0;i<6;i++){
		sprintf(szSql,"select * from %s",sTBName[i].szFrom);		
		char szContent[2048];	memset(szContent,0,sizeof(szContent));
//		char szUpTime[20];		memset(szUpTime,0,sizeof(szUpTime));
		char szAnswer[20];		memset(szAnswer,0,sizeof(szAnswer));
		DATE d;//用来取日期
		CRecordSet * pSet=m_cAdoConnExcel.CreateRecordSet(szSql);
		if(!pSet)
		{
			m_list.AddString("打开表失败");
			CString strInfo;
			strInfo.Format("Excel中 %s 表不存在",sTBName[i].szFrom);
			m_lListIndex=m_list.AddString(strInfo);
			m_list.SetCurSel(m_lListIndex);
//			m_cAdoConnSQL.RollBackTrans();
//			m_cAdoConnExcel.CloseDb();
//			m_cAdoConnSQL.CloseDb();
			continue;
		}
		try
		{
			strInfo.Format("正在移动 %s —— %s...",sTBName[i].szFrom,sTBName[i].szTo);
			m_lListIndex=m_list.AddString(strInfo);
			m_list.SetCurSel(m_lListIndex);
			long iCount=0;
			while(!pSet->IsEOF())
			{
				char szSqlInsert[2048];	memset(szSqlInsert,0,sizeof(szSqlInsert));
				pSet->GetColVar(szContent,0);
                OnFilter(szContent);
				if(strcmp(sTBName[i].szFrom,"[QB$]")==0){//如果是t_q_SoccerDr，特殊处理一下
					pSet->GetColVar(szAnswer,1);
					pSet->GetColVar(&d,2);
					COleDateTime lTime(d);
					CString szUpTime;
					szUpTime.Format("%04d-%02d-%02d",lTime.GetYear(),lTime.GetMonth(),lTime.GetDay());
					sprintf(szSqlInsert,"insert into %s(content,answer,uptime) values('%s','%s','%s')",
						sTBName[i].szTo,szContent,szAnswer,szUpTime);
				}
				else{
					pSet->GetColVar(&d,1);
					COleDateTime lTime(d);
					CString szUpTime;
					szUpTime.Format("%04d-%02d-%02d",lTime.GetYear(),lTime.GetMonth(),lTime.GetDay());
					sprintf(szSqlInsert,"insert into %s(content,uptime) values('%s','%s')",
						sTBName[i].szTo,szContent,szUpTime);
				}
				int ret=m_cAdoConnSQL.Execute(szSqlInsert);
				if(ret!=0){
					strInfo.Format("向表%s中插入数据失败,语句为：%s",sTBName[i].szTo,szSqlInsert);
					m_lListIndex=m_list.AddString(strInfo);
					m_list.SetCurSel(m_lListIndex);
					break;
				}
				pSet->MoveNext();
				iCount++;
			}
			strInfo.Format("共移动了 %d 条纪录.",iCount);
			m_lListIndex=m_list.AddString(strInfo);
			m_list.SetCurSel(m_lListIndex);
		}
		catch(...)
		{
			m_cAdoConnSQL.RollBackTrans();
			m_cAdoConnExcel.DestroyRecordSet(pSet);
//			m_cAdoConnExcel.CloseDb();
//			m_cAdoConnSQL.CloseDb();
			m_lListIndex=m_list.AddString("从excel取值错误");
			m_list.SetCurSel(m_lListIndex);
			return FALSE;
		}

		m_cAdoConnExcel.DestroyRecordSet(pSet);
	}//for(int i=0;i<6,i++)
	m_cAdoConnSQL.CommitTrans();
//	m_cAdoConnSQL.CloseDb();
//	m_cAdoConnExcel.CloseDb();
	strInfo="数据移动结束。";
    m_lListIndex=m_list.AddString(strInfo);
	m_list.SetCurSel(m_lListIndex);
	return TRUE;
}

int OnOpenDB(int iSelect)
{
//	CUseExcelDlg dlg;
	char szOpenDb[256];	
	memset(szOpenDb,0,sizeof(szOpenDb));
	sprintf(szOpenDb,"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Extended Properties = Excel 8.0;User Id=;Password=;",m_strFilePath);
	HRESULT hr;
	hr=m_cAdoConnExcel.OpenDb(szOpenDb);//连接EXCEL
	if(!SUCCEEDED(hr))
	{
		return DBERR_EXCEL;
	}	
	memset(szOpenDb,0,sizeof(szOpenDb));
	if(iSelect == 0)
		sprintf(szOpenDb,"Provider=SQLOLEDB.1;Server=%s;User ID=%s;Password=%s;Initial Catalog=%s",m_szDbServer_Sedi,m_szDbUserName_Sedi,m_szDbPwd_Sedi,m_szDbName_Sedi);
	else 
		sprintf(szOpenDb,"Provider=SQLOLEDB.1;Server=%s;User ID=%s;Password=%s;Initial Catalog=%s",m_szDbServer_Star,m_szDbUserName_Star,m_szDbPwd_Star,m_szDbName_Star);
	hr=m_cAdoConnSQL.OpenDb(szOpenDb);//连接SQL Server数据库
	if(!SUCCEEDED(hr))
	{
		return DBERR_SQLSERVER;
	}
	return TRUE;
}

void CUseExcelDlg::OnFilter(char szContent[])
{
	int iLen=strlen(szContent);
	int nFlag=0;
	for (int i=0;i<iLen;i++)
	{
		if(szContent[i]=='\'')
			szContent[i]='"';
	}
}

void OnCloseDB()
{
//	CUseExcelDlg dlg;
	m_cAdoConnSQL.CloseDb();
	m_cAdoConnExcel.CloseDb();
}

void CUseExcelDlg::OnDestroy() 
{
	CDialog::OnDestroy();
	OnCloseDB();
	// TODO: Add your message handler code here
}

void CUseExcelDlg::OnBtnTestgetdate() 
{
	// TODO: Add your control notification handler code here
	DATE d1;
	DATE d2;
	char szSql[2048];
	memset(szSql,0,sizeof(szSql));
	sprintf(szSql,"select * from SM$");