Generating XLS Files

A scenario may arise where you need generate an Excel 97 file (.xls) for certain systems. Currently in Linx, there exists an Excel plugin which allows one to read/write excel spreadsheets using the built in functions, however this only works with the (.xlsx) format.

There is however a workaround in Linx which allows one to generate (.xls) files. It works by converting the data for the file into Bytes and writing it to a file which will be (.xls).

Linx 5 - WriteExcelFileXLS.lsoz (12.0 KB)
Linx 6 - Linx6.zip (16.3 KB)



Please note the terms ‘Process’ and ‘Custom Type’ have been depreciated and have been replaced with ‘Function’ and ‘Type’ respectively. More details here.

CustomTypes – To hold the data values that will be written into the file
RowColValue_Record – Holds the location and value of the cell
Fields:

  • Col: integer
    
  • Row: integer
    
  • Value: string
    

RowColValue_List – Holds a list of Record types
Field: RowColValue_List: List<Project.CustomTypes.RowColValue_Record>
What this means is that the custom type will be of type ‘List’ and the ‘List’ is made up of items of the type ‘CustomType > RowColValue_Record’

ExcelProcesses:

  • addString_RowCol: Concatenates the values passed in and returns them as a list of bytes
  • writeExcel: Adds the returned Bytes from above and creates the actual file.

RunProcess:

  • RunProcess: This process Function integrates all the above functions in a real-world example of reading from a database and passing through the fields to get written into an .xls file. Use this process Function as a base to understand how to use the Excel writing workaround.

You will need to configure the DB connection string appropriately if you want to test with the example.

Thanks to Koos van Staden for the great solution.