We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Automatic table resizing with data

In Excel, Tables will resize automatically to contents when data is written. (PFA Excel macro example (Module1, Test)

Is there anything like this for Aspose.Cells?

Thanks,
DeanExpandTable.zip (19.0 KB)

@Moonglum,
You can use Aspose.Cells to accomplish all the tasks except running or executing macros. I am afraid, currently, Aspose.Cells does not support to execute/run vba codes or macro functions although we have supported to create, manipulate or embed VBA codes/ macros. We still however could not support to run or execute macros or vba code.

You may consider using AutoFitColumn() function to expand the column according to the data in the cells. Following article demonstrates this feature:
Autofit columns in a range of cells

The VBA was just an example of how tables resize automatically (rows and columns) when data is written.
Can we get the functionality to automatically expand table rows and columns in Aspose.cells like Excel’s native functionality?

@Moonglum,
Could you please explain your use case in detail as in the shared article, it will automatically expand the columns according to the available data? You can add data in the workbook and then use AutoFitColumn() function to set the column width.

I want a table to resize when data is written to it. This is native Excel table behavior.

PFA video demonstrating tables expanding in rows when data copied and pasted to it.VwvGuRuRNq.zip (524.4 KB)

Notice the rows of the table are automatically formatted (alternate color, data appears in header filters, etc.)

@Moonglum,
You can achieve this requirement by resizing the list object as demonstrated in the following sample code:

// Create a Workbook object.
// Open a template excel file.
Workbook workbook = new Workbook(dataDir + "ExpandTable.xlsx");

// Get the List objects collection in the first worksheet.
ListObjectCollection listObjects = workbook.Worksheets[0].ListObjects;
var listObject = listObjects[0];

//Copy data
Worksheet ws = workbook.Worksheets[0];
//Source range to be copied
Aspose.Cells.Range sourceRange = ws.Cells.CreateRange("C4:E13");

//Destination range
Aspose.Cells.Range destRange = ws.Cells.CreateRange("H4:J13");


//Copy the source range into destination range
destRange.Copy(sourceRange);
//Resize the list object
listObject.Resize(2, 7, 12, 9, true);

ws.AutoFitColumns();
// Save the excel file.
workbook.Save(dataDir + "output.xlsx");

ExpandTable.zip (7.3 KB)
output.zip (7.5 KB)
output.png (10.9 KB)

Perfect, this is exactly what I was looking for.

Thanks!

@Moonglum,
You are welcome.