数据保存到Excel中，要快速！

Excel模板如下：

     A        B       C
     时间     温度     电流
15   xx.xx    xxx      xx.xx
16   xx.xx    xxx      xx.xx
17   ...      ...      ....
.
.
.
.
.
1200

================================
定义3维数组变量
   m_ADBuffer[3][1200]

将采集的数据放入m_ADBuffer

检测完毕后，将m_ADBuffer中数据保存到Excel文件中

方法：
   1.VB6.0 的写法如下：

    Private Const startrow =15
    index=1200

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(strFilePath & "\" & savename & ".xls", False)
    Set xlSheet = xlBook.Worksheets(1)

    '///关键代码
    xlSheet.Range(xlSheet.Cells(15, 3), xlSheet.Cells(startrow+index-1, 3)).Value = m_ADBuffer

    上面一行代码，一次就将m_ADBuffer的数据全部写入到Excel指定的区域内

   2.VC6.0的代码（用循环一个一个写入，太慢！）

        不知道，VC实现上述代码：xlSheet.Range(xlSheet.Cells(1, 3), xlSheet.Cells(startrow+index-1, 3)).Value = m_ADBuffer
        是怎么做的？


        VC6.0的代码如下：

        //时间温度电流数组
CString tmpColTime,tmpColTemp,tmpColCurr;
CString tmpRowNum="";
CString tmpVal="";
tmpColTime="C";
tmpColTemp="D";
tmpColCurr="E";

for(i=0;i<m_lADIndex;i++)
{
tmpRowNum.Format("%d",i+15);
               //选择时间工作表
tmpVal=tmpColTime+tmpRowNum;
range=sheet.GetRange(COleVariant(tmpVal),covOptional);
tmpStr.Format("%.3f",m_ADExcel[0][i]);
range.SetValue2(COleVariant(tmpStr));

//选择温度工作表
tmpVal=tmpColTemp+tmpRowNum;
range=sheet.GetRange(COleVariant(tmpVal),covOptional);
tmpStr.Format("%.3f",m_ADExcel[1][i]);
range.SetValue2(COleVariant(tmpStr));

//选择电流工作表
tmpVal=tmpColCurr+tmpRowNum;
range=sheet.GetRange(COleVariant(tmpVal),covOptional);
tmpStr.Format("%.3f",m_ADExcel[2][i]);
range.SetValue2(COleVariant(tmpStr));

}

6 个解决方案

#1

自己解决了

#2

恭喜楼主，散发吧，哈哈

#3

恭喜楼主了，怎么解决的?

#4

CoInitialize(NULL);
_Application appExcel;
Workbooks workBooks;
_Workbook workBook;
Worksheets workSheets;
_Worksheet workSheet;

/* Range rangeCell;*/
COleVariant vResult;
COleVariant vOpt((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
if(!appExcel.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Can't Create!");
return ;
}
appExcel.SetVisible(FALSE);
appExcel.SetUserControl(TRUE);

workBooks.AttachDispatch(appExcel.GetWorkbooks());
workBook = workBooks.Add(vOpt);

workSheets = workBook.GetWorksheets();
workSheet = workSheets.GetItem(COleVariant((short)1));
workSheet.SetName("First Sheet");

Range oRange;
    oRange = workSheet.GetRange(COleVariant("A1"), vOpt);
    oRange.SetValue2(COleVariant("Date"));
    oRange = workSheet.GetRange(COleVariant("B1"), vOpt);
    oRange.SetValue2(COleVariant("Order"));
    oRange = workSheet.GetRange(COleVariant("C1"), vOpt);
    oRange.SetValue2(COleVariant("Amount"));
    oRange = workSheet.GetRange(COleVariant("D1"), vOpt);
    oRange.SetValue2(COleVariant("Tax"));
oRange.AttachDispatch(workSheet.GetCells());
short iRow(1),iColumn(1);
char szContent[10];

for (;iColumn <= 4;iColumn++)
{
iRow = 1;
for (;iRow <= 10;iRow++)
{
memset(szContent,0,sizeof(szContent));
sprintf(szContent,"行列:%d,%d",iRow,iColumn);
oRange.SetItem(COleVariant(iRow),COleVariant(iColumn),COleVariant(szContent));
}
}
workBook.SaveAs(COleVariant("D:\\Test.xls"),vOpt,vOpt,vOpt,vOpt,vOpt,0,vOpt,vOpt,vOpt,vOpt,vOpt);
workBook.Close(COleVariant((short)FALSE),COleVariant("D:\\Test.xls"),vOpt);
workBooks.Close();
appExcel.Quit();
CoUninitialize();

#5

楼主可以找个好使的封装类用用

#6

这样效率很低吧，我正在烦恼这个，你用多一些数据测试一下
！

#1