一個封裝的實用的EXCEL類

/------------------------------------------------------------------------EXCELFILE.H-------------------------------------------------

#pragma once

#include "CApplication.h"

#include "CWorkbook.h"

#include "CWorkbooks.h"

#include "CWorksheet.h"

#include "CWorksheets.h"

#include "CRange.h"

#include "comdef.h"

class ExcelFile

{

public:

 void ShowInExcel(bool bShow);

 CString GetCell(int iRow, int iColumn);

 int     GetCellInt(int iRow, int iColumn);

 int GetRowCount();

 int GetColumnCount();

 bool LoadSheet(int iIndex);

 bool LoadSheet(char* sheet);

 CString GetSheetName(int iIndex);

 void InitExcel();

 void ReleaseExcel(CString strOutFileName);

 int GetSheetCount();

 bool Open(CString FileName);

 ExcelFile();

 virtual ~ExcelFile();

private:

 CWorkbooks    m_Books;

 CWorkbook     m_Book;

 CWorksheets   m_sheets;

 CWorksheet    m_sheet;

 CRange        m_Rge;

 static CApplication m_ExcelApp;

};

/-----------------------excelfile.cpp----------------

#include "StdAfx.h"

#include "./excelfile.h"

COleVariant

        covTrue((short)TRUE),

        covFalse((short)FALSE),

        covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);   

ExcelFile::ExcelFile()

{

}

ExcelFile::~ExcelFile()

{

 m_Rge.ReleaseDispatch();

 m_sheet.ReleaseDispatch();

 m_sheets.ReleaseDispatch();

 m_Book.ReleaseDispatch();

 m_Books.ReleaseDispatch();

}

CApplication ExcelFile::m_ExcelApp;

void ExcelFile::InitExcel()

{

 初始化COM

 //if (::CoInitialize( NULL ) == E_INVALIDARG)

 //{

 // AfxMessageBox(_T("初始化Com失敗!"));

 //}

 //建立Excel 2000伺服器(啟動Excel)

 if (!m_ExcelApp.CreateDispatch("Excel.Application",NULL))

 {

  AfxMessageBox("建立Excel服務失敗!");

  exit(1);

 }

}

void ExcelFile::ReleaseExcel(CString strOutFileName)

{

 m_sheets.ReleaseDispatch();

 m_sheet.ReleaseDispatch();

 m_Rge.ReleaseDispatch();

 m_Book.Close(covOptional,COleVariant(strOutFileName),covOptional);

 m_Books.Close();

 m_ExcelApp.Quit();

 m_ExcelApp.ReleaseDispatch();

 m_Books=NULL;

 m_ExcelApp=NULL;

}

bool ExcelFile::Open(CString FileName)

{//打開excel檔案

 //利用模闆檔案建立新文檔

 m_Books.AttachDispatch(m_ExcelApp.get_Workbooks(),true);

 LPDISPATCH lpDis = NULL;

 lpDis = m_Books.Add(_variant_t(FileName));

 if (lpDis)

 {

  m_Book.AttachDispatch(lpDis);

  //得到Worksheets

  m_sheets.AttachDispatch(m_Book.get_Worksheets(),true);

  return true;

 }

 return false;

}

int ExcelFile::GetSheetCount()

{

 return m_sheets.get_Count();

}

CString ExcelFile::GetSheetName(int iIndex)

{

 CWorksheet sheet;

 sheet.AttachDispatch(m_sheets.get_Item(_variant_t((long)iIndex)),true);

 CString name = sheet.get_Name();

 sheet.ReleaseDispatch();

 return name;

}

bool ExcelFile::LoadSheet(int iIndex)

{

 LPDISPATCH lpDis = NULL;

 m_Rge.ReleaseDispatch();

 m_sheet.ReleaseDispatch();

 lpDis = m_sheets.get_Item(_variant_t((long)iIndex));

 if (lpDis)

 {

  m_sheet.AttachDispatch(lpDis,true);

  m_Rge.AttachDispatch(m_sheet.get_Cells(), true);

  return true;

 }

 return false;

}

bool ExcelFile::LoadSheet(char* sheet)

{

 LPDISPATCH lpDis = NULL;

 m_Rge.ReleaseDispatch();

 m_sheet.ReleaseDispatch();

 lpDis = m_sheets.get_Item(_variant_t(sheet));

 if (lpDis)

 {

  m_sheet.AttachDispatch(lpDis,true);

  m_Rge.AttachDispatch(m_sheet.get_Cells(), true);

  return true;

 }

 return false;

}

int ExcelFile::GetColumnCount()

{

 CRange range;

 CRange usedRange;

 usedRange.AttachDispatch(m_sheet.get_UsedRange(), true);

 range.AttachDispatch(usedRange.get_Columns(), true);

 int count = range.get_Count();

 usedRange.ReleaseDispatch();

 range.ReleaseDispatch();

 return count;

}

int ExcelFile::GetRowCount()

{

 CRange range;

 CRange usedRange;

 usedRange.AttachDispatch(m_sheet.get_UsedRange(), true);

 range.AttachDispatch(usedRange.get_Rows(), true);

 int count = range.get_Count();

 usedRange.ReleaseDispatch();

 range.ReleaseDispatch();

 return count;

}

CString ExcelFile::GetCell(int iRow, int iColumn)

{

 CRange range;

 range.AttachDispatch(m_Rge.get_Item (COleVariant((long)iRow),COleVariant((long)iColumn)).pdispVal, true);

 COleVariant vResult =range.get_Value2();

 CString str;

 if(vResult.vt == VT_BSTR)       //字元串

 {

  str=vResult.bstrVal;

 }

 else if (vResult.vt==VT_INT)

 {

  str.Format("%d",vResult.pintVal);

 }

 else if (vResult.vt==VT_R8)     //8位元組的數字

 {

  str.Format("%0.0f",vResult.dblVal);

  //str.Format("%.0f",vResult.dblVal);

  //str.Format("%1f",vResult.fltVal);

 }

 else if(vResult.vt==VT_DATE)    //時間格式

 {

  SYSTEMTIME st;

  VariantTimeToSystemTime(vResult.date, &st);

  CTime tm(st);

  str=tm.Format("%Y-%m-%d");

 }

 else if(vResult.vt==VT_EMPTY)   //單元格空的

 {

  str="";

 } 

 range.ReleaseDispatch();

 return str;

}

int ExcelFile::GetCellInt(int iRow, int iColumn)

{

 CRange range;

 range.AttachDispatch(m_Rge.get_Item (COleVariant((long)iRow),COleVariant((long)iColumn)).pdispVal, true);

 COleVariant vResult =range.get_Value2();

 int num;

 num = (int)vResult.date;

 range.ReleaseDispatch();

 return num;

}

void ExcelFile::ShowInExcel(bool bShow)

{

 m_ExcelApp.put_Visible(bShow);

}