excel批处理dlg.cpp

该程序可以批量的将选中.txt文件中的数据添加到同名的Excel文件中

// Excel批处理Dlg.cpp : implementation file
//

#include "stdafx.h"
#include "Excel批处理.h"
#include "Excel批处理Dlg.h"
#include "excel9.h"
#include <strstream>                    //注意该头文件不可与下一个头文件顺序颠倒
#include "iostream.h"
#include "afxdlgs.h"
#include <ctype.h>
#include "string.h"
#include <stdlib.h>
#include "fstream"
#include <comdef.h>
#include "Windows.h"
#include "afxwin.h"
using namespace std;




#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()

/////////////////////////////////////////////////////////////////////////////
// CExcelDlg dialog

CExcelDlg::CExcelDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CExcelDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CExcelDlg)
	GetCurrentDirectory(200,autotemplet);
	templetfile=autotemplet;
    templetfile+=templetfile[2];
	m_edit1 = _T("");
	m_edit2 = _T(templetfile+"templet.xls");
	N=-1;
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
	m_brush.CreateSolidBrush(RGB(220,230,255));
}

void CExcelDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CExcelDlg)
	DDX_Control(pDX, IDC_EDIT3, m_edit3);
	DDX_Control(pDX, IDC_LIST1, m_ListBox);
	DDX_Text(pDX, IDC_EDIT1, m_edit1);
	DDX_Text(pDX, IDC_EDIT2, m_edit2);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CExcelDlg, CDialog)
	//{{AFX_MSG_MAP(CExcelDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	ON_BN_CLICKED(IDC_BUTTON2, OnButton2)
	//}}AFX_MSG_MAP	
	ON_WM_CTLCOLOR_REFLECT()               //控制对话框颜色
	ON_WM_CTLCOLOR()                       //控制各控件颜色

END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CExcelDlg message handlers

BOOL CExcelDlg::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_ListBox.EnableWindow(TRUE);
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

HBRUSH CExcelDlg::CtlColor(CDC* pDC,UINT nCtlColor) 
{
      return (HBRUSH) m_brush;
}

/****************设置各控件颜色*********************/

HBRUSH CExcelDlg::OnCtlColor(CDC* pDC, CWnd* pWnd, UINT nCtlColor) 
{
	HBRUSH hbr = CDialog::OnCtlColor(pDC, pWnd, nCtlColor);
	
	// TODO: Change any attributes of the DC here

      if(pWnd->GetDlgCtrlID()==IDC_EDIT1||pWnd->GetDlgCtrlID()==IDC_EDIT2||pWnd->GetDlgCtrlID()==IDC_LIST1)   
	  {       
		static HBRUSH hbrEdit=::CreateSolidBrush(RGB(255,240,255));   
        pDC->SetBkColor(RGB(255,240,255));   
        pDC->SetTextColor(RGB(250,100,100));   
        return   hbrEdit;   
      }
	  else if (pWnd->GetDlgCtrlID()==IDC_STATIC1||pWnd->GetDlgCtrlID()==IDC_STATIC2||pWnd->GetDlgCtrlID()==IDC_STATIC3||pWnd->GetDlgCtrlID()==IDC_STATIC4||pWnd->GetDlgCtrlID()==IDC_STATIC5||pWnd->GetDlgCtrlID()==IDC_STATIC6||pWnd->GetDlgCtrlID()==IDC_STATIC7) 
	  {
		static HBRUSH hbrEdit=::CreateSolidBrush(RGB(220,230,255));   
        pDC->SetBkColor(RGB(220,230,255));   
        pDC->SetTextColor(RGB(100,140,100));   
        return   hbrEdit; 
	  }
	  else if (pWnd->GetDlgCtrlID()==IDC_EDIT3) 
	  {
		static HBRUSH hbrEdit=::CreateSolidBrush(RGB(220,230,255));   
        pDC->SetBkColor(RGB(220,230,255));   
        pDC->SetTextColor(RGB(0,0,255));   
        return   hbrEdit; 
	  }

	// TODO: Return a different brush if the default is not desired
	return hbr;
}


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

void CExcelDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
	
    m_ListBox.ResetContent();

	int i,k;  
	for (i=0;i<200;i++)
	{
	     filename[i].Empty();                 //将filename[]和listPath[]清空
	     listPath[i].Empty();                        
	}

	TCHAR *pszFile = new TCHAR[MAX_PATH * MAX_PATH];           //MAX_PATH是一个内部常量 260
	memset(pszFile,0,sizeof(TCHAR)*MAX_PATH * MAX_PATH);
	
	CFileDialog fileDlg(TRUE,NULL,NULL,OFN_ALLOWMULTISELECT);

    CString strPath; 
	fileDlg.m_ofn.lpstrFile = pszFile;
	fileDlg.m_ofn.nMaxFile = MAX_PATH*MAX_PATH;
	fileDlg.m_ofn.lpstrTitle="请选择待处理的文件:";
	fileDlg.m_ofn.lpstrFilter="Text Files(*.txt)\0*.txt\0All Files(*.*)\0*.*\0\0";
	if(fileDlg.DoModal() == IDOK)
	{
		i=0;
		filepath=fileDlg.GetPathName();
		POSITION	Pos;
		Pos = fileDlg.GetStartPosition();
		while (Pos != NULL)
		{
			strPath = fileDlg.GetNextPathName(Pos);
			UpdateData(true);
            filename[i]=strPath;
			UpdateData(false);
			i++;
		} 
	    N=i-1;
	
	 if (filepath.Find(".txt")==-1)                      //当选择多个文件时，filepath.Find()只返回路径，而不返回文件名
	 {
		k=filepath.GetLength();
		if (filepath[k-1]!=filepath[2]) 
           filepath+=filepath[2];
        k=filepath.GetLength();	
	    for (i=0;i<=N;i++)
	       listPath[i]=filename[i].Mid(k);
	 }
	 else 
	 {
		k=filepath.GetLength();
		for (i=k-1;i>=0;i--)
		{
			if (filepath[i]==filepath[2]) 
            break;
		}
        strPath=filepath.Mid(0,i+1);
		listPath[0]=filepath.Mid(i+1);
        filepath=strPath;
	 }

	 m_edit1=filepath;
     for (i=0;i<=N;i++) 
	 this->m_ListBox.AddString((LPCTSTR) listPath[i]);
	}

	UpdateData(FALSE);	   	
}

void CExcelDlg::OnButton2() 
{
	// TODO: Add your control notification handler code here
	CFileDialog fileDlg1(TRUE);

	CString strPath; 
	fileDlg1.m_ofn.lpstrTitle="请选择模板文件:";
	fileDlg1.m_ofn.lpstrFilter="Text Files(*.xls)\0*.xls\0All Files(*.*)\0*.*\0\0";
	if(fileDlg1.DoModal() == IDOK)
	{
		POSITION	Pos;
		Pos = fileDlg1.GetStartPosition();
		strPath= fileDlg1.GetNextPathName(Pos);
			UpdateData(true);
	}

	UpdateData(true);
	m_edit2=strPath;
	UpdateData(false);
}

void CExcelDlg::OnOK() 
{
	// TODO: Add extra validation here
 UpdateData(true);
 int t;
 if (filename[0].IsEmpty()==FALSE&&m_edit2.IsEmpty()==FALSE)
 {
	m_edit3.SetSel(0,-1);
	m_edit3.ReplaceSel("正在处理,请稍候!");               //实时显示

  for (t=0;t<=N;t++) 
  {
	m_edit3.SetSel(0,-1);
	m_edit3.ReplaceSel(listPath[t]);

/******************Excel批处理**************************/
    #define isspace(c)  (((c)==32)||((c)==9))            //用空格键或Tab键来区分每一行单个的字符串

	CString filein,fileout;
	CString strPath;
	int i,j,k;
    int m,n[500];
	char temp[5000],s[40];                        //定义每行最多可读入5000个字符,每个字符串最多为40个字符          
	CString a[500][260];                    //定义最多可使用500行和260列（Excel最多只允许265列）

	filein=filename[t];
	ifstream putin;
	putin.open(filein,ios::nocreate);
    if(putin.fail())
	{
		MessageBox("没有找到"+filein+"文件!");
		continue;
	}

    for (i=0;putin.eof()==false;i++)
	{
		k=0;
        putin.getline(temp,5000,'\n');
		if (isspace(temp[0])!=0) 
		    k++;	
        istrstream ins(temp);
	    for(j=k;ins.eof()==false;j++)
		{
		    ins>>s;
		    a[i][j]=s;

		}   
        n[i]=j+k;
	}
	  m=i;
	
	putin.close();

//********************启动EXCEL*********************//

	CoInitialize(NULL);
	COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);

	_Application  ExcelApp; 
	Workbooks     dingBooks; 
	_Workbook     dingBook; 
	Worksheets    dingsheets; 
	_Worksheet    dingsheet; 
	Range         dingRge; 	

	//创建Excel 2000服务器(启动Excel) 
	if (!ExcelApp.CreateDispatch("Excel.Application",NULL)) 
	{ 
		AfxMessageBox("创建Excel服务失败!"); 
		exit(1);
	} 
	ExcelApp.SetVisible(false); 
	//利用模板文件建立新文档 

	dingBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);
	dingBook.AttachDispatch(dingBooks.Add(_variant_t(m_edit2)));
	dingsheets.AttachDispatch(dingBook.GetWorksheets(),true);
	dingsheet.AttachDispatch(dingsheets.GetItem(_variant_t((short)1)),true);      //默认将数据填到第一张工作表中
	dingRge.AttachDispatch(dingsheet.GetCells(),true);


	for(i=0;i<=m;i++)
	{
        for(j=0;j<=n[i];j++)	
		dingRge.SetItem(_variant_t((long)(i+1)),_variant_t((long)(j+1)),_variant_t(a[i][j]));
	}

	//将表格保存
    i=filename[t].GetLength();
	strPath=filename[t].Mid(0,i-4);
	strPath+=".xls";
    fileout=strPath;
	dingsheet.SaveAs(fileout,vtMissing,vtMissing,vtMissing,vtMissing,
		vtMissing,vtMissing,vtMissing,vtMissing);

	UpdateData(false);

	ExcelApp.SetVisible(false); 
	dingBook.Close(_variant_t(fileout),vtMissing,vtMissing);

	//释放对象 
	dingRge.ReleaseDispatch(); 
	dingsheet.ReleaseDispatch(); 
	dingsheets.ReleaseDispatch(); 
	dingBook.ReleaseDispatch(); 
	dingBooks.ReleaseDispatch(); 
	ExcelApp.ReleaseDispatch();
	
	CoUninitialize();


	if(t==0)                                    //将处理过的文件名从列表框中删除
	    this->m_ListBox.DeleteString(N);
	else
        this->m_ListBox.DeleteString(0);


	UpdateData(false);
		
  }

    for (t=0;t<=N;t++)
	{
	     filename[t].Empty();                 //将filename[]和listPath[]清空
	     listPath[t].Empty();                
	}

	m_edit3.SetSel(0,-1);
	m_edit3.ReplaceSel("处理完毕!");
	MessageBox("处理完毕!");

}	
//  else if (N==-1) 
  else if (filename[0].IsEmpty()==TRUE) 
	   MessageBox("请选择待处理的文件!");
  else if (m_edit2.IsEmpty()==TRUE)
       MessageBox("请选择Excel模板文件!");
}