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

MFC 加载 EXCEL库 可以查看
http://t.csdn.cn/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();