读取excel数据

来源：互联网发布：男友追踪器软件编辑：程序博客网时间：2024/05/16 07:07

读取excel数据
2008-07-20 16:23:09

读excel

staAfx.h

#define INITGUID
#import "c:/program files/common files/system/ado/msado15.dll"no_namespace rename("EOF","EndOfFields")
#include <comdef.h>
#include "icrsint.h"

头文件：

#include "Excel9.h"
#include "comdef.h"

初始化：

CoInitialize(NULL);

_Application app;
    Workbooks books;
    _Workbook book;
    Worksheets sheets;
    _Worksheet sheet;
    Range range;

    if(!app.CreateDispatch("Excel.Application"))
{
  AfxMessageBox("Cannot start Excel.");
        //return;
    }

app.SetVisible(FALSE);
    books.AttachDispatch( app.GetWorkbooks(), TRUE );
LPDISPATCH lpDisp = NULL;
    COleVariant covTrue((short)TRUE);
    COleVariant covFalse((short)FALSE);
    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    Range            oCurCell;

lpDisp = books.Open(lpszFile,
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing),
  _variant_t(vtMissing));

// 获得活动的WorkBook( 工作簿 )
       book.AttachDispatch( lpDisp, TRUE );
// 获得活动的WorkSheet( 工作表 )
       sheet.AttachDispatch( book.GetActiveSheet(), TRUE );
// 获得使用的区域Range( 区域 )
       range.AttachDispatch( sheet.GetUsedRange(), TRUE );

// 获得使用的行数
       long lgUsedRowNum = 0;
       range.AttachDispatch( range.GetRows(), TRUE );
       lgUsedRowNum = range.GetCount();

// 获得使用的列数
       long lgUsedColumnNum = 0;
       range.AttachDispatch( range.GetColumns(), TRUE );
       lgUsedColumnNum = range.GetCount();
// 读取Sheet的名称
       CString strSheetName = sheet.GetName();
//得到全部Cells，此时,CurrRange是cells的集合
       range.AttachDispatch( sheet.GetCells(), TRUE );

// 遍历整个Excel表格
CStringArray* arrayStr;

    int nRecordCount = 1;
    SAVE_ITEM sItem;
       arrayStr = new CStringArray[lgUsedRowNum];
       for ( int i2 = 1; i2 < lgUsedRowNum; )
       {
              for ( int j = 1; j <= lgUsedColumnNum; j++)
              {
                     oCurCell.AttachDispatch( range.GetItem( COleVariant( (long)(i2 + 1)), COleVariant( (long)j ) ).pdispVal, TRUE );
                     VARIANT varItemName = oCurCell.GetText();
                     CString strItem;
      strItem = varItemName.bstrVal;
      //AfxMessageBox(strI　tem);

      switch(j)
      {
      case 1:
       sItem.sCurveName = strItem;
       break;
      case 2:
       sItem.sCurveType = strItem;
       break;
      case 3:
       sItem.sDate1 = strItem;
       break;
      case 4:
       sItem.fRepayDate = atof(strItem);
       break;
      case 5:
       sItem.fWinPercent = atof(strItem);
       break;
      case 6:
          sItem.fNYear = atof(strItem);
       break;
      case 7:
          sItem.fKYear = atof(strItem);
       break;
      }

                     // 判断是否是合并的单元格
                    /* VARIANT varMerge = oCurCell.GetMergeCells();
                     if ( varMerge.boolVal == -1 )
                     {
//                         AfxMessageBox( _T( "是合并的单元格!" ) );
                     }
                     else if ( varMerge.boolVal == 0 )
                     {
//                         AfxMessageBox( _T( "不是合并的单元格!" ) );
                     }*/

                     arrayStr[i2].Add( strItem );
              }
      if(sItem.Valid())
     {
      if(WriteInDB((void*)&sItem)) nRecordCount++;
     }
              i2++;

}

// 更新列表控件数据
       //m_pExcelOperDlg->initListCtrlColumn( lgUsedColumnNum );
       //m_pExcelOperDlg->updateListCtrl#( arrayStr, lgUsedRowNum );

// 释放二维数组
       delete[] arrayStr;

// 关闭
       book.Close( covOptional, COleVariant(lpszFile), covOptional );
       books.Close();
// 释放
       range.ReleaseDispatch();
       sheet.ReleaseDispatch();
       sheets.ReleaseDispatch();
       book.ReleaseDispatch();
       books.ReleaseDispatch();
       app.ReleaseDispatch();
       app.Quit();    // 这条语句是推出Excel程序，任务管理器中的EXCEL进程会自动结束。