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