modula-2 home

  Home  
  Tutorial  
  Win32 API  
  Reference  
  Projects  
 

 

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)