Introduction

sfExcel is a cover class for using the Syncfusion XlsIO namespace that is a 100% native .NET library that generates fully functional Microsoft Excel Spreadsheets in native Excel format without depending on Microsoft Excel. Syncfusion XlsIO is a perfect solution for the users who need to read and write Microsoft Excel files. It does not require Microsoft Excel to be installed in the Report generation machine or server.

Installation and deployment

The following assemblies are required in the subdirectory /Syncfusion/4.5 of the Dyalog APL version 14 (or newer) directory:

• Syncfusion.Core.dll
• Syncfusion.Compression.Base.dll
• Syncfusion.XlsIO.Base.dll
• Syncfusion.Tools.Wpf.dll
• Syncfusion.Spreadsheet.Wpf.dll
• Syncfusion.Shared.Wpf.dll

Typical Usage

Instanciating the class

    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

Writing APL data to the spreadsheet

  'A2'     xl.SetValue (⊂'Hello World')  ⍝ Set the value of a single cell in A1 notation
   2 1     xl.SetValue (⊂'Hello World')  ⍝ Set the value of a single cell in RC notation
  'D5:F8'  xl.SetValue 4 3⍴⍳12           ⍝ Set the value of a range in A1 notation
   5 4 8 6 xl.SetValue 4 3⍴⍳12           ⍝ Set the value of a range in RC notation

If the data is only Numbers or only Text use instead .SetNumber and .SetText that are faster:

  'A2'     xl.SetText (⊂'Hello World')   ⍝ Set the value of a single cell in A1 notation
   2 1     xl.SetText (⊂'Hello World')   ⍝ Set the value of a single cell in RC notation
  'D5:F8'  xl.SetNumber 4 3⍴⍳12          ⍝ Set the value of a range in A1 notation
   5 4 8 6 xl.SetNumber 4 3⍴⍳12          ⍝ Set the value of a range in RC notation

The method .ImportDataTable is very fast for importing a large set of number and text. However each columns must be of the same type:

   dt←xl.AplToDT 10 10⍴⍳100                ⍝ Get a .Net DataTable from the APL data
   (row col) xl.ImportDataTable dt         ⍝ Import the DataTable at position [row;col] (upper left corner)

For dates use the method .TsToOADate that gives a number compatible with the Excel date format.

  'A3' xl.SetNumber (xl.TsToOADate ⎕TS)

Reading APL data from the spreadsheet

   xl.GetValue 'A2'       ⍝ Get the value of a single cell in A1 notation
   xl.GetValue 2 1        ⍝ Get the value of a single cell in RC notation
   xl.GetValue 'D5:F8'    ⍝ Get the value of a range in A1 notation
   xl.GetValue 5 4 8 6    ⍝ Get the value of a range in RC notation

Again the methods .GetNumber and .GetText are faster when only numbers or characters are in the range:

   xl.GetText 'A2'        ⍝ Get the value of a single cell in A1 notation
   xl.GetText 2 1         ⍝ Get the value of a single cell in RC notation
   xl.GetNumber 'D5:F8'   ⍝ Get the value of a range in A1 notation
   xl.GetNumber 5 4 8 6   ⍝ Get the value of a range in RC notation

To convert dates use .OADateToTs that convert the OA Date back to ⎕TS.

   xl.OADateToTs (xl.GetNumber 'A3')  ⍝ To convert an Excel date to ⎕TS

Methods for Formatting a range

  range xl.SetFontBold bit          ⍝ Set a range font in bold. Bit is 0 or 1.
  range xl.SetFontItalic bit        ⍝ Set a range font in italic. Bit is 0 or 1.
  range xl.WrapText bit             ⍝ Set if text in range is wrapped. Bit is 0 or 1.

  range xl.SetFontColor color       ⍝ Set a range font color. Color can be a name or the R G B values.
  range xl.SetFontName fontName     ⍝ Set a range font name.
  range xl.SetFontSize fontSize     ⍝ Set a range font size in points.
  range xl.SetFormat format         ⍝ Set a range format (ie.: 'yy/m/d h:mm', '0.00', etc.)

  range xl.SetHAlignment alignment  ⍝ Set range Horizontal alignment (Center, CenterAcrossSelection, Distributed, Fill, General, Justify, Left, Right)
  range xl.SetVAlignment alignment  ⍝ Set range Vertical alignment (Bottom, Center, Distributed, Justify, Top)

    xl.Merge range                  ⍝ Merge cells in range.    
    xl.AutoFitColumns range         ⍝ Adjust width of columns in range to the widest value.
    xl.AutoFitRows range            ⍝ Adjust hight of rows to the highest value.
    xl.FreezeRows noRows            ⍝ noRows = number of rows to freeze from top of worksheet.
    xl.LastColumn                   ⍝ Get the last column of a worksheet (base 1).
    xl.LastRow                      ⍝ Get the last row of a worksheet (base 1).
    xl.UsedRange                    ⍝ Get the range used by the active worksheet in RC notation (if empty = 0 0).

Methods related to the worksheet

    xl.AddWorksheet sheetName       ⍝ Add a new worksheet
    xl.DeleteWorksheet sheet        ⍝ Delete a worksheet. sheet = SheetName or Index (base 0) of the worksheet