Skip to main content
R
RobertAtGaN
Inspiring
January 2, 2023
Answered

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

  • January 2, 2023
  • 1 reply
  • 516 views

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

 

    This topic has been closed for replies.
    Correct answer BKBK

    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- 

    1 reply

    K
    Kevin D. Wright
    Participant
    January 7, 2023

    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
      Community Expert
      BKBKCommunity ExpertCorrect answer
      Community Expert
      January 9, 2023

      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- 

      R
      RobertAtGaNAuthor
      Inspiring
      January 9, 2023

      Thanks

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices