Support Knowledge Base, Article 611 |
| Product |
| ExcelWriter |
| Title |
| How To: Autofit all cells in a workbook when using the ExcelTemplate object |
| 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 alot 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
|
| Created : 1/7/2008 3:11:09 PM (last modified : 1/8/2008 5:45:43 PM) |
|
