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
Syncfusion.Grid.WPF.dll

When saving to a PDF file the following assemblies are also required:

Syncfusion.ExcelToPDFConverter.Base.dll
Syncfusion.Pdf.Base.dll

Typical Usage

Instanciating and Disposing of 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.

   xl.Dispose         ⍝ To do when finish using the class to release the memory.

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 to the spreadsheet. 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)  ⍝ To convert ⎕TS to an OADate compatible with Excel.

Reading data from the spreadsheet to APL:

   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.

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.

The method .ExportDataTable is recommended for large range.

   dt←xl.ExportDataTable 'J10:S19'    ⍝ Export the spreadsheet data as a .Net data table.
   xl.DTtoApl dt                      ⍝ Convert a .Net DataTable to APL.

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 (ex.: '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.
   xl.LastRow                   ⍝ Get the last row of a worksheet.
   xl.UsedRange                 ⍝ Get the range used by the active worksheet in RC notation (if empty = 0 0).

   xl.ConvertA1toRC             ⍝ Convert from Excel A1 notation to Row and Column Index notation (RC).
                                ⍝ 'B5'     → 5 2
                                ⍝ 'D2:E10' → 2 4 10 5

   xl.ConvertRCtoA1             ⍝ Convert from Row and Column Index notation to Excel A1 notation.
                                ⍝ 5 2      → 'B5'
                                ⍝ 2 4 10 5 → 'D2:E10'

Remark for method with a range as argument:
The method .ParseRange is used internally to obtain a Syncfusion range object with the following parameters:

range can be in A1 notation (ex.: 'D5:F8' or 'B2')
range can be in RC notation (ex.: 'D5:F8' is 5 4 8 6) or row col lastRow lastCol
range can be a single positive number as the Column index base 1
range can be a single negative number as the Row index base 1
range size and the numbers of element(s) of the apl variable must match all the time

Methods related to the worksheet:

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

      xl.GetActiveWorksheetName      ⍝ Get the active sheet name of active workbook.
      xl.SetActiveWorksheet sheet    ⍝ Set active a specific worksheet. sheet = SheetName or Index of the worksheet.
      xl.GetWorksheetsNames          ⍝ Get all the worksheets names of active workbook.

index xl.SetWorksheetName sheetName  ⍝ Set the worksheet name. index = index of worksheet

Methods related to the workbook:

    xl.Show                  ⍝ Show the active workbook.
    xl.Print sheet           ⍝ Print worksheet. sheet = SheetName or Index of the worksheet.
    xl.LoadFile fileName     ⍝ Load an Excel workbook file.
    xl.SaveAsXls fileName    ⍝ Save the current workbook as fileName in .Xls format.
    xl.SaveAsXlsx fileName   ⍝ Save the current workbook as fileName in .Xlsx format.
    xl.SaveAsPdf fileName    ⍝ Save the current worksheet as fileName in .Pdf format.

    ⍝ Note: fileName = filename with full path and extension

There is many more methods and options available from Syncfusion.
Their complete online documentation is at http://help.syncfusion.com/wpf and look for XlsIo at the bottom of the left pane.

Knowned limitations

XlsIo will work fine on most simple cases but it is not a 100% perfect replacement of Microsoft Excel. For example the shapes of Excel are not supported in XlsIo.

How to install sfExcel in your workspace

Download sfExcel_v1.txt and save it to your disk.
Open sfExcel_v1.txt with Notepad.
Do a Select all (Ctrl+A) and a copy (Ctrl+C).
In your workspace execute ')ed ○sfExcel'
Paste the text of the Notepad into the Dyalog editor
Press Escape and ')save' your workspace

Share your snipplets of code !

You can share the methods related to this class here.
Just edit this section yourself with your method(s) or alternatively you can send me an email and I will add them to this section.

Contributed by PierreGilbert
CategorySyncfusionExamples