Problem
When using the ExcelTemplate object to populate a spreadsheet with data, you may want to AutoFit the row heights or column widths to better display the data in the cells. The AutoFit needs to be done after all the data has been populated.
ExcelTemplate is a small, light-weight object model that is designed to populate data very quickly. ExcelTemplate does not provide AutoFit functionality, because that would require parsing the entire spreadsheet and creating objects for every single cell, the way the ExcelApplication object does with its much larger object model.
Solution
One solution is to pass the workbook to the ExcelApplication object after it has been populated with data, and then perform the AutoFit (using ColumnProperties.AutoFitWidth or RowProperties.AutoFitHeight). However, this can have an impact on performance, particularly if the workbook contains a lot of data.
If you want to avoid reopening the workbook on the server with the ExcelApplication object, another solution is to use a macro that will run when the user opens the file on the client machine. The following VBA code can be placed in your template spreadsheet to AutoFit all of the cells in a workbook. The code should be placed in the ThisWorkbook object in the VBA Project for the spreadsheet.
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Me.Worksheets ws.Cells.Columns.AutoFit ws.Cells.Rows.AutoFit Next End Sub
Share the post "How to autofit all cells in a workbook when using the ExcelTemplate object"
Follow