|
|
Library to read Excel files
For Stony Brook Modula-2
By Frank Schoonjans (frank.schoonjans@medcalc.net)
Reading an Excel file is much more complicated than writing one. Amongst others, we have to deal with different file versions.
Since later Excel versions use the OLE 2 Compound File format specification, and therefore OLE libraries are required to reading these files correctly, we have opted to limit the procedures described here to Excel version 4 or less.
We will handle the OLE files in a different project later.
The download ExcelIn.zip contains
the complete source of a library (both definition and implementation modules) to read Excel version 4 (or less) files.
This is the ExcelIn DEFINITION MODULE:
DEFINITION MODULE ExcelIn;
(* Routines to read data in Excel v. 4 format *)
(* Follow these steps to read an Excel file:
1. Assign Procedures to the Procedure variables
in order to handle the data read
2. call the procedure ReadExcelFile
*)
TYPE
StoreDefaultColumnWidthT = PROCEDURE(INTEGER);
(* width *)
StoreColumnWidthT = PROCEDURE(INTEGER, INTEGER);
(* column , width *)
StoreRealT = PROCEDURE(INTEGER, INTEGER, LONGREAL, INTEGER);
(* column, row , value , precision *)
StoreIntegerT = PROCEDURE(INTEGER, INTEGER, INTEGER);
StoreCardinalT = PROCEDURE(INTEGER, INTEGER, CARDINAL);
StoreBooleanT = PROCEDURE(INTEGER, INTEGER, BOOLEAN);
StoreStringT = PROCEDURE(INTEGER, INTEGER, ARRAY OF CHAR);
(* column, row , value *)
TYPE
DateFormats = (mmddyy,ddmmyy,yymmdd);
TimeFormats = (hms24,hms12,hm24,hm12);
TYPE
Errors = (EXCEL_ok,EXCEL_cannotopen,EXCEL_protected,EXCEL_notexcel);
VAR
StoreDefaultColumnWidth : StoreDefaultColumnWidthT;
StoreColumnWidth : StoreColumnWidthT;
StoreReal : StoreRealT;
StoreInteger : StoreIntegerT;
StoreCardinal : StoreCardinalT;
StoreBoolean : StoreBooleanT;
StoreString : StoreStringT;
TimeSeparator : CHAR; (* default = ":" *)
TimeFormat : TimeFormats; (* default = hms24 *)
YearDigits : INTEGER; (* default = 4 *)
DateFormat : DateFormats; (* default = ddmmyy *)
DateSeparator : CHAR; (* default = "/" *)
PROCEDURE ReadExcelFile(filename : ARRAY OF CHAR) : Errors;
END ExcelIn.
|
The use of the library is illustrated in the following program:
MODULE ExcelInTest;
FROM ExcelIn IMPORT Errors,ReadExcelFile,StoreDefaultColumnWidth,StoreColumnWidth,
StoreReal,StoreInteger,StoreCardinal,StoreBoolean,StoreString;
FROM STextIO IMPORT WriteString,WriteLn,ReadString,ReadChar,SkipLine;
FROM SWholeIO IMPORT WriteCard,WriteInt;
FROM SLongIO IMPORT WriteFloat,WriteFixed;
PROCEDURE MyStoreDefaultColumnWidth(width : INTEGER);
BEGIN
WriteString("Default column width = "); WriteInt(width,1); WriteLn;
END MyStoreDefaultColumnWidth;
PROCEDURE WriteColumnId(column : INTEGER);
VAR c1,c2 : INTEGER;
BEGIN
c1:=column MOD 26;
c2:=column DIV 26;
IF c2>0 THEN WriteString(CHR(c2+64)); END;
WriteString(CHR(c1+65));
END WriteColumnId;
PROCEDURE MyStoreColumnWidth(col,w : INTEGER);
BEGIN
WriteString("Column width: column "); WriteColumnId(col);
WriteString(", width = "); WriteInt(w,1); WriteLn;
END MyStoreColumnWidth;
PROCEDURE WriteCoords(column,row : INTEGER);
VAR c1,c2 : INTEGER;
BEGIN
WriteString("Cell ");
WriteColumnId(column);
WriteInt(row,1);
WriteString(": ");
END WriteCoords;
PROCEDURE MyStoreReal(column,row : INTEGER; val : LONGREAL; prec : INTEGER);
BEGIN
WriteCoords(column,row);
IF prec<0 THEN
WriteFixed(val,9,20);
ELSE
WriteFixed(val,prec,20);
END;
WriteLn;
END MyStoreReal;
PROCEDURE MyStoreInteger(column,row : INTEGER; val : INTEGER);
BEGIN
WriteCoords(column,row);
WriteInt(val,1);
WriteLn;
END MyStoreInteger;
PROCEDURE MyStoreCardinal(column,row : INTEGER; val : CARDINAL);
BEGIN
WriteCoords(column,row);
WriteCard(val,1);
WriteLn;
END MyStoreCardinal;
PROCEDURE MyStoreBoolean(column,row : INTEGER; bool : BOOLEAN);
BEGIN
WriteCoords(column,row);
IF bool THEN
WriteString("TRUE");
ELSE
WriteString("FALSE");
END;
WriteLn;
END MyStoreBoolean;
PROCEDURE MyStoreString(column,row : INTEGER; str : ARRAY OF CHAR);
BEGIN
WriteCoords(column,row);
WriteString(str); WriteLn;
END MyStoreString;
VAR ch : CHAR;
BEGIN
StoreDefaultColumnWidth := MyStoreDefaultColumnWidth;
StoreColumnWidth := MyStoreColumnWidth;
StoreReal := MyStoreReal;
StoreInteger := MyStoreInteger;
StoreCardinal := MyStoreCardinal;
StoreBoolean := MyStoreBoolean;
StoreString := MyStoreString;
CASE ReadExcelFile("test.xls") OF
| EXCEL_ok : WriteString("Ready.");
| EXCEL_cannotopen : WriteString("Cannot open file.");
| EXCEL_protected : WriteString("File is password protected.");
| EXCEL_notexcel : WriteString("Not Excel 4 file.");
END;
WriteLn;
WriteString("Press Enter to continue");
ReadChar(ch); SkipLine;
END ExcelInTest.
|
Excel file format documentation
http://chicago.sourceforge.net/devel/docs/excel/
http://sc.openoffice.org/excelfileformat.pdf (PDF, 818 K)
| |