C++实现批量快速写入数据到Excel

最新推荐文章于 2023-07-06 16:52:06 发布

hjatcq

最新推荐文章于 2023-07-06 16:52:06 发布

阅读量2.1k

点赞数 3

文章标签： excel c++

本文链接：https://blog.csdn.net/hjatcq/article/details/127966780

版权

网上有很多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。