Export Text File to Excel File

Author: admin | Category: Files, Tips and Tricks

//This is how to convert or export text file to Ms. Excel File
//SaveAsExcelFile function taken from Torry's Delphi Pages http://www.swissdelphicenter.ch/torry/
//Use button1 (Tbutton) in your Form1 then try this code
 
unit Unit1;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids;
 
type
  TForm1 = class(TForm)
    Button1: TButton;
    OpenDialog1: TOpenDialog;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    StringGridku:TStringGrid;
  end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.dfm}
 
uses
  ComObj;
 
function RefToCell(ARow, ACol: Integer): string;
begin
  Result := Chr(Ord('A') + ACol - 1) + IntToStr(ARow);
end;
 
function SaveAsExcelFile(AGrid: TStringGrid; ASheetName, AFileName: string): Boolean;
const
  xlWBATWorksheet = -4167;
var
  Row, Col: Integer;
  GridPrevFile: string;
  XLApp, Sheet, Data: OLEVariant;
  i, j: Integer;
begin
  // Prepare Data
  Data := VarArrayCreate([1, AGrid.RowCount, 1, AGrid.ColCount], varVariant);
  for i := 0 to AGrid.ColCount - 1 do
    for j := 0 to AGrid.RowCount - 1 do
      Data[j + 1, i + 1] := AGrid.Cells[i, j];
  // Create Excel-OLE Object
  Result := False;
  XLApp := CreateOleObject('Excel.Application');
  try
    // Hide Excel
    XLApp.Visible := False;
    // Add new Workbook
    XLApp.Workbooks.Add(xlWBatWorkSheet);
    Sheet := XLApp.Workbooks[1].WorkSheets[1];
    Sheet.Name := ASheetName;
    // Fill up the sheet
    Sheet.Range[RefToCell(1, 1), RefToCell(AGrid.RowCount,
      AGrid.ColCount)].Value := Data;
    // Save Excel Worksheet
    try
      XLApp.Workbooks[1].SaveAs(AFileName);
      Result := True;
    except
      // Error ?
    end;
  finally
    // Quit Excel
    if not VarIsEmpty(XLApp) then
    begin
      XLApp.DisplayAlerts := False;
      XLApp.Quit;
      XLAPP := Unassigned;
      Sheet := Unassigned;
    end;
  end;
end;
 
procedure TForm1.Button1Click(Sender: TObject);
var
  StringListKu:TStrings;
  i:integer;
begin
  StringGridku := TStringGrid.Create(self);
  StringListKu := TStringList.Create;
  try
    if OpenDialog1.Execute then
    begin
      StringListKu.LoadFromFile(OpenDialog1.FileName);
      for i := 0 to StringListKu.Count - 1 do
      begin
        StringGridku.Cells[0,i] := StringListKu.Strings[i]
      end;
      if SaveAsExcelFile(StringGridku, 'Datakuuuu', 'c:\MyExcelFile.xls') then
      ShowMessage('StringGrid tersimpan di c:\MyExcelFile.xls');
    end;
  finally
    StringGridku.Free;
    StringListKu.Free;
  end;
end;
 
end.

Tags: Excel, Files, StringGrid