Aspose Cells hyperlink count

Aspose.Cells Product Family
1
The below code produces an Excel workbook that "requires recovery". If I output one less row or one less column, it opens up fine. I receive an object error in VBA when creating this many hyperlinks in a single worksheet. This is clearly an Excel processing limitation: 1285 * 51 = 65,535 - the maximum 16-bit integer value. The real number from my testing is a maximum of 65,530 hyperlinks in one worksheet.

Should Aspose Cells likewise give an error when adding a hyperlink and there are already 65,530 hyperlinks in a worksheet? Or should I always test the current count of hyperlinks before adding one?

'USING ASPOSE CELLS 8.2.0.0
'USING Microsoft Office Professional Plus 2010's Microsoft Excel, opening the generated file I receive "Excel found unreadable content in 'Sample.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.".
Dim WORKBOOK As New Aspose.Cells.Workbook()
Dim SHEET1 = WORKBOOK.Worksheets.Item(0)
Dim SHEET2 = WORKBOOK.Worksheets.Item(WORKBOOK.Worksheets.Add())
For ROWCTR = 0 To 1284
For COLCTR = 0 To 50
Call SHEET1.Hyperlinks.Add(SHEET1.Cells(ROWCTR, COLCTR).Name, 1, 1, "='" & SHEET2.Name & "'!" & SHEET2.Cells(ROWCTR, COLCTR).Name)
Call SHEET2.Hyperlinks.Add(SHEET2.Cells(ROWCTR, COLCTR).Name, 1, 1, "='" & SHEET1.Name & "'!" & SHEET1.Cells(ROWCTR, COLCTR).Name)
Next COLCTR
Next ROWCTR
WORKBOOK.Save("C:\TEMP\Sample.xlsx", Aspose.Cells.SaveFormat.Xlsx)



'USING Microsoft Office Professional Plus 2010's Microsoft Excel, running this macro on an empty workbook I receive "Run-time error '1004': Application-defined or object-defined error" on row 1285 column 47 (letter AU).
Public Sub HLinks()
Dim SHEET1 As Worksheet
Dim SHEET2 As Worksheet
SHEET1 = ActiveWorkbook.Worksheets("Sheet1")
SHEET2 = ActiveWorkbook.Worksheets("Sheet2")
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For ROWCTR = 1 To 1285
For COLCTR = 1 To 51
Call SHEET1.Hyperlinks.Add(SHEET1.Cells(ROWCTR, COLCTR), "='" & SHEET2.Name & "'!" & Replace(SHEET2.Cells(ROWCTR, COLCTR).Address, "$", ""))
Call SHEET2.Hyperlinks.Add(SHEET2.Cells(ROWCTR, COLCTR), "='" & SHEET1.Name & "'!" & Replace(SHEET1.Cells(ROWCTR, COLCTR).Address, "$", ""))
Next COLCTR
Next ROWCTR
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

2

Hi,


Thanks for providing us details and sample code.

After an initial test, I observed the issue as you mentioned. MS Excel gives the “Excel found unreadable content…” error when opening the output file into MS Excel. Aspose.Cells does not give any exception when adding these many hyperlinks using your sample code via Aspose.Cells APIs as we have crossed the limits for hyperlinks, i.e., 1285 * 51 = 65,535 - the maximum 16-bit integer value.
We need to check if we could give an exception when adding so many hyperlinks in the spreadsheet or suppress the error message. I have logged a ticket with an id “CELLSNET-42943” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
3

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.2.0.2 and let us know your feedback.

4

I used the Aspose.Cells.dll version 8.2.0.2 as provided in the link. I was able to run the sample code without generating an error. Opening the newly created document I received the same “Excel found unreadable content” error.

5

Hi,

Thanks for your feedback and using Aspose.Cells.

We were able to replicate this issue after executing the following code using the latest version: Aspose.Cells
for .NET v8.2.0.2. The output file does not open fine and shows “Excel found unreadable content…” error.

But if we change the maximum rows to 100, then it works fine and generates correct file.

I have attached the output xlsx file which is corrupt and generated by the following code for a reference.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-42964 - “Excel found unreadable content…” error while generating Hyperlinks


VB.NET

Dim WORKBOOK As New Aspose.Cells.Workbook(FileFormatType.Xlsx)

Dim SHEET1 = WORKBOOK.Worksheets.Item(0)

Dim SHEET2 = WORKBOOK.Worksheets.Item(WORKBOOK.Worksheets.Add())

For ROWCTR = 0 To 1284

For COLCTR = 0 To 50

Call SHEET1.Hyperlinks.Add(SHEET1.Cells(ROWCTR, COLCTR).Name, 1, 1, “='” & SHEET2.Name & “'!” & SHEET2.Cells(ROWCTR, COLCTR).Name)

Call SHEET2.Hyperlinks.Add(SHEET2.Cells(ROWCTR, COLCTR).Name, 1, 1, “='” & SHEET1.Name & “'!” & SHEET1.Cells(ROWCTR, COLCTR).Name)

Next COLCTR

Next ROWCTR

WORKBOOK.Save(“Sample.xlsx”, Aspose.Cells.SaveFormat.Xlsx)



6

"using the following sample code"


This sample code produces 62,867 hyperlinks on two separate worksheets. (1283 * 49 = 62,867) This issue only shows when saving a document with more than 65,530 hyperlinks on a worksheet. When your sample code conditions are increased to ROWCTR <= 1284 and COLCTR <= 50, each worksheet will contain 65,535 hyperlinks thus causing Excel to display the error.

P.S. Please post Visual Basic code samples for me to test. I’m allergic to curly braces.
7

Hi Don,

Thanks for your feedback and using Aspose.Cells.

We were able to reproduce this issue already and logged it in our database for a fix. Once, the issue is fixed or we have some other update for you, we will let you know asap.

Please refer to the above post: 570215.

8

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.2.0.3 and let us know your feedback.

9

The newest fix Aspose Cells for .NET v8.2.0.3 correctly throws an Aspose.Cells.CellsException with the message text “The count of Hyperlinks cannot be larger than 66530.” Thank you for this fix.

10

Hi,


Thanks for your feedback.

Good to know that it suits your needs now, we have closed your ticket. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
11

The issues you have found earlier (filed as CELLSNET-42964;CELLSNET-42943) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
12

Just want to let you all know that there is a typo in your error message. You say can’t have more than 66530 hyperlinks, when the real max is 65530 as you all stated earlier in this post

13
Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue, the count of hyperlinks in the exception message is wrong. It shows 66530 but it should be 65530.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-45370 - Exception Message is Wrong - The count of Hyperlinks cannot be larger than 66530

Here is the test code we used to replicate this issue and the screenshot that shows the exception message for a reference.

C#
Workbook wb = new Workbook();

Worksheet ws = wb.Worksheets[0];

for(int i=0; i<65531; i++)
{
Cell cell = ws.Cells[i, 0];

ws.Hyperlinks.Add(cell.Name, 1, 1, "http://www.aspose.com");

}

wb.Save("output.xlsx");

14
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSNET-45370 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
15
Hi,

Thanks for using Aspose.Cells.

Please download and try the following latest fix

Aspose.Cells for .NET v17.5.3 (.NET 2.0) compiled in .NET Framework 2.0.
Aspose.Cells for .NET v17.5.3 (.NET 4.0) compiled in .NET Framework 4.0.

and let us know your feedback.
16

The issues you have found earlier (filed as CELLSNET-45370) have been fixed in Aspose.Cells for .NET 17.6.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.