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.
- Use a database driver. See http://linx.blog.twenty57.com/2014/09/read-from-and-write-to-excel-with.html.
- We give you a utility that you can run from Linx to convert xls to xlsx or csv. You will need Excel installed on the Linx Server.
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.