[Delphi] Excel 변환

FireMonkey Desktop Application으로 프로젝트 생성(VCL Forms Application 생성시에도 동일)

uses절에 ComObj, VCL.OleCtrls 추가.

Form에 Button, SaveDialog 생성.

unit Unit1;

interface

uses

  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants, 

  FMX.Types, FMX.Graphics, FMX.Controls, FMX.Forms, FMX.Dialogs, FMX.StdCtrls,  ComObj, VCL.OleCtrls;

const

   xlBottom = -4107;

   xlLeft = -4131;

   xlRight = -4152;

   xlTop = -4160;

   xlHAlignCenter = -4108;

   xlVAlignCenter = -4108;

type

  TForm1 = class(TForm)

    SaveDialog1: TSaveDialog;

    Button1: TButton;

    procedure Button1Click(Sender: TObject);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

var

  Form1: TForm1;

implementation

{$R *.fmx}

procedure TForm1.Button1Click(Sender: TObject);

var

  xlApp, xlSheet, xlRange : Variant;

  xl_FileName : string;

  tempColor : TColor;

  rowPoint : Integer;

begin

  try

    xlApp := CreateOleObject('Excel.Application');

  except

    FMX.Dialogs.MessageDlg('Excel이 설치되어 있지 않습니다.', TMsgDlgType.mtConfirmation, [TMsgDlgBtn.mbOK], 1);

    Exit;

  end;

  try

    SaveDialog1.FileName := 'excelText.xls';

    if SaveDialog1.Execute then

    begin

      xl_FileName:= SaveDialog1.FileName;

      //if FileExists(xl_FileName) then

      //    DeleteFile(xl_FileName);

      tempColor := $00C0C0C0;

      xlApp := CreateOleObject('Excel.Application');

      // WorkSheet1

      xlApp.WorkBooks.Add;

      xlApp.WorkBooks[1].WorkSheets[1].Name := 'Sheet1';

      xlSheet := xlApp.WorkBooks[1].WorkSheets[xlApp.WorkBooks[1].WorkSheets[1].Name];

      xlRange := xlSheet.Range['A1:J1'];

      xlRange.Columns[1].ColumnWidth := 6;

      xlRange.Columns[2].ColumnWidth := 25;

      xlRange.Columns[3].ColumnWidth := 18;

      xlRange.Columns[4].ColumnWidth := 10;

      xlRange.Columns[5].ColumnWidth := 20;

      xlRange.Columns[6].ColumnWidth := 10;

      xlRange.Columns[7].ColumnWidth := 13;

      xlRange.Columns[8].ColumnWidth := 13;

      xlRange.Columns[9].ColumnWidth := 18;

      xlRange.Columns[10].ColumnWidth := 18;

      xlRange.Font.Size := 10;

      xlRange.Columns.Interior.Color := tempColor;

      xlRange.Borders.LineStyle := 1;

      xlRange.HorizontalAlignment := xlHAlignCenter;

      xlSheet.Cells[1, 1] := '컬럼1';

      xlSheet.Cells[1, 2] := '컬럼2';

      xlSheet.Cells[1, 3] := '컬럼3';

      xlSheet.Cells[1, 4] := '컬럼4';

      xlSheet.Cells[1, 5] := '컬럼5';

      xlSheet.Cells[1, 6] := '컬럼6';

      xlSheet.Cells[1, 7] := '컬럼7';

      xlSheet.Cells[1, 8] := '컬럼8';

      xlSheet.Cells[1, 9] := '컬럼9';

      xlSheet.Cells[1, 10] := '컬럼10';

      rowPoint := 2;

      xlRange := xlSheet.Range['A' + IntToStr(rowPoint) + ':J' + IntToStr(rowPoint)];

      xlRange.Borders.LineStyle := 1;

      xlRange := xlSheet.Range['A' + IntToStr(rowPoint) + ':A' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlHAlignCenter;

      xlRange := xlSheet.Range['B' + IntToStr(rowPoint) + ':B' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlLeft;

      xlRange := xlSheet.Range['C' + IntToStr(rowPoint) + ':C' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlHAlignCenter;

      xlRange.NumberFormat := '######-#######';

      xlRange := xlSheet.Range['D' + IntToStr(rowPoint) + ':D' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlRight;

      xlRange.NumberFormat := '#,##0';

      xlRange := xlSheet.Range['E' + IntToStr(rowPoint) + ':E' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlLeft;

      xlRange := xlSheet.Range['F' + IntToStr(rowPoint) + ':F' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlRight;

      xlRange.NumberFormat := '#,##0';

      xlRange := xlSheet.Range['G' + IntToStr(rowPoint) + ':H' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlHAlignCenter;

      xlRange := xlSheet.Range['I' + IntToStr(rowPoint) + ':JH' + IntToStr(rowPoint)];

      xlRange.HorizontalAlignment := xlRight;

      xlRange.NumberFormat := '#,##0';

      xlSheet.Cells[rowPoint, 1] := rowPoint - 1;

      xlSheet.Cells[rowPoint, 2] := '컬럼2 데이터';

      xlSheet.Cells[rowPoint, 3] := '1234561234567';

      xlSheet.Cells[rowPoint, 4] := '10000';

      xlSheet.Cells[rowPoint, 5] := '컬럼5 데이터';

      xlSheet.Cells[rowPoint, 6] := '20000';

      xlSheet.Cells[rowPoint, 7] := '컬럼7 데이터';

      xlSheet.Cells[rowPoint, 8] := '컬럼8 데이터';

      xlSheet.Cells[rowPoint, 9] := '30000';

      xlSheet.Cells[rowPoint, 10] := '40000';

      Inc(rowPoint);

      if FileExists(xl_FileName) then

          DeleteFile(xl_FileName);

      xlApp.DisplayAlerts := False;

      xlApp.ActiveWorkBook.SaveAs(xl_FileName);

      //엑셀 자동 실행 않기

      //if not VarIsEmpty(xlApp) then begin

      //  xlApp.Quit;

      //end;

      ShowMessage('엑셀파일로 변환되었습니다!');

      xlApp.Visible := True;

      /////////////////////////////////////////////////////////////////////////////////////////

    end;

  except

    if not VarIsEmpty(xlApp) then begin

       xlApp.Quit;

    end;

  end;

end;

end.