Can I adjust the width of Excel columns without setting them separately?

Question

I am using cfspreadsheet to create an Excel spreadsheet using ColdFusion. I insert the title bar and then use spreadsheetAddRows to send the request to the sheet. The problem is that the columns are often not wide enough. I know that I can use SpreadsheetSetColumnWidth to adjust each column separately, but is there a way that I can just apply automatic width to the whole sheet? I do not know the maximum width of each column, and I do not want to apply it to each column separately. Excel has an automatic width function for columns - is there a way to call it from ColdFusion code? (Or even better: can I add an automatic width & mdash; set each column to a maximum width of + 2 or something like that)

Answer 1

The last thing I checked was not a registered CF function. However, you can use the POI method autoSizeColumn (columnIndex) to automatically size each column. Just note that the POI uses a base zero for sheet and column indices.

 <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>