vc 读取execl 日期和时间格式

首先利用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);
 }
}