Need to find the width of a column in excel using cfspreadsheet.

[RobertAtGaN]

Saw note to use getColumn width from a static function but don't know how to call it within coldfusion.

static int getWidth(Sheet sheet, int col) {  int width = sheet.getColumnWidth(col);  if (width == sheet.getDefaultColumnWidth()) {    width = (int) (width * 256);  }  return width;}

[Kevin D. Wright · Accepted solution]

Robert,

It is relatively easy to use the underlying POI library once you get a hook into it with CF.

<!--- create the spreadsheet with ColdFusion --->
<cfset sObj = SpreadsheetNew("mySpreadsheet") />

<!--- fill your spreadsheet with your data / query however --->

<!--- get a handle on the ColdFusion underlying POI object --->
<cfset objWorkbook = sObj.getWorkbook() />

<!--- get a handle on the workbook's sheet --->
<cfset objSheet = objWorkbook.getSheetAt(objWorkbook.getActiveSheetIndex()) />

<!--- then you can reference the columns within the sheet and get it's width --->
<cfset myColWidth = objSheet.getColumnWidth(columnIndex) />

[BKBK · Accepted solution]

Some additional suggestions to @Kevin D. Wright 's code:

• For completeness, place the following line at the top
  

  <cfparam name="variables.columnIndex" default="0">​

  This is related to the next bullet-point.
• Be aware that the code is Java, hence column numbering starts with 0.
• The unit of measurement of the width is (1/256)th of the width of a single character. So a width of 2048 would be equivalent to a space occupied by 8 characters.

See for example https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Sheet.html#getColumnWidth-int- 

[RobertAtGaN]

Thanks