This is a bit of a weird one that I have been unable to replicate in a simple manner. I recently upgraded aspose.cells from 19.3.2 to 19.9 and then this issue started occurring.
Basically, workbooks created by Aspose have weird behaviour when later manually creating hyperlinks in Microsoft Excel.
We had a reporting system that used excel to create workbooks. We rewrote it to use aspose.cells. Since our latest upgrade to aspose.cells the resultant workbook has weird behaviour when later being used by users in Microsoft Excel.
I have tried to replicate with simple code steps but it seems to only happen with workbooks generated out of our reporting system which has a lot of processing that occurs.
I have attached the initial output created by both excel (old way) and aspose (new way) via our reporting system. I have also included what it looks like after I perform some operations to the output manually via Microsoft Excel to demonstrate the weird behaviour.
There is also a document showing the steps outlined below to replicate the issue.
Steps to replicate:
Open output (ie workbook)
Select cell D10
Change backcolour to something other than white (e.g. Yellow)
Right mouse click cell D10 to add hyperlink
Select to hyperlink within workbook and leave as A1
The background colour of Yellow is gone, as is the line border from the top of the cell
Doing these same steps with the initial output generated via old excel system does not affect background or cell borders. Also does not happen when generated out of aspose.cells version 19.3.2.
I am hoping that by providing the two different outputs (one via Excel and the other via Aspose) you can see what is inside the workbook that might cause them to behave differently.
We noticed the issue/behavior after performing the steps using your provided files. But we need more details and samples:
Was “ExcelVersionOrig.xls” file created by older Aspose.Cells (19.3.2) or it was created manually in MS Excel ?
Was “AsposeVersionOrig.xls” file created by Aspose.Cells v19.9.x?
Did you change any code segment while upgrading to the newer version and which code segment? Could you at least provide sample code segments comparisons (older version Vs new version) for relevant modules, you may provide some sample text file containing those code segments for evaluation.
When you manually do perform the task separately and then add background and hyperlinks, does it not lose the background color of the cell (hyperlinked)?
After getting these details and samples, we will evaluate your issue further and log the appropriate ticket(s) if required.
ExcelVersionOrig was created using excel. It was not manually created though. It was created using our old system which we rewrote using Aspose.
AsposeVersionOrig was created by Aspose.Cells 19.9.0
I did remove one line of code (a call on the workbook to RemoveUnusedStyles) because it started to get an error. I thought the error may have resulted from CELLSNET-46480 but had not got around to raising it here as I was unsure if I even needed that call so was conducting testing when I discovered this other issue. See additional comment with full replication details for that error.
When adding a hyperlink only the font details change (ie to be blue and underlined). This has been the case for our legacy excel system for over 15 years. It has also been the case for the rewritten Aspose system for the past 4 years. It is only since upgrading from Aspose.Cells 19.3.2 to 19.9.0 that we have started to see the behaviour where the background is reset. If we create the hyperlink within the system using aspose while creating the workbook it is fine. It is just the resultant workbook when opened in excel later that has the weird behaviour.
Please also now find attached the same report generated with Aspose.Cells 19.3.2. As you can see the same behaviour (of reset background and cell border) does not occur for that workbook when later used in excel.
Further to above, here are details for replicating the error I get with RemoveUnusedStyles. This code can also be used to replicate my initial hyperlink issue by skipping over the call to RemoveUnusedStyles so that it does not error and saves the workbook. The final workbook has the same hyperlink issue.
Error: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
at System.ThrowHelper.ThrowArgumentOutOfRangeException()
at System.Collections.Generic.List`1.get_Item(Int32 index)
at .(Int32 , )
at Aspose.Cells.Workbook.RemoveUnusedStyles()
Code to replicate:
'' Open source as read only
Dim loFromWB As Aspose.Cells.Workbook
Dim loFromStream As System.IO.FileStream
loFromStream = New System.IO.FileStream("D:\temp\Styles\Initial.xlsx", IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.ReadWrite)
loFromWB = New Aspose.Cells.Workbook(loFromStream)
loFromStream.Close()
'' Copy source sheet into new Workbook
Dim loWBTo As New Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx)
Dim loToSheet As Aspose.Cells.Worksheet = loWBTo.Worksheets(0)
loToSheet.Copy(loFromWB.Worksheets(0))
Dim lvColour, lvUnderline, lvItalic, lvBold, lvSize, lvName As Object
Dim loCell As Aspose.Cells.Cell
Dim loHyperLinks As Aspose.Cells.HyperlinkCollection = loToSheet.Hyperlinks
'' Loop through details and create hyperlinks pointing to same cell
'' Make sure existing font details are preserved after creating the hyperlinks
For liCol As Integer = 0 To 4
For liRow As Integer = 4 To 6
loCell = loToSheet.Cells(liRow, liCol)
Dim loStyle As Aspose.Cells.Style = loCell.GetStyle
With loStyle.Font
lvColour = .Color
lvUnderline = .Underline
lvItalic = .IsItalic
lvBold = .IsBold
lvSize = .Size
lvName = .Name
End With
Dim lsAddress As String = "Sheet1!"
Select Case liCol
Case 0
lsAddress &= "A"
Case 1
lsAddress &= "B"
Case 2
lsAddress &= "C"
Case 3
lsAddress &= "D"
Case 4
lsAddress &= "E"
End Select
Select Case liRow
Case 4
lsAddress &= "5"
Case 5
lsAddress &= "6"
Case 6
lsAddress &= "7"
End Select
Dim liIndex As Integer = loHyperLinks.Add(loCell.Name, 1, 1, lsAddress)
loHyperLinks(liIndex).ScreenTip = "Click to drilldown"
With loStyle.Font
If (lvColour <> .Color) Then .Color = lvColour
If (lvUnderline <> .Underline) Then .Underline = lvUnderline
If (lvItalic <> .IsItalic) Then .IsItalic = lvItalic
If (lvBold <> .IsBold) Then .IsBold = lvBold
If (lvSize <> .Size) Then .Size = lvSize
If (lvName <> .Name) Then .Name = lvName
End With
loCell.SetStyle(loStyle)
loCell = Nothing
Next
Next
'' Comment out this line to avoid error and generate a workbook
loToSheet.Workbook.RemoveUnusedStyles()
'' Cleanup and Save
loHyperLinks = Nothing
loWBTo.Save("D:\temp\Styles\Final.xlsx")
Here is the “Initial” workbook and then the “Final” workbook (if you do skip over the RemoveUnusedStyles).
Thanks for the sample code, sample files and details.
I guess your issue (as described in first post) might be due to this exception as you have to comment out the line and have to generate the file without it. Anyways, we got to figure this issue out. I was able to reproduce the issue as you mentioned by using your sample code with your sample file(s). I got an exception “System.ArgumentOutOfRangeException: Index was out of range” when calling Workbook.RemoveUnusedStyles(). I have logged a ticket with an id “CELLSNET-46952” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-46952”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
The issues you have found earlier (filed as CELLSNET-46952) have been fixed in Aspose.Cells for .NET v19.10 (Dlls). This message was posted using Bugs notification tool by Amjad_Sahi
Just letting you know that after upgrading to 19.10 I no longer receive the error calling RemoveUnusedStyles. As a result of re-adding that line the weird background colour resetting when creating hyperlinks has now also gone.
@t1jsw,
Good to know that your issue is sorted out by the latest version. Feel free to contact us at any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
