CtlExcel9

CtlExcel9 contains the automation interface for Microsoft Excel 9.0. The objects contained in this module are explained in the corresponding help file (VBAXL9.CHM) which is located on the MS Office CD but is not installed by default.
For more information about automation controllers in BlackBox see the CtlDocu.



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.
		
