excelrwdlg.cpp

用vc操作excel表格的例子－修改表格中的公式

// ExcelRWDlg.cpp : implementation file
//

#include "stdafx.h"
#include "ExcelRW.h"
#include "ExcelRWDlg.h"
#include <comdef.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()

/////////////////////////////////////////////////////////////////////////////
// CExcelRWDlg dialog

CExcelRWDlg::CExcelRWDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CExcelRWDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CExcelRWDlg)
	m_strDest = _T("");
	m_strSource = _T("");
	m_iSheetNum = 0;
	m_iEndRow = 0;
	m_iStartRow = 0;
	m_strEndCol = _T("");
	m_strStartCol = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);

	m_appExcel = 0;
}

void CExcelRWDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CExcelRWDlg)
	DDX_Text(pDX, IDC_DEST_STR, m_strDest);
	DDX_Text(pDX, IDC_SOURCE_STR, m_strSource);
	DDX_Text(pDX, IDC_SHEETNUM, m_iSheetNum);
	DDX_Text(pDX, IDC_ENDROW, m_iEndRow);
	DDX_Text(pDX, IDC_STARTROW, m_iStartRow);
	DDX_Text(pDX, IDC_ENDCOL, m_strEndCol);
	DDX_Text(pDX, IDC_STARTCOL, m_strStartCol);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CExcelRWDlg, CDialog)
	//{{AFX_MSG_MAP(CExcelRWDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_OPENFILE, OnOpenfile)
	ON_BN_CLICKED(IDC_MODIFY, OnModify)
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_ABOUT, OnAbout)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CExcelRWDlg message handlers

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

void CExcelRWDlg::OnOpenfile() 
{
	// TODO: Add your control notification handler code here

	if("" != m_appExcel.GetName())
	{
		m_book.DetachDispatch();
		m_books.DetachDispatch();
		m_appExcel.DetachDispatch();
	}

	
	CFileDialog filedlg(TRUE, ".xls", "*.xls",OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,
		"(*.xls)|*.xls|All Files (*.*)|*.*||");
	if (filedlg.DoModal() == IDCANCEL)
	{
		return;
	}
	m_strFileName = filedlg.GetPathName();
	

	GetDlgItem(IDC_FILEPATH)->SetWindowText(m_strFileName);

	COleVariant covFile(m_strFileName);

//    COleVariant
 //                covTrue((short)TRUE),
//                 covFalse((short)FALSE),
 //                covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

 //*
	if(!m_appExcel.CreateDispatch("Excel.Application"))
    {
		AfxMessageBox("Couldn't start Excel and get Application object.");
		return;
    }

    //Get a new workbook.
    m_books = m_appExcel.GetWorkbooks();
    m_book = m_books.Add(covFile);
	m_appExcel.SetVisible(TRUE);
//*/	
}

void CExcelRWDlg::OnModify() 
{
	// TODO: Add your control notification handler code here

	if("" == m_strFileName)
	{
		MessageBox("请先打开一个文件");
		return;
	}
	
    Worksheets sheets;
    _Worksheet sheet;
    Range range;
    Range cols;

    
//* 
    sheets = m_book.GetSheets();
	int iCount = sheets.GetCount();

	UpdateData();

	//Get the  sheet.
	if(m_iSheetNum < 1 )
	{
		MessageBox("请输入一个大于0的表单数字!");
		return;
	}
    sheet = sheets.GetItem(COleVariant((short)m_iSheetNum));
	CString strSheetName = sheet.GetName();

	CString strMsg;
	strMsg.Format("你要修改的表单名称是：%s\n 你要修改的源串是：%s\n 目标串是 %s\n 单元格的起始位置是第%d行%s列，结束位置是第%d行%s列",
		strSheetName,m_strSource,m_strDest,
		m_iStartRow,m_strStartCol,m_iEndRow,m_strEndCol);

	if(MessageBox(strMsg,NULL,MB_YESNO) == IDNO)
	{
		return;
	}

	//将字母列转化为数字
	int iStartCol = 0;
	int iEndCol = 0;
	m_strStartCol.MakeUpper();
	m_strEndCol.MakeUpper();
	if(m_strStartCol.GetLength() > 1 || m_strEndCol.GetLength() > 1 )
	{
		MessageBox("不好意思，该版本只支持列数为单个字母，\n想要操作更大范围的列数，请与王小清联系!");
		return;
	}
    char * cTemp = m_strStartCol.GetBuffer(0);
	iStartCol = * cTemp - 64;
	cTemp = m_strEndCol.GetBuffer(0);
	iEndCol = * cTemp - 64;

	//对用户输入的位置进行判断
	if(iStartCol < 1 || iEndCol < 1 || m_iStartRow < 1 || m_iEndRow < 1 ||
		m_iEndRow < m_iStartRow || iEndCol < iStartCol)
	{
		MessageBox("输入的位置有误！请重新输入");
		return;
	}

//	m_appExcel.SetVisible(FALSE);

	Range SelRge;
	Range rgMyRge = sheet.GetCells();
	VARIANT varValue;	
	CString strVal;
	VARIANT varItem;

	int iHead = -1;
	

	for(int i=m_iStartRow; i<=m_iEndRow; i++)
	{
		for(int j=iStartCol; j<=iEndCol; j++)
		{
			//将单元格的公式读到strVal中
			varItem = rgMyRge.GetItem(_variant_t((long)i),_variant_t((long)j));
			SelRge.AttachDispatch(varItem.pdispVal,TRUE);
			varValue = SelRge.GetFormula();

			strVal = varValue.bstrVal;			

			//修改strVal再写入单元格
			iHead = strVal.Find(m_strSource.GetBuffer(0));
			if( -1 == iHead )
			{
				continue;
			}
			strVal.Delete(iHead,m_strSource.GetLength());
			strVal.Insert(iHead,m_strDest.GetBuffer(0));
			SelRge.SetFormula(COleVariant(strVal.GetBuffer(0)));
			SelRge.ReleaseDispatch();
		}

	}

	//Fill D2:D6 with a formula(=RAND()*100000) and apply a number
    //format.
//    range = sheet.GetRange(COleVariant("D2"), COleVariant("E6"));
//    range.SetFormula(COleVariant("=COUNTIF('0501'!B698:AF733,4)/2"));
//	range.GetItem(
//    range.SetNumberFormat(COleVariant("$0.00"));

	m_appExcel.SetUserControl(TRUE);
    
	//*/	
}

void CExcelRWDlg::OnDestroy() 
{
	CDialog::OnDestroy();
	
	// TODO: Add your message handler code here
	m_book.DetachDispatch();
	m_books.DetachDispatch();
	m_appExcel.DetachDispatch();	
}

void CExcelRWDlg::OnAbout() 
{
	// TODO: Add your control notification handler code here

	CAboutDlg dlg;
	dlg.DoModal();
}