Spreadsheet400 by cKJ3212f

VIEWS: 0 PAGES: 14

									Spreadsheet400
Since the introduction of Microsoft® Office XP, it is possible to create spreadsheets using SpreadsheetML,
an XML dialect developed by Microsoft to represent the information in an Excel spreadsheet.
This tool contains most used (80% in usage, 20% in volume) tags of the MS SpreadSheetML dialect. It’s a
native SQLRPGLE copy. Yes. You can include it in any of your existing 400 PGM, produce an IFS text file
containing the SpreadShetML, then open this (IFS text file) spreadsheet with Excel 2003.
How to use it
      First, build a template
open Excel, and key in a complete sample, including freeze panes, colors, fonts, borders, …
Save this template under excel format,
Save this template under XML format,
Close Excel
Reopen the XML and the XLS versions of the template, compare them. You now have an idea of what
SpreadSheetML can do, and what it can not. For example SSML can not save graphs.
          Second, look at the SSML text code
It’s not so complicated. I have no tool to read the XSD shemas, so I have only used the MS office 2003 XML
reference Shemas help (OfficeXMLSDK.chm in xsdref.msi, available at
http://www.microsoft.com/downloads/details.aspx?FamilyID=fe118952-3547-420a-a412-
00a2662442d9&displaylang=en ) to understand this language.
          Third, rewrite the SSML under RPG
Using the constants and procedures provided by the copy, you can mimic the SSML, this will build a SSML
you will read with Excel 2003

The SSML reference, and the RPG implementation

XML SpreadSheet 2002

Workbook
      o:DocumentProperties
      o:CustomDocumentProperties
      Styles
             Style
                    Alignment
                    Borders
                             Border
                    Font
                    Interior
                    NumberFormat
                    Protection
      Names
             NamedRange
      Worksheet
             Column
             Table
                    Row
                             Cell
                                       Data
                                       Comment
       o:SmartTagType
       o:OfficeDocumentSettings
       x:ExcelWorkbook
       x:PivotCache
       x:Name
       x2:MapInfo
       x2:Binding
       c:ComponentOptions


XML SpreadSheet 2000
AlertVersion
Alignment
AllItemName
AlternateMethod
Area
AutoFilter
AutoFilterAnd
AutoFilterColumn
AutoFilterCondition
AutoFilterCondition
AutoFilterCondition
AutoFilterOr
AutoFormatAlignment
AutoFormatBorder
AutoFormatFont
AutoFormatName
AutoFormatNumber
AutoFormatPattern
AutoFormatPattern
AutoFormatWidth
AutoRepublish
AutoShowCount
AutoShowField
AutoShowRange
AutoShowType
AutoSortField
AutoSortOrder
BackgroundQuery
BaseField
BaseItem
BlackAndWhite
BlankLineAfterItems
BlockTotal
Boolean
Boolean
BoundField
CacheFile
CacheFile
CacheIndex
CacheIndex
CacheIndex
CachePosition
Calculation
CantGetUniqueItems
Caption
Caption
Caption
Caption
Caption
Caption
Caption
Caption
CaptionAlignment
CaseSensitive
CaseSensitive
Cell
CellRangeList
CellsExpanded
CellsExpandedSeqNum
CellsNotExpanded
Cf
Clipped
ColBreak
ColBreaks
ColFirst
ColLast
ColLast

Column
Column
ColumnInfo
ColumnInputCell
ComboHide
Comma
CommandText
CommandText
CommandTextOrignal
CommandType
CommandType
CommentsLayout
Condition
ConditionalFormatting
Connection
Connection
Consecutive

ConsolidationReference
ConsolidationReference
Count
CountOfSameItems
Crn
CubeSource
CurrentPage
CurrentPage
Custom
DataField
DataField
DataMember
DataMember
DataTable
DataType
DataType
DataValidation
Decimal
DefaultItem
DeletedTitle
Delimiters
Descending
Descending
DetailFormat
DetailFormat
DetailMaxHeight
DetailMaxWidth
DetailRowHeight
DetailSortOrder
DetailWidth
DetailWidth
DetailWidth
Dimension
DisableDateRecognition
DisableEdit
DisableRefresh
DisableRefresh
DisplayEmptyMembers
DisplayIn
DisplayRightToLeft
DivID
DivID
DoNotJoinDelimiters
DoNotPersist
DoNotPersist
DoNotPersistSort
DoNotPersstAF
DoNotPromptForFile
DoNotSaveLinkValues
DontShowInFieldList
DraftQuality
DrilledLevel
DrilledMember
EditWebPage
EnableMultiplePageItems
EnableRedirections
EntirePage
Error
Error
ErrorHide
ErrorMessage
ErrorStyle
ErrorTitle
ExcelName
ExcelType
ExcelWorkbook
Expanded
Expanded
Expanded
Expanded
ExternName
ExtraLeftColumns
ExtraRightColumns
FieldLabelFormat
FieldListTop
FieldStart
FieldType
FileName
FillDown
Filled
FilterCaption
FilterCaption
FilterMember
FilterOn
FitHeight
FitToPage
FitWidth
Footer
Format
FormatSettings
FormatType
Formula
Formula
Formula
Formula
Formula
Formula
FormulaIndex
FormulaIndex
FormulaV10
FormulaV10

FormulaV10
Function
FunctionGroup
FunctionGroupIndex
FuturePersist
Gridlines
GroupBy
GroupDefinition
GroupedWidth
GroupedWidth
GroupEnd
GroupEndAuto
GroupLevel
GroupMember
GroupNumber
GroupStart
GroupStartAuto
GroupType
HasNoRecords
Header
HeaderRow
Hidden
Hidden
HideDetail
HideDropDowns
HideDropDowns
Horizontal
HorizontalResolution
HTMLFormat
HTMLTables
HtmlType
Id
Id
IdWrapped
IMEMode
IndividualCellBorders
InputHide
InputMessage
InputTitle
InsertEntireRows
InvertedColumnMember
InvertedRowMember
IsGroupLevel
IsMemberProperty
IsNotFiltered
Item
Item
Item
ItemType
KeyboardShortcut
Label
Label
Layout
LayoutForm
LayoutForm
LayoutPageBreak
LayoutSubtotalLocation
LayoutSubtotalLocation
LeafColumnMember
LeafRowMember
Left
Left
LeftToRight
LeftToRight
LengthLevelUniqueName
Level
LocalConnection
LocalConnection
Location
Macro
Maintain
Maintain
MapChildItems
Max
Measure
Member
MemberFormat
MemberPropertiesOrder
MemberPropertiesOrder
MemberProperty
MemberProperty
MemberPropertyParent
Min
Missing
Missing
MissingItemsLimit
Moper
MoveAfterReturn
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
Name
NewAsync
NewItemsHidden
NewItemsHidden
NextId
NoAutoFit
NoAutoFit
NoAutoPage
NoDetailAutoFit
NoDragToColumn
NoDragToColumn
NoDragToData
NoDragToData
NoDragToHide
NoDragToHide
NoDragToPage
NoDragToPage
NoDragToRow
NoDragToRow
NonDefaultName
NoPreserveFormatting
NoPreserveFormatting
NoPrinterInfo
NoRefreshCache
NoSaveData
NoSaveData
NoTextToColumns
NotInverted
NoTitles
NoTitles
NoToggleDataHeader
NotVisible
Number
Number
Number
NumberFormat
NumberFormat
NumberFormat
NumberOfCopies
OleLink
Operator
OptimizeCache
Orientation
Orientation
Orientation
Orientation
Orientation
Outline
OverwriteCells
PageBreaks
PageMargins
PageSetup
Pane
Pane
PaperSizeIndex
Parameter
Parameter
ParameterType
ParameterValue
ParentField
ParentField
ParentIsOther
ParentName
ParentUniqueName
ParseFormulaAsV10
ParseFormulaAsV10
ParseFormulaAsV10
ParseRuleAsV10
Path
Path
Path
Path
Path
phonetictext
PivotCache

PivotItem
PivotItem
PivotTable
PLCaption
PLDataOrientation
PLDataOrientation
PLGroupType
PLName
PLPosition
PLPosition
PLSubtotal
PLSubtotal
PLTPivotItem
Position
Position
PrintErrors
PromptString
PTLineItem
PTPivotData
PTRule
PTRule
PTRule
PublishObjects
Qualifier
Qualifier
Query97
QuerySource
QuerySource
QuerySource
QueryTable
QueryType
QueryType
Range
Range
Range
ReadingOrder
Reference
Reference
RefreshDate
RefreshDateCopy
RefreshedInXl9
RefreshInfo
RefreshInfo
RefreshName
RefreshOnChange
RefreshOnFileOpen
RefreshOnFileOpen
RefreshTimeSpan
RefreshTimeSpan
RobustConnect
RobustConnect
Rotation
Row
Row
RowBreak
RowBreaks
RowColHeadings
RowInputCell
RowLast
RowNumbers
RowNumbers
Rule
RuleType
RuleV10
Scale
SemiColon
SeqNum
SeqNum
ServerBased
ServerSortOrder
Set
SheetIndex
SheetIndex
SheetName

ShowAllItems
ShowAllItems
Sort
Sort
Sorting
SortKey
SortOrder
Source
Source
Source
SourceConnectionFile
SourceConnectionFile
SourceConsolidation
SourceDataFile
SourceDataFile
SourceHierarchy
SourceHierarchyLevel
SourceHierarchyLevel
SourceName
SourceName
SourceName
SourceType
Space
SQLType
SQLType
StartRow
Subtotal
Subtotal
SubtotalFormat
SubtotalFormat
SubtotalName
Synchronous
Tab
Text
Text
Text
TextQualifier
TextWizardSettings
ThousandSeparator
Top
Top
TotalAlignment
TotalAllMembers
TotalCaptionAlignment
TotalFormat
TotalWidth
TotalWidth
Type
UniqueName
UniqueName
UniqueName
UniqueName
UniqueName
UniqueName
URLString
UseBlank
UseLocalConnection
User
UseSameSettings
ValidPrinterInfo
Value
Value1
Value2
VersionLastEdit
VersionLastEdit
VersionLastRefresh
VersionLastRefresh
VersionLastRefresh
VersionRefreshableMin
VersionRefreshableMin
VersionRefreshableMin
Vertical
VerticalResolution
WantAdvise
WantPict
Watch
Watches
WebPostString
Width
WorkbookOptions
WorksheetOptions
WorksheetSource
Xct



ExcelWorkBook.AcceptLabelsInFormulas
ExcelWorkBook.ActiveChart
ExcelWorkBook.ActiveSheet
ExcelWorkBook.Calculation
ExcelWorkBook.CreateBackup
ExcelWorkBook.Date1904
ExcelWorkBook.DisplayDrawingObjects
ExcelWorkBook.DisplayInkNotes
ExcelWorkBook.DoNotCalculateBeforeSave
ExcelWorkBook.DoNotSaveLinkValues
ExcelWorkBook.EmbedSaveSmartTags
ExcelWorkBook.FirstVisibleSheet
ExcelWorkBook.FutureVer
ExcelWorkBook.HideHorizontalScrollBar
ExcelWorkBook.HidePivotTableFieldList
ExcelWorkBook.HideVerticalScrollBar
ExcelWorkBook.HideWorkbookTabs
ExcelWorkBook.Iteration
ExcelWorkBook.MaxChange
ExcelWorkBook.MaxIterations
ExcelWorkBook.NoAutoRecover
ExcelWorkBook.PrecisionAsDisplayed
ExcelWorkBook.ProtectStructure
ExcelWorkBook.ProtectWindows
ExcelWorkBook.PublishObjects
ExcelWorkBook.RefModeR1C1
ExcelWorkBook.SelectedSheets
ExcelWorkBook.SupBook
ExcelWorkBook.TabRatio
ExcelWorkBook.Uncalced
ExcelWorkBook.WindowHeight
ExcelWorkBook.WindowHidden
ExcelWorkBook.WindowIconic
ExcelWorkBook.WindowTopX
ExcelWorkBook.WindowTopY
ExcelWorkBook.WindowWidth


WorkBook.WorkSheet.x:WorkSheetOptions.Panes.Pane.
WorkBook.WorkSheet.x:WorkSheetOptions.Panes.Pane.Number
WorkBook.WorkSheet.x:WorkSheetOptions.Panes.Pane.ActiveRow
WorkBook.WorkSheet.x:WorkSheetOptions.Panes.Pane.ActiveCol
WorkBook.WorkSheet.x:WorkSheetOptions.Panes.Pane.RangeSelection
WorkBook.WorkSheet.x:WorkSheetOptions.Panes.Pane.Selection
WorkBook.WorkSheet.x:WorkSheetOptions.ActiveColumn
WorkBook.WorkSheet.x:WorkSheetOptions.ActivePane
WorkBook.WorkSheet.x:WorkSheetOptions.ActiveRow
WorkBook.WorkSheet.x:WorkSheetOptions.AllowDeleteCols
WorkBook.WorkSheet.x:WorkSheetOptions.AllowDeleteRows
WorkBook.WorkSheet.x:WorkSheetOptions.AllowFilter
WorkBook.WorkSheet.x:WorkSheetOptions.AllowFormatCells
WorkBook.WorkSheet.x:WorkSheetOptions.AllowInsertCols
WorkBook.WorkSheet.x:WorkSheetOptions.AllowInsertHyperlinks
WorkBook.WorkSheet.x:WorkSheetOptions.AllowInsertRows
WorkBook.WorkSheet.x:WorkSheetOptions.AllowSizeCols
WorkBook.WorkSheet.x:WorkSheetOptions.AllowSizeRows
WorkBook.WorkSheet.x:WorkSheetOptions.AllowSort
WorkBook.WorkSheet.x:WorkSheetOptions.AllowUsePivotTables
WorkBook.WorkSheet.x:WorkSheetOptions.ApplyAutomaticOutlineStyles
WorkBook.WorkSheet.x:WorkSheetOptions.CodeName
WorkBook.WorkSheet.x:WorkSheetOptions.DefaultColumnWidth
WorkBook.WorkSheet.x:WorkSheetOptions.DefaultRowHeight
WorkBook.WorkSheet.x:WorkSheetOptions.DisplayFormulas
WorkBook.WorkSheet.x:WorkSheetOptions.DisplayPageBreak
WorkBook.WorkSheet.x:WorkSheetOptions.DisplayRightToLeft
WorkBook.WorkSheet.x:WorkSheetOptions.DoNotDisplayColHeaders
WorkBook.WorkSheet.x:WorkSheetOptions.DoNotDisplayGridlines
WorkBook.WorkSheet.x:WorkSheetOptions.DoNotDisplayHeadings
WorkBook.WorkSheet.x:WorkSheetOptions.DoNotDisplayOutline
WorkBook.WorkSheet.x:WorkSheetOptions.DoNotDisplayRowHeaders
WorkBook.WorkSheet.x:WorkSheetOptions.DoNotDisplayZeros
WorkBook.WorkSheet.x:WorkSheetOptions.EnableSelection
WorkBook.WorkSheet.x:WorkSheetOptions.ExcelWorksheetType
WorkBook.WorkSheet.x:WorkSheetOptions.FilterOn
WorkBook.WorkSheet.x:WorkSheetOptions.FreezePanes
WorkBook.WorkSheet.x:WorkSheetOptions.FrozenNoSplit
WorkBook.WorkSheet.x:WorkSheetOptions.GridlineColor
WorkBook.WorkSheet.x:WorkSheetOptions.GridlineColorIndex
WorkBook.WorkSheet.x:WorkSheetOptions.IntlMacro
WorkBook.WorkSheet.x:WorkSheetOptions.LeftColumnRightPane
WorkBook.WorkSheet.x:WorkSheetOptions.LeftColumnVisible
WorkBook.WorkSheet.x:WorkSheetOptions.Name
WorkBook.WorkSheet.x:WorkSheetOptions.NoSummaryColumnsRightDetail
WorkBook.WorkSheet.x:WorkSheetOptions.NoSummaryRowsBelowDetail
WorkBook.WorkSheet.x:WorkSheetOptions.PageBreakZoom
WorkBook.WorkSheet.x:WorkSheetOptions.PageSetup
WorkBook.WorkSheet.x:WorkSheetOptions.Panes
WorkBook.WorkSheet.x:WorkSheetOptions.Print
WorkBook.WorkSheet.x:WorkSheetOptions.ProtectContents
WorkBook.WorkSheet.x:WorkSheetOptions.ProtectObjects
WorkBook.WorkSheet.x:WorkSheetOptions.ProtectScenarios
WorkBook.WorkSheet.x:WorkSheetOptions.RangeSelection

WorkBook.WorkSheet.x:WorkSheetOptions.Selected
WorkBook.WorkSheet.x:WorkSheetOptions.Selection
WorkBook.WorkSheet.x:WorkSheetOptions.ShowPageBreakZoom
WorkBook.WorkSheet.x:WorkSheetOptions.SpaceAbove
WorkBook.WorkSheet.x:WorkSheetOptions.SpaceBelow
WorkBook.WorkSheet.x:WorkSheetOptions.SplitHorizontal
WorkBook.WorkSheet.x:WorkSheetOptions.SplitVertical
WorkBook.WorkSheet.x:WorkSheetOptions.StandardWidth
WorkBook.WorkSheet.x:WorkSheetOptions.TabColorIndex
WorkBook.WorkSheet.x:WorkSheetOptions.TopRowBottomPane
WorkBook.WorkSheet.x:WorkSheetOptions.TopRowVisible
WorkBook.WorkSheet.x:WorkSheetOptions.TransitionExpressionEvaluation
WorkBook.WorkSheet.x:WorkSheetOptions.TransitionFormulaEntry
WorkBook.WorkSheet.x:WorkSheetOptions.Unsynced
WorkBook.WorkSheet.x:WorkSheetOptions.ViewableRange
WorkBook.WorkSheet.x:WorkSheetOptions.Visible
WorkBook.WorkSheet.x:WorkSheetOptions.ZeroHeight
WorkBook.WorkSheet.x:WorkSheetOptions.Zoom
WorkBook.WorkSheet.x:
WorkBook.WorkSheet.Names
WorkBook.WorkSheet.Table
WorkBook.WorkSheet.x:WorksheetOptions
WorkBook.WorkSheet.x:Watches
WorkBook.WorkSheet.x:DataTable
WorkBook.WorkSheet.x:PageBreaks
WorkBook.WorkSheet.x:PivotTable
WorkBook.WorkSheet.x:DataValidation
WorkBook.WorkSheet.x:AutoFilter
WorkBook.WorkSheet.x:ConditionalFormatting
WorkBook.WorkSheet.x:Sorting
WorkBook.WorkSheet.c:worksheetoptions
WorkBook.WorkSheet.x:QueryTable


PivotTable.AutoFormatAlignment
PivotTable.AutoFormatBorder
PivotTable.AutoFormatFont
PivotTable.AutoFormatName
PivotTable.AutoFormatNumber
PivotTable.AutoFormatPattern
PivotTable.CacheDetails
PivotTable.CalculatedMember
PivotTable.ConnectionString
PivotTable.CubeField
PivotTable.DataAxisEmpty
PivotTable.DataMember
PivotTable.DataValueEditing
PivotTable.DefaultVersion
PivotTable.DisableDrillDown
PivotTable.DisableFieldDialog
PivotTable.DisableWizard
PivotTable.DisplayErrorString
PivotTable.DisplayFieldList
PivotTable.ErrorString
PivotTable.FieldListBottom
PivotTable.FieldListLeft
PivotTable.FieldListRight

PivotTable.FieldListTop
PivotTable.Formula
PivotTable.GrandTotalString
PivotTable.HasNoAutoFormat
PivotTable.Height
PivotTable.HideDropDowns
PivotTable.HideTotalsAnnotation
PivotTable.ImmediateItemsOnDrop
PivotTable.Invalid
PivotTable.Location
PivotTable.MaxHeight
PivotTable.MaxWidth
PivotTable.MemberExpand
PivotTable.MemberName
PivotTable.MergeLabels
PivotTable.Name
PivotTable.Name
PivotTable.NoAutoFit
PivotTable.NoAutoFormatWidth
PivotTable.NoColumnGrand
PivotTable.NoDisplayNullString
PivotTable.NoPreserveFormatting
PivotTable.NoPrintRepeatItems
PivotTable.NoRowGrand
PivotTable.NoViewCalculatedMembers
PivotTable.NullString
PivotTable.PageFieldOrder
PivotTable.PageFieldStyle
PivotTable.PageFieldWrapCount
PivotTable.ParentUniqueName
PivotTable.PivotAxis
PivotTable.PivotField
PivotTable.PivotView
PivotTable.PLExport
PivotTable.PLPivotField
PivotTable.PLTotal
PivotTable.PrintSetTitles
PivotTable.PTFormat
PivotTable.PTFormula
PivotTable.PTLineItems
PivotTable.PTSource
PivotTable.QTSource
PivotTable.Selection
PivotTable.Set
PivotTable.ShowCellBackgroundFromOLAP
PivotTable.ShowPageMultipleItemLabel
PivotTable.SmallGrid
PivotTable.SolveOrder
PivotTable.SourceHierarchy
PivotTable.SourceHierarchyLevel
PivotTable.SubtotalHiddenPageItems
PivotTable.TableStyle
PivotTable.Tag
PivotTable.VacatedStyle
PivotTable.VersionLastUpdate
PivotTable.VersionRefreshableMin
PivotTable.VersionUpdateableMin
PivotTable.VisualTotals
PivotTable.Width

								
To top