Yo hice algo similar a lo que tu mencionas de la siguiente manera
Código Delphi
[-]
Procedure TfrmBalanceGasto.ExportarToExcel(fName: string);
const
xlWBATworksheet = -4167;
var
Excel, WorkBook, WorkSheet: OleVariant;
I, J, _FilaIni, _quiebre, _FilaTitulos: Integer;
_Campo :String;
_f, _c :Integer;
_posiciones :array [1..10] of Integer;
const _letras :array [1..13] of string = ('C','D','E','F','G','H','I','J','K','L','M','N','O');
begin
_FilaTitulos := 6;
_Campo := '';
Excel := CreateOleObject('Excel.Application');
Excel.DisplayAlerts := False;
WorkBook := Excel.Workbooks.Add(xlWBATWorksheet);
WorkSheet := WorkBook.WorkSheets[1];
WorkSheet.Name := 'Hoja1';
WorkSheet.Cells[1,1].font.Bold := True;
WorkSheet.Cells[1,1] := dmGlobal.g_NombreEmpresa;
WorkSheet.Cells[2,1] := 'R.U.C. : ' + dmGlobal.g_RUCEmpresa;
WorkSheet.Cells[3,1].font.Bold := True;
WorkSheet.Cells[3,1] := 'Consolidado Anual de Gastos';
WorkSheet.Cells[4,1] := iif (rgRango.ItemIndex = 0, 'Mensual del mes de ','Acumulado al mes de ') + NombreMes(StrToInt(dmGlobal.g_Mes));
WorkSheet.Cells[5,1] := 'Cuenta 9' + edtDigito.Text;
worksheet.range['A3:O5'].HorizontalAlignment := xlHAlignCenterAcrossSelection;
WorkSheet.cells[_FilaTitulos,1] := 'Cuenta';
WorkSheet.cells[_FilaTitulos,3] := 'Enero';
WorkSheet.cells[_FilaTitulos,4] := 'Febrero';
WorkSheet.cells[_FilaTitulos,5] := 'Marzo';
WorkSheet.cells[_FilaTitulos,6] := 'Abril';
WorkSheet.cells[_FilaTitulos,7] := 'Mayo';
WorkSheet.cells[_FilaTitulos,8] := 'Junio';
WorkSheet.cells[_FilaTitulos,9] := 'Julio';
WorkSheet.cells[_FilaTitulos,10] := 'Agosto';
WorkSheet.cells[_FilaTitulos,11] := 'Setiembre';
WorkSheet.cells[_FilaTitulos,12] := 'Octubre';
WorkSheet.cells[_FilaTitulos,13] := 'Noviembre';
WorkSheet.cells[_FilaTitulos,14] := 'Diciembre';
WorkSheet.cells[_FilaTitulos,15] := 'Total';
worksheet.range['A5:O5'].HorizontalAlignment := xlHAlignCenter;
i:= _FilaTitulos + 1;
_FilaIni := i;
_quiebre := 0;
DBGrid1.DataSource.DataSet.First;
_Campo := Izquierda(DBGrid1.Fields[0].AsString, StrToInt(edtDigito.Text));
while not DBGrid1.DataSource.DataSet.Eof do
begin
for J := 0 to DBGrid1.FieldCount -1 do
case DBGrid1.Fields[J].DataType of
ftAutoInc, ftBytes, ftInteger, ftSmallint, ftWord: WorkSheet.Cells[I, J +1] := DBGrid1.Fields[J].AsInteger;
ftBCD, ftFloat, ftCurrency: WorkSheet.Cells[I, J +1] := DBGrid1.Fields[J].AsFloat;
ftDateTime, ftDate, ftTime: WorkSheet.Cells[I, J +1] := DBGrid1.Fields[J].AsDateTime;
else WorkSheet.Cells[I, J +1] := DBGrid1.Fields[J].AsString;
end;
inc (i);
ProgressBarXls.Position := ProgressBarXls.Position +1;
DBGrid1.DataSource.DataSet.Next;
if edtDigito.Text <> Derecha(dmGlobal.g_NivelCuenta,1) then
begin
if (_Campo <> Izquierda(DBGrid1.Fields[0].AsString, StrToInt(edtDigito.Text))) or (DBGrid1.DataSource.DataSet.Eof) Then
begin
_Campo := Izquierda(DBGrid1.Fields[0].AsString, StrToInt(edtDigito.Text));
WorkSheet.cells[i,3] := '=Sum(C' + IntToStr(_Filaini) + ':C' + IntToStr(i-1) + ')';
WorkSheet.cells[i,4] := '=Sum(D' + IntToStr(_Filaini) + '' + IntToStr(i-1) + ')';
WorkSheet.cells[i,5] := '=Sum(E' + IntToStr(_Filaini) + ':E' + IntToStr(i-1) + ')';
WorkSheet.cells[i,6] := '=Sum(F' + IntToStr(_Filaini) + ':F' + IntToStr(i-1) + ')';
WorkSheet.cells[i,7] := '=Sum(G' + IntToStr(_Filaini) + ':G' + IntToStr(i-1) + ')';
WorkSheet.cells[i,8] := '=Sum(H' + IntToStr(_Filaini) + ':H' + IntToStr(i-1) + ')';
WorkSheet.cells[i,9] := '=Sum(I' + IntToStr(_Filaini) + ':I' + IntToStr(i-1) + ')';
WorkSheet.cells[i,10] := '=Sum(J' + IntToStr(_Filaini) + ':J' + IntToStr(i-1) + ')';
WorkSheet.cells[i,11] := '=Sum(K' + IntToStr(_Filaini) + ':K' + IntToStr(i-1) + ')';
WorkSheet.cells[i,12] := '=Sum(L' + IntToStr(_Filaini) + ':L' + IntToStr(i-1) + ')';
WorkSheet.cells[i,13] := '=Sum(M' + IntToStr(_Filaini) + ':M' + IntToStr(i-1) + ')';
WorkSheet.cells[i,14] := '=Sum(N' + IntToStr(_Filaini) + ':N' + IntToStr(i-1) + ')';
WorkSheet.cells[i,15] := '=Sum(O' + IntToStr(_Filaini) + ':O' + IntToStr(i-1) + ')';
Inc(_quiebre);
_posiciones [_quiebre] := i;
Inc(i);
_FilaIni := i;
end;
end;
end;
if _posiciones[1] <> 0 then
Begin
for _c := 1 to 13 do
Begin
_Campo := '';
for _f := 1 to 10 do
if _posiciones [_f] <> 0 then
_Campo := _campo + '+' + _letras[_C] + IntToStr(_posiciones[_f]);
WorkSheet.cells [i,2 + _c] := '= Sum( ' + _Campo + ')';
End;
End;
WorkSheet.Cells.Columns.AutoFit;
WorkBook.SaveAs(fname);
WorkBook.Close(fName);
Excel.Quit;
end;
lo que si debes tener cuidado es en la version del excel que tengas.. he visto algunos casos en que se debe colocar =SUMA... en lugar de =SUM