©2020-2024 ioDraw All rights reserved,
Privacy Policy
网上有很多C++利用OLE读取Excel的例子，其中有提到利用COleSafeArray类来预加载数据来提升效率，但是没有实现快速的写入大量数据到Excel，今天专门写了一个方法，同样利用COleSafeArray类将大量数据一次性的写入Excel，提高了效率，参考代码如下：
void CExcelFile::SetString(long startRow, long startCol, long RowCount, long
ColCount, vector< vector<CComBSTR> >& writeData)
{
if (startRow < 1 || startCol < 1)
{
return;
}
VARTYPE vt = VT_BSTR; /*数组元素的类型，string*/
SAFEARRAYBOUND sabWrite[2]; /*用于定义数组的维数和下标的起始值*/
sabWrite[0].cElements = RowCount;
sabWrite[0].lLbound = 0;
sabWrite[1].cElements = ColCount;
sabWrite[1].lLbound = 0;
COleSafeArray olesaWrite;
olesaWrite.Create(vt, sizeof(sabWrite) / sizeof(SAFEARRAYBOUND),
sabWrite);
/*通过指向数组的指针来对二维数组的元素进行间接赋值*/
long(*pArray)[2] = NULL;
olesaWrite.AccessData((void **)&pArray);
memset(pArray, 0, sabWrite[0].cElements * sabWrite[1].cElements *
sizeof(long));
/*释放指向数组的指针*/
olesaWrite.UnaccessData();
pArray = NULL;
/*对二维数组的元素进行逐个赋值*/
long index[2] = { 0, 0 };
long lFirstLBound = 0;
long lFirstUBound = 0;
long lSecondLBound = 0;
long lSecondUBound = 0;
olesaWrite.GetLBound(1, &lFirstLBound);
olesaWrite.GetUBound(1, &lFirstUBound);
olesaWrite.GetLBound(2, &lSecondLBound);
olesaWrite.GetUBound(2, &lSecondUBound);
for (long i = lFirstLBound; i <= lFirstUBound; i++)
{
index[0] = i;
for (long j = lSecondLBound; j <= lSecondUBound; j++)
{
index[1] = j;
//防止越界而且不写入空值
if ((i < writeData.size()) && (j < writeData[i].size()) &&
(writeData[i][j].Length() != 0))
{
olesaWrite.PutElement(index, (void*)writeData[i][j]);
}
}
}
CString strStartRow;
CString strStartCol;
strStartRow.Format(_T("%d"), startRow);
strStartCol = ConvertNumColtoStringCol(startCol);
CString strRange1;
strRange1 = strStartCol + strStartRow;
CString strEndRow;
CString strEndCol;
strEndRow.Format(_T("%d"), startRow + RowCount - 1);
strEndCol = ConvertNumColtoStringCol(startCol + ColCount - 1);
CString strRange2;
strRange2 = strEndCol + strEndRow;
///
CRange start_range = excel_work_sheet_.get_Range(COleVariant(strRange1),
COleVariant(strRange2));
start_range.put_Value2((VARIANT)olesaWrite);
start_range.ReleaseDispatch();
}
//将Excel数字列转为字符串列，1--A
CString CExcelFile::ConvertNumColtoStringCol(long Col)
{
CString strBack;
do {
Col--;
int n = Col % 26;
CString strTemp;
strTemp.Format(_T("%c"), n + (int) 'A');
strBack += strTemp;
Col = (int)((Col - n) / 26);
} while (Col > 0);
return strBack;
}
需要注意的地方是，字符串必须要要BSTR类型，不然会提示内存不足，我这里用的是CComBSTR。