myresizingdlg.cpp

以SQL语句从数据库里查询出记录

// MyResizingDlg.cpp : implementation file
//

#include "stdafx.h"
#include "TestListCtrlToExcel.h"
#include "MyResizingDlg.h"
#include "SQLDlg.h"
#include "excel.h"

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

/////////////////////////////////////////////////////////////////////////////
// CMyResizingDlg dialog


CMyResizingDlg::CMyResizingDlg(CWnd* pParent /*=NULL*/)
	: CResizingDialog(CMyResizingDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CMyResizingDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	m_strSQL.Empty();
}


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


BEGIN_MESSAGE_MAP(CMyResizingDlg, CResizingDialog)
	//{{AFX_MSG_MAP(CMyResizingDlg)
	ON_BN_CLICKED(IDC_BUTTON2, OnButton2)
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMyResizingDlg message handlers

BOOL CMyResizingDlg::OnInitDialog() 
{
	CResizingDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	SetControlInfo(IDC_LIST1,RESIZE_BOTH);
	SetControlInfo(IDC_BUTTON2,ANCHORE_RIGHT|ANCHORE_BOTTOM);
	SetControlInfo(IDC_BUTTON1,ANCHORE_RIGHT|ANCHORE_BOTTOM);

	OnInitDatabase();
	
	//ShowWindow(SW_MAXIMIZE);
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

void CMyResizingDlg::OnInitDatabase()
{
	if(m_strSQL.IsEmpty())return;
	CListCtrl *pListCtrl = (CListCtrl *)GetDlgItem(IDC_LIST1);
	m_pDbAccess->QueryToListCtrl(m_strSQL,pListCtrl);
}
void CMyResizingDlg::OnButton2() 
{
	// TODO: Add your control notification handler code here
	CSQLDlg sqlDlg;
	sqlDlg.m_strSQL = m_strSQL;
	if(sqlDlg.DoModal()!=IDOK)return;
	m_strSQL = sqlDlg.m_strSQL;
	OnInitDatabase();
}

void CMyResizingDlg::OnButton1() 
{
	try
	{
		_Application app;
		_Workbook book;
		_Worksheet sheet;
		Workbooks books;
		Worksheets sheets;
		
		Range range;
		LPDISPATCH lpDisp;
		
		COleVariant
			covTrue((short)TRUE),
			covFalse((short)FALSE),
			covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
		
		if(!app.CreateDispatch("Excel.Application"))
		{
			AfxMessageBox("伙计，你没有安装Excel2002,Excel2003.");
			return;
		}
		
		app.SetVisible(TRUE);
		
		lpDisp = app.GetWorkbooks();
		ASSERT(lpDisp);
		books.AttachDispatch(lpDisp);

		lpDisp = books.Add( covOptional );
		ASSERT(lpDisp);
		book.AttachDispatch( lpDisp );
		lpDisp = book.GetSheets();
		ASSERT(lpDisp);
		sheets.AttachDispatch(lpDisp);
		

		lpDisp = sheets.GetItem( COleVariant((short)(1)) );
		ASSERT(lpDisp);
		sheet.AttachDispatch(lpDisp);
		
		CListCtrl *pListCtrl = (CListCtrl *)GetDlgItem(IDC_LIST1);	
		int nCols = pListCtrl->GetHeaderCtrl()->GetItemCount();
		
		for(int iColumn=0;iColumn<nCols;iColumn++){
			CString strLine,strRange;
			strLine.Format("%d",1);
			strRange=CString('A'+iColumn)+strLine;
			
			lpDisp=sheet.GetRange(COleVariant(strRange),COleVariant(strRange)); //.GetRange(COleVariant(strRange));
			range.AttachDispatch(lpDisp);

			CString strHeadName;
			HDITEM hdItem;
			
			TCHAR lpszText[256];
			memset(lpszText,0,256*sizeof(TCHAR));
			hdItem.pszText = lpszText;
			hdItem.cchTextMax = 255;
			hdItem.mask = HDI_TEXT;

			pListCtrl->GetHeaderCtrl()->GetItem(iColumn,&hdItem);
			strHeadName = _T(hdItem.pszText);
			
			range.SetValue2(COleVariant(strHeadName));
			range.SetColumnWidth(COleVariant((short)(strHeadName.GetLength()+10)));
		}

		
		range.ReleaseDispatch();
		CString strTemp;
		CString strLine,strRange;
		
		for(int i=0;i<pListCtrl->GetItemCount();i++){
			for(int j = 0;j<nCols;j++){
				strTemp=pListCtrl->GetItemText(i,j);
				strLine.Format("%d",i+2);
				strRange=CString('A'+j)+strLine;
				lpDisp=sheet.GetRange((COleVariant(strRange)),(COleVariant(strRange)));
				range.AttachDispatch(lpDisp);
				range.SetValue2(COleVariant(strTemp));
			}
		}

		
		range.ReleaseDispatch();
		sheet.ReleaseDispatch();

		//app.ReleaseDispatch();
		
      }	  
	  catch(COleException *e)
      {
		  char buf[1024];
		  sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
		  ::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
      }
      catch(COleDispatchException *e)
      {
		  char buf[1024];
		  sprintf(buf,
			  "COleDispatchException. SCODE: %08lx, Description: \"%s\".",
			  (long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));
		  ::MessageBox(NULL, buf, "COleDispatchException",
			  MB_SETFOREGROUND | MB_OK);
      }
      catch(...)
      {
		  ::MessageBox(NULL, "General Exception caught.", "Catch-All",
			  MB_SETFOREGROUND | MB_OK);
      }
	  



	/*


	CListCtrl *pListCtrl = (CListCtrl *)GetDlgItem(IDC_LIST1);

	LPDISPATCH pDisp;
	LPUNKNOWN  pUnk;
	CLSID      clsid;
	_Application excelApp;
	Workbooks    workBooks;
	Worksheets   workSheets;
	_Worksheet   workSheet;
	Range        range;
	COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
	
	BeginWaitCursor();
	
	//初始化EXCEL...
	::CLSIDFromProgID((LPCOLESTR)("excel.application"),&clsid);
	if(::GetActiveObject(clsid,NULL,&pUnk)==S_OK){
		VERIFY(pUnk->QueryInterface(IID_IDispatch,(void FAR* FAR*)&pDisp)==S_OK);
		excelApp.AttachDispatch(pDisp);
		pUnk->Release();
	}
	else{
		excelApp.ReleaseDispatch();
		if(!excelApp.CreateDispatch("excel.application")){
			EndWaitCursor();
			MessageBox("没装Excel2000吧??");
			return;
		}
	}
	excelApp.SetVisible(TRUE);
	
	//添加一张页面
	excelApp.SetSheetsInNewWorkbook(2);
	LPDISPATCH lpWorkbooks;
	lpWorkbooks=excelApp.GetWorkbooks();
	workBooks.AttachDispatch(lpWorkbooks);
	
	LPDISPATCH pWorkbook=NULL;
	if(workBooks.GetCount()==0){
		pWorkbook=workBooks.Add(vOpt);
	}
	
	//往表内写东西
	LPDISPATCH pWorksheets=excelApp.GetWorksheets(); //GetWorksheets(COleVariant(CString("sheet1")));
	workSheets.AttachDispatch(pWorksheets);
	workSheet = Worksheets.GetItem(COleVariant((short)1));
	
	LPDISPATCH pRange;
	
	int nCols = pListCtrl->GetHeaderCtrl()->GetItemCount();

	for(int iColumn=0;iColumn<nCols;iColumn++){
		CString strLine,strRange;
		strLine.Format("%d",1);
		strRange=CString('A'+iColumn)+strLine;
		
		COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
		pRange=workSheet.GetRange(COleVariant(strRange),COleVariant(strRange)); //.GetRange(COleVariant(strRange));
		range.ReleaseDispatch();
		range.AttachDispatch(pRange);
		CString strHeadName;
		pListCtrl->GetHeaderCtrl()->GetDlgItemText(iColumn,strHeadName);
		range.SetValue(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR),COleVariant(strHeadName));
	}
	//填写数据...
	int pointsnum=nCols;
	range.ReleaseDispatch();
	for(int i=0;i<pointsnum;i++){
		for(int j = 0;j<pListCtrl->GetItemCount();j++){
			CString strTemp=pListCtrl->GetItemText(i,j);//户名
			CString strLine,strRange;
			strLine.Format("%d",i+2);
			strRange=CString('A'+j)+strLine;
			pRange=workSheet.GetRange((COleVariant(strRange)),(COleVariant(strRange)));

			range.AttachDispatch(pRange);
			range.SetValue(COleVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR),COleVariant(strTemp));
			range.ReleaseDispatch();
		}
	}
	workSheet.ReleaseDispatch();
	workBooks.ReleaseDispatch();
	excelApp.ReleaseDispatch();	    
	
	EndWaitCursor();
	*/
}