1

I write a code to export an excel file from a dbgrid. Here's the following code :

    var i, x:integer;
sfile:string;
begin
XlApp:=createoleobject('Excel.Application');
XlBook:=XlApp.WorkBooks.Add;
XlSheet:= XlBook.worksheets.add;
for i:=0 to dbgrid1.fieldcount-1 do begin
xlsheet.cells[1,i+1].value:=dbgrid1.columns[i].title.caption;
end;
zquery1.first;
x:=1;
while not zquery1.eof do begin
xlsheet.cells[x+1,1].value:=x;
for i:=1 to dbgrid1.fieldcount-1 do begin
xlsheet.cells[x+1, i+1].value:=dbgrid1.fields[i].text;
end;
zquery1.next;
inc(x);
end;
xlapp.visible:=true;
end;

it works just fine, but the table is so messy. How can I customize this excel file so that each column give the correct width value according to each data? thanks.

Ken White
  • 123,280
  • 14
  • 225
  • 444
mizkyd
  • 75
  • 1
  • 11

2 Answers2

2

An Excel Range has an AutoFit method that will do this for you in a single call, and doesn't require you to experiment to figure out what width you need to assign to each column.

Here's an example (tested in Delphi 2007) that creates a two-dimensional variant array, populates it with sample text that is too wide to fit a default Excel cell, assigns that text to an Excel range, and autowidths all cells within that range to the proper width for the text (similar to what happens when you double-click the divider between columns in Excel). You should be able to easily adapt both this way to autowidth the text (and the much faster way of transferring data into Excel via automation) to work with your code.

For demo purposes, I've put the code into a TButton.OnClick event handler.

uses
  ComObj, ActiveX;

procedure TForm3.Button1Click(Sender: TObject);
var
  xls, wb, Range: OLEVariant;
  arrData: Variant;
  RowCount, ColCount, i, j: Integer;
begin
  {create variant array where we'll copy our data}
  RowCount := 10;
  ColCount := 10;
  arrData := VarArrayCreate([1, RowCount, 1, ColCount], varVariant);

  {fill array}
  for i := 1 to RowCount do
    for j := 1 to ColCount do
      arrData[i, j] := Format('This is test text #%d-%d', [i, j]);

  {initialize an instance of Excel}
  xls := CreateOLEObject('Excel.Application');

  {create workbook}
  wb := xls.Workbooks.Add;

  {retrieve a range where data must be placed}
  Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
                                  wb.WorkSheets[1].Cells[RowCount, ColCount]];

  { copy data from the array into an Excel Range, and then use AutoFit to size them }
  Range.Value := arrData;
  Range.Columns.AutoFit;

  {show Excel with our data}
  xls.Visible := True;
end;
Ken White
  • 123,280
  • 14
  • 225
  • 444
0

You can set the width of each column using Columns[i].ColumnWidth.

var
  ColRange: Variant;

ColRange := xlsheet.Columns;
ColRange.Columns[1].ColumnWidth := 12;
W. Chang
  • 494
  • 3
  • 10
  • thank you so much! I'm new at coding so there are so much properties I didn't know! – mizkyd Jul 30 '18 at 02:59
  • I'm actually also trying to format my cell value. I'm trying colrange.columns[1].numberformat:='#,0' but it isn't working. any idea? – mizkyd Jul 30 '18 at 03:31
  • never mind, I found it! instead of '#,0' I use '#.##0' and it works. – mizkyd Jul 30 '18 at 04:48