QueryRange Max column - breaking change

[karczk-dnv]

Excel Type

• [ x ] XLSX
• [ x ] XLSM
• [ ] CSV
• [ ] OTHER

MiniExcel Version

1.38 - OK, all columns are parsed even if they don't exist

1.39 - unknown / not tested 1.40 - exception from iterator

Code example

var rows = wbStream.QueryRange(
    sheetName: "Some sheet name", // HINT: sheet has columns from A to X
    startCell: "A", 
    endCell: "ALL"); // HINT: column no. 1000


foreach (var row in rows)
{
    // Exception: "Invaild sheet dimension start data"
}

[shps951023]

It looks like 1.38 bug, because miniexcel QueryRange doesn't support ALL keyword. But you can use Query, its default support all columns.

[karczk-dnv]

@shps951023 what do you mean by keyword? :) ALL is just the next "excel letter" after ALK

1 - A 2 - B 100 - CV 1000 - ALL 1001 - ALM

By using ALL I limit columns to 1000.

[michelebastione]

I think this is due to a change I implemented to solve issue #686, but honestly I believe this shouldn't have worked to begin with. When you use parameters startCell and endCell you're supposed to give a full reference for the cells, e.g. A1 or ALL23

@shps951023 Am I wrong to assume this was always the intended behaviour?

[shps951023]

@karczk-dnv 😅 I got the point. @michelebastione Because I never use QueryRange, but for me, it should be A1 or ALL23 not A or ALL. (the name has cell)

[shps951023]

@karczk-dnv yes, it's a breaking change, we will update release notes. you can specific the cell number to resolve the issue. please feel free to feedback.

[karczk-dnv]

@michelebastione @shps951023 I agree with you that if there is cell in the variable name, the address should also contains a number.

However this function is still misleading. Let's forget about everything for now and just look at the definition. My reasoning:

var rows = MiniExcel.QueryRange(path, startCell: "A1", endCell: "J1"); It should return one row: A1-J1

var rows = MiniExcel.QueryRange(path, startCell: "A2", endCell: "J2"); It should return one row: A2-J2

var rows = MiniExcel.QueryRange(path, startCell: "A5", endCell: "J6"); It should return two rows: A5-{the last column, could be more than J}5, A6-J6

Actual:

var rows = MiniExcel.QueryRange(path, startCell: "A1", endCell: "J1"); All rows are returned, A1-J1, A2-J2 etc.

var rows = MiniExcel.QueryRange(path, startCell: "A2", endCell: "J2"); One row is returned:, A2-J2

This is not consistent, it looks like a bug. I'm afraid I'll migrate to this and then it'll get fixed and I'll be surprised again :)

Proposal:

Use cells but respect it :) That will be a breaking change, again. var rows = MiniExcel.QueryRange(path, startCell: "A1", endCell: "J1");

and add new overload (because the cells themselves do not fulfill all the scenarios)

var rows = MiniExcel.QueryRange(path, startRowNumber: 2, endRowNumber: 500, startColumn: "A", endColumn: "J"); where endRowNumber is nullable (null = take to the end what is there) where endColumn is nullable (null = take to the end what is there)

What do you think about it?

[michelebastione]

@karczk-dnv Thank you for your feedback. I agree that the logic should be made more consistent by making MiniExcel.QueryRange return a single row when both startCell and endCell's number is 1. I'm not sure about the overloads, but it can be done. Ultimately it's @shps951023 's decision.

[karczk-dnv]

Just you to know, I just tested this case:

var rows = MiniExcel.QueryRange(path, startCell: "A5", endCell: "J6"); It should return two rows: A5-{the last column, could be more than J}5, A6-J6

In pratice it is: two rows: A5-J5, A6-J6

That could be misleading as well (K5, L5 etc. are skipped). I'm trying to help if you're going to work on the next version.

My scenario that I am currently trying to solve is: I want to parse all rows, but limit columns to 1000. I guess that only the new overload would solve it.

When you fix scenario: var rows = MiniExcel.QueryRange(path, startCell: "A1", endCell: "J1");

then I need to use some dummy number: var rows = MiniExcel.QueryRange(path, startCell: "A1", endCell: "ALL100000"); not the best option...

When you fix scenario: var rows = MiniExcel.QueryRange(path, startCell: "A5", endCell: "J6"); then there is not workaround for me (at least using QueryRange)

[shps951023]

I think we can add new overload for column : var rows = MiniExcel.QueryRange(path, startRowNumber: 2, endRowNumber: 500, startColumn: "A", endColumn: "J"); And we need to fix current version startCell: "A5", endCell: "J6" issue.