As an example, suppose I have a worksheet with a table (ListObject) spanning from A1 (first row is headers) to, say, F2 with formulas in all cells except columns B through E and, more importantly, I have disabled all table styling and have my own formatting for all the different columns in the worksheet.
Now, using Aspose Cells, I write values for, B2 through E11 in something along the following way:
var x = new Random();
for (var row = 1; row <= 10; ++row)
{
for (var col = 1; col <= 4; ++col)
{
sht.Cells[row, col].PutValue(x.Next(1, 200));
}
}
What I would suppose to happen once I save the Excel sheet would be that the table would be expanded through to row 11 and the formatting would also carry throughout the columns, but neither happens.
I did manage to update the table formatting by using the ReSize() method for the listobject, but, even then, the formatting of the columns didn’t carry across.
Ideally, I’d expect that if I entered data into an existing table and kept adding in data into rows after that, that the table would auto-resize as it does in Excel, but I definitely am not sure about what to do about the formatting not carrying through.
Please try to first resize the ListObject/Table (via ListObject.Resize() method) based on maximum rows/column accordingly and then insert data into the cells if it makes any difference. You may also try to use ListObject.PutCellValue() method to insert your data. If you still find the issue, kindly do provide us your template Excel file here, we will check it soon.
The challenge is that I’'m trying to write a library to be used by all my programs and, usually, all I want to do is insert data into cells that may oy may not be part of a table, so it’s tough to reference a ListObject - Of course I could if there was no other way around it, but I was truly hoping that if I simply put data into cells that included a ListObject, it would be resized automatically.
As for attaching the template file / source code, I don’t have any buttons to include them / know how to do that otherwise. If you let me know how, I’ll happily attach my sample project, but it can very easily be recreated based upon the example I gave above:
Create a worksheet with a table in Range A1:F2 - headers in first row
Remove all table formatting and format each of the cells B2-E2 in different ways
Create any formulas you want in A2 and F2
Save workbook to be used by your project (next step)
Create project that opens workbook workbook and writes data into B2:E11, say
Save updated workbook
When I do this using code much like mentioned above, the table isn’t even expanded and even when I expand it, I still don’t have the formatting carry over.
@Amjad_Sahi, in looking at this a bit closer, although I definitely want a solution to the original problem too, it seems that even if I just create a table with formatting as I stated above and simply do a ListObject.Resize() it doesn’t carry the formatting through the rows.
Again, this is part of a larger issue, but I did want to bring that to your attention too.
Thanks for providing us further details. Well, you may use “Upload” button while replying (after clicking on Reply button) to attach files, see the screenshot for your reference:
Well, I have evaluated your scenario/ case using a simple template file (attached) and following sample code and found that the custom formatting (for the specific cells) is not applied in the expanded rows in the table. I found the custom formatting is overwritten by the built-in table’s original formattings. I have also attached the output file for your reference:
e.g Sample code:
Workbook workbook = new Workbook("e:\\test2\\Bk_writingval1.xlsx");
Worksheet ws = workbook.Worksheets[0];
Aspose.Cells.Tables.ListObject listObject = ws.ListObjects[0];
listObject.Resize(0, 0, 10, 5,true);
var x = new Random();
for (var row = 1; row <= 10; ++row)
{
for (var col = 1; col <= 4; ++col)
{
ws.Cells[row, col].PutValue(x.Next(1, 200));
}
}
workbook.Save("e:\\test2\\out1.xlsx");
Could you confirm, this is the issue you are talking about or you find some other issue. You may also give us more details, sample code (runnable) and template files, we will check and log a ticket for it to fix the issue. files1.zip (20.7 KB)
PS. I am using v16.6.x, so you should try our latest version/fix if you are not already using it.
Thanks so much, @Amjad_Sahi. Unfortunately, when I try and download your files, I get an error:
The forum.aspose.com page isn’t working
forum.aspose.com is currently unable to handle this request.
However, based upon your code and explanation, that is the 100% the second issue I mentioned - I resize the ListObject, but don’t get my custom formatting carried across.
The other issue I mentioned is simply that I’d like to know if the ListObject need be resized or if, like Excel, Aspose would see the table range expanded and resize the Table accordingly.
It seems, we have found the issue. Please check the files attached by Amjad as well as the screenshot showing the comparison between source and output Excel files. Please review them and then we will create your ticket in our database.
We have logged this issue in our database for investigation and for a fix. Once, your issue is resolved or we have some other news for you, we will let you know asap.
This issue has been logged as
CELLSNET-45493 - Resizing the ListObject do not get custom formatting carried across
Please make sure you have logged in Aspose site first and then open the thread and try to download the attachment (I attached in my previous reply) if it works fine.
Let us know if you still find the issue with downloading the attachments.
We are pleased to inform you that we have fixed your issue (logged earlier as “CELLSNET-45493”) now. Hopefully we will soon (in the next a few days) provide you the fix after performing QA and incorporating other enhancements and fixes.
Wondering if you can help - I purchased Aspose Cells in 2015 (version 8.6) and need to know how to get the fix into my version or if you allow previous customers to download “fix” versions.
Please could you advise how best I can get it and please let me know if you need any other information regarding purchase information, etc.
Well, your issue was fixed in Aspose.Cells for .NET v17.7, so in order to use newer versions (e.g v17.7 or v17.8 (latest): Download .NET DLL Components to Process Excel | Aspose.Cells), I am afraid, you got to upgrade your subscription first, there is no other alternative to it.
FYI, when you purchase a license for a product, you are authorized to use the license file with any new (and upcoming (official versions)) versions or hot fixes of the component for the next whole year. Moreover, your license will never expire if you continue to use your license with the product’s version/hotfix that should be released before your subscription expiry date.
You may open your license file into notepad (please do not edit the license file least it won’t work anymore) and check the subscription date. You will see that the subscription expiry date is smaller than fix (v17.7) July release date, so, you may not use it with the latest versions.
