Excel 操作类

最新推荐文章于 2025-08-10 22:46:37 发布

原创最新推荐文章于 2025-08-10 22:46:37 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #file #object #office #constants #components

//.h file

#include "msexcel.h"
// Excel component is in COM format, so include this header file to support COM
#include <atlbase.h>

class CExcelManager
{
public:
void SetHorizontalAlignment( int row, int col, int style );

// Set column width.
// Parameter:
// int col: The index of the column to set width.
// int width: The width to set.
void SetColumnWidth( int col, int width );

// Set the worksheet name .
// Parameter:
// CString name: A new name to be set.
void SetSheetName(CString name);

// Set text font.
// Parameter:
// CString fontName: Name of the font, for example, Arial.
// int font Size: Size of the font, for example, 10.
void SetFont( CString fontName, int fontSize );

// Set the value of a specified worksheet cell.
// Parameter:
// int row: The cell row.
// int col: The cell column.
// CString value: The text to set on the cell.
void SetCellValue( int row, int col, CString value );

// Close the file.
// Return Value:
// True: The file is successfully closed.
// False: if the component fails to close the open file.
BOOL Close();

// Save the opened workbook and create a workbook into the specified file.
// Parameter:
// CString filename: the specified file name to save Excel file to.
// Return Value:
// True: A new workbook is successfully created.
// False: If the component fails to create a new workbook.
BOOL SaveAs(CString fileName);

// Create a new Excel workbook.
// Return Value:
// True: A new workbook is successfully created.
// False: If the component fails to create a new workbook.
BOOL Create();

// Open the specified file in the Excel component.
// Parameter:
// CString filename: the file to be opened.
// Return Value:
// True: If the file is successfully open.
// False: If the component fails to open the specified file.
BOOL Open( CString fileName );

// Uninitializes the Excel instance and releases it.
// Return Value:
// True: The Excel instance is successfully un initialized and released.
// False: The Excel instance fails to un initialize it.
BOOL Uninit();

// Initializes the Excel component.
// Return Value:
// True: Excel instance is successfully initialized.
// False: The Excel instance fails to initialize it.
BOOL Init();

CExcelManager();
virtual ~CExcelManager();

private:
// The object representing the Excel application.
_Application ExcelApp;

// The object representing an Excel workbook collection.
Workbooks wbsMyBooks;

// The object representing an Excel workbook.
_Workbook wbMyBook;

// The object representing an Excel worksheet collection.
Worksheets MySheets;

// The object representing an Excel worksheet.
_Worksheet MySheet;

// The object representing some cells in an Excel worksheet.
Range MyRange;

// Predefined VARIANT type constants to be used internally.
CComVariant vTrue, vFalse, vZero, vOne, vOpt;
};

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// ExcelManager.cpp: implementation of the CExcelManager class.
//
// This is the class encapsulating Excel component operating.

#include "stdafx.h"
#include "MSExcel.h"
#include "ExcelManager.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CExcelManager::CExcelManager()
{
// initialize the internal variables
vTrue = (short)TRUE;
vFalse = (short)FALSE;
vZero = 0;
vOne = 1;
vOpt = CComVariant((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
}

CExcelManager::~CExcelManager()
{

}

BOOL CExcelManager::Init()
{
try
{
// create the Excel component
  if (!ExcelApp.CreateDispatch(_T("Excel.Application"),NULL))
  {
   CException e;
   throw &e;
  }

// hide the component from being displayed, only need it to work at background
ExcelApp.SetVisible( FALSE );

// disable the component popping up alert boxes
ExcelApp.SetDisplayAlerts( FALSE );

CString ver = ExcelApp.GetVersion();
// AfxMessageBox( ver );

wbsMyBooks = ExcelApp.GetWorkbooks();

  if(!wbsMyBooks)
  {
   CException e;
   throw &e;
  }
}
catch (COleException *e)
{
  e->Delete();
  Uninit();
  return FALSE;
}
catch (CException *e)
{
  e->Delete();
  Uninit();
  return FALSE;
}

return TRUE;
}

BOOL CExcelManager::Uninit()
{
// close all objects and quit the excel component
wbsMyBooks.Close();
wbsMyBooks.ReleaseDispatch();

ExcelApp.Quit();
ExcelApp.ReleaseDispatch();

return TRUE;
}

BOOL CExcelManager::Open(CString fileName)
{
CComVariant openFileName( fileName );

// office 2003 compatible
// wbMyBook = wbsMyBooks.Open( fileName,vZero,vTrue,vOpt,vOpt,vOpt,
// vTrue,vOpt,vOpt,vOpt,vOpt,vOpt,vFalse,vOpt,vOpt);

// office 2000 and above compatible
wbMyBook = wbsMyBooks.Open( fileName,vZero,vTrue,vOpt,vOpt,vOpt,
vTrue,vOpt,vOpt,vOpt,vOpt,vOpt,vFalse );

MySheets = wbMyBook.GetWorksheets();

MySheet = MySheets.GetItem( vOne );

MyRange = MySheet.GetCells();

return TRUE;
}

BOOL CExcelManager::Create()
{
// create a new workbook in excel
wbMyBook = wbsMyBooks.Add( vOpt );

// get the first worksheet in the workbook
MySheets = wbMyBook.GetWorksheets();
MySheet = MySheets.GetItem( vOne );

// get all cells collection
MyRange = MySheet.GetCells();

return TRUE;
}

BOOL CExcelManager::SaveAs(CString fileName)
{
CComVariant saveFileName( fileName );

try
{
// office 2003 compatible
// wbMyBook.SaveAs( saveFileName, vOpt, vOpt, vOpt, vOpt, vOpt, 0, vOpt, vOpt, vOpt, vOpt, vOpt );

// office 2000 and above compatible
  wbMyBook.SaveAs( saveFileName, vOpt, vOpt, vOpt, vOpt, vOpt, 0, vOpt, vOpt, vOpt, vOpt );
}
catch (COleException *e)
{
  e->Delete();
  return FALSE;
}
catch (CException *e)
{
  e->Delete();
  return FALSE;
}

return TRUE;
}

BOOL CExcelManager::Close()
{
// release the COM components
MyRange.ReleaseDispatch();
MySheet.ReleaseDispatch();
MySheets.ReleaseDispatch();

wbMyBook.Close(vFalse,vOpt,vOpt);
wbMyBook.ReleaseDispatch();

return TRUE;
}

void CExcelManager::SetCellValue(int row, int col, CString value)
{
CComVariant cellValue( value );

MyRange.SetItem( CComVariant(row), CComVariant(col), cellValue );
}

void CExcelManager::SetFont(CString fontName, int fontSize)
{
Font f = MyRange.GetFont();

// set the font name
f.SetName( CComVariant( fontName ) );

// set the font size
f.SetSize( CComVariant( fontSize ) );
}

void CExcelManager::SetSheetName(CString name)
{
MySheet.SetName( name );
}

void CExcelManager::SetColumnWidth( int col, int width )
{
// set the width for the columns
Range tmpRange = MySheet.GetRange( CComVariant(_T("B1")), vOpt );
tmpRange.SetColumnWidth( CComVariant(width) );
tmpRange.ReleaseDispatch();

for( char c = _T('C'); c <= _T('Z'); c++)
{
CString tmp;
tmp.Format( _T("%c1"), c );

  tmpRange = MySheet.GetRange( CComVariant(tmp), vOpt );
  tmpRange.SetColumnWidth( CComVariant(12) );
  tmpRange.ReleaseDispatch();
}
}

void CExcelManager::SetHorizontalAlignment( int row, int col, int style )
{
CString tmp;
tmp.Format( _T("%c%d"), col + _T('A') - 1, row );

Range tmpRange = MySheet.GetRange( CComVariant( tmp ), vOpt );
CComVariant vStyle( style );
tmpRange.SetHorizontalAlignment( vStyle );
tmpRange.ReleaseDispatch();
}