How to find contents when XLS->Worksheets->Cells with MaxData(Column|Row) and Max(Row|Column) return -1

Hello! We are running into the following situation:

We use use MaxData(Column|Row) to find if a worksheet has contents. We found an xls (97-2003) that despite having content its sheets return -1 in these properties.

Is there another method/property that we could to accurately find if a sheet has contents in its cells?

Best regards,

Jose

@jose.cornado,
You may share your sample Excel file which is causing this issue. We will reproduce the scenario here and provide assistance accordingly.

Hello! Thanks for your prompt response!

I just asked about this file to the pertinent people. I will let you know if we can pass it along.

Best regards,

Jose

@jose.cornado,
Sure, take your time to collect the required data and share with us for further analysis.

Hello!

Please find attached the xls in question. Please keep it confidential.

Best regards,

Jose

LTS-617.zip (9.6 KB)

@jose.cornado,
This issue is reproduced and logged in our database for further investigation and a fix. You will be notified here once any update is ready to share in this regard.

This issue is logged as:
CELLSCPP-267 - cells->GetMaxDataRow() and cells->GetMaxDataColumn() return -1

@jose.cornado,
Please refer to the API reference Aspose.Cells for C++ - API Reference

GetMaxDataRow(): Maximum row index of cell which contains data. Return -1 if there is no cell which contains data.

GetMaxDataColumn(): Maximum column index of cell which contains data. Return -1 if there is not cell which contains data.

Hello!

The problem is a little more complex than it seems. The same xls reports MaxDataRow != -1 if loaded from a directory. The error consistently happens in our testing environment (cloud based) even before we perform any manipulations on the document.

I will update when a reproducible test case can be attached to the thread.

Thanks a lot!!

@jose.cornado,
You are welcome and take your time to create a reproducible test case which can be executed here for our analysis.

Hello!

I found the problem. I will provide a test for your team next week. It turns out that prior opening the stream, we processed the Excel metadata. This messes up the Cells collection for '97-'03 format. We just changed the order of when the metadata was processed and worked around our problem. But I thought your team may want to evaluate the scenario.

@jose.cornado,
Sure, we will wait for your test code for further analysis.

Metadata.Corrupts.97-02.xls.zip (5.9 MB)

Hello! Please find attached the test project. If you run it with the metadata lines commented out, the variable “i” is set to 3. If the two metadata lines are commented in, “i” has a value of -1.

I hope this helps your time. We worked around this by changing the order of how we manipulated the documents

@jose.cornado,

By just seeing your code segment, I guess this is correct behavior. Please note, when you use the following lines of code, only document properties would be loaded from the workbook which were saved back to the streams (output workbook will have only document properties and no data).
e.g.
Sample code:

    MetadataOptions options = new MetadataOptions(MetadataType.DocumentProperties);
    fs.CopyTo(ms);
    WorkbookMetadata meta = new WorkbookMetadata(ms, options);
    meta.Save(ms);

Now when you read this very workbook from streams into Aspose.Cells.Workbook object model, there would be no data but only properties in the file. That’s why you are getting “-1” for variable “i” when using the lines of code:
e.g.
Sample code:

    Workbook wb = new Workbook(ms);
                int i = wb.Worksheets[0].Cells.MaxDataColumn;
                Console.WriteLine(i);

Let us know if you still have any issue or confusion.

ACK.

Thanks!

@jose.cornado,

You are welcome.

Moreover, your file “LTS-617.xls” is MS Excel95/5.0 format type and Aspose.Cells does not support metadata for such an older file format.