New Request: .xls support on Excel Components

We are currently involved in/planning two Linx 5 projects that will be using the excel components in Linx 5. However, most excel files we will be dealing with are .xls not .xlsx, therefore support for this is needed. Also, is there any way of completely reading an excel sheet without knowing the range beforehand?

Xls and the Excel plugin

Xls is the old Excel format. We do not support it in our Excel plugin because

  • It is being superseded by xlsx.
  • You need Excel installed on the server running Linx.
  • The APIs differ between xls and xlsx which makes supporting the same functionality problematic.

Working with xls

Although we do not intend to support it in our Excel plugin we can provide other mechanisms to deal with it. Here are some ideas.

Unknown range

Please let me know which of start position, width or length are unknown and send me a couple of samples.

I think I’m going to have to look into converting the files, as many of them come from an older version of SSRS.

With regards to the range of the excel document, I can filter for the correct lines in the file, however, when specifying a range on the component configuration, i’m not sure how many lines (transactions) the file is going to have beforehand. I’m wondering whether I just specify the range as some arbitrarily large number, so that I’m sure to read all of the transactions in the file. This, however, makes me worried about the performance of the solution, if it has to do a bunch of unnecessary loops.

On my notebook it takes 1 min 26 seconds to run through 100,000 lines using the standard ExcelRead function. You can probably do a quick calculation to see whether you might be looking at performance problems.

There are areas we can improve e.g.

  • Stop reading the Excel when no more data is present.
  • Introduce a Break function that allows you to break out of loops when your
    processing is finished.

I’m a bit late to the game, but you can retrieve all of the rows by using an expression like A:E. The expression is based on the A1 reference style.
It seems we support all of these except the entire-rows expression (like 2:5).

ExcelRead now only reads data up to the last populated row, even if you specify a much larger range.