Differences between revisions 10 and 50 (spanning 40 versions)
⇤ ← Revision 10 as of 2014-11-10 13:04:09 →
Size: 8723
Editor: PierreGilbert
Comment:
← Revision 50 as of 2018-05-05 12:02:42 → ⇥
Size: 14092
Editor: PierreGilbert
Comment:
Deletions are marked like this. Additions are marked like this.
Line 1: Line 1:
<<TableOfContents>>  = 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.		 <<TableOfContents>>

= sfExcel =

== Overview ==

'''sfExcel''' is a Dyalog 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.
Line 5: Line 12:
The following assemblies are required in the subdirectory '''/Syncfusion/4.5''' of the Dyalog APL version 14 (or newer) directory: The following assemblies (version 12.3 or newer) are required in the subdirectory '''/Syncfusion/4.5''' of the Dyalog APL version 14 (or newer) directory:
Line 12: Line 19:

When saving to a PDF file the following assemblies are also required: 		  * Syncfusion.Grid.WPF.dll

When saving to a PDF file the following assemblies are also required:
Line 16: Line 24:
Line 17: Line 26:
=== 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.
=== Instantiating 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.
Line 24: Line 35:
Line 25: Line 37:
Line 30: Line 43:
}}}
If the data is only Numbers or only Text use instead '''.!SetNumber''' and '''.!SetText''' that are faster:
 ⍝ With version 1.6 the Position of Upper Left Corner of the range can be used with the .SetXXX methods.
  'D5' xl.SetValue 4 3⍴⍳12
   5 4 xl.SetValue 4 3⍴⍳12
}}}
If the data is only Numbers or only Text use instead `.SetNumber` and `.SetText` that are faster:
Line 38: Line 55:
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.		 The methods `.SetNumber2` and `.SetText2` are very fast for importing a large set of number and text to the spreadsheet. They are using a `DataTable` internally. 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 (see also [[netDataTable]] for more info on !DataTable):
{{{
  dt ← xl.AplToDT ?100000 5⍴100000 ⍝ Get a .Net DataTable from the APL data.
   1 1 xl.ImportDataTable dt ⍝ Import the DataTable at position [row;col] (upper left corner).
   1 1 xl.SetNumber2 ?100000 5⍴100000 ⍝ Same thing but the DataTable is used internally.
}}}
For dates use the method `.TsToOADate` that gives a number compatible with the Excel date format.
Line 47: Line 65:
For writing an array after the last row or column of the current Worksheet.
{{{
       xl.AddAfterLastRow array
       xl.AddAfterLastColumn array
}}}
Line 48: Line 71:
Line 54: Line 78:
The methods '''.!GetNumber''' and '''.!GetText''' are faster when only numbers or characters are in the range: The methods `.GetNumber` and `.GetText` are faster when only numbers or characters are in the range:
Line 61: Line 85:
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.		 The method `.ExportDataTable` is recommended for large range. The methods `.GetNumber2` and `.GetText2` are using a `DataTable` internally and are very fast for large array of the same type. See [[netDataTable#To_Query_a_DataTable_.28Contributed_by_Richard_Proctor.29|link]] to learn how to query a `DataTable` obtained with the method `.ExportDataTable`.
{{{
   dt←xl.ExportDataTable 'J10:S19' ⍝ Export the spreadsheet data as a .Net data table.
      xl.DTtoApl dt ⍝ Convert a .Net DataTable to APL.

      xl.GetNumber2 1 1 100000 5 ⍝ Retrieve large array while using a DataTable internally.
   ds←xl.ExportDataSet ⍝ Export the Workbook as a DataSet (collection of DataTables).
}}}
To convert dates use `.OADateToTs` that convert the OA Date back to `⎕TS`.
Line 69: Line 96:
}}}     }}}
Line 71: Line 99:
Line 75: Line 104:
   range xl.SetBorderAround 'Thin' ⍝ Set a 'Thin' border around range.
Line 85: Line 115:
   xl.Merge range ⍝ Merge cells in range.        xl.Merge range ⍝ Merge cells in range.
Line 91: Line 121:
   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).		    xl.UsedRange ⍝ Get the range used by the active worksheet in RC notation.

   xl.ConvertA1toRC ⍝ Convert from A1 notation to Row and Column Index notation (RC).
Line 101: Line 131:
Remark for method with a '''range''' as argument:<<BR>>
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 and dimension(⍴) of the apl variable must match all the time		 Remark for methods 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.
Line 109: Line 141:
Line 114: Line 147:
      xl.SetActiveWorksheet sheet ⍝ Set active a specific worksheet. sheet = SheetName or Index of the worksheet.       xl.SetActiveWorksheet sheet ⍝ Set active a worksheet. sheet = SheetName or Index of the worksheet.
Line 116: Line 149:

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

=== Methods related to the workbook: ===

{{{
    xl.ExportDataSet ⍝ Export the Workbook as a DataSet (collection of DataTables).
    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.SaveAsCSV fileName ⍝ Save to file the specify worksheet with the CSV format.
    xl.SaveAsStream ⍝ Save the Current WorkBook As a Stream in .Xlsx format.
    xl.LoadFromStream ⍝ Load an Excel Workbook from a Stream obtained by the method 'SaveAsStream'
    xl.SaveAsHtml fileName ⍝ Save the current worksheet as fileName in .Html format.
    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/ug/wpf/default.htm#!documents/XlsIO.htm|http://help.syncfusion.com/wpf|target="_blank"]] and look for '''!XlsIo''' at the bottom of the left pane.

Syncfusion is using the assembly '''Syncfusion.Compression.Base.dll''' to zip and unzip the Excel file in the .Xlsx format. The Wiki [[sfZip]] is using that assembly to Zip and !UnZip text and file(s) on disk.

=== Known 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.

Dyalog APL version 14.0 is shipping with version 12.1 of Syncfusion assemblies, you need to upgrade them to 12.3 otherwise not everything will work correctly.

MS Excel supports 16,384 columns by 1,048,576 rows in Excel 2007 and above formats (*.xlsx), and 256 columns by 65,536 rows in Excel97to2003 format (*.xls). XlsIO allows the same limit in its respective formats. By default, the Excel version is Excel97to2003 (*.xls) in XlsIO. So, you have to set the !ExcelVersion property of IWorkbook to Excel2007 or above to use entire 1,048,576 rows.

=== How to install sfExcel in your workspace ===

 1. Download [[attachment:sfExcel.v2.1.txt]]
 1. Do a Select all (Ctrl+A) and a copy (Ctrl+C).
 1. In your workspace execute `)ed ○ sfExcel`
 1. Paste (Ctrl+V) the text into the Dyalog editor
 1. Press Escape and ')save' your workspace

=== Test Run ===

If you load the sample worksheet from the book ''Mastering Dyalog APL'' from Bernard Legrand you get the following:
{{{
      xl←⎕NEW sfExcel

      xl.LoadFile 'd:\XLDemo.xls'

      7 7↑ xl.GetValue (xl.UsedRange)
       2009 [Null] [Null] Sales forecast [Null] [Null] [Null]
 Updated on [Null] January 17 2008 [Null] Source
    Country Coffee Tea Chocolate Soda Sugar Biscuits
    Germany 1089 783 5217 2309 643 304
      Spain [Null] [Null] 5420 4380 650 320
      Italy 1050 800 5500 3210 660 330
     Canada 1080 800 5620 2560 [Null] 380

      xl.Show

    ⍝ To Show the same file with MS Excel
      XL←⎕NEW'OleClient'(⊂'ClassName' 'Excel.Application')
      XL.Workbooks.Open(⊂'d:\XLDemo.xls')
      XL.Visible←1
}}}
{{attachment:XLDemo.png || width=800}}

== Version Information ==
{{{
 Version 1.0 November 2014, Pierre Gilbert

 Version 1.1 November 2014, Typo corrections suggested by Daniel Baronet

 Version 1.2 November 2014, Typo corrections suggested by Daniel Baronet

 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

 Version 1.6 June 2015
   Methods Improved: SetNumber, SetText, SetValue, AplToDT
   Methods Added: SetNumber2, SetText2, GetNumber2, GetText2 for large array
   Methods Modified: DTtoApl returns numeric 0 on empty cells
                     All the SetXXX methods accept now the upper left corner position of the array
                     like: 1 1 xl.SetNumber 5 4⍴⍳20
   Bug corrected in: SetWorksheetName, DeleteWorksheet, SaveAsCSV, SaveAsHtml, SetActiveWorksheet

   ⎕USING is now Global instead of Local for most of the Methods

 Version 1.7 September 2015
   Additional comments added on methods: ExportDataTable and ExportDataSet

 Version 1.8 October 2015
   Bug corrected in ParseRange when using RC notation on single value

 Version 1.9 August 2017
   Bug corrected in RangeDimension: did not report the correct dimensions on empty range (thanks to Michael Baas)
   Bug corrected in GetNumber: will return a number on a cell with a formula (thanks to Michael Baas)

 Version 2.0 August 2017
   Bug corrected in GetText: will return a text on a cell with a formula (thanks to Michael Baas)

 Version 2.1 Feb 2018 (thanks to Michael Bass)
   performance improvements for GetValue (Factor 100 or more!)
Line 117: Line 264:
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 ⍝ Print the active 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.<<BR>>
Their online documentation is at [[http://help.syncfusion.com/wpf||target="_blank"]] and look for '''!XlsIo''' in 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 ===
 1. Download [[attachment:sfExcel_v1.txt]] and save it to your disk.
 1. Open '''sfExcel_v1.txt''' with Notepad.
 1. Do a Select all (Ctrl+A) and a copy (Ctrl+C).
 1. In your workspace execute ')ed ○sfExcel'
 1. Paste the text of the Notepad into the Dyalog editor
 1. Press Escape and ')save' your workspace
== Share your snipplets of code ! ==
You can share the methods related to this class here.<<BR>>
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.
{{{

}}}		 }}}
||Original author: ||Pierre Gilbert ||
||Responsible: ||PierreGilbert ||
||Email: || <<MailTo(apgil AT SPAMFREE videotron DOT ca)>> ||

----
CategoryDyalog - CategoryDyalogDotNet - CategorySyncfusionWpfExamples - CategoryDotNet

Contents

  1. sfExcel
    1. Overview
    2. Installation and deployment
    3. Typical Usage
      1. Instantiating and Disposing of the class:
      2. Writing APL data to the spreadsheet:
      3. Reading data from the spreadsheet to APL:
      4. Methods for Formatting a range:
      5. Methods related to the worksheet:
      6. Methods related to the workbook:
      7. Known limitations
      8. How to install sfExcel in your workspace
      9. Test Run
    4. Version Information

sfExcel

Overview

sfExcel is a Dyalog 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 (version 12.3 or newer) 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

Instantiating 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.

 ⍝ With version 1.6 the Position of Upper Left Corner of the range can be used with the .SetXXX methods.
  'D5' xl.SetValue 4 3⍴⍳12
   5 4 xl.SetValue 4 3⍴⍳12

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 methods .SetNumber2 and .SetText2 are very fast for importing a large set of number and text to the spreadsheet. They are using a DataTable internally. 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 (see also netDataTable for more info on DataTable): 

  dt ← xl.AplToDT ?100000 5⍴100000      ⍝ Get a .Net DataTable from the APL data.
   1 1 xl.ImportDataTable dt            ⍝ Import the DataTable at position [row;col] (upper left corner).
   1 1 xl.SetNumber2 ?100000 5⍴100000   ⍝ Same thing but the DataTable is used internally.

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.

For writing an array after the last row or column of the current Worksheet. 

       xl.AddAfterLastRow array
       xl.AddAfterLastColumn array

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. The methods .GetNumber2 and .GetText2 are using a DataTable internally and are very fast for large array of the same type. See link to learn how to query a DataTable obtained with the method .ExportDataTable. 

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

      xl.GetNumber2 1 1 100000 5    ⍝ Retrieve large array while using a DataTable internally.
   ds←xl.ExportDataSet              ⍝ Export the Workbook as a DataSet (collection of DataTables).

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.SetBorderAround 'Thin'  ⍝ Set a 'Thin' border around range.

   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.

   xl.ConvertA1toRC             ⍝ Convert from 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 methods 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. 

      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 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

    xl.ExportDataSet         ⍝ Export the Workbook as a DataSet (collection of DataTables).
    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.SaveAsCSV fileName    ⍝ Save to file the specify worksheet with the CSV format.
    xl.SaveAsStream          ⍝ Save the Current WorkBook As a Stream in .Xlsx format.
    xl.LoadFromStream        ⍝ Load an Excel Workbook from a Stream obtained by the method 'SaveAsStream'
    xl.SaveAsHtml fileName   ⍝ Save the current worksheet as fileName in .Html format.
    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.

Syncfusion is using the assembly Syncfusion.Compression.Base.dll to zip and unzip the Excel file in the .Xlsx format. The Wiki sfZip is using that assembly to Zip and UnZip text and file(s) on disk.

Known 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.

Dyalog APL version 14.0 is shipping with version 12.1 of Syncfusion assemblies, you need to upgrade them to 12.3 otherwise not everything will work correctly.

MS Excel supports 16,384 columns by 1,048,576 rows in Excel 2007 and above formats (*.xlsx), and 256 columns by 65,536 rows in Excel97to2003 format (*.xls). XlsIO allows the same limit in its respective formats. By default, the Excel version is Excel97to2003 (*.xls) in XlsIO. So, you have to set the ExcelVersion property of IWorkbook to Excel2007 or above to use entire 1,048,576 rows.

How to install sfExcel in your workspace

  1. Download sfExcel.v2.1.txt

  2. Do a Select all (Ctrl+A) and a copy (Ctrl+C).

  3. In your workspace execute )ed ○ sfExcel

  4. Paste (Ctrl+V) the text into the Dyalog editor
  5. Press Escape and ')save' your workspace

Test Run

If you load the sample worksheet from the book Mastering Dyalog APL from Bernard Legrand you get the following: 

      xl←⎕NEW sfExcel

      xl.LoadFile 'd:\XLDemo.xls'

      7 7↑ xl.GetValue (xl.UsedRange)
       2009  [Null]   [Null]  Sales forecast  [Null]  [Null]    [Null]
 Updated on  [Null]  January              17    2008  [Null]    Source
    Country  Coffee      Tea       Chocolate    Soda   Sugar  Biscuits
    Germany    1089      783            5217    2309     643       304
      Spain  [Null]   [Null]            5420    4380     650       320
      Italy    1050      800            5500    3210     660       330
     Canada    1080      800            5620    2560  [Null]       380

      xl.Show

    ⍝ To Show the same file with MS Excel
      XL←⎕NEW'OleClient'(⊂'ClassName' 'Excel.Application')
      XL.Workbooks.Open(⊂'d:\XLDemo.xls')
      XL.Visible←1

XLDemo.png

Version Information

 Version 1.0 November 2014, Pierre Gilbert

 Version 1.1 November 2014, Typo corrections suggested by Daniel Baronet

 Version 1.2 November 2014, Typo corrections suggested by Daniel Baronet

 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

 Version 1.6 June 2015
   Methods Improved: SetNumber, SetText, SetValue, AplToDT
   Methods Added:    SetNumber2, SetText2, GetNumber2, GetText2 for large array
   Methods Modified: DTtoApl returns numeric 0 on empty cells
                     All the SetXXX methods accept now the upper left corner position of the array
                     like:  1 1 xl.SetNumber 5 4⍴⍳20
   Bug corrected in: SetWorksheetName, DeleteWorksheet, SaveAsCSV, SaveAsHtml, SetActiveWorksheet

   ⎕USING is now Global instead of Local for most of the Methods

 Version 1.7 September 2015
   Additional comments added on methods: ExportDataTable and ExportDataSet

 Version 1.8 October 2015
   Bug corrected in ParseRange when using RC notation on single value

 Version 1.9 August 2017
   Bug corrected in RangeDimension: did not report the correct dimensions on empty range (thanks to Michael Baas)
   Bug corrected in GetNumber: will return a number on a cell with a formula (thanks to Michael Baas)

 Version 2.0 August 2017
   Bug corrected in GetText: will return a text on a cell with a formula (thanks to Michael Baas)

 Version 2.1 Feb 2018 (thanks to Michael Bass)
   performance improvements for GetValue (Factor 100 or more!)

Original author:

Pierre Gilbert

Responsible:

PierreGilbert

Email:

<apgil AT SPAMFREE videotron DOT ca>

CategoryDyalog - CategoryDyalogDotNet - CategorySyncfusionWpfExamples - CategoryDotNet

sfExcel (last edited 2018-05-05 12:02:42 by PierreGilbert)