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