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 methods .ImportDataTable/.ExportDataTable are very fast for a large set of number and text.
  40 ⍝
  41 ⍝  Known limitations:
  42 ⍝   Shapes are not supported.
  43 
  44 ⍝ Methods in sfExcel:
  45 ⍝  AddAfterLastColumn     - Add a range after the last column.
  46 ⍝  AddAfterLastRow        - Add a range after the last row.
  47 ⍝  AddWorksheet           - Add a new Worksheet.
  48 ⍝  AplToDT                - Create a DataTable from an Apl Array. Each column must be of the same type.
  49 ⍝  AutoFitColumns         - Adjust Width of Column to the Widest Value.
  50 ⍝  AutoFitRows            - Adjust Hight of Rows to the Highest Value.
  51 ⍝  ConvertA1toRC          - Convert from Excel A1 notation to Row and Column Index notation (RC) (base 1).
  52 ⍝  ConvertRCtoA1          - Convert from Row and Column Index notation to Excel A1 notation.
  53 ⍝  DeleteWorksheet        - Delete a Worksheet from Active Workbook.
  54 ⍝  Dispose                - Quit Excel and Dispose of the "XL" object.
  55 ⍝  DTtoApl                - Convert a .Net DataTable to APL.
  56 ⍝  ExportDataSet          - Export the Workbook as a DataSet (collection of DataTables).
  57 ⍝  ExportDataTable        - Export the spreadsheet data as a DataTable.
  58 ⍝  FreezeRows             - Freeze Rows from the Top of WorkSheet.
  59 ⍝  GetActiveWorksheetName - Get the Active Sheet in Active Workbook.
  60 ⍝  GetDisplayText         - Gets the text that is displayed in the cell.
  61 ⍝  GetNumber              - Get data of Active WorkSheet. The data must be only numeric.
  62 ⍝  GetText                - Get data of Active WorkSheet. The data must be only characters.
  63 ⍝  GetValue               - Get data of Active WorkSheet. The data can be numeric or text.
  64 ⍝  GetWorksheetsNames     - Get All the Worksheets Names.
  65 ⍝  ImportDataTable        - Import a DataTable at [row;col] position in the Active Worksheet.
  66 ⍝  LastColumn             - Get the Last Column of a Worksheet.
  67 ⍝  LastRow                - Get the Last Row of a Worksheet.
  68 ⍝  LoadFile               - Load an Excel Workbook file.
  69 ⍝  LoadFromStream         - Load an Excel Workbook from a Stream obtained by the method 'SaveAsStream'
  70 ⍝  Merge                  - Merge Text in Range.
  71 ⍝  OADateToTs             - ⎕TS from OA Date.
  72 ⍝  Print                  - Print using XlsIo (without Excel).
  73 ⍝  RangeDimension         - Get the dimension of a range (no of rows, no of columns).
  74 ⍝  SaveAsCSV              - Save to file the specify worksheet with the CSV format.
  75 ⍝  SaveAsHtml             - Save to file the specify worksheet with the HTML format.
  76 ⍝  SaveAsPdf              - Save the Current WorkSheet As fileName in .Pdf format.
  77 ⍝  SaveAsStream           - Save the Current WorkBook As a Stream in .Xlsx format.
  78 ⍝  SaveAsXls              - Save the Current WorkBook As fileName in .Xls format.
  79 ⍝  SaveAsXlsx             - Save the Current WorkBook As fileName in .Xlsx format.
  80 ⍝  SetActiveWorksheet     - Activate specific Worksheet in active workbook.
  81 ⍝  SetBackgroundColor     - Set a Range Font Background Color.
  82 ⍝  SetFontBold            - Set a Range Font in Bold.
  83 ⍝  SetFontColor           - Set a Range Font Color.
  84 ⍝  SetFontItalic          - Set a Range Font in Italic.
  85 ⍝  SetFontName            - Set a Range Font Name.
  86 ⍝  SetFontSize            - Set a Range Font Size in Points.
  87 ⍝  SetFormat              - Set a Range Format.
  88 ⍝  SetHAlignment          - Set Horizontal Alignment of Range.
  89 ⍝  SetNumber              - Set data of Active WorkSheet. The data must be numeric.
  90 ⍝  SetText                - Set data of Active WorkSheet. The data must be Text only.
  91 ⍝  SetVAlignment          - Set Vertical Alignment of Range.
  92 ⍝  SetValue               - Set data of Active WorkSheet.
  93 ⍝  SetWorksheetName       - Set the WorkSheet Name.
  94 ⍝  Show                   - Show the workbook in a window.
  95 ⍝  ShowExcelFile          - To View only an Excel file.
  96 ⍝  TsToOADate             - OA Date from ⎕TS.
  97 ⍝  UsedRange              - Get the Range Used by the Active Worksheet.
  98 ⍝  UsedRangeDimension     - Get the Dimension of the Used Range Used by the Active Worksheet.
  99 ⍝  WrapText               - Set if Text in Range is Wrapped.
 100 
 101 ⍝ Version 1.0 November 2014, Pierre Gilbert
 102 ⍝ Version 1.1 November 2014, Typo corrections suggested by Daniel Baronnet
 103 ⍝ Version 1.2 November 2014, Typo corrections suggested by Daniel Baronnet
 104 ⍝ Version 1.3 May 2015
 105 ⍝   Methods Improved: GetNumber, GetText, GetValue, GetDisplayText
 106 ⍝   Methods Added:    SaveAsCSV, SaveAsStream, LoadFromStream, AddAfterLastRow
 107 ⍝                     AddAfterLastColumn, ExportDataSet
 108 ⍝   Methods Modified: ExportDataTable, ImportDataTable
 109 
 110 ⍝ Version 1.4 May 2015
 111 ⍝   Methods Improved: GetNumber, GetText, ParseRange
 112 
 113 ⍝ Version 1.5 May 2015
 114 ⍝   Method ExportDataSet corrected for empty worksheet
 115 
 116 
 117     (⎕IO ⎕ML ⎕WX)←1 3 3
 118 
 119     :Field Private sfDir←'Syncfusion/4.5/'  ⍝ Location of the Syncfusion dll's
 120 
 121     :Field Public XL                        ⍝ Syncfusion.XlsIO.ExcelEngine to experiment with other methods
 122 
 123     :Field Public Win                       ⍝ Windows .Net object
 124 
 125       isString←{0 2∊⍨10|⎕DR ⍵               ⍝ Test to detect characters from numbers
 126       }
 127 
 128       rangeRect←{                           ⍝ Get position and size of range
 129           ⍵.IsBlank:0 0 0 0
 130           1+∊⍵.GetRectangles.(Y X Height Width)}
 131 
 132     ∇ Init0;sink;⎕USING
 133       :Access Public
 134       :Implements Constructor
 135      
 136      ⍝ Initialize a Syncfusion ExcelEngine object and creates a Workbook
 137      ⍝  with the default amount of Worksheets (typically 3).
 138       :Trap 0
 139           ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 140           XL←⎕NEW ExcelEngine
 141           sink←XL.Excel.Workbooks.Create ⍬
 142           InitOptions
 143       :End
 144     ∇
 145 
 146     ∇ Init noOfWorksheets;sink;⎕USING
 147       :Access Public
 148       :Implements Constructor
 149      
 150      ⍝ Initialize a Syncfusion ExcelEngine object and creates a Workbook
 151      ⍝ with the the specify number of Worksheets.
 152       :Trap 0
 153           ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 154           XL←⎕NEW ExcelEngine
 155           sink←XL.Excel.Workbooks.Create noOfWorksheets
 156           InitOptions
 157       :End
 158     ∇
 159 
 160     ∇ InitOptions
 161      ⍝ Continue the Initialization with the following options:
 162      
 163      ⍝ When using the Value2 property, set to 0 if you are sure that the given
 164      ⍝ value is not of DateTime data type which improves time performance (default is 1).
 165       XL.Excel.ActiveWorkbook.DetectDateTimeInValue←0
 166      
 167      ⍝ Indicates if all values in the workbook are preserved as strings.(default is 0)
 168      ⍝ XL.Excel.(⌷Worksheets).IsStringsPreserved←1
 169     ∇
 170 
 171     ∇ AddAfterLastColumn array
 172       :Access Public
 173     ⍝ Add array after the last column
 174      
 175       (LastColumn+1)SetValue array
 176     ∇
 177 
 178     ∇ AddAfterLastRow array
 179       :Access Public
 180    ⍝ Add array after the last row
 181      
 182       (-LastRow+1)SetValue array
 183     ∇
 184 
 185     ∇ AddWorksheet sheetName;sink
 186       :Access Public
 187     ⍝ Add a new Worksheet.
 188     ⍝ sheetName = Name of the new sheet
 189      
 190       sink←XL.Excel.ActiveWorkbook.Worksheets.Create(⊂sheetName)
 191     ∇
 192 
 193     ∇ dt←{columnNames}AplToDT aplArray;columnType;index;tableName;⎕USING
 194       :Access Public
 195     ⍝ Create a DataTable from an Apl Array. Each column must be of the same type.
 196     ⍝ aplArray    = An APL array of Numbers and Characters.
 197     ⍝ columnNames = Column names for the DataTable. Works best with 2 characters per name.
 198     ⍝ dt          = DataTable
 199     ⍝ Note: For dates, use a number representing the OA date from method TsToOADate.
 200      
 201     ⍝ If a Vector, set as a one row matrix:
 202       :If 1=⍴⍴aplArray
 203           aplArray←(1,⍴aplArray)⍴aplArray
 204       :EndIf
 205      
 206     ⍝ Check if aplArray is a 2 dimensional array:
 207       :If 2≠⍴⍴aplArray
 208           ⎕←'sfExcel.AplToDT Error: Argument must be of rank equal or smaller than 2'
 209           →0
 210       :End
 211      
 212     ⍝ Check if columnNames is properly formed:
 213       :If 0=⎕NC'columnNames'            ⍝ There is no columnNames
 214       :OrIf (⍴columnNames)≠1↓⍴aplArray  ⍝ Wrong Shape
 215       :OrIf 1∊0=1↑¨0⍴¨columnNames       ⍝ Not characters
 216       :OrIf 0∊≡¨columnNames             ⍝ Wrong Depth
 217      
 218         ⍝ Generate the Column Names as 'C1' 'C2', etc.:
 219           columnNames←'C',¨⍕¨⍳1↓⍴aplArray
 220      
 221       :Else
 222         ⍝ Do nothing the column names are properly formed.
 223      
 224       :End
 225      
 226       tableName←'Data'  ⍝ Default TableName
 227      
 228     ⍝ Default value in case of error:
 229       ⎕USING←'System' 'System.Data,System.Data.dll'
 230       dt←⎕NEW DataTable(⊂tableName)
 231      
 232       :Trap 0
 233         ⍝ Determine the Type of each column:
 234           columnType←''
 235           :For index :In ⍳1↓⍴aplArray
 236               :Select ⎕DR aplArray[;index]
 237               :CaseList 80 160 320 326
 238                   columnType,←String
 239               :Else
 240                   columnType,←Double  ⍝ was Decimal
 241               :EndSelect
 242           :EndFor
 243      
 244         ⍝ Set the Column's Types and Names of the DataTable:
 245           {}columnNames{dt.Columns.Add ⍺ ⍵}¨columnType
 246      
 247         ⍝ Fill the DataTable:
 248           2010⌶dt aplArray
 249      
 250       :EndTrap
 251     ∇
 252 
 253     ∇ AutoFitColumns range
 254       :Access Public
 255     ⍝ Adjust Width of Columns to the Widest Value.
 256     ⍝ Does not work well with Wrapped Text.
 257      
 258       (ParseRange range).AutofitColumns
 259     ∇
 260 
 261     ∇ AutoFitRows range
 262       :Access Public
 263     ⍝ Adjust Height of Rows to the Highest Value.
 264     ⍝ Does not work well with Wrapped Text.
 265      
 266       (ParseRange range).AutofitRows
 267     ∇
 268 
 269     ∇ r←ConvertA1toRC range;height;width;x;y
 270       :Access Public
 271     ⍝ Convert from Excel A1 notation to Row and Column Index notation (RC) (base 1).
 272     ⍝ 'B5'     → 5 2
 273     ⍝ 'D2:E10' → 2 4 10 5
 274      
 275       (y x height width)←∊XL.Excel.ActiveWorkbook.ActiveSheet.Range[⊂range].GetRectangles.(Y X Height Width)
 276      
 277       :If 0 0≡height width
 278         ⍝ Single cell
 279           r←1+y,x
 280       :Else
 281         ⍝ Range
 282           r←1+y,x,(height+y),(width+x)
 283       :End
 284     ∇
 285 
 286     ∇ r←ConvertRCtoA1 range;col;lastCol;lastRow;row
 287       :Access Public
 288     ⍝ Convert from Row and Column Index notation to Excel A1 notation
 289     ⍝ 5 2      → 'B5'
 290     ⍝ 2 4 10 5 → 'D2:E10'
 291      
 292       :If 2=⍴range  ⍝ Single cell
 293           (row col)←range
 294           r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col].AddressLocal
 295      
 296       :ElseIf 4=⍴range  ⍝ Range
 297           (row col lastRow lastCol)←range
 298           r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol].AddressLocal
 299       :End
 300     ∇
 301 
 302     ∇ DeleteWorksheet sheet;item
 303       :Access Public
 304     ⍝ Delete a Worksheet from Active Workbook.
 305     ⍝ sheet = SheetName or Index (base 1) of the Worksheet
 306      
 307       :Select isString sheet
 308       :Case 1 ⍝ String
 309           :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 310               XL.Excel.ActiveWorkbook.Worksheets[⊂sheet].Remove
 311           :Else
 312               ⎕←'sfExcel.DeleteWorksheet Error: Worksheet Name ',sheet,' does not exist'
 313           :End
 314       :CaseList 0 ⍝ Numeric
 315           :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count
 316               XL.Excel.ActiveWorkbook.Worksheets[sheet+1].Remove
 317           :Else
 318               ⎕←'sfExcel.DeleteWorksheet Error: Worksheet index ',sheet,' does not exist'
 319           :End
 320       :EndSelect
 321     ∇
 322 
 323     ∇ Dispose
 324       :Access Public
 325     ⍝ Quit Excel and Dispose of the "XL" object.
 326      
 327       :Trap 0
 328           XL.Excel.Workbooks.Close
 329           XL.Dispose
 330           XL←⎕NULL
 331           ⎕EX'XL'
 332       :EndTrap
 333     ∇
 334 
 335     ∇ apl←DTtoApl dt
 336       :Access Public
 337     ⍝ Convert a .Net DataTable to APL
 338      
 339       :If 9≠⎕NC'dt'
 340       :OrIf 'System.Data.DataTable'≢dt.GetType.ToString
 341           ⎕←'sfExcel.DTtoApl Error: The argument is not a DataTable Object !'
 342       :EndIf
 343      
 344       apl←2011⌶dt
 345     ∇
 346 
 347     ∇ ds←ExportDataSet options;dt;tableName;tableNames;⎕USING
 348       :Access Public
 349      ⍝ Export the whole Workbook as a DataSet
 350      ⍝ Each Worksheets becomes a DataTable
 351      
 352      ⍝ None                    0 No datatable exports flags.
 353      ⍝ ColumnNames             1 Represents the ColumnNames datatable export flag.
 354      ⍝ ComputedFormulaValues   2 Represents the ComputedFormulaValues datatable export flag.
 355      ⍝ DetectColumnTypes       4 Indicates that XlsIO should try to detect column types.
 356      ⍝ 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.
 357      ⍝ PreserveOleDate         16 Indicates whether to preserve Ole date(double numbers)instead of date-time values.
 358      
 359       ⎕USING←'System.Data,System.Data.dll'
 360       ds←⎕NEW DataSet(⊂'DataSet')
 361      
 362       tableNames←GetWorksheetsNames
 363      
 364       :For tableName :In tableNames
 365      
 366           SetActiveWorksheet tableName
 367           :If 0 0 0 0≢UsedRange
 368               dt←options ExportDataTable UsedRange
 369               ds.Tables.Add(dt)
 370           :Else
 371               ⍝ Do nothing. Worksheet is empty.
 372           :EndIf
 373      
 374       :EndFor
 375     ∇
 376 
 377     ∇ dt←{options}ExportDataTable range
 378       :Access Public
 379      ⍝ Export the spreadsheet data as a .Net data table.
 380      
 381      ⍝ None                    0 No datatable exports flags.
 382      ⍝ ColumnNames             1 Represents the ColumnNames datatable export flag.
 383      ⍝ ComputedFormulaValues   2 Represents the ComputedFormulaValues datatable export flag.
 384      ⍝ DetectColumnTypes       4 Indicates that XlsIO should try to detect column types.
 385      ⍝ 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.
 386      ⍝ PreserveOleDate         16 Indicates whether to preserve Ole date(double numbers)instead of date-time values.
 387      
 388       :If 0=⎕NC'options'
 389           options←2+4
 390       :End
 391      
 392       range←ParseRange range
 393       dt←XL.Excel.ActiveWorkbook.ActiveSheet.ExportDataTable(range options)
 394     ∇
 395 
 396     ∇ FreezeRows noRows
 397       :Access Public
 398     ⍝ Freeze Rows from the Top of WorkSheet.
 399     ⍝ noRows = number of rows to freeze from top (base 1)
 400      
 401       XL.Excel.ActiveWorkbook.ActiveSheet.Range[1+noRows;1].FreezePanes
 402     ∇
 403 
 404     ∇ r←GetActiveWorksheetName
 405       :Access Public
 406     ⍝ Get the Active Sheet in Active Workbook.
 407     ⍝ r = active sheet name
 408      
 409       r←XL.Excel.ActiveWorkbook.ActiveSheet.Name
 410     ∇
 411 
 412     ∇ array←GetDisplayText range
 413       :Access Public
 414     ⍝ Gets the text that is displayed in the cell. This is a read-only property, which returns
 415     ⍝ a cell value that is displayed after the number format application.
 416     ⍝ array = Array same Size as Range
 417      
 418       range←ParseRange range
 419      
 420 ⍝     array←(¯2↑rangeRect range)⍴range.Cells.DisplayText
 421      
 422 ⍝     ↓↓↓ This is faster than line above for large range.
 423       array←⊃{⍵.Cells.DisplayText}¨range.Rows
 424     ∇
 425 
 426     ∇ array←GetNumber range;dim;valueConverter
 427       :Access Public
 428     ⍝ Get data of Active WorkSheet. The data must be only numeric.
 429     ⍝ array = Array same Size as Range (of Number only)
 430      
 431       range←ParseRange range
 432       dim←({1+∊⍵.GetRectangles.(Height Width)}range)
 433      
 434       valueConverter←{
 435           ⍵.HasNumber:⍵.Number
 436           ⍵.IsBlank:0   ⍝ Default value when the cell is empty
 437           ⍵.Value2
 438       }
 439      
 440 ⍝     array←dim⍴∊range.Cells.Number
 441      
 442 ⍝     ↓↓↓ This is faster than previous line and will not freeze the interpreter
 443       :If dim[1]≥dim[2]
 444         ⍝ More rows than columns. Resolve column wise.
 445 ⍝         array←⍉⊃{⍵.Cells.Number}¨range.Columns  ⍝ This is working but will bug on empty cell
 446           array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns
 447       :Else
 448         ⍝ More columns than rows. Resolve row wise.
 449 ⍝         array←⊃{⍵.Cells.Number}¨range.Rows      ⍝ This is working but will bug on empty cell
 450           array←⊃{valueConverter¨⍵.Cells}¨range.Rows
 451       :End
 452     ∇
 453 
 454     ∇ array←GetText range;col;dim;lastCol;lastRow;row;valueConverter
 455       :Access Public
 456     ⍝ Get data of Active WorkSheet. The data must be only characters.
 457     ⍝ array = Array same Size as Range (of Text only)
 458      
 459       range←ParseRange range
 460       dim←({1+∊⍵.GetRectangles.(Height Width)}range)
 461      
 462       valueConverter←{
 463           ⍵.HasString:⍵.Text
 464           ⍵.IsBlank:''   ⍝ Default value when the cell is empty
 465           ⍵.Value2
 466       }
 467      
 468 ⍝     array←dim⍴∊range.Cells.Text
 469      
 470 ⍝     ↓↓↓ This is faster than previous line and will not freeze the interpreter
 471       :If dim[1]≥dim[2]
 472         ⍝ More rows than columns. Resolve column wise.
 473 ⍝         array←⍉⊃{⍵.Cells.Text}¨range.Columns  ⍝ This is working but will bug on empty cell
 474           array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns
 475       :Else
 476         ⍝ More columns than rows. Resolve row wise.
 477 ⍝         array←⊃{⍵.Cells.Text}¨range.Rows      ⍝ This is working but will bug on empty cell
 478           array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns
 479       :End
 480     ∇
 481 
 482     ∇ array←GetValue range;dim;valueConverter
 483       :Access Public
 484     ⍝ Get data of Active WorkSheet. The data can be numeric or text.
 485     ⍝ array = Array same Size as Range (Text or Number)
 486      
 487       valueConverter←{
 488           ⍵.HasNumber:⍵.Number
 489           ⍵.HasString:⍵.Text
 490           ⍵.HasFormulaNumberValue:⍵.FormulaNumberValue
 491           ⍵.HasFormulaStringValue:⍵.FormulaStringValue
 492           ⍵.IsBlank:⎕NULL              ⍝ You can use ⎕NULL, 0 or ⊂'' as returned value when cell is blank
 493           ⍵.HasDateTime:⍵.Number
 494           ⍵.HasBoolean:⍵.Number
 495           ⍵.HasRichText:⍵.RichText
 496           ⍵.Value2
 497       }
 498      
 499       range←ParseRange range
 500       dim←({1+∊⍵.GetRectangles.(Height Width)}range)
 501      
 502 ⍝     ↓↓↓ Syncfusion .Value2 is valid but does not work like Excel .Value2
 503 ⍝     array←(¯2↑rangeRect range)⍴range.Cells.Value2
 504      
 505 ⍝     ↓↓↓ This is valid, but will freeze the interpreter while executing
 506 ⍝     array ← dim ⍴ valueConverter¨range.Cells
 507      
 508 ⍝     ↓↓↓ This is faster than previous line and will not freeze the interpreter
 509       :If dim[1]≥dim[2]
 510         ⍝ More rows than columns. Resolve column wise.
 511           array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns
 512       :Else
 513         ⍝ More columns than rows. Resolve row wise.
 514           array←⊃{valueConverter¨⍵.Cells}¨range.Rows
 515       :End
 516      
 517 ⍝     Somehow 'range.Rows.Cells.HasNumber' is faster than 'range.Cells.HasNumber'
 518     ∇
 519 
 520     ∇ r←GetWorksheetsNames
 521       :Access Public
 522     ⍝ Get All the Worksheets Names.
 523     ⍝ r = Worksheets Names
 524      
 525       r←XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 526     ∇
 527 
 528     ∇ range ImportDataTable dt;col;keepHeader;preserveType;row
 529       :Access Public
 530     ⍝ Import a DataTable at [row;col] position in the Active Worksheet.
 531     ⍝ row col      = Row and Column (base 1) of the upper left corner of the DataTable
 532     ⍝ keepHeader   = The first row will be the columns names of the DataTable
 533     ⍝ preserveType = Preserve the Type of the DataTable
 534      
 535      
 536 ⍝     (row col)←range
 537      
 538     ⍝ ↓↓↓ Added in v1.3
 539       (row col keepHeader preserveType)←4↑range,0 1
 540      
 541       {}XL.Excel.ActiveWorkbook.ActiveSheet.ImportDataTable dt keepHeader row col preserveType  ⍝ DataTable, KeepHeader, Row, Col, PreserveType
 542     ∇
 543 
 544     ∇ r←LastColumn
 545       :Access Public
 546     ⍝ Get the Last Column of a Worksheet.
 547     ⍝ r = last column (base 1)
 548      
 549       r←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn
 550     ∇
 551 
 552     ∇ r←LastRow
 553       :Access Public
 554     ⍝ Get the Last Row of a Worksheet.
 555     ⍝ r = last row (base 1)
 556      
 557       r←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow
 558     ∇
 559 
 560     ∇ LoadFile fileName;sink;⎕USING
 561       :Access Public
 562     ⍝ Load an Excel Workbook file.
 563     ⍝ fileName = filename with full path and extension
 564      
 565       ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 566       sink←XL.Excel.Workbooks.Open((⊂fileName),ExcelOpenType.Automatic)
 567     ∇
 568 
 569     ∇ LoadFromStream stream;MS;sink;⎕USING
 570       :Access Public
 571     ⍝ Load an Excel Workbook from a stream obtained by the method 'SaveAsStream'
 572      
 573       ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
 574       ⎕USING,←'System.IO,mscorlib.dll' 'System,mscorlib.dll'
 575      
 576       MS←⎕NEW MemoryStream(⊂⎕UCS stream)
 577       MS.Position←Convert.ToInt64 0
 578       sink←XL.Excel.Workbooks.Open(MS,ExcelOpenType.Automatic)
 579     ∇
 580 
 581     ∇ Merge range
 582       :Access Public
 583     ⍝ Merge Text in Range.
 584      
 585       (ParseRange range).Merge
 586     ∇
 587 
 588     ∇ ts←OADateToTs oaDate;date;⎕USING
 589       :Access Public
 590     ⍝ Convert OA Date to ⎕TS
 591     ⍝ oaDate = .Net OADate
 592     ⍝ ts = ⎕TS
 593     ⍝ Works with TsToOADate
 594      
 595       ⎕USING←'System,mscorlib.dll'
 596       date←DateTime.FromOADate oaDate
 597       ts←date.(Year Month Day Hour Minute Second Millisecond)
 598     ∇
 599 
 600     ∇ r←{array}ParseRange range;col;lastCol;lastRow;row
 601       :Access Public
 602     ⍝ Subroutine used to parse a range
 603     ⍝ range = Range in A1 notation (ex.: 'D5:F8' or 'B2')
 604     ⍝ range = Range with Row and Column Index (ex.: 'D5:F8' is 5 4 8 6)
 605     ⍝ range = Range as single positive number is the Column index base 1
 606     ⍝ range = Range as single negative number is the Row index base 1
 607     ⍝ array = optional, used internally.
 608      
 609       :If 0=isString range  ⍝ Is range a number ?
 610         ⍝ range is a number
 611           :If 1=⍴,range
 612               :If range>0
 613                 ⍝ Positive Number = Single Column as number.
 614                   :If range≤XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn
 615                      ⍝ Pick an existing column.
 616 ⍝                     r←XL.Excel.ActiveWorkbook.ActiveSheet.Columns[range]   ⍝ Too Slow
 617                       lastRow←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow
 618                       r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[1;range;lastRow;range]
 619                   :Else
 620                      ⍝ Define a new range outside the UsedRange.
 621                       row←1 ⋄ col←range
 622                       :If 0≠⎕NC'array'
 623                        ⍝ 'array' exist.
 624                           :If 2=↑⍴⍴array
 625                               lastRow←row+¯1+1⌷⍴array
 626                               lastCol←col+¯1+2⌷⍴array
 627                           :Else
 628                               lastRow←row+¯1+⍴,array
 629                               lastCol←col
 630                           :End
 631                       :Else
 632                         ⍝ 'array' does not exist (GetXXX) and outside of 'UsedRange'
 633                           lastRow←1⌈XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow
 634                           lastCol←col
 635                       :End
 636      
 637                       r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol]
 638                   :End
 639      
 640               :Else
 641                 ⍝ Negative Number = Single Row as number.
 642                   :If (|range)≤XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow
 643                     ⍝ Pick an existing row.
 644 ⍝                     r←XL.Excel.ActiveWorkbook.ActiveSheet.Rows[|range]  ⍝ Too Slow on large Worksheet
 645                       lastCol←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn
 646                       r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[|range;1;|range;lastCol]
 647                   :Else
 648                     ⍝ Define a new range.
 649                       row←|range ⋄ col←1
 650                       :If 0≠⎕NC'array'
 651                         ⍝ 'array' exist.
 652                           :If 2=↑⍴⍴array
 653                               lastRow←row+¯1+1⌷⍴array
 654                               lastCol←col+¯1+2⌷⍴array
 655                           :Else
 656                               lastRow←row
 657                               lastCol←col+¯1+⍴,array
 658                           :End
 659                       :Else
 660                         ⍝ 'array' does not exist (GetXXX) and outside of 'UsedRange'
 661                           lastRow←row
 662                           lastCol←1⌈XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn
 663                       :End
 664      
 665                       r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol]
 666                   :End
 667               :End
 668      
 669           :ElseIf 2=⍴range ⍝ RC notation: Single cell
 670               (row col)←range
 671               r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col]
 672      
 673           :ElseIf 4=⍴range  ⍝ RC notation: Range
 674               (row col lastRow lastCol)←range
 675               r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol]
 676           :EndIf
 677      
 678       :Else  ⍝ range is Text
 679         ⍝ A1 notation: range or single cell
 680           r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[⊂range]
 681      
 682       :End
 683     ∇
 684 
 685     ∇ PrepareWindow;xaml;⎕USING
 686       :Access Private
 687      ⍝ Generic Window to display a SpreadsheetControl.
 688      
 689       xaml←'<sf:RibbonWindow'
 690       xaml,←'   xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"'
 691       xaml,←'   xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"'
 692       xaml,←'   xmlns:sf="http://schemas.syncfusion.com/wpf"'
 693       xaml,←'   Title="SpreadSheet Viewer"'
 694       xaml,←'   x:Name="window"'
 695       xaml,←'   sf:SkinStorage.VisualStyle="Office2010Blue"'
 696       xaml,←'   Width="800" Height="600">'
 697      
 698       xaml,←'   <Grid x:Name="LayoutRoot" >'
 699       xaml,←'       <Grid.RowDefinitions>'
 700       xaml,←'           <RowDefinition Height="Auto"/>'
 701       xaml,←'           <RowDefinition Height="*"/>'
 702       xaml,←'       </Grid.RowDefinitions>'
 703       xaml,←'   <sf:SpreadsheetRibbon x:Name="ribbon" DataContext="{Binding ElementName=spreadsheet}" sf:SkinStorage.VisualStyle="Office2010Blue"/>'
 704       xaml,←'   <sf:SpreadsheetControl x:Name="spreadsheet" FormulaBarVisibility="Visible" Grid.Row="1" sf:SkinStorage.VisualStyle="Office2010Blue"/>'
 705       xaml,←'   </Grid>'
 706       xaml,←'</sf:RibbonWindow>'
 707      
 708       ⎕USING←'Syncfusion.Windows.Tools.Controls,',sfDir,'Syncfusion.Tools.Wpf.dll'
 709       ⎕USING,←⊂'Syncfusion.Windows.Controls.Spreadsheet,',sfDir,'Syncfusion.Spreadsheet.Wpf.dll'
 710       ⎕USING,←⊂'Syncfusion.Windows.Shared,',sfDir,'Syncfusion.Shared.Wpf.dll'
 711      
 712       {}RibbonWindow SpreadsheetRibbon SpreadsheetControl SkinStorage
 713      
 714       ⎕USING,←⊂'System.Windows.Markup,WPF/PresentationFramework.dll'
 715       Win←XamlReader.Parse(⊂xaml)
 716      
 717       Win.ribbon←Win.FindName(⊂'ribbon')
 718       Win.spreadsheet←Win.FindName(⊂'spreadsheet')
 719       Win.window←Win.FindName(⊂'window')
 720     ∇
 721 
 722     ∇ Print sheet;GC;gridModel;SV;Win;⎕USING
 723       :Access Public
 724      ⍝ Print the worksheet selected by the sheetIndex
 725      ⍝ sheet = SheetName or Index (base 1) of the Worksheet
 726      
 727       :Select isString sheet
 728       :Case 1 ⍝ String
 729           :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 730               sheet←(XL.Excel.ActiveWorkbook.(⌷Worksheets).Name)⍳(⊂sheet)
 731           :Else
 732               ⎕←'sfExcel.Print Error: Worksheet Name ',sheet,' does not exist'
 733           :End
 734       :CaseList 0 ⍝ Numeric
 735           :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count
 736               ⍝ Do nothing. sheet is a valid sheet number.
 737           :Else
 738               ⎕←'sfExcel.Print Error: Worksheet index ',sheet,' does not exist'
 739           :End
 740       :EndSelect
 741      
 742       ⎕USING←'Syncfusion.Windows.Controls.Grid.Converter,',sfDir,'Syncfusion.Spreadsheet.Wpf.dll'
 743       gridModel←sheet⊃ExcelGridModelImportExtensions.ImportFromExcel(XL.Excel.ActiveWorkbook)
 744      
 745       ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll'
 746       GC←⎕NEW GridControl
 747       {}GC.Model←gridModel
 748      
 749      ⍝ ↓↓↓ Uncomment if you want to have a window before printing.
 750 ⍝     ⎕USING←'System.Windows.Controls,WPF/PresentationFramework.dll'
 751 ⍝     SV←⎕NEW ScrollViewer
 752 ⍝     SV.CanContentScroll←1
 753 ⍝     SV.HorizontalScrollBarVisibility←SV.HorizontalScrollBarVisibility.Auto
 754 ⍝     SV.VerticalScrollBarVisibility←SV.VerticalScrollBarVisibility.Auto
 755 ⍝     SV.Content←GC
 756 ⍝
 757 ⍝     ⎕USING←'System.Windows,WPF/PresentationFramework.dll'
 758 ⍝     Win←⎕NEW Window
 759 ⍝     Win.Title←'Spreadsheet Viewer'
 760 ⍝     Win.Content←SV
 761 ⍝     Win.Show
 762      
 763       ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll'
 764       GridPrintExtensions.Print GC
 765     ∇
 766 
 767     ∇ r←RangeDimension range
 768       :Access Public
 769     ⍝ Get the dimension of a range (no of rows, no of columns).
 770      
 771     ⍝ ↓↓↓ Working but is Slower for large range
 772     ⍝ r←∊⍴¨(ParseRange range).(Rows Columns)
 773      
 774       r←¯2↑rangeRect(ParseRange range)
 775     ∇
 776 
 777     ∇ {sheet}SaveAsCSV fileName
 778       :Access Public
 779     ⍝ Save a Sheet As fileName in CSV format.
 780     ⍝ fileName = filename with full path and extension (.csv)
 781     ⍝ sheet    = Sheet name or index (base 1)
 782     ⍝ Note: If sheet absent willl use the active sheet.
 783      
 784       :If 0=⎕NC'sheet'
 785         ⍝ sheet is absent used the Active one
 786           sheet←XL.Excel.ActiveWorkbook.ActiveSheet
 787      
 788       :ElseIf isString sheet
 789         ⍝ sheet is characters, use as sheet name
 790           sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet]
 791      
 792       :Else
 793         ⍝ sheet is number, use as index base 1
 794           sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet]
 795       :End
 796      
 797 ⍝      XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel97to2003
 798 ⍝      XL.Excel.ActiveWorkbook.SaveAs(fileName(,','))
 799       sheet.SaveAs(fileName(,','))
 800     ∇
 801 
 802 
 803     ∇ {sheet}SaveAsHtml fileName;options
 804       :Access Public
 805     ⍝ Save the Current WorkSheet As fileName in .Html format.
 806     ⍝ fileName = filename with full path and extension (.Html)
 807     ⍝ sheet = Sheet name or index (base 1)
 808     ⍝ Note: If sheet absent willl use the active sheet.
 809      
 810       :If 0=⎕NC'sheet'
 811         ⍝ sheet is absent used the Active one
 812           sheet←XL.Excel.ActiveWorkbook.ActiveSheet
 813      
 814       :ElseIf isString sheet
 815         ⍝ sheet is characters, use as sheet name
 816           sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet]
 817      
 818       :Else
 819         ⍝ sheet is number, use as index base 1
 820           sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet]
 821       :End
 822      
 823       ⎕USING←'Syncfusion.XlsIO.Implementation,',sfDir,'Syncfusion.XlsIO.Base.dll'
 824       options←⎕NEW HtmlSaveOptions
 825       options.TextMode←HtmlSaveOptions.GetText.DisplayText
 826       sheet.SaveAsHtml(fileName options)
 827     ∇
 828 
 829     ∇ {sheet}SaveAsPdf fileName;converter;pdfDoc;settings;⎕USING
 830       :Access Public
 831     ⍝ Save the Current WorkSheet As fileName in .Pdf format.
 832     ⍝ fileName = filename with full path and extension (.pdf)
 833     ⍝ sheet = Sheet name or index (base 1)
 834     ⍝ Note: If sheet absent willl use the active sheet.
 835      
 836       :If 0=⎕NC'sheet'
 837         ⍝ sheet is absent used the Active one
 838           sheet←XL.Excel.ActiveWorkbook.ActiveSheet
 839      
 840       :ElseIf isString sheet
 841         ⍝ sheet is characters, use as sheet name
 842           sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet]
 843      
 844       :Else
 845         ⍝ sheet is number, use as index base 1
 846           sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet]
 847       :End
 848      
 849      ⍝ Set the PageSetup here if required:
 850      ⍝ sheet.PageSetup.PrintArea
 851      
 852       ⎕USING←'Syncfusion.ExcelToPdfConverter,',sfDir,'Syncfusion.ExcelToPDFConverter.Base.dll'
 853      
 854      ⍝ Open the Excel document you want to convert
 855       converter←⎕NEW ExcelToPdfConverter(sheet)
 856      
 857      ⍝ Initialize the ExcelToPdfconverterSettings
 858       settings←⎕NEW ExcelToPdfConverterSettings
 859      
 860      ⍝ Initialize the PDF document
 861       ⎕USING←'Syncfusion.Pdf,',sfDir,'Syncfusion.Pdf.Base.dll'
 862       pdfDoc←⎕NEW PdfDocument
 863      
 864      ⍝ Set the Layout Options for the output Pdf page.
 865      ⍝ settings.LayoutOptions←settings.LayoutOptions.FitSheetOnOnePage
 866      
 867      ⍝ Assign the PDF document to the TemplateDocument property of ExcelToPdfConverterSettings
 868       settings.TemplateDocument←pdfDoc
 869       settings.DisplayGridLines←settings.DisplayGridLines.Invisible
 870      
 871      ⍝ Convert the Excel document to PDF document
 872       pdfDoc←converter.Convert(settings)
 873      
 874      ⍝ Save the PDF file
 875       pdfDoc.Save(⊂fileName)
 876     ∇
 877 
 878     ∇ SaveAsXls fileName
 879       :Access Public
 880     ⍝ Save the Current WorkBook As fileName in .Xls format.
 881     ⍝ fileName = filename with full path and extension (.xls)
 882     ⍝ Note: .SaveAsXls& write the file to disk and do not hold the interpreter on large file.
 883     ⍝ Note: The .Xls format seems to be faster to load than the .Xlsx format on large file
 884     ⍝       but is slower to Save than the .Xlsx format.
 885      
 886       XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel97to2003
 887       XL.Excel.ActiveWorkbook.SaveAs(⊂fileName)
 888     ∇
 889 
 890     ∇ SaveAsXlsx fileName
 891       :Access Public
 892     ⍝ Save the Current WorkBook As fileName in .Xlsx format
 893     ⍝ fileName = filename with full path and extension (.xlsx)
 894     ⍝ Note: .SaveAsXlsx& write the file to disk and do not hold the interpreter on large file.
 895     ⍝ Note: The .Xlsx format seems to be faster to Save than the .Xls format on large file
 896     ⍝       but is slower to Load than the .Xls format.
 897      
 898       XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel2007
 899       XL.Excel.ActiveWorkbook.SaveAs(⊂fileName)
 900     ∇
 901 
 902     ∇ stream←SaveAsStream;MS;⎕USING
 903       :Access Public
 904     ⍝ Save the Current WorkBook As a Stream in .Xlsx format
 905      
 906       ⎕USING←'System.IO,mscorlib.dll'
 907       MS←⎕NEW MemoryStream
 908      
 909       XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel2007
 910       XL.Excel.ActiveWorkbook.SaveAs(MS)
 911       stream←⎕UCS MS.ToArray
 912      
 913       MS.Close ⋄ MS.Dispose ⋄ MS←⎕NULL
 914     ∇
 915 
 916     ∇ SetActiveWorksheet sheet
 917       :Access Public
 918     ⍝ Activate specific Worksheet in active workbook.
 919     ⍝ sheet = Index of Sheet (Base 1) or name of Sheet (Text)
 920      
 921       :Select isString sheet
 922       :Case 1 ⍝ String
 923           :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name
 924               XL.Excel.ActiveWorkbook.Worksheets[⊂sheet].Activate
 925           :Else
 926               ⎕←'sfExcel.SetActiveWorksheet: Worksheet Name ',sheet,' does not exist'
 927           :End
 928       :CaseList 0 ⍝ Numeric
 929           :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count
 930               XL.Excel.ActiveWorkbook.Worksheets[sheet+1].Activate
 931           :Else
 932               ⎕←'sfExcel.SetActiveWorksheet: Worksheet index ',sheet,' does not exist'
 933           :End
 934       :EndSelect
 935     ∇
 936 
 937     ∇ range SetBackgroundColor fontColor;paletteIndex;⎕USING
 938       :Access Public
 939     ⍝ Set a Range Font Background Color.
 940     ⍝ fontColor = The color of the font
 941      
 942       ⎕USING←('Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll')'System.Drawing,System.Drawing.dll'
 943      
 944     ⍝ ↓↓↓ Will work only with the exact color's name of the palette
 945     ⍝ paletteIndex←⍎'ExcelKnownColors.',fontColor
 946      
 947       :If isString fontColor
 948           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromName⊂fontColor
 949           (ParseRange range).CellStyle.FillBackground←paletteIndex
 950      
 951       :ElseIf ~isString fontColor
 952           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromArgb 255,3↑fontColor
 953           (ParseRange range).CellStyle.FillBackground←paletteIndex
 954      
 955       :End
 956     ∇
 957 
 958     ∇ range SetFontBold bit
 959       :Access Public
 960     ⍝ Set a Range Font in Bold.
 961     ⍝ bit = 1 or 0
 962      
 963       (ParseRange range).CellStyle.Font.Bold←bit
 964     ∇
 965 
 966     ∇ range SetFontColor fontColor;paletteIndex;⎕USING
 967       :Access Public
 968     ⍝ Set a Range Font Color.
 969     ⍝ fontColor = The color of the font
 970      
 971       ⎕USING←('Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll')'System.Drawing,System.Drawing.dll'
 972      
 973     ⍝ ↓↓↓ Will work only with the exact color's name of the palette
 974     ⍝ paletteIndex←⍎'ExcelKnownColors.',fontColor
 975      
 976       :If isString fontColor
 977           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromName⊂fontColor
 978           (ParseRange range).CellStyle.Font.Color←paletteIndex
 979      
 980       :Else
 981           paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromArgb 255,3↑fontColor
 982           (ParseRange range).CellStyle.Font.Color←paletteIndex
 983      
 984       :End
 985     ∇
 986 
 987     ∇ range SetFontItalic bit
 988       :Access Public
 989     ⍝ Set a Range Font in Italic.
 990     ⍝ bit = 1 or 0
 991      
 992       (ParseRange range).CellStyle.Font.Italic←bit
 993     ∇
 994 
 995     ∇ range SetFontName fontName
 996       :Access Public
 997     ⍝ Set a Range Font Name.
 998     ⍝ fontName = name of the font
 999      
1000       (ParseRange range).CellStyle.Font.FontName←fontName
1001     ∇
1002 
1003     ∇ range SetFontSize fontSize
1004       :Access Public
1005     ⍝ Set a Range Font Size in Points.
1006     ⍝ size = Size of Font in Points
1007      
1008       (ParseRange range).CellStyle.Font.Size←fontSize
1009     ∇
1010 
1011     ∇ range SetFormat format
1012       :Access Public
1013     ⍝ Set a Range Format.
1014     ⍝ format = Format (ex.: 'yy/m/d h:mm', '0.00')
1015      
1016       (ParseRange range).NumberFormat←(⊂format)
1017     ∇
1018 
1019     ∇ range SetHAlignment alignment;⎕USING
1020       :Access Public
1021     ⍝ Set Horizontal Alignment of Range.
1022     ⍝ alignment = Alignment (Center, CenterAcrossSelection, Distributed, Fill,General, Justify, Left, Right)
1023      
1024       ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
1025       alignment←⍎'ExcelHAlign.HAlign',alignment
1026      
1027       (ParseRange range).HorizontalAlignment←alignment
1028     ∇
1029 
1030     ∇ range SetNumber array;col;lastCol;lastRow;row
1031       :Access Public
1032     ⍝ Set data of Active WorkSheet. The data must be numeric.
1033     ⍝ array = Array same Size as Range (Number only)
1034      
1035       (array ParseRange range).Cells.Number←,array
1036     ∇
1037 
1038     ∇ range SetText array;col;lastCol;lastRow;row
1039       :Access Public
1040     ⍝ Set data of Active WorkSheet. The data must be Text only.
1041     ⍝ array = Array must be the same Dimension as Range (Text only)
1042      
1043       (array ParseRange range).Cells.Text←,array
1044     ∇
1045 
1046     ∇ range SetVAlignment alignment;⎕USING
1047       :Access Public
1048     ⍝ Set Vertical Alignment of Range.
1049     ⍝ alignment = Alignment (Bottom, Center, Distributed, Justify, Top)
1050      
1051       ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll'
1052       alignment←⍎'ExcelVAlign.VAlign',alignment
1053      
1054       (ParseRange range).VerticalAlignment←alignment
1055     ∇
1056 
1057     ∇ range SetValue array;col;lastCol;lastRow;row
1058       :Access Public
1059     ⍝ Set data of Active WorkSheet.
1060     ⍝ array = Array same Size as Range (Text or Number)
1061      
1062       (array ParseRange range).Cells.Value2←,array
1063     ∇
1064 
1065     ∇ index SetWorksheetName sheetName
1066       :Access Public
1067     ⍝ Set the WorkSheet Name.
1068     ⍝ index =     Index of Worksheet (Base 1)
1069     ⍝ sheetName = Name of Worksheet
1070      
1071       XL.Excel.ActiveWorkbook.Worksheets[index+1].Name←(⊂sheetName)
1072     ∇
1073 
1074     ∇ Show
1075       :Access Public
1076     ⍝ Show the workbook in a window.
1077     ⍝ The changes made in the spreadsheet are not pass into the workspace.
1078     ⍝ This is a one-way, one-time interaction.
1079     ⍝ The method can only be call one time (don't know why).
1080      
1081     ⍝ Generic window to display the SpreadsheetControl
1082       PrepareWindow
1083      
1084     ⍝ Import the current Workbook into the SpreadsheetControl
1085       {}Win.spreadsheet.ImportFromExcel(XL.Excel.ActiveWorkbook)
1086      
1087     ⍝ Show the Spreadsheet
1088       Win.Show
1089     ∇
1090 
1091     ∇ ShowExcelFile fileName
1092       :Access Public
1093     ⍝ To View only an Excel file.
1094      
1095     ⍝ Generic window to display the SpreadsheetControl
1096       PrepareWindow
1097      
1098     ⍝ Import the current Workbook into the SpreadsheetControl
1099       {}Win.spreadsheet.ImportFromExcel(⊂fileName)
1100      
1101     ⍝ Show the Spreadsheet
1102       Win.Show
1103     ∇
1104 
1105     ∇ r←TsToOADate ts;⎕USING
1106       :Access Public
1107      ⍝ OA Date from ⎕TS.
1108      ⍝ ts = ⎕TS
1109      ⍝ r = .Net OADate
1110      ⍝ Note: Works with OADateToTs
1111      
1112       ⎕USING←'System,mscorlib.dll'
1113       r←(⎕NEW DateTime ts).ToOADate
1114     ∇
1115 
1116     ∇ r←UsedRange
1117       :Access Public
1118     ⍝ Get the Range Used by the Active Worksheet.
1119     ⍝ r = Range in RC notation (ex.: 'B1:B12' is 1 2 12 2)
1120     ⍝ r = 0 0 0 0 if Worksheet is empty
1121      
1122       r←rangeRect XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange
1123     ∇
1124 
1125     ∇ r←UsedRangeDimension;range
1126       :Access Public
1127     ⍝ Get the Dimension of the Used Range Used by the Active Worksheet.
1128      
1129       r←¯2↑rangeRect XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange
1130     ∇
1131 
1132     ∇ range WrapText bit
1133       :Access Public
1134     ⍝ Set if Text in Range is Wrapped.
1135     ⍝ bit = 1 or 0
1136      
1137       (ParseRange range).WrapText←bit
1138     ∇
1139 
1140 :EndClass