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