Hi!
We are facing two issues which are rather urgent, because our customer is expecting the results to be correct results.
The issue can be observed in two ways (and I think that the underlying problem is the same).
SCENARIO 1:
The input file (insert_input.xlsx) contains two sheets. On sheet 1 there is a pie chart and some values from which the pie chart has been constructed. Sheet 2 contains an exact copy of that pie chart, referencing the exact same range on the first sheet. When running the attached example app, you will get a file insert_output.xlsx. What the app does is insert new rows and then save the XLSX. The chart on the first sheet is correctly updated to include the correct values; the chart on the second sheet, however, is not and therefore is missing some values.
SCENARIO 2:
The exact same scenario as the first one, the only difference being that in this case an existing row will be deleted by the app. Again, the first chart is correctly updated, where the chart on the second sheet is not getting updated (therefore having an empty value in the legend, because it didn’t recognize the row deletion).
Maybe the underlying issue is the same in both scenarios: charts on other sheets are not updated when rows are being inserted/deleted on another sheet, even though they reference the range that contains the deleted/inserted rows.
You can find the input files + VB.NET project that performs both scenarios in the attached ZIP:
AsposeCellsDeleteOrInsertRowBug.zip (5.1 MB)
Since this is a rather urgent issue for us, I would be very happy to have a quick response - thanks!
Kind Regards,
Bernd
@bernd.podhradsky,
Thanks for the sample project, template files and details.
Well, since your chart in the second sheet has source in the first (other) sheet, so you need to update reference (in other worksheets) whenever you will insert or delete rows/cols into the cells. I have updated your code segment that works fine and as expected, please refer to it and update your original code accordingly:
e.g
Sample code:
.........
Dim lWorkbook As New Workbook("insert_input.xlsx")
'PERFORM AN INSERTION
With lWorkbook.Worksheets.First().Cells
.InsertRows(2I, 1, True)
.GetCell(2I, 0I).PutValue("2011")
.GetCell(2I, 1I).PutValue(50000.0R)
.InsertRows(3I, 1, True)
.GetCell(3I, 0I).PutValue("2012")
.GetCell(3I, 1I).PutValue(90000.0R)
.InsertRows(4I, 1, True)
.GetCell(4I, 0I).PutValue("2013")
.GetCell(4I, 1I).PutValue(2000.0R)
.DeleteRows(5I, 1, True)
End With
lWorkbook.Save("insert_output1.xlsx", SaveFormat.Xlsx)
'PERFORM A DELETION
lWorkbook = New Workbook("delete_input.xlsx")
lWorkbook.Worksheets.First().Cells.DeleteRows(3I, 1, True)
lWorkbook.Save("delete_output2.xlsx", SaveFormat.Xlsx)
(Note: I used InsertRows and DeleteRows overloads because these methods have Boolean parameter i.e., updateReference. The simple InsertRow/DeleteRow don’t have such overloads. The updated code is doing the same as you were doing)
Hope, this helps a bit.
Hi!
Thanks. So are you saying that references will not be updated when using InsertRow/DeleteRow as I did it? If yes, why did the chart on the same sheet update correctly? Or does this only refer to objects from another sheet?
Bernd
Yes, updateReference only affects on other worksheets in the workbook.
Very interesting, thank you!