Obx/Mod/Excel.odc

MODULE ObxExcel;
(**
	project	= "BlackBox"
	organization	= "www.oberon.ch"
	contributors	= "Oberon microsystems"
	version	= "System/Rsrc/About"
	copyright	= "System/Rsrc/About"
	license	= "Docu/BB-License"
	changes	= "
	- YYYYMMDD, nn, ...
	"
	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.