델파이6 기준으로 TexcelApplication,
TExcelWorkbook, TExcelWorksheet, TexcelChart 올려놓고 실행하면 됩니다.
uses절에 Activex 추가하시고 다음과 같이 코딩하면 됩니다.
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, excel2000, OleServer, Activex;
type
TForm1 = class(TForm)
Button1: TButton;
ExcelApplication1: TexcelApplication;
ExcelWorkbook1: TexcelWorkbook;
ExcelWorksheet1: TexcelWorksheet;
ExcelChart1: TexcelChart;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
var
LCID,i : Integer;
Sheet, Selection : Variant;
Format : OleVariant;
//차트용
ChObj: ChartObject;
SheetType: OleVariant;
Rnge, ChType: OleVariant;
Ax: Axis;
begin
excelApplication1.Connect; //엑셀을 가동한다.(InVisible 상태)
ExcelWorkbook1.connectto(excelApplication1.workbooks.add(TOleEnum(xlWBATWorksheet), LCID));
ExcelWorksheet1.connectto(excelWorkbook1.worksheets.item['Sheet1'] as _worksheet );
//워크시트 이름 변경
excelWorksheet1.Name := '날 죽여라';
excelApplication1.DisplayAlerts[LCID] := False;
excelApplication1.Visible[LCID] := true;
Sheet := ExcelApplication1.WorkBooks[ExcelApplication1.Workbooks.Count].WorkSheets[excelWorkbook1.Worksheets.Count];
Sheet.Cells[1,1] := '엑셀서식';
excelApplication1.Range['A1','A1'].borders.lineStyle := 1;
excelApplication1.Range['A1','A1'].borders.Color := clNavy;
excelApplication1.Range['A1','A1'].Interior.Color := clYellow;
//폰트변경
excelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[1,1]].font.bold := true;
excelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[1,1]].font.Size := 20;
excelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[1,1]].font.Name := '±A¼';
//우측정렬(가로정렬)
excelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[1,1]].HorizontalAlignment := xlHAlignRight;
//가운데정렬(세로정렬)
excelApplication1.Range['B1','B1'].VerticalAlignment := xlHAlignCenter;
//범위로 찍을 경우
excelApplication1.Range['B1','C2'].Value := '123456789';
//숫자형 포맷
Format := '_-* #,##0.0_-;-* #,##0.0_-;_-* "-"???_-;_-@_-';
'@'; // 테스트형식
#,##0.0 // 숫자형식
excelApplication1.Range['B1','B1'].NumberFormatLocal := Format;
Sheet.Range['B2', 'C2'].Interior.Color := RGB(223, 123, 123);
excelApplication1.Range['B4', 'C4'].Interior.Color := clSilver;
//날짜 찍기
Sheet.Cells[5,1] := '2002/5/6';
Sheet.Cells[5,2] := '2002/5/6';
//숫자형
Sheet.Cells[5,3] := '12345';
Sheet.Cells[5,4] := '12345';
//날짜포맷
Format := 'yyyy-mm-dd';
excelWorksheet1.Range[Sheet.Cells[5,1], Sheet.Cells[5,1]].NumberFormat := Format;
Format := 'mmmm d, yyyy';
excelWorksheet1.Range[Sheet.Cells[5,2], Sheet.Cells[5,2]].NumberFormat := Format;
// 스트링형으로 변경
Format := '@';
excelWorksheet1.Range[Sheet.Cells[5,3], Sheet.Cells[5,3]].NumberFormat := Format;
excelWorksheet1.Range['B11','B11'].VerticalAlignment := xlHAlignCenter;
excelWorksheet1.Range['B11','B11'].HorizontalAlignment := xlHAlignRight;
excelWorksheet1.Range['B11','B11'].Value := '셀병합후 가운데(세로) 정렬';
excelWorksheet1.Range['B11','B13'].MergeCells := true;
excelWorksheet1.Range['B11','B13'].borders.LineStyle := 2;
excelWorksheet1.Range['B15','B15'].borders.lineStyle := 0;
excelWorksheet1.Range['B15','B15'].HorizontalAlignment := xlHAlignRight;
excelWorksheet1.Range['B15','B15'].Value := '셀병합후 우측(가로) 정렬';
excelWorksheet1.Range['B15','D15'].MergeCells := true;
excelWorksheet1.Range['B15','D15'].borders.LineStyle := 1;
excelWorksheet1.Range['F15','G20'].MergeCells := true;
excelWorksheet1.Range['F15','F15'].Value := '다중셀병합';
excelWorksheet1.Range['F15','G20'].MergeCells := true;
excelWorksheet1.Range['F15','F15'].HorizontalAlignment := xlHAlignCenter;
excelWorksheet1.Range['F15','F15'].VerticalAlignment := xlHAlignCenter;
excelWorksheet1.Range['F15','G20'].borders.Weight := 4;
//라인 스타일
for i := 0 to 13 do
begin
excelWorksheet1.Range['B'+inttostr((2*i)+16),'B'+inttostr((2*i)+16)].borders.lineStyle := i;
excelWorksheet1.Range['B'+inttostr((2*i)+16),'B'+inttostr((2*i)+16)].Value := 'borders.lineStyle := '+inttostr(i);
end;
//border Weight
for i := 1 to 4 do
begin
excelWorksheet1.Range['B'+inttostr((2*i)+42),'B'+inttostr((2*i)+42)].borders.lineStyle := 1;
excelWorksheet1.Range['B'+inttostr((2*i)+42),'B'+inttostr((2*i)+42)].borders.Weight := 1;
excelWorksheet1.Range['B'+inttostr((2*i)+42),'B'+inttostr((2*i)+42)].Value := 'borders.Weight := '+inttostr(i);
end;
//라인 위치
excelWorksheet1.Range['D18','D18'].borders.Item[1].LineStyle := 1;
excelWorksheet1.Range['D18','D18'].Value := 'borders.Item[1].LineStyle := 1';
excelWorksheet1.Range['D20','D20'].borders.Item[2].LineStyle := 1;
excelWorksheet1.Range['D20','D20'].Value := 'borders.Item[2].LineStyle := 1';
excelWorksheet1.Range['D22','D22'].borders.Item[3].LineStyle := 1;
excelWorksheet1.Range['D22','D22'].Value := 'borders.Item[3].LineStyle := 1';
excelWorksheet1.Range['D24','D24'].borders.Item[4].LineStyle := 1;
excelWorksheet1.Range['D24','D24'].Value := 'borders.Item[4].LineStyle := 1';
//패턴 변경
for i := 1 to 18 do
begin
excelWorksheet1.Range['D'+inttostr(i+24),'D'+inttostr(i+24)].Interior.Pattern := i;
excelWorksheet1.Range['E'+inttostr(i+24),'E'+inttostr(i+24)].Value := 'Interior.Pattern := '+inttostr(i);
end;
{ 이미지를 삽입할 경우 실제파일을 기록해야 되기 때문에 주석처리
실제 파일과 경로명 기록하고 주석푸시고 싱행해보세요
//백그라운드 이미지
//excelWorksheet1.SetBackgroundPicture('C:\My Documents\My Pictures\couplevssolo(6).jpg');
//이미지 입력
Selection := Sheet.Pictures.Insert('C:\My Documents\My Pictures\302492_2.jpg');
//이미지 위치 조절
Selection.ShapeRange.IncrementLeft(243);
Selection.ShapeRange.IncrementTop(605);
}
//수식 입력
Format := '#,##0.00_ ;-#,##0.00;_-* "-"???_-;_-@_-';
excelApplication1.Range['F3','H8'].NumberFormatLocal := Format;
excelWorksheet1.Range['F3', 'H8'].Formula := '=RAND()*10';
excelWorksheet1.Range['F9', 'F9'].Formula := '=SUM(F3:F8)';
excelWorksheet1.Range['G9', 'G9'].Formula := '=SUM(G3:G8)';
excelWorksheet1.Range['H9', 'H9'].Formula := '=SUM(H3:H8)';
excelWorksheet1.Range['I9', 'I9'].Formula := '=SUM(F9:H9)';
excelWorksheet1.Range['F2', 'F2'].Value := '1학년';
excelWorksheet1.Range['G2', 'G2'].Value := '2학년';
excelWorksheet1.Range['H2', 'H2'].Value := '3학년';
excelWorksheet1.Range['E3', 'E3'].Value := '1년';
excelWorksheet1.Range['E4', 'E4'].Value := '2년';
excelWorksheet1.Range['E5', 'E5'].Value := '3년';
excelWorksheet1.Range['E6', 'E6'].Value := '4년';
excelWorksheet1.Range['E7', 'E7'].Value := '5년';
excelWorksheet1.Range['E8', 'E8'].Value := '6년';
excelWorksheet1.Range['E3', 'E8'].HorizontalAlignment := xlHAlignRight;
//차트용 오브젝트 생성
ChObj := (excelWorksheet1.ChartObjects(EmptyParam, lcid) as ChartObjects).Add(600, 10, 400, 250);
excelChart1.ConnectTo(ChObj.Chart as _Chart);
//데이터 범위(데이터뿐만 아니라 가로축 세로축에 찍힐 주석값까지 포함)
Rnge := excelWorksheet1.Range['E2','H8']; // the data range, including titles
//차트타입
ChType := TOleEnum(xl3DColumn);
excelChart1.ChartWizard(Rnge, ChType, EmptyParam, xlColumns, 1, 1, True,
excelWorksheet1.Range['A1', 'A1'].Text, // The chart title
'번호', '점수', EmptyParam, lcid);
Ax := excelChart1.Axes(xlValue, xlPrimary, lcid) as Axis;
Ax.AxisTitle.Font.FontStyle := '굴림체';
//자동 컬럼 폭 맞춤
excelWorksheet1.Range[ XL.Cells[1, 1], XL.Cells[ir, 9]].Select;
excelWorksheet1.Columns.AutoFit;
end;
end
출처 :: Posted by 내멋대로 코딩 나비 (http://skql.tistory.com/tag/Delphi%20Excel)
