将查询数据导出至Excel时“Bad variable type”异常的排查与解决

问题现象 #

在Delphi中编写查询数据导出至Excel的代码时，执行Range.Value := arrData;语句触发EOleSysError异常，提示信息为“Bad variable type”。仅当查询语句包含日期时间字段ExpDate和计算字段summa（由quantity*price得到）时会触发该异常，移除这两个字段则导出正常。已引入excel2000、ComObj单元。

相关代码 #

导出实现代码 #

procedure Tform1.BExcelClick(Sender: TObject);
var 
  row,col,i,j:integer;
  xls, wb, Range: OLEVariant;
  arrData: OleVariant;
begin
 Query1.Close;
 Query1.Open;
                
 row := Query1.RecordCount;
 col := Query1.FieldCount;
 arrData := VarArrayCreate([1, row, 1, col], varVariant);
                   
 i:=1;
 while not query1.Eof do begin
  for j:=1 to col do
    arrData[i,j] := query1.Fields[j-1].Value;
    query1.Next;
    Inc(i);
  end;
                                                                             
//or tried this   
{for i:=1 to row do begin                     
  for j:=1 to col do
    arrData[i,j] := Query1.Fields[j-1].Value;
     Query1.Next;
  end;
 }
           
 xls := CreateOLEObject('Excel.Application');                                  
 wb := xls.Workbooks.Add;     
              
 for j:=1 to col do
     WB.Worksheets[1].Cells[1,j]:= dbgrid1.Columns[j-1].Title.Caption;
                
 Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[2, 1], wb.WorkSheets[1].Cells[row+1, col]];
 Range.Value := arrData;
 xls.Visible := True;
end;

触发异常的SQL查询语句 #

select 
 g.GoodsName,   --goods name 
 s.qnt,         --quantity of sold good
 s.Price,       -- price
 s.rg*s.price  as summa,       --触发异常字段
 i.ExpDate,   --expDate        --触发异常字段   
 t.temp       --store temperature
from goods g,sales s,  incomes i, temperatures t
where     (s.Doc_id=:Doc_id) and
          (s.goods_id= g.goods_id)and
          (s.record_id=i.record_id)and
          (g.temp_id=t.id)
group by g.goodsname, s.qnt, s.Price, summa, s.ExpDate, t.temp
order by g.GoodsName

解决方案 #

将循环中的字段值赋值语句显式转换为OleVariant类型：

原代码：

arrData[i,j] := query1.Fields[j-1].Value;

修改为：

arrData[i,j] := OleVariant(Query1.Fields[j-1].Value);

内容的提问来源于stack exchange，提问作者basti