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