testexporttoexceldlg.cpp

可以将列表控件中的内容导出到EXCEL中

// testExportToExcelDlg.cpp : implementation file
//

#include "stdafx.h"
#include "testExportToExcel.h"
#include "testExportToExcelDlg.h"

#include "ExcelCtrl.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()

/////////////////////////////////////////////////////////////////////////////
// CTestExportToExcelDlg dialog

CTestExportToExcelDlg::CTestExportToExcelDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CTestExportToExcelDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CTestExportToExcelDlg)
		// 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 CTestExportToExcelDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CTestExportToExcelDlg)
	DDX_Control(pDX, IDC_LIST, m_List);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CTestExportToExcelDlg, CDialog)
	//{{AFX_MSG_MAP(CTestExportToExcelDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_EXPORTTTOEXCEL, OnExportttoexcel)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CTestExportToExcelDlg message handlers

BOOL CTestExportToExcelDlg::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
	this->m_List.DeleteAllItems(); 
	//设置list控件的文字和背景颜色
	m_List.SetBkColor(RGB(255,255,255));
	m_List.SetTextBkColor(RGB(255,255,255));	

	//清空list控件的数据
	for(int delcolumn=100;delcolumn>=0;delcolumn--)
		m_List.DeleteColumn(delcolumn);
	//设置list对话框的列
	DWORD dwStyle;
	RECT rect;
	LV_COLUMN lvc;

	dwStyle = m_List.GetStyle();
	dwStyle |= LVS_EX_GRIDLINES | LVS_EX_FULLROWSELECT |LVS_SHOWSELALWAYS |LVS_EDITLABELS ;

	m_List.SetExtendedStyle(dwStyle);
	m_List.GetClientRect(&rect);

	lvc.mask = LVCF_TEXT | LVCF_SUBITEM | LVCF_WIDTH | LVCF_FMT;
	lvc.fmt = LVCFMT_CENTER;

	lvc.iSubItem = 0;
	lvc.pszText = _T("编号");
	lvc.cx = 70;
	m_List.InsertColumn(1,&lvc);

	lvc.iSubItem = 1;
	lvc.pszText = _T("姓名");
	lvc.cx = 100;
	m_List.InsertColumn(2,&lvc); 

	lvc.iSubItem = 2;
	lvc.pszText = _T("住址");
	lvc.cx = 100;
	m_List.InsertColumn(3,&lvc); 

	lvc.iSubItem = 3;
	lvc.pszText = _T("测试列一");
	lvc.cx = 100;
	m_List.InsertColumn(4,&lvc); 

	lvc.iSubItem = 4;
	lvc.pszText = _T("测试列二");
	lvc.cx = 100;
	m_List.InsertColumn(5,&lvc); 

	char strMsg[100];
	memset(strMsg, 0, sizeof(strMsg));

	for (int i=0; i<20; i++)
	{
		sprintf(strMsg, "编号%.2d", i);
		m_List.InsertItem(i, strMsg);
		sprintf(strMsg, "姓名%.2d", i);
		m_List.SetItemText(i, 1, strMsg);
		sprintf(strMsg, "住址%.2d", i);
		m_List.SetItemText(i, 2, strMsg);
		sprintf(strMsg, "测试列一%.2d", i);
		m_List.SetItemText(i, 3, strMsg);
		sprintf(strMsg, "测试列二%.2d", i);
		m_List.SetItemText(i, 4, strMsg);
	}
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

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

void CTestExportToExcelDlg::OnExportttoexcel() 
{
	CFileDialog mFileDlg(FALSE, NULL, NULL, OFN_ALLOWMULTISELECT, 
		_T("Excel文件 (*.xls)|*.xls||"), this); 
	if(mFileDlg.DoModal() == IDOK)
	{
		CString strPathName, strFileName;
		strPathName = mFileDlg.GetPathName();
		strFileName = mFileDlg.GetFileName();
		if(strFileName.Find('.') == -1)
		{
			strPathName += ".xls";
		}
		else
		{
			if(strFileName.Right(4) != ".xls" && strFileName.Right(4) != ".XLS")
			{
				MessageBox("保存文件格式错误，后缀名应该为“.xls”！","错误信息",MB_OK|MB_ICONERROR);
				return;
			}
		}

		//删除替换表格内容，这里防止替换文件的处理方法 
		CFileFind fFind;
		if(fFind.FindFile(strPathName))
		{
			if(MessageBox(strFileName+"文件已经存在，是否替换原文件？","提示信息",MB_OKCANCEL|MB_ICONQUESTION) == IDCANCEL)
			{
				return;
			}
			DeleteFile(strFileName);
		}

		//写入EXCEL表格内容
		BeginWaitCursor();
		CExcelCtrl SS(strPathName, "导出查询表格信息");  
		
		CStringArray sampleArray; 
		SS.BeginTransaction();
		
		// 加入标题
		sampleArray.RemoveAll();
		for(int i=0; i<m_List.GetHeaderCtrl()->GetItemCount(); i++)
		{
			HDITEM hdi;
			char lpBuffer[256];
			hdi.mask = HDI_TEXT;
			hdi.pszText = lpBuffer;
			hdi.cchTextMax = 256;

			m_List.GetHeaderCtrl()->GetItem(i, &hdi);
			sampleArray.Add(hdi.pszText);
		}
		SS.AddHeaders(sampleArray);

		//加入数据		
		for(i=0; i<m_List.GetItemCount(); i++)
		{
			sampleArray.RemoveAll();
			for(int j=0; j<m_List.GetHeaderCtrl()->GetItemCount(); j++)
			{
				sampleArray.Add(m_List.GetItemText(i, j));
			}
			SS.AddRow(sampleArray);
		}		
		SS.Commit();
		AfxMessageBox("数据保存成功，保存文件为："+strPathName);
		EndWaitCursor(); 
	}
}