modula-2 home

  Home  
  Tutorial  
  Win32 API  
  Reference  
  Projects  
 

 

Library to write Excel files

For XDS and Stony Brook Modula-2

By Frank Schoonjans (frank.schoonjans@medcalc.be)

 

It's always nice when a program can export its data in a common file format such was Microsoft's Excel file format.

The download ExcelOut.zip contains the complete source of a library (both definition and implementation modules) that will enable any program to write Excel files.

The implemention module ExcelOut imports the ISO modules IOChan,ChanConsts and RndFile.

We have tested the software on both XDS and Stony Brook Modula-2, but it should be easy to adapt for any other development kit.

Note: Stony Brook users should remove or comment out the <* ALIGNMENT="1" *> directive in the ExcelOut.mod implementation module.

Use the CreateExcelFile function to create and initialize the Excel file. This function returns a handle which should be used in all subsequent procedures. After creating the file, you can optionally specify a global column width (unit = characters) for the spreadsheet, or specify the width of particular columns. Next data can be written to the file: strings, real numbers, cardinals and integers. Write the data row by row, top to bottom, and for every row the columns from left to right. Finally close the file.

In principle the file created by the current software should be compatible will all Excel versions starting from 2.1. We have only tested with Excel 5 and more recent (up to Office XP).

Your comments are appreciated.

This is the ExcelOut DEFINITION MODULE:

DEFINITION MODULE ExcelOut;

(* Routines to store data in Excel v. 5 format *)

(* Follow these steps to create an Excel file:
   1. CreateExelFile
   2. WriteDefaultColumnWidth - if desired
   3. WriteColumnWidth for specific columns - if desired
   4. Write data:   row by row, column by column.
   5. CloseExelFile
*)

FROM SYSTEM IMPORT CAST;

TYPE EXCEL_HANDLE;

CONST INVALID_EXCEL_HANDLE = CAST(EXCEL_HANDLE,NIL);

PROCEDURE CreateExelFile(filename : ARRAY OF CHAR) : EXCEL_HANDLE;
PROCEDURE CloseExelFile(handle: EXCEL_HANDLE);

PROCEDURE WriteDefaultColumnWidth(handle: EXCEL_HANDLE; width : INTEGER);
PROCEDURE WriteColumnWidth(handle: EXCEL_HANDLE; column, width : INTEGER);

PROCEDURE WriteReal(handle: EXCEL_HANDLE; r,c : INTEGER; num : LONGREAL);
PROCEDURE WriteInteger(handle: EXCEL_HANDLE; r,c : INTEGER; num : INTEGER);
PROCEDURE WriteCardinal(handle: EXCEL_HANDLE; r,c : INTEGER; num : CARDINAL);
PROCEDURE WriteString(handle: EXCEL_HANDLE; r,c : INTEGER; s : ARRAY OF CHAR);

END ExcelOut.

The use of the library is illustrated in the following program:

MODULE ExcelOutTest;

FROM ExcelOut IMPORT EXCEL_HANDLE,INVALID_EXCEL_HANDLE,
                     CreateExelFile,CloseExelFile,
                     WriteDefaultColumnWidth,WriteColumnWidth,
                     WriteReal,WriteInteger,WriteString;

VAR handle     : EXCEL_HANDLE;
    row,column : INTEGER;
    str        : ARRAY [0..80] OF CHAR;
BEGIN
    handle:=CreateExelFile("test.xls");
    IF handle=INVALID_EXCEL_HANDLE THEN
      (* problem *)
      RETURN;
    END;
    WriteDefaultColumnWidth(handle,10);
    WriteColumnWidth(handle,0,15);
    WriteString(handle,0,0,"http://www.modula2.org");
    FOR column:=0 TO 8 DO
      WriteReal(handle,1,column,VAL(LONGREAL,column)+0.5);
    END;
    FOR row:=3 TO 6 DO
      FOR column:=0 TO 8 DO
         WriteInteger(handle,row,column,row*1000+column);
      END;
    END;
    FOR column:=4 TO 20 DO
      str[0]:=CHR(65+column); str[1]:=00C;
      WriteString(handle,8,column,str);
    END;
    FOR row:=10 TO 299 DO
      WriteInteger(handle,row,5,row-150);
    END;
    CloseExelFile(handle);
END ExcelOutTest.

Excel file format documentation

http://chicago.sourceforge.net/devel/docs/excel/

http://sc.openoffice.org/excelfileformat.pdf (PDF, 818 K)

Version for Ada

This project has been tranlated to Ada, with some enhancements, by Gautier de Montmollin.

See http://sourceforge.net/projects/excel-writer/