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