首先利用Visual C++ 6.0,建立一个MFC基于对话框的工程项目,共享DLL,Win32平台。工程名称ExcelTest。在主对话框中加入一个按钮,
ID IDC_EXCELTEST
Caption Test Excel
双击该按钮,增加成员函数void CExcelTestDlg::OnExceltest()。
在BOOL CExcelTestApp::InitInstance()中,dlg.DoModal();之前增加代码:
if (CoInitialize(NULL)!=0)
{
AfxMessageBox("初始化COM支持库失败!");
exit(1);
}
在return FALSE; 语句前,加入:
CoUninitialize();
选择Menu->View->ClassWizade,打开ClassWizade窗口,选择Add Class->From a type library,(Microsoft Office 2003)选择D:/Program Files/Microsoft Office/office11/Excel.exe(Microsoft Office2000 D:/Program Files/Microsoft Office/office/Excel9.OLB)。选择_Application、Workbooks、_Workbook、 Worksheets、_Worksheet、Range,加入新类,分别为_Application、Workbooks、_Workbook、 Worksheets、_Worksheet、Range,头文件Excel9.h,源文件Excel9.cpp。
在ExcelTestDlg.cpp文件的头部,#include "ExcelTestDlg.h"语句之下,增加 :
// ExcelToTbeDlg.cpp : implementation file
//
#include "stdafx.h"
#include "ExcelToTbe.h"
#include "ExcelToTbeDlg.h"
#include "Excel.h"
#include <afxdlgs.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()
/
// CExcelToTbeDlg dialog
CExcelToTbeDlg::CExcelToTbeDlg(CWnd* pParent /*=NULL*/)
: CDialog(CExcelToTbeDlg::IDD, pParent)
{
//{{AFX_DATA_INIT(CExcelToTbeDlg)
m_TbePathName = _T("");
m_XLS_PathName = _T("");
//}}AFX_DATA_INIT
// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}
void CExcelToTbeDlg::DoDataExchange(CDataExchange* pDX)
{
CDialog::DoDataExchange(pDX);
//{{AFX_DATA_MAP(CExcelToTbeDlg)
DDX_Control(pDX, IDC_CONVERSION_PROGRESS, m_CsProgress);
DDX_Text(pDX, IDC_TBE_PATHNAME, m_TbePathName);
DDX_Text(pDX, IDC_XLS_PATHNAME, m_XLS_PathName);
//}}AFX_DATA_MAP
}
BEGIN_MESSAGE_MAP(CExcelToTbeDlg, CDialog)
//{{AFX_MSG_MAP(CExcelToTbeDlg)
ON_WM_SYSCOMMAND()
ON_WM_PAINT()
ON_WM_QUERYDRAGICON()
ON_BN_CLICKED(IDC_CONVERSION, OnConversion)
ON_BN_CLICKED(IDC_XLS_OPEN, OnXlsOpen)
ON_BN_CLICKED(IDC_TBE_OPEN, OnTbeOpen)
//}}AFX_MSG_MAP
END_MESSAGE_MAP()
/
// CExcelToTbeDlg message handlers
BOOL CExcelToTbeDlg::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->SetWindowText("Excel转tbe表格程序");
this->m_CsProgress.SetRange(0,10);
this->m_CsProgress.SetPos(0);
return TRUE; // return TRUE unless you set the focus to a control
}
void CExcelToTbeDlg::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 CExcelToTbeDlg::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 CExcelToTbeDlg::OnQueryDragIcon()
{
return (HCURSOR) m_hIcon;
}
void CExcelToTbeDlg::OnCancel()
{
// TODO: Add extra cleanup here
CDialog::OnCancel();
}
void CExcelToTbeDlg::OnOK()
{
// TODO: Add extra validation here
//CDialog::OnOK();
}
//开始转换文件
void CExcelToTbeDlg::OnConversion()
{
if(this->m_XLS_PathName.GetLength()<=0)
{
AfxMessageBox("请浏览需要打开文件的路径和名称!");
return ;
}
if(this->m_TbePathName.GetLength() <= 0)
{
AfxMessageBox("请浏览需要保存文件的路径和名称!");
return ;
}
//变量定义
_Application app;
Workbooks books;
_Workbook book;
Sheets sheets;
_Worksheet sheet;
Range range;
Range iCell;
LPDISPATCH lpDisp;
COleVariant vResult;
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
//创建Excel服务器(启动Excel)
if(!app.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("启动Excel服务器失败!");
return ;
}
this->m_CsProgress.SetPos(1);//进度指示
app.SetVisible(false); //使Excel可见
app.SetUserControl(TRUE); //允许其它用户控制Excel
books.AttachDispatch(app.GetWorkbooks());
lpDisp = books.Open(this->m_XLS_PathName,covOptional,covOptional,covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional);
this->m_CsProgress.SetPos(2);//进度指示
//得到Workbook
book.AttachDispatch(lpDisp);
//*****
//得到Worksheets
sheets.AttachDispatch(book.GetWorksheets());
//*****
//得到当前活跃sheet
//如果有单元格正处于编辑状态中,此操作不能返回,会一直等待
lpDisp=book.GetActiveSheet();
sheet.AttachDispatch(lpDisp);
//*****
//读取已经使用区域的信息,包括已经使用的行数、列数、起始行、起始列
Range usedRange;
usedRange.AttachDispatch(sheet.GetUsedRange());
range.AttachDispatch(usedRange.GetRows());
long iRowNum=range.GetCount(); //已经使用的行数
range.AttachDispatch(usedRange.GetColumns());
long iColNum=range.GetCount(); //已经使用的列数
this->m_CsProgress.SetPos(3);//进度指示
long iStartRow=usedRange.GetRow(); //已使用区域的起始行,从1开始
long iStartCol=usedRange.GetColumn(); //已使用区域的起始列,从1开始
CStdioFile writefile;//开始写*.tbe文件
if(writefile.Open(this->m_TbePathName,CFile::modeCreate|CFile::typeText|CFile::modeWrite))
{
CString Textbuf="";//写入字符串
writefile.SeekToEnd();
COLORREF color=RGB(255,0,0);
//写入文件标题和对齐方式
writefile.WriteString("|t||t|0|t||tfont|宋体|tfont||tsize|13|tsize||tcolor|红色|tcolor||talign|居 中|talign||tbold|0|tbold||titalic|0|titalic||tunderline|0|tunderline||align|1|align|/n");
//网格线颜色
writefile.WriteString("|clr|");
Textbuf.Format("%d",color);
writefile.WriteString(Textbuf);
writefile.WriteString("|clr|/n");
this->m_CsProgress.SetPos(4); //进度指示
if(iRowNum>1)
{
//行高
writefile.WriteString("|row|20|row||col|70|col||*||*|宋体|*|13|*|200|*|0|*|0|*|-16777216|*|-16777216|*|32813|*|70|*|20|*|`");
for(int col=1;col<=iColNum;col++)
{
writefile.WriteString("|col|70|col||*|");
Textbuf.Format("%c",(char)(64+col));
writefile.WriteString(Textbuf);
writefile.WriteString("|*|宋体|*|13|*|200|*|0|*|0|*|-16777216|*|-16777216|*|32813|*|70|*|20|*|");
if(col==iColNum)
writefile.WriteString("`/n");
else
writefile.WriteString("`");
}
}
int progress=1;
for(int row=1;row<=iRowNum;row++)
{
//行高
writefile.WriteString("|row|20|row||*|");
Textbuf.Format("%d",row);
writefile.WriteString(Textbuf);
writefile.WriteString("|*|宋体|*|13|*|200|*|0|*|0|*|-16777216|*|-16777216|*|32813|*|70|*|20|*|`");
for(int col=1;col<=iColNum;col++)
{
if(progress==(iRowNum*iColNum/5))
{
this->m_CsProgress.SetPos(progress+4);//进度指示
}
progress++;
//*****
//读取第一个单元格的值
range.AttachDispatch(sheet.GetCells());
range.AttachDispatch(range.GetItem (COleVariant((long)row),COleVariant((long)col)).pdispVal);
COleVariant vt;
vResult=range.GetValue(vt);
CString value="";
if(vResult.vt == VT_BSTR) //字符串
{
value=vResult.bstrVal;
}
else if(vResult.vt == VT_DATE) //时间格式
{
SYSTEMTIME st;
VariantTimeToSystemTime(vResult.date, &st);
CString buf="";
//日期
if(st.wYear>0)
{
buf.Format("%d-",st.wYear);
value+=buf;
}
if(st.wMonth>0)
{
buf.Format("%d-",st.wMonth);
value+=buf;
}
if(st.wDay>0)
{
buf.Format("%d",st.wDay);
value+=buf;
}
//时间
if(value.GetLength()>0)
{
value+=" ";
}
if(st.wHour>0)
{
if(st.wSecond > 0)
{
buf.Format("%.2d:%.2d:%.2d",st.wHour,st.wMinute,st.wSecond);
}
else
{
buf.Format("%.2d:%.2d",st.wHour,st.wMinute);
}
value+=buf;
}
else if(st.wMinute>0)
{
buf.Format("%.2d:%.2d",st.wMinute,st.wSecond);
value+=buf;
}
}
else if (vResult.vt==VT_R8) //8字节的数字
{
SYSTEMTIME st;
BOOL IfTime = true;
if(IfTime)
{
VariantTimeToSystemTime(vResult.date, &st);
CString buf="";
if(st.wHour > 0)
{
if(st.wSecond > 0)
{
buf.Format("%.2d:%.2d:%.2d",st.wHour,st.wMinute,st.wSecond);
}
else
{
buf.Format("%.2d:%.2d",st.wHour,st.wMinute);
}
value+=buf;
IfTime = false;
}
else if(st.wMinute > 0)
{
buf.Format("%.2d:%.2d",st.wMinute,st.wSecond);
value+=buf;
IfTime = false;
}
}
if(IfTime)
{
value = "";
value.Format("%.6f", vResult.dblVal);
//去尾数0
for(int i=0;i<=value.GetLength();i++)
{
if(::atoi(value.Right(i))>0)
{
value=value.Left(value.GetLength()-(i-1));
break;
}
}
}
}
else if(vResult.vt==VT_EMPTY) //单元格空的
{
value="";
}
//文本
writefile.WriteString("|*|");
writefile.WriteString(value);
//字体、字号、粗体、斜体、下划线、前景色、背景色
writefile.WriteString("|*|宋体|*|13|*|200|*|0|*|0|*|255|*|0");
//对齐方式
writefile.WriteString("|*|32813");
//宽度
writefile.WriteString("|*|70");
//高度
writefile.WriteString("|*|20");
if(col==iColNum)
writefile.WriteString("`/n");
else
writefile.WriteString("`");
}
}
writefile.Close();//关闭文件
}
//*****
//关闭所有的book,退出Excel
CString OutFilename;
book.Close (covOptional,COleVariant(OutFilename),covOptional);
books.Close();
app.Quit();
this->m_CsProgress.SetPos(10);//进度指示
//消息提示
MessageBox("文件转换成功!","提示",MB_ICONQUESTION);
//进度条初始化
this->m_CsProgress.SetPos(0);//进度指示
}
//浏览打开XLS文件
void CExcelToTbeDlg::OnXlsOpen()
{
// TODO: Add your control notification handler code here
CFileDialog XlsFiledlg(true,"wd.xls",NULL,OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT|OFN_ALLOWMULTISELECT,"Data Files (*.xls;)|*.xls;|All Files(*.*)|*.*||");
if(XlsFiledlg.DoModal()==IDOK)
{
this->UpdateData(true);
this->m_XLS_PathName=XlsFiledlg.GetPathName();//xls文件路径
//保存tbe文件路径
CString pathname=XlsFiledlg.GetPathName();
pathname.Replace(".xls",".tbe");
this->m_TbePathName=pathname;
this->UpdateData(false);
}
}
//浏览打开TBE文件
void CExcelToTbeDlg::OnTbeOpen()
{
// TODO: Add your control notification handler code here
CFileDialog TbeFiledlg(false,"wd.tbe",NULL,OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT|OFN_ALLOWMULTISELECT,"Data Files (*.tbe;)|*.tbe;|All Files(*.*)|*.*||");
if(TbeFiledlg.DoModal()==IDOK)
{
this->UpdateData(true);
//保存tbe文件路径
this->m_TbePathName=TbeFiledlg.GetPathName();
this->UpdateData(false);
}
}