MFC 加载 EXCEL 并快速读取大量数据

 CApplication app1; CWorkbooks books; CWorkbook book; CWorksheets sheets; CWorksheet sheet; CRange range; CRange oCurCell; LPDISPATCH lpDisp; COleVariant vResult; //COleVariant类是对VARIANT结构的封装 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); COleSafeArray ole_safe_array_; if (S_OK != CoInitialize(NULL)){ 
    return -1; } if (!app1.CreateDispatch(_T("Excel.Application"), NULL)) { 
    MessageBox(NULL,_T("无法启动Excel服务器!"), _T("提示"), MB_ICONWARNING); return 0; } books.AttachDispatch(app1.get_Workbooks()); lpDisp = books.Open(pDlg->m_fileName, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional); //得到Workbook (工作簿) book.AttachDispatch(lpDisp); //得到Worksheets (工作表) sheets.AttachDispatch(book.get_Worksheets()); //sheet = sheets.get_Item(COleVariant((short)1)); //得到当前活跃sheet  //如果有单元格正处于编辑状态中，此操作不能返回，会一直等待  lpDisp = book.get_ActiveSheet(); sheet.AttachDispatch(lpDisp); // 获得使用的区域Range(区域) range.AttachDispatch(sheet.get_UsedRange(), TRUE); // 获得使用的行数 long lgUsedRowNum = 0; range.AttachDispatch(range.get_Rows(), TRUE); lgUsedRowNum = range.get_Count(); //获得使用的列数 long lgUsedColumnNum = 0; range.AttachDispatch(range.get_Columns(), TRUE); lgUsedColumnNum = range.get_Count(); //读出sheet的名称 CString strSheetName = sheet.get_Name(); //得到全部Cells，此时CurrRange是cells的集合 range.AttachDispatch(sheet.get_Cells(), TRUE); //遍历整个excel表格 for (int i = 0; i < lgUsedRowNum; i++)//遍历行 { 
    for (int j = 1; j <= lgUsedColumnNum; j++)//遍历列 { 
    oCurCell.AttachDispatch(range.get_Item(COleVariant((long)(i + 1)), COleVariant((long)j)).pdispVal, TRUE); VARIANT varMerge = oCurCell.get_MergeCells(); VARIANT varItemName = oCurCell.get_Text(); //-----------此处部分可自行修改，varItemName是每个单元格的内容 if (i != 0 && j == 1) { 
    pDlg->m_hospitalRecName.push_back(varItemName); } if (i !=0 && j == 2) { 
    pDlg->m_healthCareName.push_back(varItemName); } //----------- } } books.Close(); app1.Quit(); //释放对象  range.ReleaseDispatch(); oCurCell.ReleaseDispatch(); sheet.ReleaseDispatch(); sheets.ReleaseDispatch(); book.ReleaseDispatch(); books.Close(); books.ReleaseDispatch(); app1.Quit(); //此两条关闭代码顺序不能反，否则无法关闭 app1.ReleaseDispatch(); 

方式二：预先加载所有数据，通过遍历提供的封装数组来得到数据，大数量也速度也不慢（目前excel有9W多条数据，1-2秒就读完了）

 CApplication app1; CWorkbooks books; CWorkbook book; CWorksheets sheets; CWorksheet sheet; CRange range; CRange oCurCell; LPDISPATCH lpDisp; COleVariant vResult; //COleVariant类是对VARIANT结构的封装 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); COleSafeArray ole_safe_array_; if (S_OK != CoInitialize(NULL)){ 
    return -1; } if (!app1.CreateDispatch(_T("Excel.Application"), NULL)) { 
    MessageBox(NULL,_T("无法启动Excel服务器!"), _T("提示"), MB_ICONWARNING); return 0; } books.AttachDispatch(app1.get_Workbooks()); lpDisp = books.Open(pDlg->m_fileName, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional); //得到Workbook (工作簿) book.AttachDispatch(lpDisp); //得到Worksheets (工作表) sheets.AttachDispatch(book.get_Worksheets()); //sheet = sheets.get_Item(COleVariant((short)1)); //得到当前活跃sheet  //如果有单元格正处于编辑状态中，此操作不能返回，会一直等待  lpDisp = book.get_ActiveSheet(); sheet.AttachDispatch(lpDisp); // 获得使用的区域Range(区域) range.AttachDispatch(sheet.get_UsedRange(), TRUE); // 获得使用的行数 long lgUsedRowNum = 0; range.AttachDispatch(range.get_Rows(), TRUE); lgUsedRowNum = range.get_Count(); //获得使用的列数 long lgUsedColumnNum = 0; range.AttachDispatch(range.get_Columns(), TRUE); lgUsedColumnNum = range.get_Count(); VARIANT ret = range.get_Value2(); ole_safe_array_.Attach(ret); COleVariant vresult; long read_address[2]; VARIANT val; CString varItemName; //遍历整个excel表格 for (int i = 2; i <= lgUsedRowNum; i++)//遍历行 { 
    read_address[0] = i; for (int j = 1; j <= lgUsedColumnNum; j++)//遍历列 { 
    read_address[1] = j; ole_safe_array_.GetElement(read_address, &val); vresult = val; varItemName = vresult.bstrVal; if (i != 0 && j == 1) { 
    pDlg->m_hospitalRecName.push_back(varItemName); } if (i != 0 && j == 2) { 
    pDlg->m_healthCareName.push_back(varItemName); } } } books.Close(); app1.Quit(); //释放对象  range.ReleaseDispatch(); oCurCell.ReleaseDispatch(); sheet.ReleaseDispatch(); sheets.ReleaseDispatch(); book.ReleaseDispatch(); books.Close(); books.ReleaseDispatch(); app1.Quit(); //此两条关闭代码顺序不能反，否则无法关闭 app1.ReleaseDispatch();