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