Excel import export library

This simple library of functions and procedures contains three major procedures...

inputXLS() --input to temp-tables an xls/xlsx file using Windows Excel API (requires installed Excel)
outputXLS() --output to xls/xlsx file using Windows Excel API (fully featured but very slow for large spreadsheets)
outputXML() --output to Excel XML Spreadsheet 2003 format using OE datasets (super fast and does not require Excel or even Windows)

...with four support functions...

createCell()
createFormula()
createSheet()
createSheetWithProperties()

Creating a spreadsheet is as simple as...

define variable wxls_no as integer initial 1 no-undo.
define variable wsheet_no as integer initial 1 no-undo.
define variable werr_msg as character no-undo.
define variable wtrans_ok as logical no-undo.
{xls.i}

createSheetWithProperties(wxls_no,wsheet_no,"Sheet One","LANDSCAPE=TRUE|FITTOPAGE=1").
createCell(wxls_no,wsheet_no,1,1,"Text Example","TEXT=TRUE|BGCOLOR=GRAY|BOLD=TRUE|UNDERLINE=TRUE|RIGHTALIGN=TRUE").
createCell(wxls_no,wsheet_no,2,1,string(1234,"$->>>,>>>,>>9.99"),"NUMBER=TRUE|FORMAT=$#,##0.00_);($#,##0.00)").
createCell(wxls_no,wsheet_no,3,1,string(2345,"$->>>,>>>,>>9.99"),"NUMBER=TRUE|UNDERLINE=TRUE|FORMAT=$#,##0.00_);($#,##0.00)").
createFormula(wxls_no,wsheet_no,2,1,"=SUM(R[-2]C:R[-1]C)","FORMAT=$#,##0.00_);($#,##0.00)|BOLD=TRUE|HEAVYBOX=TRUE").

assign wsheet_no = wsheet_no + 1.
createSheetWithProperties(wxls_no,wsheet_no,"Sheet Two","LANDSCAPE=TRUE|FITTOPAGE=1").
createCell(wxls_no,wsheet_no,1,1,"Text Example","TEXT=TRUE|BGCOLOR=LIGHTGREEN|BOLD=TRUE|UNDERLINE=TRUE|RIGHTALIGN=TRUE").

run outputXML(input "example.xml",input wxls_no,output wtrans_ok,output werr_msg).
run outputXLS(input "example.xlsx",input wxls_no,output wtrans_ok,output werr_msg).


Releases



AttachmentSize
xls.i128.81 KB
sys.i8.58 KB