MaxDataColumn and MaxDataRow returns -1 in LightCells API

Aspose.Cells Product Family
1

MaxDataColumn and MaxDataRow returns -1 when the file format is .xls or .xlsx. However it returns the correct value when the file format is .xlsb


How to resolve this?


2
Hi,

Thanks for query.

Well, we need your template (.xls, .xlsx etc.) file to evaluate your issue on our end, please provide us the file here, we will check it soon.

Thank you.


3

Here you go.


Attached you will find the data in .xlsb format. Use the same file in .xlsx format (use SaveAs method of excel), then try reading the maxcolumn property.
4

Hi,


Thanks for the template file.

<span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:“Times New Roman”;mso-ansi-language:EN-SG;mso-fareast-language:
EN-SG;mso-bidi-language:AR-SA”>Please try our latest version/fix: Aspose.Cells
for .NET v8.3.1.6
I have tested your scenario with it using the template Excel XLSX file (I simply opened your XLSB file and re-saved it as XLSX file format in MS Excel manually) using the following sample code, it works fine and gives me correct farthest row and column indexes:
e.g
Sample code:

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(“e:\test2\Servers_san1.xlsx”);
int maxCol = workbook.Worksheets[0].Cells.MaxDataColumn; //Ok
int maxRow = workbook.Worksheets[0].Cells.MaxDataRow;//Ok

If you still find the issue with v8.3.1.6, kindly create a simple console application with v8.3.1.6, zip it and post us here with all the files, we will check it soon.

Thank you.

5

This fix did not work.


It leads to compilation issue. ??? Attached you will find the exception message screenshot.

6

Hi,


Well, the Aspose.Cells.Cells class is there in the newer versions as they were before in the older versions of the product. I have tested using the similar code segment as per your attached screen shot, it works fine here.

Could you separate the issue regarding Aspose.Cells component only and create a fresh and simple application with Aspose.Cells for .NET v8.3.1.6, zip it and post us here to reproduce the issue on our end, we will check it soon.

Thank you.
7

We tried and it does not compile.


Attached is the console application for your reference.
8

Hi,


Thanks for the project.

I have tested your project a bit and found those compile time errors when I tried to build the project. Well, it is hard to evaluate your big project properly, it looks like you are combing normal mode and light mode a bit. We appreciate if could simplify your project to separate the issue regarding Aspose.Cells component only in a smaller application, zip it and post us here to reproduce the issue on our end, it will surely help us to save our time and look into your issue more precisely and efficiently to consequently figure the issue (if found) out soon.

Thank you.
9

We were just reviewing this again and found that there are a number of issues.


workbook.worksheet contains a collection of worksheet that are not iteratable. And most of the code that we have written does not work with this version of the dll. We are unable to implement LINQ on top of worksheets. However it works with 8.3.1.0.

Even the following line does not compile:

//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets.Where(s => s.Name.ToLower().Trim().Equals(sheet.Name.ToLower().Trim())).SingleOrDefault();
10

Hi


As per your request, I have attached the console application.
It works fine when I am reading a xlsb file. But when I am reading xlsx file it still returns -1.
11

Hi,


Well, it might be due to the fact that the fix(v8.3.1.6) shared earlier is compiled on .NET framework 2.0, so you might be getting these errors on compile time. I have attached the latest fix for Client Profile version for your needs.

Could you try it and let us know your results and did you try .NET framework 3.5 version from v8.3.1.0 and what was the result.

If you still find any issue regarding MaxDataRow and MaxDataColumn, kindly provide more details with sample application, sample files, screen shots, etc., we will check it soon.

Thank you.
12
Hi,

koppikar:

As per your request, I have attached the console application.
It works fine when I am reading a xlsb file. But when I am reading xlsx file it still returns -1.

Thanks for the new project with template files.

I have tested your scenario/ case using your recently attached project.
Well, since your underlying template "1.xlsx" file has List Object/ table, so you need to import data with formatting via LoadOptions, so kindly change your line of code:
i.e.,
loadOptions.LoadDataAndFormatting = false;

to:
loadOptions.LoadDataAndFormatting = true;

I have tested with the updated line of code and it works fine.


Thank you.

13

Hi,

Furthermore, please note:

For LightCells, LoadDataAndFormatting, currently we only support XLS/XLSX(including XLSM,XLTM,XLTX file formats) files.

  1. When reading XLSB file, the LightCells API is not used and in fact it is read in normal mode, so you can get the correct data row/column, etc.

  2. When setting LoadDataAndFormatting as true, the LightCells API also will not be used anymore. However, this setting will be made obsolete in future and replaced with LightCells API.

  3. When using LightCells, commonly cell data will be processed by the user’s implementation of LightCellsDataHandler and disregarded after calling LightCellsDataHandler.ProcessCell(Cell cell). In this way there is no cell object in memory for the workbook/worksheet after the loading process, so lots of memory can be saved. However, because there is no cell object in Cells collection, the MaxDataRow/MaxDataColumn is surely returns -1 for the empty collection. If you do want to keep all or some cells in memory, you can let LightCellsDataHandler.ProcessCell(Cell cell) to return true for those cells. If you keep all cells in memory after the loading, then you can get the correct MaxDataRow/MaxDataColumn(with higher memory cost). In fact for performance consideration, we think it is easy and better for you to calculate MaxDataRow/MaxDataColumn value by yourself in LightCellsDataHandlerVisitCells.

Thank you

14

Hi,


We want to read the data without format(string).
Please let us know how to achieve this??






15

Hi,


There is no such option available to read data only, either you will read data with formulas/formatting only (it will load only data, formulas and formatting from the file - other contents
and settings are all discarded. For example : shapes ,validation,conditional
formatting, PivotTables are not loaded any more) by making use of LoadOptions.LoadDataAndFormatting to set it to true. Or if you set it to false, everything would be included while loading the file.

Thank you.