/------------------------------------------------------------------------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);
}