VC++操作Excel 2007

自己设计的。用来读excel 2007的。

excel 2007的数据一般都是double 和string的。

希望对大家能有所帮助。

 

 

声明: 

 

 

#if _MSC_VER > 1000

#pragma  once

#endif

 

#include "excel12.h"

 

class CExcelReader

{

public:

  CExcelReader(){ CoInitialize(NULL);};

 

  void OpenRead(const CString& file); // open and read the specified file

 

  void Init_App();  // init the excel app

 

  void Quit_App();  // quit the excel App

 

  void Close();  //  close the current document

 

  long GetCurrentSheetRowCount();

 

  long GetCurrentSheetColCount();

 

  //************************************

  // Method:    ReadCell, read the cell of the excel that you opened. the cell is related to sheet loaded.

  // FullName:  CExcelReader::ReadCell

  // Access:    public 

  // Returns:   VARIANT

  // Qualifier:

  // Parameter: long rowindex, starts from 1 

  // Parameter: long columnindex, starts from 1

  //************************************

  VARIANT ReadCell( long rowindex,  long columnindex);  // read the specified cell

 

 

  //************************************

  // Method:    ReadCell

  // FullName:  CExcelReader::ReadCell

  // Access:    public 

  // Returns:   VARIANT

  // Qualifier:

  // Parameter: long rowindex

  // Parameter: long columnindex

  // Parameter: const VARIANT & vartype, specifed by t

  //************************************

 // VARIANT ReadCell( long rowindex, long columnindex, const VARIANT& vartype);  // specified the var type

 

  //************************************

  // Method:    ReadCellAsString, read the cell content as a string.

  // FullName:  CExcelReader::ReadCellAsString

  // Access:    public 

  // Returns:   CString

  // Qualifier:

  // Parameter: long rowindex

  // Parameter: long columnindex

  //************************************

  CString ReadCellAsString(long rowindex, long columnindex);

 

 

  //************************************

  // Method:    ReadCellAsDouble, read a cell value as a double

  // FullName:  CExcelReader::ReadCellAsDouble

  // Access:    public 

  // Returns:   double

  // Qualifier:

  // Parameter: long rowindex

  // Parameter: long columnindex

  //************************************

  double ReadCellAsDouble(long rowindex, long columnindex);

 

  ~CExcelReader();  // 

 

  CString GetFileName() const { return m_filename; }  

 

 

  //************************************

  long GetTotalsheetNumber() const {return m_sheetNumber; }

 

  //************************************

  // load a sheet by the name of the sheet

  BOOL LoadSheetByName(const CString& sheetname);

 

  //************************************

  // load a sheet by the index of the sheet, starts from 1

  BOOL LoadSheetByIndex(long index);

 

protected:

 

private:

  CString m_filename;   //  file name to be opened. the reader should will be associated with one file at a time.

 

  long   m_sheetNumber;  // numbers of sheets of the excel file

 

  _Application excelApp;           // the excel app

 

  Workbooks      oBooks;  // the work books container

 

  _Workbook      oBook;   // the work book

 

  Sheets oSheets;      // the sheets container

 

  _Worksheet     oSheet;  // a single sheet

 

  Range   xlsrange;

};

 

 

 

 

 

#include "StdAfx.h"

#include "CExcelReader.h"

#include "Shlwapi.h"

 

#pragma comment(lib, "shlwapi")

 

void CExcelReader::Init_App()

{

 

  try

  {

    if ( !excelApp.CreateDispatch(_T("Excel.Application")) ) // attach to the excel application

    {

      AfxMessageBox(_T("Failed to Start the Excel application"));

      return;

    }

    excelApp.SetVisible(FALSE);

    excelApp.SetDisplayAlerts(FALSE);

    oBooks = excelApp.GetWorkbooks();

 

  }

  catch (COleDispatchException* e)

  {

   AfxMessageBox(e->m_strDescription);

  }

  catch (COleException* e)

  {

    TCHAR buff[1024];

    memset(buff, 0x00, sizeof(buff));

 

    e->GetErrorMessage(buff, 1024);

    AfxMessageBox(buff);

  }

 

}

 

void CExcelReader::OpenRead( const CString& filename )

{

  // check whether the file exists

  if ( !PathFileExists(filename) )

  {

    AfxMessageBox(_T("File does not exist"));

    return;

  }

 

  if (m_filename.CompareNoCase(filename) == 0)

  {

    AfxMessageBox(_T("The file has already been opened."));

    return;

  }

 

  // try to open the file

  COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

  COleVariant VTrue((short)TRUE);

  COleVariant VFalse((short)FALSE);

  try

  {

    // open and read only

    oBooks.Open(filename, VFalse, VOptional, VOptional, VOptional, VOptional, VOptional,

                VOptional, VOptional, VOptional, VOptional, VOptional, VOptional, VOptional, VOptional);

 

    m_filename = filename;

 

    COleVariant index(short(1));

 

    oBook = oBooks.GetItem(index);

 

    oSheets = oBook.GetSheets();

  m_sheetNumber = oSheets.GetCount();

  }

  catch (COleDispatchException* e)

  {

    AfxMessageBox(e->m_strDescription);

    return;

  }

  catch(COleException* e)

  {

    TCHAR buff[1024];

    memset(buff, 0x00, sizeof(buff));

 

    e->GetErrorMessage(buff, 1024);

    AfxMessageBox(buff);

  }

 

}

 

BOOL CExcelReader::LoadSheetByName( const CString& sheetname )

{

  if (oSheets.m_lpDispatch == NULL)

  {

    return FALSE;

  }

 

  try

  {

    oSheet = oSheets.GetItem(COleVariant(sheetname));

  }

  catch(COleDispatchException* e)

  {

    AfxMessageBox(e->m_strDescription);

    return FALSE;

  }

 

  return TRUE;

}

 

BOOL CExcelReader::LoadSheetByIndex(long index )

{

  if (oSheets.m_lpDispatch == NULL)

  {

    return FALSE;

  }

 

  try

  {

     oSheet = oSheets.GetItem(COleVariant(index));

  }

  catch (COleDispatchException* e)

  {

    AfxMessageBox(e->m_strDescription);

   return FALSE;

  }

 

  return TRUE;

 

}

 

VARIANT CExcelReader::ReadCell(  long rowindex,  long columnindex )

{

  VARIANT result;

  VariantInit(&result);

 

  try

  {

    xlsrange = oSheet.GetCells();

 

    VARIANT item;

    item = xlsrange.GetItem(COleVariant(rowindex), COleVariant(columnindex)); 

 

    if (item.vt == VT_DISPATCH)

    {

      xlsrange.AttachDispatch(item.pdispVal);

      if (xlsrange.m_lpDispatch != NULL)

      {

        result = xlsrange.GetValue2();

      }

    }

 

  }

  catch (COleDispatchException* e)

  {

   AfxMessageBox(e->m_strDescription);

  }

  catch(COleException* e)

  {

    TCHAR buff[1024];

    memset(buff, 0x00, sizeof(buff));

 

    e->GetErrorMessage(buff, 1024);

    AfxMessageBox(buff);

 

  }

 

  return result;  

}

 

 

 

 

void CExcelReader::Close()

{

  oBook.DetachDispatch();

  oSheet.DetachDispatch();

  xlsrange.DetachDispatch();

 

  oBooks.Close();

  oBooks.ReleaseDispatch();

}

 

void CExcelReader::Quit_App()

{

  excelApp.Quit(); 

}

 

long CExcelReader::GetCurrentSheetRowCount()

{

  if (oSheet.m_lpDispatch == NULL)

  {

    return -1;

  }else

  {

    try

    {

      Range usedrange = oSheet.GetUsedRange();  // get used range

      Range rows = usedrange.GetRows();  //  get used rows

 

      return rows.GetCount();

    }

    catch (COleDispatchException* e)

    {

      AfxMessageBox(e->m_strDescription);

      return -1;

    }

    catch(COleException* e)

    {

      TCHAR buff[256];

      memset(buff, 0x00, sizeof(buff));

      e->GetErrorMessage(buff, 256);

      AfxMessageBox(buff);

      return -1;

    }    

  }

}

 

long CExcelReader::GetCurrentSheetColCount()

{

  if (oSheet.m_lpDispatch == NULL)

  {

    return 0;

  }else

  {

    try

    {

      Range usedrange = oSheet.GetUsedRange();  // get used range

      Range cols = usedrange.GetColumns();

      return cols.GetCount();

    }

    catch (COleDispatchException* e)

    {

      AfxMessageBox(e->m_strDescription);

      return -1;

    }

    catch (COleException* e)

    {

      TCHAR buff[256];

      memset(buff, 0x00, sizeof(buff));

      e->GetErrorMessage(buff, 256);

      AfxMessageBox(buff);

      return -1;

    }        

  }

 

}

 

CExcelReader::~CExcelReader()

{

  Close();

  Quit_App();

 

  CoUninitialize();  

}

 

/*

VARIANT CExcelReader::ReadCell( long rowindex, long columnindex, const VARIANT& vartype )

{

  VARIANT result;

  VariantInit(&result);

 

  try

  {

    xlsrange = oSheet.GetCells();

 

    VARIANT item;

    item = xlsrange.GetItem(COleVariant(rowindex), COleVariant(columnindex)); 

 

    if (item.vt == VT_DISPATCH)

    {

      xlsrange.AttachDispatch(item.pdispVal);

      if (xlsrange.m_lpDispatch != NULL)

      {

        result = xlsrange.GetValue(vartype);

      }

    }

 

  }

  catch (COleDispatchException* e)

  {

    AfxMessageBox(e->m_strDescription);

  }

  catch(COleException* e)

  {

    TCHAR buff[1024];

    memset(buff, 0x00, sizeof(buff));

 

    e->GetErrorMessage(buff, 1024);

    AfxMessageBox(buff);

 

  }

 

  return result;  

}*/

 

 

CString CExcelReader::ReadCellAsString( long rowindex, long columnindex )

{

  CString result;

 

  VARIANT var = ReadCell(rowindex, columnindex);

 

  if (var.vt == VT_BSTR)

    result = var.bstrVal;

  else if (var.vt == VT_R8)

    result.Format("lf", var.dblVal);

 

  return result;

}

 

 

double CExcelReader::ReadCellAsDouble( long rowindex, long columnindex )

{

  double result;

  VARIANT var = ReadCell(rowindex, columnindex);  

  if(var.vt == VT_R8)  

    result = var.dblVal;

 

  return result;

 

}

 

 

 

 

如果有什么问题， 请联系作者: bicheng.gui@gmail.com，欢迎交流