C++软件开发中,利用ATL操作Excel文件时,通常是对每个单元格依次操作,读写效率较低;写入时如果包含公式,Excel还会进行运算,严重影响用户使用体验。为改进读写速度,考虑同时对多个单元格进行读写操作,需要使用COleSafeArray对CRange的内容进行批量读写。
前提
需要引入Excel的类型库,可以采用import方式,也可直接导入已有的CApplication.h、CWorkbooks.h、CWorkbook.h、CWorksheets.h、CWorksheet、CRange.h等头文件。
批量写入数据
首先声明一个COleSafeArray变量,然后通过COleSafeArray::Create方法创建改变量,Create方法定义如下:
void Create(VARTYPE vtSrc, DWORD dwDims, DWORD rgElements);*
参数vtSrc为该变量内存储数据的类型,可根据写入数据类型进行选择如VT_BSTR(字符串类型)或VT_I8(整数类型)等;如果写入数据包含多个类型,可使用VT_VARIANT来写入。
参数dwDims用来定义维度,读写Excel是通常是1维或2维。
参数rgElements维指向定义了各维度边界的数组。
编写过程中建议使用COleVariant类型代替VARIANT类型数据,COleVariant是对VARIANT的封装,使用更安全方便。
实现代码:
BOOL CSafeArrayTestDlg::WriteExcelBySafeArray(CString strFilePath/*保存文件路径*/)
{
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CApplication elApp;
CWorkbook elBook;
CWorksheet elSheet;
CWorkbooks elBooks;
CWorksheets elSheets;
CRange elRange, elAllRange;
if (!elApp.CreateDispatch(_T("Excel.Application"), NULL))
return FALSE;
// 是否允许其他用户控制Excel
elApp.put_UserControl(FALSE);
// Excel是否可视
elApp.put_Visible(FALSE);
// 程序是否显示告警
elApp.put_DisplayAlerts(FALSE);
LPDISPATCH lpDisp; //接口指针
elBooks.AttachDispatch(elApp.get_Workbooks());
elBook.AttachDispatch(elBooks.Add(covOptional));
elSheet.AttachDispatch(elBook.get_ActiveSheet());
elAllRange.AttachDispatch(elSheet.get_Cells());
//通过起始单元格和结束单元格定位elRange
elRange.AttachDispatch(elSheet.get_Range(elAllRange.get_Item(COleVariant(long(1)), COleVariant(long(1))), elAllRange.get_Item(COleVariant(long(2)), COleVariant(long(2)))));
COleSafeArray saRet; //定义一个安全数组SAFEARRAY
DWORD numElements[2]; //定义各维度边界数组
numElements[0] = 2; // 在elRange中的行数.
numElements[1] = 2; // 在elRange中的列数.
long index[2]; //单元格在elRange中行号列号的数组
//创建一个VARIANT类型的COleSafeArray
saRet.Create(VT_VARIANT, 2, numElements);
//为安全数组saRet内各个元素赋值
//可以为整形、字符串、浮点行、公式等
index[0] = 0;
index[1] = 0;
saRet.PutElement(index, COleVariant(long(12)));
index[0] = 0;
index[1] = 1;
saRet.PutElement(index, COleVariant(_T("safearray")));
index[0] = 1;
index[1] = 0;
saRet.PutElement(index, COleVariant(12.5));
index[0] = 1;
index[1] = 1;
saRet.PutElement(index, COleVariant(_T("=A1+A2")));
//将saRet的值写入elRange的各个单元格中
elRange.put_Value2(COleVariant(saRet));
//保存文件
elBook.SaveAs(COleVariant(strFilePath), covOptional, covOptional,
covOptional, covOptional, covOptional, 0, covOptional,
covOptional, covOptional, covOptional, covOptional);
//释放资源
elRange.ReleaseDispatch();
elSheet.ReleaseDispatch();
elSheets.ReleaseDispatch();
elBook.Close(covOptional, COleVariant(strFilePath), covOptional);
elBook.ReleaseDispatch();
elBooks.Close();
elBooks.ReleaseDispatch();
elApp.Quit();
elApp.ReleaseDispatch();
return TRUE;
}
运行结果如下图
批量写入公式
写入公式时,如需要公式在写入后集中计算可采用先以VT_BSTR类型写入Excel文件特定区域,才使用CRange::Replace函数激活所有公式,实现代码如下:
BOOL CSafeArrayTestDlg::WriteExcelBySafeArray(CString strFilePath)
{
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CApplication elApp;
CWorkbook elBook;
CWorksheet elSheet;
CWorkbooks elBooks;
CWorksheets elSheets;
CRange elRange, elAllRange;
if (!elApp.CreateDispatch(_T("Excel.Application"), NULL))
return FALSE;
// 是否允许其他用户控制Excel
elApp.put_UserControl(FALSE);
// Excel程序是否可视
elApp.put_Visible(FALSE);
elApp.put_DisplayAlerts(FALSE);
LPDISPATCH lpDisp; //接口指针
elBooks.AttachDispatch(elApp.get_Workbooks());
elBook.AttachDispatch(elBooks.Add(covOptional));
elSheet.AttachDispatch(elBook.get_ActiveSheet());
elAllRange.AttachDispatch(elSheet.get_Cells());
elRange.AttachDispatch(elSheet.get_Range(elAllRange.get_Item(COleVariant(long(1)), COleVariant(long(1))), elAllRange.get_Item(COleVariant(long(2)), COleVariant(long(2)))));
COleSafeArray saRet; //定义一个安全数组SAFEARRAY
DWORD numElements[2]; //设置安全数组的边界
numElements[0] = 2; // 在elRange中的行数.
numElements[1] = 2; // 在elRange中的列数.
//创建一个二维数组,数组中存放的元素类型为VT_BSTR
saRet.Create(VT_BSTR, 2, numElements);
CString strData[4] = { _T("a"),_T("=A1&A2"), _T("c"), _T("=A1&B1&A2") };
long index[2];
for (int i = 0; i < 2; i++)
{
for (int j = 0; j < 2; j++)
{
index[0] = i;
index[1] = j;
saRet.PutElement(index, strData[i * 2 + j].AllocSysString());
}
}
elRange.put_Value2(COleVariant(saRet));
//替换区域内字符串中的"=",主要用来激活公式计算
elRange.Replace(COleVariant(_T("=")), COleVariant(_T("=")), COleVariant((long)2), COleVariant((long)1), covOptional, covOptional, covOptional, covOptional);
elBook.SaveAs(COleVariant(strFilePath), covOptional, covOptional,
covOptional, covOptional, covOptional, 0, covOptional,
covOptional, covOptional, covOptional, covOptional);
elRange.ReleaseDispatch();
elSheet.ReleaseDispatch();
elSheets.ReleaseDispatch();
elBook.Close(covOptional, COleVariant(strFilePath), covOptional);
elBook.ReleaseDispatch();
elBooks.Close();
elBooks.ReleaseDispatch();
elApp.Quit();
elApp.ReleaseDispatch();
return TRUE;
}
运行结果如下:
无Replace的运行结果:
使用Replace的运行结果:
批量读取数据
BOOL CSafeArrayTestDlg::ReadExcelBySafeArray(CString strFilePath)
{
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CApplication elApp;
CWorkbook elBook;
CWorksheet elSheet;
CWorkbooks elBooks;
CWorksheets elSheets;
CRange elRange, elAllRange;
if (!elApp.CreateDispatch(_T("Excel.Application"), NULL))
return FALSE;
// 是否允许其他用户控制Excel
elApp.put_UserControl(FALSE);
// Excel程序是否可视
elApp.put_Visible(FALSE);
elApp.put_DisplayAlerts(FALSE);
LPDISPATCH lpDisp; //接口指针
elBooks.AttachDispatch(elApp.get_Workbooks());
lpDisp = elBooks.Open(strFilePath, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional);
// 得到WorkBook
elBook.AttachDispatch(lpDisp);
elSheet.AttachDispatch(elBook.get_ActiveSheet());
elAllRange.AttachDispatch(elSheet.get_Cells());
elRange.AttachDispatch(elSheet.get_Range(elAllRange.get_Item(COleVariant(long(1)), COleVariant(long(1))), elAllRange.get_Item(COleVariant(long(2)), COleVariant(long(2)))));
COleSafeArray saRet; //定义一个安全数组SAFEARRAY
DWORD numElements[2]; //设置安全数组的边界
numElements[0] = 2; // 在elRange中的行数.
numElements[1] = 2; // 在elRange中的列数.
saRet.Create(VT_VARIANT, 2, numElements);
saRet = elRange.get_Value2();
long index[2];
//这里COleSafeArray的行列坐标从1开始,不是从0开始,因此index[0] = 1;index[1] = 1;表示取第一行第一列的值,而不是index[0] = 0;index[1] = 0;
index[0] = 1;
index[1] = 1;
COleVariant t;
saRet.GetElement(index, t);
//可根据t的vt值对不同类型数据进行分类处理,这里直接转为字符串输出
CString strValue = t;
MessageBox(strValue);
elRange.ReleaseDispatch();
elSheet.ReleaseDispatch();
elSheets.ReleaseDispatch();
elBook.Close(covOptional, covOptional, covOptional);
elBook.ReleaseDispatch();
elBooks.Close();
elBooks.ReleaseDispatch();
elApp.Quit();
elApp.ReleaseDispatch();
return TRUE;
}
可根据COleVariant中的vt值对不同类型进行区分处理。
需要注意在读取时GetElement中保存行列坐标的数组index要从1开始,因此取saRet中第一行第一列的值需要对index赋值index[0]=1 index[1]=1而不是index[0]=0 index[1]=0。