델파이 스트링그리드 엑셀 변환 예제

엑셀 화일을 직접 저장하는 방법은 모르겠지만

셀에 내용입력, 셀병합, 테두리 지정, 수식 지정, 도움말 입력, 이미지 삽입등의 방법은 이 안에 다 있습니다.

필요한 부분 요약해서 참조하도록 하세요.

저도 정리를 하다 만 소스라서 좀 지저분 하기는 합니다.

그리고 부분적으로 삭제를 했기때문에

표가 아래쪽은 좀 깨질 수도 있으니 그저 참조만 하시길...

이해해 주시기 바래요.



procedure TMainForm.Button_Result_ExcelViewClick(Sender: TObject);

var

     LCID, I        : Integer;

     Sheet   : Variant;

     Format         : OleVariant;

     GraphTop, GraphLeft : Integer;

     oRng : OleVariant;

     DirName : String;

     ImageFile : String;

begin

     DirName := 'C:\...\...\' + FileNameEdit.Text;

     LCID := 0;

     If DirectoryExists(DirName) = False Then

     Begin

          CreateDir(DirName);

          ChDir(DirName);

          if IOResult <> 0 then  MessageDlg('Cannot find directory', mtWarning, [mbOk], 0);

     End;



     ExcelApplication1.Connect; //엑셀을 가동한다(InVisible 상태)

     ExcelWorkbook1.connectto(ExcelApplication1.workbooks.add(TOleEnum(xlWBATWorksheet), LCID));

     ExcelWorksheet1.connectto(ExcelWorkbook1.worksheets.item['Sheet1'] as _worksheet );

 

     //워크시트 이름 변경

     ExcelWorksheet1.Name := AnalEdit.Text;// '엑셀 쉬트 이름 '

 

     ExcelApplication1.DisplayAlerts[LCID] := False;

     ExcelApplication1.Visible[LCID] := true;

 

     Sheet := ExcelApplication1.WorkBooks[ExcelApplication1.Workbooks.Count].WorkSheets[ExcelWorkbook1.Worksheets.Count];

 

     ExcelWorksheet1.Range[Sheet.Cells[1,1],Sheet.Cells[1,1]].VerticalAlignment := xlHAlignCenter;

     ExcelWorksheet1.Range[Sheet.Cells[1,1],Sheet.Cells[1,1]].HorizontalAlignment := xlHAlignCenter;

     ExcelWorksheet1.Range[Sheet.Cells[1,1],Sheet.Cells[1,1]].Value := 'Drop Watcher Test';

     ExcelWorksheet1.Range[Sheet.Cells[1,1],Sheet.Cells[2,18]].MergeCells := true;

     ExcelWorksheet1.Range[Sheet.Cells[1,1],Sheet.Cells[2,18]].borders.LineStyle := 1;

     ExcelApplication1.Range['A1','A1'].borders.Color := clNavy;

     ExcelApplication1.Range['A1','A1'].Interior.Color := clYellow;

     ExcelApplication1.Range['A1','A1'].borders.lineStyle := 1;

     ExcelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[2,18]].font.bold := true;

     ExcelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[2,18]].font.Size := 20;

     ExcelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[2,18]].font.Name := '굴림체';

     ExcelApplication1.Range[Sheet.Cells[1,1],Sheet.Cells[2,18]].VerticalAlignment := xlHAlignCenter;

     Sheet.Cells[1,1] := 'Drop Watcher Test ';



     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 81, 18 ]].font.Size := 9;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 81, 18 ]].font.Name := '굴림체';

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 81, 18 ]].HorizontalAlignment := xlHAlignCenter;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 81, 18 ]].VerticalAlignment := xlHAlignCenter;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 81, 18 ]].font.bold := True;



     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 261, 18 ]].font.Size := 9;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 261, 18 ]].font.Name := '굴림체';

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 261, 18 ]].HorizontalAlignment := xlHAlignCenter;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 261, 18 ]].VerticalAlignment := xlHAlignCenter;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 261, 6 ]].borders.lineStyle := 1;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 261, 18 ]].font.bold := True;



     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 5, 6 ]].font.Color:= clWhite;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 5, 6 ]].borders.Color := clNavy;

     ExcelApplication1.Range[Sheet.Cells[ 4, 1 ],Sheet.Cells[ 5, 6 ]].Interior.Color := clBlue;



     ExcelApplication1.Range[Sheet.Cells[ 6, 1 ],Sheet.Cells[ 261, 1 ]].borders.lineStyle := 1;

     ExcelApplication1.Range[Sheet.Cells[ 6, 1 ],Sheet.Cells[ 261, 1 ]].borders.Color := clBlack;

     ExcelApplication1.Range[Sheet.Cells[ 6, 1 ],Sheet.Cells[ 261, 1 ]].Interior.Color := clYellow;



     ExcelWorksheet1.Range[Sheet.Cells[4,1],Sheet.Cells[4,6]].MergeCells := true;



     GraphTop := 5;



     Sheet.Cells[ GraphTop-1, 1 ].Value := '항  목';

     Sheet.Cells[   GraphTop, 1 ].Value := 'No';

     Sheet.Cells[   GraphTop, 2 ].Value := '부  피(pl)';

     Sheet.Cells[   GraphTop, 3 ].Value := '좌  우(um)';  //Sheet.Cells[   GraphTop, 3 ].Memo := '';

     Sheet.Cells[   GraphTop, 3 ].AddComment( '수평 좌우 편차거리');

     Sheet.Cells[   GraphTop, 4 ].Value := '상  하(um)';

     Sheet.Cells[   GraphTop, 4 ].AddComment( '수직 상하 편차거리');

     Sheet.Cells[   GraphTop, 5 ].Value := '속 도(m/s)';

     Sheet.Cells[   GraphTop, 6 ].Value := '각  도(˚)';



     Format := '0';       ExcelApplication1.Range[Sheet.Cells[ 6, 1 ],Sheet.Cells[ 261, 1 ]].NumberFormatLocal   := Format;

     Format := '#,##0.0'; ExcelApplication1.Range[Sheet.Cells[ 6, 2 ],Sheet.Cells[ 261, 6 ]].NumberFormatLocal   := Format;



     For I := 1 to xxx do

     Begin

          Sheet.Cells[ GraphTop + I, 1 ].Value := Formatfloat('000', I );      // Nozzle No

          Sheet.Cells[ GraphTop + I, 2 ].Value := Formatfloat('0.0', m_DropTailVolume       [I] ); // 부피

          Sheet.Cells[ GraphTop + I, 3 ].Value := Formatfloat('0.0', m_DropTailStraightness [I] );  // 좌우편차

          Sheet.Cells[ GraphTop + I, 4 ].Value := Formatfloat('0.0', m_DropTailLength       [I] ); // 드랍간 거리

          Sheet.Cells[ GraphTop + I, 5 ].Value := Formatfloat('0.0', m_DropTailSpeed        [I] ); // 드랍 속도

          Sheet.Cells[ GraphTop + I, 6 ].Value := Formatfloat('0.0', m_DropAngle            [I] ); // 휘어짐 각도

     End;



     GraphTop := 4; GraphLeft := 8;



     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+1,GraphLeft+10]].VerticalAlignment := xlHAlignCenter;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+1,GraphLeft+10]].HorizontalAlignment := xlHAlignCenter;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+1,GraphLeft+10]].Value := 'Test 환경';

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+1,GraphLeft+10]].MergeCells := true;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+1,GraphLeft+10]].borders.LineStyle := 1;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+1,GraphLeft+10]].borders.Color  := clBlack;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+1,GraphLeft+10]].Interior.Color := clYellow;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+6,GraphLeft+10]].borders.lineStyle := 1;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop+2,GraphLeft],Sheet.Cells[GraphTop+6,GraphLeft+10]].Interior.Color := $00DEFEE3;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+6,GraphLeft+10]].font.bold := true;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop  ,GraphLeft+10]].font.Size := 12;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop+2,GraphLeft],Sheet.Cells[GraphTop+6,GraphLeft+10]].font.Size := 9;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop  ,GraphLeft],Sheet.Cells[GraphTop+6,GraphLeft+10]].font.Name := '굴림체';

           

     Format := '#,##0.0';

     ExcelApplication1.Range[Sheet.Cells[GraphTop+11,GraphLeft+1],Sheet.Cells[GraphTop+15,GraphLeft+9]].NumberFormatLocal   := Format;

     Format := '0';

     ExcelApplication1.Range[Sheet.Cells[GraphTop+11,GraphLeft+6],Sheet.Cells[GraphTop+11,GraphLeft+6]].NumberFormatLocal   := Format;

     Format := '#,##0.0%';

     ExcelApplication1.Range[Sheet.Cells[GraphTop+11,GraphLeft+5],Sheet.Cells[GraphTop+15,GraphLeft+5]].NumberFormatLocal   := Format;

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



     GraphTop := GraphTop + 17;



     ExcelWorksheet1.Range[Sheet.Cells[GraphTop,GraphLeft],Sheet.Cells[GraphTop+102,GraphLeft+10]].VerticalAlignment := xlHAlignCenter;

     ExcelWorksheet1.Range[Sheet.Cells[GraphTop,GraphLeft],Sheet.Cells[GraphTop+102,GraphLeft+10]].HorizontalAlignment := xlHAlignCenter;



     for I :=  0 to 4 do

     Begin

  case I of

            0: ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop,GraphLeft],Sheet.Cells[(I*20)+GraphTop,GraphLeft+10]].Value := '부  피(pl)';

            1: Begin

              ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop,GraphLeft],Sheet.Cells[(I*20)+GraphTop,GraphLeft+10]].Value := '좌  우(um)';

        Sheet.Cells[   (I*20)+GraphTop,GraphLeft ].AddComment( '수평 좌우 편차거리');

             End;

            2: Begin

              ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop,GraphLeft],Sheet.Cells[(I*20)+GraphTop,GraphLeft+10]].Value := '상  하(um)';

        Sheet.Cells[   (I*20)+GraphTop,GraphLeft ].AddComment( '수직 상하 편차거리');

             End;

            3: ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop,GraphLeft],Sheet.Cells[(I*20)+GraphTop,GraphLeft+10]].Value := '속  도(m/s)';

            4: ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop,GraphLeft],Sheet.Cells[(I*20)+GraphTop,GraphLeft+10]].Value := '각  도(˚)';

          end;



      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop  ,GraphLeft],Sheet.Cells[(I*20)+GraphTop   ,GraphLeft+10]].MergeCells := true;

      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop+1,GraphLeft],Sheet.Cells[(I*20)+GraphTop+18,GraphLeft+10]].MergeCells := true;

      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop  ,GraphLeft],Sheet.Cells[(I*20)+GraphTop+18,GraphLeft+10]].borders.LineStyle := 1;

      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop  ,GraphLeft],Sheet.Cells[(I*20)+GraphTop   ,GraphLeft+10]].borders.Color  := clBlack;

      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop  ,GraphLeft],Sheet.Cells[(I*20)+GraphTop   ,GraphLeft+10]].Interior.Color := clYellow;

      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop  ,GraphLeft],Sheet.Cells[(I*20)+GraphTop+18,GraphLeft+10]].font.bold := true;

      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop  ,GraphLeft],Sheet.Cells[(I*20)+GraphTop+18,GraphLeft+10]].font.Size := 12;

      ExcelWorksheet1.Range[Sheet.Cells[(I*20)+GraphTop  ,GraphLeft],Sheet.Cells[(I*20)+GraphTop+18,GraphLeft+10]].font.Name := '굴림체';



   oRng := Sheet.Cells[(I*20)+GraphTop+1,GraphLeft].Select;

          case I of

            0: ImageFile := VolumeGraph;

            1: ImageFile := StraightGraph;

            2: ImageFile := LengthGraph;

            3: ImageFile := SpeedGraph;

            4: ImageFile := AngleGraph;

          end;

      if FileExists(ImageFile) then

      begin

           oRng := Sheet.Pictures.Insert(ImageFile).ShapeRange;

               //oRng := Sheet.

        oRng.ScaleWidth (1, False, 1);

        oRng.ScaleHeight (1, false, 1);

               //////////// Excel 2007에서는 아래처럼 코딩 필요 ///////////////////////////////////////////////////////////////////////////////////////

               //oSheet.Shapes.AddPicture(gsAppDir + `\Logo\` + sCode + `.jpg`, msOTrue, msoTrue, Left:=0, Top:=(iPage*775)+0, Width:=148, Height:=55);

      end;

     End;

     Sheet.Cells[1,1].Select;



     //숫자형 포맷

   //Format := '_-* #,##0.0_-;-* #,##0.0_-;_-* "-"???_-;_-@_-';

   //ExcelApplication1.Range['B1','B1'].NumberFormatLocal := Format;



   //ExcelWorksheet1.Range['F3', 'H8'].Formula := '=RAND()*10';

   //ExcelWorksheet1.Range['F9', 'F9'].Formula := '=SUM(F3:F8)';

   //패턴변경

   //=============================================================================================================================

   //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);



   //차트용 오브젝트 생성

   //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.Columns.AutoFit;

end;