1 :Class sfExcel
   2 
   3 ⍝ Cover Class for using the Syncfusion XlsIO namespace.
   4 ⍝ Essential XlsIO is a 100% native .NET library that generates fully functional Microsoft Excel Spreadsheets
   5 ⍝ in native Excel format without depending on Microsoft Excel.
   6 ⍝ Essential XlsIO is a perfect solution for the users who need to read and write Microsoft Excel files.
   7 ⍝ It does not require MS Excel to be installed in the Report generation machine or server.
   8 ⍝ See individual comments of each methods for more info.
   9 
  10 ⍝ Typical usage:
  11 ⍝    xl←⎕NEW sfExcel    ⍝ Instanciate a New Workbook with the default amount of Worksheets (3)
  12 ⍝ or xl←⎕NEW sfExcel 1  ⍝ Instanciate a New Workbook with 1 Worksheet
  13 ⍝
  14 ⍝  'A1'      xl.SetValue (⊂'Hello World')   ⍝ Set the value of a single cell in A1 notation
  15 ⍝  'B2:K11'  xl.SetValue (10 10⍴⍳100)       ⍝ Set the value of a range in A1 notation
  16 ⍝  2 2 11 11 xl.SetValue (10 10⍴⍳100)       ⍝ Set the value of a range in RC notation
  17 ⍝
  18 ⍝   dt←xl.AplToDT 10 10⍴⍳100                ⍝ Get a .Net DataTable
  19 ⍝   (row col) xl.ImportDataTable dt         ⍝ To import a DataTable at position [row;col]
  20 ⍝
  21 ⍝  xl.GetValue 'C4'        ⍝ Get the value of a cell in A1 notation
  22 ⍝  xl.GetValue 4 3         ⍝ Get the value of a single cell in RC notation
  23 ⍝  xl.GetValue 'B2:K11'    ⍝ Get the value of a range in A1 notation
  24 ⍝  xl.GetValue 2 2 11 11   ⍝ Get the value of a range in RC notation
  25 ⍝
  26 ⍝  xl.LoadFile fileName    ⍝ Load a Workbook from file
  27 ⍝  xl.SaveAsXlsx fileName  ⍝ Save the Workbook to fileName as .xlsx format
  28 ⍝  xl.SaveAsPdf fileName   ⍝ Save the Workbood to fileName as .pdf format
  29 ⍝
  30 ⍝  xl.Dispose              ⍝ To dispose of the class
  31 ⍝
  32 ⍝  A range can be defined using:
  33 ⍝   A1 notation (ex.: 'D5:F8')
  34 ⍝   With Row and Column Index (RC notation) (ex.: 'D5:F8' is 5 4 8 6)
  35 ⍝   As a single positive number for the Column index base 1
  36 ⍝   As a single negative number for the Row index base 1
  37 ⍝
  38 ⍝  The methods .SetText and .SetNumber are faster than .SetValue and are preferred when possible.
  39 ⍝  The method .ImportDataTable is very fast for importing a large set of number and text.
  40 ⍝
  41 ⍝  Known limitations:
  42 ⍝   Shapes are not supported.
  43 
  44 ⍝ Methods in sfExcel:
  45 ⍝  AddWorksheet           - Add a new Worksheet.
  46 ⍝  AplToDT                - Create a DataTable from an Apl Array. Each column must be of the same type.
  47 ⍝  AutoFitColumns         - Adjust Width of Column to the Widest Value.
  48 ⍝  AutoFitRows            - Adjust Hight of Rows to the Highest Value.
  49 ⍝  ConvertA1toRC          - Convert from Excel A1 notation to Row and Column Index notation (RC) (base 1).
  50 ⍝  ConvertRCtoA1          - Convert from Row and Column Index notation to Excel A1 notation.
  51 ⍝  DeleteWorksheet        - Delete a Worksheet from Active Workbook.
  52 ⍝  Dispose                - Quit Excel and Dispose of the "XL" object.
  53 ⍝  DTtoApl                - Convert a .Net DataTable to APL.
  54 ⍝  ExportDataTable        - Export the spreadsheet data as a .Net data table.
  55 ⍝  FreezeRows             - Freeze Rows from the Top of WorkSheet.
  56 ⍝  GetActiveWorksheetName - Get the Active Sheet in Active Workbook.
  57 ⍝  GetDisplayText         - Gets the text that is displayed in the cell.
  58 ⍝  GetNumber              - Get data of Active WorkSheet. The data must be only numeric.
  59 ⍝  GetText                - Get data of Active WorkSheet. The data must be only characters.
  60 ⍝  GetValue               - Get data of Active WorkSheet. The data can be numeric or text.
  61 ⍝  GetWorksheetsNames     - Get All the Worksheets Names.
  62 ⍝  ImportDataTable        - Import a DataTable at [row;col] position in the Active Worksheet.
  63 ⍝  LastColumn             - Get the Last Column of a Worksheet.
  64 ⍝  LastRow                - Get the Last Row of a Worksheet.
  65 ⍝  LoadFile               - Load an Excel Workbook file.
  66 ⍝  Merge                  - Merge Text in Range.
  67 ⍝  OADateToTs             - ⎕TS from OA Date.
  68 ⍝  Print                  - Print using XlsIo (without Excel).
  69 ⍝  RangeDimension         - Get the dimension of a range (no of rows, no of columns).
  70 ⍝  SaveAsPdf              - Save the Current WorkSheet As fileName in .Pdf format.
  71 ⍝  SaveAsXls              - Save the Current WorkBook As fileName in .Xls format.
  72 ⍝  SaveAsXlsx             - Save the Current WorkBook As fileName in .Xlsx format.
  73 ⍝  SetActiveWorksheet     - Activate specific Worksheet in active workbook.
  74 ⍝  SetFontBold            - Set a Range Font in Bold.
  75 ⍝  SetFontColor           - Set a Range Font Color.
  76 ⍝  SetFontItalic          - Set a Range Font in Italic.
  77 ⍝  SetFontName            - Set a Range Font Name.
  78 ⍝  SetFontSize            - Set a Range Font Size in Points.
  79 ⍝  SetFormat              - Set a Range Format.
  80 ⍝  SetHAlignment          - Set Horizontal Alignment of Range.
  81 ⍝  SetNumber              - Set data of Active WorkSheet. The data must be numeric.
  82 ⍝  SetText                - Set data of Active WorkSheet. The data must be Text only.
  83 ⍝  SetVAlignment          - Set Vertical Alignment of Range.
  84 ⍝  SetValue               - Set data of Active WorkSheet.
  85 ⍝  SetWorksheetName       - Set the WorkSheet Name.
  86 ⍝  Show                   - Show the workbook in a window.
  87 ⍝  TsToOADate             - OA Date from ⎕TS.
  88 ⍝  UsedRange              - Get the Range Used by the Active Worksheet.
  89 ⍝  WrapText               - Set if Text in Range is Wrapped.
  90 
  91 ⍝ Version 1.0 November 2014, Pierre Gilbert
  92 ⍝ Version 1.1 November 2014, Typo corrections suggested by Daniel Baronnet
  93 ⍝ Version 1.2 November 2014, Typo corrections suggested by Daniel Baronnet
  94 
  95 
  96     ⎕ML←3 ⋄ ⎕IO←1
  97 
  98     :Field Private sfDir←'Syncfusion/4.5/'  ⍝ Location of the Syncfusion dll's
  99 
 100     :Field Public XL                        ⍝ Syncfusion.XlsIO.ExcelEngine to experiment with other methods
 101 
 102     :Field Public Win                       ⍝ Windows .Net object
 103 
 104       isString←{0 2∊⍨10|⎕DR ⍵               ⍝ Test to detect characters from numbers
 105       }
 106       
 107       rangeRect←{                           ⍝ Get position and size of range
 108           ⍵.IsBlank:0 0 0 0
 109           1+∊⍵.GetRectangles.(Y X Height Width)}
 110 
 111     ∇ Init0;sink;⎕USING
 112       :Access Public
 113       :Implements Constructor
 114      
 115      ⍝ Initialize a Syncfusion ExcelEngine object and creates a Workbook
 116      ⍝  with the default amount of Worksheets (typically 3).
 117       :Trap 0
 118           ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 119           XL←⎕NEW ExcelEngine
 120           sink←XL.Excel.Workbooks.Create ⍬
 121           InitOptions
 122       :End
 123     ∇
 124 
 125     ∇ Init noOfWorksheets;sink;⎕USING
 126       :Access Public
 127       :Implements Constructor
 128      
 129      ⍝ Initialize a Syncfusion ExcelEngine object and creates a Workbook
 130      ⍝ with the the specify number of Worksheets.
 131       :Trap 0
 132           ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 133           XL←⎕NEW ExcelEngine
 134           sink←XL.Excel.Workbooks.Create noOfWorksheets
 135           InitOptions
 136       :End
 137     ∇
 138 
 139     ∇ InitOptions
 140      ⍝ Continue the Initialization with the following options:
 141      
 142      ⍝ When using the Value2 property, set to 0 if you are sure that the given
 143      ⍝ value is not of DateTime data type which improves time performance (default is 1).
 144       XL.Excel.ActiveWorkbook.DetectDateTimeInValue←0
 145      
 146      ⍝ Indicates if all values in the workbook are preserved as strings.(default is 0)
 147      ⍝ XL.Excel.(⌷Worksheets).IsStringsPreserved←1
 148     ∇
 149 
 150     ∇ AddWorksheet sheetName;sink
 151       :Access Public
 152     ⍝ Add a new Worksheet.
 153     ⍝ sheetName = Name of the new sheet
 154      
 155       sink←XL.Excel.ActiveWorkbook.Worksheets.Create(⊂sheetName)
 156     ∇
 157 
 158     ∇ dt←{columnNames}AplToDT aplArray;columnType;index;tableName;⎕USING
 159       :Access Public
 160     ⍝ Create a DataTable from an Apl Array. Each column must be of the same type.
 161     ⍝ aplArray    = An APL array of Numbers and Characters.
 162     ⍝ columnNames = Column names for the DataTable. Works best with 2 characters per name.
 163     ⍝ dt          = DataTable
 164     ⍝ Note: For dates, use a number representing the OA date from method TsToOADate.
 165      
 166     ⍝ If a Vector, set as a one row matrix:
 167       :If 1=⍴⍴aplArray
 168           aplArray←(1,⍴aplArray)⍴aplArray
 169       :EndIf
 170      
 171     ⍝ Check if aplArray is a 2 dimensional array:
 172       :If 2≠⍴⍴aplArray
 173           ⎕←'sfExcel.AplToDT Error: Argument must be of rank equal or smaller than 2'
 174           →0
 175       :End
 176      
 177     ⍝ Check if columnNames is properly formed:
 178       :If 0=⎕NC'columnNames'            ⍝ There is no columnNames
 179       :OrIf (⍴columnNames)≠1↓⍴aplArray  ⍝ Wrong Shape
 180       :OrIf 1∊0=1↑¨0⍴¨columnNames       ⍝ Not characters
 181       :OrIf 0∊≡¨columnNames             ⍝ Wrong Depth
 182      
 183         ⍝ Generate the Column Names as 'C1' 'C2', etc.:
 184           columnNames←'C',¨⍕¨⍳1↓⍴aplArray
 185      
 186       :Else
 187         ⍝ Do nothing the column names are properly formed.
 188      
 189       :End
 190      
 191       tableName←'Data'  ⍝ Default TableName
 192      
 193     ⍝ Default value in case of error:
 194       ⎕USING←'System' 'System.Data,System.Data.dll'
 195       dt←⎕NEW DataTable(⊂tableName)
 196      
 197       :Trap 0
 198         ⍝ Determine the Type of each column:
 199           columnType←''
 200           :For index :In ⍳1↓⍴aplArray
 201               :Select ⎕DR aplArray[;index]
 202               :CaseList 80 160 320 326
 203                   columnType,←String
 204               :Else
 205                   columnType,←Double  ⍝ was Decimal
 206               :EndSelect
 207           :EndFor
 208      
 209         ⍝ Set the Column's Types and Names of the DataTable:
 210           {}columnNames{dt.Columns.Add ⍺ ⍵}¨columnType
 211      
 212         ⍝ Fill the DataTable:
 213           2010⌶dt aplArray
 214      
 215       :EndTrap
 216     ∇
 217 
 218     ∇ AutoFitColumns range
 219       :Access Public
 220     ⍝ Adjust Width of Columns to the Widest Value.
 221     ⍝ Does not work well with Wrapped Text.
 222      
 223       (ParseRange range).AutofitColumns
 224     ∇
 225 
 226     ∇ AutoFitRows range
 227       :Access Public
 228     ⍝ Adjust Height of Rows to the Highest Value.
 229     ⍝ Does not work well with Wrapped Text.
 230      
 231       (ParseRange range).AutofitRows
 232     ∇
 233 
 234     ∇ r←ConvertA1toRC range;height;width;x;y
 235       :Access Public
 236     ⍝ Convert from Excel A1 notation to Row and Column Index notation (RC) (base 1).
 237     ⍝ 'B5'     → 5 2
 238     ⍝ 'D2:E10' → 2 4 10 5
 239      
 240       (y x height width)←∊XL.Excel.ActiveWorkbook.ActiveSheet.Range[⊂range].GetRectangles.(Y X Height Width)
 241      
 242       :If 0 0≡height width
 243         ⍝ Single cell
 244           r←1+y,x
 245       :Else
 246         ⍝ Range
 247           r←1+y,x,(height+y),(width+x)
 248       :End
 249     ∇
 250 
 251     ∇ r←ConvertRCtoA1 range;col;lastCol;lastRow;row
 252       :Access Public
 253     ⍝ Convert from Row and Column Index notation to Excel A1 notation
 254     ⍝ 5 2      → 'B5'
 255     ⍝ 2 4 10 5 → 'D2:E10'
 256      
 257       :If 2=⍴range  ⍝ Single cell
 258           (row col)←range
 259           r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col].AddressLocal
 260      
 261       :ElseIf 4=⍴range  ⍝ Range
 262           (row col lastRow lastCol)←range
 263           r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol].AddressLocal
 264       :End
 265     ∇
 266 
 267     ∇ DeleteWorksheet sheet;item
 268       :Access Public
 269     ⍝ Delete a Worksheet from Active Workbook.
 270     ⍝ sheet = SheetName or Index (base 1) of the Worksheet
 271      
 272       :Select isString sheet
 273       :Case 1 ⍝ String
 274           :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 275               XL.Excel.ActiveWorkbook.Worksheets[⊂sheet].Remove
 276           :Else
 277               ⎕←'sfExcel.DeleteWorksheet Error: Worksheet Name ',sheet,' does not exist'
 278           :End
 279       :CaseList 0 ⍝ Numeric
 280           :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count
 281               XL.Excel.ActiveWorkbook.Worksheets[sheet+1].Remove
 282           :Else
 283               ⎕←'sfExcel.DeleteWorksheet Error: Worksheet index ',sheet,' does not exist'
 284           :End
 285       :EndSelect
 286     ∇
 287 
 288     ∇ Dispose
 289       :Access Public
 290     ⍝ Quit Excel and Dispose of the "XL" object.
 291      
 292       :Trap 0
 293           XL.Excel.Workbooks.Close
 294           XL.Dispose
 295           XL←⎕NULL
 296           ⎕EX'XL'
 297       :EndTrap
 298     ∇
 299 
 300     ∇ apl←DTtoApl dt
 301       :Access Public
 302     ⍝ Convert a .Net DataTable to APL
 303      
 304       :If 9≠⎕NC'dt'
 305       :OrIf 'System.Data.DataTable'≢dt.GetType.ToString
 306           ⎕←'sfExcel.DTtoApl Error: The argument is not a DataTable Object !'
 307       :EndIf
 308      
 309       apl←2011⌶dt
 310     ∇
 311 
 312     ∇ dt←ExportDataTable range;options
 313       :Access Public
 314      ⍝ Export the spreadsheet data as a .Net data table.
 315      
 316      ⍝ None                    0 No datatable exports flags.
 317      ⍝ ColumnNames             1 Represents the ColumnNames datatable export flag.
 318      ⍝ ComputedFormulaValues   2 Represents the ComputedFormulaValues datatable export flag.
 319      ⍝ DetectColumnTypes       4 Indicates that XlsIO should try to detect column types.
 320      ⍝ DefaultStyleColumnTypes 8 When DetectColumnTypes is set and this flag is set too,it means that default column style must be used to detect style,if this flag is not set,but DetectColumnTypes is set,then first cell in the column will be used to detect column type.
 321      ⍝ PreserveOleDate         16 Indicates whether to preserve Ole date(double numbers)instead of date-time values.
 322      
 323       options←2+4
 324       range←ParseRange range
 325       dt←XL.Excel.ActiveWorkbook.ActiveSheet.ExportDataTable(range options)
 326     ∇
 327 
 328     ∇ FreezeRows noRows
 329       :Access Public
 330     ⍝ Freeze Rows from the Top of WorkSheet.
 331     ⍝ noRows = number of rows to freeze from top (base 1)
 332      
 333       XL.Excel.ActiveWorkbook.ActiveSheet.Range[1+noRows;1].FreezePanes
 334     ∇
 335 
 336     ∇ r←GetActiveWorksheetName
 337       :Access Public
 338     ⍝ Get the Active Sheet in Active Workbook.
 339     ⍝ r = active sheet name
 340      
 341       r←XL.Excel.ActiveWorkbook.ActiveSheet.Name
 342     ∇
 343 
 344     ∇ array←GetDisplayText range
 345       :Access Public
 346     ⍝ Gets the text that is displayed in the cell. This is a read-only property, which returns
 347     ⍝ a cell value that is displayed after the number format application.
 348     ⍝ array = Array same Size as Range
 349      
 350       range←ParseRange range
 351       array←(¯2↑rangeRect range)⍴range.Cells.DisplayText
 352     ∇
 353 
 354     ∇ array←GetNumber range
 355       :Access Public
 356     ⍝ Get data of Active WorkSheet. The data must be only numeric.
 357     ⍝ array = Array same Size as Range (of Number only)
 358      
 359       range←ParseRange range
 360       array←(¯2↑rangeRect range)⍴range.Cells.Number
 361     ∇
 362 
 363     ∇ array←GetText range;col;lastCol;lastRow;row
 364       :Access Public
 365     ⍝ Get data of Active WorkSheet. The data must be only characters.
 366     ⍝ array = Array same Size as Range (of Text only)
 367      
 368       range←ParseRange range
 369       array←(¯2↑rangeRect range)⍴range.Cells.Text
 370     ∇
 371 
 372     ∇ array←GetValue range;col;lastCol;lastRow;row;valueConverter
 373       :Access Public
 374     ⍝ Get data of Active WorkSheet. The data can be numeric or text.
 375     ⍝ array = Array same Size as Range (Text or Number)
 376      
 377       valueConverter←{
 378           ⍵.HasNumber:⍵.Number
 379           ⍵.HasString:⍵.Text
 380           ⍵.HasFormulaNumberValue:⍵.FormulaNumberValue
 381           ⍵.HasFormulaStringValue:⍵.FormulaStringValue
 382           ⍵.IsBlank:⎕NULL              ⍝ You can use ⎕NULL, 0 or ⊂'' as returned value when cell is blank
 383           ⍵.HasDateTime:⍵.Number
 384           ⍵.HasBoolean:⍵.Number
 385           ⍵.HasRichText:⍵.RichText
 386           ⍵.Value2
 387       }
 388      
 389     ⍝ ↓↓↓ Syncfusion .Value2 is valid but does not work like Excel .Value2
 390     ⍝ array←(range←ParseRange range).Cells.Value2
 391      
 392       range←ParseRange range
 393       array←(¯2↑rangeRect range)⍴valueConverter¨range.Cells
 394     ∇
 395 
 396     ∇ r←GetWorksheetsNames
 397       :Access Public
 398     ⍝ Get All the Worksheets Names.
 399     ⍝ r = Worksheets Names
 400      
 401       r←XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 402     ∇
 403 
 404     ∇ range ImportDataTable dt;col;row
 405       :Access Public
 406     ⍝ Import a DataTable at [row;col] position in the Active Worksheet.
 407     ⍝ row col = Row and Column (base 1) of the upper left corner of the DataTable
 408      
 409       (row col)←range
 410       {}XL.Excel.ActiveWorkbook.ActiveSheet.ImportDataTable dt 0 row col 1  ⍝ DataTable, KeepHeader, Row, Col, PreserveType
 411     ∇
 412 
 413     ∇ r←LastColumn
 414       :Access Public
 415     ⍝ Get the Last Column of a Worksheet.
 416     ⍝ r = last column (base 1)
 417      
 418       r←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn
 419     ∇
 420 
 421     ∇ r←LastRow
 422       :Access Public
 423     ⍝ Get the Last Row of a Worksheet.
 424     ⍝ r = last row (base 1)
 425      
 426       r←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow
 427     ∇
 428 
 429     ∇ LoadFile fileName;sink;⎕USING
 430       :Access Public
 431     ⍝ Load an Excel Workbook file.
 432     ⍝ fileName = filename with full path and extension
 433      
 434       ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 435       sink←XL.Excel.Workbooks.Open((⊂fileName),ExcelOpenType.Automatic)
 436     ∇
 437 
 438     ∇ Merge range
 439       :Access Public
 440     ⍝ Merge Text in Range.
 441      
 442       (ParseRange range).Merge
 443     ∇
 444 
 445     ∇ ts←OADateToTs oaDate;date;⎕USING
 446       :Access Public
 447     ⍝ Convert OA Date to ⎕TS
 448     ⍝ oaDate = .Net OADate
 449     ⍝ ts = ⎕TS
 450     ⍝ Works with TsToOADate
 451      
 452       ⎕USING←'System,mscorlib.dll'
 453       date←DateTime.FromOADate oaDate
 454       ts←date.(Year Month Day Hour Minute Second Millisecond)
 455     ∇
 456 
 457     ∇ r←ParseRange range;col;lastCol;lastRow;row
 458       :Access Public
 459     ⍝ Subroutine used to parse a range
 460     ⍝ range = Range in A1 notation (ex.: 'D5:F8' or 'B2')
 461     ⍝ range = Range with Row and Column Index (ex.: 'D5:F8' is 5 4 8 6)
 462     ⍝ range = Range as single positive number is the Column index base 1
 463     ⍝ range = Range as single negative number is the Row index base 1
 464      
 465       :If 0=isString range  ⍝ Is range a number ?
 466         ⍝ range is a number
 467           :If 1=⍴,range
 468               :If range>0
 469                 ⍝ Positive Number = Single Column as number.
 470                   r←XL.Excel.ActiveWorkbook.ActiveSheet.Columns[range]
 471               :Else
 472                 ⍝ Negative Number = Single Row as number.
 473                   r←XL.Excel.ActiveWorkbook.ActiveSheet.Rows[|range]
 474               :End
 475      
 476           :ElseIf 2=⍴range ⍝ RC notation: Single cell
 477               (row col)←range
 478               r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col]
 479      
 480           :ElseIf 4=⍴range  ⍝ RC notation: Range
 481               (row col lastRow lastCol)←range
 482               r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol]
 483           :EndIf
 484      
 485       :Else  ⍝ range is Text
 486         ⍝ A1 notation: range or single cell
 487           r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[⊂range]
 488      
 489       :End
 490     ∇
 491 
 492     ∇ PrepareWindow;xaml;⎕USING
 493       :Access Private
 494      ⍝ Generic Window to display a SpreadsheetControl.
 495      
 496       xaml←'<sf:RibbonWindow'
 497       xaml,←'   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"'
 498       xaml,←'   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"'
 499       xaml,←'   xmlns:sf="http://schemas.syncfusion.com/wpf"'
 500       xaml,←'   Title="SpreadSheet Viewer"'
 501       xaml,←'   x:Name="window"'
 502       xaml,←'   sf:SkinStorage.VisualStyle="Office2010Blue"'
 503       xaml,←'   Width="800" Height="600">'
 504      
 505       xaml,←'   <Grid x:Name="LayoutRoot" >'
 506       xaml,←'       <Grid.RowDefinitions>'
 507       xaml,←'           <RowDefinition Height="Auto"/>'
 508       xaml,←'           <RowDefinition Height="*"/>'
 509       xaml,←'       </Grid.RowDefinitions>'
 510       xaml,←'   <sf:SpreadsheetRibbon x:Name="ribbon" DataContext="{Binding ElementName=spreadsheet}" sf:SkinStorage.VisualStyle="Office2010Blue"/>'
 511       xaml,←'   <sf:SpreadsheetControl x:Name="spreadsheet" FormulaBarVisibility="Visible" Grid.Row="1" sf:SkinStorage.VisualStyle="Office2010Blue"/>'
 512       xaml,←'   </Grid>'
 513       xaml,←'</sf:RibbonWindow>'
 514      
 515       ⎕USING←'Syncfusion.Windows.Tools.Controls,',sfDir,'Syncfusion.Tools.Wpf.dll'
 516       ⎕USING,←⊂'Syncfusion.Windows.Controls.Spreadsheet,',sfDir,'Syncfusion.Spreadsheet.Wpf.dll'
 517       ⎕USING,←⊂'Syncfusion.Windows.Shared,',sfDir,'Syncfusion.Shared.Wpf.dll'
 518      
 519       {}RibbonWindow SpreadsheetRibbon SpreadsheetControl SkinStorage
 520      
 521       ⎕USING,←⊂'System.Windows.Markup,WPF/PresentationFramework.dll'
 522       Win←XamlReader.Parse(⊂xaml)
 523      
 524       Win.ribbon←Win.FindName(⊂'ribbon')
 525       Win.spreadsheet←Win.FindName(⊂'spreadsheet')
 526       Win.window←Win.FindName(⊂'window')
 527     ∇
 528 
 529     ∇ Print sheet;GC;gridModel;SV;Win;⎕USING
 530       :Access Public
 531      ⍝ Print the worksheet selected by the sheetIndex
 532      ⍝ sheet = SheetName or Index (base 1) of the Worksheet
 533      
 534       :Select isString sheet
 535       :Case 1 ⍝ String
 536           :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 537               sheet←(XL.Excel.ActiveWorkbook.(⌷Worksheets).Name)⍳(⊂sheet)
 538           :Else
 539               ⎕←'sfExcel.Print Error: Worksheet Name ',sheet,' does not exist'
 540           :End
 541       :CaseList 0 ⍝ Numeric
 542           :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count
 543               ⍝ Do nothing. sheet is a valid sheet number.
 544           :Else
 545               ⎕←'sfExcel.Print Error: Worksheet index ',sheet,' does not exist'
 546           :End
 547       :EndSelect
 548      
 549       ⎕USING←'Syncfusion.Windows.Controls.Grid.Converter,',sfDir,'Syncfusion.Spreadsheet.Wpf.dll'
 550       gridModel←sheet⊃ExcelGridModelImportExtensions.ImportFromExcel(XL.Excel.ActiveWorkbook)
 551      
 552       ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll'
 553       GC←⎕NEW GridControl
 554       {}GC.Model←gridModel
 555      
 556      ⍝ ↓↓↓ Uncomment if you want to have a window before printing.
 557 ⍝     ⎕USING←'System.Windows.Controls,WPF/PresentationFramework.dll'
 558 ⍝     SV←⎕NEW ScrollViewer
 559 ⍝     SV.CanContentScroll←1
 560 ⍝     SV.HorizontalScrollBarVisibility←SV.HorizontalScrollBarVisibility.Auto
 561 ⍝     SV.VerticalScrollBarVisibility←SV.VerticalScrollBarVisibility.Auto
 562 ⍝     SV.Content←GC
 563 ⍝
 564 ⍝     ⎕USING←'System.Windows,WPF/PresentationFramework.dll'
 565 ⍝     Win←⎕NEW Window
 566 ⍝     Win.Title←'Spreadsheet Viewer'
 567 ⍝     Win.Content←SV
 568 ⍝     Win.Show
 569      
 570       ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll'
 571       GridPrintExtensions.Print GC
 572     ∇
 573 
 574     ∇ r←RangeDimension range
 575       :Access Public
 576     ⍝ Get the dimension of a range (no of rows, no of columns).
 577      
 578     ⍝ ↓↓↓ Working but is Slower for large range
 579     ⍝ r←∊⍴¨(ParseRange range).(Rows Columns)
 580      
 581       r←¯2↑rangeRect(ParseRange range)
 582     ∇
 583       
 584     ∇ {sheet}SaveAsHtml fileName;options
 585       :Access Public
 586     ⍝ Save the Current WorkSheet As fileName in .Html format.
 587     ⍝ fileName = filename with full path and extension (.Html)
 588     ⍝ sheet = Sheet name or index (base 1)
 589     ⍝ Note: If sheet absent willl use the active sheet.
 590      
 591       :If 0=⎕NC'sheet'
 592         ⍝ sheet is absent used the Active one
 593           sheet←XL.Excel.ActiveWorkbook.ActiveSheet
 594      
 595       :ElseIf isString sheet
 596         ⍝ sheet is characters, use as sheet name
 597           sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet]
 598      
 599       :Else
 600         ⍝ sheet is number, use as index base 1
 601           sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet]
 602       :End
 603      
 604       ⎕USING←'Syncfusion.XlsIO.Implementation,',sfDir,'Syncfusion.XlsIO.Base.dll'
 605       options←⎕NEW HtmlSaveOptions
 606       options.TextMode←HtmlSaveOptions.GetText.DisplayText
 607       sheet.SaveAsHtml(fileName options)
 608     ∇
 609       
 610     ∇ {sheet}SaveAsPdf fileName;converter;pdfDoc;settings;⎕USING
 611       :Access Public
 612     ⍝ Save the Current WorkSheet As fileName in .Pdf format.
 613     ⍝ fileName = filename with full path and extension (.pdf)
 614     ⍝ sheet = Sheet name or index (base 1)
 615     ⍝ Note: If sheet absent willl use the active sheet.
 616      
 617       :If 0=⎕NC'sheet'
 618         ⍝ sheet is absent used the Active one
 619           sheet←XL.Excel.ActiveWorkbook.ActiveSheet
 620      
 621       :ElseIf isString sheet
 622         ⍝ sheet is characters, use as sheet name
 623           sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet]
 624      
 625       :Else
 626         ⍝ sheet is number, use as index base 1
 627           sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet]
 628       :End
 629      
 630      ⍝ Set the PageSetup here if required:
 631      ⍝ sheet.PageSetup.PrintArea
 632      
 633       ⎕USING←'Syncfusion.ExcelToPdfConverter,',sfDir,'Syncfusion.ExcelToPDFConverter.Base.dll'
 634      
 635      ⍝ Open the Excel document you want to convert
 636       converter←⎕NEW ExcelToPdfConverter(sheet)
 637      
 638      ⍝ Initialize the ExcelToPdfconverterSettings
 639       settings←⎕NEW ExcelToPdfConverterSettings
 640      
 641      ⍝ Initialize the PDF document
 642       ⎕USING←'Syncfusion.Pdf,',sfDir,'Syncfusion.Pdf.Base.dll'
 643       pdfDoc←⎕NEW PdfDocument
 644      
 645      ⍝ Set the Layout Options for the output Pdf page.
 646      ⍝ settings.LayoutOptions←settings.LayoutOptions.FitSheetOnOnePage
 647      
 648      ⍝ Assign the PDF document to the TemplateDocument property of ExcelToPdfConverterSettings
 649       settings.TemplateDocument←pdfDoc
 650       settings.DisplayGridLines←settings.DisplayGridLines.Invisible
 651      
 652      ⍝ Convert the Excel document to PDF document
 653       pdfDoc←converter.Convert(settings)
 654      
 655      ⍝ Save the PDF file
 656       pdfDoc.Save(⊂fileName)
 657     ∇
 658 
 659     ∇ SaveAsXls fileName
 660       :Access Public
 661     ⍝ Save the Current WorkBook As fileName in .Xls format.
 662     ⍝ fileName = filename with full path and extension (.xls)
 663      
 664       XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel97to2003
 665       XL.Excel.ActiveWorkbook.SaveAs(⊂fileName)
 666     ∇
 667     
 668     ∇ SaveAsXlsx fileName
 669       :Access Public
 670     ⍝ Save the Current WorkBook As fileName in .Xlsx format
 671     ⍝ fileName = filename with full path and extension (.xlsx)
 672      
 673       XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel2007
 674       XL.Excel.ActiveWorkbook.SaveAs(⊂fileName)
 675     ∇
 676 
 677     ∇ SetActiveWorksheet sheet
 678       :Access Public
 679     ⍝ Activate specific Worksheet in active workbook.
 680     ⍝ sheet = Index of Sheet (Base 1) or name of Sheet (Text)
 681      
 682       :Select isString sheet
 683       :Case 1 ⍝ String
 684           :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 685               XL.Excel.ActiveWorkbook.Worksheets[⊂sheet].Activate
 686           :Else
 687               ⎕←'sfExcel.SetActiveWorksheet: Worksheet Name ',sheet,' does not exist'
 688           :End
 689       :CaseList 0 ⍝ Numeric
 690           :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count
 691               XL.Excel.ActiveWorkbook.Worksheets[sheet+1].Activate
 692           :Else
 693               ⎕←'sfExcel.SetActiveWorksheet: Worksheet index ',sheet,' does not exist'
 694           :End
 695       :EndSelect
 696     ∇
 697     
 698     ∇ range SetBackgroundColor fontColor;paletteIndex;⎕USING
 699       :Access Public
 700     ⍝ Set a Range Font Color.
 701     ⍝ fontColor = The color of the font
 702      
 703       ⎕USING←('Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll')'System.Drawing,System.Drawing.dll'
 704      
 705     ⍝ ↓↓↓ Will work only with the exact color's name of the palette
 706     ⍝ paletteIndex←⍎'ExcelKnownColors.',fontColor
 707      
 708       :If isString fontColor
 709           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromName⊂fontColor
 710           (ParseRange range).CellStyle.FillBackground←paletteIndex
 711      
 712       :ElseIf ~isString fontColor
 713           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromArgb 255,3↑fontColor
 714           (ParseRange range).CellStyle.FillBackground←paletteIndex
 715      
 716       :End
 717     ∇
 718 
 719     ∇ range SetFontBold bit
 720       :Access Public
 721     ⍝ Set a Range Font in Bold.
 722     ⍝ bit = 1 or 0
 723      
 724       (ParseRange range).CellStyle.Font.Bold←bit
 725     ∇
 726 
 727     ∇ range SetFontColor fontColor;paletteIndex;⎕USING
 728       :Access Public
 729     ⍝ Set a Range Font Color.
 730     ⍝ fontColor = The color of the font
 731      
 732       ⎕USING←('Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll')'System.Drawing,System.Drawing.dll'
 733      
 734     ⍝ ↓↓↓ Will work only with the exact color's name of the palette
 735     ⍝ paletteIndex←⍎'ExcelKnownColors.',fontColor
 736      
 737       :If isString fontColor
 738           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromName⊂fontColor
 739           (ParseRange range).CellStyle.Font.Color←paletteIndex
 740      
 741       :Else
 742           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromArgb 255,3↑fontColor
 743           (ParseRange range).CellStyle.Font.Color←paletteIndex
 744      
 745       :End
 746     ∇
 747 
 748     ∇ range SetFontItalic bit
 749       :Access Public
 750     ⍝ Set a Range Font in Italic.
 751     ⍝ bit = 1 or 0
 752      
 753       (ParseRange range).CellStyle.Font.Italic←bit
 754     ∇
 755 
 756     ∇ range SetFontName fontName
 757       :Access Public
 758     ⍝ Set a Range Font Name.
 759     ⍝ fontName = name of the font
 760      
 761       (ParseRange range).CellStyle.Font.FontName←fontName
 762     ∇
 763 
 764     ∇ range SetFontSize fontSize
 765       :Access Public
 766     ⍝ Set a Range Font Size in Points.
 767     ⍝ size = Size of Font in Points
 768      
 769       (ParseRange range).CellStyle.Font.Size←fontSize
 770     ∇
 771 
 772     ∇ range SetFormat format
 773       :Access Public
 774     ⍝ Set a Range Format.
 775     ⍝ format = Format (ex.: 'yy/m/d h:mm', '0.00')
 776      
 777       (ParseRange range).NumberFormat←(⊂format)
 778     ∇
 779 
 780     ∇ range SetHAlignment alignment;⎕USING
 781       :Access Public
 782     ⍝ Set Horizontal Alignment of Range.
 783     ⍝ alignment = Alignment (Center, CenterAcrossSelection, Distributed, Fill,General, Justify, Left, Right)
 784      
 785       ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 786       alignment←⍎'ExcelHAlign.HAlign',alignment
 787      
 788       (ParseRange range).HorizontalAlignment←alignment
 789     ∇
 790 
 791     ∇ range SetNumber array;col;lastCol;lastRow;row
 792       :Access Public
 793     ⍝ Set data of Active WorkSheet. The data must be numeric.
 794     ⍝ array = Array same Size as Range (Number only)
 795      
 796       (ParseRange range).Cells.Number←,array
 797     ∇
 798 
 799     ∇ range SetText array;col;lastCol;lastRow;row
 800       :Access Public
 801     ⍝ Set data of Active WorkSheet. The data must be Text only.
 802     ⍝ array = Array must be the same Dimension as Range (Text only)
 803      
 804       (ParseRange range).Cells.Text←,array
 805     ∇
 806 
 807     ∇ range SetVAlignment alignment;⎕USING
 808       :Access Public
 809     ⍝ Set Vertical Alignment of Range.
 810     ⍝ alignment = Alignment (Bottom, Center, Distributed, Justify, Top)
 811      
 812       ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 813       alignment←⍎'ExcelVAlign.VAlign',alignment
 814      
 815       (ParseRange range).VerticalAlignment←alignment
 816     ∇
 817 
 818     ∇ range SetValue array;col;lastCol;lastRow;row
 819       :Access Public
 820     ⍝ Set data of Active WorkSheet.
 821     ⍝ array = Array same Size as Range (Text or Number)
 822      
 823       (ParseRange range).Cells.Value2←,array
 824     ∇
 825 
 826     ∇ index SetWorksheetName sheetName
 827       :Access Public
 828     ⍝ Set the WorkSheet Name.
 829     ⍝ index =     Index of Worksheet (Base 1)
 830     ⍝ sheetName = Name of Worksheet
 831      
 832       XL.Excel.ActiveWorkbook.Worksheets[index+1].Name←(⊂sheetName)
 833     ∇
 834     
 835     ∇ Show
 836       :Access Public
 837     ⍝ Show the workbook in a window.
 838     ⍝ The changes made in the spreadsheet are not pass into the workspace.
 839     ⍝ This is a one-way, one-time interaction.
 840     ⍝ The method can only be call one time (don't know why).
 841      
 842     ⍝ Generic window to display the SpreadsheetControl
 843       PrepareWindow
 844      
 845     ⍝ Import the current Workbook into the SpreadsheetControl
 846       {}Win.spreadsheet.ImportFromExcel(XL.Excel.ActiveWorkbook)
 847      
 848     ⍝ Show the Spreadsheet
 849       Win.Show
 850     ∇
 851     
 852     ∇ ShowExcelFile fileName
 853       :Access Public
 854     ⍝ To View only an Excel file.
 855      
 856     ⍝ Generic window to display the SpreadsheetControl
 857       PrepareWindow
 858      
 859     ⍝ Import the current Workbook into the SpreadsheetControl
 860       {}Win.spreadsheet.ImportFromExcel(⊂fileName)
 861      
 862     ⍝ Show the Spreadsheet
 863       Win.Show
 864     ∇
 865 
 866     ∇ r←TsToOADate ts;⎕USING
 867       :Access Public
 868      ⍝ OA Date from ⎕TS.
 869      ⍝ ts = ⎕TS
 870      ⍝ r = .Net OADate
 871      ⍝ Note: Works with OADateToTs
 872      
 873       ⎕USING←'System,mscorlib.dll'
 874       r←(⎕NEW DateTime ts).ToOADate
 875     ∇
 876 
 877     ∇ r←UsedRange
 878       :Access Public
 879     ⍝ Get the Range Used by the Active Worksheet.
 880     ⍝ r = Range in RC notation (ex.: 'B1:B12' is 1 2 12 2)
 881     ⍝ r = 0 0 0 0 if Worksheet is empty
 882      
 883       r←rangeRect XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange
 884     ∇
 885     
 886     ∇ r←UsedRangeDimension;range
 887       :Access Public
 888     ⍝ Get the Dimension of the Used Range Used by the Active Worksheet.
 889      
 890       r←¯2↑rangeRect XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange
 891     ∇
 892 
 893     ∇ range WrapText bit
 894       :Access Public
 895     ⍝ Set if Text in Range is Wrapped.
 896     ⍝ bit = 1 or 0
 897      
 898       (ParseRange range).WrapText←bit
 899     ∇
 900 
 901 :EndClass