MODULE ObxExcel;
(**
project = "BlackBox"
organization = "www.oberon.ch"
contributors = "Oberon microsystems"
version = "System/Rsrc/About"
copyright = "System/Rsrc/About"
license = "Docu/BB-License"
changes = ""
issues = ""
**)
IMPORT
CtlT, CtlExcel := CtlExcel9, Dates, Views, Containers,
OleViews, TextModels, TextViews, TextMappers, StdLog;
PROCEDURE ShowExcel*;
VAR ws: CtlExcel.Worksheet; wb: CtlExcel.Workbook;
range: CtlExcel.Range;
a: ARRAY 3 OF INTEGER;
d: Dates.Date; t: Dates.Time;
date: CtlT.OleDate; (* = REAL *)
cy: CtlT.OleCy;(* = LONGINT *)
v: Views.View;
text: TextModels.Model; out: TextMappers.Formatter;
BEGIN
v := OleViews.NewObjectView("Excel.Sheet");
IF v # NIL THEN
(* in excel 8.0 Excel.Sheet is a workbook object ! *)
wb := CtlExcel.This_Workbook(OleViews.AutoObject(v));
ws := CtlExcel.This_Worksheet(wb.Worksheets().Item(CtlT.Int(1)));
range := ws.Range(CtlT.Str("A1"), NIL);
range.PUTValue(CtlT.Str("Hello World"));
range.PUTColumnWidth(CtlT.Int(12)); (* unit = width of "0" in standard font *)
ws.Range(CtlT.Str("B1"), NIL).PUTValue(CtlT.Int(13));
d.year := 1996; d.month := 8; d.day := 5;
t.hour := 10; t.minute := 5; t.second := 30;
date := CtlT.OleDateFromDateAndTime(d, t); (* (day - 1.1.1900 + 2) + hour / 24 + minute / (24 * 60) + ... *)
range := ws.Range(CtlT.Str("C1"), NIL);
range.PUTValue(CtlT.Date(date));
range.PUTColumnWidth(CtlT.Int(15));
cy := 133500; (* 13.35 *)
ws.Range(CtlT.Str("D1"), NIL).PUTValue(CtlT.Cy(cy));
a[0] := 1; a[1] := 2; a[2] := 3;
ws.Range(CtlT.Str("A2"), CtlT.Str("C2")).PUTValue(CtlT.IntArr(a));
ws.Range(CtlT.Str("D2"), NIL).PUTValue(CtlT.Str("=A2+B2+C2"));
text := TextModels.dir.New();
out.ConnectTo(text);
out.WriteString("Excel Automation Example");
out.WriteLn; out.WriteLn; out.WriteLn;
out.WriteTab; out.WriteTab; out.WriteView(v);
out.WriteLn; out.WriteLn; out.WriteLn;
Views.OpenView(TextViews.dir.New(text));
ELSE
StdLog.String("cannot open excel object"); StdLog.Ln
END
END ShowExcel;
PROCEDURE ReadExcel*;
VAR v: Views.View; ws: CtlExcel.Worksheet;
obj: CtlT.Object;
p: POINTER TO ARRAY OF ARRAY OF CtlT.Any;
val, cell: CtlT.Any; r, c: INTEGER;
date: Dates.Date; time: Dates.Time;
str: ARRAY 256 OF CHAR;
BEGIN
v := Containers.FocusSingleton();
IF v # NIL THEN
IF OleViews.IsAutoView(v) THEN
obj:= OleViews.AutoObject(v);
IF CtlExcel.Is_Workbook(obj) THEN
ws := CtlExcel.This_Worksheet(CtlExcel.This_Workbook(obj).Worksheets().Item(CtlT.Int(1)));
val := ws.Range(CtlT.Str("A1"), CtlT.Str("E5")).Value();
ASSERT(val # NIL, 10);
ASSERT(val.dim = 2, 11);
ASSERT(val.typeId = CtlT.any, 12);
p := val(CtlT.AnyArray2).p; r := 0;
StdLog.Int(LEN(p^)); StdLog.Int(LEN(p^, 1)); StdLog.Ln;
WHILE r < LEN(p^) DO
c := 0;
WHILE c < LEN(p^, 1) DO
cell := p[r, c];
IF cell # NIL THEN
ASSERT(cell.dim = 0, 13);
StdLog.String("Cell "); StdLog.Char(CHR(c + ORD("A"))); StdLog.Int(r); StdLog.String(": ");
CASE cell.typeId OF
| CtlT.byte: StdLog.Int(cell(CtlT.ByteT).val)
| CtlT.shortint: StdLog.Int(cell(CtlT.ShortInt).val)
| CtlT.integer: StdLog.Int(cell(CtlT.Integer).val)
| CtlT.shortreal: StdLog.Real(cell(CtlT.ShortReal).val)
| CtlT.real: StdLog.Real(cell(CtlT.RealT).val)
| CtlT.result: StdLog.IntForm(cell(CtlT.Result).val, 16, 8, "0", FALSE)
| CtlT.date: CtlT.OleDateToDateAndTime(cell(CtlT.DateT).val, date, time);
Dates.DateToString(date, Dates.short, str); StdLog.String(str);
StdLog.String("");
Dates.TimeToString(time, str); StdLog.String(str)
| CtlT.currency: StdLog.Int(SHORT(cell(CtlT.Currency).val DIV 10000));
StdLog.Char(".");
StdLog.IntForm(SHORT(cell(CtlT.Currency).val MOD 10000), 10, 4, "0", FALSE);
| CtlT.boolean: StdLog.Bool(cell(CtlT.Boolean).val)
| CtlT.string: StdLog.String(cell(CtlT.String).val^)
| CtlT.object: StdLog.String("<object>")
| CtlT.interface: StdLog.String("<interface>")
END;
StdLog.Ln
END;
INC(c)
END;
INC(r)
END
ELSE
StdLog.String("not a worksheet"); StdLog.Ln
END
ELSE
StdLog.String("not an OLE object"); StdLog.Ln
END
ELSE
StdLog.String("no singleton"); StdLog.Ln
END
END ReadExcel;
PROCEDURE OpenChart*;
VAR ws: CtlExcel.Worksheet; wb: CtlExcel.Workbook; ch: CtlExcel.Chart;
obj: CtlT.Object; v: Views.View;
a: CtlExcel.Application;
BEGIN
v := Containers.FocusSingleton();
IF v # NIL THEN
IF OleViews.IsAutoView(v) THEN
obj:= OleViews.AutoObject(v);
IF CtlExcel.Is_Workbook(obj) THEN
ws := CtlExcel.This_Worksheet(CtlExcel.This_Workbook(obj).Worksheets().Item(CtlT.Int(1)));
a := ws.Application();
a.PUTVisible(TRUE);
wb := a.Workbooks().Add(NIL);
ch := CtlExcel.This_Chart(wb.Sheets().Add(NIL, NIL, NIL, CtlT.Int(CtlExcel.xlChart)));
ch.ChartWizard(ws.Range(CtlT.Str("A2"), CtlT.Str("E2")), CtlT.Int(CtlExcel.xlBar), NIL, NIL, NIL, NIL, NIL,
CtlT.Str("Demo Chart"), NIL, NIL, NIL);
ELSE
StdLog.String("not a worksheet"); StdLog.Ln
END
ELSE
StdLog.String("not an OLE object"); StdLog.Ln
END
ELSE
StdLog.String("no singleton"); StdLog.Ln
END
END OpenChart;
END ObxExcel.