MFC 加载 EXCEL库 可以查看
/U8TJU
这里主要记录一下读取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();