class Excel *---------------------------------------------------------------------* * Athor : Patrick Bower * Date Written : 2nd March 2005 * Version : 01.50 27/08/07 *---------------------------------------------------------------------* * The Excel Class sets up an XML document in a format understandable * by Excel. It provides methods to set up cell attributes, formula * and print options. As well as a means to pass this document to the * browser. The Spreadsheet is populated from left to right, line by * line. * * new(Constructor) * Sets up a new "Excel" document. * Parms: * Sheetname(Optional) - Name of the first Worksheet. * * newSheet * Used to add a new sheet to the "Excel" spreadsheet. * Parms: * Sheetname(Optional) - Name of the New Sheet being added. * * addRowElement * Add a value to the current Cell. * Parms: * elementValue - New Value to be added. * elementType (Optional) - Type of element (ie. Number, String) * styleId (Optional) - User defined style to aply to the cell * merge (Optional) - Merge n cells to the right * comment (Optional) - Add a Comment to a Cell * hyperlink (Optional) - Add a hyperlink to this cell * mergeDown (Optional) - Merge n cells to the bottom * * setStyle * Allow the setup of a user defined style. These styles can be * aplied to cells. * Parms: * styleId - Style Code to be used as a key to this * style. * font (Optional) - Font Name (ie. 'Arial') * fontSize (Optional) - Size of Font. * bold (Optional) - Boolean to switch Bold on(1) or off(0) * italic (Optional) - Boolean to switch Italic on(1) or off(0) * fontColor(Optional) - Colour of Font. * border (Optional) - Set the border width. * bgColor (Optional) - Set the cell colour. * (Format:#FF9999 or preset colurs(Red,Green)) * align (Optional) - Cell Alignment (L)eft, (R)ight or (C)entre. * rotate (Optional) - Rotate text to a degree. (ie '-90') * wrap (Optional) - Allow text to wrap within cell. * numMask (Optional) - Specify an edit mask for numeric fields. * outLine (Optional) - Specify which border to include when using * a border. In order from top clockwise. * eg. 0100 will only include the right border. * Note: Can only be set if Border is set. * lineStyle(Optional) - Border style to use Default 'Continuous'. * Other Options - 'Dash' or 'Dot' * Note: Can only be set if Border is set. * valign (Optional) - Cell Alignment (T)op, (B)ottom or (C)entre. * protect (Optional) - To unprotect cell, set value to '0'. * Note: Can only be set if the worksheet * is protected. * * sumColumn * Sums values in the current column into current cell. * Parms: * rowFrom (Optional) - Row to sum from, will assume, from the top * rowTo (Optional) - Row to sum to, will assume , the previous row * styleID (Optional) - User defined style to aply to the cell * * setColumnWidth * This method will preset the column width for indexed column. * The Columns must be set up front (befor populating the table) and * in column order. * Parms: * column (Optional) - Column to affect. * width (Optional) - Width to set. * * newRow * This method moves active cell to the next row, column 1. * Parms: * height (Optional) - Row height. * * setSheetHeader * This method sets the number of rows from the top to print on every * page when printing. Used for column Headers. * Parms: * noOfRows (Optional) - Number of rows from top of current worksheet * * setFilterOn * This method allows filtering of columns. Always starting from 1 and * ending at row and column set. This needs to be set prior to adding * any data to the sheet. * Parms: * columnFrom - Starting Column. * columnTo - Ending Column. * maxRows - Max Rows that will be included in Filter. * * resetFilterMax * This method allows resetting of filter maximum. It can be called * any time after setFilterOn. It is used to correct the filtermax if * is was not known at the start of the document. * Parms: * maxRows - Max Rows that will be included in Filter. * * setRangeName * Name a cell or range of cells. * Parms: * name - Name to be set for a cell or range of cells * sheetName - work sheet name * range - Specifies the cell range (ie. R2C1:R16C1) * * addDropdown * This method will add a dropdown list to the current cell. It can be * used with the sumQuery method to provide totaling at different * levels.R2C1:R16C1 * Parms: * range - Specifies the cell range that contains * values for the list (ie. R2C1:R16C1 or * if using a named range then the name) * styleId (Optional) - User defined style to aply to the cell. * merge (Optional) - Merge n cells to the right * mergeDown (Optional) - Merge n cells to the bottom * * sumQuery * This is a complex method that will sum values of a column based on * user entered criteria. * Parms: * table - Specifies the cell range that contains * the table to query against (ie. R1C1:R16C3) * field - The column header of the row to sum up in the * table being queried (ie 'Sales'). * query - This is a range containg a table with the * same headings as the data table, and the * query information (ie. R20C1:R21C2) * styleId (Optional) - User defined style to aply to the cell. * * getQuery * This is a complex method that will get values of a column based on * user entered criteria. * Parms: * table - Specifies the cell range that contains * the table to query against (ie. R1C1:R16C3) * field - The column header of the row to sum up in the * table being queried (ie 'Sales'). * query - This is a range containg a table with the * same headings as the data table, and the * query information (ie. R20C1:R21C2) * styleId (Optional) - User defined style to aply to the cell. * * setFormula * This is a complex method that will set a formula to whatever is sent * to the method. * Parms: * formula - Formula to be set * styleId (Optional) - User defined style to aply to the cell. * merge (Optional) - Merge n cells to the right * mergeDown (Optional) - Merge n cells to the bottom * elementType (Optional) - Type of element (ie. Number, String) * * setPrintOptions * Set up some printer defaults for current worksheet. * Parms: * landscape (Optional) - Set Page format to Landscape * header (Optional) - Set Header * trailer (Optional) - Set trailer * noOfRows (Optional) - Set onscreen rows to fix for header. * noOfCols (Optional) - Set onscreen columns to fix. * * setDefaultWidth * Set the default column width. This will be overriden by specific * column settings. * Parms: * width - The default column width * * protectSheet * Set the current sheet to protected. * * serial * Convert the Excel Document to a string. This can be used for storing * reports to a flat file. * * print * Print the XML Document. * * websend * Send the XML Document to the browser. * * outputExcelDoc * Prints the whole document into 131 length lines (serialised). * This can be used to convert report to a flat file. This flat * file can be opend by excel later. note for excel to recognise * the file, it needs to have a xls extention. * *---------------------------------------------------------------------* public variable excelDoc is object xmlDoc variable excelRow is object xmlNode variable excelWorkbook is object xmlNode variable excelWorksheet is object xmlNode variable excelNames is object xmlNode variable excelBookNames is object xmlNode variable excelTable is object xmlNode variable excelStyle is object xmlNode variable excelNamedRange is object xmlNode variable excelAutoFilter is object xmlNode variable crntRow is fixed variable crntColumn is fixed variable crntNoOfRows is fixed variable crntSheetName is string len 31 variable crntFilterFrom is fixed variable crntFilterTo is fixed constructor new( - %sheetName is string len 50 default('sheet1')) subroutine newSheet( - %sheetName is string len 50) subroutine addRowElement( - %elementValue is longString, - %elementType is string len 8 default('String'), - %styleId is string len 7 default(''), - %merge is fixed default('0'), - %comment is string len 50 default(''), - %hyperlink is string len 150 default(''), - %mergeDown is fixed default('0')) subroutine setStyle( - %styleId is string len 7, - %font is string len 20 default('') - nameAllowed, - %fontSize is fixed default(0) - nameAllowed, - %bold is fixed default(0) - nameAllowed, - %italic is fixed default(0) - nameAllowed, - %fontColor is string len 7 default('') - nameAllowed, - %border is fixed default(0) - nameAllowed, - %bgColor is string len 7 default('') - nameAllowed, - %align is string len 1 default('0') - nameAllowed, - %rotate is string len 4 default('0') - nameAllowed, - %wrap is fixed default('0') - nameAllowed, - %numMask is string len 15 default('') - nameAllowed, - %outLine is string len 4 default('1111') - nameAllowed, - %lineStyle is string len 10 - default('Continuous') - nameAllowed, - %valign is string len 1 default('B') - nameAllowed, - %protect is string len 1 default('') - nameAllowed) subroutine sumColumn( - %rowFrom is fixed default(1), - %rowTo is fixed default(0), - %styleId is string len 7 default('')) subroutine setColumnWidth( - %column is fixed default(1), - %width is string len 3 default('A')) subroutine newRow( - %height is string len 3 default('')) subroutine setSheetHeader( - %noOfRows is fixed default(1)) subroutine setFilterOn( - %columnFrom is fixed, - %columnTo is fixed, - %maxRows is fixed) subroutine resetFilterMax( - %maxRows is fixed) subroutine setPrintOptions( - %landscape is fixed default(0), - %header is string len 40 - default('&A&R&D'), - %trailer is string len 40 - default('Page &P of &N'),- %noOfRows is fixed default(0),- %noOfCols is fixed default(0)) subroutine setRangeName( - %name is string len 20, - %sheetName is string len 20, - %range is string len 20) subroutine addDropdown( - %range is string len 20, - %styleId is string len 7 default(''), - %merge is fixed default('0'), - %mergeDown is fixed default('0')) subroutine sumQuery( - %table is string len 20, - %field is string len 20, - %query is string len 20, - %styleId is string len 7 default('')) subroutine getQuery( - %table is string len 20, - %field is string len 20, - %query is string len 20, - %styleId is string len 7 default('')) subroutine setFormula( - %formula is longstring, - %styleId is string len 7 default(''), - %merge is fixed default('0'), - %mergeDown is fixed default('0'), - %elementType is string len 7 default('String')) subroutine protectSheet() subroutine setDefaultWidth( - %width is fixed) function serial() is longstring callable subroutine print subroutine webSend subroutine outputExcelDoc end public constructor new( - %sheetName is string len 50 default('sheet1')) %excelDoc = new %excelDoc:version = '1.0' %excelWorkbook = %excelDoc:addElement('Workbook', , - 'urn:schemas-microsoft-com:office:spreadsheet') %excelWorkbook:addNamespace('o', - 'urn:schemas-microsoft-com:office:office') %excelWorkbook:addNamespace('x', - 'urn:schemas-microsoft-com:office:excel') %excelWorkbook:addNamespace('ss', - 'urn:schemas-microsoft-com:office:spreadsheet') %excelWorkbook:addNamespace('html', - 'http://www.w3.org/TR/REC-html40') %excelDoc:SelectionNamespace('ss') = - 'urn:schemas-microsoft-com:office:spreadsheet' %excelDoc:SelectionNamespace('x') = - 'urn:schemas-microsoft-com:office:excel' %excelWorkbook:addElement('DocumentProperties', , - 'urn:schemas-microsoft-com:office:office') %excelWorkbook:addElement('OfficeDocumentSettings', , - 'urn:schemas-microsoft-com:office:office') %excelWorkbook:addElement('ExcelWorkbook', , - 'urn:schemas-microsoft-com:office:excel') %excelStyle = %excelWorkbook:addElement('Styles') %excelBookNames = %excelWorkbook:addElement('Names') %excelWorksheet = %excelWorkbook:addElement('Worksheet') %crntSheetName = %sheetName %excelWorksheet:addAttribute('ss:Name', %crntSheetName) %excelNames = %excelWorksheet:addElement('Names') %excelTable = %excelWorksheet:addElement('Table') %crntNoOfRows = 0 %crntFilterFrom = 0 %crntFilterTo = 0 %crntRow = 0 %crntColumn = 1 end constructor subroutine newSheet( - %sheetName is string len 50) %excelWorksheet = %excelWorkbook:addElement('Worksheet') %crntSheetName = %sheetName %excelWorksheet:addAttribute('ss:Name', %crntSheetName) %excelNames = %excelWorksheet:addElement('Names') %excelTable = %excelWorksheet:addElement('Table') %crntNoOfRows = 0 %crntFilterFrom = 0 %crntFilterTo = 0 %crntRow = 0 %crntColumn = 1 end subroutine newSheet subroutine addRowElement( - %elementValue is longString, - %elementType is string len 8 default('String'), - %styleId is string len 7 default(''), - %merge is fixed default('0'), - %comment is string len 50 default(''), - %hyperlink is string len 150 default(''), - %mergeDown is fixed default('0')) %cell is object xmlNode %data is object xmlNode %namedCell is object xmlNode %commentNode is object xmlNode %cell = %excelRow:addElement('Cell') if %merge then %cell:addAttribute('ss:MergeAcross',%merge) end if if %mergeDown then %cell:addAttribute('ss:MergeDown',%mergeDown) end if if %styleId ne '' then %cell:addAttribute('ss:StyleID', %styleId) end if if %hyperlink ne '' then %cell:addAttribute('ss:HRef', %hyperlink) end if %data = %cell:addElement('Data', %elementValue) %data:addAttribute('ss:Type', %elementType) if %crntNoOfRows ge %crntRow then %namedCell = %cell:addElement('NamedCell') %namedCell:addAttribute('ss:Name', 'Print_Titles') end if if %crntFilterFrom ge %crntColumn and - %crntFilterTo le %crntColumn then %namedCell = %cell:addElement('NamedCell') %namedCell:addAttribute('ss:Name', '=_FilterDatabase') end if if %comment ne '' then %commentNode = %cell:addElement('Comment') %commentNode:addElement('ss:Data', %comment) end if %crntColumn = %crntColumn + 1 end subroutine addRowElement subroutine setStyle( - %styleId is string len 7, - %font is string len 20 default('') - nameAllowed, - %fontSize is fixed default(0) - nameAllowed, - %bold is fixed default(0) - nameAllowed, - %italic is fixed default(0) - nameAllowed, - %fontColor is string len 7 default('') - nameAllowed, - %border is fixed default(0) - nameAllowed, - %bgColor is string len 7 default('') - nameAllowed, - %align is string len 1 default('0') - nameAllowed, - %rotate is string len 4 default('0') - nameAllowed, - %wrap is fixed default('0') - nameAllowed, - %numMask is string len 15 default('') - nameAllowed, - %outLine is string len 4 default('1111') - nameAllowed, - %lineStyle is string len 10 default('Continuous') - nameAllowed, - %valign is string len 1 default('B') - nameAllowed, - %protect is string len 1 default('') - nameAllowed) %style is object xmlNode %fontNode is object xmlNode %borders is object xmlNode %borderLine is object xmlNode %interior is object xmlNode %alignment is object xmlNode %numberFormat is object xmlNode %protection is object xmlNode %style = %excelStyle:addElement('Style') %style:addAttribute('ss:ID', %styleId) %fontNode = %style:addElement('Font') if %font ne '' then %fontNode:addAttribute('ss:FontName', %font) end if if %numMask ne '' then %numberFormat = %style:addElement('NumberFormat') %numberFormat:addAttribute('ss:Format', %numMask) end if if %fontSize then %fontNode:addAttribute('ss:Size', %fontSize) end if if %fontColor ne '' then if #substr(%fontColor,1,1) = '#' then * Use the colour code entered. elseif %fontColor='Red' then %fontColor='#FF0000' elseif %fontColor='Yellow' then %fontColor='#FFFF00' elseif %fontColor='Blue' then %fontColor='#0000FF' elseif %fontColor='Navy' then %fontColor='#003366' elseif %fontColor='Green' then %fontColor='#008000' elseif %fontColor='LGreen' then %fontColor='#339966' else %fontColor='#FFFFFF' end if %fontNode:addAttribute('ss:Color', %fontColor) end if if %bold then %fontNode:addAttribute('ss:Bold', '1') end if if %italic then %fontNode:addAttribute('ss:Italic', '1') end if if %border then %borders = %style:addElement('Borders') if #substr(%outLine,3,1) then %borderLine = %borders:addElement('Border') %borderLine:addAttribute('ss:Position' , 'Bottom') %borderLine:addAttribute('ss:LineStyle' , %lineStyle) %borderLine:addAttribute('ss:Weight' , %border) end if if #substr(%outLine,4,1) then %borderLine = %borders:addElement('Border') %borderLine:addAttribute('ss:Position' , 'Left') %borderLine:addAttribute('ss:LineStyle' , %lineStyle) %borderLine:addAttribute('ss:Weight' , %border) end if if #substr(%outLine,2,1) then %borderLine = %borders:addElement('Border') %borderLine:addAttribute('ss:Position' , 'Right') %borderLine:addAttribute('ss:LineStyle' , %lineStyle) %borderLine:addAttribute('ss:Weight' , %border) end if if #substr(%outLine,1,1) then %borderLine = %borders:addElement('Border') %borderLine:addAttribute('ss:Position' , 'Top') %borderLine:addAttribute('ss:LineStyle' , %lineStyle) %borderLine:addAttribute('ss:Weight' , %border) end if end if if %bgColor ne '' then if #substr(%bgColor,1,1) = '#' then * Use the colour code entered. elseif %bgColor='Red' then %bgColor='#FF0000' elseif %bgColor='Yellow' then %bgColor='#FFFF00' elseif %bgColor='Blue' then %bgColor='#0000FF' elseif %bgColor='Navy' then %bgColor='#003366' elseif %bgColor='Green' then %bgColor='#008000' elseif %bgColor='LGreen' then %bgColor='#339966' else %bgColor='#C0C0C0' end if %interior = %style:addElement('Interior') %interior:addAttribute('ss:Color', %bgColor) %interior:addAttribute('ss:Pattern', 'Solid') end if if %align ne '' or - %valign ne '' or - %rotate ne '' or - %wrap then %alignment = %style:addElement('Alignment') if %align = 'C' then %alignment:addAttribute('ss:Horizontal','Center') elseif %align = 'R' then %alignment:addAttribute('ss:Horizontal','Right') elseif %align = 'L' then %alignment:addAttribute('ss:Horizontal','Left') end if if %valign = 'C' then %alignment:addAttribute('ss:Vertical','Center') elseif %valign = 'T' then %alignment:addAttribute('ss:Vertical','Top') elseif %valign = 'B' then %alignment:addAttribute('ss:Vertical','Bottom') end if if %rotate ne '0' then %alignment:addAttribute('ss:Rotate', %rotate) end if if %wrap then %alignment:addAttribute('ss:WrapText',%wrap) end if end if %protection = %style:addElement('Protection') if %protect ne '' then %protection:addAttribute('ss:Protected', %protect) end if end subroutine setStyle subroutine setColumnWidth( - %column is fixed default(1), - %width is string len 3 default('A')) %col is object xmlNode %col = %excelTable:addElement('Column') %col:addAttribute('ss:Index', %column) if %width = 'A' then %col:addAttribute('ss:AutoFitWidth', '1') else %col:addAttribute('ss:AutoFitWidth', '0') %col:addAttribute('ss:Width' , %width) end if end subroutine setColumnWidth subroutine newRow( - %height is string len 3 default('')) %excelRow = %excelTable:addElement('Row') if not %height = '' then %excelRow:addAttribute('ss:AutoFitHeight', '0') %excelRow:addAttribute('ss:Height' , %height) end if %crntColumn = 1 %crntRow = %crntRow + 1 end subroutine newRow subroutine sumColumn( - %rowFrom is fixed default(1), - %rowTo is fixed default(0), - %styleId is string len 7 default('')) %cell is object xmlNode %data is object xmlNode %formula is string len 20 %row is fixed if not %rowTo then %row = %crntRow - 1 else %row = %rowTo end if %formula = '=SUM(R' with %rowFrom with 'C' with %crntColumn with - ':R' with %row with 'C' with %crntColumn with ')' %cell = %excelRow:addElement('Cell') %cell:addAttribute('ss:Formula', %formula) if %styleId ne '' then %cell:addAttribute('ss:StyleID', %styleId) end if %data = %cell:addElement('Data', '0') %data:addAttribute('ss:Type', 'Number') %crntColumn = %crntColumn + 1 end subroutine sumColumn subroutine setSheetHeader( - %noOfRows is fixed default(1)) %namedRange is object xmlNode %reference is string len 50 %reference = '=''' with %crntSheetName with '''' with '!R1' with - ':R' with %noOfRows %namedRange = %excelNames:addElement('NamedRange') %namedRange:addAttribute('ss:Name', 'Print_Titles') %namedRange:addAttribute('ss:RefersTo', %reference) %crntNoOfRows = %noOfRows end subroutine setSheetHeader subroutine setFilterOn( - %columnFrom is fixed, - %columnTo is fixed, - %maxRows is fixed) %reference is string len 50 %range is string len 20 %ssURI is string len 50 %xURI is string len 50 %range = 'R1C' with %columnFrom with ':R' with %maxRows with - 'C' with %ColumnTo %reference = '=''' with %crntSheetName with '''' with - '!' with %range %ssURI = %excelDoc:SelectionNamespace('ss') %xURI = %excelDoc:SelectionNamespace('x') %excelNamedRange = %excelNames:addElement('NamedRange') %excelNamedRange:addAttribute('ss:Name', '_FilterDatabase') %excelNamedRange:addAttribute('ss:RefersTo', %reference, %ssURI) %excelAutoFilter = - %excelWorksheet:addElement('AutoFilter',, - 'urn:schemas-microsoft-com:office:excel') %excelAutoFilter:addAttribute('x:Range', %range, %xURI) %crntFilterFrom = %columnFrom %crntFilterTo = %ColumnTo end subroutine setFilterOn subroutine resetFilterMax( - %maxRows is fixed) %reference is string len 50 %range is string len 20 %newRefer is string len 50 %newRange is string len 20 %pos is float %reference = %excelNamedRange:value('@ss:RefersTo') %range = %excelAutoFilter:value('@x:Range') * Find and load data prior to the new value %pos = (#index(%reference,':R')) + 1 %newRefer = #substr(%reference,1,%pos) with %maxRows * Find and load the data after new value %reference = #substr(%reference,%pos) %pos = #index(%reference,'C') %newRefer = %newRefer with #substr(%reference,%pos) %excelNamedRange:value('@ss:RefersTo') = %newRefer * Find and load data prior to the new value %pos = (#index(%range,':R')) + 1 %newRange = #substr(%range,1,%pos) with %maxRows * Find and load the data after new value %range = #substr(%range,%pos) %pos = (#index(%range,'C')) %newRange = %newRange with #substr(%range,%pos) %excelAutoFilter:value('@x:Range') = %newRange end subroutine resetFilterMax subroutine setRangeName( - %name is string len 20, - %sheetName is string len 20, - %range is string len 20) %namedRange is object xmlNode %reference is string len 50 %ssURI is string len 50 %ssURI = %excelDoc:SelectionNamespace('ss') %reference = '=''' with %SheetName with '''' with '!' with %range %namedRange = %excelBookNames:addElement('NamedRange') %namedRange:addAttribute('ss:Name', %name) %namedRange:addAttribute('ss:RefersTo', %reference, %ssURI) end subroutine setRangeName subroutine addDropdown( - %range is string len 20, - %styleId is string len 7 default(''), - %merge is fixed default('0'),- %mergeDown is fixed default('0')) %cell is object xmlNode %dataValidation is object xmlNode %currentCell is string len 10 %cell = %excelRow:addElement('Cell') if %merge then %cell:addAttribute('ss:MergeAcross',%merge) end if if %mergeDown then %cell:addAttribute('ss:MergeDown',%mergeDown) end if if %styleId ne '' then %cell:addAttribute('ss:StyleID', %styleId) end if %currentCell = 'R' with %crntRow with 'C' with %crntColumn %dataValidation = - %excelWorksheet:addElement('DataValidation',, - 'urn:schemas-microsoft-com:office:excel') %dataValidation:addElement('Range',%currentCell) %dataValidation:addElement('Type','List') %dataValidation:addElement('UseBlank') %dataValidation:addElement('Value',%range) end subroutine addDropdown subroutine sumQuery( - %table is string len 20, - %field is string len 20, - %query is string len 20, - %styleId is string len 7 default('')) %cell is object xmlNode %data is object xmlNode %formula is longString %formula = '=DSUM(' with %table with ',"' with %field with - '",' with %query with ')' %cell = %excelRow:addElement('Cell') if %styleId ne '' then %cell:addAttribute('ss:StyleID', %styleId) end if %cell:addAttribute('ss:Formula',%formula) %data = %cell:addElement('Data','0') %data:addAttribute('ss:Type', 'Number') %crntColumn = %crntColumn + 1 end subroutine sumQuery subroutine getQuery( - %table is string len 20, - %field is string len 20, - %query is string len 20, - %styleId is string len 7 default('')) %cell is object xmlNode %data is object xmlNode %formula is longString %formula = '=DGET(' with %table with ',"' with %field with - '",' with %query with ')' %cell = %excelRow:addElement('Cell') if %styleId ne '' then %cell:addAttribute('ss:StyleID', %styleId) end if %cell:addAttribute('ss:Formula',%formula) %data = %cell:addElement('Data','') %data:addAttribute('ss:Type', 'String') %crntColumn = %crntColumn + 1 end subroutine getQuery subroutine setFormula( - %formula is longstring, - %styleId is string len 7 default(''), - %merge is fixed default('0'), - %mergeDown is fixed default('0'), - %elementType is string len 7 default('String')) %cell is object xmlNode %data is object xmlNode %cell = %excelRow:addElement('Cell') if %merge then %cell:addAttribute('ss:MergeAcross',%merge) end if if %mergeDown then %cell:addAttribute('ss:MergeDown',%mergeDown) end if if %styleId ne '' then %cell:addAttribute('ss:StyleID', %styleId) end if %cell:addAttribute('ss:Formula',%formula) %data = %cell:addElement('Data','') %data:addAttribute('ss:Type', %elementType) %crntColumn = %crntColumn + 1 end subroutine setFormula subroutine setPrintOptions( - %landscape is fixed default(0), - %header is string len 40 - default('&A&R&D'), - %trailer is string len 40 - default('Page &P of &N'), - %noOfRows is fixed default(0),- %noOfCols is fixed default(0)) %worksheetOptions is object xmlNode %pageSetup is object xmlNode %layout is object xmlNode %print is object xmlNode %headNode is object xmlNode %footer is object xmlNode %worksheetOptions = - %excelWorksheet:addElement('WorksheetOptions',, - 'urn:schemas-microsoft-com:office:excel') %pageSetup = %worksheetOptions:addElement('PageSetup') if %landscape then %layout = %pageSetup:addElement('Layout') %layout:addAttribute('x:Orientation','Landscape') end if if (%noOfRows or %noOfCols) then %worksheetOptions:addElement('FreezePanes') %worksheetOptions:addElement('FrozenNoSplit') %worksheetOptions:addElement('SplitHorizontal',%noOfRows) %worksheetOptions:addElement('TopRowBottomPane',%noOfRows) %worksheetOptions:addElement('SplitVertical',%noOfCols) %worksheetOptions:addElement('LeftColumnRightPane',%noOfCols) %worksheetOptions:addElement('ActivePane',2) end if %headNode = %pageSetup:addElement('Header') %headNode:addAttribute('x:Data', %header) %footer = %pageSetup:addElement('Footer') %footer:addAttribute('x:Data', %trailer) %print = %worksheetOptions:addElement('Print') %print:addElement('ValidPrinterInfo') %print:addElement('PaperSizeIndex','9') %print:addElement('HorizontalResolution','600') %print:addElement('VerticalResolution','600') %print:addElement('Selected') %print:addElement('ProtectObjects','False') %print:addElement('ProtectScenarios','False') %worksheetOptions:addElement('DoNotDisplayGridlines') end subroutine setPrintOptions subroutine setDefaultWidth( - %width is fixed) %excelTable:addAttribute('ss:DefaultColumnWidth',%width) end subroutine setDefaultWidth subroutine protectSheet() %excelWorksheet:addAttribute('ss:Protected','1') end subroutine protectSheet function serial() is longstring callable return %excelDoc:serial(,'EBCDIC') end function serial subroutine print %excelDoc:print end subroutine print subroutine webSend %excelDoc:webSend end subroutine webSend subroutine outputExcelDoc %ls is longstring %string is string len 131 %from is fixed %len is fixed %ls = %excelDoc:serial(,'EBCDIC') print '' %from = 1 %len = #lstr_len(%ls) repeat %string = #lstr_substr(%ls,%from,131) * %ls = #lstr_substr(%ls,132) print %string %from = %from + 131 if %from > %len then loop end end if end repeat end subroutine outputExcelDoc end class