...

Arsip: Membaca Excel

 
user image
more 17 years ago

fatahjunaidi

Teman-teman saya mau tanya gimana caranya membaca data excel melalui delphi. Terima kasih ya atas bantuannya ....
user image
more 17 years ago

cyber_hecker

Credit : http://community.borland.com Charlie Calvert Delphi dan Microsoft Office Otomatisasi Excel Ada dua cara yang digunakan dalam otomatisasi Excel dan Word. Pertama yaitu dengan menggunakan variants dan Idispatch, dan yang kedua dengan dispinterfaces dan standart COM interface. Perbedaan antara dua cara tersebut adalah : 1. dengan menggunakan variants biasanya lebih mudah, tetapi performance yang di hasilkan sangat rendah atau lambat. 2. menggunakan COM interfaces lebih sulit, tetapi dalam masalah kinerja dan kecepatan lebih baik dari yang lainnya. Untuk artikel ini saya menggunakan cara pertama. Habis gampang seh… wakakak :D Memulai Delphi dan excel Berikut ini akan akan saya berikan contoh sederhana untuk menjalankan Microsoft excel dengan menggunakan Delphi dan menutupnya. 
implementation
uses ComObj;
{$R .dfm}
procedure TForm1.Button1Click(Sender: TObject);
begin
  // membuka aplikasi microsoft excel
  XLApp := CreateOleObject('Excel.Application');
  XLApp.Visible := True;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
  // menutup aplikasi microsoft excel
  if not VarIsEmpty(XLApp) then
    XLApp.Quit;
end;[/code:1:d837201da2]
Hirarki Excel Automation Objects
Seperti pada pemrograman berorientasi objek (OOP), Objek excel juga memiliki hirarki yang dapat digambarkan sebagai berikut :
- Application
--- Workbooks
----- Sheets
----- Worksheets
----- Charts
hirarki diatas menerangkan cara mengakses objek-objek dalam excel. 
Berikut ini adalah sample sederhana cara mengakses masing-masing objek dalam excel.
uses ComObj;
{$R  .dfm}
procedure TForm1.Button1Click(Sender: TObject);
const
{ XlSheetType }
  xlChart = -4109;
  xlDialogSheet = -4116;
  xlExcel4IntlMacroSheet = 4;
  xlExcel4MacroSheet = 3;
  xlWorksheet = -4167;
{ XlWBATemplate }
  xlWBATChart = -4109;
  xlWBATExcel4IntlMacroSheet = 4;
  xlWBATExcel4MacroSheet = 3;
  xlWBATWorksheet = -4167;
var
  i, j: Integer;
  Sheets: Variant;
begin
  XLApp := CreateOleObject('Excel.Application');
  XLApp.Visible := True;
  XLApp.Workbooks.Add;
  XLApp.Workbooks.Add(xlWBatChart);
  XLApp.Workbooks.Add(xlWBatWorkSheet);
  XLApp.Workbooks[2].Sheets.Add(,,1,xlChart);
  XLApp.Workbooks[3].Sheets.Add(,,1,xlWorkSheet);
  for i := 1 to XLApp.Workbooks.Count do
  begin
    ListBox1.Items.Add('Workbook: ' + XLApp.Workbooks[i].Name);
    for j := 1 to XLApp.Workbooks[i].Sheets.Count do
      ListBox1.Items.Add('  Sheet: ' +  
        XLApp.Workbooks[i].Sheets[j].Name);
  end;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
  // menutup aplikasi microsoft excel
  if not VarIsEmpty(XLApp) then begin
    XLApp.DisplayAlerts := False;  // Discard unsaved files....
    XLApp.Quit;
  end;
end;[/code:1:d837201da2]
Menyimpan dan Mengakses data pada Excel.
Berikut ini merupakan contoh sederhana dalam menyimpan dan mengakses data Excel.
[code:1:d837201da2]implementation
uses ComObj, XLConst;
{$R .dfm}
procedure TForm1.InsertData;
var
  i: Integer;
  Sheet: Variant;
begin
  Sheet := XLApp.Workbooks[1].WorkSheets['Delphi Data'];
  for i := 1 to 10 do
    Sheet.Cells[i, 1] := i;
  Sheet.Cells[i, 1] := '=Sum(A1:A10)';
end;
procedure TForm1.HandleRange;
var
  Range: Variant;
begin
  Range :=
   XLApp.Workbooks[1].WorkSheets['Delphi Data'].Range['C1:F25'];
  Range.Formula := '=RAND()';
  Range.Columns.Interior.ColorIndex := 3;
  Range.Borders.LineStyle := xlContinuous;
end;
procedure TForm1.ChangeColumns;
var
  ColumnRange: Variant;
begin
  ColumnRange := XLApp.Workbooks[1].WorkSheets['Delphi Data'].Columns;
  ColumnRange.Columns[1].ColumnWidth := 5;
  ColumnRange.Columns[1].Font.Bold := True;
  ColumnRange.Columns[1].Font.Color := clBlue;
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
  XLApp:= CreateOleObject('Excel.Application');
  XLApp.Visible := True;
  XLApp.Workbooks.Add(xlWBatWorkSheet);
  XLApp.Workbooks[1].WorkSheets[1].Name := 'Delphi Data';
  InsertData;
  HandleRange;
  ChangeColumns;
end;
procedure TForm1.Button2Click(Sender: TObject);
var
  Sheet: Variant;
  Num: Integer;
//  Nama : String;
begin
  Sheet := XLApp.Workbooks[1].WorkSheets['Delphi Data'];
  Num := Sheet.Range['C1:F25'].Columns.Interior.PatternColor;
  ShowMessage(Format('Value: %x', [Num]));
  // jika ingin mengakses nilai string
  // tapi belum ku aktifkan.. silakan bereksperimen
{ Nama := Sheet.Range['A1:A1'].Value;
  ShowMessage(Num); }
end;
procedure TForm1.FormDestroy(Sender: TObject);
begin
  if not VarIsEmpty(XLApp) then begin
    XLApp.DisplayAlerts := False;  // Discard unsaved files....
    XLApp.Quit;
  end;
end;
sedangkan sorce code untuk XLConst adalah sebagai berikut :

unit XLConst;
interface
const
{ XlSheetType }
  xlChart = -4109;
  xlDialogSheet = -4116;
  xlExcel4IntlMacroSheet = 4;
  xlExcel4MacroSheet = 3;
  xlWorksheet = -4167;
{ XlWBATemplate }
  xlWBATChart = -4109;
  xlWBATExcel4IntlMacroSheet = 4;
  xlWBATExcel4MacroSheet = 3;
  xlWBATWorksheet = -4167;
{ XlPattern }
  xlPatternAutomatic = -4105;
  xlPatternChecker = 9;
  xlPatternCrissCross = 16;
  xlPatternDown = -4121;
  xlPatternGray16 = 17;
  xlPatternGray25 = -4124;
  xlPatternGray50 = -4125;
  xlPatternGray75 = -4126;
  xlPatternGray8 = 18;
  xlPatternGrid = 15;
  xlPatternHorizontal = -4128;
  xlPatternLightDown = 13;
  xlPatternLightHorizontal = 11;
  xlPatternLightUp = 14;
  xlPatternLightVertical = 12;
  xlPatternNone = -4142;
  xlPatternSemiGray75 = 10;
  xlPatternSolid = 1;
  xlPatternUp = -4162;
  xlPatternVertical = -4166;
  { XlBordersIndex }
  xlInsideHorizontal = 12;
  xlInsideVertical = 11;
  xlDiagonalDown = 5;
  xlDiagonalUp = 6;
  xlEdgeBottom = 9;
  xlEdgeLeft = 7;
  xlEdgeRight = 10;
  xlEdgeTop = 8;
{ XlLineStyle }
  xlContinuous = 1;
  xlDash = -4115;
  xlDashDot = 4;
  xlDashDotDot = 5;
  xlDot = -4118;
  xlDouble = -4119;
  xlSlantDashDot = 13;
  xlLineStyleNone = -4142;
  { XlChartType }
  xlColumnClustered = 51;
  xlColumnStacked = 52;
  xlColumnStacked100 = 53;
  xl3DColumnClustered = 54;
  xl3DColumnStacked = 55;
  xl3DColumnStacked100 = 56;
  xlBarClustered = 57;
  xlBarStacked = 58;
  xlBarStacked100 = 59;
  xl3DBarClustered = 60;
  xl3DBarStacked = 61;
  xl3DBarStacked100 = 62;
  xlLineStacked = 63;
  xlLineStacked100 = 64;
  xlLineMarkers = 65;
  xlLineMarkersStacked = 66;
  xlLineMarkersStacked100 = 67;
  xlPieOfPie = 68;
  xlPieExploded = 69;
  xl3DPieExploded = 70;
  xlBarOfPie = 71;
  xlXYScatterSmooth = 72;
  xlXYScatterSmoothNoMarkers = 73;
  xlXYScatterLines = 74;
  xlXYScatterLinesNoMarkers = 75;
  xlAreaStacked = 76;
  xlAreaStacked100 = 77;
  xl3DAreaStacked = 78;
  xl3DAreaStacked100 = 79;
  xlDoughnutExploded = 80;
  xlRadarMarkers = 81;
  xlRadarFilled = 82;
  xlSurface = 83;
  xlSurfaceWireframe = 84;
  xlSurfaceTopView = 85;
  xlSurfaceTopViewWireframe = 86;
  xlBubble = 15;
  xlBubble3DEffect = 87;
  xlStockHLC = 88;
  xlStockOHLC = 89;
  xlStockVHLC = 90;
  xlStockVOHLC = 91;
  xlCylinderColClustered = 92;
  xlCylinderColStacked = 93;
  xlCylinderColStacked100 = 94;
  xlCylinderBarClustered = 95;
  xlCylinderBarStacked = 96;
  xlCylinderBarStacked100 = 97;
  xlCylinderCol = 98;
  xlConeColClustered = 99;
  xlConeColStacked = 100;
  xlConeColStacked100 = 101;
  xlConeBarClustered = 102;
  xlConeBarStacked = 103;
  xlConeBarStacked100 = 104;
  xlConeCol = 105;
  xlPyramidColClustered = 106;
  xlPyramidColStacked = 107;
  xlPyramidColStacked100 = 108;
  xlPyramidBarClustered = 109;
  xlPyramidBarStacked = 110;
  xlPyramidBarStacked100 = 111;
  xlPyramidCol = 112;
  xl3DColumn = -4100;
  xlLine = 4;
  xl3DLine = -4101;
  xl3DPie = -4102;
  xlPie = 5;
  xlXYScatter = -4169;
  xl3DArea = -4098;
  xlArea = 1;
  xlDoughnut = -4120;
  xlRadar = -4151;

{ Various Constants }
  xlAll = -4104;
  xlAutomatic = -4105;
  xlBoth = 1;
  xlCenter = -4108;
  xlChecker = 9;
  xlCircle = 8;
  xlCorner = 2;
  xlCrissCross = 16;
  xlCross = 4;
  xlDiamond = 2;
  xlDistributed = -4117;
  xlDoubleAccounting = 5;
  xlFixedValue = 1;
  xlFormats = -4122;
  xlGray16 = 17;
  xlGray8 = 18;
  xlGrid = 15;
  xlHigh = -4127;
  xlInside = 2;
  xlJustify = -4130;
  xlLightDown = 13;
  xlLightHorizontal = 11;
  xlLightUp = 14;
  xlLightVertical = 12;
  xlLow = -4134;
  xlManual = -4135;
  xlMinusValues = 3;
  xlModule = -4141;
  xlNextToAxis = 4;
  xlNone = -4142;
  xlNotes = -4144;
  xlOff = -4146;
  xlOn = 1;
  xlPercent = 2;
  xlPlus = 9;
  xlPlusValues = 2;
  xlSemiGray75 = 10;
  xlShowLabel = 4;
  xlShowLabelAndPercent = 5;
  xlShowPercent = 3;
  xlShowValue = 2;
  xlSimple = -4154;
  xlSingle = 2;
  xlSingleAccounting = 4;
  xlSolid = 1;
  xlSquare = 1;
  xlStar = 5;
  xlStError = 4;
  xlToolbarButton = 2;
  xlTriangle = 3;
  xlGray25 = -4124;
  xlGray50 = -4125;
  xlGray75 = -4126;
  xlBottom = -4107;
  xlLeft = -4131;
  xlRight = -4152;
  xlTop = -4160;
  xl3DBar = -4099;
  xl3DSurface = -4103;
  xlBar = 2;
  xlColumn = 3;
  xlCombination = -4111;
  xlCustom = -4114;
  xlDefaultAutoFormat = -1;
  xlMaximum = 2;
  xlMinimum = 4;
  xlOpaque = 3;
  xlTransparent = 2;
  xlBidi = -5000;
  xlLatin = -5001;
  xlContext = -5002;
  xlLTR = -5003;
  xlRTL = -5004;
  xlVisualCursor = 2;
  xlLogicalCursor = 1;
  xlSystem = 1;
  xlPartial = 3;
  xlHindiNumerals = 3;
  xlBidiCalendar = 3;
  xlGregorian = 2;
  xlComplete = 4;
  xlScale = 3;
  xlClosed = 3;
  xlColor1 = 7;
  xlColor2 = 8;
  xlColor3 = 9;
  xlConstants = 2;
  xlContents = 2;
  xlBelow = 1;
  xlCascade = 7;
  xlCenterAcrossSelection = 7;
  xlChart4 = 2;
  xlChartSeries = 17;
  xlChartShort = 6;
  xlChartTitles = 18;
  xlClassic1 = 1;
  xlClassic2 = 2;
  xlClassic3 = 3;
  xl3DEffects1 = 13;
  xl3DEffects2 = 14;
  xlAbove = 0;
  xlAccounting1 = 4;
  xlAccounting2 = 5;
  xlAccounting3 = 6;
  xlAccounting4 = 17;
  xlAdd = 2;
  xlDebugCodePane = 13;
  xlDesktop = 9;
  xlDirect = 1;
  xlDivide = 5;
  xlDoubleClosed = 5;
  xlDoubleOpen = 4;
  xlDoubleQuote = 1;
  xlEntireChart = 20;
  xlExcelMenus = 1;
  xlExtended = 3;
  xlFill = 5;
  xlFirst = 0;
  xlFloating = 5;
  xlFormula = 5;
  xlGeneral = 1;
  xlGridline = 22;
  xlIcons = 1;
  xlImmediatePane = 12;
  xlInteger = 2;
  xlLast = 1;
  xlLastCell = 11;
  xlList1 = 10;
  xlList2 = 11;
  xlList3 = 12;
  xlLocalFormat1 = 15;
  xlLocalFormat2 = 16;
  xlLong = 3;
  xlLotusHelp = 2;
  xlMacrosheetCell = 7;
  xlMixed = 2;
  xlMultiply = 4;
  xlNarrow = 1;
  xlNoDocuments = 3;
  xlOpen = 2;
  xlOutside = 3;
  xlReference = 4;
  xlSemiautomatic = 2;
  xlShort = 1;
  xlSingleQuote = 2;
  xlStrict = 2;
  xlSubtract = 3;
  xlTextBox = 16;
  xlTiled = 1;
  xlTitleBar = 8;
  xlToolbar = 1;
  xlVisible = 12;
  xlWatchPane = 11;
  xlWide = 3;
  xlWorkbookTab = 6;
  xlWorksheet4 = 1;
  xlWorksheetCell = 3;
  xlWorksheetShort = 5;
  xlAllExceptBorders = 6;
  xlLeftToRight = 2;
  xlTopToBottom = 1;
  xlVeryHidden = 2;
  xlDrawingObject = 14;
implementation
end.

Membuat Grafik
Untuk membuat grafik adalah sangat sederhana. Semuanya sudah otomatis dari Excel. Berikut ini contoh program sederhananya :
[code:1:d837201da2]    procedure Button1Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
    XLApp: Variant;
    WordApp: Variant;
  public
    { Public declarations }
    procedure HandleData;
    procedure ChartData;
    procedure CopyData;
    procedure CopyChartToWord;
    procedure CopyCellsToWord;
  end;
var
  Form1: TForm1;
implementation
uses
  ComObj, XLConst, WordConst, ActiveX;
{$R  .DFM}
procedure TForm1.Button1Click(Sender: TObject);
begin
  XLApp := CreateOleObject('Excel.Application');
  XLApp.Visible := True;
  XLApp.Workbooks.Add[XLWBatWorksheet];
  XLApp.Workbooks[1].Worksheets[1].Name := 'Delphi Data';
  HandleData;
  ChartData;
  CopyData;
end;
procedure TForm1.HandleData;
var
  Sheet: Variant;
  i: Integer;
begin
  Sheet := XLApp.Workbooks[1].Worksheets['Delphi Data'];
  for i := 1 to 10 do
    Sheet.Cells[i, 1] := i;
end;
procedure TForm1.ChartData;
var
  ARange: Variant;
  Sheets: Variant;
begin
  XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
  Sheets := XLApp.Sheets;
  ARange := Sheets.Item['Delphi Data'].Range['A1:A10'];
  Sheets.Item['Chart1'].SeriesCollection.Item[1].Values := ARange;
  Sheets.Item['Chart1'].ChartType := xl3DPie;
  Sheets.Item['Chart1'].SeriesCollection.Item[1].HasDataLabels := True;
  XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
  Sheets.Item['Chart2'].SeriesCollection.Item[1].Values := ARange;
  Sheets.Item['Chart2'].SeriesCollection.Add(ARange);
  Sheets.Item['Chart2'].SeriesCollection.NewSeries;
  Sheets.Item['Chart2'].SeriesCollection.Item[3].Values :=
    VarArrayOf([1,2,3,4,5, 6,7,8,9,10]);
  Sheets.Item['Chart2'].ChartType := xl3DColumn;
end;
procedure TForm1.CopyData;
var
  Sheets: Variant;
begin
  SetFocus;
  
  Sheets := XLApp.Sheets;
  Sheets.Item['Delphi Data'].Activate;
  Sheets.Item['Delphi Data'].Range['A1:A10'].Select;
  Sheets.Item['Delphi Data'].UsedRange.Copy;
  CopyCellsToWord;
  Sheets.Item['Chart1'].Select;
  XLApp.Selection.Copy;
  CopyChartToWord;
end;
procedure TForm1.CopyChartToWord;
var
  Range: Variant;
  i, NumPars: Integer;
begin
  NumPars := WordApp.Documents.Item(1).Paragraphs.Count;
  Range := WordApp.Documents.Item(1).Range(
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.Start,
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.End);
  Range.Text := 'This is graph: ';
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;
  Range := WordApp.Documents.Item(1).Range(
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 1).Range.Start,
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 1).Range.End);
  Range.PasteSpecial(,,,,wdPasteOleObject);
end;
procedure TForm1.CopyCellsToWord;
var
  Range: Variant;
  i: Integer;
begin
  WordApp := CreateOleObject('Word.Application');
  WordApp.Visible := True;
  WordApp.Documents.Add;
  Range := WordApp.Documents.Item(1).Range;
  Range.Text := 'This is a column from a spreadsheet: ';
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;
  Range := 
    WordApp.Documents.Item(1).Range(WordApp.Documents.Item(1).
      Paragraphs.Item(3).Range.Start);
  Range.Paste;
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;
end;
procedure TForm1.FormDestroy(Sender: TObject);
begin
  if not VarIsEmpty(XLApp) then begin
    XLApp.DisplayAlerts := False;  // Discard unsaved files....
    XLApp.Quit;
  end;
  if not VarIsEmpty(WordApp)then begin
    WordApp.Documents.Item(1).Close(wdDoNotSaveChanges);
    WordApp.Quit;
  end;
end;
sedangkan code untuk WordConst adalah sebagai berikut : 
unit WordConst;
interface
const
{ WdPasteDataType }
  wdPasteOLEObject = 0;
  wdPasteRTF = 1;
  wdPasteText = 2;
  wdPasteMetafilePicture = 3;
  wdPasteBitmap = 4;
  wdPasteDeviceIndependentBitmap = 5;
  wdPasteHyperlink = 7;
  wdPasteShape = 8;
  wdPasteEnhancedMetafile = 9;
{ WdAlertLevel }
  wdAlertsNone = 0;
  wdAlertsMessageBox = -2;
  wdAlertsAll = -1;
{ WdSaveOptions }
  wdDoNotSaveChanges = 0;
  wdSaveChanges = -1;
  wdPromptToSaveChanges = -2;
{ WdGoToDirection }
  wdGoToFirst = 1;
  wdGoToLast = -1;
  wdGoToNext = 2;
  wdGoToRelative = 2;
  wdGoToPrevious = 3;
  wdGoToAbsolute = 1;
{ WdGoToItem }
  wdGoToBookmark = -1;
  wdGoToSection = 0;
  wdGoToPage = 1;
  wdGoToTable = 2;
  wdGoToLine = 3;
  wdGoToFootnote = 4;
  wdGoToEndnote = 5;
  wdGoToComment = 6;
  wdGoToField = 7;
  wdGoToGraphic = 8;
  wdGoToObject = 9;
  wdGoToEquation = 10;
  wdGoToHeading = 11;
  wdGoToPercent = 12;
  wdGoToSpellingError = 13;
  wdGoToGrammaticalError = 14;
  wdGoToProofreadingError = 15;
{ WdSectionStart }
  wdSectionContinuous = 0;
  wdSectionNewColumn = 1;
  wdSectionNewPage = 2;
  wdSectionEvenPage = 3;
  wdSectionOddPage = 4;
  
implementation
end.
Sekian… terimakasih :D nb. ini sebenarnya artikel tahun '97, karena males ngetik ulang, ku paste aja sekalian. wakakak :D harap maklum :oops: semoga sedikit membantu
user image
more 17 years ago

fatahjunaidi

Terima kasih banyak sobat atas jawabannya. Semoga bermanfaat untuk kita semua.
user image
more 15 years ago

MrLee

bisa ngak ya cara akses option di excel, seperti mendisable GRID SHOW, Mematikan AUTO CALC, Saya Baru Dapat option Printernya saja seperti mengatur margin kiri dll, memilih ukuran kertas dll lah kasih tahu donk!!!!
more ...
  • Pages:
  • 1
|
Share to

Last Articles

Last Topic

Random Topic

Local Business Directory, Search Engine Submission & SEO Tools FreeWebSubmission.com SonicRun.com