Can I adjust the widths of Excel columns without setting them each individually?

Question

I'm using cfspreadsheet to generate an Excel spreadsheet using ColdFusion. I insert a header row, and then use spreadsheetAddRows to dump a query into the sheet. The problem is that the columns are often not wide enough. I know I can use SpreadsheetSetColumnWidth to adjust each column individually, but is there any way that I can just apply an auto-width to the entire sheet? I don't know the max width of each column, and I don't want to apply it to each column individually. Excel has an auto-width feature for columns — is there any way to trigger it from the ColdFusion code? (Or even better: Can I add on to the auto-width — set each column to the max width + 2 or something?)

Answer 1

Last I checked there was not a documented CF function. However you can use POI's autoSizeColumn(columnIndex) method to auto size each column. Just note POI uses base zero for sheet and column indexes.

<cfscript>
    // create a workbook and add a long value
    wb = SpreadSheetNew();
    spreadSheetSetCellValue(wb, repeatString("x", 200), 1, 1);
    // get the first sheet
    sheet = wb.getWorkBook().getSheetAt( javacast("int", 0) );
    // resize first column ie "A"
    sheet.autoSizeColumn( javacast("int", 0) );
    spreadSheetWrite( wb, "c:/test.xls", true ); 
</cfscript>