:Class sfExcel ⍝ Cover Class for using the Syncfusion XlsIO namespace. ⍝ Essential XlsIO is a 100% native .NET library that generates fully functional Microsoft Excel Spreadsheets ⍝ in native Excel format without depending on Microsoft Excel. ⍝ Essential XlsIO is a perfect solution for the users who need to read and write Microsoft Excel files. ⍝ It does not require MS Excel to be installed in the Report generation machine or server. ⍝ See individual comments of each methods for more info. ⍝ Typical usage: ⍝ xl←⎕NEW sfExcel ⍝ Instanciate a New Workbook with the default amount of Worksheets (3) ⍝ or xl←⎕NEW sfExcel 1 ⍝ Instanciate a New Workbook with 1 Worksheet ⍝ ⍝ 'A1' xl.SetValue (⊂'Hello World') ⍝ Set the value of a single cell in A1 notation ⍝ 'B2:K11' xl.SetValue (10 10⍴⍳100) ⍝ Set the value of a range in A1 notation ⍝ 2 2 11 11 xl.SetValue (10 10⍴⍳100) ⍝ Set the value of a range in RC notation ⍝ ⍝ dt←xl.AplToDT 10 10⍴⍳100 ⍝ Get a .Net DataTable ⍝ (row col) xl.ImportDataTable dt ⍝ To import a DataTable at position [row;col] ⍝ ⍝ xl.GetValue 'C4' ⍝ Get the value of a cell in A1 notation ⍝ xl.GetValue 4 3 ⍝ Get the value of a single cell in RC notation ⍝ xl.GetValue 'B2:K11' ⍝ Get the value of a range in A1 notation ⍝ xl.GetValue 2 2 11 11 ⍝ Get the value of a range in RC notation ⍝ ⍝ xl.LoadFile fileName ⍝ Load a Workbook from file ⍝ xl.SaveAsXlsx fileName ⍝ Save the Workbook to fileName as .xlsx format ⍝ xl.SaveAsPdf fileName ⍝ Save the Workbood to fileName as .pdf format ⍝ ⍝ xl.Dispose ⍝ To dispose of the class ⍝ ⍝ A range can be defined using: ⍝ A1 notation (ex.: 'D5:F8') ⍝ With Row and Column Index (RC notation) (ex.: 'D5:F8' is 5 4 8 6) ⍝ As a single positive number for the Column index base 1 ⍝ As a single negative number for the Row index base 1 ⍝ ⍝ The methods .SetText and .SetNumber are faster than .SetValue and are preferred when possible. ⍝ The methods .ImportDataTable/.ExportDataTable are very fast for a large set of number and text. ⍝ ⍝ Known limitations: ⍝ Shapes are not supported. ⍝ Methods in sfExcel: ⍝ AddAfterLastColumn - Add a range after the last column. ⍝ AddAfterLastRow - Add a range after the last row. ⍝ AddWorksheet - Add a new Worksheet. ⍝ AplToDT - Create a DataTable from an Apl Array. Each column must be of the same type. ⍝ AutoFitColumns - Adjust Width of Column to the Widest Value. ⍝ AutoFitRows - Adjust Hight of Rows to the Highest Value. ⍝ ConvertA1toRC - Convert from Excel A1 notation to Row and Column Index notation (RC) (base 1). ⍝ ConvertRCtoA1 - Convert from Row and Column Index notation to Excel A1 notation. ⍝ DeleteWorksheet - Delete a Worksheet from Active Workbook. ⍝ Dispose - Quit Excel and Dispose of the "XL" object. ⍝ DTtoApl - Convert a .Net DataTable to APL. ⍝ ExportDataSet - Export the Workbook as a DataSet (collection of DataTables). ⍝ ExportDataTable - Export the spreadsheet data as a DataTable. ⍝ FreezeRows - Freeze Rows from the Top of WorkSheet. ⍝ GetActiveWorksheetName - Get the Active Sheet in Active Workbook. ⍝ GetDisplayText - Gets the text that is displayed in the cell. ⍝ GetNumber - Get data of Active WorkSheet. The data must be only numeric. ⍝ GetText - Get data of Active WorkSheet. The data must be only characters. ⍝ GetValue - Get data of Active WorkSheet. The data can be numeric or text. ⍝ GetWorksheetsNames - Get All the Worksheets Names. ⍝ ImportDataTable - Import a DataTable at [row;col] position in the Active Worksheet. ⍝ LastColumn - Get the Last Column of a Worksheet. ⍝ LastRow - Get the Last Row of a Worksheet. ⍝ LoadFile - Load an Excel Workbook file. ⍝ LoadFromStream - Load an Excel Workbook from a Stream obtained by the method 'SaveAsStream' ⍝ Merge - Merge Text in Range. ⍝ OADateToTs - ⎕TS from OA Date. ⍝ Print - Print using XlsIo (without Excel). ⍝ RangeDimension - Get the dimension of a range (no of rows, no of columns). ⍝ SaveAsCSV - Save to file the specify worksheet with the CSV format. ⍝ SaveAsHtml - Save to file the specify worksheet with the HTML format. ⍝ SaveAsPdf - Save the Current WorkSheet As fileName in .Pdf format. ⍝ SaveAsStream - Save the Current WorkBook As a Stream in .Xlsx format. ⍝ SaveAsXls - Save the Current WorkBook As fileName in .Xls format. ⍝ SaveAsXlsx - Save the Current WorkBook As fileName in .Xlsx format. ⍝ SetActiveWorksheet - Activate specific Worksheet in active workbook. ⍝ SetBackgroundColor - Set a Range Font Background Color. ⍝ SetFontBold - Set a Range Font in Bold. ⍝ SetFontColor - Set a Range Font Color. ⍝ SetFontItalic - Set a Range Font in Italic. ⍝ SetFontName - Set a Range Font Name. ⍝ SetFontSize - Set a Range Font Size in Points. ⍝ SetFormat - Set a Range Format. ⍝ SetHAlignment - Set Horizontal Alignment of Range. ⍝ SetNumber - Set data of Active WorkSheet. The data must be numeric. ⍝ SetText - Set data of Active WorkSheet. The data must be Text only. ⍝ SetVAlignment - Set Vertical Alignment of Range. ⍝ SetValue - Set data of Active WorkSheet. ⍝ SetWorksheetName - Set the WorkSheet Name. ⍝ Show - Show the workbook in a window. ⍝ ShowExcelFile - To View only an Excel file. ⍝ TsToOADate - OA Date from ⎕TS. ⍝ UsedRange - Get the Range Used by the Active Worksheet. ⍝ UsedRangeDimension - Get the Dimension of the Used Range Used by the Active Worksheet. ⍝ WrapText - Set if Text in Range is Wrapped. ⍝ Version 1.0 November 2014, Pierre Gilbert ⍝ Version 1.1 November 2014, Typo corrections suggested by Daniel Baronnet ⍝ Version 1.2 November 2014, Typo corrections suggested by Daniel Baronnet ⍝ Version 1.3 May 2015 ⍝ Methods Improved: GetNumber, GetText, GetValue, GetDisplayText ⍝ Methods Added: SaveAsCSV, SaveAsStream, LoadFromStream, AddAfterLastRow ⍝ AddAfterLastColumn, ExportDataSet ⍝ Methods Modified: ExportDataTable, ImportDataTable ⍝ Version 1.4 May 2015 ⍝ Methods Improved: GetNumber, GetText, ParseRange ⍝ Version 1.5 May 2015 ⍝ Method ExportDataSet corrected for empty worksheet (⎕IO ⎕ML ⎕WX)←1 3 3 :Field Private sfDir←'Syncfusion/4.5/' ⍝ Location of the Syncfusion dll's :Field Public XL ⍝ Syncfusion.XlsIO.ExcelEngine to experiment with other methods :Field Public Win ⍝ Windows .Net object isString←{0 2∊⍨10|⎕DR ⍵ ⍝ Test to detect characters from numbers } rangeRect←{ ⍝ Get position and size of range ⍵.IsBlank:0 0 0 0 1+∊⍵.GetRectangles.(Y X Height Width)} ∇ Init0;sink;⎕USING :Access Public :Implements Constructor ⍝ Initialize a Syncfusion ExcelEngine object and creates a Workbook ⍝ with the default amount of Worksheets (typically 3). :Trap 0 ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll' XL←⎕NEW ExcelEngine sink←XL.Excel.Workbooks.Create ⍬ InitOptions :End ∇ ∇ Init noOfWorksheets;sink;⎕USING :Access Public :Implements Constructor ⍝ Initialize a Syncfusion ExcelEngine object and creates a Workbook ⍝ with the the specify number of Worksheets. :Trap 0 ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll' XL←⎕NEW ExcelEngine sink←XL.Excel.Workbooks.Create noOfWorksheets InitOptions :End ∇ ∇ InitOptions ⍝ Continue the Initialization with the following options: ⍝ When using the Value2 property, set to 0 if you are sure that the given ⍝ value is not of DateTime data type which improves time performance (default is 1). XL.Excel.ActiveWorkbook.DetectDateTimeInValue←0 ⍝ Indicates if all values in the workbook are preserved as strings.(default is 0) ⍝ XL.Excel.(⌷Worksheets).IsStringsPreserved←1 ∇ ∇ AddAfterLastColumn array :Access Public ⍝ Add array after the last column (LastColumn+1)SetValue array ∇ ∇ AddAfterLastRow array :Access Public ⍝ Add array after the last row (-LastRow+1)SetValue array ∇ ∇ AddWorksheet sheetName;sink :Access Public ⍝ Add a new Worksheet. ⍝ sheetName = Name of the new sheet sink←XL.Excel.ActiveWorkbook.Worksheets.Create(⊂sheetName) ∇ ∇ dt←{columnNames}AplToDT aplArray;columnType;index;tableName;⎕USING :Access Public ⍝ Create a DataTable from an Apl Array. Each column must be of the same type. ⍝ aplArray = An APL array of Numbers and Characters. ⍝ columnNames = Column names for the DataTable. Works best with 2 characters per name. ⍝ dt = DataTable ⍝ Note: For dates, use a number representing the OA date from method TsToOADate. ⍝ If a Vector, set as a one row matrix: :If 1=⍴⍴aplArray aplArray←(1,⍴aplArray)⍴aplArray :EndIf ⍝ Check if aplArray is a 2 dimensional array: :If 2≠⍴⍴aplArray ⎕←'sfExcel.AplToDT Error: Argument must be of rank equal or smaller than 2' →0 :End ⍝ Check if columnNames is properly formed: :If 0=⎕NC'columnNames' ⍝ There is no columnNames :OrIf (⍴columnNames)≠1↓⍴aplArray ⍝ Wrong Shape :OrIf 1∊0=1↑¨0⍴¨columnNames ⍝ Not characters :OrIf 0∊≡¨columnNames ⍝ Wrong Depth ⍝ Generate the Column Names as 'C1' 'C2', etc.: columnNames←'C',¨⍕¨⍳1↓⍴aplArray :Else ⍝ Do nothing the column names are properly formed. :End tableName←'Data' ⍝ Default TableName ⍝ Default value in case of error: ⎕USING←'System' 'System.Data,System.Data.dll' dt←⎕NEW DataTable(⊂tableName) :Trap 0 ⍝ Determine the Type of each column: columnType←'' :For index :In ⍳1↓⍴aplArray :Select ⎕DR aplArray[;index] :CaseList 80 160 320 326 columnType,←String :Else columnType,←Double ⍝ was Decimal :EndSelect :EndFor ⍝ Set the Column's Types and Names of the DataTable: {}columnNames{dt.Columns.Add ⍺ ⍵}¨columnType ⍝ Fill the DataTable: 2010⌶dt aplArray :EndTrap ∇ ∇ AutoFitColumns range :Access Public ⍝ Adjust Width of Columns to the Widest Value. ⍝ Does not work well with Wrapped Text. (ParseRange range).AutofitColumns ∇ ∇ AutoFitRows range :Access Public ⍝ Adjust Height of Rows to the Highest Value. ⍝ Does not work well with Wrapped Text. (ParseRange range).AutofitRows ∇ ∇ r←ConvertA1toRC range;height;width;x;y :Access Public ⍝ Convert from Excel A1 notation to Row and Column Index notation (RC) (base 1). ⍝ 'B5' → 5 2 ⍝ 'D2:E10' → 2 4 10 5 (y x height width)←∊XL.Excel.ActiveWorkbook.ActiveSheet.Range[⊂range].GetRectangles.(Y X Height Width) :If 0 0≡height width ⍝ Single cell r←1+y,x :Else ⍝ Range r←1+y,x,(height+y),(width+x) :End ∇ ∇ r←ConvertRCtoA1 range;col;lastCol;lastRow;row :Access Public ⍝ Convert from Row and Column Index notation to Excel A1 notation ⍝ 5 2 → 'B5' ⍝ 2 4 10 5 → 'D2:E10' :If 2=⍴range ⍝ Single cell (row col)←range r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col].AddressLocal :ElseIf 4=⍴range ⍝ Range (row col lastRow lastCol)←range r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol].AddressLocal :End ∇ ∇ DeleteWorksheet sheet;item :Access Public ⍝ Delete a Worksheet from Active Workbook. ⍝ sheet = SheetName or Index (base 1) of the Worksheet :Select isString sheet :Case 1 ⍝ String :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name XL.Excel.ActiveWorkbook.Worksheets[⊂sheet].Remove :Else ⎕←'sfExcel.DeleteWorksheet Error: Worksheet Name ',sheet,' does not exist' :End :CaseList 0 ⍝ Numeric :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count XL.Excel.ActiveWorkbook.Worksheets[sheet+1].Remove :Else ⎕←'sfExcel.DeleteWorksheet Error: Worksheet index ',sheet,' does not exist' :End :EndSelect ∇ ∇ Dispose :Access Public ⍝ Quit Excel and Dispose of the "XL" object. :Trap 0 XL.Excel.Workbooks.Close XL.Dispose XL←⎕NULL ⎕EX'XL' :EndTrap ∇ ∇ apl←DTtoApl dt :Access Public ⍝ Convert a .Net DataTable to APL :If 9≠⎕NC'dt' :OrIf 'System.Data.DataTable'≢dt.GetType.ToString ⎕←'sfExcel.DTtoApl Error: The argument is not a DataTable Object !' :EndIf apl←2011⌶dt ∇ ∇ ds←ExportDataSet options;dt;tableName;tableNames;⎕USING :Access Public ⍝ Export the whole Workbook as a DataSet ⍝ Each Worksheets becomes a DataTable ⍝ None 0 No datatable exports flags. ⍝ ColumnNames 1 Represents the ColumnNames datatable export flag. ⍝ ComputedFormulaValues 2 Represents the ComputedFormulaValues datatable export flag. ⍝ DetectColumnTypes 4 Indicates that XlsIO should try to detect column types. ⍝ 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. ⍝ PreserveOleDate 16 Indicates whether to preserve Ole date(double numbers)instead of date-time values. ⎕USING←'System.Data,System.Data.dll' ds←⎕NEW DataSet(⊂'DataSet') tableNames←GetWorksheetsNames :For tableName :In tableNames SetActiveWorksheet tableName :If 0 0 0 0≢UsedRange dt←options ExportDataTable UsedRange ds.Tables.Add(dt) :Else ⍝ Do nothing. Worksheet is empty. :EndIf :EndFor ∇ ∇ dt←{options}ExportDataTable range :Access Public ⍝ Export the spreadsheet data as a .Net data table. ⍝ None 0 No datatable exports flags. ⍝ ColumnNames 1 Represents the ColumnNames datatable export flag. ⍝ ComputedFormulaValues 2 Represents the ComputedFormulaValues datatable export flag. ⍝ DetectColumnTypes 4 Indicates that XlsIO should try to detect column types. ⍝ 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. ⍝ PreserveOleDate 16 Indicates whether to preserve Ole date(double numbers)instead of date-time values. :If 0=⎕NC'options' options←2+4 :End range←ParseRange range dt←XL.Excel.ActiveWorkbook.ActiveSheet.ExportDataTable(range options) ∇ ∇ FreezeRows noRows :Access Public ⍝ Freeze Rows from the Top of WorkSheet. ⍝ noRows = number of rows to freeze from top (base 1) XL.Excel.ActiveWorkbook.ActiveSheet.Range[1+noRows;1].FreezePanes ∇ ∇ r←GetActiveWorksheetName :Access Public ⍝ Get the Active Sheet in Active Workbook. ⍝ r = active sheet name r←XL.Excel.ActiveWorkbook.ActiveSheet.Name ∇ ∇ array←GetDisplayText range :Access Public ⍝ Gets the text that is displayed in the cell. This is a read-only property, which returns ⍝ a cell value that is displayed after the number format application. ⍝ array = Array same Size as Range range←ParseRange range ⍝ array←(¯2↑rangeRect range)⍴range.Cells.DisplayText ⍝ ↓↓↓ This is faster than line above for large range. array←⊃{⍵.Cells.DisplayText}¨range.Rows ∇ ∇ array←GetNumber range;dim;valueConverter :Access Public ⍝ Get data of Active WorkSheet. The data must be only numeric. ⍝ array = Array same Size as Range (of Number only) range←ParseRange range dim←({1+∊⍵.GetRectangles.(Height Width)}range) valueConverter←{ ⍵.HasNumber:⍵.Number ⍵.IsBlank:0 ⍝ Default value when the cell is empty ⍵.Value2 } ⍝ array←dim⍴∊range.Cells.Number ⍝ ↓↓↓ This is faster than previous line and will not freeze the interpreter :If dim[1]≥dim[2] ⍝ More rows than columns. Resolve column wise. ⍝ array←⍉⊃{⍵.Cells.Number}¨range.Columns ⍝ This is working but will bug on empty cell array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns :Else ⍝ More columns than rows. Resolve row wise. ⍝ array←⊃{⍵.Cells.Number}¨range.Rows ⍝ This is working but will bug on empty cell array←⊃{valueConverter¨⍵.Cells}¨range.Rows :End ∇ ∇ array←GetText range;col;dim;lastCol;lastRow;row;valueConverter :Access Public ⍝ Get data of Active WorkSheet. The data must be only characters. ⍝ array = Array same Size as Range (of Text only) range←ParseRange range dim←({1+∊⍵.GetRectangles.(Height Width)}range) valueConverter←{ ⍵.HasString:⍵.Text ⍵.IsBlank:'' ⍝ Default value when the cell is empty ⍵.Value2 } ⍝ array←dim⍴∊range.Cells.Text ⍝ ↓↓↓ This is faster than previous line and will not freeze the interpreter :If dim[1]≥dim[2] ⍝ More rows than columns. Resolve column wise. ⍝ array←⍉⊃{⍵.Cells.Text}¨range.Columns ⍝ This is working but will bug on empty cell array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns :Else ⍝ More columns than rows. Resolve row wise. ⍝ array←⊃{⍵.Cells.Text}¨range.Rows ⍝ This is working but will bug on empty cell array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns :End ∇ ∇ array←GetValue range;dim;valueConverter :Access Public ⍝ Get data of Active WorkSheet. The data can be numeric or text. ⍝ array = Array same Size as Range (Text or Number) valueConverter←{ ⍵.HasNumber:⍵.Number ⍵.HasString:⍵.Text ⍵.HasFormulaNumberValue:⍵.FormulaNumberValue ⍵.HasFormulaStringValue:⍵.FormulaStringValue ⍵.IsBlank:⎕NULL ⍝ You can use ⎕NULL, 0 or ⊂'' as returned value when cell is blank ⍵.HasDateTime:⍵.Number ⍵.HasBoolean:⍵.Number ⍵.HasRichText:⍵.RichText ⍵.Value2 } range←ParseRange range dim←({1+∊⍵.GetRectangles.(Height Width)}range) ⍝ ↓↓↓ Syncfusion .Value2 is valid but does not work like Excel .Value2 ⍝ array←(¯2↑rangeRect range)⍴range.Cells.Value2 ⍝ ↓↓↓ This is valid, but will freeze the interpreter while executing ⍝ array ← dim ⍴ valueConverter¨range.Cells ⍝ ↓↓↓ This is faster than previous line and will not freeze the interpreter :If dim[1]≥dim[2] ⍝ More rows than columns. Resolve column wise. array←⍉⊃{valueConverter¨⍵.Cells}¨range.Columns :Else ⍝ More columns than rows. Resolve row wise. array←⊃{valueConverter¨⍵.Cells}¨range.Rows :End ⍝ Somehow 'range.Rows.Cells.HasNumber' is faster than 'range.Cells.HasNumber' ∇ ∇ r←GetWorksheetsNames :Access Public ⍝ Get All the Worksheets Names. ⍝ r = Worksheets Names r←XL.Excel.ActiveWorkbook.(⌷Worksheets).Name ∇ ∇ range ImportDataTable dt;col;keepHeader;preserveType;row :Access Public ⍝ Import a DataTable at [row;col] position in the Active Worksheet. ⍝ row col = Row and Column (base 1) of the upper left corner of the DataTable ⍝ keepHeader = The first row will be the columns names of the DataTable ⍝ preserveType = Preserve the Type of the DataTable ⍝ (row col)←range ⍝ ↓↓↓ Added in v1.3 (row col keepHeader preserveType)←4↑range,0 1 {}XL.Excel.ActiveWorkbook.ActiveSheet.ImportDataTable dt keepHeader row col preserveType ⍝ DataTable, KeepHeader, Row, Col, PreserveType ∇ ∇ r←LastColumn :Access Public ⍝ Get the Last Column of a Worksheet. ⍝ r = last column (base 1) r←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn ∇ ∇ r←LastRow :Access Public ⍝ Get the Last Row of a Worksheet. ⍝ r = last row (base 1) r←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow ∇ ∇ LoadFile fileName;sink;⎕USING :Access Public ⍝ Load an Excel Workbook file. ⍝ fileName = filename with full path and extension ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll' sink←XL.Excel.Workbooks.Open((⊂fileName),ExcelOpenType.Automatic) ∇ ∇ LoadFromStream stream;MS;sink;⎕USING :Access Public ⍝ Load an Excel Workbook from a stream obtained by the method 'SaveAsStream' ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll' ⎕USING,←'System.IO,mscorlib.dll' 'System,mscorlib.dll' MS←⎕NEW MemoryStream(⊂⎕UCS stream) MS.Position←Convert.ToInt64 0 sink←XL.Excel.Workbooks.Open(MS,ExcelOpenType.Automatic) ∇ ∇ Merge range :Access Public ⍝ Merge Text in Range. (ParseRange range).Merge ∇ ∇ ts←OADateToTs oaDate;date;⎕USING :Access Public ⍝ Convert OA Date to ⎕TS ⍝ oaDate = .Net OADate ⍝ ts = ⎕TS ⍝ Works with TsToOADate ⎕USING←'System,mscorlib.dll' date←DateTime.FromOADate oaDate ts←date.(Year Month Day Hour Minute Second Millisecond) ∇ ∇ r←{array}ParseRange range;col;lastCol;lastRow;row :Access Public ⍝ Subroutine used to parse a range ⍝ range = Range in A1 notation (ex.: 'D5:F8' or 'B2') ⍝ range = Range with Row and Column Index (ex.: 'D5:F8' is 5 4 8 6) ⍝ range = Range as single positive number is the Column index base 1 ⍝ range = Range as single negative number is the Row index base 1 ⍝ array = optional, used internally. :If 0=isString range ⍝ Is range a number ? ⍝ range is a number :If 1=⍴,range :If range>0 ⍝ Positive Number = Single Column as number. :If range≤XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn ⍝ Pick an existing column. ⍝ r←XL.Excel.ActiveWorkbook.ActiveSheet.Columns[range] ⍝ Too Slow lastRow←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[1;range;lastRow;range] :Else ⍝ Define a new range outside the UsedRange. row←1 ⋄ col←range :If 0≠⎕NC'array' ⍝ 'array' exist. :If 2=↑⍴⍴array lastRow←row+¯1+1⌷⍴array lastCol←col+¯1+2⌷⍴array :Else lastRow←row+¯1+⍴,array lastCol←col :End :Else ⍝ 'array' does not exist (GetXXX) and outside of 'UsedRange' lastRow←1⌈XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow lastCol←col :End r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol] :End :Else ⍝ Negative Number = Single Row as number. :If (|range)≤XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastRow ⍝ Pick an existing row. ⍝ r←XL.Excel.ActiveWorkbook.ActiveSheet.Rows[|range] ⍝ Too Slow on large Worksheet lastCol←XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[|range;1;|range;lastCol] :Else ⍝ Define a new range. row←|range ⋄ col←1 :If 0≠⎕NC'array' ⍝ 'array' exist. :If 2=↑⍴⍴array lastRow←row+¯1+1⌷⍴array lastCol←col+¯1+2⌷⍴array :Else lastRow←row lastCol←col+¯1+⍴,array :End :Else ⍝ 'array' does not exist (GetXXX) and outside of 'UsedRange' lastRow←row lastCol←1⌈XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange.LastColumn :End r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol] :End :End :ElseIf 2=⍴range ⍝ RC notation: Single cell (row col)←range r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col] :ElseIf 4=⍴range ⍝ RC notation: Range (row col lastRow lastCol)←range r←⍬⍴XL.Excel.ActiveWorkbook.ActiveSheet.Range[row;col;lastRow;lastCol] :EndIf :Else ⍝ range is Text ⍝ A1 notation: range or single cell r←XL.Excel.ActiveWorkbook.ActiveSheet.Range[⊂range] :End ∇ ∇ PrepareWindow;xaml;⎕USING :Access Private ⍝ Generic Window to display a SpreadsheetControl. xaml←'' xaml,←' ' xaml,←' ' xaml,←' ' xaml,←' ' xaml,←' ' xaml,←' ' xaml,←' ' xaml,←' ' xaml,←'' ⎕USING←'Syncfusion.Windows.Tools.Controls,',sfDir,'Syncfusion.Tools.Wpf.dll' ⎕USING,←⊂'Syncfusion.Windows.Controls.Spreadsheet,',sfDir,'Syncfusion.Spreadsheet.Wpf.dll' ⎕USING,←⊂'Syncfusion.Windows.Shared,',sfDir,'Syncfusion.Shared.Wpf.dll' {}RibbonWindow SpreadsheetRibbon SpreadsheetControl SkinStorage ⎕USING,←⊂'System.Windows.Markup,WPF/PresentationFramework.dll' Win←XamlReader.Parse(⊂xaml) Win.ribbon←Win.FindName(⊂'ribbon') Win.spreadsheet←Win.FindName(⊂'spreadsheet') Win.window←Win.FindName(⊂'window') ∇ ∇ Print sheet;GC;gridModel;SV;Win;⎕USING :Access Public ⍝ Print the worksheet selected by the sheetIndex ⍝ sheet = SheetName or Index (base 1) of the Worksheet :Select isString sheet :Case 1 ⍝ String :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name sheet←(XL.Excel.ActiveWorkbook.(⌷Worksheets).Name)⍳(⊂sheet) :Else ⎕←'sfExcel.Print Error: Worksheet Name ',sheet,' does not exist' :End :CaseList 0 ⍝ Numeric :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count ⍝ Do nothing. sheet is a valid sheet number. :Else ⎕←'sfExcel.Print Error: Worksheet index ',sheet,' does not exist' :End :EndSelect ⎕USING←'Syncfusion.Windows.Controls.Grid.Converter,',sfDir,'Syncfusion.Spreadsheet.Wpf.dll' gridModel←sheet⊃ExcelGridModelImportExtensions.ImportFromExcel(XL.Excel.ActiveWorkbook) ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll' GC←⎕NEW GridControl {}GC.Model←gridModel ⍝ ↓↓↓ Uncomment if you want to have a window before printing. ⍝ ⎕USING←'System.Windows.Controls,WPF/PresentationFramework.dll' ⍝ SV←⎕NEW ScrollViewer ⍝ SV.CanContentScroll←1 ⍝ SV.HorizontalScrollBarVisibility←SV.HorizontalScrollBarVisibility.Auto ⍝ SV.VerticalScrollBarVisibility←SV.VerticalScrollBarVisibility.Auto ⍝ SV.Content←GC ⍝ ⍝ ⎕USING←'System.Windows,WPF/PresentationFramework.dll' ⍝ Win←⎕NEW Window ⍝ Win.Title←'Spreadsheet Viewer' ⍝ Win.Content←SV ⍝ Win.Show ⎕USING←'Syncfusion.Windows.Controls.Grid,',sfDir,'Syncfusion.Grid.WPF.dll' GridPrintExtensions.Print GC ∇ ∇ r←RangeDimension range :Access Public ⍝ Get the dimension of a range (no of rows, no of columns). ⍝ ↓↓↓ Working but is Slower for large range ⍝ r←∊⍴¨(ParseRange range).(Rows Columns) r←¯2↑rangeRect(ParseRange range) ∇ ∇ {sheet}SaveAsCSV fileName :Access Public ⍝ Save a Sheet As fileName in CSV format. ⍝ fileName = filename with full path and extension (.csv) ⍝ sheet = Sheet name or index (base 1) ⍝ Note: If sheet absent willl use the active sheet. :If 0=⎕NC'sheet' ⍝ sheet is absent used the Active one sheet←XL.Excel.ActiveWorkbook.ActiveSheet :ElseIf isString sheet ⍝ sheet is characters, use as sheet name sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet] :Else ⍝ sheet is number, use as index base 1 sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet] :End ⍝ XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel97to2003 ⍝ XL.Excel.ActiveWorkbook.SaveAs(fileName(,',')) sheet.SaveAs(fileName(,',')) ∇ ∇ {sheet}SaveAsHtml fileName;options :Access Public ⍝ Save the Current WorkSheet As fileName in .Html format. ⍝ fileName = filename with full path and extension (.Html) ⍝ sheet = Sheet name or index (base 1) ⍝ Note: If sheet absent willl use the active sheet. :If 0=⎕NC'sheet' ⍝ sheet is absent used the Active one sheet←XL.Excel.ActiveWorkbook.ActiveSheet :ElseIf isString sheet ⍝ sheet is characters, use as sheet name sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet] :Else ⍝ sheet is number, use as index base 1 sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet] :End ⎕USING←'Syncfusion.XlsIO.Implementation,',sfDir,'Syncfusion.XlsIO.Base.dll' options←⎕NEW HtmlSaveOptions options.TextMode←HtmlSaveOptions.GetText.DisplayText sheet.SaveAsHtml(fileName options) ∇ ∇ {sheet}SaveAsPdf fileName;converter;pdfDoc;settings;⎕USING :Access Public ⍝ Save the Current WorkSheet As fileName in .Pdf format. ⍝ fileName = filename with full path and extension (.pdf) ⍝ sheet = Sheet name or index (base 1) ⍝ Note: If sheet absent willl use the active sheet. :If 0=⎕NC'sheet' ⍝ sheet is absent used the Active one sheet←XL.Excel.ActiveWorkbook.ActiveSheet :ElseIf isString sheet ⍝ sheet is characters, use as sheet name sheet←XL.Excel.ActiveWorkbook.Worksheets[⊂sheet] :Else ⍝ sheet is number, use as index base 1 sheet←XL.Excel.ActiveWorkbook.Worksheets[1+sheet] :End ⍝ Set the PageSetup here if required: ⍝ sheet.PageSetup.PrintArea ⎕USING←'Syncfusion.ExcelToPdfConverter,',sfDir,'Syncfusion.ExcelToPDFConverter.Base.dll' ⍝ Open the Excel document you want to convert converter←⎕NEW ExcelToPdfConverter(sheet) ⍝ Initialize the ExcelToPdfconverterSettings settings←⎕NEW ExcelToPdfConverterSettings ⍝ Initialize the PDF document ⎕USING←'Syncfusion.Pdf,',sfDir,'Syncfusion.Pdf.Base.dll' pdfDoc←⎕NEW PdfDocument ⍝ Set the Layout Options for the output Pdf page. ⍝ settings.LayoutOptions←settings.LayoutOptions.FitSheetOnOnePage ⍝ Assign the PDF document to the TemplateDocument property of ExcelToPdfConverterSettings settings.TemplateDocument←pdfDoc settings.DisplayGridLines←settings.DisplayGridLines.Invisible ⍝ Convert the Excel document to PDF document pdfDoc←converter.Convert(settings) ⍝ Save the PDF file pdfDoc.Save(⊂fileName) ∇ ∇ SaveAsXls fileName :Access Public ⍝ Save the Current WorkBook As fileName in .Xls format. ⍝ fileName = filename with full path and extension (.xls) ⍝ Note: .SaveAsXls& write the file to disk and do not hold the interpreter on large file. ⍝ Note: The .Xls format seems to be faster to load than the .Xlsx format on large file ⍝ but is slower to Save than the .Xlsx format. XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel97to2003 XL.Excel.ActiveWorkbook.SaveAs(⊂fileName) ∇ ∇ SaveAsXlsx fileName :Access Public ⍝ Save the Current WorkBook As fileName in .Xlsx format ⍝ fileName = filename with full path and extension (.xlsx) ⍝ Note: .SaveAsXlsx& write the file to disk and do not hold the interpreter on large file. ⍝ Note: The .Xlsx format seems to be faster to Save than the .Xls format on large file ⍝ but is slower to Load than the .Xls format. XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel2007 XL.Excel.ActiveWorkbook.SaveAs(⊂fileName) ∇ ∇ stream←SaveAsStream;MS;⎕USING :Access Public ⍝ Save the Current WorkBook As a Stream in .Xlsx format ⎕USING←'System.IO,mscorlib.dll' MS←⎕NEW MemoryStream XL.Excel.ActiveWorkbook.Version←XL.Excel.ActiveWorkbook.Version.Excel2007 XL.Excel.ActiveWorkbook.SaveAs(MS) stream←⎕UCS MS.ToArray MS.Close ⋄ MS.Dispose ⋄ MS←⎕NULL ∇ ∇ SetActiveWorksheet sheet :Access Public ⍝ Activate specific Worksheet in active workbook. ⍝ sheet = Index of Sheet (Base 1) or name of Sheet (Text) :Select isString sheet :Case 1 ⍝ String :If (⊂sheet)∊XL.Excel.ActiveWorkbook.(⌷Worksheets).Name XL.Excel.ActiveWorkbook.Worksheets[⊂sheet].Activate :Else ⎕←'sfExcel.SetActiveWorksheet: Worksheet Name ',sheet,' does not exist' :End :CaseList 0 ⍝ Numeric :If sheet≤XL.Excel.ActiveWorkbook.Worksheets.Count XL.Excel.ActiveWorkbook.Worksheets[sheet+1].Activate :Else ⎕←'sfExcel.SetActiveWorksheet: Worksheet index ',sheet,' does not exist' :End :EndSelect ∇ ∇ range SetBackgroundColor fontColor;paletteIndex;⎕USING :Access Public ⍝ Set a Range Font Background Color. ⍝ fontColor = The color of the font ⎕USING←('Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll')'System.Drawing,System.Drawing.dll' ⍝ ↓↓↓ Will work only with the exact color's name of the palette ⍝ paletteIndex←⍎'ExcelKnownColors.',fontColor :If isString fontColor paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromName⊂fontColor (ParseRange range).CellStyle.FillBackground←paletteIndex :ElseIf ~isString fontColor paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromArgb 255,3↑fontColor (ParseRange range).CellStyle.FillBackground←paletteIndex :End ∇ ∇ range SetFontBold bit :Access Public ⍝ Set a Range Font in Bold. ⍝ bit = 1 or 0 (ParseRange range).CellStyle.Font.Bold←bit ∇ ∇ range SetFontColor fontColor;paletteIndex;⎕USING :Access Public ⍝ Set a Range Font Color. ⍝ fontColor = The color of the font ⎕USING←('Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll')'System.Drawing,System.Drawing.dll' ⍝ ↓↓↓ Will work only with the exact color's name of the palette ⍝ paletteIndex←⍎'ExcelKnownColors.',fontColor :If isString fontColor paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromName⊂fontColor (ParseRange range).CellStyle.Font.Color←paletteIndex :Else paletteIndex←XL.Excel.ActiveWorkbook.SetColorOrGetNearest Color.FromArgb 255,3↑fontColor (ParseRange range).CellStyle.Font.Color←paletteIndex :End ∇ ∇ range SetFontItalic bit :Access Public ⍝ Set a Range Font in Italic. ⍝ bit = 1 or 0 (ParseRange range).CellStyle.Font.Italic←bit ∇ ∇ range SetFontName fontName :Access Public ⍝ Set a Range Font Name. ⍝ fontName = name of the font (ParseRange range).CellStyle.Font.FontName←fontName ∇ ∇ range SetFontSize fontSize :Access Public ⍝ Set a Range Font Size in Points. ⍝ size = Size of Font in Points (ParseRange range).CellStyle.Font.Size←fontSize ∇ ∇ range SetFormat format :Access Public ⍝ Set a Range Format. ⍝ format = Format (ex.: 'yy/m/d h:mm', '0.00') (ParseRange range).NumberFormat←(⊂format) ∇ ∇ range SetHAlignment alignment;⎕USING :Access Public ⍝ Set Horizontal Alignment of Range. ⍝ alignment = Alignment (Center, CenterAcrossSelection, Distributed, Fill,General, Justify, Left, Right) ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll' alignment←⍎'ExcelHAlign.HAlign',alignment (ParseRange range).HorizontalAlignment←alignment ∇ ∇ range SetNumber array;col;lastCol;lastRow;row :Access Public ⍝ Set data of Active WorkSheet. The data must be numeric. ⍝ array = Array same Size as Range (Number only) (array ParseRange range).Cells.Number←,array ∇ ∇ range SetText array;col;lastCol;lastRow;row :Access Public ⍝ Set data of Active WorkSheet. The data must be Text only. ⍝ array = Array must be the same Dimension as Range (Text only) (array ParseRange range).Cells.Text←,array ∇ ∇ range SetVAlignment alignment;⎕USING :Access Public ⍝ Set Vertical Alignment of Range. ⍝ alignment = Alignment (Bottom, Center, Distributed, Justify, Top) ⎕USING←'Syncfusion.XlsIO,',sfDir,'Syncfusion.XlsIO.Base.dll' alignment←⍎'ExcelVAlign.VAlign',alignment (ParseRange range).VerticalAlignment←alignment ∇ ∇ range SetValue array;col;lastCol;lastRow;row :Access Public ⍝ Set data of Active WorkSheet. ⍝ array = Array same Size as Range (Text or Number) (array ParseRange range).Cells.Value2←,array ∇ ∇ index SetWorksheetName sheetName :Access Public ⍝ Set the WorkSheet Name. ⍝ index = Index of Worksheet (Base 1) ⍝ sheetName = Name of Worksheet XL.Excel.ActiveWorkbook.Worksheets[index+1].Name←(⊂sheetName) ∇ ∇ Show :Access Public ⍝ Show the workbook in a window. ⍝ The changes made in the spreadsheet are not pass into the workspace. ⍝ This is a one-way, one-time interaction. ⍝ The method can only be call one time (don't know why). ⍝ Generic window to display the SpreadsheetControl PrepareWindow ⍝ Import the current Workbook into the SpreadsheetControl {}Win.spreadsheet.ImportFromExcel(XL.Excel.ActiveWorkbook) ⍝ Show the Spreadsheet Win.Show ∇ ∇ ShowExcelFile fileName :Access Public ⍝ To View only an Excel file. ⍝ Generic window to display the SpreadsheetControl PrepareWindow ⍝ Import the current Workbook into the SpreadsheetControl {}Win.spreadsheet.ImportFromExcel(⊂fileName) ⍝ Show the Spreadsheet Win.Show ∇ ∇ r←TsToOADate ts;⎕USING :Access Public ⍝ OA Date from ⎕TS. ⍝ ts = ⎕TS ⍝ r = .Net OADate ⍝ Note: Works with OADateToTs ⎕USING←'System,mscorlib.dll' r←(⎕NEW DateTime ts).ToOADate ∇ ∇ r←UsedRange :Access Public ⍝ Get the Range Used by the Active Worksheet. ⍝ r = Range in RC notation (ex.: 'B1:B12' is 1 2 12 2) ⍝ r = 0 0 0 0 if Worksheet is empty r←rangeRect XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange ∇ ∇ r←UsedRangeDimension;range :Access Public ⍝ Get the Dimension of the Used Range Used by the Active Worksheet. r←¯2↑rangeRect XL.Excel.ActiveWorkbook.ActiveSheet.UsedRange ∇ ∇ range WrapText bit :Access Public ⍝ Set if Text in Range is Wrapped. ⍝ bit = 1 or 0 (ParseRange range).WrapText←bit ∇ :EndClass