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