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