You fixed the listobject.PutCellValue() method with the recent update which is great. But if a listObject has end row enabled (listObject.ShowTotals= true) and you write more values than the table height, you overwrite the content of the totals row, which is bad.
I didn’t prepare an example, you just need to create a template with like 3 rows and enable totals tow, select some aggreate functions you like and add more values with putcellValue() than there are rows. All the content of the endrow will be overwritten, sometimes endrow even gets disabled.
My current workaround is to disable totals row, write values and re enable it. Which negatively impacts listobject behavior by not moving images beneath the table anymore but keeping the totals
row intact.
Edit: Moving images below works in excel when adding lines to a table with total row enabled with tab but not with aspose
@SimplyLiz
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-53284
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Hi,
I found more bugs and i tried to describe them better.
Bug 1:
If you write (listObject.PutCellValue) to a table with totals row enabled, formatting is not carried over as it would work if totals is disabled (example Good 1)
Bug 2:
If you deactivate the totals row before writing and enable after you wrote the values, the format is carried over correctly but it overwrites the existing style of the totals row
Bug3:
Values written to a table are not using existing format (the4re is also no bool isConverted like in PutValue).
Values are written in a weird way so the cell cannot be referenced in formulas (see example next to table Bug3b).
You need to double click the result in the cell after writing in the field and press enter to make the formulas referencing it work.
Code:
ListObjectCollection listObjects = workbook.Worksheets[0].ListObjects;
for (int sheetIndex = 0; sheetIndex < report.Workbook.Worksheets.Count; sheetIndex++)
{
foreach (ListObject listObject in workbook.Worksheets[sheetIndex].ListObjects)
{
int tableWidth = listObject.EndColumn - listObject.StartColumn;
for (int colOffset = 0; colOffset <= tableWidth; colOffset++)
{
// Bug 2:
if (listObject.DisplayName.StartsWith("totalsTest"))
listObject.ShowTotals = false;
// End Bug 2
if (listObject.DisplayName.StartsWith("Formatted"))
{ // Bug 3: write value as string to a table
listObject.PutCellValue(1, colOffset, "11");
listObject.PutCellValue(2, colOffset, "22");
listObject.PutCellValue(3, colOffset, "33.333");
} // Bug 3 End
else
{
listObject.PutCellValue(1, colOffset, 11);
listObject.PutCellValue(2, colOffset, 22);
listObject.PutCellValue(3, colOffset, 33.333);
}
// Bug2:
if (listObject.DisplayName.StartsWith("totalsTest"))
listObject.ShowTotals = true;
// End Bug 2:
}
}
}
// Good 3 : Write strings to formatted cells
workbook.Worksheets[0].Cells["K10"].PutValue("11", true);
workbook.Worksheets[0].Cells["L11"].PutValue("11,135", true);
workbook.Worksheets[0].Cells["K10"].PutValue("05.05.2023", true);
workbook.Worksheets[0].Cells["L11"].PutValue("05.05.2023", true);
Files:
examples.zip (22.1 KB)
@SimplyLiz
We will provide ListObject.PutCellValue(int rowOffset, int columnOffset, object value,bool isTotalsRowLabel) method in the next version.
If isTotalsRowLabel is false and the row is total row, a new table row will be inserted.
For Bug3b, if you expand rows in table Tabelle6, in MS Excel the table Formatted will be shifted dow, the value of the G31 will be #VALUE!. So it’s not a bug.
@SimplyLiz
The cells in the table does not contain existing format, so no styles are copied.
@SimplyLiz,
We are pleased to inform you that your issue (logged earlier as “CELLSNET-53284”) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.6) that we plan to release in the first half of June 2023. You will be notified when the next version is released.
Hi and thank you! I’ve tested it thoroughly, but there are still a few problems left.
First, thank you for the quick fix. PutCellValues with totals set to off now works as expected. yay.
But here are the bugs.
1.) Graphs below are still not moved, as they would be if you add new lines to the table in excel using tab. (see Examples “Totals1” and “Formula2” in the attached example)
2.) You didn’t add the new flag to CellPutFormula, so this is still broken the same way as CellPutValue before. See Example “Formula 1”. Please also fix that function as it’s super important to being able to write formulas to tables for our project. It’s working just fine in Example “Formula 2” without a totals row.
Thanks!
temp.zip (31.5 KB)
Code:
ListObjectCollection listObjects = workbook.Worksheets[0].ListObjects;
int linesToWrite = 20;
for (int sheetIndex = 0; sheetIndex < report.Workbook.Worksheets.Count; sheetIndex++)
{
foreach (ListObject listObject in workbook.Worksheets[sheetIndex].ListObjects)
{
int tableWidth = listObject.EndColumn - listObject.StartColumn;
for (int colOffset = 0; colOffset <= tableWidth; colOffset++)
{
for (int rowOffset = 1; rowOffset <= linesToWrite; rowOffset++)
{ // rowOffset = 1 because of headers
if (listObject.DisplayName.StartsWith("Totals"))
{
listObject.PutCellValue(rowOffset, colOffset, 1, true);
}
else if (listObject.DisplayName.StartsWith("formula"))
{
listObject.PutCellFormula(rowOffset, colOffset, "32+18");
}
else
{
listObject.PutCellValue(rowOffset, colOffset, 0, false);
}
}
}
}
}
@SimplyLiz,
Thanks for the sample and details.
We told you that your issue (logged earlier as “CELLSNET-53284”) has been resolved. But the fix will be included in our upcoming release (Aspose.Cells v23.6) that we will publish in the first half of June 2023. You will be notified when the next version is released. So, let the next version comes out so you could try your scenario/case with it. Hopefully, your issue(s) and concerns will be sorted out in it.
@SimplyLiz
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-53358
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@SimplyLiz
We will support extend chart data source when extending table in the next version 23.6.
@SimplyLiz,
This is to inform you that your issue (CELLSNET-53358 - Support extending rows when setting formula to totals row in the table) has been resolved now. The fix will be included in our upcoming release (Aspose.Cells v23.6) that we plan to release in the first half of June 2023. You will be notified when the next version is released.
Thank you.
What about this bug?
When you place a chart underneath a table with totals row enabled and you extend the table in excel by pressing, the chart object gets moved down when the table is expanded.
When doing the same thing in aspose, the chart does not move and lies over the table.
Additionally, if you disable totals row and put text underneath the table and a chart underneath the text, the table also gets moved. this does not work in aspose. Chart stays where it is.
It is safe to say a graph under a table is never moved as it happens in excel
@SimplyLiz,
Your issue(s) have been addressed and fixes/enhancements will be included in our upcoming release (Aspose.Cells v23.6).
In previous versions, if there are total rows, list objects were not expanding accordingly.
2, Update totalsRowLabel=“Total” to table settings.
Further analysis of the bug have shown that if a chart beneath a table overlaps the last column of a table, the table is not moved (as it would work in excel). If it’s smaller, it works as intended.
See the attached screenshot. As soon as the chart is bigger than the yellow marked column (the last one) chart stays where it is.
Screenshot 2023-05-17 174014.jpg (259.4 KB)
Is this the bug you have adressed? Is it possible to get a pre-release version to help you test that bug?
In previous versions, if there are total rows, list objects were not expanding accordingly.
2, Update totalsRowLabel=“Total” to table settings.
the totalsRowLabe parameter has been introduced (for PutCellValue, not PutCellFormula) with 23.5, i’m already using this, it works great for the original issue but it’s not related to moving the tables.
@SimplyLiz,
Thanks for sharing your concerns with screenshot.
We already addressed similar scenarios. But will evaluate your particular scenario/case and figure it out as well in the upcoming release.
We deliver hot fixes or pre-releases in paid support desk only. In normal support forums, we don’t provide pre-releases for the users.
@SimplyLiz
Please check the attached files :test.zip (31.5 KB)
which is generated by 23.5.1.
Thanks for the preview, but it looks like the first chart in the dest file is being cut off?
Screenshot 2023-05-19 083101.png (125.1 KB)
Screenshot 2023-05-19 083119.png (20.3 KB)
@SimplyLiz,
The attachment is a screenshot of our file. If you still have any questions, please provide us with your documents.result.png (7.8 KB)
The issues you have found earlier (filed as CELLSNET-53358,CELLSNET-53284) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi