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

53 阅读 0 评论 35 点赞

我是靠谱客的博主健忘龙猫，最近开发中收集的这篇文章主要介绍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();